SQLite as a Document Database (2020)
1–10 of 63 posts
Re: SQLite as a Document Database (2020)
#2I 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.
Re: SQLite as a Document Database (2020)
#3In 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
Re: SQLite as a Document Database (2020)
#4In 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
Are you doing this because you also use SQLite for other relational data?
Re: SQLite as a Document Database (2020)
#5Is this new or something? Works amazing as a document backend.
Re: SQLite as a Document Database (2020)
#6In 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
Are you doing this because you also use SQLite for other relational data?
Possibly yes, otherwise I could use another simple solution
Re: SQLite as a Document Database (2020)
#7Is this new or something? Works amazing as a document backend.
Almost 6 years old. (2020).
Re: SQLite as a Document Database (2020)
#8> (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!)
Re: SQLite as a Document Database (2020)
#9> (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!)
Even easier: click the download link on the website. https://sqlite.org/download.html
Re: SQLite as a Document Database (2020)
#10Is this not similar to what Simon Willison has written about before?
https://sqlite-tutorial-pycon-2023.readthedocs.io/en/latest/...