Unfortunately written by an AI - that completely takes the wind out of the content and makes me not even want to read any further. The distinction between “production” and “non-production” is also questionable. For an evaluation, I recommend the following original articles (certainly not AI-generated):
The blog lists ways to optimize sqlite for production, for those who have already made that choice. But these comment links are about choosing sqlite vs others and differences, which is completely unrelated and unnecessary for those who have already made that choice.
I'm seeing a worrying trend on HN. Nearly for all articles, there is one unquantified, unproven comment at the top saying it's 100% AI — no proof, just baseless emotion of what fits the commenter's writing style. This is the new witch hunt, or virtue trolling.
I read the original article and to be honest, this off topic comment definitely looks more like an AI troll bot wrote it than the article. The troll bot is instructed to do a google search and put 3 source links — no matter how irrelevant they are.
So what is it now — one emotion vs another? If an article has sections and blocks, it's AI. What about infinite poor humans like me who have been writing like this since childhood?
Since LLMs were trained on high quality blog posts, we can no longer have posts that fit the pre-AI definition of high quality? The content must be dumbed down with typis and scatter unconventional words, to prove AI trollers that it's not AI? Or a timelapse video of typing the article?
Instead how about: if you don't want to read the article because it doesn't fit your personal style expectation — just don't bother commenting?
I hope HN would ban AI trolling comments, and discuss substance.
I'm more than aware that my comment won't move the needle on the AI debate a slight bit, but I do think that the readership deserves some honesty and transparency there. No, not a single LLM ever has "deployed SQLite in Production", had to be on duty during the weekend, had to pop open a terminal to a misbehaving service while on family vacation, nor meaningfully suffer the anguish and stress to hotfix a botched or misconfigured deployment. Only humans have. What makes or breaks a software product, costs you your holiday, and perhaps even your job, probably won't be found in some generic piece of common knowledge rehashed and repackaged with some stylistic tweaks. It's the fine details, the one-off things, those weird edge-cases and compounded factors that in hindsights were obvious but had to be learned the hard way, and hopefully, shared by benevolent contributors.
That's the whole reason why many here put the effort to read/post articles and argue over comments: so there's something non-obvious to be learned. LLMs are effectively "knowledge averagers". They will put together a pretty article that's not worth reading, except maybe if the problem space is completely new to you. And that's the kind of disclaimer I would hope to see ahead of anything LLM-produced, and particularly on this website.
I'm on-call all the time, and the first thing I've taught myself is to not glorify it like some superhero saves the day worship. I'm both proud and (slightly) ashamed each time I handle an incident. It's not a virtue.
How many people who were writing blogs or creating open source tools before signed an on-call oath or declaration? Does that mean they're all wrong or unqualified?
On call avoidance should be rewarded more than on call superheroism. People managing software fail to realize it. Avoidance is stashing enough operational bullet points, mental checklists and manpages.
Everything is generic — because your use case and your operating environment is ONLY yours. Postgres has hundreds of parameters. Nothing is specific or made to satisfy you. If it doesn't, then you don't call the parameter as generic. You choose what to apply for your environment and expectation.
What you're saying is both a wrong reward cycle and it was also not necessary or practical before AI.
except 99% of AI—written content is spam. If the article was an ad for some company and also made no sense, would you hate on the guy who commented "this is an ad"?
Because that's not what happened? What? Neither smartmic's root-level comment or rdsubhas's response mention it being an ad. I was criticising adhamsalama's dismissal of rdsubhas's comment as AI, who also said nothing about it being an ad. I'm so baffled. Can you not see the structure or the content of this thread?
I think this is deliberate to make a point. Even an AI doesn't use that many em dashes. GP wants you to question it as a "haha see? I'm human yet you called me an AI just like I thought!"
Hope this becomes a cultural norm outside of HN. You can have AI written text that had some actual human effort put into it [1], but sloppy AI written content should only be meant for anohter AI consumers and not humans.
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.
A handy trick for column types is check constraints.
You can define constraints on a column that ensure it is text that's valid JSON for example:
CREATE TABLE documents (
id INTEGER PRIMARY KEY,
data TEXT NOT NULL
CHECK (
json_valid(data)
json_type(data) = 'object'
)
);
Or to ensure specific keys:
CREATE TABLE documents (
id INTEGER PRIMARY KEY,
data TEXT NOT NULL CHECK (
json_valid(data)
AND json_type(data) = 'object'
AND json_type(data, '$.name') = 'text'
AND json_type(data, '$.age') = 'integer'
)
);
You can even use this for things like enforcing a valid YYYY-MM-DD date, though that gets a bit convoluted:
CREATE TABLE events (
id INTEGER PRIMARY KEY,
occurred_on TEXT NOT NULL CHECK (
length(occurred_on) = 10
AND occurred_on GLOB
'[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
AND date(occurred_on, '+0 days') = occurred_on
)
);
My sqlite-utils CLI tool and Python library offers solutions to both the alter table limitations and the need for schema migrations.
For alter table it offers a "transform" command which implements the pattern of creating a new table with your desired scheme, copying data to it from the old table, then renaming the tables (all in a transaction): https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...
sqlite-utils transform fixtures.db roadside_attractions \
--rename pk id \
--default name Untitled \
--column-order id \
--column-order longitude \
--column-order latitude \
--drop address
> 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.
SQLite has transactional DDL: you can start a transaction, do a bunch of create tables, copy data from old tables into the new tables. If an error occurs during this and a rollback occurs, everything will be just like it was before the transaction started. If a commit occurs, the migration succeeds and everyone sees the new schema on their next transaction.
In rollback mode, only the migration thread can be active because it's a write transaction, but I'm guessing in WAL mode, readers can continue to read during the migration, as with any other write transaction. I don't use WAL mode much because for my application (HashBackup), I don't need db concurrency.
If you can accept downtime and you really don't need db concurrency, then that's great and SQLite is probably a good fit for you. There are many applications for which that isn't the case.
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.
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.
ALTER TABLE is very limited in sqlite3, though they've been improving it (search the page for " 3." to find the version-specific changes). Any significant schema change is usually simpler to create a new table and copy the data.
FWIW, in a lot of cases you aren't expecting even thousands of simultaneous users, so SQLite is a perfectly valid option. Not to mention services like Cloudflare D2 and Turso which build on SQLite as a core with different features for scale/concurrency.
A lot of people manage to run several containerized applications on a single VPS behind a reverse proxy for personal or small groups. Managing a full rdbms takes work supporting multiple applications, or spinning up multiple instances per app in said containerized flows takes up excess resources, where SQLite would do the job just fine.
Not everything is going to be running 5+ nines of operation with distributed workloads. Plenty of real things run on a decent server with a good enough backup system in place.
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.
Good links. To make it easier, here are the first paragraphs;
"SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.
Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasize scalability, concurrency, centralization, and control. SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.
SQLite does not compete with client/server databases. SQLite competes with fopen()."
> 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.
Where I part ways a little with the recommendation is in the busy_timeout + BEGIN IMMEDIATE suggestion. On an embedded system, that's not really sufficient; I maintain a caching service that is used by thousands of clients. It ingests data over MQTT and has a web interface for queries. In my design, there is a MQTT thread and pruning thread. The MQTT ingestion thread and the pruning thread can both end up "contending" for the write lock, and increasing the busy_timeout only makes them wait longer before noticing that contention and moving on to the next task. What we ended up needing to do was add an application-level lock that only allows a single writer transaction to be in flight at a time; BEGIN IMMEDIATE is nice for preventing other readers from blocking, but it doesn't help much with other writers.
Meanwhile, you might be surprised how often “single-tenant edge deployment” comes up in embedded contexts; a Pi with an SD card is a common configuration for an embedded database, and it's right at the intersection of these problems.
One of the lessons I picked up from my very brief foray into the world of realtime software is the idea of amortization. If you have one activity that is possibly time-unbounded, then the solution is to trade away a bit of throughput for certainty by taxing the frequent task with making incremental progress on the cleanup. The first real-time garbage collectors worked this way. Every allocation had to do a bit of sweeping work, which generally keeps all but the largest allocations from every being in danger of hitting a pause (in real time systems large allocations are typically done at bootstrapping time to avoid this issue entirely)
> Meanwhile, you might be surprised how often “single-tenant edge deployment” comes up in embedded contexts; a Pi with an SD card is a common configuration for an embedded database, and it's right at the intersection of these problems.
Probably shouldn't be. One of the more high profile use cases for SQLite is per-customer sharding. Particularly with services where at any given time a small number of customers are generating the majority of the traffic. The data for customers that you haven't seen logged in for weeks tends to have a logarithmic effect on the query time for active customers when you use a single shared database for all of the data. Unloading that data reduces the cost of interactive workloads handily, even though the cost of backups may still be either the same or still have a logarithmic cost (eg, rsync detects deltas).
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.
> In NORMAL mode, the database engine syncs to disk only at critical moments (e.g., during checkpoints) rather than at every single transaction commit. In WAL mode, this is completely safe from database corruption; even if the server crashes, only the uncommitted transactions in the WAL are lost, but the database integrity remains intact.
No, this is not safe.
You can lose the latest committed transaction with this pragma.
"Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not."
Safe in this context means not corrupting the database. You might lose data, but the database will live on happily as if the missing data was never written to it. You don’t get half-committed transactions, and the database isn’t in a weird state.
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.
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).
I have some experience with different databases and system designs. In one of my last projects I used SQLite. However, I used only one dedicated process for writing and other processes for read only. This might help.
The era of AI agents need serverless SQL. Its very important to have cost effective tech to run the internet.
hence we ended up writing our own WAL for a Postgres compatible engine, and what surprised me was how much of the work is in the fsync ordering rather than the log format.
Relaxing that is a real choice to lose the last few transactions on a crash. Fine for some workloads but it should be a decision someone made on purpose.
It works well enough if you deploy a separate version of the app for each tenant, and upgrade each tenant's app + db in lockstep.
It's a bit of a 1990s setup (as the sibling comment says, it's more or less the same model as a widely-deployed desktop app), but it does scale and can be useful in situations where strict isolation between tenants is beneficial.
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!
Isn't this exactly what every mobile app does when the db is on a phone, and every app where the db is kept on the local machine? I do it with HashBackup and have done 35 db migrations over 17 years without much trouble. There have been 1 or 2 migrations that had a bug, but you fix that by doing another migration.
"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?
Unfortunately written by an AI - that completely takes the wind out of the content and makes me not even want to read any further. The distinction between “production” and “non-production” is also questionable. For an evaluation, I recommend the following original articles (certainly not AI-generated):
- https://sqlite.org/whentouse.html
- https://sqlite.org/different.html
- https://sqlite.org/quirks.html
I have to disagree with this as off topic.
The blog lists ways to optimize sqlite for production, for those who have already made that choice. But these comment links are about choosing sqlite vs others and differences, which is completely unrelated and unnecessary for those who have already made that choice.
I'm seeing a worrying trend on HN. Nearly for all articles, there is one unquantified, unproven comment at the top saying it's 100% AI — no proof, just baseless emotion of what fits the commenter's writing style. This is the new witch hunt, or virtue trolling.
I read the original article and to be honest, this off topic comment definitely looks more like an AI troll bot wrote it than the article. The troll bot is instructed to do a google search and put 3 source links — no matter how irrelevant they are.
So what is it now — one emotion vs another? If an article has sections and blocks, it's AI. What about infinite poor humans like me who have been writing like this since childhood?
Since LLMs were trained on high quality blog posts, we can no longer have posts that fit the pre-AI definition of high quality? The content must be dumbed down with typis and scatter unconventional words, to prove AI trollers that it's not AI? Or a timelapse video of typing the article?
Instead how about: if you don't want to read the article because it doesn't fit your personal style expectation — just don't bother commenting?
I hope HN would ban AI trolling comments, and discuss substance.
I'm more than aware that my comment won't move the needle on the AI debate a slight bit, but I do think that the readership deserves some honesty and transparency there. No, not a single LLM ever has "deployed SQLite in Production", had to be on duty during the weekend, had to pop open a terminal to a misbehaving service while on family vacation, nor meaningfully suffer the anguish and stress to hotfix a botched or misconfigured deployment. Only humans have. What makes or breaks a software product, costs you your holiday, and perhaps even your job, probably won't be found in some generic piece of common knowledge rehashed and repackaged with some stylistic tweaks. It's the fine details, the one-off things, those weird edge-cases and compounded factors that in hindsights were obvious but had to be learned the hard way, and hopefully, shared by benevolent contributors.
That's the whole reason why many here put the effort to read/post articles and argue over comments: so there's something non-obvious to be learned. LLMs are effectively "knowledge averagers". They will put together a pretty article that's not worth reading, except maybe if the problem space is completely new to you. And that's the kind of disclaimer I would hope to see ahead of anything LLM-produced, and particularly on this website.
I'm on-call all the time, and the first thing I've taught myself is to not glorify it like some superhero saves the day worship. I'm both proud and (slightly) ashamed each time I handle an incident. It's not a virtue.
How many people who were writing blogs or creating open source tools before signed an on-call oath or declaration? Does that mean they're all wrong or unqualified?
On call avoidance should be rewarded more than on call superheroism. People managing software fail to realize it. Avoidance is stashing enough operational bullet points, mental checklists and manpages.
Everything is generic — because your use case and your operating environment is ONLY yours. Postgres has hundreds of parameters. Nothing is specific or made to satisfy you. If it doesn't, then you don't call the parameter as generic. You choose what to apply for your environment and expectation.
What you're saying is both a wrong reward cycle and it was also not necessary or practical before AI.
Try some of the suggestions there and see what works vs doesn't, hell feed it to an LLM and ask what in there is correct.
Even your comment looks like it's generated by AI.
"You must accept the truth from whatever source it comes." - Maimonides
I feel like "this is AI" needs to join the ranks "it's not that deep" and "this is a Wendy's" as an annoying thought-terminating cliche.
except 99% of AI—written content is spam. If the article was an ad for some company and also made no sense, would you hate on the guy who commented "this is an ad"?
Because that's not what happened? What? Neither smartmic's root-level comment or rdsubhas's response mention it being an ad. I was criticising adhamsalama's dismissal of rdsubhas's comment as AI, who also said nothing about it being an ad. I'm so baffled. Can you not see the structure or the content of this thread?
did you actually read my comment
I think this is deliberate to make a point. Even an AI doesn't use that many em dashes. GP wants you to question it as a "haha see? I'm human yet you called me an AI just like I thought!"
an unprompted LLM would not incorrectly add spaces on either side of their em dashes.
> This is the new witch hunt, or virtue trolling.
The site has had that forever though. You missed out on the last witch hunt of going after straight white men as the root of all evil.
> Unfortunately written by an AI - that completely takes the wind out of the content
I don't think the all-too-common "written by AI" remarks are helpful.
The article was informative and useful. That's what matters IMHO.
Hope this becomes a cultural norm outside of HN. You can have AI written text that had some actual human effort put into it [1], but sloppy AI written content should only be meant for anohter AI consumers and not humans.
1 - https://www.ilfoglio.it/il-foglio-ai/2025/03/22/news/a-first...
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.
A handy trick for column types is check constraints.
You can define constraints on a column that ensure it is text that's valid JSON for example:
Or to ensure specific keys: You can even use this for things like enforcing a valid YYYY-MM-DD date, though that gets a bit convoluted:Correction to the above: it should use
Using = fails because a missing key returns null and in SQLite null = 'text' is null: https://latest.datasette.io/_memory/-/query?sql=select+null%...A CHECK constraint only fails when the expression is false, NULL counts as passing.
My sqlite-utils CLI tool and Python library offers solutions to both the alter table limitations and the need for schema migrations.
For alter table it offers a "transform" command which implements the pattern of creating a new table with your desired scheme, copying data to it from the old table, then renaming the tables (all in a transaction): https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...
And for migrations there's a new-in-v4 "migrate" command which lets you create and execute an ordered sequence of migrations: https://sqlite-utils.datasette.io/en/stable/cli.html#running... Migrations files look like this: https://sqlite-utils.datasette.io/en/stable/migrations.html#...I'd def recommend simonw's CLI and Python lib for anyone doing SQLite stuff, there's all kinds of stuff there.
> 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.
SQLite has transactional DDL: you can start a transaction, do a bunch of create tables, copy data from old tables into the new tables. If an error occurs during this and a rollback occurs, everything will be just like it was before the transaction started. If a commit occurs, the migration succeeds and everyone sees the new schema on their next transaction.
In rollback mode, only the migration thread can be active because it's a write transaction, but I'm guessing in WAL mode, readers can continue to read during the migration, as with any other write transaction. I don't use WAL mode much because for my application (HashBackup), I don't need db concurrency.
If you can accept downtime and you really don't need db concurrency, then that's great and SQLite is probably a good fit for you. There are many applications for which that isn't the case.
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.
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.
Edit: unless the table is empty.
Wrong, you can change NOT NULL since 3.53:
https://sqlite.org/releaselog/3_53_3.html
Released a month ago, thanks, didn't know.
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.
You create a nullable column and then change it to not null. Which wasn't possible in SQLite until recently.
I’m not sure what your original point is pointing out.
Some data doesn’t convert is what I’m pointing out regardless of Postgres or SQLite.
Ah, my bad, first I was wrong about null/not null, and now I see what you mean.
SQLite supports ALTER TABLE:
https://www.sqlite.org/lang_altertable.html
On Go you can embed your migrations in your binary:
https://oscarforner.com/blog/2023-10-10-go-embed-for-migrati...
ALTER TABLE is very limited in sqlite3, though they've been improving it (search the page for " 3." to find the version-specific changes). Any significant schema change is usually simpler to create a new table and copy the data.
FWIW, in a lot of cases you aren't expecting even thousands of simultaneous users, so SQLite is a perfectly valid option. Not to mention services like Cloudflare D2 and Turso which build on SQLite as a core with different features for scale/concurrency.
A lot of people manage to run several containerized applications on a single VPS behind a reverse proxy for personal or small groups. Managing a full rdbms takes work supporting multiple applications, or spinning up multiple instances per app in said containerized flows takes up excess resources, where SQLite would do the job just fine.
Not everything is going to be running 5+ nines of operation with distributed workloads. Plenty of real things run on a decent server with a good enough backup system in place.
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.
https://sqlite.org/whentouse.html
https://sqlite.org/quirks.html
Good links. To make it easier, here are the first paragraphs;
"SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.
Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasize scalability, concurrency, centralization, and control. SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.
SQLite does not compete with client/server databases. SQLite competes with fopen()."
> 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.
[0]: https://www.sqlite.org/lang_altertable.html#otheralter
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 also treat articles about production optimisation with a bit of caution when they don't include any numbers to back up the claims.
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.
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.
> - You have limited options for dealing with schema migrations.
That's the biggest pain of dealign with SQLite.
Date and time are a deeper loss for me.
Where I part ways a little with the recommendation is in the busy_timeout + BEGIN IMMEDIATE suggestion. On an embedded system, that's not really sufficient; I maintain a caching service that is used by thousands of clients. It ingests data over MQTT and has a web interface for queries. In my design, there is a MQTT thread and pruning thread. The MQTT ingestion thread and the pruning thread can both end up "contending" for the write lock, and increasing the busy_timeout only makes them wait longer before noticing that contention and moving on to the next task. What we ended up needing to do was add an application-level lock that only allows a single writer transaction to be in flight at a time; BEGIN IMMEDIATE is nice for preventing other readers from blocking, but it doesn't help much with other writers.
Meanwhile, you might be surprised how often “single-tenant edge deployment” comes up in embedded contexts; a Pi with an SD card is a common configuration for an embedded database, and it's right at the intersection of these problems.
One of the lessons I picked up from my very brief foray into the world of realtime software is the idea of amortization. If you have one activity that is possibly time-unbounded, then the solution is to trade away a bit of throughput for certainty by taxing the frequent task with making incremental progress on the cleanup. The first real-time garbage collectors worked this way. Every allocation had to do a bit of sweeping work, which generally keeps all but the largest allocations from every being in danger of hitting a pause (in real time systems large allocations are typically done at bootstrapping time to avoid this issue entirely)
> Meanwhile, you might be surprised how often “single-tenant edge deployment” comes up in embedded contexts; a Pi with an SD card is a common configuration for an embedded database, and it's right at the intersection of these problems.
Probably shouldn't be. One of the more high profile use cases for SQLite is per-customer sharding. Particularly with services where at any given time a small number of customers are generating the majority of the traffic. The data for customers that you haven't seen logged in for weeks tends to have a logarithmic effect on the query time for active customers when you use a single shared database for all of the data. Unloading that data reduces the cost of interactive workloads handily, even though the cost of backups may still be either the same or still have a logarithmic cost (eg, rsync detects deltas).
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.
sqlite-web, put it behind nginx with basic auth or configure a password.
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...
Connect to the remote server and run it?
> PRAGMA synchronous = NORMAL;
> In NORMAL mode, the database engine syncs to disk only at critical moments (e.g., during checkpoints) rather than at every single transaction commit. In WAL mode, this is completely safe from database corruption; even if the server crashes, only the uncommitted transactions in the WAL are lost, but the database integrity remains intact.
No, this is not safe. You can lose the latest committed transaction with this pragma.
Then WAL mode is not safe for you.
"Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not."
https://sqlite.org/lang_attach.html
Safe in this context means not corrupting the database. You might lose data, but the database will live on happily as if the missing data was never written to it. You don’t get half-committed transactions, and the database isn’t in a weird state.
> even if the server crashes, only the uncommitted transactions in the WAL are lost
I'm mainly referring to this quote.
So, you can lose committed transactions too.
I lost rows in a busy table until I changed to PRAGMA synchronous = FULL
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.
all clients think they're google
I dunno, not losing data and minimizing unnecessary downtime is table-stakes for a pretty significant class of businesses.
If for no other reason, dealing with these at any scale often distracts from running your actual business.
Sqlite with litestream has a smaller default window of data loss than RDF. 1s vs 5 minutes.
RDS*
If you are going down that path, might also be useful to read through this PowerSync blog post.
- https://powersync.com/blog/sqlite-optimizations-for-ultra-hi...
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).
I have some experience with different databases and system designs. In one of my last projects I used SQLite. However, I used only one dedicated process for writing and other processes for read only. This might help.
The era of AI agents need serverless SQL. Its very important to have cost effective tech to run the internet.
hence we ended up writing our own WAL for a Postgres compatible engine, and what surprised me was how much of the work is in the fsync ordering rather than the log format.
Relaxing that is a real choice to lose the last few transactions on a crash. Fine for some workloads but it should be a decision someone made on purpose.
I 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.
It works well enough if you deploy a separate version of the app for each tenant, and upgrade each tenant's app + db in lockstep.
It's a bit of a 1990s setup (as the sibling comment says, it's more or less the same model as a widely-deployed desktop app), but it does scale and can be useful in situations where strict isolation between tenants is beneficial.
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!
Isn't this exactly what every mobile app does when the db is on a phone, and every app where the db is kept on the local machine? I do it with HashBackup and have done 35 db migrations over 17 years without much trouble. There have been 1 or 2 migrations that had a bug, but you fix that by doing another migration.
"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?
When using AI to generate text, I highly recommend asking it to "simplify" the text before publishing. Maybe even more than once.
There should probably be a forum rule to forbid AI written articles or atleast editoralize the title to add an [AI] tag. Would've saved me the click