Knex migrations, seeds and queries on better-sqlite3: working versions (tested 2026-10-15)

{
  "name": "knex-better-sqlite3-migrations",
  "private": true,
  "type": "module",
  "scripts": {
    "test": "node --import tsx --test test/*.test.ts"
  },
  "dependencies": {
    "knex": "3.3.0",
    "better-sqlite3": "13.0.3"
  },
  "devDependencies": {
    "tsx": "4.23.15",
    "@types/node": "26.6.5"
  }
}

import knexFactory, { type Knex } from "knex";

type Migration = { up: (k: Knex) => Promise<void>; down: (k: Knex) => Promise<void> };

/** Migrations kept in code. A custom migrationSource avoids loader quirks with .ts files on disk. */
export const migrations: Record<string, Migration> = {
  "20261001_create_users": {
    up: (k) => k.schema.createTable("users", (t) => {
      t.increments("id");
      t.string("email").notNullable().unique();
      t.string("name").notNullable();
      t.timestamp("created_at").notNullable().defaultTo(k.fn.now());
    }),
    down: (k) => k.schema.dropTable("users"),
  },
  "20261008_create_posts": {
    up: (k) => k.schema.createTable("posts", (t) => {
      t.increments("id");
      t.integer("user_id").notNullable().references("id").inTable("users").onDelete("CASCADE");
      t.string("title").notNullable();
      t.boolean("published").notNullable().defaultTo(false);
      t.index(["user_id", "published"]);
    }),
    down: (k) => k.schema.dropTable("posts"),
  },
  "20261012_users_add_role": {
    up: (k) => k.schema.alterTable("users", (t) => { t.string("role").notNullable().defaultTo("member"); }),
    down: (k) => k.schema.alterTable("users", (t) => { t.dropColumn("role"); }),
  },
};

const migrationSource: Knex.MigrationSource<string> = {
  getMigrations: async () => Object.keys(migrations).sort(),
  getMigrationName: (name) => name,
  getMigration: async (name) => migrations[name],
};

export function createDb(filename = ":memory:") {
  return knexFactory({
    client: "better-sqlite3",
    connection: { filename },
    useNullAsDefault: true,
    // SQLite has foreign keys off by default; turn them on for every pooled connection.
    pool: { afterCreate: (conn: { pragma: (s: string) => void }, done: (err: Error | null, conn: unknown) => void) => { conn.pragma("foreign_keys = ON"); done(null, conn); } },
    migrations: { migrationSource },
  });
}

export async function seed(k: Knex) {
  await k.transaction(async (trx) => {
    const [ada] = await trx("users").insert({ email: "[email protected]", name: "Ada", role: "admin" }).returning("id");
    const [bob] = await trx("users").insert({ email: "[email protected]", name: "Bob" }).returning("id");
    await trx("posts").insert([
      { user_id: ada.id, title: "Engines", published: true },
      // multi-row inserts fill missing columns with NULL (useNullAsDefault), so be explicit
      { user_id: ada.id, title: "Draft notes", published: false },
      { user_id: bob.id, title: "Hello", published: true },
    ]);
  });
}

/** Published post counts per author, a typical join plus group by. */
export function publishedCounts(k: Knex) {
  return k("users as u")
    .leftJoin("posts as p", function () { this.on("p.user_id", "u.id").andOn("p.published", k.raw("?", [1])); })
    .select("u.name")
    .count({ published: "p.id" })
    .groupBy("u.id")
    .orderBy("u.name");
}

import { test } from "node:test";
import assert from "node:assert/strict";
import { type Knex } from "knex";
import { createDb as open, seed, publishedCounts } from "../src/db.ts";

// Every test gets its own in-memory database; t.after destroys it even when an assertion fails,
// so a failing test never leaves the pool open and hangs the run.
function createDb(t: { after: (fn: () => Promise<void>) => void }): Knex {
  const k = open();
  t.after(() => k.destroy());
  return k;
}

test("migrate.latest applies every migration in order and records them", async (t) => {
  const k = createDb(t);
  const [batch, applied] = await k.migrate.latest();
  assert.equal(batch, 1);
  assert.deepEqual(applied, ["20261001_create_users", "20261008_create_posts", "20261012_users_add_role"]);
  assert.equal(await k.schema.hasColumn("users", "role"), true);
  assert.equal(await k.migrate.currentVersion(), "20261012");
});

test("rollback and down undo the schema step by step", async (t) => {
  const k = createDb(t);
  await k.migrate.latest();
  await k.migrate.down();
  assert.equal(await k.schema.hasColumn("users", "role"), false);
  await k.migrate.rollback(undefined, true); // all remaining
  assert.equal(await k.schema.hasTable("users"), false);
  const [done, pending] = await k.migrate.list();
  assert.equal(done.length, 0);
  assert.equal(pending.length, 3);
});

test("seeds in a transaction and queries with joins and aggregates", async (t) => {
  const k = createDb(t);
  await k.migrate.latest();
  await seed(k);
  const rows = await publishedCounts(k);
  assert.deepEqual(rows.map((r) => ({ ...r, published: Number(r.published) })), [{ name: "Ada", published: 1 }, { name: "Bob", published: 1 }]);
  const bob = await k("users").where({ email: "[email protected]" }).first();
  assert.equal(bob.role, "member");
});

test("unique and foreign key constraints are enforced", async (t) => {
  const k = createDb(t);
  await k.migrate.latest();
  await seed(k);
  await assert.rejects(k("users").insert({ email: "[email protected]", name: "Ada 2" }), /UNIQUE constraint failed/);
  await assert.rejects(k("posts").insert({ user_id: 999, title: "orphan" }), /FOREIGN KEY constraint failed/);
  await k("users").where({ name: "Ada" }).del();
  const left = await k("posts").count({ n: "*" }).first();
  assert.equal(Number(left?.n), 1); // ON DELETE CASCADE removed Ada's posts
});

test("a failing transaction leaves no rows behind", async (t) => {
  const k = createDb(t);
  await k.migrate.latest();
  await assert.rejects(k.transaction(async (trx) => {
    await trx("users").insert({ email: "[email protected]", name: "X" });
    await trx("users").insert({ email: "[email protected]", name: "dup" });
  }));
  assert.equal(Number((await k("users").count({ n: "*" }).first())?.n), 0);
});