Yeah, it works when it works. Just that in many cases you'll run into scenarios with it when it completely doesn't or is missing something crucial and we'll get another prodigal son story about going back to Postgres.
SQLite the only database you will ever need in most cases (2021)
21–30 of 378 posts
Re: SQLite the only database you will ever need in most cases (2021)
#22>The only time you need to consider a client-server setup is: Where you have multiple physical machines accessing the same database server over a network. In this setup you have a shared database between multiple clients. This caveat covers "most cases". If there's only a single machine, then any data stored is not durable. Additionally, to my knowledge SQLite doesn't have a solution for durability other than asynchr…
https://litestream.io/ does streaming replication to S3 (or similar service). With this, you probably have better data durability than a small database cluster.
even with litestream, how do you do deployments? do you just terminate the process and re-launch it on the same machine?
Re: SQLite the only database you will ever need in most cases (2021)
#23I like sqlite as much as the next guy but it's built-in datatypes are limited. Things like arrays, UUIDs, geometry stuff, JSON, etc. Sure you can store more advanced stuff as blobs or text but then you have to mess around with deserializing it in the host language and you lose the ability to query it directly in the db engine.
That's true - but I think it goes back to "you will need." It's nice to query these things in the DB, but for most users you can just load everything based on associations and sort it out in memory. It's less efficient, but most of the time you will be ok.
Re: SQLite the only database you will ever need in most cases (2021)
#24SQLite — works 80% of the time — every time.
Excuse me, are you disparaging our lord and saviour SQLite? I'll have you know that SQLite works perfectly for all use cases. If you use it in production you don't even need SLAs because it literally works 100% of the time.
Re: SQLite the only database you will ever need in most cases (2021)
#25I like sqlite as much as the next guy but it's built-in datatypes are limited. Things like arrays, UUIDs, geometry stuff, JSON, etc. Sure you can store more advanced stuff as blobs or text but then you have to mess around with deserializing it in the host language and you lose the ability to query it directly in the db engine.
Also the moment you need two databases for high availability or access to it over the network you end up inventing mysql but using sqlite rather than innodb.
Re: SQLite the only database you will ever need in most cases (2021)
#26I like sqlite as much as the next guy but it's built-in datatypes are limited. Things like arrays, UUIDs, geometry stuff, JSON, etc. Sure you can store more advanced stuff as blobs or text but then you have to mess around with deserializing it in the host language and you lose the ability to query it directly in the db engine.
SQLite has a bunch of JSON features though, doesn't it? https://www.sqlite.org/json1.html Are there gaps?
Re: SQLite the only database you will ever need in most cases (2021)
#27>The only time you need to consider a client-server setup is: Where you have multiple physical machines accessing the same database server over a network. In this setup you have a shared database between multiple clients. This caveat covers "most cases". If there's only a single machine, then any data stored is not durable. Additionally, to my knowledge SQLite doesn't have a solution for durability other than asynchr…
people have been providing acid transaction semantics on single machines for 50 years do you think ims/db ran on a cluster the d in acid stands for durability you're talking about pitr, which is what mysql semi-sync provides (and afaik you are correct that sqlite doesn't offer pitr)
That's not what the parent means by durability, they mean having your data survive any one of your machines being instantly nuked from orbit at the most inconvenient possible time.
Just having sync replication is enough, doesn't have to be fancy like semi-sync.
Re: SQLite the only database you will ever need in most cases (2021)
#28> The only time you need to consider a client-server setup is: Where you have multiple physical machines accessing the same database server over a network. In this setup you have a shared database between multiple clients.
Am I misunderstanding this or is this not the vast, vast majority of all cases?
Re: SQLite the only database you will ever need in most cases (2021)
#29This sentiment pops up regularly on HN, and I've seen at least one article per month for the past few months, but the trouble is, none of them seem to help you actually deploy it. They assume you're comfortable spinning up public web servers.
If you want to use a PaaS to deploy an app, because you don't want to spend your time learning to be a sysadmin, then all the tutorials are going to put you on the Postgres path, because that's what's supported. (Of course, you'll then end up paying $15+/mo for Postgres, which is hilarious for most hobby projects storing 50MB of data.) But in reality, you could just scale vertically on one machine and be completely fine. No need for "distributed" anything, in theory.
I took a shot at productionizing SQLite here as an experiment: https://cheapo.onrender.com/ But I'm not sure I did it right, because I don't have much experience working below the level of a PaaS. I'm an application developer and I do not want to become a release engineer. I resent even having to learn Docker. :-)
Anyway, if somebody wants to nitpick my Flask+SQLite deployment, I'd really appreciate it, because it seems really silly that people have to keep writing this stuff from scratch when 90% of hobby sites have the exact same needs. And the Fly.io/Render configs would apply just as well to Node, Ruby, etc. https://github.com/irskep/cheapo_website
Edit: Somebody got mad at the Docker bit. I tossed it in for effect, but I promise you I don't hate learning new skills. I wish people would recognize that weekend coding projects should be fun, and sometimes that means avoiding certain kinds of things that a person experiences as difficult or frustrating. Arguing on the internet sucks, who knew?
Re: SQLite the only database you will ever need in most cases (2021)
#30Earlier quoted context omitted.
people have been providing acid transaction semantics on single machines for 50 years do you think ims/db ran on a cluster the d in acid stands for durability you're talking about pitr, which is what mysql semi-sync provides (and afaik you are correct that sqlite doesn't offer pitr)
That's not what the parent means by durability, they mean having your data survive any one of your machines being instantly nuked from orbit at the most inconvenient possible time. Just having sync replication is enough, doesn't have to be fancy like semi-sync.
Michael, is that you?