# ZQL on the Server

The Zero package includes utilities to run ZQL on the server directly against your upstream Postgres database.

This is useful for many reasons:

* It allows [mutators](https://zero.rocicorp.dev/docs/mutators) to read data using ZQL to check permissions or invariants.
* You can use ZQL to implement standard REST endpoints, allowing you to share code with mutators.
* In the future ([but not yet implemented](#ssr)), this can support server-side rendering.

> `ZQLDatabase` currently does a read of your postgres schema before every transaction. This is fine for most usages, but for high scale it may become a problem. [Let us know](https://bugs.rocicorp.dev/issue/3799) if you need a fix for this.

## Creating a Database

To run ZQL on the server, you will create a `ZQLDatabase` instance. Zero ships with several built-in factories for popular Postgres libraries and ORMs.

**Drizzle**

```ts
// app/api/mutate/db-provider.ts
import {zeroDrizzle} from '@rocicorp/zero/server/adapters/drizzle'
import {schema} from '../../zero/schema.ts'
import * as drizzleSchema from '../../drizzle/schema.ts'

// pass a drizzle client instance. for example:
export const drizzleClient = drizzle(pool, {
  schema: drizzleSchema
})
export const dbProvider = zeroDrizzle(schema, drizzleClient)

// Register the database provider for type safety
declare module '@rocicorp/zero' {
  interface DefaultTypes {
    dbProvider: typeof dbProvider
  }
}
```

**Kysely**

```ts
// app/api/mutate/db-provider.ts
import {Kysely, PostgresDialect} from 'kysely'
import {zeroKysely} from '@rocicorp/zero/server/adapters/kysely'
import {Pool} from 'pg'
import {schema} from '../../zero/schema.ts'

interface Database {
  user: {
    id: string
    name: string | null
    status: 'active' | 'inactive'
  }
}

const kysely = new Kysely<Database>({
  dialect: new PostgresDialect({
    pool: new Pool({
      connectionString: process.env.ZERO_UPSTREAM_DB!
    })
  })
})
export const dbProvider = zeroKysely(schema, kysely)

// Register the database provider for type safety
declare module '@rocicorp/zero' {
  interface DefaultTypes {
    dbProvider: typeof dbProvider
  }
}
```

**Prisma**

```ts
// app/api/mutate/db-provider.ts
import {PrismaPg} from '@prisma/adapter-pg'
import {PrismaClient} from '@prisma/client'
import {zeroPrisma} from '@rocicorp/zero/server/adapters/prisma'
import {schema} from '../../zero/schema.ts'

const prisma = new PrismaClient({
  adapter: new PrismaPg({
    connectionString: process.env.ZERO_UPSTREAM_DB!
  })
})
export const dbProvider = zeroPrisma(schema, prisma)

// Register the database provider for type safety
declare module '@rocicorp/zero' {
  interface DefaultTypes {
    dbProvider: typeof dbProvider
  }
}
```

**node-postgres**

```ts
// app/api/mutate/db-provider.ts
import {zeroNodePg} from '@rocicorp/zero/server/adapters/pg'
import {Pool} from 'pg'
import {schema} from '../../zero/schema.ts'

const pool = new Pool({
  connectionString: process.env.ZERO_UPSTREAM_DB!
})
export const dbProvider = zeroNodePg(schema, pool)

// You can also pass a client instead of a pool:
//
// const client = new Client({
//   connectionString: process.env.ZERO_UPSTREAM_DB!
// })
// await client.connect()
// export const dbProvider = zeroNodePg(schema, client)

// Register the database provider for type safety
declare module '@rocicorp/zero' {
  interface DefaultTypes {
    dbProvider: typeof dbProvider
  }
}
```

**postgres.js**

```ts
// app/api/mutate/db-provider.ts
import {zeroPostgresJS} from '@rocicorp/zero/server/adapters/postgresjs'
import postgres from 'postgres'
import {schema} from '../../zero/schema.ts'

const sql = postgres(process.env.ZERO_UPSTREAM_DB!)
export const dbProvider = zeroPostgresJS(schema, sql)

// Register the database provider for type safety
declare module '@rocicorp/zero' {
  interface DefaultTypes {
    dbProvider: typeof dbProvider
  }
}
```

Within your mutators, you can access the underlying transaction via `tx.dbTransaction.wrappedTransaction`:

**Drizzle**

```ts
// mutators.ts
export const mutators = defineMutators({
  createUser: defineMutator(
    z.object({id: z.string(), name: z.string()}),
    async ({tx, args: {id, name}}) => {
      if (tx.location === 'server') {
        await tx.dbTransaction.wrappedTransaction
          .insert(drizzleSchema.user)
          .values({id, name})
      }
    }
  )
})
```

**Kysely**

```ts
// mutators.ts
export const mutators = defineMutators({
  createUser: defineMutator(
    z.object({id: z.string(), name: z.string()}),
    async ({tx, args: {id, name}}) => {
      if (tx.location === 'server') {
        await tx.dbTransaction.wrappedTransaction
          .insertInto('user')
          .values({id, name, status: 'active'})
          .execute()
      }
    }
  )
})
```

**Prisma**

```ts
// mutators.ts
export const mutators = defineMutators({
  createUser: defineMutator(
    z.object({id: z.string(), name: z.string()}),
    async ({tx, args: {id, name}}) => {
      if (tx.location === 'server') {
        await tx.dbTransaction.wrappedTransaction.user.create(
          {
            data: {
              id,
              name,
              status: 'active'
            }
          }
        )
      }
    }
  )
})
```

**node-postgres**

```ts
// mutators.ts
export const mutators = defineMutators({
  createUser: defineMutator(
    z.object({id: z.string(), name: z.string()}),
    async ({tx, args: {id, name}}) => {
      if (tx.location === 'server') {
        await tx.dbTransaction.wrappedTransaction.query(
          'insert into "user" (id, name) values ($1, $2) returning *',
          [id, name]
        )
      }
    }
  )
})
```

**postgres.js**

```ts
// mutators.ts
export const mutators = defineMutators({
  createUser: defineMutator(
    z.object({id: z.string(), name: z.string()}),
    async ({tx, args: {id, name}}) => {
      if (tx.location === 'server') {
        await tx.dbTransaction.wrappedTransaction<
          {id: string; name: string}[]
        >`insert into "user" (id, name)
          values (${id}, ${name})
          returning *`
      }
    }
  )
})
```

### Custom Database

To implement support for some other Postgres bindings library, you will implement the `DBConnection` interface.

See the implementations for the [existing adapters](https://github.com/rocicorp/mono/tree/main/packages/zero-server/src/adapters) for examples.

## Running ZQL

Once you have an instance of `ZQLDatabase`, use the `transaction()` method to run ZQL:

```ts
await dbProvider.transaction(async tx => {
  // await tx.mutate...
  // await tx.query...
  // await myMutator.fn({tx, ctx, args})
})
```

## SSR

Zero doesn't yet have the wiring setup in its bindings layers to really nicely support server-side rendering ([patches welcome though!](https://bugs.rocicorp.dev/issue/3491)).

For now, we don't recommend using Zero with SSR. Use your framework's recommended pattern to prevent SSR execution:

**TanStack Start**

```tsx
import {lazy} from 'react'

// Use React lazy to defer loading the ZeroProvider
const ZeroProvider = lazy(() =>
  import('@rocicorp/zero/react').then(mod => ({
    default: mod.ZeroProvider
  }))
)

function Root() {
  return (
    <ZeroProvider>
      <App />
    </ZeroProvider>
  )
}
```

**Next.js**

```tsx
// Mark client-only components
'use client'

import {ZeroProvider} from '@rocicorp/zero/react'

export default function Root() {
  return (
    <ZeroProvider>
      <App />
    </ZeroProvider>
  )
}
```

**SolidStart**

```tsx
import {clientOnly} from '@solidjs/start'

const ZeroProvider = clientOnly(async () => {
  // Optionally dynamic import to code-split
  return import('@rocicorp/zero/solid').then(mod => ({
    default: mod.ZeroProvider
  }))
})

export default function Root() {
  return (
    <ZeroProvider>
      <App />
    </ZeroProvider>
  )
}
```

**For AI agents**: to view all the available documentation, visit https://zero.rocicorp.dev/llms.txt