SQLite as a Document Database (2020)

 (dgl.cx)

91 points | by lioeters 4 days ago

6 comments

  • stanac 2 hours ago
    I am using SQLite as document db for a side project for years now. Made a custom repository base class that can also store blobs in separate columns, so this type of data is not part of the json document. Today there is also jsonb [1], as far as I remember all functions work the same for json and jsonb.

    Also the repo class stores write and delete timestamps as separate columns so I can have CDC. CDC is used for building cached view models and is pushed to object storage every 5 minutes for backup as NDJSON. Another process on home server is restoring the db every couple of minutes for second backup and ready to use DB in case it's needed.

    I know there are things like Litestream, I wanted something in process and something that can send alerts on failed backups.

    [1] https://sqlite.org/jsonb.html

  • tolerance 46 minutes ago
    Is this not similar to what Simon Willison has written about before?

    https://sqlite-tutorial-pycon-2023.readthedocs.io/en/latest/...

    https://simonwillison.net/2021/Jul/28/baked-data/

    [-]
    • simlevesque 41 minutes ago
      Check the dates, the "before" part is inaccurate.
  • delduca 2 hours ago
    In my personal project (a game) I use an indexed key + a JSONB to store the save state, which comes from Lua.

    So I can have cassette.propxyz = {1, 2, “abc”, true}

    And

    local anotherprop = cassette.leprop

    [-]
    • nonethewiser 1 hour ago
      Are you doing this because you also use SQLite for other relational data?
      [-]
      • delduca 1 hour ago
        Possibly yes, otherwise I could use another simple solution
  • smalltorch 1 hour ago
    Is this new or something? Works amazing as a document backend.
    [-]
  • antonvs 1 hour ago
    > (Aside: The hard bit may be getting a new enough SQLite, at the time of writing Homebrew on macOS has it, else you likely need to use an unstable source like nixpkgs-unstable.)

    Or just download the source and build it! (Gasp!)

    [-]
  • mayankbpatel 23 minutes ago
    Why don’t you use MongoDB? MongoDB is web scale.

    https://youtu.be/b2F-DItXtZs?is=HlayyJ_DPb8NzbS4

    (lol! couldn’t resist)

    [-]
    • alterom 0 minutes ago
      Wait, are all of those shared online?

      That was the part I really missed from my Google days.

      That, insane achievement badges, and terrible-ideas-discuss (if anyone at Google is reading this, I have one word for you: dirigibles). It was like /r/NonCredibleDefense but for Google.