I love sqlite as much as the next guy but as soon as you are working with multiple write processes (which you quickly will) you will have to move off. So, why not just start with Postgres?

SQLite is a replacement for structured data stored in a file. If your data store is a big XML or JSON or CSV, SQLite may be a better solution. For example, I’m using it for ETL tasks and it’s working well for this use case.

If you need a networked database supporting many users, probably better off with Postgres.

For server applications I don't think so (despite the recent hype driven by businesses marketing the cloud services). Managing multiple writes and durability needs the same or even more effort and attention than running PG or MySQL. And on top of it SQLite is missing many often crucial features. For me, those are the type system and functions.

Sure, if you simply need a dumb store just for storing basic types that’s simple to operate. But SQLite is not at feature parity with Postgres.

sqlite is great for what it is

But I recently ended up using sqlite on a site, & it's clear it makes a lot of trade offs to keep itself minimalist. I wish I had just used postgres

That minimalism can be appropriate if you want something that works with a single file to track state & no external process

Have you tried using something like Turso? Curious how that experience would be for you.

(I do not work at Turso)

Looking over it, I don't see why you'd use a managed sqlite service. Unless you started with sqlite & now you're stuck with its quirks

Don't know, I lean pretty minimalist on things, like I'm using `hyper` directly on another website I work on, rather than picking axum or warp

I am curious on this. Why do you think SQLite is simpler than Postgres? and in what setting is that the case?

Sqlite comes with the headache of managing an attached volume.

Building an mvp or a single container app.

I’m actually trying to get Postgres to work like this right now.

SQLite being embedded is simpler to use and more performant in this environment as there is no IPC overhead.

Why even use a database? Just assign a variable. All state is dead the second you destroy the container anyways.

Well then single container plus one attached volume.

Yep, my point was that this roughly has the complexity of setting up eg. Supabase.

In conclusion: I would always advice for Postgres over the approach of setting up a volume with sqlite.

The second you need to move on, you need to get that pesky file out of that volume. With Postgres, you just connect to the database from any psql compliant client.