Why SQLite is so great for the edge

Everyone already knows that SQLite is great. This article makes the praise much more specific though — it explains why we, the developers of libSQL, think that SQLite is a great choice for bringing your data to the edge.

#It's lightweight

The software industry has a habit of jumping between over- and under-valuing products with a small memory footprint. It was an important factor back in the days when desktop RAM was counted in kilobytes, then it got neglected as the hardware became less expensive and more powerful. The trend returned when we started writing software for mobile devices with limited RAM — until manufacturers continued to pack themwith gigabytes of RAM and storage as well. Now, serverless functions environments like Fermyon Spin and Cloudflare Workers, which demand your apps to be as compact as possible. The reason they do so is straightforward: if you can pack twice as many instances of your application into a single server, you also cut the hardware costs roughly in half. How does SQLite fit into the picture? It's simply orders of magnitude lighter than similar products, such as Postgres.

At the time of writing, Cloudflare Worker binary size limit for deployments is 5MiB after compression. Let's see how SQLite and Postgres fit into the limit:

Sure, Postgres is much more heavyweight due to all its networking code, extensions, utilities, and so on. But the fact remains — you can squeeze ~10 SQLite instances into a machine in place of a single Postgres installation, and in some environments, that counts!

#It's fast

Speed is of course a relative term, but SQLite really is a highly optimized database library. It's borderline impossible to compare it against networked database management systems like MySQL or Postgres, because SQLite is a library that operates on a local file — it bypasses all the costs incurred by the network, layers of serialization and deserialization, authentication, authorization, and more.

Still, it makes sense to evaluate the performance of a database you want to use. SQLite comes with a useful utility called speedtest1, which is perfect for getting a quick overview of the database performance with your setup:

$ ./speedtest1 --size 5
 100 - 2500 INSERTs into table with no index.......................    0.006s
 110 - 2500 ordered INSERTS with one index/PK......................    0.009s
 120 - 2500 unordered INSERTS with one index/PK....................    0.010s
 130 - 25 SELECTS, numeric BETWEEN, unindexed......................    0.007s
 140 - 10 SELECTS, LIKE, unindexed.................................    0.008s
 142 - 10 SELECTS w/ORDER BY, unindexed............................    0.007s
 145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed..................    0.004s
 150 - CREATE INDEX five times.....................................    0.005s
 160 - 500 SELECTS, numeric BETWEEN, indexed.......................    0.001s
 161 - 500 SELECTS, numeric BETWEEN, PK............................    0.001s
 170 - 500 SELECTS, text BETWEEN, indexed..........................    0.002s
 180 - 2500 INSERTS with three indexes.............................    0.003s
 190 - DELETE and REFILL one table.................................    0.003s
 200 - VACUUM......................................................    0.006s
 210 - ALTER TABLE ADD COLUMN, and query...........................    0.000s
 230 - 500 UPDATES, numeric BETWEEN, indexed.......................    0.001s
 240 - 2500 UPDATES of individual rows.............................    0.002s
 250 - One big UPDATE of the whole 2500-row table..................    0.001s
 260 - Query added column after filling............................    0.000s
 270 - 500 DELETEs, numeric BETWEEN, indexed.......................    0.002s
 280 - 2500 DELETEs of individual rows.............................    0.003s
 290 - Refill two 2500-row tables using REPLACE....................    0.009s
 300 - Refill a 2500-row table using (b&1)==(a&1)..................    0.004s
 310 - 500 four-ways joins.........................................    0.005s
 320 - subquery in result set......................................    0.003s
 400 - 3500 REPLACE ops on an IPK..................................    0.002s
 410 - 3500 SELECTS on an IPK......................................    0.003s
 500 - 3500 REPLACE on TEXT PK.....................................    0.002s
 510 - 3500 SELECTS on a TEXT PK...................................    0.003s
 520 - 3500 SELECT DISTINCT........................................    0.002s
 980 - PRAGMA integrity_check......................................    0.009s
 990 - ANALYZE.....................................................    0.001s
       TOTAL.......................................................    0.124s

At the time of this writing, the time limit for a Cloudflare Worker is 10ms. This is plenty for 2500 database inserts and a couple of hundred selects on top! Sounds like orders of magnitude more than your typical shopping cart implementation requires.

#It's zero-configuration

Arguably the main advantage of SQLite is that you don't need to spend dozens of hours trying to set up a distributed system in order to even start using it. In fact, you don't really need anything, not even a hard drive, to start prototyping. Your environment doesn't have persistent storage? No problem: SQLite will gladly run an in-memory database for you. That translates to superior developer experience. The primary focus of libSQL is the ability to start fast and prototype your project 100% locally, and push it to the cloud only when deploying to production.

#It's rock-solid

SQLite is backed by an extensive set of tests, claiming 100% branch coverage, in various aspects and configurations:

• Correctness checks
• I/O error tests
• Fuzzing
• Regression tests
• Crash/power loss tests
• Constrained memory tests

… and more.

A fair part of this test suite is unfortunately closed-source (we're working on it). Still, the software is universally considered battle-tested, as it powers everything from smartphones to aircraft and more. It's important to be able to trust the database with your data — avoid corruption, data loss, bit rot, and all other kinds of unpleasant things that can happen. And while it's impossible to escape all of such situations unharmed, proper test coverage is the best preventive measure to start with.

#It's feature-packed

Don't get misled by its compact size — SQLite is loaded with features. Did you know that you can ask for today's date with a simple query?

SELECT date('now');

Alright, not particularly impressive. How about the first day of the current month?

SELECT date('now', 'start of month');

Still not impressed? Let's get the first week of the month instead:

SELECT date('now', 'start of month', '+7 days');

Oh, you wanted the first Thursday? SQLite has you covered:

SELECT date('now', 'start of month', '+7 days', 'weekday 3');

UTC? There you go:

SELECT date('now', 'start of month', '+7 days', 'weekday 3', 'utc');

And we've barely covered date/time functions here.

The extension mechanism is also worth noting. While the SQLite base is relatively small and robust, it can be easily extended with endless functionalities. Open-source extensions (e.g. sqlean) range from additional math functions, through complex data structures, supporting different storage formats, AI integration like embeddings or vector search, replication, CRDT, and countless more features.

libSQL also extends the vanilla SQLite interface with WebAssembly user-defined functions, which gives users even more flexibility.

#It can be Wasm!!

SQLite, as well as libSQL, can be compiled into a concise Wasm module — just 390 KiB compressed, less than 900 KiB uncompressed size. It can run a fully-fledged in-memory SQL engine, but that's not all! It's also capable of persisting the data in your browser's localStorage, or OPFS — Origin Private File System — which is a modern approach for providing a private filesystem for web applications.. You can try the demo to get a feel for how it works.

But WebAssembly goes way beyond browsers, and so does SQLite. Recent releases compile to WASI — a modular system interface for WebAssembly. That opens the whole new world of possibilities, making SQLite feasible for edge environments that run serverless functions in the form of Wasm modules — Fermyon Spin, Cloudflare Workers, scale.sh.

#What's next?

SQLite has great potential for the edge. Our fork — libSQL — is both open source and open contribution, and we aim to evolve it to suit many more use cases than SQLite was originally designed for, including edge environments — like Turso, our edge-native database. We are a community effort, so feel encouraged to become a contributor. Oh, and star us on GitHub!