Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: Doculite – Use SQLite as a Document Database

npmjs.com

Post reply on HN
Print view View original

1–10 of 61 posts

Show HN: Doculite – Use SQLite as a Document Database

#1

Post by thenorthbay »

Hi!

As I was working on a side project, I noticed I wanted to use SQLite like a Document Database on the server. So I built Doculite. DocuLite lets you use SQLite like Firebase Firestore. It's written in Typescript and an adapter on top of sqlite3 and sqlite.

Reasons:

1) Using an SQL Database meant having less flexibility and iterating slower.

2) Alternative, proven Document Databases only offered client/server support.

3) No network. Having SQLite server-side next to the application is extremely fast.

4) Replicating Firestore's API makes it easy to use.

5) Listeners and real-time updates enhance UX greatly.

6) SQLite is a proven, stable, and well-liked standard. And, apparently one of the most deployed software modules right now. (src: https://www.sqlite.org/mostdeployed.html)

What do you think? Feel free to comment with questions, remarks, and thoughts.

Happy to hear them.

Thanks

Show HN: Doculite – Use SQLite as a Document Database
npmjs.com

Re: Show HN: Doculite – Use SQLite as a Document Database

#3

Post by bastawhiz »

The one feature that I'd want out of this is atomic writes. If I have a document and want to increment the value of a field in it by one, I'm not sure that's possible with Doculite today: if two requests read the same document at the same time and both write an incremented value, the value is incremented by one, not two.

The way _I_ would expect to do this is something like this:

  const ref = db.collection('page').doc('foo');
  do {
    const current = await ref.get();
    try {
      await ref.set({ likes: current.likes + 1 }, { when: { likes: current.likes } });
    } catch {
      continue;
    }
  } while (false);
If `set()` attempts to write to the ref when the conditions in `when` are not matched exactly, the write should fail and you should have to try the operation again. In this example, the `set()` call increments the like value by one, but specifies that the write is only valid if `likes` is equal to the value that the client read. In the scenario I provided, one of the two concurrent requests would fail and retry the write (and succeed on the second go).

Re: Show HN: Doculite – Use SQLite as a Document Database

#4

Post by tracker1 »

Kind of nifty... Just curious if this is using the JSON functions/operators for SQLite under the covers?

https://www.sqlite.org/json1.html

Edit: where is the database file stored? A parameter for the Database() constructor seems obvious, but not seeing it in the basic sample.

Re: Show HN: Doculite – Use SQLite as a Document Database

#5

Post by thenorthbay »

tracker1 wrote: post #4

Kind of nifty... Just curious if this is using the JSON functions/operators for SQLite under the covers? https://www.sqlite.org/json1.html Edit: where is the database file stored? A parameter for the Database() constructor seems obvious, but not seeing it in the basic sample.

Yes – I'm using JSON_extract and generated virtual columns https://www.sqlite.org/json1.html#jex Edit: the database is stored in a sqlite.db file in the cwd

Re: Show HN: Doculite – Use SQLite as a Document Database

#7

Post by DANmode »

bastawhiz wrote: post #3

The one feature that I'd want out of this is atomic writes. If I have a document and want to increment the value of a field in it by one, I'm not sure that's possible with Doculite today: if two requests read the same document at the same time and both write an incremented value, the value is incremented by one, not two. The way _I_ would expect to do this is something like this: const ref = db.collection('page').doc…

I'd like to enable the same in my startup.

What are you using for this today?

Re: Show HN: Doculite – Use SQLite as a Document Database

#8

Post by thenorthbay »

bastawhiz wrote: post #3

The one feature that I'd want out of this is atomic writes. If I have a document and want to increment the value of a field in it by one, I'm not sure that's possible with Doculite today: if two requests read the same document at the same time and both write an incremented value, the value is incremented by one, not two. The way _I_ would expect to do this is something like this: const ref = db.collection('page').doc…

Interesting. Updating values via incrementing them is a use case I barely had in Firebase. I mostly only dealt with 1-time updates to values, e.g. by the user or scheduled jobs. In which scenario would the current design cause you problems?

Re: Show HN: Doculite – Use SQLite as a Document Database

#9

Post by simonw »

thenorthbay wrote: post #5
tracker1 wrote: post #4

Kind of nifty... Just curious if this is using the JSON functions/operators for SQLite under the covers? https://www.sqlite.org/json1.html Edit: where is the database file stored? A parameter for the Database() constructor seems obvious, but not seeing it in the basic sample.

Yes – I'm using JSON_extract and generated virtual columns https://www.sqlite.org/json1.html#jex Edit: the database is stored in a sqlite.db file in the cwd

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.
Post reply on HN