wehub-resource-sync
70bf21e064
Deploy (to testing) and Test Playground Preview Worker / Deploy Playground Preview Worker (testing) (push) Has been skipped
Deploy Workers Shared Staging / Deploy Workers Shared Staging (push) Failing after 0s
Prerelease / build (push) Has been skipped
Handle Changesets / Handle Changesets (push) Has been cancelled
Semgrep OSS scan / semgrep-oss (push) Has been cancelled
738 lines
23 KiB
TypeScript
738 lines
23 KiB
TypeScript
import assert from "node:assert";
|
|
import fs from "node:fs/promises";
|
|
import { type D1Database } from "@cloudflare/workers-types/experimental";
|
|
import { Miniflare } from "miniflare";
|
|
import { beforeEach, type ExpectStatic, onTestFinished, test } from "vitest";
|
|
import { useDispose, useTmp, utf8Encode } from "../../test-shared";
|
|
import { binding, ctx, getDatabase, opts } from "./test";
|
|
import type { MiniflareOptions } from "miniflare";
|
|
|
|
export const SCHEMA = (
|
|
tableColours: string,
|
|
tableKitchenSink: string,
|
|
tablePalettes: string
|
|
) => `
|
|
CREATE TABLE ${tableColours} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, rgb INTEGER NOT NULL);
|
|
CREATE TABLE ${tableKitchenSink} (id INTEGER PRIMARY KEY, int INTEGER, real REAL, text TEXT, blob BLOB);
|
|
CREATE TABLE ${tablePalettes} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, colour_id INTEGER NOT NULL, FOREIGN KEY (colour_id) REFERENCES ${tableColours}(id));
|
|
INSERT INTO ${tableColours} (id, name, rgb) VALUES (1, 'red', 0xff0000);
|
|
INSERT INTO ${tableColours} (id, name, rgb) VALUES (2, 'green', 0x00ff00);
|
|
INSERT INTO ${tableColours} (id, name, rgb) VALUES (3, 'blue', 0x0000ff);
|
|
INSERT INTO ${tablePalettes} (id, name, colour_id) VALUES (1, 'Night', 3);
|
|
`;
|
|
|
|
export interface ColourRow {
|
|
id: number;
|
|
name: string;
|
|
rgb: number;
|
|
}
|
|
|
|
export interface KitchenSinkRow {
|
|
id: number;
|
|
int: number | null;
|
|
real: number | null;
|
|
text: string | null;
|
|
blob: number[] | null;
|
|
}
|
|
|
|
beforeEach(async () => {
|
|
const ns = `${Date.now()}_${Math.floor(
|
|
Math.random() * Number.MAX_SAFE_INTEGER
|
|
)}`;
|
|
const tableColours = `colours_${ns}`;
|
|
const tableKitchenSink = `kitchen_sink_${ns}`;
|
|
const tablePalettes = `palettes_${ns}`;
|
|
|
|
const db = await getDatabase(ctx.mf);
|
|
const bindings = await ctx.mf.getBindings();
|
|
|
|
await db.exec(SCHEMA(tableColours, tableKitchenSink, tablePalettes));
|
|
|
|
ctx.bindings = bindings;
|
|
ctx.db = db;
|
|
ctx.tableColours = tableColours;
|
|
ctx.tableKitchenSink = tableKitchenSink;
|
|
ctx.tablePalettes = tablePalettes;
|
|
});
|
|
|
|
function throwCause<T>(promise: Promise<T>): Promise<T> {
|
|
return promise.catch((error) => {
|
|
assert.strictEqual(error.message, "D1_ERROR");
|
|
assert.notStrictEqual(error.cause, undefined);
|
|
throw error.cause;
|
|
});
|
|
}
|
|
|
|
test("D1Database: batch", async ({ expect }) => {
|
|
const { db, tableColours } = ctx;
|
|
|
|
const insert = db.prepare(
|
|
`INSERT INTO ${tableColours} (id, name, rgb) VALUES (?, ?, ?)`
|
|
);
|
|
const batchResults = await db.batch<Pick<ColourRow, "name">>([
|
|
insert.bind(4, "yellow", 0xffff00),
|
|
db.prepare(`SELECT name FROM ${tableColours}`),
|
|
]);
|
|
expect(batchResults.length).toBe(2);
|
|
expect(batchResults[0].success).toBe(true);
|
|
expect(batchResults[0].results).toEqual([]);
|
|
expect(batchResults[1].success).toBe(true);
|
|
const expectedResults = [
|
|
{ name: "red" },
|
|
{ name: "green" },
|
|
{ name: "blue" },
|
|
{ name: "yellow" },
|
|
];
|
|
expect(batchResults[1].results).toEqual(expectedResults);
|
|
|
|
// Check error mid-batch rolls-back entire batch
|
|
const badInsert = db.prepare(
|
|
`PUT IN ${tableColours} (id, name, rgb) VALUES (?, ?, ?)`
|
|
);
|
|
await expect(
|
|
throwCause(
|
|
db.batch([
|
|
insert.bind(5, "purple", 0xff00ff),
|
|
badInsert.bind(6, "blurple", 0x5865f2),
|
|
insert.bind(7, "cyan", 0x00ffff),
|
|
])
|
|
)
|
|
).rejects.toThrow(/syntax error/);
|
|
const result = await db
|
|
.prepare(`SELECT name FROM ${tableColours}`)
|
|
.all<Pick<ColourRow, "name">>();
|
|
expect(result.results).toEqual(expectedResults);
|
|
});
|
|
test("D1Database: exec", async ({ expect }) => {
|
|
const { db, tableColours } = ctx;
|
|
|
|
// Check with single statement
|
|
// eslint-disable-next-line workers-sdk/no-unsafe-command-execution -- D1 database exec, not child_process
|
|
let execResult = await db.exec(
|
|
`UPDATE ${tableColours} SET name = 'Red' WHERE name = 'red'`
|
|
);
|
|
expect(execResult.count).toBe(1);
|
|
expect(execResult.duration >= 0).toBe(true);
|
|
let result = await db
|
|
.prepare(`SELECT name FROM ${tableColours} WHERE name = 'Red'`)
|
|
.all<Pick<ColourRow, "name">>();
|
|
expect(result.results).toEqual([{ name: "Red" }]);
|
|
|
|
// Check with multiple statements
|
|
const statements = [
|
|
`UPDATE ${tableColours} SET name = 'Green' WHERE name = 'green'`,
|
|
`UPDATE ${tableColours} SET name = 'Blue' WHERE name = 'blue'`,
|
|
].join("\n");
|
|
execResult = await db.exec(statements);
|
|
expect(execResult.count).toBe(2);
|
|
expect(execResult.duration >= 0).toBe(true);
|
|
result = await db.prepare(`SELECT name FROM ${tableColours}`).all();
|
|
expect(result.results).toEqual([
|
|
{ name: "Red" },
|
|
{ name: "Green" },
|
|
{ name: "Blue" },
|
|
]);
|
|
});
|
|
|
|
test("D1PreparedStatement: bind", async ({ expect }) => {
|
|
const { db, tableColours, tableKitchenSink } = ctx;
|
|
|
|
// Check with all parameter types
|
|
const blob = utf8Encode("Walshy");
|
|
const blobArray = Array.from(blob);
|
|
await db
|
|
.prepare(
|
|
`INSERT INTO ${tableKitchenSink} (id, int, real, text, blob) VALUES (?, ?, ?, ?, ?)`
|
|
)
|
|
// Preserve `Uint8Array` type through JSON serialisation
|
|
.bind(1, 42, 3.141, "🙈", blobArray)
|
|
.run();
|
|
let result = await db
|
|
.prepare(`SELECT * FROM ${tableKitchenSink}`)
|
|
.all<KitchenSinkRow>();
|
|
expect(result.results).toEqual([
|
|
{ id: 1, int: 42, real: 3.141, text: "🙈", blob: blobArray },
|
|
]);
|
|
|
|
// Check with null values
|
|
await db.prepare(`UPDATE ${tableKitchenSink} SET blob = ?`).bind(null).run();
|
|
result = await db.prepare(`SELECT * FROM ${tableKitchenSink}`).all();
|
|
expect(result.results).toEqual([
|
|
{ id: 1, int: 42, real: 3.141, text: "🙈", blob: null },
|
|
]);
|
|
|
|
// Check with multiple statements
|
|
const colourResultsPromise = db
|
|
.prepare(
|
|
`SELECT * FROM ${tableColours} WHERE name = ?; SELECT * FROM ${tableColours} WHERE id = ?;`
|
|
)
|
|
.bind("green")
|
|
.all<ColourRow>();
|
|
|
|
// workerd changed the error message here. Miniflare's tests should pass with either version of workerd
|
|
await expect(colourResultsPromise).rejects.toThrow(
|
|
/A prepared SQL statement must contain only one statement|When executing multiple SQL statements in a single call, only the last statement can have parameters./
|
|
);
|
|
|
|
// Check with numbered parameters (execute and query)
|
|
// https://github.com/cloudflare/miniflare/issues/504
|
|
await db
|
|
.prepare(`INSERT INTO ${tableColours} (id, name, rgb) VALUES (?3, ?1, ?2)`)
|
|
.bind("yellow", 0xffff00, 4)
|
|
.run();
|
|
const colourResult = await db
|
|
.prepare(`SELECT * FROM ${tableColours} WHERE id = ?1`)
|
|
.bind(4)
|
|
.first<ColourRow>();
|
|
expect(colourResult).toEqual({ id: 4, name: "yellow", rgb: 0xffff00 });
|
|
});
|
|
|
|
// Lots of strange edge cases here...
|
|
|
|
test("D1PreparedStatement: first", async ({ expect }) => {
|
|
const { db, tableColours } = ctx;
|
|
|
|
// Check with read statement
|
|
const select = await db.prepare(`SELECT * FROM ${tableColours}`);
|
|
let result: ColourRow | null = await select.first<ColourRow>();
|
|
expect(result).toEqual({ id: 1, name: "red", rgb: 0xff0000 });
|
|
let id: number | null = await select.first<number>("id");
|
|
expect(id).toBe(1);
|
|
|
|
// Check with multiple statements
|
|
const resultPromise = db
|
|
.prepare(
|
|
`SELECT * FROM ${tableColours} WHERE name = 'none'; SELECT * FROM ${tableColours} WHERE id = 1;`
|
|
)
|
|
.first();
|
|
|
|
// workerd changed its behaviour from throwing to returning the last result. Miniflare's tests should pass with either version of workerd
|
|
try {
|
|
const d1Result = await resultPromise;
|
|
expect(d1Result).toEqual({
|
|
id: 1,
|
|
name: "red",
|
|
rgb: 16711680,
|
|
});
|
|
} catch (e) {
|
|
expect(e).toBeInstanceOf(Error);
|
|
expect(
|
|
/A prepared SQL statement must contain only one statement/.test(
|
|
(e as Error).message
|
|
)
|
|
).toBeTruthy();
|
|
}
|
|
|
|
// Check with write statement (should actually execute statement)
|
|
result = await db
|
|
.prepare(`INSERT INTO ${tableColours} (id, name, rgb) VALUES (?, ?, ?)`)
|
|
.bind(4, "yellow", 0xffff00)
|
|
.first();
|
|
expect(result).toBe(null);
|
|
id = await db
|
|
.prepare(`SELECT id FROM ${tableColours} WHERE name = ?`)
|
|
.bind("yellow")
|
|
.first("id");
|
|
expect(id).toBe(4);
|
|
});
|
|
test("D1PreparedStatement: run", async ({ expect }) => {
|
|
const { db, tableColours, tableKitchenSink } = ctx;
|
|
|
|
// Check with read statement
|
|
let result = await db.prepare(`SELECT * FROM ${tableColours}`).run();
|
|
expect(result.meta.duration >= 0).toBe(true);
|
|
expect(result).toEqual({
|
|
success: true,
|
|
results: [
|
|
{ id: 1, name: "red", rgb: 16711680 },
|
|
{ id: 2, name: "green", rgb: 65280 },
|
|
{ id: 3, name: "blue", rgb: 255 },
|
|
],
|
|
meta: {
|
|
changed_db: false,
|
|
changes: 0,
|
|
// Don't know duration, so just match on returned value asserted > 0
|
|
duration: result.meta.duration,
|
|
// Not an `INSERT`, so `last_row_id` non-deterministic
|
|
last_row_id: result.meta.last_row_id,
|
|
served_by: "miniflare.db",
|
|
size_after: result.meta.size_after,
|
|
rows_read: 3,
|
|
rows_written: 0,
|
|
},
|
|
});
|
|
|
|
// Check with read/write statement
|
|
result = await db
|
|
.prepare(
|
|
`INSERT INTO ${tableColours} (id, name, rgb) VALUES (?, ?, ?) RETURNING *`
|
|
)
|
|
.bind(4, "yellow", 0xffff00)
|
|
.run();
|
|
expect(result.meta.duration >= 0).toBe(true);
|
|
expect(result).toEqual({
|
|
results: [{ id: 4, name: "yellow", rgb: 16776960 }],
|
|
success: true,
|
|
meta: {
|
|
changed_db: true,
|
|
changes: 1,
|
|
// Don't know duration, so just match on returned value asserted > 0
|
|
duration: result.meta.duration,
|
|
last_row_id: 4,
|
|
served_by: "miniflare.db",
|
|
size_after: result.meta.size_after,
|
|
rows_read: 2,
|
|
rows_written: 1,
|
|
},
|
|
});
|
|
|
|
// Check with multiple statements
|
|
const resultPromise = db
|
|
.prepare(
|
|
`INSERT INTO ${tableKitchenSink} (id) VALUES (1); INSERT INTO ${tableKitchenSink} (id) VALUES (2);`
|
|
)
|
|
.run();
|
|
|
|
// workerd changed its behaviour from throwing to returning the last result. Miniflare's tests should pass with either version of workerd
|
|
try {
|
|
result = await resultPromise;
|
|
expect(result).toEqual({
|
|
meta: {
|
|
changed_db: true,
|
|
changes: 2,
|
|
// Don't know duration, so just match on returned value asserted > 0
|
|
duration: result.meta.duration,
|
|
last_row_id: result.meta.last_row_id,
|
|
rows_read: 1,
|
|
rows_written: 1,
|
|
served_by: "miniflare.db",
|
|
size_after: result.meta.size_after,
|
|
},
|
|
results: [],
|
|
success: true,
|
|
});
|
|
} catch (e) {
|
|
expect(e).toBeInstanceOf(Error);
|
|
expect(
|
|
/A prepared SQL statement must contain only one statement/.test(
|
|
(e as Error).message
|
|
)
|
|
).toBeTruthy();
|
|
}
|
|
|
|
// Check with write statement
|
|
result = await db
|
|
.prepare(`INSERT INTO ${tableColours} (id, name, rgb) VALUES (?, ?, ?)`)
|
|
.bind(5, "orange", 0xff8000)
|
|
.run();
|
|
expect(result.meta.duration >= 0).toBe(true);
|
|
expect(result).toEqual({
|
|
results: [],
|
|
success: true,
|
|
meta: {
|
|
changed_db: true,
|
|
changes: 1,
|
|
// Don't know duration, so just match on returned value asserted > 0
|
|
duration: result.meta.duration,
|
|
last_row_id: 5,
|
|
served_by: "miniflare.db",
|
|
size_after: result.meta.size_after,
|
|
rows_read: 1,
|
|
rows_written: 1,
|
|
},
|
|
});
|
|
});
|
|
test("D1PreparedStatement: all", async ({ expect }) => {
|
|
const { db, tableColours } = ctx;
|
|
|
|
// Check with read statement
|
|
let result = await db
|
|
.prepare(`SELECT * FROM ${tableColours}`)
|
|
.all<ColourRow>();
|
|
expect(result.meta.duration >= 0).toBe(true);
|
|
expect(result).toEqual({
|
|
results: [
|
|
{ id: 1, name: "red", rgb: 0xff0000 },
|
|
{ id: 2, name: "green", rgb: 0x00ff00 },
|
|
{ id: 3, name: "blue", rgb: 0x0000ff },
|
|
],
|
|
success: true,
|
|
meta: {
|
|
changed_db: false,
|
|
changes: 0,
|
|
// Don't know duration, so just match on returned value asserted > 0
|
|
duration: result.meta.duration,
|
|
// Not an `INSERT`, so `last_row_id` non-deterministic
|
|
last_row_id: result.meta.last_row_id,
|
|
served_by: "miniflare.db",
|
|
size_after: result.meta.size_after,
|
|
rows_read: 3,
|
|
rows_written: 0,
|
|
},
|
|
});
|
|
|
|
// Check with multiple statements
|
|
const resultPromise = db
|
|
.prepare(
|
|
`SELECT * FROM ${tableColours} WHERE id = 1; SELECT * FROM ${tableColours} WHERE id = 3;`
|
|
)
|
|
.all<ColourRow>();
|
|
|
|
// workerd changed its behaviour from throwing to returning the last result. Miniflare's tests should pass with either version of workerd
|
|
try {
|
|
result = await resultPromise;
|
|
expect(result).toEqual({
|
|
meta: {
|
|
changed_db: false,
|
|
changes: 0,
|
|
// Don't know duration, so just match on returned value asserted > 0
|
|
duration: result.meta.duration,
|
|
last_row_id: result.meta.last_row_id,
|
|
rows_read: 1,
|
|
rows_written: 0,
|
|
served_by: "miniflare.db",
|
|
size_after: result.meta.size_after,
|
|
},
|
|
results: [
|
|
{
|
|
id: 3,
|
|
name: "blue",
|
|
rgb: 255,
|
|
},
|
|
],
|
|
success: true,
|
|
});
|
|
} catch (e) {
|
|
expect(e).toBeInstanceOf(Error);
|
|
expect(
|
|
/A prepared SQL statement must contain only one statement/.test(
|
|
(e as Error).message
|
|
)
|
|
).toBeTruthy();
|
|
}
|
|
|
|
// Check with write statement (should actually execute, but return nothing)
|
|
result = await db
|
|
.prepare(`INSERT INTO ${tableColours} (id, name, rgb) VALUES (?, ?, ?)`)
|
|
.bind(4, "yellow", 0xffff00)
|
|
.all();
|
|
expect(result.results).toEqual([]);
|
|
expect(result.meta.last_row_id).toBe(4);
|
|
expect(result.meta.changes).toBe(1);
|
|
const id = await db
|
|
.prepare(`SELECT id FROM ${tableColours} WHERE name = ?`)
|
|
.bind("yellow")
|
|
.first("id");
|
|
expect(id).toBe(4);
|
|
|
|
// Check with write statement that returns data
|
|
result = await db
|
|
.prepare(
|
|
`INSERT INTO ${tableColours} (id, name, rgb) VALUES (?, ?, ?) RETURNING id`
|
|
)
|
|
.bind(5, "orange", 0xff8000)
|
|
.all();
|
|
expect(result.results).toEqual([{ id: 5 }]);
|
|
expect(result.meta.last_row_id).toBe(5);
|
|
expect(result.meta.changes).toBe(1);
|
|
});
|
|
test("D1PreparedStatement: raw", async ({ expect }) => {
|
|
const { db, tableColours } = ctx;
|
|
|
|
// Check with read statement
|
|
type RawColourRow = [/* id */ number, /* name */ string, /* rgb*/ number];
|
|
let results = await db
|
|
.prepare(`SELECT * FROM ${tableColours}`)
|
|
.raw<RawColourRow>();
|
|
expect(results).toEqual([
|
|
[1, "red", 0xff0000],
|
|
[2, "green", 0x00ff00],
|
|
[3, "blue", 0x0000ff],
|
|
]);
|
|
|
|
// Check with multiple statements (should only return first statement results)
|
|
const resultPromise = db
|
|
.prepare(
|
|
`SELECT * FROM ${tableColours} WHERE id = 1; SELECT * FROM ${tableColours} WHERE id = 3;`
|
|
)
|
|
.raw<RawColourRow>();
|
|
|
|
// workerd changed its behaviour from throwing to returning the last result. Miniflare's tests should pass with either version of workerd
|
|
try {
|
|
const result = await resultPromise;
|
|
expect(result).toEqual([[3, "blue", 0x0000ff]]);
|
|
} catch (e) {
|
|
expect(e).toBeInstanceOf(Error);
|
|
expect(
|
|
/A prepared SQL statement must contain only one statement/.test(
|
|
(e as Error).message
|
|
)
|
|
).toBeTruthy();
|
|
}
|
|
|
|
// Check with write statement (should actually execute, but return nothing)
|
|
results = await db
|
|
.prepare(`INSERT INTO ${tableColours} (id, name, rgb) VALUES (?, ?, ?)`)
|
|
.bind(4, "yellow", 0xffff00)
|
|
.raw();
|
|
expect(results).toEqual([]);
|
|
const id = await db
|
|
.prepare(`SELECT id FROM ${tableColours} WHERE name = ?`)
|
|
.bind("yellow")
|
|
.first("id");
|
|
expect(id).toBe(4);
|
|
|
|
// Check whether workerd raw test case passes here too
|
|
// Note that this test did not pass with the old binding
|
|
if (!ctx.bindings["__D1_BETA__DB"]) {
|
|
await db.prepare(`CREATE TABLE abc (a INT, b INT, c INT);`).run();
|
|
await db.prepare(`CREATE TABLE cde (c INT, d INT, e INT);`).run();
|
|
await db.prepare(`INSERT INTO abc VALUES (1,2,3),(4,5,6);`).run();
|
|
await db.prepare(`INSERT INTO cde VALUES (7,8,9),(1,2,3);`).run();
|
|
const rawPromise = await db
|
|
.prepare(`SELECT * FROM abc, cde;`)
|
|
.raw({ columnNames: true });
|
|
expect(rawPromise).toEqual([
|
|
["a", "b", "c", "c", "d", "e"],
|
|
[1, 2, 3, 7, 8, 9],
|
|
[1, 2, 3, 1, 2, 3],
|
|
[4, 5, 6, 7, 8, 9],
|
|
[4, 5, 6, 1, 2, 3],
|
|
]);
|
|
}
|
|
});
|
|
|
|
test("operations persist D1 data", async ({ expect }) => {
|
|
const { tableColours, tableKitchenSink, tablePalettes } = ctx;
|
|
|
|
// Create new temporary file-system persistence directory
|
|
const tmp = await useTmp();
|
|
const persistOpts: MiniflareOptions = { ...opts, d1Persist: tmp };
|
|
const mf = new Miniflare(persistOpts);
|
|
useDispose(mf);
|
|
let db = await getDatabase(mf);
|
|
|
|
// Check execute respects persist
|
|
await db.exec(SCHEMA(tableColours, tableKitchenSink, tablePalettes));
|
|
await db
|
|
.prepare(
|
|
`INSERT INTO ${tableColours} (id, name, rgb) VALUES (4, 'purple', 0xff00ff);`
|
|
)
|
|
.run();
|
|
let result = await db
|
|
.prepare(`SELECT name FROM ${tableColours} WHERE id = 4`)
|
|
.first();
|
|
expect(result).toEqual({ name: "purple" });
|
|
|
|
// Check directory created for database
|
|
const names = await fs.readdir(tmp);
|
|
expect(names.includes("miniflare-D1DatabaseObject")).toBe(true);
|
|
|
|
// Check "restarting" keeps persisted data
|
|
await mf.dispose();
|
|
const mf2 = new Miniflare(persistOpts);
|
|
useDispose(mf2);
|
|
db = await getDatabase(mf2);
|
|
result = await db
|
|
.prepare(`SELECT name FROM ${tableColours} WHERE id = 4`)
|
|
.first();
|
|
expect(result).toEqual({ name: "purple" });
|
|
});
|
|
|
|
test("operations permit strange database names", async ({ expect }) => {
|
|
const { tableColours, tableKitchenSink, tablePalettes } = ctx;
|
|
|
|
// Set option, then reset after test
|
|
const id = "my/ Database";
|
|
await ctx.setOptions({ ...opts, d1Databases: { [binding]: id } });
|
|
onTestFinished(() => ctx.setOptions(opts));
|
|
const db = await getDatabase(ctx.mf);
|
|
|
|
// Check basic operations work
|
|
|
|
await db.exec(SCHEMA(tableColours, tableKitchenSink, tablePalettes));
|
|
|
|
await db
|
|
.prepare(
|
|
`INSERT INTO ${tableColours} (id, name, rgb) VALUES (4, 'pink', 0xff00ff);`
|
|
)
|
|
.run();
|
|
const result = await db
|
|
.prepare(`SELECT name FROM ${tableColours} WHERE id = 4`)
|
|
.first<Pick<ColourRow, "name">>();
|
|
expect(result).toEqual({ name: "pink" });
|
|
});
|
|
|
|
test("it properly handles ROWS_AND_COLUMNS results format", async ({
|
|
expect,
|
|
}) => {
|
|
const { tableColours, tablePalettes } = ctx;
|
|
const db = await getDatabase(ctx.mf);
|
|
|
|
const results = await db
|
|
.prepare(
|
|
`SELECT ${tableColours}.name, ${tablePalettes}.name FROM ${tableColours} JOIN ${tablePalettes} ON ${tableColours}.id = ${tablePalettes}.colour_id`
|
|
)
|
|
.raw();
|
|
|
|
let expectedResults;
|
|
// Note that this test did not pass with the old binding
|
|
if (!ctx.bindings["__D1_BETA__DB"]) {
|
|
expectedResults = [["blue", "Night"]];
|
|
} else {
|
|
expectedResults = [["Night"]];
|
|
}
|
|
expect(results).toEqual(expectedResults);
|
|
});
|
|
|
|
/**
|
|
* Test that the `dumpSql` method returns a valid SQL dump of the database.
|
|
* This test creates a new D1 database, fills it with dummy data, and then
|
|
* exports the SQL dump using the `PRAGMA miniflare_d1_export` command.
|
|
* It then executes the dump in a new D1 database and checks if both databases
|
|
* are equal in terms of schema and data.
|
|
*/
|
|
test("dumpSql exports and imports complete database structure and content correctly", async ({
|
|
expect,
|
|
}) => {
|
|
// Create a new Miniflare instance with D1 database
|
|
const tmp1 = await useTmp();
|
|
const originalMF = new Miniflare({
|
|
...opts,
|
|
d1Persist: tmp1,
|
|
d1Databases: { test: "test" },
|
|
});
|
|
useDispose(originalMF);
|
|
const originalDb = await originalMF.getD1Database("test");
|
|
|
|
// Fill the original database with dummy data
|
|
await fillDummyData(originalDb);
|
|
|
|
// Export the database schema and data
|
|
const result = await originalDb
|
|
.prepare("PRAGMA miniflare_d1_export(?,?,?);")
|
|
.bind(0, 0)
|
|
.raw();
|
|
const [dumpStatements] = result as [string[]];
|
|
const dump = dumpStatements.join("\n");
|
|
|
|
// Create a new Miniflare instance and import the dump into a new database
|
|
const tmp2 = await useTmp();
|
|
const mirrorMF = new Miniflare({
|
|
...opts,
|
|
d1Persist: tmp2,
|
|
d1Databases: { test: "test" },
|
|
});
|
|
useDispose(mirrorMF);
|
|
|
|
const mirrorDb = await mirrorMF.getD1Database("test");
|
|
await mirrorDb.exec(dump);
|
|
|
|
// Verify that the schema and data in both databases are equal
|
|
await isDatabaseEqual(expect, originalDb, mirrorDb);
|
|
});
|
|
|
|
/**
|
|
* Populates a D1 database with test data for schema export testing.
|
|
* Creates tables with various schema features (foreign keys, special characters, etc.)
|
|
* and inserts sample data including edge cases like NULL values and type mismatches.
|
|
*/
|
|
async function fillDummyData(db: D1Database) {
|
|
// Create schema with various SQL features to test export compatibility
|
|
// Each table must have an ID column as primary key so that we can use it for ordering in equality tests
|
|
|
|
const schemas = [
|
|
// Create basic table with text primary key
|
|
`CREATE TABLE "classrooms"(id TEXT PRIMARY KEY, capacity INTEGER, test_blob BLOB)`,
|
|
|
|
// Create table with foreign key constraint
|
|
`CREATE TABLE "students" (id INTEGER PRIMARY KEY, name TEXT NOT NULL, classroom TEXT NOT NULL, FOREIGN KEY (classroom) REFERENCES "classrooms" (id) ON DELETE CASCADE)`,
|
|
|
|
// Create table with spaces in name to test quoting
|
|
`CREATE TABLE "test space table" (id INTEGER PRIMARY KEY, name TEXT NOT NULL)`,
|
|
|
|
// Create table with escaped quotes and SQL reserved keywords
|
|
`CREATE TABLE "test""name" (id INTEGER PRIMARY KEY, "escaped""column" TEXT, "order" INTEGER)`,
|
|
];
|
|
|
|
await db.exec(schemas.join(";"));
|
|
|
|
// Prepare sample data
|
|
const classroomData = [
|
|
// Standard numeric data
|
|
...Array.from({ length: 10 }, (_, i) => ({
|
|
id: `classroom_${i + 1}`,
|
|
capacity: (i + 1) * 10,
|
|
test_blob: utf8Encode(`Blob data for classroom ${i + 1}`),
|
|
})),
|
|
|
|
// Edge case: type mismatch (string where number expected)
|
|
{ id: "different_type_classroom", capacity: "not_a_number" },
|
|
|
|
// Edge case: NULL value
|
|
{ id: "null_classroom", capacity: null },
|
|
];
|
|
|
|
// Insert classroom data
|
|
const classroomStmt = db.prepare(
|
|
`INSERT INTO classrooms (id, capacity) VALUES (?, ?)`
|
|
);
|
|
|
|
for (const classroom of classroomData) {
|
|
await classroomStmt.bind(classroom.id, classroom.capacity).run();
|
|
}
|
|
|
|
// Generate and insert student data with classroom references
|
|
const studentStmt = db.prepare(
|
|
`INSERT INTO students (id, name, classroom) VALUES (?, ?, ?)`
|
|
);
|
|
|
|
// Create 2 students for each classroom
|
|
for (let i = 0; i < 10; i++) {
|
|
for (let j = 1; j <= 2; j++) {
|
|
const studentId = i * 2 + j;
|
|
await studentStmt
|
|
.bind(studentId, `student_${studentId}`, `classroom_${i + 1}`)
|
|
.run();
|
|
}
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Compares two D1 databases to check if they are equal in terms of schema and data.
|
|
* It retrieves the schema of both databases, compares the tables, and then
|
|
* checks if the data in each table is identical.
|
|
*/
|
|
async function isDatabaseEqual(
|
|
expect: ExpectStatic,
|
|
db: D1Database,
|
|
db2: D1Database
|
|
) {
|
|
// SQL to select schema excluding internal tables
|
|
const selectSchemaSQL =
|
|
"SELECT * FROM sqlite_master WHERE type = 'table' AND (name NOT LIKE 'sqlite_%' AND name NOT LIKE '_cf_%')";
|
|
|
|
// Check if schema (tables) in both databases is equal
|
|
const tablesFromMirror = (await db2.prepare(selectSchemaSQL).all()).results;
|
|
const tablesFromOriginal = (await db.prepare(selectSchemaSQL).all()).results;
|
|
expect(tablesFromMirror).toEqual(tablesFromOriginal);
|
|
|
|
// Check if data in each table is equal
|
|
// We will use a simple SELECT * FROM table ORDER BY id to ensure consistent ordering
|
|
for (const table of tablesFromMirror) {
|
|
const tableName = table.name as string;
|
|
|
|
// Escape and ORDER BY to ensure consistent ordering
|
|
const selectTableSQL = `SELECT * FROM "${tableName.replace(
|
|
/"/g,
|
|
'""'
|
|
)}" ORDER BY id ASC`;
|
|
|
|
const originalData = (await db.prepare(selectTableSQL).all()).results;
|
|
const mirrorData = (await db2.prepare(selectTableSQL).all()).results;
|
|
|
|
// Data mismatch in table: ${tableName}
|
|
expect(originalData).toEqual(mirrorData);
|
|
}
|
|
}
|