Show HN: Doculite – Use SQLite as a Document Database (opens in new tab)

Interesting project! I have lately been trying out these cool and perhaps in some way similar sqlite libraries:

- https://github.com/haxtra/kvstore-sqlite (Basic key-value store for SQLite databases.)

- 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.)

- https://github.com/haxtra/live-object (Standard javascript object with built-in JSON serialization to file. Dreams do come true sometimes.)

All from github user: https://github.com/haxtra

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)

- https://www.npmjs.com/package/best.db (easy and quick storage)

- https://tinybase.org (This project seems to be an even more similar idea to Doculite) https://github.com/tinyplex/tinybase (The reactive data store for local-first apps.)

Good luck with your project!

They would have to install sqlite and sqlite3 separately and feed the sqlite instance to your library when creating a new instance of your library. It would no longer be a dependency in package.json (maybe a devDependency when you write unit tests using it). Look at how the npm sqlite source code does it as an example.

https://github.com/kriasoft/node-sqlite

In the src/index.ts file, the `open()` function takes in an instance of sqlite3, vs the file importing it from the sqlite3 package itself.

An adapter / driver pattern would extend this where you can interchange usage of either the sqlite package or an alternative.

For example, let's say you have two SQLite drivers. They both allow you to execute a statement, but their methods and maybe parameters are named differently. One might expose a `exec()`, while the other exposes `run()` to do the same thing.

Your codebase probably is coded for one or the other, which means you can't freely interchange the drivers.

So what you do to support this is create a an interface with methods that describe what you want to do. In the above case, you might describe a method called `executeStatement()`.

Then you create two classes, one for each driver. They both implement `executeStatement()`, but under the hood, they'll run `exec()` and `run()` in their implementations.

So your main code now will accept an instance of anything that implements your interface, and instead of calling like `exec()`, you'll be calling `executeStatement()`, which under the hood calls `exec()`.

So it goes like this:

- Define common interface for interacting with different database libraries (the abstraction)

- Implementation class using that interface (the drivers)

- Your constructor takes in anything that conforms to that interface

- The user creates an instance of your implementation (eg SqliteDriver) and feeds in the instance of the driver (eg `new SqliteDriver(<output of npm sqlite open()>)`

- Your code calls the interface methods in place of the actual database calls instead

You had something like:

  db.collection('users').set(..)

So rather than calling SQLite `run()`, it'd be calling your interface method `executeStatment()` (which calls `run()`) instead in that `set()` call.

It looks like the "Bridge" pattern is what you want here:

https://www.phind.com/agent?cache=cll1zw1n60010la08mn89dd8g

The generated code is Java, but the idea and concept is the exact same that I've described above.

Incrementing is only one possible use case. Any time you read data and then write back based on that data, you need to ensure that nobody wrote to the document in the interim. RDBMS do this with transactions.

Consider the case where a user is submitting an e-commerce order. You want to mark their order as processed and submit it for fulfillment. If you read the order to check if it's already submitted, two requests to submit it made at almost the same time (e.g., hitting the button twice) will both read that it's unfulfilled and try to each submit it.

By doing an atomic write you can be sure that at most one request submits the order.

Honestly, it's weird that you never ran into this. This is a requirement for any data store and I've never not used atomic updates at any company. Most basic example: what if two users load the same object at the same time and you want to increment a "seen" count...?

That's just sugar on top of atomic writes

If you're using something like MySQL or Postgres, you can do locking with the built in transaction primitives (see, for instance, SELECT FOR UPDATE). Mongo has tools like findAndModify which can help.

If you're using SQLite you can use exclusive transactions to perform the read+write but I'm sure there's probably a more efficient way to go about it. You can craft an UPDATE that selects on the primary key and the condition and then use sqlite3_changes() to get back the number of records modified (and fail if it's zero), but that may not be possible with your setup.

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.

https://github.com/WiseLibs/better-sqlite3#why-should-i-use-...

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.

Found where you're using those: https://github.com/thenorthbay/doculite/blob/c05d98c209d0031...

It looks like your tables have a single value column and a id generated column that extracts $.id from that value:

      CREATE TABLE IF NOT EXISTS ${collection} (
            value TEXT,
            id TEXT GENERATED ALWAYS AS (json_extract(value, "$.id")) VIRTUAL NOT NULL
      )

GENERATED ALWAYS AS was added in a relatively recent SQLite version - 2020-01-22 (3.31.0) - do you have a feel for how likely it is for Node.js users to be stuck on an older version? I've had a lot of concern about Python users who are on a stale SQLite for my own projects.

Cool, might be worth taking an optional input for the path or "::memory::" for an in-memory database.

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.

a) IndexedDB's API is horrid, so everyone wants to layer an abstraction on top anyways.

b) You can't use IndexedDB on the server, so you wouldn't be able to write sync code that runs on both the client and the server

The same reasons you wouldn’t use IndexedDB on the server?

Modern offline-first applications include a local backend every bit as complex and demanding as a server-based backend.