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