3 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 applicatio

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.

claytongulick 2y ago
It's absolutely supported for that use case if you can get away with single-writer multi-reader architecture, which IMHO most medium sized applications can. [1] [1] https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html (see Server Side Database)
blopker 2y ago
Maybe it's not for you, but the "everything is a string" thing is just the default. SQLite has STRICT table option since 2021 that people really should be using if possible: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html This brings strict types that people expect from the other server-based databases.
dudus 2y ago
And even being this simple and having clear challenges it's worth using it for the latency gains.
- strken 2y ago
  When is it a good choice, and when is it not? Just saying that it's "worth using it" gives the impression that it's a good choice for all levels of traffic, all security requirements, all isolation requirements (yeah it's serialisable, but this comes back to levels of traffic), the kind of use case where you need SKIP LOCKED instead of just a single writer, etc.
SQLite 2y ago
Just to clear up the error in the parent post: SQLite has native blobs, floats, and integers, not just strings. It doesn't have a bunch of other types for things like dates and JSON - you just represent those things using the native times of integer, float, string or blob. But it is not limited to only strings. This has been true for 20 years.
- VWWHFSfQ 2y ago
  But it has functions for operating on dates and times though, right? date(), strftime() etc. Those just silently fail with a NULL if the value in the tuple isn't something that it recognizes. So it seems like it _tries_ to do the things that people want, but only half-heartedly, and just fails silently if it can't. Was the column actually NULL? Or just a wrong string format? Who knows? The point is, SQLite is very "flimsy". And that's perfectly fine for what it is (or what it intends to be). But if I try to insert or retrieve a date() or do strftime() on a string that isn't a valid date/time, don't just return a NULL. That's where the "flimsiness" makes it a deal-breaker for anything where integrity is important and the database itself is supposed to be the authority.
lupusreal 2y ago
> It's great for what it is, a tiny embeddable client-side application database. Like an address book on your phone Size is no real concern, if the user of a client side application has many gigabytes of data a sqlite database is still well suited for the role. There's no shoehorning, it just works.

claytongulick 2y ago

It's absolutely supported for that use case if you can get away with single-writer multi-reader architecture, which IMHO most medium sized applications can. [1] [1] https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html (see Server Side Database)

blopker 2y ago

Maybe it's not for you, but the "everything is a string" thing is just the default. SQLite has STRICT table option since 2021 that people really should be using if possible: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html This brings strict types that people expect from the other server-based databases.

dudus 2y ago

And even being this simple and having clear challenges it's worth using it for the latency gains.

strken 2y ago
When is it a good choice, and when is it not? Just saying that it's "worth using it" gives the impression that it's a good choice for all levels of traffic, all security requirements, all isolation requirements (yeah it's serialisable, but this comes back to levels of traffic), the kind of use case where you need SKIP LOCKED instead of just a single writer, etc.

SQLite 2y ago

Just to clear up the error in the parent post: SQLite has native blobs, floats, and integers, not just strings. It doesn't have a bunch of other types for things like dates and JSON - you just represent those things using the native times of integer, float, string or blob. But it is not limited to only strings. This has been true for 20 years.

VWWHFSfQ 2y ago
But it has functions for operating on dates and times though, right? date(), strftime() etc. Those just silently fail with a NULL if the value in the tuple isn't something that it recognizes. So it seems like it _tries_ to do the things that people want, but only half-heartedly, and just fails silently if it can't. Was the column actually NULL? Or just a wrong string format? Who knows? The point is, SQLite is very "flimsy". And that's perfectly fine for what it is (or what it intends to be). But if I try to insert or retrieve a date() or do strftime() on a string that isn't a valid date/time, don't just return a NULL. That's where the "flimsiness" makes it a deal-breaker for anything where integrity is important and the database itself is supposed to be the authority.

lupusreal 2y ago

> It's great for what it is, a tiny embeddable client-side application database. Like an address book on your phone Size is no real concern, if the user of a client side application has many gigabytes of data a sqlite database is still well suited for the role. There's no shoehorning, it just works.