6 ms·

I built a rules engine around SQLite queries for the last product I was working on. This article was the reason. You can run an unbelievable # of select statem

I built a rules engine around SQLite queries for the last product I was working on. This article was the reason. You can run an unbelievable # of select statements per unit time against a SQLite database. I think of it like a direct method invocation to members of some List<T> within the application itself. Developers who still protest SQLite are really sleeping on the latency advantages. 2-3 orders of magnitude are really hard to fight against. This opens up entirely new use cases that hosted solutions cannot consider. Databases that are built into the application by default are the future. Our computers are definitely big enough. There are many options for log replication (sync & async), as well as the obvious hypervisor snapshot approach (WAL can make this much less dangerous).

norir 2y ago
> Databases that are built into the application by default are the future. Our computers are definitely big enough. Interesting point. It's easy to forget that in the past local storage was small and expensive so that it was necessary to have a separate database machine.
- nevdka 2y ago
  It wasn't just storage. In the 90s, most servers were single core. Databases already had network interfaces so they could be shared by non-web applications running on desktops, and so were the natural place to split a website over multiple machines. This turned into the received wisdom of always putting your database on a separate machine, despite processing power and storage latency improving so much since the 486 and pentium days that network overhead dominated response times.
crazygringo 2y ago
> Databases that are built into the application by default are the future. No they're not, because for web servers, you have many (tens? hundreds? thousands?) of web servers that all need to talk to a single database. And you sure don't want to replicate and sync a gigantic database across each web server -- that would be a disaster. While for local apps on your phone or computer, usage of SQLite is already widespread -- it's not the future, it's here. And cloud-connected apps that work offline already do some sort of sync for that.
- secondcoming 2y ago
  We have a giant Postgres DB, but instead of having every machine connect directly to it, we have a job that creates a smaller SQLite cache of relevant data and that’s pushed out to the machines who then reload that on the fly. All this depends on your data being somewhat shardable of course.
  - aynyc 2y ago
    You can’t really sync the data in real time tho. Transactions still need to hit your Postgres, and then you reload. You might as well do write thru cache.
  - binary132 2y ago
    Yeah, this sounds pretty not great if you need ACID consensus or locking between multiple machines. If what you need is to read a somewhat static state, fine, great.
- Nathanba 2y ago
  Most people don't have tens or thousands of webservers and could be running with a more efficient inprocess database that syncs to another networked readonly replica for a very long time. I'm very surprised that MySQL or Postgres still don't have an inprocess mode because it's such an obvious win and on a technical coding level it should (naively) be very easy to switch from network calls to direct calls.
  - crazygringo 2y ago
    But it's standard to have at least 2 webservers. And syncing databases is just waaay harder than having a dedicated one. It really seriously is a pain to configure and debug and manage storage and performance requirements around and dealing with scaling and managing failure modes and so forth. If you can even find someone who has the skills to do it properly so you never lose data. It's far, far easier to just write normal SQL queries that don't have to make hundreds of requests, than to deal with syncing a database across all your webservers.
corytheboyd 2y ago
> Databases that are built into the application by default are the future. That’s a bit overzealous. SQLite is great, but it’s not a replacement for a hosted database. Not all data can live on the client. Use SQLite when you have the right use case for it (offline desktop app, etc.)
- gwbas1c 2y ago
  There are ways to use a hosted SQL, or from a cloud application. I personally haven't used it. What I am curious about is if "Many Small Queries Are Efficient in SQLite" when using the various hosted flavors of SQLite.
  - claytongulick 2y ago
    Nope. You get back to the roundtrip problem.
    - infamia 2y ago
      The SQLite db can easily live on the same machine as the app server.
      - deleted 2y ago
        [deleted]
      - danielheath 2y ago
        > using the various hosted flavors of SQLite
        closeparen 2y ago
        I think the "hosted flavors of SQLite" would be doing the type of trick Litestream does: streaming the WAL to other copies of the database. The copy you interact with is still local.
- bob1029 2y ago
VWWHFSfQ 2y ago
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.
emseetech 2y ago
| Databases that are built into the application by default are the future. I'm not sold on client side, but a lot of great work is being done on putting the db and application on the same server, between SQLite replication and other approaches like SpacetimeDB. I'm interested to see where it goes.
- vlovich123 2y ago
  SQLite underpins nearly every on-disk storage mechanism at Apple and Google as the OS level. You may not be sold but systems programmers elsewhere are.
  - emseetech 2y ago
    What works for Apple and Google is rarely the best approach for the rest of us. My concern is over-reliance on client side storage and the complexity it adds to have records of state on both client and server.
    - vsuperpower2021 2y ago
      Sqlite isn't something that works for only apple or google. Point is it's sufficient for them, simple, and more than sufficient for you. He's explaining to you that this is something that has already happened as you're unaware.
      - emseetech 2y ago
        I use SQLite all the time, I said in another comment it's my go-to for server-side green field projects. But there's a lot of push to create data solutions that are fully bidirectional between client and server, or to even make them client-driven in an awkward peer-to-peer model, and I'm not sold on either pattern.
        lupusreal 2y ago
        Sqlite is at it's best in "pure client" situations where there is no server and there are no P2P things going on. Just the application, the user, and their data on their own drive with sqlite.
mschuster91 2y ago
> Databases that are built into the application by default are the future. Our computers are definitely big enough. That assumes local applications themselves are the future, and that assumption has grown ever weaker and weaker with everyone and their dog going cloud-only (or starting as a SaaS in the first place) to grab all the sweet sweet recurring subscription revenue.
timClicks 2y ago
> Databases that are built into the application by default are the future. Our computers are definitely big enough. Knowing this, I wonder if there will be another wave of object databases at some point.
grishka 2y ago
> Databases that are built into the application by default are the future. It still depends a lot. If your system is high-load enough, you may want sharding, for example.
- kllrnohj 2y ago
  Wouldn't a hosted database really only "make sense" if you don't want sharding? It's a lot easier to shard at the front end before it makes it to any business logic than to do that and also shard the database. It's when you don't want to deal with database sharding at all that you'd want all application instances to hit the same hosted database.
  - grishka 2y ago
    Sorry I forgot that people do "front end" these days. I was thinking of the old-school server-side-rendered web apps that load almost instantly, not the modern spinner-fest ones.
binary132 2y ago
I often find myself thinking about what a database like SQLite that might look like if it had a native API instead of a query language. I guess it wouldn’t be so different from a dataframe API, but with persistence, locking, and relational features, and I guess an mmap under the hood.

norir 2y ago

> Databases that are built into the application by default are the future. Our computers are definitely big enough. Interesting point. It's easy to forget that in the past local storage was small and expensive so that it was necessary to have a separate database machine.

nevdka 2y ago
It wasn't just storage. In the 90s, most servers were single core. Databases already had network interfaces so they could be shared by non-web applications running on desktops, and so were the natural place to split a website over multiple machines. This turned into the received wisdom of always putting your database on a separate machine, despite processing power and storage latency improving so much since the 486 and pentium days that network overhead dominated response times.

crazygringo 2y ago

> Databases that are built into the application by default are the future. No they're not, because for web servers, you have many (tens? hundreds? thousands?) of web servers that all need to talk to a single database. And you sure don't want to replicate and sync a gigantic database across each web server -- that would be a disaster. While for local apps on your phone or computer, usage of SQLite is already widespread -- it's not the future, it's here. And cloud-connected apps that work offline already do some sort of sync for that.

secondcoming 2y ago
We have a giant Postgres DB, but instead of having every machine connect directly to it, we have a job that creates a smaller SQLite cache of relevant data and that’s pushed out to the machines who then reload that on the fly. All this depends on your data being somewhat shardable of course.
- aynyc 2y ago
  You can’t really sync the data in real time tho. Transactions still need to hit your Postgres, and then you reload. You might as well do write thru cache.
- binary132 2y ago
  Yeah, this sounds pretty not great if you need ACID consensus or locking between multiple machines. If what you need is to read a somewhat static state, fine, great.
Nathanba 2y ago
Most people don't have tens or thousands of webservers and could be running with a more efficient inprocess database that syncs to another networked readonly replica for a very long time. I'm very surprised that MySQL or Postgres still don't have an inprocess mode because it's such an obvious win and on a technical coding level it should (naively) be very easy to switch from network calls to direct calls.
- crazygringo 2y ago
  But it's standard to have at least 2 webservers. And syncing databases is just waaay harder than having a dedicated one. It really seriously is a pain to configure and debug and manage storage and performance requirements around and dealing with scaling and managing failure modes and so forth. If you can even find someone who has the skills to do it properly so you never lose data. It's far, far easier to just write normal SQL queries that don't have to make hundreds of requests, than to deal with syncing a database across all your webservers.

corytheboyd 2y ago

> Databases that are built into the application by default are the future. That’s a bit overzealous. SQLite is great, but it’s not a replacement for a hosted database. Not all data can live on the client. Use SQLite when you have the right use case for it (offline desktop app, etc.)

gwbas1c 2y ago
There are ways to use a hosted SQL, or from a cloud application. I personally haven't used it. What I am curious about is if "Many Small Queries Are Efficient in SQLite" when using the various hosted flavors of SQLite.
- claytongulick 2y ago
  Nope. You get back to the roundtrip problem.
  - infamia 2y ago
    The SQLite db can easily live on the same machine as the app server.
    - deleted 2y ago
      [deleted]
    - danielheath 2y ago
      > using the various hosted flavors of SQLite
      - closeparen 2y ago
        I think the "hosted flavors of SQLite" would be doing the type of trick Litestream does: streaming the WAL to other copies of the database. The copy you interact with is still local.
bob1029 2y ago

VWWHFSfQ 2y ago

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.

emseetech 2y ago

| Databases that are built into the application by default are the future. I'm not sold on client side, but a lot of great work is being done on putting the db and application on the same server, between SQLite replication and other approaches like SpacetimeDB. I'm interested to see where it goes.

vlovich123 2y ago
SQLite underpins nearly every on-disk storage mechanism at Apple and Google as the OS level. You may not be sold but systems programmers elsewhere are.
- emseetech 2y ago
  What works for Apple and Google is rarely the best approach for the rest of us. My concern is over-reliance on client side storage and the complexity it adds to have records of state on both client and server.
  - vsuperpower2021 2y ago
    Sqlite isn't something that works for only apple or google. Point is it's sufficient for them, simple, and more than sufficient for you. He's explaining to you that this is something that has already happened as you're unaware.
    - emseetech 2y ago
      I use SQLite all the time, I said in another comment it's my go-to for server-side green field projects. But there's a lot of push to create data solutions that are fully bidirectional between client and server, or to even make them client-driven in an awkward peer-to-peer model, and I'm not sold on either pattern.
      - lupusreal 2y ago
        Sqlite is at it's best in "pure client" situations where there is no server and there are no P2P things going on. Just the application, the user, and their data on their own drive with sqlite.

mschuster91 2y ago

> Databases that are built into the application by default are the future. Our computers are definitely big enough. That assumes local applications themselves are the future, and that assumption has grown ever weaker and weaker with everyone and their dog going cloud-only (or starting as a SaaS in the first place) to grab all the sweet sweet recurring subscription revenue.

timClicks 2y ago

> Databases that are built into the application by default are the future. Our computers are definitely big enough. Knowing this, I wonder if there will be another wave of object databases at some point.

grishka 2y ago

> Databases that are built into the application by default are the future. It still depends a lot. If your system is high-load enough, you may want sharding, for example.

kllrnohj 2y ago
Wouldn't a hosted database really only "make sense" if you don't want sharding? It's a lot easier to shard at the front end before it makes it to any business logic than to do that and also shard the database. It's when you don't want to deal with database sharding at all that you'd want all application instances to hit the same hosted database.
- grishka 2y ago
  Sorry I forgot that people do "front end" these days. I was thinking of the old-school server-side-rendered web apps that load almost instantly, not the modern spinner-fest ones.

binary132 2y ago

I often find myself thinking about what a database like SQLite that might look like if it had a native API instead of a query language. I guess it wouldn’t be so different from a dataframe API, but with persistence, locking, and relational features, and I guess an mmap under the hood.