I am obsessed with the idea of per tenant databases. But I am afraid of migrations. Has anyone tried that?
SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
21–30 of 87 posts
Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
#22I am obsessed with the idea of per tenant databases. But I am afraid of migrations. Has anyone tried that?
We do it on Postgres with schemas but we have a very fixed amount of tenants so it works.
I don't think it scales when you have unbounded amounts of tenants.
Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
#23Earlier quoted context omitted.
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…
> 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. Edit: unless the table is empty.
How do you migrate in place data that doesn’t convert between types while maintaining a strict condition like NOT NULL?
This is again a scenario I’ve never run into 20ish years of SQL.
Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
#24I'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…
> 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 SQ…
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.
Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
#25Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
#26Earlier quoted context omitted.
DBeaver can also open/manage SQLite databases. I use it daily (although on a tiny page).
Ah, my point was more on how to connect to the live database on some remote server.
DBeaver can connect to remote sqlite databases through an SSH tunnel:
https://dbeaver.com/docs/dbeaver/Database-driver-SQLite/#rem...
Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
#27I'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…
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.
Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
#28I 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.
Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
#29I am obsessed with the idea of per tenant databases. But I am afraid of migrations. Has anyone tried that?
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!
Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
#30Earlier quoted context omitted.
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…
> 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. Edit: unless the table is empty.
Wrong, you can change NOT NULL since 3.53: