[starttoaster]

For me, the biggest trade offs for sqlite are just that you need to think about how you're going to store and backup the database a lot more. Specifically in container orchestration environments like kubernetes, I think sqlite presents a couple of challenges. With MySQL, you can set up a replicated database server instance outside of the cluster that you just connect to over the network, and you can use standard MySQL tools like mysqldump to back them up. Kubernetes isn't ideal for stateful workloads so that tends to be one of the more sane solutions there.

With SQlite you need to set up a persistent volume to keep the database around between container restarts, and you need to think of a clever way to then back up that sqlite database to somewhere like S3, likely using an sqlite3 command with a VACUUM statement and then an `aws s3 cp` command, which requires AWS credentials. Overall, a lot of additional work and privileges on the application container, at least in container orchestration environments. In lieu of all that, maybe you trust your persistent volume provisioner enough to try to do an online snapshot, but that always sketches me out / I don't trust the backup enough to rely on it.

Of course you can use a public cloud sqlite service like Cloudflare D1, but I haven't used that solution enough to say if it would be flexible enough to work with, say, an on-prem application server, or if it only works with Cloudflare workers. I'm sure I could find that out in the documentation but I've exhausted my mental stamina for the day with leafing through documentation pages.

[xiaomai]

Backing up sqlite databases is straightforward. `.backup` is a command that you use in sqlite for this purpose. Since you already have a volume for the database, you can backup to that same volume (if offloading to S3/etc is too much work).

[starttoaster]

> Since you already have a volume for the database, you can backup to that same volume (if offloading to S3/etc is too much work).

I think I just accidentally didn't see this part of your reply or something last night. But backing up a database to the same place the primary one is stored is decidedly _not_ a real backup solution. Nobody should believe that offers them any of the same forms of protection that a real backup would give them. At the absolute best, you're protected from your database getting malformed by your application server. But if you lose that volume for any reason, your backups are just gone. Imagine explaining to your boss in such a scenario that the solution that was come up with was that the backups are kept on the same linux partition as the primary running database. They would fire me. I would fire me.

[starttoaster]

Yeah, my point was pretty specific to container environments, that using sqlite forces you to add a bunch of sqlite handling logic to your application. Whereas with MySQL (and other similar RDBMS’), you can have your application just worry about its own logic, and handle MySQL backups completely separately from it.

[yencabulator]

Note that sqlite `.backup` does not back up PRAGMA values. Some people use `PRAGMA user_version` for schema versioning (not a good idea, because of this trap).