[throwaway38375]

I love SQLite, so please don't think that I'm a SQLite hater, but:

I've never seen SQLite used in a setup which multiple machines connect to the same database over a network.

For example: A web application with a web server, a worker/job server, and a database server.

In these instances MySQL or PostgreSQL seem to be much better choices.

So will SQLite ever be able to "take over" in these scenarios?

[karmarepellent]

Yes PostgreSQL (and others) are a better choice in these scenarios. I think the point is that a lot of applications/systems might not even need this separation since a single server would be able to handle the load. In this case a local SQLite database could be a serious performance enhancement.

A lot of factors play into this and it certainly does not work in every case. But I recently got to re-write an application at work in that way and was baffled how simple the application could be if I did not outright overengineer it from the start.

That is just anecdata. But my guess is that this applies to a lot of applications out there. The choice is not between PostgreSQL/MySQL and SQLite, but between choosing a single node to host your application or splitting them between multiple servers for load balancing or other reasons. So the choice is architectural in nature.

[sgbeal]

> I've never seen SQLite used in a setup which multiple machines connect to the same database over a network.

Noting that the sqlite developers recommend against such usage:

https://sqlite.org/whentouse.html

Section 3 says:

3. Checklist For Choosing The Right Database Engine

- Is the data separated from the application by a network? → choose client/server

[gwd]

> I've never seen SQLite used in a setup which multiple machines connect to the same database over a network.

If you actually mean "database server", i.e., SQL is going over the wire, I don't see why you'd ever structure things that way. You lose both SQLite's advantages (same address space, no network round-trip, no need to manage a "database server") and also lose traditional RDBMS advantages (decades of experience doing multiple users, authentication, efficient wire transfer, stored procedures, efficient multiple-writer transactions, etc).

Assuming that it's the worker / job server which is primarily issuing SQL queries, what you'd do is move the data to the appropriate server and integrate SQLite into those processes. (ETA: Or to think about it differently, you'd move anything that needs to issue SQL queries onto the "database server" and have them access the data directly.) You'd lose efficient multiple-writer transactions, but potentially get much lower latency and much simpler deployment and testing.

[rmbyrro]

If you need replication on the app level, SQLite doesn't make sense, because it's not built for networks.

But...

Many projects won't ever need that. A bare metal machine can give you dozens of cores handling thousands of requests per second, for a fraction of the cost of cloud servers. And a fraction of the operational complexity.

Single point of failure is really problematic if your system is mission critical. If not, most apps can live with the possibility of a few minutes of downtime to spin up a fail over machine.

[dlisboa]

Turso (https://turso.tech/) offers a solution in that scenario. The advantage with SQLite being that each machine has a local copy of the database (optionally I think) for reads so it’ll be extremely fast, and writes happen to one primary database but are abstracted and replicated.

[xenodium]

> I've never seen SQLite used in a setup which multiple machines connect to the same database over a network.

Cloudflare D1 https://developers.cloudflare.com/d1 offers cloud SQLite databases.

> For example: A web application with a web server, a worker/job server, and a database server.

I've been giving it a run on a blogging service https://lmno.lol. Here's my blog on it https://lmno.lol/alvaro.

[masfoobar]

In a nutshell, if you have a database that will have multiple instances talking to it - you are better off with a client-server database, like Postgres, MariaDB, SQL Server, Oracle, etc.

SQlite, generally speaking, is a FANTASTIC local database solution, like for use in an application.

[throwaway290]

If you split DBs so each has only one writer then it probably is possible even in vanilla SQLite...

[colechristensen]

You can have a backend talking to sqlite and everything else interacting with backend apis

[shiroiushi]

What's the point of this? If you have multiple applications on multiple systems accessing the same DB, it seems to make more sense to just use PostgreSQL, since it's specifically designed for concurrent operation like this, instead of trying to handle this in your own custom backend code.

[jddj]

There are absolutely production web apps running sqlite as the datastore.

The "one writer at a time and the rest queue" caveat is fine for most web applications when writes happen in single digit / low 10s of ms

[BaculumMeumEst]

Is there documentation on how to configure SQLite in the manner you’re describing?

[Sammi]

So many people have multiple machines when they would be better served by just having the one.

I mean look at the prices from Hetzner.com.

  Shared vps:    16 core cpu,  32 GB ram, 320 GB disk for €  38.56
  Dedicated vps: 48 core cpu, 192 GB ram, 960 GB disk for € 343.30

The amount of stuff you can run for peanuts. It's amazing.