> One huge benefit to SQLite is the fact that it runs as an embedded part of your application
But all production workloads I've been involved with the last ≈5 years have either been containerised apps offloading state to databases/caches/blob storage etc, or have strived towards that.
What I do think would be awesome would be an embeddable Postgres library/binary that could use a single state file on your local filesystem for development and use a networked database in production. Getting the benefits I like most about sqlite locally, and not having to deal with files in production.
This article really needs to be taken in the context of the author’s target audience: He sells courses that primarily target web dev juniors doing learning projects and small projects. That’s why he includes the “most of you reading this” disclaimer in the article:
> So, can you use SQLite? For the vast majority of you reading this, the answer is “yes.” Should you use SQLite? I’d say that still for the majority of you reading this, the answer is also “yes.”
Pushing strong contrarian opinions is part of his social media marketing style. He’s also pushing a “why I won’t use next.js” article and opinion on his social media, while conveniently omitting the obvious conflict of interest that he sells courses that don’t happen to cover Next.js.
I think some people are missing the conflict of interest because the author blurs the lines between his personal opinion and his business. He turned his personal brand into his business and uses his name as his brand.
No, what's going on is that you don't understand what "conflict of interest" refers to.
Different people having different interests, as when the apple company says you should buy apples even though you prefer oranges, is just a regular conflict. A conflict of interest is when one person has two different interests. The apple company isn't experiencing a conflict in your example.
Pretty sure that's just marketing.
A lot of product pages will spell out features they have and list competitors that don't have those features.
It's even worse: that Nike has a conflict of interest because they think that wearing shoes while running is good, even though it would be extremely weird if a running shoe company thought that running with shoes is bad.
Remove the business from the equation and the opinion is fine — it's the existence of a business that aligns with the opinion that creates the conflict! Galaxy brain definition.
He has motivations that make him not impartial, but that's not usually termed a "conflict of interest."
You're just using the term strangely. Usually the phrase "conflict of interest" implies the person has some sort of official commitment / obligation / duty to another party that is put in jeopardy because of another conflicting interest.
But this is just some guy selling stuff. He hasn't made any official commitments; he doesn't have any official duty or obligation to remain impartial.
By your definition, anyone selling anything has a conflict of interest because they want money from their customers, which may be against the customers' best interests.
I really don't get the reasoning here...
I wouldn’t be surprised if he offered Next.js courses if it becomes popular enough in the future. For now, the anti-Nextjs push seems like a clear defensive play to steer people back to the course material he already had for sale.
The whole course he's been advertising is based on remix, which came up YEARS after next.js rose to popularity.
Where did you get this info that you were so confident on?
https://www.reddit.com/r/nextjs/comments/17giozq/why_i_wont_...
https://www.reddit.com/r/reactjs/comments/17gl76d/why_i_wont...
Last time I truly used NextJS was in 2020 (it was v9), and that was only to make a statically generated brochure type site. I had started the site with Gatsby and didn't love Gatsby, so I switched to NextJS and loved it.
Recently I did start up a Next v13 project using the new App Router. But I haven't gotten that far past the initial scaffold and creating a couple components. Seeing all of these complaints about v13, especially the App Router, are making me want to scrap that and use something else.
I mean this is more of less what using a local container for Postgres is. It’s not exactly one file but it’s hidden in a volume somewhere where you don’t need to go file digging, so for all intents and purposes sure, it’s one file.
Most ORMs also will let you swap out your RDBMS so you can run sqlite locally and Postgres in prod via the same code. I think this is a bit of an anti pattern though. While the two are similar in most standard use cases, it’s not always apparent when you veer off that standard use case and into something that’s not supported by sqlite. And then all of the sudden you either have a divergence between what works locally vs in production or you start to intentionally limit your app’s capability to only what works with sqlite because you can’t locally validate it.
I run a single container Postgres for my homeserver, and have to deal with occasional issues that wouldn’t apply to SQLite (which I run as well).
I don't understand this. How would you solve backups without using the filesystem?
What's the conceptual difference between backing up your Oracle database to an Oracle Cloud Infrastructure bucket and backing up your sqlite database to an S3 bucket?
My point was more that with a networked database, you can easily centralise your backing up of databases. If you are in the cloud with managed databases, the backing up is kinda solved out of the box. If you are doing it yourself or on-prem, you probably have a database cluster of some kind where backing up can be managed (which of course involves a filesystem at some point).
But if you are using sqlite, every sqlite file across your organisation needs to be backed up. You'll probably have some scripts creating the actual snapshot and then some other scripts shipping that away somewhere. Those scripts probably need to be quite bespoke for different hosting solutions. Are you going to ssh into the vm's running the apps and do the backup that way, or will you be running the backing up scripts on each vm?
Of course if you have a single VM hosting a few apps with one sqlite database each, none of this is an issue, and it might be easier in the short term than a networked database. Or even if you are operating at "scale" and treating your VM's more like pets than cattle, it's probably not going to be an issue either.
Absolutely! And also, short of that but incrementally building toward it, some sort of simplification of initial db setup and configuration, including really solid, thoughtful defaults (many of them are but I am guessing they could be substantially improved).
I feel like half of people’s attraction to SQLite is the easy setup and first deploy story. Postgres will never match that but could get closer.
That is an incredibly important part of the story!
Spending a lot of time on configuring and monitoring and operationalizing your database can kill a business iterating on its minimum viable project.
You can delete and recreate the volume when you want with `docker compose down --volumes` flag, which is great for testing.
Shameless plug: https://mahesh-hegde.github.io/posts/postgres_local_using_do...
I wired Postgres up for our local dev. I don't believe in mocking the database, so all our tests and local dev run against a real Postgres instance.
The main tricks:
- Store the Postgres installation in a known location.
- Set the dynamic loader path to the installation lib dir, e.g., LD_PRELOAD.
- Don't run CI as root (or patch out Postgres' check for root)
- Create and cache the data directory based on the source code migrations.
- Use clone file to duplicate the cached data directory to give to individual tests.
One thing I'd like to pursue is to store the Postgres data dir in SQLite [1]. Then, I can reset the "file system" using SQL after each test instead of copying the entire datadir.
https://github.com/zonkyio/embedded-postgres
Each test just copies a template database so it’s ultra fast and avoids the need for complicated reset logic.
1. Each test runs all migrations on a fresh database. Each test spends 2.1 seconds on db setup.
2. Each test suite runs all migrations. Each test copies the template database from the test suite. Each suite takes 2.1 seconds to run migrations, but cloning a template database takes 300 ms.
3. Bazel caches the data dir and rebuilds it once for all test suites. Reduces the initial test suite setup from 2.1 seconds to 160 ms (to copy the datadir).
4. Each test in a suite uses clonefile to copy the datadir. Reduces db setup overhead from 300 ms (to copy a template database) to 80 ms (to clonefile a datadir).
Currently, most of our testing overhead is clonefile and cleaning up the datadir. I'm interested in a single file sqlite FS because I could be clever and use LD_PRELOAD to replace Postgres's file system operations with sqlite, avoiding most syscalls altogether.
Another problem is that parallel tests can exhaust memory.
The slowest part of our tests is syscall overhead. A mostly empty Postgres data dir for a medium-sized database with a few hundred tables consists of 3k files. On my M1 macOS, it takes 120 ms to delete the entire data dir. Copying is cheaper at 80 ms using clonefile.
In $oldjob I designed and managed a system that was eventually running many hundreds of MySQL 3-node groups. MySQL in particular has a lot of nobs that you need to understand to set up a cluster that doesn't lose data during a fail over (semi sync, sync bin log, trx commit flush, ....). And then of course: neither MySQL nor PostgreSQL have a built-in mechanism to handle fail overs at all. It's another software stack around that, or even something custom built.
Again, not saying I agree with the author. I'm just saying you're portraying it easier than it actually is.
You obviously don’t need a cluster if you’re considering SQLite as an alternative.
Comparing a full database cluster to SQLite doesn’t make sense.
If you just want peace of mind and better disaster handling, and your current uptime, concurrent workload split, and performance is fine -- maybe your hosting provider eats it for an hour or two and you don't want to be hamstrung -- I would suggest just using a tool like Litestream, replicating the DB to S3, and setting up a hot standby server in some alternative region that you can fail over to and that actively synchronizes the working set. Always keep them up to date with deployment automation. Set up some alerting, and if a failure happens, terminate the main instance with prejudice, let the replica catch up by replaying any latest changes, and reorient your load balancer to point to it (Cloudflare tunnels are a good low-tech solution you could use to do that.) This might imply a small downtime window for the hot failover. For bonus points, you can automate this whole task, and actively perform it regularly, swapping between servers A/B on regular cadence -- and then you have good confidence in hot-failover disaster recovery. e.g. just do this whole dance every week on Monday at 1am UTC, and you can have confidence it works and will stay working.
This feels like an uncharitable interpretation of the maturity of projects like LiteFS/rqlite/dqlite.
That said, I looked at 2/3 a while ago:
- rqlite looks great and mature, though it has a HTTP interface, so I'll need to rewrite my database layer anyway to use it. At that point might as well to Postgres
- dqlite seemed like a failed Canonical experiment to me last time I checked, but looking again I think I may have had the wrong impression.
And: the SQLite homepage runs on - guess it. Maybe they have some doc/blog describing how they manage their traffic.
On the other hand, read replicas are also the easy case for database clusters. And just making your one database server bigger also scales a lot, there is nothing stopping you from adding a terabyte of RAM.
And just making your one database server
bigger also scales a lot, there is nothing
stopping you from adding a terabyte of RAM.
Additionally, I think a large percentage of the HN crowd underestimates what a single large-ish single server can handle these days. Probably because we are all used to only seeing tiny virtualized slices these days.
192+ threads, 2TB of RAM, terabytes of crazy fast PCIe storage. This was multiple racks of servers not too long ago, now in ~2U and it costs less than a car.
Relatively easy to get started with a 'production-grade' cluster.
And they typically don't require any external load balancers, connection poolers, etc.
I'd like to think once NewSQL DBs mature, we'll see a shift away from using/abusing SQLite for these kinds of use cases.
There exist varying levels of complexity. Here at my company, we have a very small cluster comprised of a main PostgreSQL instance and four replicas. It just works and requires very low maintenance effort.
But I imagine that things can get really messy if you're dealing with dozens or hundreds of instances, for sure, especially if you need sharding.
As you said, it depends on what you want:
- If you are okay with losing data in the event of a failover, then a simple setup is fine. - If you want lossless failovers (i.e. not a single transaction is lost), then that's hard. - If you want entirely automatic failovers, that's also very hard. - ...
My experience stems from MySQL, and not PostgreSQL, so this may or may not apply to pg. I wrote about my experience in this blog post: https://blog.heckel.io/2021/10/19/lossless-mysql-semi-sync-r... --- TLDR: Not losing data with MySQL is really really hard.
What concerns me more than anything with a process like LiteFS is the 'unknown unknowns' perspective of what can go wrong - whereas I think MySQL and Postgres have generally more understood failure modes.
I also think that a lot of people in middle sized projects where they're not big enough to have a dedicated database team, etc will end up using some kind of managed database solution - something like Amazon Aurora, or RDS, or even someone like DigitalOcean's managed database product? Those generally take care of sensible configuration, backup, and failover at least.
edit: FWIW, I've done both ways - self-managed MySQL & Postgres clusters and managed services. You're right - getting MySQL in particular set up to be crash-safe etc requires careful documentation reading, and it's very frustrating that default installs of MySQL don't come already configured like this. In my eyes, safe-but-slow is a much better default than fast-but-risky :/
SQLite can handle multiple processes: https://www.sqlite.org/faq.html#q5
I am not sure it could back then
If the load is very read heavy the write locks aren't an issue, but if you're tracking page views, comments or any other write-heavy feature, it'll be harder to get good performance.
Alter table is solvable too. I built tooling for that here: https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...
I'm not sure how else you could do that besides a DHT without a central database or manually assigning certain pieces of data to specific servers.
People like messing around with meme features but I don’t think there’s many companies in the world using more than that.
But... for an app running multiple instances, the article suggests a lot of extra complexity. Managing that complexity makes less sense to me than just firing up a managed database server (RDS, or whatever).
Even if you're self hosting, I think running a MySQL/Postgres cluster is a lot less complex than the options this article calls out.
edit: and more to the point - MySQL / Postgres replication is boring. It's done by a ton of deployments, and isn't going to surprise you, at least at smaller scale.