> Dqlite is a fast, embedded, persistent SQL database with Raft consensus that is perfect for fault-tolerant IoT and Edge devices.
Why do you say it works for "small" websites but presumably not large ones? If it's not transactions, concurrent reading, backups, or administrative tasks... then what's the issue you run into?
Genuinely curious... I'm wondering if everything I've heard about "don't use SQLite for websites" is wrong, or when it's right?
SQLite just makes the tradeoff to be simpler since often it doesn't matter. But don't make the mistake that it doesn't matter. Since PG helps avoid data problems and you might need to scale out web servers that is why Django for instance recommends switching to Postgres (or whatever you're actually going to use) ASAP cause there are differences. You may end up relying on PG to reject things SQLite doesn't care about by default. SQLite might let you get away with inserting data which PG refuses to handle.
Not to mention the DB specific features can differ. Like PG's JSON field types or etc.
I'd guess the reason to be that people keep hearing things like "don't use SQLite for websites" and thus don't even try.
> Why do you say it works for "small" websites but presumably not large ones?
Not the GP, but the main reason I wouldn't use SQLite for a large website is that SQLite itself doesn't offer much re: failover/replication (i.e. multiple servers, one database), and I haven't used RQLite enough (or at all; I should fix that) to be comfortable with it in production. Because of that, I'm more likely to reach for / recommend PostgreSQL instead.
That being said, if your website has crazy "web scale" FAANGesque needs and you're at the point where you need to write your own replicated datastore, using SQLite as a base and building your own replication layer on top of it (or using RQLite and maybe adjusting it for your needs) seems like a reasonable way to go.
Wonderful job.
Would this enable any node to be a writer (i.e. would it lock the DB across all nodes)? Or would I have to designate some "master" server with exclusive write access and have any other servers forward write requests to that server?
Exactly what Bloomberg did with Comdb2: https://github.com/bloomberg/comdb2
[1] https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...
I should revisit this policy now that you can run a truly huge site off a 1U slot (I work for an alexa top10k, and our compute would fit comfortably in 1U); computers are so fast that vertical scaling is probably a viable option.
IMO a lot of organizations should start re-investing in on-premise as well. Having a mid-range AMD EPYC server can serve most businesses out there without ever having more than 40% CPU usage.
That, plus scaling down Kubernetes clusters. Most companies absolutely didn't need them in the first place.
Still, for the cloud I started preferring going for Rust (where applicable!) and optimizing the hell out of the performance hot-spots. So far the results have only been crushing successes. Horizontal scaling has been employed only as a means of backup instances with a load balancer in front of them. And for blue/green deployments.
Horizontal scaling hasn't been at all necessary otherwise. A $25 instance never got north of 15% CPU for one of the services I rewrote. I/O utilization never got beyond 60% and is usually 3-5%.
But I can agree that the cloud has unquestionable benefits. It's just that I feel that their number is gradually dwindling.
For instance I remember that at some point sqlite didn't have foreign keys, so I couldn't run my existing migrations on it (without rewriting everything) which was a big issue for me at the time. Now I see that they've added the support for referential integrity in the meanwhile, but I had no idea about it because simply there's so many other libs and technologies to follow - one just can't keep track of every single tool in the world obviously - so some great tools just fall out of focus.
My guess is the word will slowly get out and in a few years people will probably shift to using it more in a web world - but it will take some time.
And it doesn't have a native date type. Date handling has to be handled at the application layer. It can be tricky to do massive time-series calculations or date-based aggregations.
You can use integers or text types to represent dates, but this open-endedness means you can't share your db because everyone implements their own datetime representation.
The JSON extension library is amazing and works well. If SQLite were to grow a first-rate RFC 3339 library, one which could read from tz when available and do the things which strftime cant, acting on your choice of Unix timestamp and valid RFC 3339 date string, this would be a real boon to the ecosystem.
I haven't found typechecking inputs to be a real barrier. Sure, `val INTEGER CHECK (val = 0 or val = 1)` is a long way to spell `val BOOLEAN` but `CHECK json(metadata)` is a reasonable way to spell `metadata JSON`, and a similar function would surely exist for a SQLite datetime extension. You can do it now by coercing the string through an expected format, but that doesn't generalize well.
OTOH, as an embedded database with freeform advisory typing and easy extensibility (for functions, etc.) in most host languages, it's not hard at all to get whatever you need for datetimes if you are using a host language that has a decent datetime library itself (and, as a bonus, you then don't have to worry about subtle differences between manipulations of datetimes through SQL and manipulations through other mechanisms in the app.)
In terms of scale, sqlite is just fine. But I am tired of fiddling with the dates, it's too easy for bugs to sneak into my code, and I want to use table valued functions to essentially parameterize views instead of having to build complex queries in the app layer.
If your web app is mostly reading and writing single rows, yeah, sqlite is just fine. But if there's substantial and complex logic involved, it has its limits.
It absolutely has good use cases, but those are rather niche. You'll mostly be better off with the traditional postgres etc.
SQLAlchemy, at least, lets you switch from SQLite to Postgres in a configuration file.
Isn't there a useful SQL subset which allows you to switch from one database to another without rewriting? There seems to be such a subset for C, for example, which multiple compilers all interpret the same way, and SQL is a standardized language, too.
Using a wrapper like SQLAlchemy is probably an improvement for most medium and large projects, but it has costs of its own. Using portable ANSI SQL - above the Hello World level - is something basically nobody does unless they make a serious effort, lean on linting tools and forego some of the most useful features of their DBMS. sh is perhaps a closer comparison than C here: it's at least possible to write portable C accidentally.
And neither of these help you if you started with a SQLite database - an excellent choice for most - and decide that you need to support a dozen or a hundred concurrent users.
Only if you're happy throwing away a lot of the features, which is taking away from what makes SQL an attractive solution in the first place. Plus, even some basic things aren't the same between different implementations (e.g., SELECT TOP 1 * FROM t vs SELECT * FROM t LIMIT 1), so you'd really have to be testing with different RDBMSes the whole way through to make sure you didn't accidentally break compatibility.
On GCP:
- A regionally replicated disk will replicate writes synchronously to another zone (another data centre 100km away). This means when the SQLite write call returns, the data will be geographically replicated, and the second disk can be used as a fail over. This is all transparent to app/db.
- Disk snapshots are incremental, and are stored in Cloud Storage, which is geo replicated across regions (E.g. Europe and US).
In a way, this gets you even better failover and backups than traditional server DBMS's that have these features built in, and often need custom administration to ensure they are working.
I think it has concurrent backups via the backup api: https://www.sqlite.org/backup.html
Imo part of that is just web applications tend to be not very efficient when compared to something like unix cli utilities so to get performance you end up with potentially massive amounts of horizontal scaling
On the other hand, the lack of performance /usually/ buys you higher productivity so you can make product changes faster (you let a GC manage the memory to save time coding but introduce GC overhead)
It's less about performance than about resilience IMO. Of course if you don't have a proper HA datastore (master-master) then you kind of undermine that, but a datastore SPOF is better than the whole application being a SPOF.
For my reporting/read-only use case https://datasette.io/ solves the above beautifully.
Actually, I think it's network access that is the issue. SQLite isn't really designed to have 4-5 network connected apps using it as a shared data store. In most shared hosting setups, database is treated as a network service, so you can have 200 different sites leveraging the same db server... and sometimes the db server isn't on the same hardware as the http server.
What it doesn't support is concurrent writes only.
http://litereplica.io - single-master replication
http://litesync.io - multi-master replication
https://aergolite.aergo.io - highest security replication, using a small footprint blockchain
I'd much rather SQLite not waste its time on implementing features they're not good at, leaving that to the tools we already have available for rolling file backups, instead spending their time and effort on offering the best file-based database system they can.
(Heck, even failover is just a file copy initiated "when your health check sees there's a problem")
Instead you need to use the .backup mechanism or the VACCUM INTO command, both of which safely create a backup copy of your database in another file - which you can then move anywhere you like.
The main issue here is time: because copying temporarily locks the db out of further changes, it will be "out of sync" if you naively believe that performing a write means your next call will see that data, and you bake that assumption into your code. While there's a wait, on modern hardware using SQLite for its intended purposes (any volume of reads, but low volume of writes), that's just not an issue.
There are other problems associated with file-based backup, of course, such as missing out on in-memory data, or corruptions caused by power outages, but those problems only exist if we were to copy a db once. Backups run regularly, and any data we miss out on the current pass, we'll get on the next pass.
Basically: when used for the purpose that SQLite was created for, file copies are a perfectly fine backup strategy that really only shows its limitations when you start to push your project into "SQLite isn't really appropriate here anymore" territory.
(having said that: the fact that VACUUM can be run into a new file is super nice, and everyone should know that it exists)
Transactions are only atomic from the perspective of other transactions. Other processes or commands on the system can see partial state. This applies to both the rollback journal & the WAL modes.
File copies are also covered in their "How to Corrupt a SQLite Database File" web page[1].
[1]: https://www.sqlite.org/howtocorrupt.html#_backup_or_restore_...
For SQLite, "copying files", rolling file backups, shadow volumes etc. are by definition valid strategies when it comes to SQLite.
It's....not great.
> But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time.
No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc. (Edit: oops, comment pointed out it does have concurrent backups.)
SQLite isn't a toy DBMS, it's an extremely capable embedded DBMS. An embedded DBMS is geared towards serving a single purpose-built client, which is great for a desktop application that wants a reliable way to store user data.
Once you have multiple clients being developed and running concurrently, and you have production data (customer accounts that are effectively legal documents that must be preserved at all times) you want that DBMS to be an independent component. It's not principally about the concurrent performance, rather it's the administrative tasks.
That requires a level of configuration and control that is contrary to the mission of SQLite to be embedded. They don't, and shouldn't, add that kind of functionality.