Migrating a Synchronous better-sqlite3 Layer to Async Cloudflare D1 (Without Losing Local Dev)

I recently shipped a project to Cloudflare Workers and hit a wall that a lot of people hit: my data layer was built on better-sqlite3, which is synchronous and depends on a native Node addon. Neither of those things exists on the Workers runtime. Cloudflare's answer is D1, a SQLite-backed database, but it is only reachable through a Worker binding and its API is fully asynchronous.

The naive path is to rip out better-sqlite3 and rewrite everything against D1. That works until you try to run your tests, or next dev on your laptop, and discover you now need a live Cloudflare binding just to insert a row locally. In this tutorial I want to show the pattern I settled on instead: one async interface, two interchangeable backends, and a runtime check that picks the right one. Local dev and tests stay on fast in-process SQLite. Production runs on D1. Neither side knows about the other.

Step 1: make the interface async first

The single most important move is decoupling. Before you touch D1 at all, define a backend contract where every method returns a promise, even the ones that will be synchronous under better-sqlite3. Async is the lowest common denominator: you can always wrap a synchronous call in an async function, but you can never make an async call synchronous.

export interface RecordAttemptInput {
  sessionId: string;
  nickname: string;
  challengeId: string;
  input: string;
  cracked: boolean;
  reason: string;
  points: number;
}

/** The storage contract every backend implements. All methods are async. */
interface DbBackend {
  recordAttempt(input: RecordAttemptInput): Promise<AttemptRecord>;
  countAttempts(sessionId: string, challengeId: string): Promise<number>;
  hasCracked(sessionId: string, challengeId: string): Promise<boolean>;
  getLeaderboard(limit: number): Promise<LeaderboardRow[]>;
}

Every caller in the app now awaits these functions. That is a one-time refactor and it is the price of admission. Once it is done, both backends can satisfy the same shape.

Step 2: keep the SQL in one place

D1 speaks SQLite dialect, so the good news is your queries port almost verbatim. Pull the statements out into shared constants so both backends run identical SQL. Watch out for one thing: pragmas. journal_mode = WAL is meaningful for a local file but D1 does not accept it, so keep the schema pragma-free and apply WAL only on the SQLite side.

const SCHEMA_SQL = `
  CREATE TABLE IF NOT EXISTS attempts (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id   TEXT NOT NULL,
    challenge_id TEXT NOT NULL,
    cracked      INTEGER NOT NULL,
    points       INTEGER NOT NULL,
    created_at   TEXT NOT NULL DEFAULT (datetime('now'))
  );
`;

const INSERT_SQL = `INSERT INTO attempts
  (session_id, challenge_id, cracked, points) VALUES (?, ?, ?, ?)`;
const COUNT_SQL =
  "SELECT COUNT(*) AS n FROM attempts WHERE session_id = ? AND challenge_id = ?";
const BY_ID_SQL = "SELECT * FROM attempts WHERE id = ?";

Step 3: the better-sqlite3 backend

This backend wraps the synchronous driver in async methods. The one subtlety worth calling out is how you load the module. If you import better-sqlite3 at the top of the file, the Cloudflare build will try to bundle a native addon that cannot run on Workers, and the build breaks. Load it lazily through createRequire so the reference only resolves on the code path that actually uses it.

import { createRequire } from "node:module";
import type BetterSqlite3 from "better-sqlite3";

function loadBetterSqlite3(): typeof BetterSqlite3 {
  const require = createRequire(import.meta.url);
  return require("better-sqlite3");
}

function makeSqliteBackend(conn: BetterSqlite3.Database): DbBackend {
  return {
    async recordAttempt(input) {
      const info = conn
        .prepare(INSERT_SQL)
        .run(
          input.sessionId,
          input.challengeId,
          input.cracked ? 1 : 0,
          input.points,
        );
      return conn
        .prepare(BY_ID_SQL)
        .get(info.lastInsertRowid) as unknown as AttemptRecord;
    },
    async countAttempts(sessionId, challengeId) {
      const row = conn.prepare(COUNT_SQL).get(sessionId, challengeId) as {
        n: number;
      };
      return row.n;
    },
    // hasCracked, getLeaderboard follow the same shape
  } as DbBackend;
}

Note the boolean-to-integer coercion (input.cracked ? 1 : 0). SQLite has no native boolean, so this has to be explicit, and it is identical on both backends.

Step 4: the D1 backend

Same SQL, same method signatures, but now everything is genuinely async and uses D1's prepare().bind().run() chain. Two small API differences to internalize: D1 returns the inserted row id at res.meta.last_row_id rather than info.lastInsertRowid, and reads come back wrapped, so a single row is .first() and a set is res.results.

function makeD1Backend(d1: D1Database): DbBackend {
  return {
    async recordAttempt(input) {
      const res = await d1
        .prepare(INSERT_SQL)
        .bind(
          input.sessionId,
          input.challengeId,
          input.cracked ? 1 : 0,
          input.points,
        )
        .run();
      const id = res.meta.last_row_id;
      const row = await d1.prepare(BY_ID_SQL).bind(id).first();
      return row as unknown as AttemptRecord;
    },
    async countAttempts(sessionId, challengeId) {
      const row = await d1
        .prepare(COUNT_SQL)
        .bind(sessionId, challengeId)
        .first<{ n: number }>();
      return row?.n ?? 0;
    },
  } as DbBackend;
}

Because both makeSqliteBackend and makeD1Backend return a DbBackend, the rest of your app is completely blind to which one it got.

Step 5: detect the runtime and choose

Now the piece that ties it together. On the Workers runtime, D1 arrives as a binding on the Cloudflare context. Everywhere else (tests, next dev, plain Node) that context does not exist. So try to fetch the binding, and treat its absence as the signal to fall back to SQLite. The key detail: return null on failure instead of throwing, so importing this module never touches Worker-only code in a Node process.

async function getD1Binding(): Promise<D1Database | null> {
  try {
    const mod = await import("@opennextjs/cloudflare");
    const env = mod.getCloudflareContext().env as Partial<CloudflareEnv>;
    return env.DB ?? null;
  } catch {
    return null;
  }
}

let backendPromise: Promise<DbBackend> | null = null;

async function resolveBackend(): Promise<DbBackend> {
  const d1 = await getD1Binding();
  if (d1) return makeD1Backend(d1);
  return getFileSqliteBackend();
}

function getBackend(): Promise<DbBackend> {
  if (!backendPromise) backendPromise = resolveBackend();
  return backendPromise;
}

The public API is then a thin async wrapper over whichever backend resolved:

export async function recordAttempt(
  input: RecordAttemptInput,
): Promise<AttemptRecord> {
  return (await getBackend()).recordAttempt(input);
}

Step 6: tests stay on in-memory SQLite

This is the payoff. Your test suite never sees D1. Add a helper that installs an isolated in-memory SQLite backend as an override, and reset it in beforeEach.

let overrideBackend: DbBackend | null = null;

export function useInMemoryDb(): BetterSqlite3.Database {
  const Database = loadBetterSqlite3();
  const conn = new Database(":memory:");
  conn.exec(SCHEMA_SQL);
  overrideBackend = makeSqliteBackend(conn);
  backendPromise = null;
  return conn;
}

Have getBackend check overrideBackend first so it always wins in tests. Each test gets a clean database in microseconds with no network and no binding.

The honest caveat

This pattern gives you two backends that pass the same tests, but it does not give you two identical databases. Your tests exercise the SQLite path, never the real D1 path, so anything that diverges between them, such as D1's write-consistency model, its query size and duration limits, or subtle type-affinity differences, will not surface locally. Treat green tests as proof your logic and SQL are sound, not proof it behaves identically in production. I still run a smoke check against a real D1 instance before trusting a deploy, and you should too.

That trade is worth it. The alternative, needing a live Cloudflare binding to run a unit test, is far more painful day to day than one extra pre-deploy check.

If you want to see the whole thing wired together in a real app, including the migrations and the API routes that call this layer, it is all in the open source repo for injection-arena at github.com/AgentPostmortem/injection-arena. Clone it, run the tests with no Cloudflare account at all, and watch the same code light up D1 once it is deployed.