What SQLite Actually Is
For anyone who hasn’t run into it directly, SQLite is a relational database that doesn’t run as a server. The entire database is a single file sitting on disk, and your application talks to it directly through a library.
It’s probably the most deployed piece of database software on Earth, and most people who use it every day have no idea they’re using it. It’s in your phone, your browser, and half the apps on it.
The Philosophy Behind It
The thing that earned my respect for SQLite is the philosophy underneath the engineering.
D. Richard Hipp built SQLite back in 2000 and, along with his small team, made a decision that still feels almost radical today: the entire codebase is released into the public domain. No MIT or GPL. In fact, there is no license at all, because there’s nothing to license. Anyone can take it and never owe a single line of attribution.
This small but punchy team doesn’t run on venture capital and they do not chase ads. SQLite and the systems supporting it are funded through paid support contracts with companies that depend on it. While this business model is vastly different from others, it is also a luxury. With that being said, it is a testement to build software that is reliable enough so serious companies will pay just to keep it boring.
The reliability isn’t a marketing claim. SQLite’s test suite is famous for having somewhere north of 100% branch coverage, which means the tests actually outnumber the source code by a wide margin. This is the kind of dedication missing from modern software.
The One Real Weakness
I can’t pretend that SQLite is flawless. Its known weak spot is concurrency.
SQLite allows many simultaneous readers, but only one writer at a time. Even with WAL mode turned on, which loosens things up considerably, you’re still bound to a single writer at the file level. For a mobile app or a small internal tool this is a non-issue. For something with real write throughput coming from many different machines at once, this is where SQLite starts to show its age.
Turso and the Rust Rewrite
A team spun out a project called Turso, built on libSQL, which reimplements the core of SQLite in Rust rather than C.
The Rust rewrite opens the door to an async runtime, native replication across machines, and eventually a real path toward concurrent writers without giving up the things that made SQLite great in the first place.
The original C implementation will likely never be obsolete. That means the ideas Hipp’s team spent 25 years proving out are now solid enough that someone else is comfortable rebuilding the foundation underneath them in a safer language. That’s a compliment!
Playing With It in Go
SQLite is everywhere partly because it’s trivial to embed. Here’s a small example in Go using modernc.org/sqlite, a pure-Go driver, so there’s no CGO or C toolchain required at all.
package main
import (
"database/sql"
"log"
_ "modernc.org/sqlite"
)
func main() {
db, err := sql.Open("sqlite", "notes.db")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// WAL mode lets readers keep working while a write is in flight.
if _, err := db.Exec("PRAGMA journal_mode=WAL;"); err != nil {
log.Fatal(err)
}
_, err = db.Exec(`CREATE TABLE IF NOT EXISTS notes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
body TEXT NOT NULL
)`)
if err != nil {
log.Fatal(err)
}
}If you fire off concurrent writes without a busy timeout, you’ll hit database is locked almost immediately:
// Without this, concurrent writers fail fast instead of waiting their turn.
if _, err := db.Exec("PRAGMA busy_timeout = 5000;"); err != nil {
log.Fatal(err)
}
stmt, err := db.Prepare("INSERT INTO notes (body) VALUES (?)")
if err != nil {
log.Fatal(err)
}
defer stmt.Close()
if _, err := stmt.Exec("first note"); err != nil {
log.Fatal(err)
}The busy_timeout line tells SQLite to wait up to five seconds for the lock to clear instead of immediately erroring out, which is the simplest way to smooth over the single-writer model for light concurrent workloads. It’s a workaround that works for a lot of situations.
Conclusion
SQLite doesn’t have a flashy cloud console and it doesn’t need one. What it has is 25 years of a small team proving that boring, heavily tested, license-free software can quietly become the most widely deployed database on the planet without tweets, VC funding, or a launch party.
Photo by Joshua Reddekopp on Unsplash
Loading comments...