It's a c library. Other languages will have a library/package/whatever to use it. You point it at a file.
- Will multithreading make it break? (Not with WAL mode, but you have to set it manually, as this article suggests.)
- When using a PaaS, you need to explicitly add a volume and mount it on your server machine, which you might not think to do if you're a brand new bootcamp grad
- You can't(?) run migrations from another process (or at least, people don't seem to talk about doing this), so you need to prevent your server from writing while you run the migration. Even if I'm wrong, and I would love to be, it's frustrating that people don't talk about the completely ordinary need to run migrations on a database.
- Backups just mean copying the file somewhere, which is nice, but you might need to configure that yourself instead of just using somebody's managed Postgres backup feature
So, there is at least some complexity that comes with managing a "real" SQLite web server.
(I'm probably at least 25% wrong on some details above, but that's kind of the point, it's not always easy to figure this stuff out.)
Ok but the article you're replying in the comments to says "SQLite is all you need for nearly everything", and what the comment you're replying to is describing is, to use their very apt word choice, entirely ordinary.
So how do we square this circle of somebody being told both "SQLite is all you need for everything" and "it is not a good option for your totally ordinary use case"?
I think the answer is that you are a different person than the author of the article and you don't agree with the article's claim. Which, great, that was irskep's entire point!
I tend to agree with irskep that this SQLite architecture is really interesting, but I also don't quite get it. There seem to be a lot of missing details for totally bog standard applications, that people frustratingly seem to just not even mention - like the migrations thing, or how to do zero downtime deploys in general, or a few other things - as if those concepts are irrelevant or uncommon. But they aren't, they're important and typical things to think about.
"In contrast to many other database management systems, SQLite is not a client-server database engine, but you actually very rarely need that. If your application software runs on the same physical machine as the database, which is what most small to medium sized web applications does, then you probably only need SQLite."
That's how we square it. It's right there in the article.
(Nearly) zero down time is a common requirement. Can a live backup be made while transactions are in progress? That appears to be the case. What about schema changes? Can columns or indexes be added without interfering with access to the tables in question? And so on.
Are we still talking about small to medium web apps ? I'm sorry but if HN goes down things will be OK. In fact, there is a very large majority of services that can go down go down and things will still be OK.
Rather than applications you've worked on (many of us spend years on end working on a narrow range of applications), consider software you use (most of us flip between multiple applications every day spanning the gamut of uses). Ignore, for a moment, whatever you know about their implementation, and focus on the user-facing functionality.
Do even half of them logically communication between application nodes?
(Consider that there is still a rich class of software that is fully capable of running locally on the "client"'s computer!)
- Browsers
- Messengers
- The telephone directory of your cellphone
- SMS apps
- The sqlite.org Website
- ...
?
* Hobby apps designed for yourself and your friends.
* Hobby apps that you hope will become products but probably won't achieve traction.
* Apps where you're trying a new stack or framework.
* Annotation apps for academia.
Basically, every single app written by people who aren't doing internet facing web dev as their core engineering function.
Also intranet apps as other commenter mentioned.
I'm not sure exactly what you have in mind for annotation apps for academia - things like zotero that run client side? If so, sure, there is a big world of client side software where a database is useful, and I think SQLite (or DuckDB) seems like a no brainer there.
I don't really agree about intranet apps, which are often even more critical to the people using them than an arbitrary consumer app. But I'll grant that for a company that spans a small number of time zones, you can at least have downtime windows outside work hours.
In any case, as I've said all over this thread, the only disagreement here is over what kinds of software is "most". And my intuition for "most" is based on my experience working on and using applications where a lot of effort is made to keep the thing running all the time while still evolving it. Maybe you're all right that "most" software isn't like that.
edit: when I worked as a sysadmin we would have to schedule outages for updating apps that were designed for zero downtime anyways because those were the processes of the organisation.
Even for online business some downtime might be acceptable or even preferred. I used to work for a company that made most of it's money through a website and it would take the site offline for 8 hours a few weeks before important events to run a full scale load test on the production infrastructure.
Another place I worked we had to schedule downtime even when updating applications that were designed for zero downtime.
I have never worked a place that didn't allow for downtime or even heard of such a place other than the big tech companies.
Not only is that a pain for whoever is monitoring the deployment, but now if the deployment breaks something you're going to have to go wake up all of the relevant stakeholders, if you even know who they are.
Not to say late night deployments are never justified, but definitely not something devs want to be doing regularly.
The way I do it is to check the DB version `PRAGMA user_version;` on application start and run migrations if required, before the app starts taking connections. Yes, this means my app will be unavailable for a few seconds when upgrading. If you want a zero downtime solution then you will have to do it in a different way.
How do you back it up, replicate it, handle two different processes/containers/servers wanting to access the same data.
Using a PAAS solution for a database, you get all that functionality.
1. You can only run one instance of your app.
2. There is a small window of downtime each deploy.
3. Your app has access to 10 GB of storage. The storage is persisted across deploys and can be used for sqlite.
4. Your sqlite data is automatically backed up and can be restored / downloaded as needed.
How much per month would you pay for that product?
I'd find such a product really appealing, especially with a dedicated backup strategy that uses the SQLite backup API to do hourly snapshots or uses Litestream to S3 by default.
* Someone who knows Django but has zero devops skills can deploy my app with a few simple commands, add a fix, and demo it to me. Crucial: They must ask me zero questions.
* The backups happen to a non-you service, I one-click auth my google drive and/or dropbox.
* There are instructions on how to stand-up the web app on another service if you shut down. Those instructions might require two hours of my time, but should be complete.
...but in addition to bravura's requirements, I'd add: I'd want SSL termination, ideally with the option of me bringing a custom domain.
If you are trying to replicate or make available to a bunch of machines or similar - it's likely the wrong thing (although there are tools to do this, I've never used them). If you are just trying to back it up there is a pretty simple back up command.
For most of the things I touch, having a single-point-of-failure data store is typically not acceptable for production environments, except perhaps caching.
So in your day to day job as a good software engineer, yeah sqlite isn't going to be a good choice. It doesn't work well for the sorts of systems you work on. But if you were going to build a new social network for your college or something trivial like that, and you just want to move fast and have a crazy simple deployment setup, you might well use sqlite.
- how do you back up a JPG? You copy it, cause it's a file
- how do you replicate it? You copy it, cause it's a file. Unless you're talking about fancy DB replication, in which case well, that's not really a thing we do with files much. You'll have to do more research. but that's cause you're trying to do non-file things to a file.
- how do you handle two different processes accessing a JPG? They both open and read the JPG. Same for SQLite. For other concerns, you're trying to do things that don't map well to files, so once again more research needed.
https://www.unixsheikh.com/articles/sqlite-the-only-database...
Yes, technically a SQLite database is just a file on disk. And for the most part, you can treat it like other files on disk. Except for when you can’t. The GP’s questions are valid