4 ms·

> 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

by 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.
- petcat 2y ago
  I think there's a new generation of developers that don't want to use "no sql" databases anymore (MongoDB, etc.) I think that's why we're starting to see a surge in people wanting to run SQLite as their backend database. It's similarly simple to start out with, and also similarly flimsy when dealing with actual data integrity. Very limited types, limited/uncomplicated isolation options, ref integrity disabled by default (Mongo DB also disables things by default that hurts their benchmarks).
  - tptacek 2y ago
    No, you're seeing a surge in interest for SQLite because people like relational databases, but the n-tier architecture is sometimes not the right solution for the problems people have. And again: many of your arguments have been applied to MySQL, but nobody can with a straight face say it's not a "real" backend database. (To a first approximation ~nobody is interested in SQLite because it lacks correctness or rigid typing features; what's interesting about SQLite is not what was interesting about schemaless databases, but rather the ability to ship backend apps without a separate database tier.) Again: I think you need to snap out of the idea that n-tier architectures are axiomatically optimal for all backend applications. They often are! But not all the time.
    - petcat 2y ago
      I think that most applications are written for their database. Their database defines their application. If you write your application on a flimsy database then your application becomes equally flimsy. All of your business constraints become flimsy because your source-of-truth (the database) is flimsy. SQLite is flimsy by design.
      - tptacek 2y ago
        This was the kind of thing people used to say about MySQL before Meta made those arguments look silly, and so they've moved to SQLite as a new target. I like Postgres fine, but it's just a tool, like many others.
biorach 2y ago
> But it's not a real concurrent backend database. It's a client-side database People are successfully using it server-side, in specific situations it appears to be a good fit. > You can accidentally write a string to an int column Yes, you need more validation logic client-side in exchange for the performance gain. It's a trade-off, not a black/white distinction. A strongly typed language can help here.
wolf550e 2y ago
What are "real types and the associated constraints"? It has strict tables: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html

by petcat 2y ago

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.

petcat 2y ago
I think there's a new generation of developers that don't want to use "no sql" databases anymore (MongoDB, etc.) I think that's why we're starting to see a surge in people wanting to run SQLite as their backend database. It's similarly simple to start out with, and also similarly flimsy when dealing with actual data integrity. Very limited types, limited/uncomplicated isolation options, ref integrity disabled by default (Mongo DB also disables things by default that hurts their benchmarks).
- tptacek 2y ago
  No, you're seeing a surge in interest for SQLite because people like relational databases, but the n-tier architecture is sometimes not the right solution for the problems people have. And again: many of your arguments have been applied to MySQL, but nobody can with a straight face say it's not a "real" backend database. (To a first approximation ~nobody is interested in SQLite because it lacks correctness or rigid typing features; what's interesting about SQLite is not what was interesting about schemaless databases, but rather the ability to ship backend apps without a separate database tier.) Again: I think you need to snap out of the idea that n-tier architectures are axiomatically optimal for all backend applications. They often are! But not all the time.
  - petcat 2y ago
    I think that most applications are written for their database. Their database defines their application. If you write your application on a flimsy database then your application becomes equally flimsy. All of your business constraints become flimsy because your source-of-truth (the database) is flimsy. SQLite is flimsy by design.
    - tptacek 2y ago
      This was the kind of thing people used to say about MySQL before Meta made those arguments look silly, and so they've moved to SQLite as a new target. I like Postgres fine, but it's just a tool, like many others.

biorach 2y ago

> But it's not a real concurrent backend database. It's a client-side database People are successfully using it server-side, in specific situations it appears to be a good fit. > You can accidentally write a string to an int column Yes, you need more validation logic client-side in exchange for the performance gain. It's a trade-off, not a black/white distinction. A strongly typed language can help here.

wolf550e 2y ago

What are "real types and the associated constraints"? It has strict tables: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html