# Data access and outbound HTTP

Verified 2026-09 against
<https://www.prisma.io/docs/orm/more/upgrade-guides/upgrading-versions/upgrading-to-prisma-7>,
<https://nodejs.org/api/globals.html#fetch> and <https://github.com/fastify/fastify-multipart>.

## 1. Prisma 7 setup

Prisma 7 changed four things at once, and a v6-shaped setup fails in confusing ways.

**`prisma/schema.prisma`** — `output` is now mandatory; the client no longer lands in `node_modules`:

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

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
```

**`prisma.config.ts`** at the project root — the CLI reads configuration here, and **environment
variables are no longer loaded automatically**:

```ts
import 'dotenv/config';
import { defineConfig, env } from 'prisma/config';

export default defineConfig({
  schema: 'prisma/schema.prisma',
  datasource: { url: env('DATABASE_URL') },
});
```

**The client** needs a driver adapter for every database (`@prisma/adapter-pg` for PostgreSQL), and
ships ESM-only, so `"type": "module"` is required:

```ts
import { PrismaPg } from '@prisma/adapter-pg';
import { PrismaClient } from './generated/prisma/client.js';
import { config } from './config.js';

const adapter = new PrismaPg({ connectionString: config.DATABASE_URL });

export const prisma = new PrismaClient({ adapter });
```

One client per process, created once and imported. A client per request opens a pool per request,
which exhausts the database in minutes under load.

## 2. Transactions

```ts
await prisma.$transaction(async (tx) => {
  const order = await tx.order.update({
    where: { id, status: 'pending' },
    data: { status: 'paid' },
  });

  await tx.outbox.create({
    data: { type: 'orders.order-paid.v1', aggregateId: order.id, payload: { orderId: order.id } },
  });
});
```

Three rules, in order of how often they are broken:

1. **No HTTP call inside the transaction.** A three-second payment call holds a row lock for three
   seconds across every concurrent request. Publish through the outbox instead
   (`messaging-patterns`).
2. **Keep it short and predictable.** Read what you need before opening it; do not loop over a
   thousand rows inside one.
3. **Make the guard part of the write.** `where: { id, status: 'pending' }` updating zero rows is how
   you detect a concurrent change, rather than reading then writing and hoping.

Interactive transactions have a timeout (`timeout` and `maxWait` options); a transaction that needs
more than the default is a design problem, not a configuration one.

## 3. Alternatives worth knowing

- **Drizzle** — schema in TypeScript, SQL-shaped queries, inferred types, small runtime. Good on
  serverless where a lighter client and a driver adapter matter.
- **Kysely** — a typed query builder and nothing else. Best when the team knows SQL and wants the
  types without an ORM's opinions; you own migrations.
- **TypeORM / MikroORM** — entity and unit-of-work patterns, common in NestJS codebases. Read the
  generated SQL; lazy relations are an N+1 generator.

Whichever you pick: one per service. Two ORMs in one codebase means two migration histories.

## 4. A typed outbound client

```ts
import { z } from 'zod';

const ChargeResponse = z.object({
  id: z.string(),
  status: z.enum(['authorised', 'declined']),
});

export type ChargeResponse = z.infer<typeof ChargeResponse>;

export async function charge(
  input: { orderId: string; amount: number; idempotencyKey: string },
  signal?: AbortSignal,
): Promise<ChargeResponse> {
  const timeout = AbortSignal.timeout(2_000);

  const response = await fetch(`${config.PAYMENTS_URL}/charges`, {
    method: 'POST',
    headers: {
      'content-type': 'application/json',
      'idempotency-key': input.idempotencyKey,
    },
    body: JSON.stringify({ orderId: input.orderId, amount: input.amount }),
    signal: signal ? AbortSignal.any([signal, timeout]) : timeout,
  });

  if (!response.ok) {
    throw new UpstreamError('payments', response.status);
  }

  return ChargeResponse.parse(await response.json());
}
```

Why each part is there:

- `AbortSignal.timeout` — without it, `fetch` waits as long as the upstream is willing to hold the
  socket, and one slow dependency turns into your outage.
- `AbortSignal.any([signal, timeout])` — when the client disconnects, the upstream call is abandoned
  too, instead of finishing work nobody will read.
- `idempotency-key` — this is a POST that moves money. If you ever retry it, the key is what stops the
  double charge (`api-design`).
- `ChargeResponse.parse` — an upstream field rename fails here, loudly, instead of writing `undefined`
  into your database.

## 5. Retry, only where it is safe

```ts
export async function withRetry<T>(
  operation: () => Promise<T>,
  { attempts = 3, baseMs = 200 } = {},
): Promise<T> {
  let lastError: unknown;

  for (let attempt = 0; attempt < attempts; attempt++) {
    try {
      return await operation();
    } catch (error) {
      if (!isRetryable(error)) {
        throw error;                       // 4xx, validation: retrying cannot help
      }
      lastError = error;
      const jitter = Math.random() * baseMs;
      await new Promise((r) => setTimeout(r, baseMs * 2 ** attempt + jitter));
    }
  }

  throw lastError;
}
```

Retry only idempotent operations, or non-idempotent ones that carry an idempotency key. Classify
first (`isRetryable`: network error, 429, 5xx) — retrying a 400 three times is three times the load
for the same failure. Cap the attempts, add jitter, and honour `Retry-After` when the response has
one. For a dependency that fails often on a hot path, add a circuit breaker (`opossum`) so you stop
paying the timeout on every request.

## 6. File uploads

Buffering an upload into memory is how a 2 GB request kills a 512 MB pod. Stream it:

```ts
import { pipeline } from 'node:stream/promises';
import multipart from '@fastify/multipart';

await app.register(multipart, { limits: { fileSize: 10 * 1024 * 1024, files: 1 } });

app.post('/documents', async (request, reply) => {
  const file = await request.file();
  if (!file) {
    throw new AppError(400, 'https://errors.example.com/validation', 'A file is required');
  }

  await pipeline(file.file, createUploadStream(file.filename));

  if (file.file.truncated) {
    throw new AppError(413, 'https://errors.example.com/too-large', 'File exceeds the limit');
  }

  return reply.status(201).send({ filename: file.filename });
});
```

Set `limits` — the default is generous. Check `truncated` after the stream finishes: when the limit
is hit the stream ends normally and sets the flag, so without this check you store a silently
truncated file. Never derive a storage path from `file.filename` without sanitising it.
