Hello SQLite!

Dec 23rd 2025

Hello SQLite!

This November, we decided to open source one of our internal libraries to the world: the driver we use for data persistence (but not only!).

This library has been a core component of our software stack, and after using successfully the package internally for the last 18+ months, we think the API has stabilized enough, that many of the initial bugs have been sorted out, and we are happy to share the code more widely.

This not-so-short post is our attempt to provide a high-level overview of the library, and maybe see whether those ideas could help you in your next projects.

Design decisions implementing the driver

SQLite provides a rock-solid engine for data storage: not only is it the most commonly used database in the world, community extensions such as Litestream or rqlite have turbocharged its abilities to serve as a fully functional engine even for the most demanding situations.

Implementing a Go SQLite driver is becoming a bit of a rite of passage, and is even given as a fully worked out CGo example (the lineage to R.Cox’s implementation is indeed fairly obvious in our own work). But we thought the standard library approach to SQL engines was not necessarily a good fit for local application storage (and we are apparently not the only ones), and we wanted to explore a different, lighter approach to persistence than the sql package of the standard library.

Starting from a blank sheet, we honed on 4 key goals we wanted to achieve in our implementation

Goal 1: 🔨 robustness

Persistence is the part of the code you can’t fix after the fact, so stability over anything else is pervasive through the code base.

At the bottom layer, we heavily rely on the built-in durability mechanisms of the engine (so much so that Close was a late addition to the library). We also chose a set of compile-time options to focus on modern good practices (multi-threading to account for modern multi-core CPU, built-in pooling, only accepting standard SQL), and prevent API misuse – at a small runtime cost.

At the driver layer, we chose to restrict mapping between Go and SQLite types to a minimum, as to avoid surprises (e.g. time.Time is converted to the expected string format, but time.Duration is not).

And since data can only survive as long as the medium on which it is stored, we made sure that backups where just a single, simple function away (automated periodic synchronization is on our roadmap, this will probably be a great topic for a future blog article).

Now that this critical (and maybe a bit boring 🤓) topic is addressed, turning to everyone’s developer favorite: the API.

Goal 2: 🎏 fluent interface

While Go is sometimes derided for verbose error management, repetitive checks can be alleviated by storing computation state in structures (and maybe in the stack in the future!!), and threading it through methods.

In this library uses the pattern heavily, and querying a record needs only a single error check:

var mtime int if err := db.Exec(ctx, "select mtime from docs where path = ?", fname).ScanOne(&mtime); err != nil && !errors.Is(err, io.EOF) { return fmt.Errorf("cannot read previous record from path %s: %w", fname, err) }

Behind the scenes, we rely on a small struct to keep a pointer to the underlying C reference, as well as other meta-data to ensure proper lifetime management:

type Rows struct { err error ctx context.Context scflag bool stmt *stmt final func() }

Checking, and aborting for the first error is threaded through all methods on this structure, with early returns such as the one in the Scan method:

func (rows *Rows) Scan(dst ...any) { if rows.err != nil { return } // ... }

The library then trades convenience for expressiveness: for example, there is (currently) no way to recover from an error midway through a scan and continue. We believe this is the right choice by default, and will monitor how the Go community settles on control flow constructs to see how we can expand expressiveness without losing the convenience.

Meanwhile, there is another stateful aspect that need be threaded in our call stacks: creating coherent changes from atomic operations (aka transactions).

Goal 3: 🪶 lightweight transaction management

SQLite offers a powerful savepoint capability that offers nested transaction abilities. The top benefit against BEGIN / COMMIT from standard SQL (do check out the official documentation for full details!) is that a top-level save point can rollback nested operations even if they have been released – and thus we can neatly compose larger transactions from simple functions . Keeping bookeeping to a minimum on the user, the library mapps savepoints to (nested) contexts that can be passed between queries, and naturally flow with the language call stacks, e.g.:

ctx, err = db.Savepoint(ctx) if err != nil { tasks.SecureErr(ctx, w, err, "database issue") return } // note this is a no-op if the transaction has already succeeded defer db.Rollback(ctx) createUserRecord(ctx) updateGroupPreferences(ctx)

Functions using the database with Exec method can then naturally be reused and composed in multiple transactions – the context will take care of assigning them to the right timeline.

Even with the niceties of a composable, concise API, programming errors can still lurk in dark corners. And that’s naturally why we considered it our fourth goal.

Goal 4: 🐛 rich debugging

Programming bugs usually arise from a difference between the expectations of the developer and the state reached by the application. Being able to see, and indeed query application state with a powerful language (SQL!!) is a convenience we are not willing to lose. Fortunately, SQLite comes with a built-in shell that you can point to any file (including attached to a bug report! I’m still not completely over the ability to “just send the database by e-mail”). We have extended (one might even say turbocharged 🚀) the built-in abilities with a remote shell – but this will probably deserve its own post, so stay tune for this.

Observability is built-in: profiles are available to list statements running at any time, trace allocation of objects passed by pointer to CGO, list extensions currently loaded … SQLite itself has powerful introspections abilities, and improving their support is definitively high on our priority list.

And performance is a first-class citizen in our world too; not only does the library expose detailed query plans, trace regions are also created on the call path, so developers get a very comprehensive view of where time is spent during operations.

The driver is designed as a library, not a framework, so it does not impose specific patterns on data organisation or access. To complete the picture, we are happy talking about our internal patterns – as a reference point in the discussion.

Building a storage layer from the driver

Our application storage is based on a key-value model: each entity in the system is serialized (in our case using cbor), and stored as bytes in a two-column table (aptly named key and value). This setting means fetching an entity is a short invocation (FindOne is a small wrapper to execute a query and scan the result using generics):

sch, err := FindOne[NotebookSchedule](ctx, db, keyof(notebookID)) 
if err != nil { tasks.SecureErr(ctx, w, err, "invalid notebook ID") }

The convenience offered by this model is undeniable for programmers but comes with risks: a change to a data structure used in code could indeed prevent us from reading older values. We use a simple build-time script (included in cmd/stability) to alert developers early if the data structure they marked as serialized (see examples in the sqlite/stability package) changed, and therefore could trigger backward-compatibility issues.

 type DataSource struct { 
   _ stability.SerializedValue // empty marker type 
   Type string 
   ... }

Before each commit, the script walks the AST and see if the code lines of the current commit overlap with the lines of data structure. If so, it kindly asks the developer to ensure (especially with test cases) that the changes are backward compatible.

Since the format is internal-only, we reserve the right to change it between releases. Data migration scripts are common in frameworks, but also very simple to implement in SQLite: first, SQLite offers a pragma statement to store the version of the database. When the system starts, it compares the current version with the desired schema version, and, if a difference is found, runs through a slice of update statements to port the data to the desired state (schema and data changes).

var dbSchema = map[int][]string{ 
  // we use min int as a marker key for statements that should always run. 
  // it is such a large negative value that it is very unlikely to be hit by mistake. 
  minInt: {"pragma threads = 10;", "pragma synchronous = normal;", "pragma soft_heap_limit=31457280"}, // that’s 30mb 
  0: {"create table entities (key text primary key, value blob) without rowid;"}, 
  1: {"create virtual table search_col using fts5vocab(search, col);", "create virtual table search_instance using fts5vocab(search, instance);"}, 
  2: {"delete from entities where pkey = '/keyring'"}, }

We are also using SQLite as a document format for data interchange, and even in internal projects when we need a rich cache layer! Each of those projects uses a dedicated storage layer, which would be topics for a later installments (this post is already rather long).

What’s next?

Maintaining a public package is a high commitment (especially for small companies): accept responsibility for fixing bugs quickly, provide a quick and friendly response to feature requests, and maintain API stability, … At this stage, we are happy to accept bug report, but not feature requests, or even pull requests to review.

We think this package is best suited as a data point to think about designing storage layers (with, or without virtual tables) in a post-ORM world, and we are looking forward to seeing how other projects approach those problems.