You can use a third party NPM module to introspect the DB and write out types for you. Can’t remember what it’s called off the top of my head, but maybe “pocketbase-type gen” or something like that.
I just did a quick search and struggled to find anything about the performance issues you referred to - can you link something so I can take another look?
Thanks for the suggestions.
From the code, it s easy to see that you create a WRITE transaction which has the side effect of triggering READ transactions. It is also important to understand that if you mix reads and writes you cannot do it well concurrently with SQLite, so every transaction will be sequential and blocking. Looking at the code further I understand that it will not scale well, since a single write can possibly trigger a waterfall of callbacks creating bottlenecks. The problem in your case is that sqlite_update_hook doesn't transport back data and that it is listening for changes in all tables having ROWID optimisation on. So first things first you only need one such callback and a different approach for integrating it in a document abstraction than table name and rowid predicate in a dozen of registered callbacks.
You will unveil more problems with SQLite for this job as you dig deeper. What you really want is fast writes and dumb querying by document id, whereas SQLite gives you an ultra querying suite that you don't utilize but still have to pay with slower writes. This is a classic system-design problem. Just try rewriting your collection and document abstractions using proper document data storage and you will see how less complicated it is.
Hey! Thanks for the feedback. I'd just be worried people might not be using the correct libraries since I think they still provide different functionality.
So exporting one Database that allows people to pick which driver they want to use might be a simple and user-friendly solution (incl. better-sqlite3) which people have asked for.
- https://github.com/haxtra/super-sqlite3 (Fast SQLite library with optional full db encryption, simple query builder, and a host of utility features, all in one neat package.)
I think the super-sqlite3 source might also be an inspiration for the 'driver' topic: "super-sqlite3 is a thin wrapper around better-sqlite3-multiple-ciphers, which extends better-sqlite3 (the fastest SQLite library for node.js) with full database encryption using SQLite3MultipleCiphers. super-sqlite3 then adds its own query builder and other convenience features."
And do check out this user's XRay (JavaScript object browser component) library for your preferred component framework.
In my bookmarks I also found these other related and interesting links:
- https://dgl.cx/2020/06/sqlite-json-support (An article about SQLite as a document database, using the relatively new 'genrated columns' feature of sqlite 3.31.0, which you seem to be using)
Yup! I doubt this project will naturally evolve into whatever you're describing. And that's ok! This project might help people who want to use SQLite like Firebase, with a similar API and experience but without letting network requests increase latency (see: https://news.ycombinator.com/item?id=31318708).
Addressing main points like implementing atomic transactions (read and write operation on a doc) seems warranted since it exists in Firebase as well.
If you use the library on a server in a node.js environment, wouldn't it be useful to fetch data (e.g. Remix / NextJS)?
Besides, I'm not sure if better-sqlite3 offers the listener functionalities I care about. Skimming the docs, it seems it doesn't.
better-sqlite3 is orders of magnitude faster than the async SQLite bindings. We found this to be true when testing SQLite options for Notion's desktop app anyways. The "why should I use this" bits sound boastful but are reasonable.
Listener functionality could be something you'd have to write yourself, I suppose.
As for on the server, no. Sqlite is a c library, not a separate application- the work happens inside the node process. Regardless of how you do it, any call into sqlite is going to block. Adding promises or callbacks on top of that is just wasting CPU cycles, unlike reading from the filesystem or making a network request, where the work is offloaded to a process outside of node (and hence why it makes sense to let node do other things instead of waiting).
In fact, if you synchronously read and write within a single function with no awaits or timeouts in-between, you don't have to worry about atomicity- no other request is being handled in the meantime.
Yeah interesting. I'm wondering what happens when I'm starting to introduce things from "outside" the system that need async operations, like processing webhooks.
