https://curling.io/assets/js/7912ff73.79dcc67f.js

1"use strict";(self.webpackChunkdocs=self.webpackChunkdocs||[]).push([[8824],{28453:(e,t,s)=>{s.d(t,{R:()=>o,x:()=>r});var n=s(96540);const a={},i=n.createContext(a);function o(e){const t=n.useContext(i);return n.useMemo((function(){return"function"==typeof e?e(t):{...t,...e}}),[t,e])}function r(e){let t;return t=e.disableParentContext?"function"==typeof e.components?e.components(a):e.components||a:o(e.components),n.createElement(i.Provider,{value:t},e.children)}},62726:e=>{e.exports=JSON.parse('{"permalink":"/blog/sqlite-test-isolation","source":"@site/blog/2026-03-03-sqlite-test-isolation.md","title":"Test Isolation for Free with SQLite","description":"This is a technical implementation note about the testing architecture for","date":"2026-03-03T00:00:00.000Z","tags":[{"inline":true,"label":"foundation","permalink":"/blog/tags/foundation"},{"inline":true,"label":"sqlite","permalink":"/blog/tags/sqlite"},{"inline":true,"label":"testing","permalink":"/blog/tags/testing"},{"inline":true,"label":"architecture","permalink":"/blog/tags/architecture"}],"readingTime":8.68,"hasTruncateMarker":true,"authors":[{"name":"Dave Rapin","title":"Founder @ Curling IO","imageURL":"https://avatars.githubusercontent.com/u/1202?v=4","key":"dave","page":null}],"frontMatter":{"slug":"sqlite-test-isolation","title":"Test Isolation for Free with SQLite","date":"2026-03-03T00:00:00.000Z","authors":["dave"],"tags":["foundation","sqlite","testing","architecture"]},"unlisted":false,"prevItem":{"title":"Every Provincial and Territorial Curling Association in Canada Uses Curling IO","permalink":"/blog/curling-io-powers-provincial-associations"},"nextItem":{"title":"What to Look for in Curling Club Management Software","permalink":"/blog/what-to-look-for-in-curling-club-management-software"}}')},72145:(e,t,s)=>{s.r(t),s.d(t,{assets:()=>l,contentTitle:()=>r,default:()=>d,frontMatter:()=>o,metadata:()=>n,toc:()=>c});var n=s(62726),a=s(74848),i=s(28453);const o={slug:"sqlite-test-isolation",title:"Test Isolation for Free with SQLite",date:new Date("2026-03-03T00:00:00.000Z"),authors:["dave"],tags:["foundation","sqlite","testing","architecture"]},r=void 0,l={authorsImageUrls:[void 0]},c=[{value:"The Shared Database Problem",id:"the-shared-database-problem",level:2},{value:"In-Memory SQLite: One Database Per Test",id:"in-memory-sqlite-one-database-per-test",level:2},{value:"The Schema Execution Cost",id:"the-schema-execution-cost",level:2},{value:"SQLite's Backup API: Page-Level Cloning",id:"sqlites-backup-api-page-level-cloning",level:2},{value:"The Numbers",id:"the-numbers",level:2},{value:"Why This Works So Well",id:"why-this-works-so-well",level:2},{value:"Trade-offs",id:"trade-offs",level:2},{value:"This Isn't BEAM-Specific",id:"this-isnt-beam-specific",level:2},{value:"The Full Stack",id:"the-full-stack",level:2}];function h(e){const t={a:"a",admonition:"admonition",code:"code",em:"em",h2:"h2",hr:"hr",li:"li",p:"p",pre:"pre",strong:"strong",table:"table",tbody:"tbody",td:"td",th:"th",thead:"thead",tr:"tr",ul:"ul",...(0,i.R)(),...e.components},{Details:s}=t;return s||function(e,t){throw new Error("Expected "+(t?"component":"object")+" `"+e+"` to be defined: you likely forgot to import, pass, or provide it.")}("Details",!0),(0,a.jsxs)(a.Fragment,{children:[(0,a.jsx)(t.admonition,{title:"About this post",type:"note",children:(0,a.jsx)(t.p,{children:"This is a technical implementation note about the testing architecture for\nCurling IO v3. It is written for software engineers and goes deeper into\nSQLite, in-memory databases, the backup API, and test isolation than our usual\nproduct posts."})}),"\n",(0,a.jsx)(t.p,{children:"Curling IO's tests don't need a shared test database, cleanup hooks, or transaction tricks. Each test gets its own database, so a test can pass alone or in the full suite for the same reason: nothing else can touch its data."}),"\n",(0,a.jsx)(t.p,{children:"That falls out of one Version 3 choice: SQLite runs in-process. Each test gets a completely independent in-memory 

1SQLite database, cloned from a template in microseconds using SQLite's backup API."}),"\n",(0,a.jsx)(t.h2,{id:"the-shared-database-problem",children:"The Shared Database Problem"}),"\n",(0,a.jsx)(t.p,{children:"In a traditional Postgres or MySQL test setup, every test talks to the same database server. You need a strategy to keep tests from contaminating each other:"}),"\n",(0,a.jsxs)(t.p,{children:[(0,a.jsx)(t.strong,{children:"Transaction rollback."})," Wrap each test in a transaction, roll it back at the end. Works until your code uses transactions internally, or spawns processes that need to see the test data (the classic Ecto sandbox problem)."]}),"\n",(0,a.jsxs)(t.p,{children:[(0,a.jsx)(t.strong,{children:"Truncation."})," Delete all rows from every table between tests. Slow, and you need to get the table ordering right to avoid foreign key violations."]}),"\n",(0,a.jsxs)(t.p,{children:[(0,a.jsx)(t.strong,{children:"Database cleaner."})," A gem/library that combines both strategies with configuration for which tables to clean, which strategy to use, and when. It works, but it's ceremony that exists purely because of the shared database."]}),"\n",(0,a.jsx)(t.p,{children:"All of these are workarounds for the same architectural constraint: one database server, many tests."}),"\n",(0,a.jsx)(t.h2,{id:"in-memory-sqlite-one-database-per-test",children:"In-Memory SQLite: One Database Per Test"}),"\n",(0,a.jsxs)(t.p,{children:["SQLite opens a database by passing a file path. Pass ",(0,a.jsx)(t.code,{children:":memory:"})," instead and you get an in-memory database that exists only for the lifetime of that connection. It's fast (no disk I/O) and completely isolated. Nothing else can see it, and closing the connection frees everything."]}),"\n",(0,a.jsxs)(s,{children:[(0,a.jsx)("summary",{children:"Basic setup example"}),(0,a.jsx)(t.p,{children:"Our test setup function:"}),(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{className:"language-gleam",children:'pub fn setup() -> sqlight.Connection {\n  let assert Ok(conn) = sqlight.open(":memory:")\n  let assert Ok(_) = sqlight.exec("PRAGMA foreign_keys=ON;", conn)\n  let assert Ok(_) = sqlight.exec(schema_sql, conn)\n  conn\n}\n'})}),(0,a.jsxs)(t.p,{children:["The ",(0,a.jsx)(t.code,{children:"schema_sql"})," variable contains the contents of ",(0,a.jsx)(t.code,{children:"db/schema.sql"}),", which is regenerated from migrations by ",(0,a.jsx)(t.code,{children:"bin/reset"}),". This means tests always use the current schema without anyone remembering to update them."]}),(0,a.jsxs)(t.p,{children:["Every test calls ",(0,a.jsx)(t.code,{children:"setup()"}),", gets a fresh database with the full schema, inserts whatever test data it needs, and runs its assertions. When the test ends, the connection is garbage collected and the database disappears. There's nothing to clean up."]}),(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{className:"language-gleam",children:"pub fn should_reject_duplicate_registration_test() {\n  let conn = test_db.setup()\n  // Insert test-specific data\n  insert_org(conn, test_org())\n  insert_listing(conn, test_listing())\n  // ... test logic, assertions\n  // conn goes out of scope, database vanishes\n}\n"})})]}),"\n",(0,a.jsx)(t.p,{children:"There's no possible cross-contamination because there's nothing shared. Test A and test B literally operate on different databases. You can run them in parallel on BEAM processes without any coordination."}),"\n",(0,a.jsx)(t.h2,{id:"the-schema-execution-cost",children:"The Schema Execution Cost"}),"\n",(0,a.jsxs)(t.p,{children:["This approach has an obvious cost: every test executes the full schema. For us that's 77 ",(0,a.jsx)(t.code,{children:"CREATE TABLE"}),", ",(0,a.jsx)(t.code,{children:"CREATE INDEX"}),", and ",(0,a.jsx)(t.code,{children:"CREATE VIEW"})," statements. We profiled it:"]}),"\n",(0,a.jsxs)(t.ul,{children:["\n",(0,a.jsxs)(t.li,{children:["Opening an in-memory SQLite database: ",(0,a.jsx)(t.strong,{children:"15 microseconds"})]}),"\n",(0,a.jsxs)(t.li,{children:["Executing 77 schema statements: ",(0,a.jsx)(t.strong,{children:"1,100 microseconds"})," (1.1 ms)"]}),"\n"]}),"\n",(0,a.jsx)(t.p,{children:"At 1.1 ms per test across ~500 tests that need a database, that's about 550 ms of schema execution. Not terrible, but not free either. And it scales linearly with both the number of tests and the size of your schema."}),"\n",(0,a.jsx)(t.h2,{id:"sqlites-backup-api-page-level-cloning",children:"SQLite's Backup API: Page-Level Cloning"}),"\n",(0,a.jsxs)(t.p,{children:["SQLite has a ",(0,a.jsx)(t.a,{href:"https://www.sqlite.org/backup.html",children:"backup API"}

1)," designed for copying databases between connections. It operates at the page level: it doesn't re-parse or re-execute SQL, it copies raw database pages from one connection to another."]}),"\n",(0,a.jsxs)(s,{children:[(0,a.jsx)("summary",{children:"Template cloning implementation"}),(0,a.jsx)(t.p,{children:"The idea: build the schema once in a template database, then clone it per test."}),(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{children:'Once at startup:  open(":memory:") \u2192 execute full schema \u2192 template\nPer test:         open(":memory:") \u2192 backup_copy(template) \u2192 ready\n'})}),(0,a.jsxs)(t.p,{children:["The backup API is three calls: ",(0,a.jsx)(t.code,{children:"backup_init"})," (set up the copy), ",(0,a.jsx)(t.code,{children:"backup_step"})," with ",(0,a.jsx)(t.code,{children:"-1"})," (copy all pages in one shot), and ",(0,a.jsx)(t.code,{children:"backup_finish"})," (release resources)."]}),(0,a.jsxs)(t.p,{children:["Our Gleam SQLite driver, ",(0,a.jsx)(t.a,{href:"https://hexdocs.pm/sqlight/",children:"sqlight"}),", doesn't expose the backup API. But it's built on ",(0,a.jsx)(t.a,{href:"https://github.com/mmzeeman/esqlite",children:"esqlite"}),", an Erlang NIF wrapper around SQLite's C library, and esqlite exposes the full backup API. Since Gleam compiles to Erlang and runs on the BEAM, we can call Erlang libraries directly through Gleam's foreign function interface (FFI). We wrote a 12-line Erlang module that takes two sqlight connections (which are just esqlite records under the hood) and performs the clone:"]}),(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{className:"language-erlang",children:'-module(test_db_ffi).\n-export([clone_db/1]).\n\nclone_db(Template) ->\n    {ok, Dest} = esqlite3:open(":memory:"),\n    {ok, Backup} = esqlite3:backup_init(Dest, "main", Template, "main"),\n    \'$done\' = esqlite3:backup_step(Backup, -1),\n    ok = esqlite3:backup_finish(Backup),\n    {ok, Dest}.\n'})}),(0,a.jsx)(t.p,{children:"On the Gleam side, we declare the FFI binding and call it like any other function:"}),(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{className:"language-gleam",children:'@external(erlang, "test_db_ffi", "clone_db")\nfn clone_db(\n  template: sqlight.Connection,\n) -> Result(sqlight.Connection, Nil)\n'})}),(0,a.jsx)(t.p,{children:"This is one of the nice things about the BEAM ecosystem. When your high-level driver doesn't expose what you need, the lower-level library almost always does, and the FFI boundary is trivial to cross."}),(0,a.jsxs)(t.p,{children:["The template is created once per test run and cached in BEAM's ",(0,a.jsx)(t.code,{children:"persistent_term"})," (a global immutable store optimized for read-heavy access):"]}),(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{className:"language-gleam",children:'fn template_db() -> sqlight.Connection {\n  case get_cached("test_template_db") {\n    Ok(conn) -> conn\n    Error(Nil) -> {\n      let assert Ok(conn) = sqlight.open(":memory:")\n      let assert Ok(_) = sqlight.exec("PRAGMA foreign_keys=ON;", conn)\n      let assert Ok(_) = sqlight.exec(schema_sql, conn)\n      cache("test_template_db", conn)\n      conn\n    }\n  }\n}\n\npub fn setup() -> sqlight.Connection {\n  let assert Ok(conn) = clone_db(template_db())\n  let assert Ok(_) = sqlight.exec("PRAGMA foreign_keys=ON;", conn)\n  conn\n}\n'})}),(0,a.jsxs)(t.p,{children:["The ",(0,a.jsx)(t.code,{children:"PRAGMA foreign_keys=ON"})," runs after the clone because SQLite pragma settings are per-connection, not stored in the database file. It's a single statement, negligible cost."]})]}),"\n",(0,a.jsx)(t.h2,{id:"the-numbers",children:"The Numbers"}),"\n",(0,a.jsx)(t.p,{children:"At the time we switched to the clone method, we had 594 tests and 77 schema objects. We're still early in the rewrite and already approaching 1,000 tests. By launch we expect closer to twice the schema objects and s

1omewhere around 5,000 tests."}),"\n",(0,a.jsx)(t.p,{children:"Here's what we measured at 594 tests:"}),"\n",(0,a.jsxs)(t.table,{children:[(0,a.jsx)(t.thead,{children:(0,a.jsxs)(t.tr,{children:[(0,a.jsx)(t.th,{children:"Approach"}),(0,a.jsx)(t.th,{children:"Server test time"})]})}),(0,a.jsxs)(t.tbody,{children:[(0,a.jsxs)(t.tr,{children:[(0,a.jsx)(t.td,{children:"Execute full schema per test"}),(0,a.jsx)(t.td,{children:"3.2 s"})]}),(0,a.jsxs)(t.tr,{children:[(0,a.jsx)(t.td,{children:"Clone template via backup API"}),(0,a.jsx)(t.td,{children:"2.6 s"})]})]})]}),"\n",(0,a.jsx)(t.p,{children:"The schema execution cost dropped from ~550 ms to near zero. The remaining 2.6 seconds is actual test logic (inserting data, running business logic, assertions) plus BEAM VM startup overhead."}),"\n",(0,a.jsxs)(t.p,{children:["The savings become more meaningful at scale. In a traditional Postgres setup, you'd use truncation between tests, issuing ",(0,a.jsx)(t.code,{children:"TRUNCATE TABLE"})," on every table, in foreign-key-safe order, over a socket to the database server. With around 100 tables and 5,000 tests (a conservative estimate for our full rewrite, realistically 2-3x the tests), that's 500,000 truncation statements hitting a database server. Even at sub-millisecond per truncate, it adds up."]}),"\n",(0,a.jsx)(t.p,{children:"With the backup approach, the clone cost is ~25 microseconds per test regardless of schema size. At 5,000 tests that's 0.125 seconds total. No truncation ordering, no socket round-trips."}),"\n",(0,a.jsx)(t.p,{children:"For context, this is a real application test suite covering cart operations, payment processing, registration validation, affiliate fees, waivers, round robin generation, scheduling, scoring, and more. Not trivial tests."}),"\n",(0,a.jsx)(t.h2,{id:"why-this-works-so-well",children:"Why This Works So Well"}),"\n",(0,a.jsx)(t.p,{children:"The backup API is a nice optimization, but the real win is that in-process SQLite eliminates the shared database problem entirely."}),"\n",(0,a.jsx)(t.p,{children:"With Postgres, your test process talks to a database server over a socket. Every test that writes data is writing to the same place. You need isolation strategies because the architecture demands them."}),"\n",(0,a.jsx)(t.p,{children:"With in-process SQLite, the database lives in your process's memory. Creating a new one is a memory allocation, not a network connection. There's no server to coordinate with and no connection pool to manage. The isolation falls out of the architecture for free."}),"\n",(0,a.jsx)(t.p,{children:"This also means:"}),"\n",(0,a.jsxs)(t.ul,{children:["\n",(0,a.jsx)(t.li,{children:"No test ordering dependencies. Each test is fully self-contained."}),"\n",(0,a.jsx)(t.li,{children:"Safe parallelism. BEAM processes each get their own database, zero coordination needed."}),"\n",(0,a.jsxs)(t.li,{children:["No cleanup code. No ",(0,a.jsx)(t.code,{children:"teardown"}),", no ",(0,a.jsx)(t.code,{children:"after_each"}),", no ",(0,a.jsx)(t.code,{children:"database_cleaner"}),"."]}),"\n",(0,a.jsx)(t.li,{children:"No flaky tests from shared state leaking between runs."}),"\n"]}),"\n",(0,a.jsx)(t.h2,{id:"trade-offs",children:"Trade-offs"}),"\n",(0,a.jsx)(t.p,{children:"This isn't free of trade-offs. You're not testing against the same database engine you run in production... unless you also run SQLite in production, which we do. If you're using SQLite for tests but Postgres in production, you'd miss Postgres-specific behavior (custom types, advisory locks, jsonb operators, etc.)."}),"\n",(0,a.jsx)(t.p,{children:"The other trade-off is that in our setup, each test builds up its own data from scratch. In practice this is a feature (every test explicitly declares its dependencies) but it does mean more setup code per test compared to a shared fixtures approach. That said, this is a choice, not a limitation of the technique. You could just as easily insert seed data into the template database before caching it, and every clone would start with that data pre-loaded. If your test suite benefits from a well-defined set of standard users, organizations, or other reference data, seeding the template is a straightforward way to reduce per-test setup while keeping full isolation."}),"\n",(0,a.jsx)(t.h2,{id:"this-isnt-beam-specific",children:"This Isn't BEAM-Specific"}),"\n",(0,a.jsx)(t.p,{children:"Our examples are in Gleam and Erlang, but the backup API is a C-level SQLite feature. Most languages expose it:"}),"\n",(0,a.jsxs)(s,{children:[(0,a.jsx)("summary",{children:"Examples in Python, Node.js, and Rust"}),(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{className:"language-python",children:'# Python 3.7+ has it built in\nsource = sqlite3.connect(":memory:")\nsource.executescript(schema_sql)\n\ndest = sqlite3.connect(":memory:")\nsource.backup(dest)\n'})}),(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{className:"language-javascript",children:'// Node.js better-sqlite3\nconst template = new Database(":memory:");\ntemplate.exec(schemaSQL);\n\nconst dest = template.backup(":memory:");\n'})}),(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{className:"language-rust",children:"// Rust rusqlite\nlet template = Connection::open_in_memory()?;\ntemplate.execute_batch(schema_sql)?;\n\nlet mut dest = Connection::open_in_memory()?;\nlet backup = backup::Backup::new(&template, &mut dest)?;\nbackup.run_to_completion(5, Duration::ZERO, None)?;\n"})})]}),"\n",(0,a.jsx)(t.p,{children:"The pattern is the same in every language: build a template once, clone it per test via the backup API. The FFI detour we took through esqlite is only necessary because our particular Gleam driver doesn't expose it yet."}),"\n",(0,a.jsx)(t.h2,{id:"the-full-stack",children:"The Full Stack"}

1),"\n",(0,a.jsx)(t.p,{children:"Here's what our test infrastructure looks like in its entirety:"}),"\n",(0,a.jsxs)(t.ul,{children:["\n",(0,a.jsxs)(t.li,{children:[(0,a.jsx)(t.code,{children:"test_db.setup()"})," clones a cached template database via SQLite backup API"]}),"\n",(0,a.jsxs)(t.li,{children:[(0,a.jsx)(t.code,{children:"test_db.tz_db()"})," returns a cached timezone database (loaded once, not per test)"]}),"\n",(0,a.jsxs)(t.li,{children:[(0,a.jsx)(t.code,{children:"persistent_term"})," is BEAM's global cache for the template DB and timezone data"]}),"\n",(0,a.jsx)(t.li,{children:"12 lines of Erlang FFI wraps the SQLite backup API"}),"\n"]}),"\n",(0,a.jsx)(t.p,{children:"That's the entire test infrastructure. No test framework plugins, no database cleaner gems, no truncation strategies. SQLite and 12 lines of Erlang."}),"\n",(0,a.jsx)(t.hr,{}),"\n",(0,a.jsx)(t.p,{children:(0,a.jsxs)(t.em,{children:["This is Part 6 of the Curling IO Foundation series. Next up: ",(0,a.jsx)(t.a,{href:"/blog/parallel-tests-for-free",children:"Parallel Tests for Free"}),"."]})})]})}function d(e={}){const{wrapper:t}={...(0,i.R)(),...e.components};return t?(0,a.jsx)(t,{...e,children:(0,a.jsx)(h,{...e})}):h(e)}}}]);