[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.

[karmarepellent]

If you have multiple applications on different servers communicating with the same database, then yes you would need to run a database such as PostgreSQL.

If you run a single application on a server that needs a database you might want to consider SQLite, regardless of your needs for concurrency/concurrent writes.

[colechristensen]

Having an API in front of a database for full control of what is available is extremely common.

> trying to handle this in your own custom backend code

It's not writing some extra custom code, it's simply locating all of the code which interacts directly with your database on one host. Splitting up where your code is so that what would be function calls in some places if everybody interacted with the database are instead API calls. This kind of organizational decision is not at all unusual.

And if you're using SQLite it's probably because your application is simple and you should have some pushback anyway on people trying to mAkE iT WEBScaLE!! (can I still make this joke or has everybody forgotten?)

A lot of premature optimizers get very worried about concurrency and scalability on systems which will never ever have concurrent queries or need to be scaled at all. I remember making fun of developers running "scalable" Hadoop nonsense on their enormous clusters which cost more than my yearly salary to run by reimplementing their code with cut and grep on my laptop at a 100x speedup.

I've worked places where a third of our cloud budget was running a bunch of database instances which were not even 5% utilized because folks insisted on all of these database benefits which weren't ever going to be actually needed.

[rcxdude]

It's not a particularly unusual situation: it's very common for a database to effectively be entirely owned by an application which manages its own constraints on top of that database. In that circumstance sqlite is pretty interchangable with other databases.

[geraldwhen]

It’s not unusual but is never performant. Adding an api layer and network hops on what should be a database shard or view is why enterprise software sucks so much ass.

Why does the api take 3s to respond? Well it needs to call 6 other apis all of which manager their own data. The problem compounds over time. APIs are not the way to solve cross organization data concerns.

[robertlagrant]

Using SQLite inside an API doesn't add network hops.

[geraldwhen]

“An application controls its database.”

You’ve fully misunderstood what I said. When you have 500 applications, the graph of calls for how any one api resolves will go deep. Api1 calls 2 calls 3 and so on.

Vs creating an organization wide proper way to share and manage data.

[cynicalsecurity]

MySQL is better than PostgreSQL.

[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?

[jddj]

From the performance angle, there's one quoted down thread at https://news.ycombinator.com/item?id=40656043

From the webapp angle check out, for example, pocketbase.io which is an open source Go supabase style backend which wraps sqlite. They have benchmarks etc available.

[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.