Prisma 7 + SQLite: nested writes, interactive transactions and unique errors: working versions (tested 2026-10-15)

{
  "name": "prisma-sqlite-transactions",
  "private": true,
  "type": "module",
  "scripts": {
    "test": "node --import tsx --test test/*.test.ts"
  },
  "dependencies": {
    "@prisma/client": "7.10.0",
    "@prisma/adapter-better-sqlite3": "7.10.0"
  },
  "devDependencies": {
    "tsx": "4.23.15",
    "@types/node": "24.19.2",
    "prisma": "7.10.0"
  }
}

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

datasource db {
  provider = "sqlite"
}

model Account {
  id      Int    @id @default(autoincrement())
  email   String @unique
  balance Int    @default(0)
  entries Entry[]
}

model Entry {
  id        Int     @id @default(autoincrement())
  amount    Int
  account   Account @relation(fields: [accountId], references: [id])
  accountId Int
}

import { defineConfig } from "prisma/config";
export default defineConfig({
  schema: "prisma/schema.prisma",
  datasource: { url: "file:./test.db" },
});

import { PrismaBetterSqlite3 } from "@prisma/adapter-better-sqlite3";
import { PrismaClient, Prisma } from "./generated/prisma/client.ts";

export const createClient = (url = "file:./test.db") => new PrismaClient({ adapter: new PrismaBetterSqlite3({ url }) });
type Db = ReturnType<typeof createClient>;

export class EmailTaken extends Error {}

export async function openAccount(db: Db, email: string, deposit: number) {
  try {
    return await db.account.create({ data: { email, balance: deposit, entries: { create: [{ amount: deposit }] } }, include: { entries: true } });
  } catch (e) {
    if (e instanceof Prisma.PrismaClientKnownRequestError && e.code === "P2002") throw new EmailTaken(email);
    throw e;
  }
}

// Interactive transaction: any throw inside rolls back every write.
export function transfer(db: Db, from: string, to: string, amount: number) {
  return db.$transaction(async (tx) => {
    const src = await tx.account.update({ where: { email: from }, data: { balance: { decrement: amount }, entries: { create: { amount: -amount } } } });
    if (src.balance < 0) throw new Error("insufficient funds");
    await tx.account.update({ where: { email: to }, data: { balance: { increment: amount }, entries: { create: { amount } } } });
  });
}

import { test, beforeEach, after } from "node:test";
import assert from "node:assert/strict";
import { createClient, openAccount, transfer, EmailTaken } from "../src/bank.ts";

const db = createClient();
beforeEach(async () => { await db.entry.deleteMany(); await db.account.deleteMany(); });
after(() => db.$disconnect());
const bal = async (email: string) => (await db.account.findUniqueOrThrow({ where: { email } })).balance;

test("nested create", async () => {
  const a = await openAccount(db, "[email protected]", 100);
  assert.equal(a.entries.length, 1);
});

test("duplicate email maps P2002 to a domain error", async () => {
  await openAccount(db, "[email protected]", 1);
  await assert.rejects(openAccount(db, "[email protected]", 1), EmailTaken);
});

test("transfer commits both sides", async () => {
  await openAccount(db, "[email protected]", 100); await openAccount(db, "[email protected]", 0);
  await transfer(db, "[email protected]", "[email protected]", 40);
  assert.deepEqual([await bal("[email protected]"), await bal("[email protected]")], [60, 40]);
});

test("failed transfer rolls back", async () => {
  await openAccount(db, "[email protected]", 10); await openAccount(db, "[email protected]", 0);
  await assert.rejects(transfer(db, "[email protected]", "[email protected]", 50), /insufficient/);
  assert.deepEqual([await bal("[email protected]"), await bal("[email protected]")], [10, 0]);
  assert.equal(await db.entry.count(), 2);
});