1// A D1Database for tests: Node's built-in SQLite (node:sqlite) in memory,
2// with every migration in worker/migrations/ applied in order, as
3// `wrangler d1 migrations apply` does. So a test runs the Worker's real SQL
4// against the real schema, without workerd.
5//
6// It implements the part of D1's API the Worker uses: prepare().bind() with
7// run(), all(), first() and raw(), plus batch() and exec(). D1 is SQLite, so
8// the SQL behaves the same; what differs (D1's size limits, its `meta`
9// beyond changes and last_row_id) is not modelled. Add a method here when
10// the Worker starts using one.
11import { readdirSync, readFileSync } from 'node:fs';
12import { DatabaseSync, type SQLInputValue } from 'node:sqlite';
13
14export const MIGRATIONS_DIR = new URL('../migrations/', import.meta.url);
15
16// The migration files in the order wrangler applies them (by name).
17export const migrations = () =>
18  readdirSync(MIGRATIONS_DIR)
19    .filter((f) => f.endsWith('.sql'))
20    .sort();
21
22type Row = Record<string, unknown>;
23
24class Statement {
25  constructor(
26    private db: DatabaseSync,
27    private sql: string,
28    private params: SQLInputValue[] = [],
29  ) {}
30  bind(...params: unknown[]) {
31    return new Statement(this.db, this.sql, params as SQLInputValue[]);
32  }
33  private stmt() {
34    return this.db.prepare(this.sql);
35  }
36  async run() {
37    const { changes, lastInsertRowid } = this.stmt().run(...this.params);
38    return { success: true, results: [], meta: { changes: Number(changes), last_row_id: Number(lastInsertRowid) } };
39  }
40  async all<T = Row>() {
41    const results = this.stmt().all(...this.params) as T[];
42    return { success: true, results, meta: {} };
43  }
44  async first<T = Row>(column?: string): Promise<T | null> {
45    const row = this.stmt().get(...this.params) as Row | undefined;
46    if (!row) return null;
47    return (column ? row[column] : row) as T;
48  }
49  async raw<T = unknown[]>() {
50    const stmt = this.stmt();
51    stmt.setReturnArrays(true);
52    return stmt.all(...this.params) as T[];
53  }
54}
55
56export function testD1(): D1Database {
57  const db = new DatabaseSync(':memory:');
58  for (const file of migrations()) db.exec(readFileSync(new URL(file, MIGRATIONS_DIR), 'utf8'));
59  const d1 = {
60    prepare: (sql: string) => new Statement(db, sql),
61    // D1 runs a batch as one transaction: all or nothing.
62    async batch(statements: Statement[]) {
63      db.exec('BEGIN');
64      try {
65        const results = [];
66        for (const s of statements) results.push(await s.all());
67        db.exec('COMMIT');
68        return results;
69      } catch (e) {
70        db.exec('ROLLBACK');
71        throw e;
72      }
73    },
74    async exec(sql: string) {
75      db.exec(sql);
76      return { count: 0, duration: 0 };
77    },
78  };
79  return d1 as unknown as D1Database;
80}