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

11–20 of 61 posts

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

#11

Post by winrid »

thenorthbay wrote: post #8
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?

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

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

#12

Post by __jonas »

cyanydeez wrote: post #6

I'd see if you can easily port the on top of browser based sqlite in wasm, that's expand your user base and lead to some of the "holy Grail" in the offline first/sync systems

Why not just use IndexedDB in the browser if you don’t want an SQL database?

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

#13

Post by bastawhiz »

DANmode wrote: post #7
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?

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.

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

#15

Post by thenorthbay »

winrid wrote: post #11
thenorthbay wrote: post #8

Earlier quoted context omitted.

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?

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

I guess it was sufficient to not have that kind of accuracy in most of the application to deliver user value. There probably were parts of the application where we used transactions. Could be a cool feature to build for this, though.

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

#16

Post by bastawhiz »

thenorthbay wrote: post #8
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?

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.

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

#17

Post by thenorthbay »

zdragnar wrote: post #14

Why do you have async reads and writes? There's no client-server setup here, using async / await just introduces pointless waiting. https://github.com/WiseLibs/better-sqlite3

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.

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

#18

Post by thenorthbay »

simonw wrote: post #9
thenorthbay wrote: post #5

Earlier quoted context omitted.

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…

Yup! I actually don't know about that. I figured this would be used for setting up newer, server-side Remix or Next projects rather than more dated ones. I could imagine that by keeping sqlite and sqlite3 up to date, people might also not be stuck on more dated versions of SQLite. The tracing/profiling functions were also only implemented recently in sqlite3 (node to C interface), in 2023.
Post reply on HN