# Use Drizzle Transactions in Zero

2025-05-13

I've spent another week digging into [Zero](https://zero.rocicorp.dev/). To pull off instant mutations they need to use ZQL that can run in both the client and then again on the server setting. Mutations receive a `Transaction` that has typed bindings to your schema:

From the docs:

```ts
async function updateIssue(
  tx: Transaction,
  {id, title}: {id: string; title: string},
) {
  // Validate title length.
  if (title.length > 100) {
    throw new Error(`Title is too long`);
  }

  await tx.mutate.issue.update({id, title});
}
```

Because of the two-pass nature of things, the type is a union of client and server transactions:

```ts
export type Transaction<S extends Schema, TWrappedTransaction = unknown> =
  | ServerTransaction<S, TWrappedTransaction>
  | ClientTransaction<S>;
```

In that server pass, we need an actual db transaction, and for this Zero bundles a `PostgresJSConnection` class which is a `postgres-js` compatible interface - which is passed to your mutation processor in the `/push` endpoint implementation: 

```ts
const processor = new PushProcessor(
  new ZQLDatabase(
    new PostgresJSConnection(
      postgres(process.env.ZERO_UPSTREAM_DB! as string)
    ),
    schema
  )
);
```

What really caught my eye is the underlying interface that this `ZQLDatabase` needs from its connection. It's super simple. You just need to provide something that implements `DBConnection`:

```typescript
// From @rocicorp/zero/pg
export interface DBConnection<TWrappedTransaction> extends Queryable {
  transaction: <T>(
    cb: (tx: DBTransaction<TWrappedTransaction>) => Promise<T>,
  ) => Promise<T>;
}

export interface DBTransaction<T> extends Queryable {
  readonly wrappedTransaction: T; // This is the key!
}
```

(Queryable just means it has a `query(sql: string, params: unknown[]): Promise<Row[]>` method).

This means you're not locked into *only* using ZQL for your server-side write logic if you don't want to be, or if you have complex needs that are easier to express with your existing ORM. Zero uses TypeScript generics to let you "bring your own transaction type" into the server-side mutator context. It just needs to be a `Queryable` and be able to create transactions.

My setup uses Drizzle with `node-postgres`. I started by writing my own interface for `node-postgres`, but I realized I might as well go via the `drizzle` instance itself and use the `$client` interface to `node-postgres`! Let's see how this looks and what advantages it brings.

When you define your server-side mutators, you use `CustomMutatorDefs` imported from `@rocicorp/zero/pg`. This version of `CustomMutatorDefs` takes an optional second type parameter for your custom transaction type (not to be confused with `CustomMutatorDefs` from `@rocicorp/zero` (without `/pg`)):

```typescript
import type { ServerTransaction } from "@rocicorp/zero";
import type { CustomMutatorDefs } from "@rocicorp/zero/pg"; // Note: from /pg
import { eq } from "drizzle-orm";

import { Order } from "@acme/db"; // My Drizzle schema

import type { AuthData } from "./auth";
import type { DrizzleTransaction } from "./drizzle"; // My Drizzle transaction type
import { createMutators } from "./mutators"; // Client mutators
import type { Schema } from "./zero-schema.gen";

export function createServerMutators(
  authData: AuthData,
  _asyncTasks: (() => Promise<void>)[] = [], // For out-of-transaction async work
) {
  const clientMutators = createMutators(authData); // Standard client mutators
  return {
    ...clientMutators,
    // Example of a server-specific mutator or overriding a client one
    reverseOrderNameCasing: {
      update: async (
        tx, // This `tx` is now strongly typed!
        { id, fullName }: { id: string; fullName: string },
      ) => {
        // You can still call the client-side optimistic version if you want
        await clientMutators.reverseOrderNameCasing.update(tx, {
          id,
          fullName,
        });

        // And now, the magic: access your ORM's transaction directly!
        // tx.dbTransaction is the DBTransaction<DrizzleTransaction>
        // tx.dbTransaction.wrappedTransaction is our DrizzleTransaction
        await tx.dbTransaction.wrappedTransaction
          .update(Order)
          .set({ fullName })
          .where(eq(Order.id, id));
      },
    },
  } as const satisfies CustomMutatorDefs<
    ServerTransaction<Schema, DrizzleTransaction> // Here's the custom tx type!
  >;
}
```

Notice `ServerTransaction<Schema, DrizzleTransaction>`. That `DrizzleTransaction` is *my* Drizzle transaction type. Inside this server mutator, `tx.dbTransaction.wrappedTransaction` gives me the full Drizzle transaction object, with its query builder and all its power. This is fantastic if I need to do complex writes, interact with other tables not managed by Zero, or just prefer Drizzle's API for certain operations.

It's also worth noting that the client-side `CustomMutatorDefs` (the one you import from just `@rocicorp/zero`) *doesn't* have this second type parameter for a custom transaction, because on the client, mutations operate against Zero's internal ZQL-based transaction context.

This design allows for a really nice separation: keep optimistic client mutators simple with ZQL, and then on the server, use ZQL via Zero's helpers if it fits, or seamlessly drop down to your preferred, fully-typed ORM/query builder when needed.

For anyone interested, here's the Drizzle adapter I put together. It was surprisingly straightforward:

```typescript
import type { DBConnection, DBTransaction, Row } from "@rocicorp/zero/pg";
import { type NodePgDatabase } from "drizzle-orm/node-postgres";
import type { PoolClient, QueryResultRow } from "pg";

// Assuming your Drizzle schema is exported from @acme/db
import type * as schema from "@acme/db";
export * as schema from "@acme/db"; // Re-export for convenience

// My Drizzle instance type, assuming $client is the raw pg.PoolClient, matches how 
// `drizzle()` inits when using `node-postgres`
type Drizzle = NodePgDatabase<typeof schema> & { $client: PoolClient };

// Extract the Drizzle-specific transaction type
export type DrizzleTransaction = Parameters<
  Parameters<Drizzle["transaction"]>[0]
>[0];

export class DrizzleConnection implements DBConnection<DrizzleTransaction> {
  drizzle: Drizzle;

  constructor(drizzle: Drizzle) {
    this.drizzle = drizzle;
  }

  // `query` is used by Zero's ZQLDatabase for ZQL reads on the server
  query(sql: string, params: unknown[]): Promise<Row[]> {
    return this.drizzle.$client
      .query<QueryResultRow>(sql, params)
      .then(({ rows }) => rows);
  }

  // `transaction` wraps Drizzle's transaction
  transaction<T>(
    fn: (tx: DBTransaction<DrizzleTransaction>) => Promise<T>,
  ): Promise<T> {
    return this.drizzle.transaction((drizzleTx) =>
      // Pass a new Zero DBTransaction wrapper around Drizzle's one
      fn(new ZeroDrizzleTransaction(drizzleTx)),
    );
  }
}

class ZeroDrizzleTransaction implements DBTransaction<DrizzleTransaction> {
  readonly wrappedTransaction: DrizzleTransaction;

  constructor(drizzleTx: DrizzleTransaction) {
    this.wrappedTransaction = drizzleTx;
  }

  // This `query` method would be used if ZQL reads happen *within*
  // a custom mutator that is itself running inside this wrapped transaction.
  query(sql: string, params: unknown[]): Promise<Row[]> {
    // Drizzle's transaction object might hide the raw client,
    // this is one way to get at it for `pg` driver. Adjust if needed.
    const session = this.wrappedTransaction._.session as unknown as {
      client: Drizzle["$client"];
    };
    return session.client
      .query<QueryResultRow>(sql, params)
      .then(({ rows }) => rows);
  }
}
```

And to tie it all together, here’s what my Next.js `/api/push` route handler looks like using this setup:

```typescript
import type { JSONValue } from "@rocicorp/zero";
import { PushProcessor, ZQLDatabase } from "@rocicorp/zero/pg";
import { NextResponse, type NextRequest } from "next/server";

import { schema as zeroSchema } from "@acme/zero"; // My Zero schema
import { decodeAuthJWT, type AuthData } from "@acme/zero/auth";
import { DrizzleConnection } from "@acme/zero/drizzle";
import { createServerMutators } from "@acme/zero/server-mutators";

import { env } from "~/env";
import { db, withDb } from "~/lib/db"; // My Drizzle instance and connection helper

async function handler(request: NextRequest) {
  const json = await request
    .json()
    .then((data) => data as JSONValue)
    .catch(() => {
      // Gracefully handle empty body for GET or other cases
      return {} as JSONValue; // PushProcessor expects JSONValue
    });

  const authHeader = request.headers.get("Authorization");
  const subResult = decodeAuthJWT(authHeader, env.SECRET);

  if (subResult.isErr()) {
    console.error("Error decoding JWT:", subResult.error.message);
    return new NextResponse(subResult.error.message, { status: 401 });
  }
  const authData = subResult.value;

  // For any out-of-transaction async work after mutations commit
  const asyncTasks: (() => Promise<void>)[] = [];
  const mutators = createServerMutators(authData, asyncTasks);

  // Assuming `withDb` provides a Drizzle instance connected to a PoolClient
  const response = await withDb(async (drizzleDb) => {
    const processor = new PushProcessor(
      new ZQLDatabase(new DrizzleConnection(drizzleDb), zeroSchema),
    );
    const searchParams = request.nextUrl.searchParams;

    // Process the mutations
    const pushResponse = await processor.process(mutators, searchParams, json);

    // Run any async tasks *after* the database transaction has committed
    await Promise.all(asyncTasks.map((task) => task()));

    return NextResponse.json(pushResponse);
  });

  return response;
}

export { handler as GET, handler as POST }; // Next.js route convention
```

This level of control and integration with existing data access patterns on the server is a really strong point for Zero, especially for complex applications or teams already invested in a particular ORM. It makes adopting Zero for its real-time sync capabilities much less intrusive on the backend. Impressive stuff.