5 ms·

SQLite is an awesome tool, but I wish we’d stop trying to force it into situations it wasn’t made for. It doesn’t belong anywhere near a web app deployment that

SQLite is an awesome tool, but I wish we’d stop trying to force it into situations it wasn’t made for. It doesn’t belong anywhere near a web app deployment that currently or will span two or more machines. Once you have a “distributed system” and the complexities of a DR scenario, reach for a tool that was built for such. You can’t go wrong with PG/MySQL. You can go very wrong with a VC-hyped replicated file system from a company known for outages.

pookha 3y ago
I've seen people use a few hundred lines of javascript to create full blown distributed systems that are offline and end to end encrypted and backed by sqlite. They only use the server as a glorified caching system to sync to\from. It's esoteric (logical clocks, conflict free replicated data types), but that's a hell of a lot more distributed than a client-server architecture and it's backed entirely with sqlite.
- beoberha 3y ago
  I mean this is kinda what I’m trying to get at. You need to build all this bespoke infra in order to fit SQLite into the round role where a database with native replication is a far more proven and battle tested solution.
  - pookha 3y ago
    When I say a few hundred lines of javascript (and I'm no fan of javascript) I literally mean a few hundred lines of javascript. No frameworks. No complicated server logic. Just CRDTs and a hybrid logical clock (written in fifty or so lines of JS) and a server running a little message bus...I once heard JLong talk about this and demo'd the capability to a military contractor in an office in old town alexandria and they thought it was hilarious. In just a couple crappy javascript files I was syncing data offline between twenty five different clients regardless of internal system clock speeds (I used a laptop that was running hot and had a fast system clock) or other bullshit. It was magical. And all of that was ran through SQLite. I even used some SQlite PostGIS-like extension during the demo. So yes, sqlite can be distributed in the purest sense of the word.
corobo 3y ago
Yeah it kinda feels like we're just going back to how things were before everyone decided 12-factor was the new hotness. SQLite has its uses for sure - just not on a system that might need horizontal scaling, HA, or separate worker processes. Sure, sure, you can duct tape a few SQLites together but then you're dealing with this > LiteFS only allows a single node to be the primary at any given time. The primary node is the only one that can write data to the database. The other nodes are called replicas and they provide a read-only copy. Might as well have just set up a separate DB server, less faff. At the point where you need to scale MySQL/postgres beyond vertical scaling you can probably afford to pay someone to do it for you
chasil 3y ago
You can certainly span two or more machines with provisos that you must not enable WAL mode and there can be only one writer. I have never really examined how this performs, and it would be best if all access was explicitly via SQLITE3_OPEN_READONLY.
jampekka 3y ago
What share of web apps actually need to span two or more machines? Something like 0.1%?
- 0xbadcafebee 3y ago
  Running instances, not machines. There's like 15 years of architectural advancement in running web apps that says that more than one copy of stateless software is the best practice. SQLite was not designed to be used for web apps. HN's bizarre groupthink occasionally decides to subvert architectural norms for fun, and any blog post that's trending on the front page suddenly counts as "good advice". Even if it's literally insane, if it's on the front page it's considered sage wisdom, and naysayers are downvoted for being negative about the insanity.
  - simonw 3y ago
    Sometimes it's OK to reconsider. Those 15 years of architectural norms started at a time where a multi-core 2GB server was an expensive luxury! We know a LOT more today about building web applications than we did 15 years ago. That's why some of us are ready to say that for a lot of applications, a single server running SQLite (with streaming backups to S3 via Litestream) is actually a pretty great option.
    - deleted 3y ago
      [deleted]
    - 0xbadcafebee 3y ago
      I don't mean use 15-year old patterns, I mean we've learned over 15 years what's good practice and what isn't. We used to use .csv and .ini files that way. After that we used BerkeleyDB, since it was built into so many tools. Or serialized objects. If you got fancy you'd read/write them to a temporary filesystem, and occasionally sync the files to NFS; if the app restarts, sync the files from NFS back to temporary storage, or if the temporary file isn't there, read it from NFS and write it to temporary storage. Today, if you have to write a web app that has dynamic content and writes and reads user data, you can do it a bunch of ways: - "Mostly-JS": Write your app in JavaScript. Some hosted provider out there allows you to submit data to some backend app, and read it from the backend app. (This, by the way, is how CGI-BIN web apps worked 20 years ago) - "Mostly-CGI": Use some managed hosting provider to host a web app with some framework they support, like Wordpress, or Django, or something else. Typically they bundle a networked relational database with it, but I suppose you could trade that out for an sqlite file. Probably wouldn't support Litestream but you could probably have a cron job copy the file to s3. - "Mostly-standalone": Buy some virtual machine or physical server, set it up, maintain it, write your web app, run a web server that runs your web app, write the file to local disk, set up Litestream. Never touch it, patch it, upgrade it, etc, so it always has security vulns, but it will just keep running, so it will be much easier to get going. - "Mostly-managed": Pay for some hosted provider to give you the ability to run your app in containers. You build it, you push it, and then set up the thing to run it. Like the previous option, you can opt to never patch or upgrade anything so there's nothing to maintain. But since you can use a bunch of SaaS providers to build, test, and deploy automatically, it's way less to maintain and much more automated. Again, typically you'd use a networked relational database, since they're largely designed to run one app at a time, but you can customize them to have some background task try to upload your sqlite file out of the container or a sidecar. - "Completely managed": You upload your code. They completely manage the versions of your app platform, doing patches, upgrades, running different instances of your app, databases, web servers, whatever you need. You basically don't have to think about anything but code. You could use sqlite but there's probably no way to back it up. If you don't need to read and write user data, you don't need a web app at all. A static site works just fine. Hell, you don't even need a static site generator anymore, just link to the right css or js files in your html to DRY up your static content.

pookha 3y ago

I've seen people use a few hundred lines of javascript to create full blown distributed systems that are offline and end to end encrypted and backed by sqlite. They only use the server as a glorified caching system to sync to\from. It's esoteric (logical clocks, conflict free replicated data types), but that's a hell of a lot more distributed than a client-server architecture and it's backed entirely with sqlite.

beoberha 3y ago
I mean this is kinda what I’m trying to get at. You need to build all this bespoke infra in order to fit SQLite into the round role where a database with native replication is a far more proven and battle tested solution.
- pookha 3y ago
  When I say a few hundred lines of javascript (and I'm no fan of javascript) I literally mean a few hundred lines of javascript. No frameworks. No complicated server logic. Just CRDTs and a hybrid logical clock (written in fifty or so lines of JS) and a server running a little message bus...I once heard JLong talk about this and demo'd the capability to a military contractor in an office in old town alexandria and they thought it was hilarious. In just a couple crappy javascript files I was syncing data offline between twenty five different clients regardless of internal system clock speeds (I used a laptop that was running hot and had a fast system clock) or other bullshit. It was magical. And all of that was ran through SQLite. I even used some SQlite PostGIS-like extension during the demo. So yes, sqlite can be distributed in the purest sense of the word.

corobo 3y ago

Yeah it kinda feels like we're just going back to how things were before everyone decided 12-factor was the new hotness. SQLite has its uses for sure - just not on a system that might need horizontal scaling, HA, or separate worker processes. Sure, sure, you can duct tape a few SQLites together but then you're dealing with this > LiteFS only allows a single node to be the primary at any given time. The primary node is the only one that can write data to the database. The other nodes are called replicas and they provide a read-only copy. Might as well have just set up a separate DB server, less faff. At the point where you need to scale MySQL/postgres beyond vertical scaling you can probably afford to pay someone to do it for you

chasil 3y ago

You can certainly span two or more machines with provisos that you must not enable WAL mode and there can be only one writer. I have never really examined how this performs, and it would be best if all access was explicitly via SQLITE3_OPEN_READONLY.

jampekka 3y ago

What share of web apps actually need to span two or more machines? Something like 0.1%?

0xbadcafebee 3y ago
Running instances, not machines. There's like 15 years of architectural advancement in running web apps that says that more than one copy of stateless software is the best practice. SQLite was not designed to be used for web apps. HN's bizarre groupthink occasionally decides to subvert architectural norms for fun, and any blog post that's trending on the front page suddenly counts as "good advice". Even if it's literally insane, if it's on the front page it's considered sage wisdom, and naysayers are downvoted for being negative about the insanity.
- simonw 3y ago
  Sometimes it's OK to reconsider. Those 15 years of architectural norms started at a time where a multi-core 2GB server was an expensive luxury! We know a LOT more today about building web applications than we did 15 years ago. That's why some of us are ready to say that for a lot of applications, a single server running SQLite (with streaming backups to S3 via Litestream) is actually a pretty great option.
  - deleted 3y ago
    [deleted]
  - 0xbadcafebee 3y ago
    I don't mean use 15-year old patterns, I mean we've learned over 15 years what's good practice and what isn't. We used to use .csv and .ini files that way. After that we used BerkeleyDB, since it was built into so many tools. Or serialized objects. If you got fancy you'd read/write them to a temporary filesystem, and occasionally sync the files to NFS; if the app restarts, sync the files from NFS back to temporary storage, or if the temporary file isn't there, read it from NFS and write it to temporary storage. Today, if you have to write a web app that has dynamic content and writes and reads user data, you can do it a bunch of ways: - "Mostly-JS": Write your app in JavaScript. Some hosted provider out there allows you to submit data to some backend app, and read it from the backend app. (This, by the way, is how CGI-BIN web apps worked 20 years ago) - "Mostly-CGI": Use some managed hosting provider to host a web app with some framework they support, like Wordpress, or Django, or something else. Typically they bundle a networked relational database with it, but I suppose you could trade that out for an sqlite file. Probably wouldn't support Litestream but you could probably have a cron job copy the file to s3. - "Mostly-standalone": Buy some virtual machine or physical server, set it up, maintain it, write your web app, run a web server that runs your web app, write the file to local disk, set up Litestream. Never touch it, patch it, upgrade it, etc, so it always has security vulns, but it will just keep running, so it will be much easier to get going. - "Mostly-managed": Pay for some hosted provider to give you the ability to run your app in containers. You build it, you push it, and then set up the thing to run it. Like the previous option, you can opt to never patch or upgrade anything so there's nothing to maintain. But since you can use a bunch of SaaS providers to build, test, and deploy automatically, it's way less to maintain and much more automated. Again, typically you'd use a networked relational database, since they're largely designed to run one app at a time, but you can customize them to have some background task try to upload your sqlite file out of the container or a sidecar. - "Completely managed": You upload your code. They completely manage the versions of your app platform, doing patches, upgrades, running different instances of your app, databases, web servers, whatever you need. You basically don't have to think about anything but code. You could use sqlite but there's probably no way to back it up. If you don't need to read and write user data, you don't need a web app at all. A static site works just fine. Hell, you don't even need a static site generator anymore, just link to the right css or js files in your html to DRY up your static content.