[HN Gopher] Show HN: Doculite - Use SQLite as a Document Database
       ___________________________________________________________________
        
       Show HN: Doculite - Use SQLite as a Document Database
        
       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
        
       Author : thenorthbay
       Score  : 32 points
       Date   : 2023-08-07 20:14 UTC (2 hours ago)
        
 (HTM) web link (www.npmjs.com)
 (TXT) w3m dump (www.npmjs.com)
        
       | [deleted]
        
       | tracker1 wrote:
       | 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.
        
         | thenorthbay wrote:
         | 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
        
           | simonw wrote:
           | Found where you're using those: https://github.com/thenorthba
           | y/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.
        
       | cyanydeez wrote:
       | 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
        
       | bastawhiz wrote:
       | 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).
        
         | DANmode wrote:
         | I'd like to enable the same in my startup.
         | 
         | What are you using for this today?
        
         | thenorthbay wrote:
         | 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?
        
       ___________________________________________________________________
       (page generated 2023-08-07 23:00 UTC)