> “But nobody writes production applications with SQLite, right?"
We've been doing it for 5 years now. Basic tricks we employ are:
Use PRAGMA user_version for purposes of managing automatic migrations, a. la. Entity Framework. This means you can actually do one better than Microsoft's approach, because you don't need a special unicorn table to store migration info. A simple integer compared with your latest integer and executing SQL in the range is all it takes.
Use PRAGMA synchronous=NORMAL alongside PRAGMA journal_mode=WAL for maximum throughput while supporting most reasonable IT recovery concerns. If you are running your SQLite application on a VM somewhere and have RTO which is satisfied by periodic hot snapshots (which WAL is quite friendly to), this is a more than ideal way to manage recovery of all business data while also giving good throughput to writers. If you are more paranoid than we are, then you can do FULL synchronous for a moderate performance penalty. This would be more for situations where your RTO requires the exact state of the system be recoverable the moment it lost power. We can afford to lose the last few minutes of work without anyone getting yelled at. Some modern virtualization technologies do help a lot in this regard. Running bare metal you need to be a little more careful.
For development & troubleshooting, being able to copy a .db file (even while its in use) is tremendously powerful. I can easily patch up a QA database I mangled with a bad migrator in 5 minutes by stopping the service, pulling the .db local, editing, and pushing it back up. We can also ask our customers to zip up their entire db folder so we can troubleshoot the entire system state.
Being able to use SQLite as our exclusive data store also meant that our software delivery process could be trivialized. We use zero external hosts, even localhost, for our application to be installed or started. We don't even require a runtime exist on the base operating system. Unzip our latest release build to a blank Win2019 server, sc.exe the binary path, net start the service, and it just works. Anyone can deploy our software because it's literally that simple. We didn't even bother to write a script because its a bigger pain in the ass to set powershell execution mode.
So, its not just about the core data storage, but also about the higher-order implications of choosing to use a database solution that can be wholly embedded within your application. Because of decisions like these, we don't have to screw around with things like Docker or Kubernetes.