We use SQLite as a library and we don't want to migrate to a server situation.
So it seems using the month-to-month approach you outline is reasonable and not outrageous. Good to know. Thanks for the tip on schema modifications which get automatically upgraded on read. This is a great feature. I think I might try that out for my application. Does your code also map object hierarchies in json? What general format do you use? Each object type gets stored in a separate table? On Mon, Jul 13, 2015 at 7:03 PM, Roger Binns <rogerb at rogerbinns.com> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On 07/13/2015 05:43 PM, Hayden Livingston wrote: >> Is there a concept of a schema-less JSON SQLite DB? > > What exactly is it you want? A schema-less database? A JSON > database? Using SQLite to store JSON? (It is unclear if you mean > something like SQLite, or SQLite itself). Something small and light? > A library not a server? > > Be aware that you can dynamically update the SQLite schema, and that > it takes no time to do so. Another database I won't name locks the > database and rewrites each record. This can take hours or even days. > SQLite just updates the schema table, and fills in defaults for > records written before the schema change, on reads. I wrote some code > the other day that takes JSON and does the schema manipulation, > although it is annoying. (It would be really great if SQLite > supported arrays like Postgres does.) > > You can also store arbitrary JSON in a column. You won't be able to > query it effectively, but you can duplicate values into columns you > can do queries and indexes on. > >> My reason is simple: versioning. We have lot of business metrics >> that get updated let's say once a month, and we need to be agile to >> get them. Right now, we just put the version in the SQLite file, >> and then make sure no queries cross the boundaries. > > You can also use multiple databases, and attach them. For example if > you operate on a month by month basis, then you can put each month's > data in a separate SQLite file, then attach last month's as > 'lastmonth', and use in queries (eg SELECT blah blah WHERE bar.foo > > lastmonth.bar.foo) > >> Do others have experiences and requirements similar to this? > > All the time. I use SQLite when I don't need networked access, a > running database server, and need less things that can go wrong. > Mapping JSON into this is painful but possible. > > When I can use a database server, I prefer Mongodb as it is very good > at arbitrary JSON in, the same arbitrary JSON back out. It is > especially pleasant that the query syntax has the same shape as the > underlying JSON data. Also JSON maps trivially to Python which I use > the most. (Note however that Mongodb does have some issues, but so > does much other software out there. Production use does require > effort & planning as does other software.) > > In the longer term Postgres is getting increasingly better JSON > support. Hopefully it will give Mongodb a run for its money soon. > > Roger > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1 > > iEYEARECAAYFAlWkbfsACgkQmOOfHg372QTFiwCgzzf5fEzfJdWn84bk0e1fusGE > qeAAnAhR+sHb39Gsha2Owq3SXsdZVRex > =9jcT > -----END PGP SIGNATURE----- > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
