4 files changed, 52 insertions, 18 deletions
diff --git a/src/lib/server/db/groceries.test.ts b/src/lib/server/db/groceries.test.ts
index bd14040..2f8baed 100644
--- a/src/lib/server/db/groceries.test.ts
+++ b/src/lib/server/db/groceries.test.ts
@@ -1,27 +1,14 @@
 import { describe, it, expect, beforeEach } from 'vitest';
-import { drizzle } from 'drizzle-orm/libsql';
-import { createClient } from '@libsql/client';
 import { groceries, categories } from '$lib/server/db/schema';
 import { eq } from 'drizzle-orm';
+import { createTestDb } from './test-helper';
 
 describe('groceries add', () => {
-	const client = createClient({ url: ':memory:' });
-	const db = drizzle(client, { schema: { groceries, categories } });
+	let db: Awaited<ReturnType<typeof createTestDb>>['db'];
 
 	beforeEach(async () => {
-		await db.run(`DROP TABLE IF EXISTS groceries`);
-		await db.run(`DROP TABLE IF EXISTS categories`);
-		await db.run(`CREATE TABLE categories (
-			id INTEGER PRIMARY KEY AUTOINCREMENT,
-			name TEXT NOT NULL
-		)`);
-		await db.run(`CREATE TABLE groceries (
-			id INTEGER PRIMARY KEY AUTOINCREMENT,
-			name TEXT NOT NULL,
-			checked INTEGER NOT NULL DEFAULT 0,
-			created_at TEXT NOT NULL,
-			category_id INTEGER REFERENCES categories(id)
-		)`);
+		const testDb = await createTestDb();
+		db = testDb.db;
 	});
 
 	async function addItem(name: string, categoryId?: number | null) {
diff --git a/src/lib/server/db/ondelete.test.ts b/src/lib/server/db/ondelete.test.ts
new file mode 100644
index 0000000..c2c86de
--- /dev/null
+++ b/src/lib/server/db/ondelete.test.ts
@@ -0,0 +1,32 @@
+import { describe, it, expect, beforeEach } from 'vitest';
+import { groceries, categories } from '$lib/server/db/schema';
+import { eq } from 'drizzle-orm';
+import { createTestDb } from './test-helper';
+
+describe('category delete behavior', () => {
+	let db: Awaited<ReturnType<typeof createTestDb>>['db'];
+
+	beforeEach(async () => {
+		const testDb = await createTestDb();
+		db = testDb.db;
+	});
+
+	it('sets category_id to null when a referenced category is deleted', async () => {
+		await db.insert(categories).values({ name: 'Boissons' });
+		const cats = await db.select().from(categories);
+		const catId = cats[0].id;
+
+		await db.insert(groceries).values({
+			name: 'lait',
+			checked: false,
+			createdAt: new Date().toISOString(),
+			categoryId: catId,
+		});
+
+		await db.delete(categories).where(eq(categories.id, catId));
+
+		const items = await db.select().from(groceries);
+		expect(items).toHaveLength(1);
+		expect(items[0].categoryId).toBeNull();
+	});
+});
diff --git a/src/lib/server/db/schema.ts b/src/lib/server/db/schema.ts
index 9113d9b..fae002d 100644
--- a/src/lib/server/db/schema.ts
+++ b/src/lib/server/db/schema.ts
@@ -10,7 +10,9 @@ export const groceries = sqliteTable('groceries', {
 	name: text('name').notNull().unique(),
 	checked: integer('checked', { mode: 'boolean' }).notNull().default(false),
 	createdAt: text('created_at').notNull(),
-	categoryId: integer('category_id').references(() => categories.id),
+	categoryId: integer('category_id').references(() => categories.id, {
+		onDelete: 'set null',
+	}),
 });
 
 export const references = sqliteTable('references', {
diff --git a/src/lib/server/db/test-helper.ts b/src/lib/server/db/test-helper.ts
new file mode 100644
index 0000000..5bae681
--- /dev/null
+++ b/src/lib/server/db/test-helper.ts
@@ -0,0 +1,13 @@
+import { createClient } from '@libsql/client';
+import { drizzle } from 'drizzle-orm/libsql';
+import { migrate } from 'drizzle-orm/libsql/migrator';
+import * as schema from './schema';
+
+export async function createTestDb() {
+	const client = createClient({ url: ':memory:' });
+	const db = drizzle(client, { schema });
+
+	await migrate(db, { migrationsFolder: './drizzle' });
+
+	return { db, client };
+}