1test("a table is created as drizzle defines it", () => {
2 const kind = sqliteNumericEnum({ file: 0, directory: 1 });
3 const files = sqliteTable("files", {
4 id: integer().primaryKey({ autoIncrement: true }),
5 parent: integer().references((): AnySQLiteColumn => files.id, { onDelete: "cascade" }),
6 path: text().notNull().unique(),
7 kind: kind().notNull().default("file"),
8 note: text().notNull().default("it's"),
9 updated: integer().notNull().default(sql`current_timestamp`),
10 }, (table) => [index("files_path").on(sql`${table.path} collate nocase`, table.kind)]);
11 const statements = schema(files);
12 assert.deepEqual(statements, [
13 "create table \"files\" (\"id\" integer primary key autoincrement not null, \"parent\" integer, "
14 + "\"path\" text not null unique, \"kind\" integer not null default 0, \"note\" text not null default 'it''s', "
15 + "\"updated\" integer not null default (current_timestamp), "
16 + "foreign key (\"parent\") references \"files\" (\"id\") on delete cascade);",
17 "create index \"files_path\" on \"files\" (\"path\" collate nocase, \"kind\");",
18 ]);
19 // and sqlite accepts it
20 const client = new DatabaseSync(":memory:");
21 for (const statement of statements) client.exec(statement);
22});
23
24test("migrating creates a table, records its version, and then leaves it alone", () => {
25 using db = database();
26 const posts = sqliteTable("posts", { id: integer().primaryKey(), title: text().notNull() });
27 let seeded = 0;
28 const migrate = () =>
29 db.open().migrate(posts, 1, {
30 1: (db) => void (seeded++, db.insert(posts).values({ id: 1, title: "first" }).run()),
31 });
32 migrate();
33 migrate();
34 assert.equal(seeded, 1);
35 assert.deepEqual(db.open().select().from(posts).all(), [{ id: 1, title: "first" }]);
36 assert.deepEqual(db.raw("select key, version from clover_migrations"), [{ key: "posts", version: 1 }]);
37});
38
39test("a table made by the old `db.table` is already at version 1", () => {
40 using db = database();
41 db.exec("create table clover_migrations (key text not null primary key, version integer not null);");
42 db.exec(
43 "create table questions (qmid integer not null, type integer not null, text text not null, primary key (qmid));",
44 );
45 db.exec("insert into clover_migrations values ('questions', 1); insert into questions values (5, 3, 'kept');");
46 const type = sqliteNumericEnum({ normal: 0, reject: 1, pending: 3 });
47 const questions = sqliteTable("questions", {
48 qmid: integer().primaryKey(),
49 type: type().notNull(),
50 text: text().notNull(),
51 });
52 db.open().migrate(questions, 1, { 1: () => assert.fail("already at version 1") });
53 assert.deepEqual(db.open().select().from(questions).all(), [{ qmid: 5, type: "pending", text: "kept" }]);
54 assert.deepEqual(db.open().select().from(questions).where(eq(questions.type, "pending")).all().length, 1);
55});
56
57test("a newer version adds columns, runs its step, and then enforces constraints", () => {
58 using db = database();
59 const v1 = sqliteTable("posts", { id: integer().primaryKey(), title: text().notNull() });
60 db.open().migrate(v1, 1);
61 db.open().insert(v1).values([{ id: 1, title: "Hello World" }, { id: 2, title: "Second" }]).run();
62 // something else references the table, and has to keep doing so
63 db.exec("create table comments (post integer not null references posts (id) on delete cascade);");
64 db.exec("insert into comments values (2);");
65
66 const v2 = sqliteTable("posts", {
67 id: integer().primaryKey(),
68 title: text().notNull(),
69 slug: text().notNull().unique(),
70 }, (table) => [index("posts_slug").on(table.slug)]);
71 db.open().migrate(v2, 2, {
72 2: (db) => {
73 for (const post of db.select().from(v2).all()) {
74 db.update(v2).set({ slug: post.title.toLowerCase().replaceAll(" ", "-") }).where(eq(v2.id, post.id)).run();
75 }
76 },
77 });
78 assert.deepEqual(db.open().select().from(v2).all(), [
79 { id: 1, title: "Hello World", slug: "hello-world" },
80 { id: 2, title: "Second", slug: "second" },
81 ]);
82 assert.throws(() => db.open().insert(v2).values({ id: 3, title: "Dup", slug: "second" }).run());
83 assert.deepEqual(db.raw("select name from sqlite_schema where type = 'index' and name = 'posts_slug'").length, 1);
84 db.open().delete(v2).where(eq(v2.id, 2)).run();
85 assert.deepEqual(db.raw("select * from comments"), []);
86});
87
88test("a migration that leaves rows breaking a constraint changes nothing", () => {
89 using db = database();
90 const v1 = sqliteTable("posts", { id: integer().primaryKey(), title: text().notNull() });
91 db.open().migrate(v1, 1);
92 db.open().insert(v1).values({ id: 1, title: "Hello" }).run();
93
94 const v2 = sqliteTable("posts", { id: integer().primaryKey(), title: text().notNull(), slug: text().notNull() });
95 assert.throws(() => db.open().migrate(v2, 2), /NOT NULL constraint failed/);
96 assert.deepEqual(db.raw("select * from posts"), [{ id: 1, title: "Hello" }]);
97 assert.deepEqual(db.raw("select version from clover_migrations"), [{ version: 1 }]);
98});
99
100/** a database file of its own, in a temporary `CLOVER_DB` */
101function database() {
102 const dir = fs.mkdtempSync(path.join(os.tmpdir(), "drizzle-"));
103 process.env.CLOVER_DB = dir;
104 const name = `test-${path.basename(dir)}.sqlite`;
105 const client = new DatabaseSync(path.join(dir, name));
106 return {
107 open: () => getDatabase(name),
108 exec: (statements: string) => client.exec(statements),
109 raw: (query: string) => client.prepare(query).all().map((row) => ({ ...row })),
110 [Symbol.dispose]: () => {
111 client.close();
112 fs.rmSync(dir, { recursive: true, force: true });
113 },
114 };
115}
116
117import { eq, sql } from "drizzle-orm";
118import { type AnySQLiteColumn, index, integer, sqliteTable, text } from "drizzle-orm/sqlite-core";
119import assert from "node:assert/strict";
120import * as fs from "node:fs";
121import * as os from "node:os";
122import * as path from "node:path";
123import { DatabaseSync } from "node:sqlite";
124import { test } from "vitest";
125import { getDatabase, schema, sqliteNumericEnum } from "./drizzle.ts";