Cross-Database Queries in SQLite

Marketing hated it, because it meant collecting user data took conscious effort which meant they had to ask the dev team, which meant requests were often turned down when hard questions were asked about whether it was justified to collate personal information.

That was a feature, from my perspective.

We actually took most user demographics data entirely offline. Data that was only intended to create aggregates anonymised profiles of our users were kept encrypted in bank box when not being analysed. Any analysis on it was done on an airgapped machine, and only the anonymised reports taken out.

The only central database that was online was one that kept a mapping of whether or not a given user name was available.

Then each storage shard kept track of which users were on which backend, and the account data that had to be online for each user (primarily their actual mail since we were a mail provider, along with settings etc. for their mailbox) were kept on a per user basis.

It worked well, but you need to have buyin from the top for this approach, as there will be constant pressure for easing access to more and more user data.

That's a pretty bold claim. Judging by the number of applications (including "real" databases) which fail to get this right, I'd have to say it's probably harder than you think.

Besides, SQLite has done that work already, and has done so very thoroughly. I would definitely trust SQLite over something home-grown.

- crashes/power failures

- multiple processes opening the same file

- transactional updates: it rolls back if you throw an exception in the middle of writing the update. This seems like a common failure mode while under development

- scaling to huge size: many gigabytes are no problem. These get slow with a flatfile

- manual surgery on data: it comes with handy command line tools for manipulating its databases

- upgrading format: you can check a version number when opening the file and do "alter table add column" for any new features.

With SQLite you can have multiple processes access the same database and they can make changes to it that are immediately seen by the other processes. The nature of SQLite locks means this doesn't scale well if you have lots of processes all wanting to make heavy updates, but so long as you're not in that situation it works very well.

https://sqlite.org/pragma.html#pragma_journal_mode

This lets sqlite read w/o a write lock (among other things)

Since it stores the log to an adjacent file you have to make sure the process can write to the whole directory containing the db.

With this approach, services are usually separated (e.g. microservices or a monolith with several db clients) and use several sqlite db files. For example, users.db, receipts.db, posts.db etc.

For data which is frequently accessed, it really helps to consider caching (e.g. HTTP).

I realise of course this won't scale infinitely so I usually make use of ORMs (e.g. GORM for Go/Gin or Diesel for Rust/Actix) just in case the SQL engine would need to change (so I don't have to rewrite queries etc). I haven't had the need to do so yet.

But in many cases, yeah, simple file use is good enough, as long as important stuff is backed up somewhere / a human can easily re-upload and repair anything needed. It's a <0.1% optimization, it really only saves you noticeable effort when you're doing like millions of those operations per day.

- The parsing code for SQLite is exactly the same C code in every language, making it impossible to make mistakes in reading/writing.

- SQLite likely has stronger transactional writing ability than what your application created.

- Any SQL GUI will allow you to inspect and iterate on queries during development/production.

- SQL is a good first step to writing “join/filtering” queries and is backed by C code/indexes so should be fast for simple stuff.

Of course if you do not need any of those and are able to spend the extra time to write out your “queries” in pandas/Python that will work well too - just another way of doing the same thing.

Personally I like SQL as a first stop for prototyping, with the hope I do not need to use other tools. Joins, transactions and using the disk for state are all ”good enough” starting points, and I can take those techniques to any language I use.