[TemptedMuse]

I'd argue that anything larger than a desktop app should not use SQLite. If you need Litestream for replication and backup it is probably better to just use Postgres. There are a ton of one-click deployment offerings for proper databases, Fly.io actually offers managed Postgres.

[victorbjorklund]

Why would you argue that? Do you have some benchmarks backing it up or is it more a personal preference?

[crazygringo]

It's literally what they're designed for.

SQLite is designed for one local client at a time. Client-server relational databases are designed for many clients at a time.

[simonw]

That's not entirely true. SQLite is designed to support many processes reading the same file on disk at once. It only allows one process to write at a time, using locks - but since most writes finish in less than a ms in most cases having a process wait until another process finishes their write isn't actually a problem.

If you have lots of concurrent writes SQLite isn't the right solution. For concurrent reads it's fine.

SQLite also isn't a network database out-of-the-box. If you want to be able to access it over the network you need to solve that separately.

(Don't try and use NFS. https://sqlite.org/howtocorrupt.html#_filesystems_with_broke... )

[skeeter2020]

the reality is very few workloads have access patterns that SQLite can't support. I would much rather start with a strategy like 1. use sqlite for my beta / single client, 2. duplicate the entire environment for the next n clients, 3. solve the "my application is wildly successful" and SQLite is no longer appropriate problem at a future date. Spoiler: you're never going to get to step #3.

[crazygringo]

> 2. duplicate the entire environment for the next n clients

That becomes an instant problem if users ever write to your database. You can't duplicate the environment unless it's read-only.

And even if the database is read-only for users, the fact that every time you update it you need to redeploy the database to every client, is pretty annoying.

That's why it's usually better to start with Postgres or MySQL. A single source of truth for data makes everything vastly easier.

[victorbjorklund]

Not true. Can you back up your claim that the developers of Sqlite says they dont recommend it for webservers? (hint they recommend it).

If you have a read-heavy app (99% of saas) that runs on one server and dont have millions of users then sqlite is a great option.

[crazygringo]

I didn't say that. I said one local client at a time. If you're running on one server then your webserver is the one local client.

Usually you want to be able to run multiple webservers against a single database though, since that's the first thing you'll usually need to scale.