I love SQLite, and I really want to run it in production, but my clients expect minimal data loss and downtime when one of my servers goes down.
The answer to that being running it on top of LiteFS or LiteStream seems like starts to make the setup a lot less simple and lot less battle tested, which kind of starts to negate the advantages over just running Postgres.
As someone really tempted to use SQLite in production, the one thing I keep bumping against is how to have a nice GUI to interact with the running database. With our current prod databases, I can connect dbeaver and the like to them and nicely browse the data, query, or even do the occasional fix. Seems like this would be much more of a head scratcher if the database is just a file on the same VPS the app runs on.
If your app is a web app you could create a rudimental page with SQL input and table output and authorize only admins. It's risky, if someone gets access to admin account they can drop everything. I have something like this for logs, app reads log files directly and they are accessible only via private domain (tailscale), if I connect to public domain I can't access logs. Additionally you could enable only SELECT statements via web.
Another downside is that it will take time to make it look nice.
I'm fairly confident this is AI generated, but it makes me think regardless: Whenever I see these kind of articles, I'm left wondering if they've actually used SQLite in production because I always see points about how to optimize performance, like using the WAL, but never about annoyances/issues you'd run into before even needing to worry about that. I guess it's the zeitgeist to use it in a production setting, and I think it's great that it's getting hyped because it truly is a capable database, but after trying myself I think I'd never reach for it in production because it lacks a lot of power that a database like Postgres has, and some of that power is actually relevant to a real production setting:
- Column definitions aren't able to be changed with something like `alter column` after creation. To change a column definition you have to manually update the underlying schema using the `writable_schema` pragma. If you mess this up you can be left with a corrupt database.
- Column types are pretty limited. This isn't too much of an issue in practice since you can handle this somewhat in application code, but it can still be a bit annoying at times.
- You have limited options for dealing with schema migrations. You basically either copy the migrations to the server and run it there (manually or with something like Ansible), or you run the migrations in your application on startup. Ideally you'd perform your schema migrations separately from your application, and having to somehow copy/get the migrations to your server to then run the migration is a bit clunky.
All 3 of these are handled in a more powerful (and not local-only) database, and so I don't get why someone would choose SQLite except for prototyping (or places like the browser or phone apps) where performance concerns aren't really relevant.
I would only recommend sqlite if you know what you are doing (and/or prepared to learn it inside out). It's more of a build your own database primitive (often you'll have multiple sqlite databases for different things). Which can be incredibly rewarding and deliver amazing performance outcomes, simple ops, etc.
I see the migration argument come up a lot. But, in practice with sqlite you'll be using projections where you have a source of truth database (event log) and project off it into disposable/expendable sqlite databases. So schema changes are often just delete and rebuild the projection.
It's about tradeoff, sometimes those limitations doesn't really matter that much, sometimes they are. The point is not to settle on a superior option so we never need to think the again but to understand the difference and choose accordingly.
Or at least that's how I view it. Whenever I think about using SQLite, I make sure I read these documents to see if I am fine with the limitations.
I’ve never once had to change a column definition. Sure in theory that option is available. Better option is to just add a new column with the correct definition then copy over existing data in the old column.
I don’t think that’s really a positive or negative.
And the point about migrations ideally being separate is really just your own opinion. I prefer having the database definition in the same source tree as the application, ideally just a .sql file in the project.
> Better option is to just add a new column with the correct definition
After that you won't be able to change column to NOT NULL. You would need migration to create new table with not null column, copy everything, drop old table and rename the new one.
> Ideally you'd perform your schema migrations separately from your application
Why is that the ideal? With SQLite your database is 1:1 connected to your application (meaning there is no other application using that database), it doesn't make sense to move the app to a new version but not the database or vice versa. Running migrations on startup of the app is ideal.
Migrations are a bit more difficult to write for SQLite than they need to be (DROP column only being added recently...), though. I usually iterate a few times to get the column definitions just right so that I don't have to change them later.
As you say column types are limited (and enforcement lax) but in practice it's a non-issue because you convert the data to application-specific types when reading from db (and enforce by writing only right data types) anyway.
> Why is that the ideal? With SQLite your database is 1:1 connected to your application
I don't think this solves the issue though. To be fair, I was a bit loose with my wording and the principle is actually "don't make backwards breaking changes to your database schema" rather than "do your migrations separately", but if you do them separately it is a good way to enforce it. The issue you want to prevent is your application having bugs/issues in production necessitating a rollback, and your now rolled back application doing things that are incompatible with the current database version (or in a concurrent setting, that some applications may not be updated).
There's still the issue where you're copying over all of the migrations to your server too when you do it in the application, which is in my opinion something you are ideally able to avoid, but it's not a problem in practice until you have 1000s of migrations.
I don't see how having migrations out of the app enforces that.
For the rare case when you do rollback the safest thing to do is stop the app, downgrade the db (by running some sql if necessary) and app and rerun it. Not that different in postgres no?
Postgres has transactional DDL: you can be applying migrations in one transaction while serving live traffic from the old schema in another. By tying the schema changes directly to the application deployment it becomes harder to apply a big migration without downtime. You can't apply the migration and then cut over traffic to new app instances once the migration is complete.
> To change a column definition you have to manually update the underlying schema using the `writable_schema` pragma. If you mess this up you can be left with a corrupt database.
No you don’t [0]. It is less convenient than being able to directly alter columns, but you do not need to mess around with writable schemas or risk corruption.
I’m a bit confused. That’s not a column definition change, because the original column is the same, you’re doing a data migration. That is one way you would solve this class of problems in SQLite, but it’s a bit annoying compared to a something like `alter column`.
I previously had a golang based crawler doing 5 concurrent process writing into the same sqlite wal, it caused the sqlite to get corrupted, and i finally decided to move to postgres instead.
"If the database size is smaller than the mmap_size, the entire database is mapped into memory, turning disk reads into simple pointer arithmetic." hah so that's how it works?
Personally, I think the better way to tackle sqlite_busy is to have a single writer managed at the application level. That effectively eliminates sqlite_busy in the context of a single process.
> To ensure write operations don't suffer from disk synchronization bottlenecks, pair WAL mode with the following pragma
Only do this if you are prepared to sacrifice durability (i.e can afford to lose transactions).
It's pretty awful, generally best avoided unless you have a specific reason for doing so (e.g. encrypting the full SQLite DB per customer). It also introduces you to some pretty bad risks (what if there is a bug in a migration which only affects certain tenants?).
That being said it can be done and it's pretty normal for mobile apps, desktop apps etc. You just have to make sure the migrations are run when the tenant connects/unlocks/runs the app - and make sure that you minimise the risk of it going wrong!
The answer to that being running it on top of LiteFS or LiteStream seems like starts to make the setup a lot less simple and lot less battle tested, which kind of starts to negate the advantages over just running Postgres.
Another downside is that it will take time to make it look nice.
https://dbeaver.com/docs/dbeaver/Database-driver-SQLite/#rem...
- Column definitions aren't able to be changed with something like `alter column` after creation. To change a column definition you have to manually update the underlying schema using the `writable_schema` pragma. If you mess this up you can be left with a corrupt database.
- Column types are pretty limited. This isn't too much of an issue in practice since you can handle this somewhat in application code, but it can still be a bit annoying at times.
- You have limited options for dealing with schema migrations. You basically either copy the migrations to the server and run it there (manually or with something like Ansible), or you run the migrations in your application on startup. Ideally you'd perform your schema migrations separately from your application, and having to somehow copy/get the migrations to your server to then run the migration is a bit clunky.
All 3 of these are handled in a more powerful (and not local-only) database, and so I don't get why someone would choose SQLite except for prototyping (or places like the browser or phone apps) where performance concerns aren't really relevant.
I see the migration argument come up a lot. But, in practice with sqlite you'll be using projections where you have a source of truth database (event log) and project off it into disposable/expendable sqlite databases. So schema changes are often just delete and rebuild the projection.
Or at least that's how I view it. Whenever I think about using SQLite, I make sure I read these documents to see if I am fine with the limitations.
https://sqlite.org/whentouse.html
https://sqlite.org/quirks.html
I don’t think that’s really a positive or negative.
And the point about migrations ideally being separate is really just your own opinion. I prefer having the database definition in the same source tree as the application, ideally just a .sql file in the project.
After that you won't be able to change column to NOT NULL. You would need migration to create new table with not null column, copy everything, drop old table and rename the new one.
Edit: unless the table is empty.
https://sqlite.org/releaselog/3_53_3.html
This is again a scenario I’ve never run into 20ish years of SQL.
Why is that the ideal? With SQLite your database is 1:1 connected to your application (meaning there is no other application using that database), it doesn't make sense to move the app to a new version but not the database or vice versa. Running migrations on startup of the app is ideal.
Migrations are a bit more difficult to write for SQLite than they need to be (DROP column only being added recently...), though. I usually iterate a few times to get the column definitions just right so that I don't have to change them later.
As you say column types are limited (and enforcement lax) but in practice it's a non-issue because you convert the data to application-specific types when reading from db (and enforce by writing only right data types) anyway.
I don't think this solves the issue though. To be fair, I was a bit loose with my wording and the principle is actually "don't make backwards breaking changes to your database schema" rather than "do your migrations separately", but if you do them separately it is a good way to enforce it. The issue you want to prevent is your application having bugs/issues in production necessitating a rollback, and your now rolled back application doing things that are incompatible with the current database version (or in a concurrent setting, that some applications may not be updated).
There's still the issue where you're copying over all of the migrations to your server too when you do it in the application, which is in my opinion something you are ideally able to avoid, but it's not a problem in practice until you have 1000s of migrations.
For the rare case when you do rollback the safest thing to do is stop the app, downgrade the db (by running some sql if necessary) and app and rerun it. Not that different in postgres no?
No you don’t [0]. It is less convenient than being able to directly alter columns, but you do not need to mess around with writable schemas or risk corruption.
[0]: https://www.sqlite.org/lang_altertable.html#otheralter
If you're saying "do this, get that", you should be able explain how to measure and reproduce that result.
The answer to why someone might choose SQLite in production could be latency but if it is then prove it's worth the trade-offs.
> To ensure write operations don't suffer from disk synchronization bottlenecks, pair WAL mode with the following pragma
Only do this if you are prepared to sacrifice durability (i.e can afford to lose transactions).
That being said it can be done and it's pretty normal for mobile apps, desktop apps etc. You just have to make sure the migrations are run when the tenant connects/unlocks/runs the app - and make sure that you minimise the risk of it going wrong!
I don't think it scales when you have unbounded amounts of tenants.