8 ms·

I'm still not understanding this push toward using SQLite as a production backend database. It's great for what it is, a tiny embeddable client-side application

I'm still not understanding this push toward using SQLite as a production backend database. It's great for what it is, a tiny embeddable client-side application database. Like an address book on your phone. But even the developers themselves have steadfastly refused to allow it to expand beyond that scope. For instance, they won't add native types for any useful things like dates/times, or uuids. Because that would bloat the code and the size of the embedded object. So you're stuck with "everything is a string". Ref integrity can be enabled, but even those constraint options are very limited. Not sure why people are still trying to shoe-horn it into a role that it's not meant to be in, and not even really supported to be.

randomdata 2y ago
We eventually realized that the "API server" was actually just a DBMS all along. And once that was realized, it was realized that a DBMS sitting beside another DMBS that deals with the exact same data is rather silly, which is now leading to: 1. Let the clients connect to Postgres directly. - or - 2. Cut Postgres out of the picture and double down on the homegrown DMBS. Some are going the #1 route, others #2. Where #2 is opted, SQLite is a convenient engine on which to build upon. It may not be perfect but it is what we have. Keep in mind that this realization on a grand scale (I'm sure some noticed many years ago) is fairly recent, so there is a lot of experimenting going on to figure out what works and what doesn't. It's the natural cycle of computing. What is old is new again. (Replace Postgres with MySQL, MSSQL, Oracle, or other DBMS as you see fit.)
tptacek 2y ago
First off, I don't know that Richard Hipp agrees with you about what roles SQLite is "meant" to be in. Second: the reasons are straightforward: * For read-heavy access patterns, SQLite is crazy fast. * It's fast enough that you can often simplify your database access code; for instance, N+1 queries are often just not a problem in practice. * SQLite removes a whole tier from the N-tier architecture, which in turn removes a whole set of things that can go wrong (and if you've ever managed your own Postgres or MySQL: things do go wrong). It's not a perfect fit for every application, or even the majority of applications, but the push you're seeing is a correction against the pretty clearly false idea that SQLite is well suited only for "tiny embedded client-side application databases".
- petcat 2y ago
  > I don't know that Richard Hipp agrees with you about what roles SQLite is "meant" to be in. If Hipp thought that SQLite was suitable for backend applications where the database is the authority then he would allow real types and the associated constraints. But he won't do that because it complicates the code and bloats the embedded object size. SQLite is great for what it is. But it's not a real concurrent backend database. It's a client-side database. That's all the SQLite developers will ever allow it to be. We can try to layer-on a bunch of stuff like Lite Stream or whatever, and sharding. But the fact is that the core database itself is not, and will never be, suitable for backend applications. You can accidentally write a string to an int column. Will SQLite say no? No. SQLite doesn't care. It returns everything is A-OK! You can query an ISO-8601 string column with date_trunc() and strftime() and it just returns NULL whether there was a value or not, or maybe just because it did't recognize the string in that column (LOL). SQLite is fine. But it's not a real backend database. It's not a replacement for PG.
  - ncruces 2y ago
    > If Hipp thought that SQLite was suitable for backend applications where the database is the authority then he would allow real types and the associated constraints. But he won't do that because it complicates the code and bloats the embedded object size. Totally baseless claim. Advances to the query optimizer complicate code and bloat the binary far more than adding DECIMAL, DATETIME or UUID as types would. The reason types don't change is forward and backward compatibility, and the promise of supporting the current file format and APIs for interacting with it for at least another 25 years.
  - tptacek 2y ago
    What's a "real" "concurrent" "backend" database? It's absolutely not a "client-side" database. Plenty of people use it in backends. Some of them are posting about it on this thread. The correctness arguments apply just as much, if not more so, to MySQL and to document/schemaless databases. Lots of people don't like those databases, but nobody claims they're not "real backend databases". You seem hung up on the idea that "backend" means "n-tier", with a segregated compute/storage tier for the database with networked connectivity to the app server. That architecture is something SQLite will never support, but that is not the only backend architecture.
QuadrupleA 2y ago
Why not do UUIDs as a string or blob? And dates as strings, or integer / real timestamps? It conceptually simplifies things in so many ways that benefit the app developer, not just the sqlite devs and low-spec hardware. Simpler documentation, shorter learning curve, smaller surface area for bugs, smaller binary size, etc. There's a trend to add bloat and complexity to everything in software these days, but I'm so glad that a few projects like SQLite are pushing against that.

randomdata 2y ago

We eventually realized that the "API server" was actually just a DBMS all along. And once that was realized, it was realized that a DBMS sitting beside another DMBS that deals with the exact same data is rather silly, which is now leading to: 1. Let the clients connect to Postgres directly. - or - 2. Cut Postgres out of the picture and double down on the homegrown DMBS. Some are going the #1 route, others #2. Where #2 is opted, SQLite is a convenient engine on which to build upon. It may not be perfect but it is what we have. Keep in mind that this realization on a grand scale (I'm sure some noticed many years ago) is fairly recent, so there is a lot of experimenting going on to figure out what works and what doesn't. It's the natural cycle of computing. What is old is new again. (Replace Postgres with MySQL, MSSQL, Oracle, or other DBMS as you see fit.)

tptacek 2y ago

First off, I don't know that Richard Hipp agrees with you about what roles SQLite is "meant" to be in. Second: the reasons are straightforward: * For read-heavy access patterns, SQLite is crazy fast. * It's fast enough that you can often simplify your database access code; for instance, N+1 queries are often just not a problem in practice. * SQLite removes a whole tier from the N-tier architecture, which in turn removes a whole set of things that can go wrong (and if you've ever managed your own Postgres or MySQL: things do go wrong). It's not a perfect fit for every application, or even the majority of applications, but the push you're seeing is a correction against the pretty clearly false idea that SQLite is well suited only for "tiny embedded client-side application databases".

petcat 2y ago
> I don't know that Richard Hipp agrees with you about what roles SQLite is "meant" to be in. If Hipp thought that SQLite was suitable for backend applications where the database is the authority then he would allow real types and the associated constraints. But he won't do that because it complicates the code and bloats the embedded object size. SQLite is great for what it is. But it's not a real concurrent backend database. It's a client-side database. That's all the SQLite developers will ever allow it to be. We can try to layer-on a bunch of stuff like Lite Stream or whatever, and sharding. But the fact is that the core database itself is not, and will never be, suitable for backend applications. You can accidentally write a string to an int column. Will SQLite say no? No. SQLite doesn't care. It returns everything is A-OK! You can query an ISO-8601 string column with date_trunc() and strftime() and it just returns NULL whether there was a value or not, or maybe just because it did't recognize the string in that column (LOL). SQLite is fine. But it's not a real backend database. It's not a replacement for PG.
- ncruces 2y ago
  > If Hipp thought that SQLite was suitable for backend applications where the database is the authority then he would allow real types and the associated constraints. But he won't do that because it complicates the code and bloats the embedded object size. Totally baseless claim. Advances to the query optimizer complicate code and bloat the binary far more than adding DECIMAL, DATETIME or UUID as types would. The reason types don't change is forward and backward compatibility, and the promise of supporting the current file format and APIs for interacting with it for at least another 25 years.
- tptacek 2y ago
  What's a "real" "concurrent" "backend" database? It's absolutely not a "client-side" database. Plenty of people use it in backends. Some of them are posting about it on this thread. The correctness arguments apply just as much, if not more so, to MySQL and to document/schemaless databases. Lots of people don't like those databases, but nobody claims they're not "real backend databases". You seem hung up on the idea that "backend" means "n-tier", with a segregated compute/storage tier for the database with networked connectivity to the app server. That architecture is something SQLite will never support, but that is not the only backend architecture.

QuadrupleA 2y ago

Why not do UUIDs as a string or blob? And dates as strings, or integer / real timestamps? It conceptually simplifies things in so many ways that benefit the app developer, not just the sqlite devs and low-spec hardware. Simpler documentation, shorter learning curve, smaller surface area for bugs, smaller binary size, etc. There's a trend to add bloat and complexity to everything in software these days, but I'm so glad that a few projects like SQLite are pushing against that.