Hacker News Reader

Comment by VWWHFSfQ | Hacker News Reader

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.

View on HN

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

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

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.

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

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.

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.

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.

Au contraire, SQLite makes it very easy to write extensive automated testing for your application, since you can spin up in-memory DBs per test with minimal overhead. This makes your application much more robust.

Of course, once you come to that realization, then you realize that it is all one in the same and that there isn't any magic going on, which then realizes that business constraints can go anywhere in your application and be written by anyone.

I suspect what you are really trying to say is that you trust Hipp more than you trust yourself to get the constraints right. Indeed, if you screw it up you're in for a world of hurt, so you are right to be cautious. But, if you have more trust in a random stranger who has no care for your data than you do yourself to implement it for you, perhaps you shouldn't be writing any code at all? Software development certainly isn't for everyone.

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

What are "real types and the associated constraints"? It has strict tables:

https://www.sqlite.org/stricttables.html

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

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

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.

Reply on news.ycombinator.com