2 files changed, 58 insertions, 0 deletions
diff --git a/src/lib/server/db/errors.test.ts b/src/lib/server/db/errors.test.ts
new file mode 100644
index 0000000..d19aec2
--- /dev/null
+++ b/src/lib/server/db/errors.test.ts
@@ -0,0 +1,45 @@
+import { describe, it, expect, beforeEach } from 'vitest';
+import { groceries, categories } from '$lib/server/db/schema';
+import { eq } from 'drizzle-orm';
+import { createTestDb, type TestDb } from '$lib/server/db/test-helper';
+import { isUniqueViolation } from './errors';
+
+describe('isUniqueViolation', () => {
+	let db: TestDb['db'];
+
+	beforeEach(async () => {
+		db = (await createTestDb()).db;
+		await db.insert(categories).values({ name: 'Épices' });
+		await db
+			.insert(groceries)
+			.values({ name: 'olives', createdAt: '2026-01-01T00:00:00.000Z' });
+	});
+
+	it('detects a duplicate insert on a unique column', async () => {
+		const error = await db
+			.insert(categories)
+			.values({ name: 'Épices' })
+			.catch((e: unknown) => e);
+
+		expect(isUniqueViolation(error)).toBe(true);
+	});
+
+	it('detects a duplicate name on update', async () => {
+		await db
+			.insert(groceries)
+			.values({ name: 'pain', createdAt: '2026-01-02T00:00:00.000Z' });
+
+		const error = await db
+			.update(groceries)
+			.set({ name: 'olives' })
+			.where(eq(groceries.name, 'pain'))
+			.catch((e: unknown) => e);
+
+		expect(isUniqueViolation(error)).toBe(true);
+	});
+
+	it('leaves other failures alone', () => {
+		expect(isUniqueViolation(new Error('network is unreachable'))).toBe(false);
+		expect(isUniqueViolation(undefined)).toBe(false);
+	});
+});
diff --git a/src/lib/server/db/errors.ts b/src/lib/server/db/errors.ts
new file mode 100644
index 0000000..4b101f8
--- /dev/null
+++ b/src/lib/server/db/errors.ts
@@ -0,0 +1,13 @@
+/**
+ * True when the failure is SQLite's UNIQUE constraint. Drizzle wraps driver
+ * errors, so the libsql error sits at some depth of the `cause` chain.
+ */
+export function isUniqueViolation(error: unknown): boolean {
+	let current: unknown = error;
+	while (current instanceof Error) {
+		if ('code' in current && current.code === 'SQLITE_CONSTRAINT_UNIQUE')
+			return true;
+		current = current.cause;
+	}
+	return false;
+}