Theme 05 · Test-Confidence Oriented

SQLite/local substitutes

Explanation

SQLite/local substitutes

Plain Human Explanation

A local substitute is a test version of an outside system that still behaves enough like the real thing to catch meaningful mistakes. For database code, SQLite is often useful because it can enforce real constraints, run real queries, and roll back real transactions.

The point is not pretending SQLite is production. The point is avoiding fake repositories that accept impossible data and make every persistence test pass.

Technical Explanation

Use a local database, local queue, local file store, or in-memory adapter when it preserves the behavior the test cares about. For persistence, that usually means unique constraints, foreign keys, transaction boundaries, nullability, and query filtering.

Keep the substitute behind the same interface the application uses. Then the test can exercise application code with a realistic persistence boundary.

Why It Matters

User impact: bad saved data, duplicate records, and broken reads are caught before release.
Product behavior: tests can prove the stored state that future screens, jobs, and support tools depend on.
Risk: fake stores often skip the constraints that production relies on.
Decision point: use a local substitute when persistence behavior is part of the product promise.

The Core Move

Use the lightest local system that preserves the important production behavior. Call out what it does not cover, especially database-specific SQL differences.

Small Example

SQLite/local substitutes: Small Example

Bad TypeScript Example

type User = {
  id: string;
  email: string;
};

class FakeUserStore {
  private users: User[] = [];

  async create(user: User) {
    this.users.push(user);
  }
}

test("creates a user", async () => {
  const store = new FakeUserStore();

  await store.create({ id: "user_1", email: "ada@example.com" });
  await store.create({ id: "user_2", email: "ada@example.com" });

  expect(true).toBe(true);
});

type User = {
  id: string;
  email: string;
};

class FakeUserStore {
  private users: User[] = [];

  async create(user: User) {
    this.users.push(user);
  }
}

test("creates a user", async () => {
  const store = new FakeUserStore();

  await store.create({
    id: "user_1",
    email: "ada@example.com",
  });

  await store.create({
    id: "user_2",
    email: "ada@example.com",
  });

  expect(true).toBe(true);
});

Good TypeScript Example

import Database from "better-sqlite3";

type User = {
  id: string;
  email: string;
};

function readTotal(row: unknown): number {
  if (!row || typeof row !== "object" || !("total" in row) || typeof row.total !== "number") {
    throw new Error("count query returned an unexpected row");
  }

  return row.total;
}

function createUserStore(db: Database.Database) {
  db.exec(`
    create table users (
      id text primary key,
      email text not null unique
    )
  `);

  return {
    create(user: User) {
      db.prepare("insert into users (id, email) values (?, ?)").run(user.id, user.email);
    },
    count() {
      return readTotal(db.prepare("select count(*) total from users").get());
    },
  };
}

test("duplicate emails fail like real persistence", () => {
  const store = createUserStore(new Database(":memory:"));

  store.create({ id: "user_1", email: "ada@example.com" });

  expect(() => store.create({ id: "user_2", email: "ada@example.com" })).toThrow();
  expect(store.count()).toBe(1);
});

import Database from "better-sqlite3";

type User = {
  id: string;
  email: string;
};

function readTotal(row: unknown): number {
  if (
    !row ||
    typeof row !== "object" ||
    !("total" in row) ||
    typeof row.total !== "number"
  ) {
    throw new Error(
      "count query returned an unexpected row",
    );
  }

  return row.total;
}

function createUserStore(
  db: Database.Database,
) {
  db.exec(`
    create table users (
      id text primary key,
      email text not null unique
    )
  `);

  return {
    create(user: User) {
      db.prepare(
        "insert into users (id, email) values (?, ?)",
      ).run(user.id, user.email);
    },
    count() {
      return readTotal(
        db
          .prepare(
            "select count(*) total from users",
          )
          .get(),
      );
    },
  };
}

test("duplicate emails fail like real persistence", () => {
  const store = createUserStore(
    new Database(":memory:"),
  );

  store.create({
    id: "user_1",
    email: "ada@example.com",
  });

  expect(() =>
    store.create({
      id: "user_2",
      email: "ada@example.com",
    }),
  ).toThrow();

  expect(store.count()).toBe(1);
});

What Changed

The bad fake allows duplicate emails, so it cannot catch a real data-integrity bug.
The good test uses a local database with the same uniqueness rule the product depends on.
The assertion checks persisted state after the rejected write.

Realistic Example

SQLite/local substitutes: Realistic Example

Subscription changes often need transactions. A local database substitute can prove that partial writes do not leak when the second write fails.

Bad TypeScript Example

type SubscriptionStore = {
  updateStatus(customerId: string, status: string): Promise<void>;
  insertAudit(customerId: string, event: string): Promise<void>;
};

async function cancelSubscription(customerId: string, store: SubscriptionStore) {
  await store.updateStatus(customerId, "canceled");
  await store.insertAudit(customerId, "subscription.canceled");
}

test("cancels subscription", async () => {
  const calls: string[] = [];
  const store: SubscriptionStore = {
    async updateStatus() {
      calls.push("updated");
    },
    async insertAudit() {
      throw new Error("audit failed");
    },
  };

  await expect(cancelSubscription("cus_1", store)).rejects.toThrow("audit failed");
  expect(calls).toEqual(["updated"]);
});

type SubscriptionStore = {
  updateStatus(
    customerId: string,
    status: string,
  ): Promise<void>;
  insertAudit(
    customerId: string,
    event: string,
  ): Promise<void>;
};

async function cancelSubscription(
  customerId: string,
  store: SubscriptionStore,
) {
  await store.updateStatus(
    customerId,
    "canceled",
  );

  await store.insertAudit(
    customerId,
    "subscription.canceled",
  );
}

test("cancels subscription", async () => {
  const calls: string[] = [];

  const store: SubscriptionStore = {
    async updateStatus() {
      calls.push("updated");
    },
    async insertAudit() {
      throw new Error("audit failed");
    },
  };

  await expect(
    cancelSubscription("cus_1", store),
  ).rejects.toThrow("audit failed");

  expect(calls).toEqual(["updated"]);
});

Good TypeScript Example

import Database from "better-sqlite3";

type SubscriptionStatus = "active" | "canceled";

function readSubscriptionStatus(row: unknown): SubscriptionStatus {
  if (!row || typeof row !== "object" || !("status" in row)) {
    throw new Error("status query returned an unexpected row");
  }

  if (row.status !== "active" && row.status !== "canceled") {
    throw new Error("status query returned an unknown status");
  }

  return row.status;
}

function createSubscriptionStore(db: Database.Database) {
  db.exec(`
    create table subscriptions (
      customer_id text primary key,
      status text not null
    );

    create table subscription_audit (
      id integer primary key,
      customer_id text not null references subscriptions(customer_id),
      event text not null unique
    );
  `);

  return {
    seed(customerId: string, status: SubscriptionStatus) {
      db.prepare("insert into subscriptions (customer_id, status) values (?, ?)").run(customerId, status);
    },
    seedAudit(customerId: string, event: string) {
      db.prepare("insert into subscription_audit (customer_id, event) values (?, ?)").run(customerId, event);
    },
    cancel(customerId: string, event: string) {
      const transaction = db.transaction(() => {
        db.prepare("update subscriptions set status = ? where customer_id = ?").run("canceled", customerId);
        db.prepare("insert into subscription_audit (customer_id, event) values (?, ?)").run(customerId, event);
      });

      transaction();
    },
    status(customerId: string) {
      return readSubscriptionStatus(
        db.prepare("select status from subscriptions where customer_id = ?").get(customerId),
      );
    },
  };
}

test("failed audit write rolls back cancellation", () => {
  const store = createSubscriptionStore(new Database(":memory:"));
  store.seed("cus_1", "active");
  store.seedAudit("cus_1", "subscription.canceled");

  expect(() => store.cancel("cus_1", "subscription.canceled")).toThrow();
  expect(store.status("cus_1")).toBe("active");
});

import Database from "better-sqlite3";

type SubscriptionStatus =
  | "active"
  | "canceled";

function readSubscriptionStatus(
  row: unknown,
): SubscriptionStatus {
  if (
    !row ||
    typeof row !== "object" ||
    !("status" in row)
  ) {
    throw new Error(
      "status query returned an unexpected row",
    );
  }

  if (
    row.status !== "active" &&
    row.status !== "canceled"
  ) {
    throw new Error(
      "status query returned an unknown status",
    );
  }

  return row.status;
}

function createSubscriptionStore(
  db: Database.Database,
) {
  db.exec(`
    create table subscriptions (
      customer_id text primary key,
      status text not null
    );

    create table subscription_audit (
      id integer primary key,
      customer_id text not null references subscriptions(customer_id),
      event text not null unique
    );
  `);

  return {
    seed(
      customerId: string,
      status: SubscriptionStatus,
    ) {
      db.prepare(
        "insert into subscriptions (customer_id, status) values (?, ?)",
      ).run(customerId, status);
    },
    seedAudit(
      customerId: string,
      event: string,
    ) {
      db.prepare(
        "insert into subscription_audit (customer_id, event) values (?, ?)",
      ).run(customerId, event);
    },
    cancel(
      customerId: string,
      event: string,
    ) {
      const transaction = db.transaction(
        () => {
          db.prepare(
            "update subscriptions set status = ? where customer_id = ?",
          ).run("canceled", customerId);

          db.prepare(
            "insert into subscription_audit (customer_id, event) values (?, ?)",
          ).run(customerId, event);
        },
      );

      transaction();
    },
    status(customerId: string) {
      return readSubscriptionStatus(
        db
          .prepare(
            "select status from subscriptions where customer_id = ?",
          )
          .get(customerId),
      );
    },
  };
}

test("failed audit write rolls back cancellation", () => {
  const store = createSubscriptionStore(
    new Database(":memory:"),
  );

  store.seed("cus_1", "active");

  store.seedAudit(
    "cus_1",
    "subscription.canceled",
  );

  expect(() =>
    store.cancel(
      "cus_1",
      "subscription.canceled",
    ),
  ).toThrow();

  expect(store.status("cus_1")).toBe(
    "active",
  );
});

What Changed

The bad test only tracks calls and cannot prove rollback behavior.
The good test uses real tables, a real transaction, and a real uniqueness failure.
The product promise is visible: a failed audit write must not leave the subscription half-canceled.

System Example

SQLite/local substitutes: System Example

At system scale, local substitutes are strongest when they protect the persistence behaviors that other features depend on.

Larger System-Level Bad TypeScript Example

class FakeImportStore {
  contacts: Array<{
    accountId: string;
    email: string;
  }> = [];

  async insertContact(contact: { accountId: string; email: string }) {
    this.contacts.push(contact);
  }

  async listContacts(accountId: string) {
    return this.contacts.filter((contact) => contact.accountId === accountId);
  }
}

test("imports contacts", async () => {
  const store = new FakeImportStore();

  await importContacts("acct_1", ["Ada@Example.com", "ada@example.com"], store);

  expect(await store.listContacts("acct_1")).toHaveLength(2);
});

class FakeImportStore {
  contacts: Array<{
    accountId: string;
    email: string;
  }> = [];

  async insertContact(contact: {
    accountId: string;
    email: string;
  }) {
    this.contacts.push(contact);
  }

  async listContacts(accountId: string) {
    return this.contacts.filter(
      (contact) =>
        contact.accountId === accountId,
    );
  }
}

test("imports contacts", async () => {
  const store = new FakeImportStore();

  await importContacts(
    "acct_1",
    ["Ada@Example.com", "ada@example.com"],
    store,
  );

  expect(
    await store.listContacts("acct_1"),
  ).toHaveLength(2);
});

Larger System-Level Good TypeScript Example

import Database from "better-sqlite3";

type ContactStore = {
  insertContact(contact: { accountId: string; email: string }): Promise<void>;
  listContacts(accountId: string): Promise<
    Array<{
      accountId: string;
      email: string;
    }>
  >;
};

function readContactRows(rows: unknown[]): Array<{
  accountId: string;
  email: string;
}> {
  return rows.map((row) => {
    if (!row || typeof row !== "object" || !("accountId" in row) || !("email" in row) || typeof row.accountId !== "string" || typeof row.email !== "string") {
      throw new Error("contact query returned an unexpected row");
    }

    return { accountId: row.accountId, email: row.email };
  });
}

async function importContacts(accountId: string, emails: string[], store: ContactStore) {
  for (const email of emails) {
    await store.insertContact({ accountId, email: email.trim().toLowerCase() });
  }
}

function createSqliteContactStore(db: Database.Database): ContactStore {
  db.exec(`
    create table contacts (
      account_id text not null,
      email text not null,
      unique(account_id, email)
    )
  `);

  return {
    async insertContact(contact) {
      db.prepare("insert or ignore into contacts (account_id, email) values (?, ?)").run(contact.accountId, contact.email);
    },
    async listContacts(accountId) {
      const rows = db.prepare("select account_id accountId, email from contacts where account_id = ? order by email").all(accountId);

      return readContactRows(rows);
    },
  };
}

test("contact import normalizes and deduplicates per account", async () => {
  const store = createSqliteContactStore(new Database(":memory:"));

  await importContacts("acct_1", ["Ada@Example.com", " ada@example.com "], store);
  await importContacts("acct_2", ["ada@example.com"], store);

  await expect(store.listContacts("acct_1")).resolves.toEqual([{ accountId: "acct_1", email: "ada@example.com" }]);
  await expect(store.listContacts("acct_2")).resolves.toEqual([{ accountId: "acct_2", email: "ada@example.com" }]);
});

import Database from "better-sqlite3";

type ContactStore = {
  insertContact(contact: {
    accountId: string;
    email: string;
  }): Promise<void>;
  listContacts(accountId: string): Promise<
    Array<{
      accountId: string;
      email: string;
    }>
  >;
};

function readContactRows(
  rows: unknown[],
): Array<{
  accountId: string;
  email: string;
}> {
  return rows.map((row) => {
    if (
      !row ||
      typeof row !== "object" ||
      !("accountId" in row) ||
      !("email" in row) ||
      typeof row.accountId !== "string" ||
      typeof row.email !== "string"
    ) {
      throw new Error(
        "contact query returned an unexpected row",
      );
    }

    return {
      accountId: row.accountId,
      email: row.email,
    };
  });
}

async function importContacts(
  accountId: string,
  emails: string[],
  store: ContactStore,
) {
  for (const email of emails) {
    await store.insertContact({
      accountId,
      email: email.trim().toLowerCase(),
    });
  }
}

function createSqliteContactStore(
  db: Database.Database,
): ContactStore {
  db.exec(`
    create table contacts (
      account_id text not null,
      email text not null,
      unique(account_id, email)
    )
  `);

  return {
    async insertContact(contact) {
      db.prepare(
        "insert or ignore into contacts (account_id, email) values (?, ?)",
      ).run(
        contact.accountId,
        contact.email,
      );
    },
    async listContacts(accountId) {
      const rows = db
        .prepare(
          "select account_id accountId, email from contacts where account_id = ? order by email",
        )
        .all(accountId);

      return readContactRows(rows);
    },
  };
}

test("contact import normalizes and deduplicates per account", async () => {
  const store = createSqliteContactStore(
    new Database(":memory:"),
  );

  await importContacts(
    "acct_1",
    [
      "Ada@Example.com",
      " ada@example.com ",
    ],
    store,
  );

  await importContacts(
    "acct_2",
    ["ada@example.com"],
    store,
  );

  await expect(
    store.listContacts("acct_1"),
  ).resolves.toEqual([
    {
      accountId: "acct_1",
      email: "ada@example.com",
    },
  ]);

  await expect(
    store.listContacts("acct_2"),
  ).resolves.toEqual([
    {
      accountId: "acct_2",
      email: "ada@example.com",
    },
  ]);
});

What Changed

The bad fake stores duplicate contacts and teaches the test the wrong behavior.
The good substitute enforces the account-and-email uniqueness rule locally.
The test proves query filtering too, so one account’s import does not hide another account’s data.

When To Use It

SQLite/local substitutes: When To Use It

Use This When

The behavior depends on constraints, joins, filtering, transactions, or ordering.
A fake repository would accept impossible data or skip a production rule.
The test needs persisted state but should stay fast and local.

Avoid This When

The production database uses features SQLite cannot represent and those features are the point of the test.
The code under test is pure domain logic with no persistence behavior.
A real database container is already cheap, reliable, and part of the repo gate.

Tradeoffs

SQLite and local substitutes are faster than full infrastructure, but they are not perfect copies of production. Be explicit about which behavior the substitute covers and keep provider-specific behavior in a smaller adapter or migration test.

Integration tests
Real seams
Observable behavior over mocks/spies

Practice Prompt

SQLite/local substitutes: Practice Prompt

Beginner Exercise

Find a fake store in a test. List one database behavior it does not enforce: uniqueness, foreign keys, transactions, ordering, or filtering.

Intermediate Exercise

Replace that fake with a local store that preserves the missing behavior. Keep the application-facing interface the same.

Stretch Exercise

Add a test that proves rollback, duplicate handling, or account scoping using real local persistence.

Reflection Question

What production database behavior does your local substitute cover, and what behavior still needs a separate check?

Suggest an edit

Leave a private editorial note. This creates a GitHub issue for this curriculum page.

Explanation

SQLite/local substitutes

Plain Human Explanation

Technical Explanation

Why It Matters

The Core Move

Small Example

SQLite/local substitutes: Small Example

Bad TypeScript Example

Good TypeScript Example

What Changed

Realistic Example

SQLite/local substitutes: Realistic Example

Bad TypeScript Example

Good TypeScript Example

What Changed

System Example

SQLite/local substitutes: System Example

Larger System-Level Bad TypeScript Example

Larger System-Level Good TypeScript Example

What Changed

When To Use It

SQLite/local substitutes: When To Use It

Use This When

Avoid This When

Tradeoffs

Related Concepts

Practice Prompt

SQLite/local substitutes: Practice Prompt

Beginner Exercise

Intermediate Exercise

Stretch Exercise

Reflection Question

Suggest an edit