search

thanhnguyen2187 / crypta

An offline-first code snippet manager
https://thanhnguyen2187.github.io/crypta/
MIT License
7 stars 0 forks source link

`wa-sqlite` integration #28

Closed thanhnguyen2187 closed 8 months ago

thanhnguyen2187 commented 9 months ago

To solve a smaller part of #26 that revolves around using SQLite in browser.

thanhnguyen2187 commented 9 months ago

One related article: https://www.powersync.com/blog/sqlite-persistence-on-the-web

Right now, I’d recommend wa-sqlite’s IDBBatchAtomicVFS for most use cases - it has good performance, supports concurrent reads, has wide browser support, and is a fairly mature implementation.

thanhnguyen2187 commented 9 months ago

Got stuck with a strange error:

caught (in promise) TypeError: Cannot read properties of null (reading 'fileSize')
    at IDBBatchAtomicVFS.xFileSize (IDBBatchAtomicVFS.js:383:47)
    at Ac (wa-sqlite.mjs?url:70:293)
    at wa-sqlite.wasm:0x3d580
    at wa-sqlite.wasm:0x52645
    at wa-sqlite.wasm:0x14eb4
    at wa-sqlite.wasm:0x3e0cd
    at wa-sqlite.wasm:0x37533
    at wa-sqlite.wasm:0xe90e
    at wa-sqlite.wasm:0x3d1d2
    at wa-sqlite.wasm:0x27646
thanhnguyen2187 commented 8 months ago

Task list:

thanhnguyen2187 commented 8 months ago

More on database design, it seems... obvious to have these tables:

The question is for tags and snippet__tag: do we need something like that?

In the end, we settled with snippet_tags, which store snippet's ID and the tag's text. It seems to be good enough, since making it too normalized can be complex on adding new tag completely.

thanhnguyen2187 commented 8 months ago

Another note for SQLite migration: we can use PRAGMA user_version.

PRAGMA user_version;
PRAGMA user_version = 1;

https://stackoverflow.com/questions/989558/best-practices-for-in-app-database-migration-for-sqlite

thanhnguyen2187 commented 8 months ago

Everything seems to work well enough for now. Should test it thoroughly before merging:

thanhnguyen2187 commented 8 months ago

Snippet cloning doesn't work. Should note this for later resolving.

image