> On off days, I sometimes wonder if I’m bought into some narratives too strongly. Like, is Postgres really the world’s best database? Experiences like this certainly cement my conviction. Yes, it is.
I find these 'different therefore wrong' takes to be immature.
Yes, SQLite is idiosyncratic in comparison to other relational database engines. There are reasons behind those idiosyncrasies: SQLite is designed for other use cases than those other engines, and therefore has other design decisions.
Ultimately, all computer programs are solutions to problems, and the approach to solving a problem depends on the nature of the problem. A list of grievances and a Boolean judgment is useless without stating the problem that the author is trying to solve.
The wacky approach to column types came from SQLite's origins of being closely integrated with Tcl. Knowing that doesn't somehow mean it was a good default worth carrying on for decades.
Firstly, while it's the default, it's not mandatory. As the author discovered you can use STRICT to make the columns typed, and types to be enforced.
The reason it remains unaltered by default is because one of the goals (and accomplishments) of SQLite is that the on-disk-data-file is completely backwards compatible, and cross-platform. This is a very important feature in some situations, and not lightly tossed aside because some old default or system is not in vogue anymore.
A lot of people get real obsessive about the "worlds best" thing, take a motorcycle for example, but them themselves lack the technical skill to actually require that level of equipment. Looking for the "best" whatever all the time is a sign you don't know what you are doing.
I've felt this way for a long time, and I enjoy using lower quality things sometimes just to confirm it's worth the investment into something more advanced.
Like violin, I still play my $50 special I bought in 2005, because it sounds fine and I'm terrible so it's Good Enough.
On the other hand, I am building my second computer keyboard that's going to cost me $300+ because, well, I make a living with these things and I use it 60+ hours a week. I have wrist problems, so a better keyboard literally translates into more hours billed.
I would be happy if SQLite offered a native DATETIME type. That has been in the SQL spec since 1992! and is supported by every RDBMS that exists today in the planet. But nooo - its gets stored as TEXT and causes all kinds of issues for folks who don't know this.
Advanced alter table operations do involve creating a new table, but the pattern for doing that is actually pretty robust: you start a new transaction, create the new table, copy the data across and then atomically swap the table names before you commit: https://www.sqlite.org/lang_altertable.html#otheralter
Yeah, my solution doesn't automatically handle that (yet).
That's why it provides a --sql option - if it doesn't entirely handle your particular case you can instead get it to generate SQL for you without executing it, so you can make modifications you need before running it.
There are things that different databases are best for.
This seems very blunty anti-sqlite, and things postgreSQL is the best, so I'd be interested to see a guide for (two things I've used sqlite for in the last week):
* Using postgreSQL to store data in an iPhone app
* Making a small python script which uses PostgreSQL, and then seeing how much work it is to send that to someone else, so they can use your work and extend it (send the database, and also instructions for installing postreSQL, and getting everything set up. Make sure it works on linux, mac and windows).
The author is both ignorant of SQLite history, and philosophy as also ignorant of the real use-cases SQLite solves for embedding a lightweight SQL-ish database into applications.
Looking into his "about" section he worked mostly with web APIs (Stripe, Heroku) and is a self-proclaimed fan of Postgres. Maybe when he acquires some experience working with embedded applications without boatloads of resources available nor a stable network stack he might appreciate SQLite for what it is since the comparison to Postgres is non-sensical when approaching from that viewpoint.
I didn't take this post to be overly negative, just a reflection of their experience in working with the tech.
For anyone coming from a heavyweight SQL background, it is indeed pretty easy to be caught off guard with SQLite. That doesn't reduce its value of course.
None of the things mentioned in the article appear to have any significant impact on the resource usage and or embeddability if they were implemented, so it's totally fair to draw comparisons here.
If want to have more of an apples-to-apples comparison you could swap out Postgres for DuckDB (which aims to follow Postgres in SQL dialect), and all of the mentioned points should still hold.
> embedding a lightweight SQL-ish database into applications
Current personal project is a game with crafting. I'm tired of games with limited inventory and no search / filters so I've been enjoying SQLite to manage that. The fact the db is a simple file is awesome to manage backups and you don't have to start a server to tinker with it: DB Browser, open file and you're done.
Also kudos to the team behind the Godot SQLite wrapper.
I feel like the recent hype around SQLite made people use it for a lot of stuff that is not really suitable for SQLite. It has too many caveats especially around data types.
I'm not saying it does not deserve the attention, it is a fantastic piece of software but if I had the option to use PostgreSQL for something I'd never ever get close to choosing SQLite over it. It shines when you don't need or want something more feature packed.
> if I had the option to use PostgreSQL for something I'd never ever get close to choosing SQLite over it
I had the option for a recent project. It's a niche forum-like application with around 2,000 users. Went with a monolithic design and vertical scaling (if needed), so SQLite was perfect. Every dynamic HTML page renders in under 1ms. Litestream for live-replication to a couple S3 buckets.
Running PostgreSQL for something like this would be a pain in the ass, and add a minimum 10ms of latency to every request. There would genuinely be more maintenance required for the PostgreSQL server/daemon than the application itself. It just makes no sense.
> There would genuinely be more maintenance required for the PostgreSQL server/daemon than the application itself. It just makes no sense.
I think this is the key point the original post was missing. When you’re working on a project with a DB, you should factor in the cost/overhead of the DB as well. Postgres is a wonderful DB. But it is also big and requires work to keep it running. For many projects, the extra overhead is well worth it.
(And if you’re using a “cloud” DB, the overhead is still there, but you’re explicitly paying for the privilege of making it someone else’s problem. )
But for smaller projects, or ones with less DB requirements, something small like SQLite is much more appropriate. And it nearly removes the DB overhead from the maintenance equation.
Is that 10ms of network latency? I remember Postgres being pretty low latency if you run it on the same host, although the point about maintenance makes complete sense.
> if I had the option to use PostgreSQL for something I'd never ever get close to choosing SQLite over it.
I'm using both Postgres and SQLite for active projects. Postgres is great for a multi-user blog I run where the DB is hosted, backed up, etc. The same site would run fine and slightly faster with local SQLite (which it used to) but having Postgres lets me use Render.com's built in management features, which are nice.
SQLite works great for anything app-like. I have a script that OCRs certain video game screenshots and saves the OCR data in a searchable database, for example. Postgres would be complete overkill for this and would add nothing but hassle. I don't want to bother with keeping a separate database going for that in my Postgres server. I just want a folder of files, and SQLite works perfectly for that. I could use Postgres but it would offer zero useful benefits and might be a bit slower (even with a local server) due to my sloppy code.
They are just different tools for different tasks, with some overlap.
When file format is no longer backwards compatible, it makes sense to make a new major (semantic) version, so you don't have a situation where version 3.50 can read a file but version 3.43 cannot.
Agreed. Most of that can be achieved with SQL (how hard is it to add STRICT?) or the code you use to interact with SQLite. I don't see how it's worth sacrificing backwards compatibility to achieve those things, which are mostly non-issues in practice.
Worth noting that this is from Brandur's "atoms" series: https://brandur.org/atoms - "Multimedia particles in the style of a tweet"
So it's more a short set of notes (like a TIL) than a full-fleshed blog post. Brandur's long-form writing has a different tone: https://brandur.org/articles
I find comparing SQLite with Postgres moot to begin with. I use SQLite when I don't want to run a database server out-of-band, or when I want to need to copy a single file to copy an entire database. For that, it is unparallelled, easily the best in the world, by far.
You can reflect on your early experience with SQLite while simultaneously reflecting on why Postgres is a great database. If it wasn't that way, we'd all be running SQLite for our web services, but we're not.
All technologies, and especially databases, are best applied with thoughtful consideration of the context. I didn't get the sense the author was suggesting their SQLite use case would actually be better served by Postgres. Just the same as a use case best served by Postgres is not suitable for SQLite.
Sure. I can also reflect on my early experience with a Ferrari Enzo while I simultaneously reflect on how much I love my horse, but it should be clear that that's just irrelevant musing, rather than a direct comparison.
> I didn't get the sense the author was suggesting their SQLite use case would actually be better served by Postgres.
I got exactly that sense, from "I tried SQLite, it lacked these things. Postgres really is the best database".
1. Strictness by default and with no escape hatches;
2. Proper data types like all dates / times / datetimes / JSON etc.
3. In-process / embedded database. Almost all projects I worked on over the course of a 22 years of career did not need a separate node / VM / pod for a database.
PostgreSQL / MySQL and SQLite are not some ideal polar opposites. There is potential for a lot of cross-pollination of features and the ability to achieve the perfect DB.
Readit News
I find these 'different therefore wrong' takes to be immature.
Yes, SQLite is idiosyncratic in comparison to other relational database engines. There are reasons behind those idiosyncrasies: SQLite is designed for other use cases than those other engines, and therefore has other design decisions.
Ultimately, all computer programs are solutions to problems, and the approach to solving a problem depends on the nature of the problem. A list of grievances and a Boolean judgment is useless without stating the problem that the author is trying to solve.
The reason it remains unaltered by default is because one of the goals (and accomplishments) of SQLite is that the on-disk-data-file is completely backwards compatible, and cross-platform. This is a very important feature in some situations, and not lightly tossed aside because some old default or system is not in vogue anymore.
Deleted Comment
Like violin, I still play my $50 special I bought in 2005, because it sounds fine and I'm terrible so it's Good Enough.
On the other hand, I am building my second computer keyboard that's going to cost me $300+ because, well, I make a living with these things and I use it 60+ hours a week. I have wrist problems, so a better keyboard literally translates into more hours billed.
My favourite feature of my sqlite-utils CLI tool is the "transform" command which implements this pattern for you (also available as a Python library method): https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...
That's why it provides a --sql option - if it doesn't entirely handle your particular case you can instead get it to generate SQL for you without executing it, so you can make modifications you need before running it.
This seems very blunty anti-sqlite, and things postgreSQL is the best, so I'd be interested to see a guide for (two things I've used sqlite for in the last week):
* Using postgreSQL to store data in an iPhone app
* Making a small python script which uses PostgreSQL, and then seeing how much work it is to send that to someone else, so they can use your work and extend it (send the database, and also instructions for installing postreSQL, and getting everything set up. Make sure it works on linux, mac and windows).
Looking into his "about" section he worked mostly with web APIs (Stripe, Heroku) and is a self-proclaimed fan of Postgres. Maybe when he acquires some experience working with embedded applications without boatloads of resources available nor a stable network stack he might appreciate SQLite for what it is since the comparison to Postgres is non-sensical when approaching from that viewpoint.
For anyone coming from a heavyweight SQL background, it is indeed pretty easy to be caught off guard with SQLite. That doesn't reduce its value of course.
If want to have more of an apples-to-apples comparison you could swap out Postgres for DuckDB (which aims to follow Postgres in SQL dialect), and all of the mentioned points should still hold.
Current personal project is a game with crafting. I'm tired of games with limited inventory and no search / filters so I've been enjoying SQLite to manage that. The fact the db is a simple file is awesome to manage backups and you don't have to start a server to tinker with it: DB Browser, open file and you're done.
Also kudos to the team behind the Godot SQLite wrapper.
I'm not saying it does not deserve the attention, it is a fantastic piece of software but if I had the option to use PostgreSQL for something I'd never ever get close to choosing SQLite over it. It shines when you don't need or want something more feature packed.
I had the option for a recent project. It's a niche forum-like application with around 2,000 users. Went with a monolithic design and vertical scaling (if needed), so SQLite was perfect. Every dynamic HTML page renders in under 1ms. Litestream for live-replication to a couple S3 buckets.
Running PostgreSQL for something like this would be a pain in the ass, and add a minimum 10ms of latency to every request. There would genuinely be more maintenance required for the PostgreSQL server/daemon than the application itself. It just makes no sense.
I think this is the key point the original post was missing. When you’re working on a project with a DB, you should factor in the cost/overhead of the DB as well. Postgres is a wonderful DB. But it is also big and requires work to keep it running. For many projects, the extra overhead is well worth it.
(And if you’re using a “cloud” DB, the overhead is still there, but you’re explicitly paying for the privilege of making it someone else’s problem. )
But for smaller projects, or ones with less DB requirements, something small like SQLite is much more appropriate. And it nearly removes the DB overhead from the maintenance equation.
I'm using both Postgres and SQLite for active projects. Postgres is great for a multi-user blog I run where the DB is hosted, backed up, etc. The same site would run fine and slightly faster with local SQLite (which it used to) but having Postgres lets me use Render.com's built in management features, which are nice.
SQLite works great for anything app-like. I have a script that OCRs certain video game screenshots and saves the OCR data in a searchable database, for example. Postgres would be complete overkill for this and would add nothing but hassle. I don't want to bother with keeping a separate database going for that in my Postgres server. I just want a folder of files, and SQLite works perfectly for that. I could use Postgres but it would offer zero useful benefits and might be a bit slower (even with a local server) due to my sloppy code.
They are just different tools for different tasks, with some overlap.
Why make a new vesion that breaks compatibility with the old version?
Why make a new version just so it behaves like all the other database engines out there? Isn't having difference the point of having choices?
When file format is no longer backwards compatible, it makes sense to make a new major (semantic) version, so you don't have a situation where version 3.50 can read a file but version 3.43 cannot.
He heard a hype (sqlite), decided to try it to see what is it about, found out it's not for him really, wrote a bit blurb about it on his blog.
So it's more a short set of notes (like a TIL) than a full-fleshed blog post. Brandur's long-form writing has a different tone: https://brandur.org/articles
I don't understand the comparison here at all.
All technologies, and especially databases, are best applied with thoughtful consideration of the context. I didn't get the sense the author was suggesting their SQLite use case would actually be better served by Postgres. Just the same as a use case best served by Postgres is not suitable for SQLite.
> I didn't get the sense the author was suggesting their SQLite use case would actually be better served by Postgres.
I got exactly that sense, from "I tried SQLite, it lacked these things. Postgres really is the best database".
Noting that every interactive site run under the Hwaci[^1] umbrella does, e.g. sqlite's own forum and source control system.
[1]: The company behind sqlite.
1. Strictness by default and with no escape hatches;
2. Proper data types like all dates / times / datetimes / JSON etc.
3. In-process / embedded database. Almost all projects I worked on over the course of a 22 years of career did not need a separate node / VM / pod for a database.
PostgreSQL / MySQL and SQLite are not some ideal polar opposites. There is potential for a lot of cross-pollination of features and the ability to achieve the perfect DB.
There. Now you understand the comparison.