😉 SQLite – the database of the future 😉
I can just feel in my bones that this database has insane potential, and overall, I wasn't wrong about it - that's exactly what I'll talk about
(continued in comments)
#db #sql #sqlite #fdd
😉 SQLite – the database of the future 😉
# [ $davids.sh ] · message #266
-
@ [ $davids.sh ] · # 1633
SQLite is effectively a library specification that allows you to operate on database files directly from a programming language.
Advantages
– Because SQLite's performance depends on the language's speed, processor power, and IO throughput, it is theoretically one of the fastest databases, at least because it completely lacks the network complexity of standard databases.
– While other embedded storage solutions are simply key-value (rocksdb, leveldb, badger) or NoSQL with their own unique SDKs (couchdb-like), SQLite is a full-fledged SQL database. Comparing its SQL capabilities to PostgreSQL: schema, indexes, transactions, locks, joins, constraints – it has everything.
– Consequently, experience with any other database will be relevant, making SQLite very attractive to developers.
– This also means you can migrate from SQLite to PostgreSQL / MySQL almost painlessly when / if the time comes.
– Due to its simplicity, SQLite is either already integrated (browsers, mobile, native apps) or easily added (I've seen devices with minimal Linux on board that use SQLite for applications).
– You can work with a single instance from multiple processes simultaneously.
– To back up the database, you just need to upload the files to S3.
– Super simple integration testing, because you can have a separate SQLite instance (e.g., in-memory) for each test.
– If you create a SaaS without multitenancy and distribute it as a boxed solution, you can easily open an instance per client without issues.
– As free as it gets.
Disadvantages
– Classic network file systems do not allow multiple servers / containers to work with the same SQLite database (I only found information about VFS, but I don't yet understand how viable that option is).
– If something happens to the file system while writing data, there's a chance it can break in a way that you simply cannot recover from.
Use Cases
– Local database for applications with a frontend (web, mobile, desktop). – Local database for remote agents (applications that collect and send data to the cloud from devices, from a server in a warehouse). – Cache for a single instance (e.g., for actors). – Database for a startup / project requiring work with a large amount of data, but without the desire to pay a lot of money for a PostgreSQL / MySQL instance.
Addressing Disadvantages
We need 2 things:
– Sync Read replicas, so we can switch in case of master failure. – WAL Streaming Backup for reliable backups.
Optionally, async read replicas for delayed reads, or even CRDT to turn it into a distributed multi-master p2p database.
And ideally, all of this should be embeddable within SQLite itself, meaning embeddable into the language, or as a sidecar process. Otherwise, I think the use of SQLite simply loses its meaning.
Interesting Projects
– Pocketbase – Admin panel, Firebaselike HTTP API, email, auth, file storage, logs, and much more out of the box. – Turso – Distributed SQLite in an Edge environment. – Electric – Client-side SQLite that synchronizes with PostgreSQL using CRDTs, turning SQLite into a multi-master edge database. – LiteFS – SQLite database replication at the file system level. – libSQL – A fork of SQLite on which Turso is built, with the ability to deploy servers, replicas, auto-sync WAL to S3, and so on. – rqlite – Turning SQLite into a full-fledged database with read replicas, written in Go. – dqlite – Roughly the same as rqlite, but in C.
-
@ [ $davids.sh ] · # 1634
What is the future of SQLite
**– Firstly, it's clear that SQLite is transforming into a database for the Edge environment because it allows operation with minimal resources
– Also, it's definitely destined to become a p2p database (in the style of Couchbase) because it's already integrated / easily integrates into any client
– With the addition of built-in Sync Read replicas, it will be more likely to be used in production applications ** More thoughts** ** **– If I had to develop a standard business web application right now, I wouldn't bother and would use PostgreSQL
– I'll gladly use it for my own projects
– If LiteFS or libSQL work out, I would seriously consider using it in production applications
-
@ [ $davids.sh ] · # 1635
Ah, and after Sync Read replicas, it's definitely necessary to add partial tag replication (e.g., by geotags), followed by master selection for partitions/ranges, and then we'll have CockroachDB Lite.
-
@ Ivan ITK 🚫 · # 1638
I completely agree with everything! I use it in production in critical areas at factories.
Regarding rqlite, there's another option that I like more:
-
@ Ivan ITK 🚫 · # 1639
The meaning of "cockroach" is that all nodes are active masters, so the comparison isn't great)
-
@ [ $davids.sh ] · # 1640
If I'm not mistaken, the creator of Litestream then created LiteFS because there was some insurmountable problem with the former.
And were you able to use Litestream in production?
-
@ [ $davids.sh ] · # 1641
Well, CRDB is multi-master but by ranges: one node is the leader for one set of ranges, another for a different set, but the leader for the same range is always one, isn't it?
-
@ Ivan ITK 🚫 · # 1642
Not yet, but I will have that experience soon.
-
@ Ivan ITK 🚫 · # 1643
Yes, each range within a raft group has one master. However, you can read from replicas, like in PG.
-
@ [ $davids.sh ] · # 1644
Yes, yes, that's what I meant.
-
@ [ $davids.sh ] · # 1645
Honestly, reading their replicas always makes me wish for something like MongoDB: where you can specify read priority for each individual request, even to a read/write node.
I never thought I'd praise MongoDB...
-
@ Ivan ITK 🚫 · # 1646
There, in backward compatibility with PG, they made reading from the replica a requirement.