Why SQLite may become foundational for digital progress

If every dog has his day, well, perhaps the same is true for every database. Judging from the news recently, SQLite is having its day in the sun. In the last few weeks, several companies announced they were building or supporting new projects built around the venerable open-source database. 

Is SQLite one of the foundations for the next generation of the internet? Some think so. Cloudflare announced that they were deploying a new database service built around the backend tool. Meanwhile, Fly announced that it was hiring one of the developers of Litestream, an open-source project that enhances the basic version of SQLite by adding the capability to replicate the data to increase performance and survivability. 

At first glance, the idea seems a bit odd. The project is more than 20-years-old and written in plain, old C. It’s less of a standalone app worthy of its name as much as a library that can be linked into your code. It’s not so much a front-of-the-house, marquee software option as much as a forgotten servant doing thankless work. Many developers may start using the code when they’re sketching out a project or building a prototype, but they often move on to other, full-featured options like Oracle or PostgreSQL. 

But the announcements suggest that the companies see something more. Cloudflare, for instance, is rolling out a new database service called D1 to give developers another way to store data generated by their Workers serverless apps. They already offer a key-value store and bucket product (R2), but developers often want to rely on the structure and power of SQLite to simplify their workload. 

“It’s a database for the edge model,” said Rita Kozlov, the senior director of product at Cloudflare. “The embedded nature of it also made a lot of sense, where the whole purpose of D1 is to make it really easy for our developers to be able to spin up a database right alongside their compute.”

Cloudflare is rapidly expanding beyond its beginnings as a worldwide static cache. They’ve built out hundreds of data centers near end users to provide fast responses. Lately, they’ve been adding products such as Workers or Pages that offer a serverless model of app deployment. Developers can write a few basic functions, pay only for the time that the functions run and also deliver lightning fast responses because the code runs on machines close to the user. 

Adding SQLite helps developers provide more sophisticated applications. Data can be stored locally on an edge node and then eventually replicated throughout the world. Developers who are more ambitious and need to keep track of more user states can adopt the platform. 

“We asked many of our internal developers, ‘How do we get you to build more with Workers?’” said Kozlov. “Their answer has been, ‘Give me a database. That’s the tool that I’m used to.’ I can probably figure out how to do what I need to do with [Key-Value] but it’s just not where people are today. We always want to meet developers where they are.”

Fly is jumping in with the same goal. They’ve announced that they’re supporting work on Litestream, an open-source project that adds background processing to SQLite. It will stream updates to various object stores and FTP sites, so developers can trust that SQLite’s data will still be available and recoverable after trauma. 

It’s easy to see where Fly got the idea to revise and extend an open-source database. One of their main products is fully supported clusters of PostgreSQL. Developers can set up a scalable, resilient version of PostgreSQL in just a few clicks. 

Many other companies are doing the same thing with open-source databases. Companies like PlanetScale, Yugabyte, Amazon, Oracle and Google are starting with either MySQL or PostgreSQL and then adding extra layers of features to improve reliability, scalability and more. 

Just last week, Google announced AlloyDB, their version of PostgreSQL that offers full compatibility with some extra enhancements like a column store that can dramatically improve some workloads. 

Single-threaded, but multidimensional

Still, there are several differences between SQLite and the other projects. SQLite is a basic, single-threaded system. Other databases are designed with multiple threads to juggle more complex constellations of users. 

For many smaller projects, this isn’t much of a limitation and some developers see it as a feature. 

“I ran a database company before this and I think the thing people like me never want to talk about is just about everyone has a few sub-10 gigabyte databases.” said Kurt Mackey, the CEO of Fly. “If you’re really in that category, you know this is super interesting because it’s SQL and it’s amazing for 10 gig databases.”

Developers can often get much of what they want from the basic core functions without the complexity of supporting a full-featured database. 

“The documentation for Postgres 14 is nearly 3,000 pages.” said Ben Johnson, one of the developers at Fly. “And if you don’t need the Postgres features, they’re a liability. For example, even if you don’t use multiple user accounts, you’ll still need to configure and debug host-based authentication. You have to firewall off your Postgres server.”

The Litestream open-source project supported by Fly enhances SQLite by adding the option to add more resilience to hardware failures while also adding more concurrency. It solves the biggest worries that developers have about using the tool with more serious, server-side projects. 

“It’s really nice during development.” said Kent Dodds, a developer who frequently deploys SQLite in projects. “[There’s] no need to get a database server up and running (or a docker container). It’s just a file. You can even send the database file to a coworker if you need some help with something.”

The drawbacks and what lies ahead

Still, while many Fly customers use SQLite successfully for data storage for some simple apps running on the service, Fly’s Mackey reports that there are some rough edges. The software runs very fast without performance glitches, but there aren’t the same number of tools that can help support it. 

“I think the biggest complication for us is that there’s no tooling for it.” said Mackey. “We have people deploy apps. They’re like, ‘How do I connect to my database and like query things? How do I import data?’” 

Many rely upon folklore and third-party tools that are common. The code has been widely adopted over the years and many have written their tools that can support it. Even though the tools aren’t directly targeting the new server-side operations, they can still be adapted. 

“One of the things I love about it as a product is that it’s very stable.” said David Crawshaw, the chief technical officer at Tailscale. The company uses SQL to support many of the network operations. “The things it did 15 years ago, it still does today. That means when I come back to it, the things I’ve learned are still useful “

Another wrinkle is that it’s not exactly open source. The original developer of SQLite, Dwane Richard Hipp, placed it in the public domain. Generally, this means that there are no legal restrictions on using the code at all, although there are some questions whether every country recognizes this view. 

It’s a good stepping stone or starting point for developers, and sometimes that’s all it needs. This freedom has encouraged plenty of developers in the past. It’s common to find SQLite running inside many devices. Many smartphones and tablets use it for the default storage. 

Still, that leaves some wondering just how much this is a real trend and how much is just a stepping stone for the companies. This was further underscored by Kozlov, who noted that the project name “D1” at Cloudflare is named this way for a couple of reasons: It’s easy to increment the number and said, “I don’t think that this is — or rather, I know that this is not — our last final stop in the database space. I think we’ll find ways to extend or we will extend our offering.”