4 ms·

This is all true. Like most people here I'm deeply, deeply in love with SQLite, but using it on the server is not worth it, exactly for the reasons listed in th

by bambax 2y ago

This is all true. Like most people here I'm deeply, deeply in love with SQLite, but using it on the server is not worth it, exactly for the reasons listed in the post. To paraphrase: a big part of the appeal of SQLite is that there's nothing to set up. But if one wants to use it in production then that advantage is gone.

vidarh 2y ago
SQlite on the server is worth it when the server side is an application and you treat SQlite as a per user datastore for an application that is mostly per user, or where shared data across users can be treated as a queuing/async messaging problem. Decomposing a system to treat it as a per-user application working on per-user data works for a whole lot of problems and solves a whole lot of problems. It scales effortlessly. I've done that kind of system even with flat files, handling millions of user accounts on a set of servers with an aggregate capacity less than my current $300 mini PC.
- egeozcan 2y ago
  > where shared data across users can be treated as a queuing/async messaging problem Really asking: Why would you want that complexity when a single database that provides ACID with multiple users can provide that out of the box? Sqlite shines when it's on the edge (be it a fat desktop app, or a thin-client which can hold a cache, or any other modern definition of edge), and while I absolutely adore the tech[0], I don't get why people try to repurpose it to simplify server architecture, especially in this day and age even the most database with the most complex installation is one docker pull away. [0]: I honestly think Sqlite is one of the best software ever developed.
  - vidarh 2y ago
    You're assuming complexity, whereas my experience is that it reduces complexity. E.g. you get infinite scalability. You get no need to ensure all your queries are scoped correctly, because they can't directly access other data. Most large systems ends up with async processing and/or eventually sharding anyway, and then you get to restructure around those flows anyway, while still having the complexities of semi-centralised databases. The world is async - once you embrace async designs from the start and design your UX around accepting that there are very situation you need global serialisation of events, it gets a lot simpler. > Sqlite shines when it's on the edge (be it a fat desktop app, or a thin-client which can hold a cache, or any other modern definition of edge), Which is not materially different from a server spinning up to serve requests for a single user. > I don't get why people try to repurpose it to simplify server architecture, especially in this day and age even the most database with the most complex installation is one docker pull away. Because maintaining a large database cluster and scaling it to large user sets, and ensure the application software is using it correctly to avoid problems is a lot of work. I built my first datastore-per-user system in '99, without the luxury of SQlite. It was a revelation. It was a fundamentally so much more pleasant way to work. In some cases, datastore-per-user is the right pattern. In other cases datastore-per-tenant. In some cases, a big RDBMS is still the better option. But don't dismiss the former two without actually exploring it. Over the years, I'm finding it fits more and more places, especially thanks to SQlite.
- HelloNurse 2y ago
  Realistic applications can be mostly per-user data, but with a residue of global data (e.g. the tables that list all those beautifully segregated users). Even if per-user databases work well and can be managed simply (e.g. doing backups by briefly taking them offline when idle and copying entire files) the global part usually has more requirements (e.g. cannot be shut down for maintenance, requiring "live" backups) and requires greater complexity (for example sharing it through a traditional DBMS network server or through application-layer services).
  - vidarh 2y ago
    There's rarely any reason for not being able to shut them down, because you can trivially replicate. E.g. I've built systems where the only global piece of data was which backend held which user, and that data was trivially lazily updated via gossip-style protocols. Login requests were broadcast, and the backends responded, and the central services acted only as "backups" storing the most up to date info seen in case a backend happened to be unavailable. The whole thing was trivial to set up. (And you can back up sqlite "live" just fine with some care if you want to, or things like litestream).
- foobarbaz33 2y ago
  That's a pretty good take. Would make sense for a whole range of apps focused on 1 user or client. Accounting software is personal, company A doesn't need to query/join with data of company B.

by bambax 2y ago

vidarh 2y ago

SQlite on the server is worth it when the server side is an application and you treat SQlite as a per user datastore for an application that is mostly per user, or where shared data across users can be treated as a queuing/async messaging problem. Decomposing a system to treat it as a per-user application working on per-user data works for a whole lot of problems and solves a whole lot of problems. It scales effortlessly. I've done that kind of system even with flat files, handling millions of user accounts on a set of servers with an aggregate capacity less than my current $300 mini PC.

egeozcan 2y ago
> where shared data across users can be treated as a queuing/async messaging problem Really asking: Why would you want that complexity when a single database that provides ACID with multiple users can provide that out of the box? Sqlite shines when it's on the edge (be it a fat desktop app, or a thin-client which can hold a cache, or any other modern definition of edge), and while I absolutely adore the tech[0], I don't get why people try to repurpose it to simplify server architecture, especially in this day and age even the most database with the most complex installation is one docker pull away. [0]: I honestly think Sqlite is one of the best software ever developed.
- vidarh 2y ago
  You're assuming complexity, whereas my experience is that it reduces complexity. E.g. you get infinite scalability. You get no need to ensure all your queries are scoped correctly, because they can't directly access other data. Most large systems ends up with async processing and/or eventually sharding anyway, and then you get to restructure around those flows anyway, while still having the complexities of semi-centralised databases. The world is async - once you embrace async designs from the start and design your UX around accepting that there are very situation you need global serialisation of events, it gets a lot simpler. > Sqlite shines when it's on the edge (be it a fat desktop app, or a thin-client which can hold a cache, or any other modern definition of edge), Which is not materially different from a server spinning up to serve requests for a single user. > I don't get why people try to repurpose it to simplify server architecture, especially in this day and age even the most database with the most complex installation is one docker pull away. Because maintaining a large database cluster and scaling it to large user sets, and ensure the application software is using it correctly to avoid problems is a lot of work. I built my first datastore-per-user system in '99, without the luxury of SQlite. It was a revelation. It was a fundamentally so much more pleasant way to work. In some cases, datastore-per-user is the right pattern. In other cases datastore-per-tenant. In some cases, a big RDBMS is still the better option. But don't dismiss the former two without actually exploring it. Over the years, I'm finding it fits more and more places, especially thanks to SQlite.
HelloNurse 2y ago
Realistic applications can be mostly per-user data, but with a residue of global data (e.g. the tables that list all those beautifully segregated users). Even if per-user databases work well and can be managed simply (e.g. doing backups by briefly taking them offline when idle and copying entire files) the global part usually has more requirements (e.g. cannot be shut down for maintenance, requiring "live" backups) and requires greater complexity (for example sharing it through a traditional DBMS network server or through application-layer services).
- vidarh 2y ago
  There's rarely any reason for not being able to shut them down, because you can trivially replicate. E.g. I've built systems where the only global piece of data was which backend held which user, and that data was trivially lazily updated via gossip-style protocols. Login requests were broadcast, and the backends responded, and the central services acted only as "backups" storing the most up to date info seen in case a backend happened to be unavailable. The whole thing was trivial to set up. (And you can back up sqlite "live" just fine with some care if you want to, or things like litestream).
foobarbaz33 2y ago
That's a pretty good take. Would make sense for a whole range of apps focused on 1 user or client. Accounting software is personal, company A doesn't need to query/join with data of company B.