libSQL - an innovative fork of SQLite enabling simpler scaling (video, 4m)
25 years ago, in a corporate partition, Richard Hipp of General Dynamics was coding for a damage control system on guided missile destroyers for the U.S. Navy. His work led to one of the most important and peculiar projects ever: SQLite, an ACID-compliant SQL database contained in a single file, weighing just 600 kilobytes, and requiring no server process. Notably, it has become the most deployed database in history, with over 1 trillion actively in use, embedded in nearly every smartphone and computer. Even more intriguing, this project resides in the public domain and is maintained by only three people, with no outside contributions allowed, which helps to avoid many potential issues related to security. This development has paved the way for LibSQL, designed to support an unconventional use case of one database for each user in an application.
SQLite has gained prominence due to its ease of embedding, robustness, and theoretical scalability to 140 terabytes. However, most large-scale applications prefer using Postgres or NoSQL databases for massive scalability. SQLite lacks a native way to shard and replicate itself to distribute massive datasets globally. In October 2022, a small group of developers launched a fork of SQLite, without modifications to the code itself, but rather through a manifesto promoting community control. This fork, named LibSQL, retains compatibility with SQLite while introducing significant changes.
LibSQL has carved a niche in building applications that provide every user with a unique database. SQLite fundamentally operates as a file rather than something externally connected like PostgreSQL. In addition to its file mode, LibSQL supports a server mode which allows for database communication over HTTP, making it compatible with serverless environments like Cloudflare Workers and AWS Lambda. A notable feature of LibSQL is its native replication system, where developers can designate a primary instance of LibSQL, allowing other instances to pull changes from the primary, culminating in an embedded replication feature.
Moreover, LibSQL adds functionalities such as encryption at rest, which is a discouragement to hackers, and WebAssembly triggers that enable code execution during database events. The capacity to share a single schema across multiple databases and perform vector queries for AI uses opens up remarkable performance improvements, as each database remains small and can be served closer to the end-user.
However, there are considerations that arise. If a user from Dildo attempts to access data from another far-off location, like Tiddybong in Australia, latency issues could significantly affect performance. Despite these concerns, SQLite remains an underappreciated tool that powers remarkable frameworks like PocketBase, and LibSQL further enhances its capabilities. Noteworthy at the time of writing this article is that the video amassed 508215 views and 22896 likes, a testament to the growing interest in such innovative developments.
Toggle timeline summary
-
Introduction to Richard Hipp and his work on a damage control system for the U.S. Navy.
-
Discussion of SQLite's unique features as an ACID-compliant SQL database.
-
SQLite's widespread deployment, with over 1 trillion active uses.
-
SQLite's public domain status and limited maintenance team.
-
Introduction of LibSQL as a fork to support unique database per user use cases.
-
SQLite's advantages in being easily embedded and theoretically scalable.
-
Challenges of scaling SQLite compared to other databases like Postgres or NoSQL.
-
LibSQL's goals to maintain compatibility with SQLite while adding community control.
-
LibSQL's dual mode allowing HTTP connections and serverless environments.
-
Overview of features added by LibSQL, including replication and encryption.
-
Advantages of having a unique database for every user in terms of performance.
-
Example highlighting performance benefits for users in geographically close locations.
-
Concerns regarding data access between users in distant locations.
-
Highlighting SQLite's capabilities and the enhancements brought by LibSQL.
-
Introduction to Clerk, an authentication solution supporting multi-tenant databases.
-
Clerk's pre-built UI integration options for developers.
-
Closing remarks thanking viewers and mentioning the next video.
Transcription
25 years ago, in a muffy corporate partition somewhere, Richard Hipp of General Dynamics was banging out code for a damage control system on guided missile destroyers for the U.S. Navy. His work led to one of the most brilliant, important, and just flat-out weird projects ever, the SQLite, an ACID-compliant SQL database that's contained in one file, weighs only 600 kilobytes, and requires no server process. What's crazy, though, is that it's become the most deployed database in history, with over 1 trillion in active use. Almost every smartphone and computer contains multiple SQLite databases. What's even more crazy, though, is that this project is in the public domain, and its source code is only maintained by 3 people. No outside contributions allowed, and they don't even have a code of conduct. But that's a good thing if you want to prevent nerd drama and unauthorized backdoor penetration, but that doesn't mean you can't fork it and make its C code do what you want to do. And that's exactly what the creators of LibSQL have done to support the unhinged use case of one database for every user in your app. It is November 13th, 2024, and you're watching The Code Report. There's a reason they call SQLite the most deployed database in the world. It can be easily embedded, it's robust, and in theory, can scale up to 140 terabytes. However, most large-scale applications go with Postgres or a NoSQL database to scale up to a massive level. There's no native way to shard and replicate SQLite to distribute a massive dataset around the world. Well, in October 2022, a couple of developers launched a fork of SQLite with no code changes, just a manifesto and a dream of SQLite with community control. That fork is called LibSQL. It maintains SQLite compatibility, but also makes some major changes. But the thing that really inspired me to make this video is its ability to build apps where you have one database per user. Let's figure out how that's even possible. Like I mentioned, SQLite exists as a file, and not something you connect to externally with a server like PostgreSQL. But alongside this file mode, LibSQL also has a server mode where you can talk to your database via HTTP. And that allows you to access SQLite from serverless environments like Cloudflare Workers or AWS Lambda. That's cool, but another major addition is a native replication system. You can select a LibSQL instance as a primary, and then all other LibSQL instances pull down the write-ahead log changes from the primary and forward the writes to it. The end result is an embedded replicate feature. In addition, it adds features like encryption at rest to the disappointment of hackers. It adds WebAssembly triggers to run code when events happen in the database. It can share a single schema across multiple databases. And it can even do vector queries for AI applications. When you combine these features to give every user a unique database, it opens the door for better performance because not only is the database size kept very small, but it can be served from a location near the user. Sounds too good to be true, so let's look at the trade-offs with an example. Like if we close our eyes and choose a totally random place on the map, like, I don't know, say, Dildo, Newfoundland, that user who lives in Dildo could get their data from the nearby St. John's data center. Not only is that a big win for the Dildo user, but the developer also gets excellent performance without the need for an in-memory cache like Redis. In fact, you could even cache the entire database locally in Dildo. If you're building a social network where the Dildoites communicate with each other, the performance gains could be incredible. But I do have some concerns. What if we choose another random place on the other side of the map, like Tiddybong, Australia? Now what if Bob in Dildo wants to access data from Alice in Tiddybong? Combining those data sources will take some very long round trips across tens of thousands of miles of undersea fiber optic cables. That may not make a ton of sense, and ultimately a more centralized approach might be better. The bottom line here, though, is that SQLite is an awesome underappreciated tool. It powers awesome frameworks like PocketBase, and now LibSQL makes it even more capable. This video has been brought to you not by the Dildo Tourism Board, but by the amazing authentication clerk. It provides an out-of-the-box user authentication solution, and even supports the crazy multi-tenant per-user databases that we talked about in this video. As a developer, you'll have access to every possible sign-in method you could imagine, including highly secure modern strategies like biometric passkeys and multi-factor auth. What's really awesome, though, is its suite of pre-built UI elements that seamlessly integrate into any front-end code. You can drop them in as React components, or use them as an unstyled starting point that can be fully customized. Then as users start pouring in, you can manage them with the back-end dashboard, and integrate them into other services with webhooks. In fact, end-users can even manage their own data and security preferences on a fully-hosted profile page. Give clerk a try for free today using the link in the description. This has been The Code Report. Thanks for watching, and I will see you in the next one.