Ive nothing to say but “that’s cool”. I want to try this in NodeJS!
I suppose using functions defined by the host with SQLite is cheaper than using similarly defined functions in databases that are separated by a network. I wonder what the overhead is.
Also, what’s with the weird UUIDs? Why not use UUIDv7 if you want time-ordered UUIDs?
> Why not use UUIDv7 if you want time-ordered UUIDs?
It is our flavour of NIH, that said - Tou has a finer-resolution timestamp. We also didn't do our homework right and assumed the v7 UUIDs won't be accepted by Postgres because of a different "version" value.
> I suppose using functions defined by the host with SQLite is cheaper than using similarly defined functions in databases that are separated by a network.
“Infeasible” is very fast. sqlite runs in process so you can register a function pointer or five, with a trampoline back into the runtime.
Can’t do that over the network, you can create functions but only using the database’s procedural langage(s in the case of Postgres).
> Also, what’s with the weird UUIDs? Why not use UUIDv7 if you want time-ordered UUIDs?
It looks like the rationale for the Tou library is that some systems do not accept unfamiliar UUID variants as UUIDs, so a time-ordered ID that looks like a UUIDv4 is safer for some legacy systems than a (newer, and less likely recognized) UUIDv7.
I kind of thought it would ingest but not generate unknown UUID versions that nonetheless fit the broader UUID structure, but not having tried I didn't want to bring that up.
It is much cheaper, because you won't have roundtrips or requirements for the availability of extensions on the database server end. It's really a very very sweet capability that SQLite is able to provide exactly because it is hosted by the application.
> Also, what’s with the weird UUIDs? Why not use UUIDv7 if you want time-ordered UUIDs?
I’m not the author but UUIDv7 came out in about 2022. Guessing this is legacy stuff that long predated that. There were lots of solutions to solve this problem before there was a standard.
> Guessing this is legacy stuff that long predated that.
I’m guessing its not, since the tou library seems to be 8 months old and mentions avoiding the need for extensions if you are using it with Postgres as an advantage over using UUIDv7.
> The sqlite3 C API serializes all operations (even reads) within a single process. You can parallelize reads to the database but only by having multiple processes, in which case one process being blocked doesn't affect the other processes anyways. In other words, because sqlite3 serializes everything, doing things asynchronously won't speed up database access within a process. It would only free up time for your app to do other things (like HTTP requests to other servers). Unfortunately, the overhead imposed on sqlite3 to serialize asynchronous operations is quite high, making it disadvantageous 95% of the time.
The sqlite3 C API very much does not serialize "all operations within a single process."
The way threading and concurrency work in SQLite may not mesh well with NodeJS's concurrency model. I dunno, I'm not an NodeJS/libuv expert.
But at the C API level that statement is just wrong. Normally you cannot share a single connection across threads. If you compile SQLite to allow this, yes, it'll serialize operations using locks. The solution is to create additional database connections, not (necessarily) launch another process. With multiple database connections, you can have concurrency, with or without threads.
Again, whether this is viable in NodeJS, I have no idea. But it's a Node issue, not a C API issue.
BTW, we're commenting on a Ruby article, and SQLite in Ruby has seen "recent" advances that increase concurrency through implementing SQLite's BUSY handler in Ruby, which allows the GVL lock to be released, and other Ruby and SQLite code to run while waiting on a BUSY connection.
The user-supplied busy handler has been available for a long while, it's just that the Rails connection adapters did not quite use it right. Indeed, there is elevated interest in SQLite these days.
This explanation is extremely misleading. For many of my projects, vast majority of the operations do not use the database . And the few that does, contains long running huge joins/aggregates. Using the sync API is just straight up terrible because the task will block literally everything else that does not use the db in js, meaning generating a report in the background can literally prevent you from handling any requests. (I did end up using better sqlite 3 because they are personal projects and getting stuck for 2 seconds when the scheduled report generation happens is ok ish for the a few people using it. But I will not consider using better sqlite 3 for any future projects)
I suppose using functions defined by the host with SQLite is cheaper than using similarly defined functions in databases that are separated by a network. I wonder what the overhead is.
Also, what’s with the weird UUIDs? Why not use UUIDv7 if you want time-ordered UUIDs?