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.
If I create a view of a table containing JSON data (or any other kind of data), I can create columns from calculations on existing fields.
I'm guessing the advantage of generated columns is that they are real columns, not computed columns like in a view. This means that if an insert doesn't work with the new generated column an error will be generated? Is that a correct understanding ?
Is this perhaps the main advantage of this feature ?
The data creation and storage options, always computed or stored on write of dependant columns seems like a possible advantage too.
Main practical difference is that you can put index on generated column, and on view you cannot, SQLite has no materialized views. Constraint part you understood right, NOT NULL on generated column fails at insert, and note that VIRTUAL costs nothing on disk but can still be indexed, so STORED is mostly for when expression itself is expensive.
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
I'm guessing the advantage of generated columns is that they are real columns, not computed columns like in a view. This means that if an insert doesn't work with the new generated column an error will be generated? Is that a correct understanding ?
Is this perhaps the main advantage of this feature ?
The data creation and storage options, always computed or stored on write of dependant columns seems like a possible advantage too.