---
title: Postgres
description: Any Postgres database. You create A2's tables; A2 never changes your schema.
---

```ts server/orders.ts
import { createServer } from 'experimental-a2/server'
import { postgres } from 'experimental-a2/store-postgres'
import { orders } from '@/contracts'

export const ordersServer = createServer({
  contract: orders,
  store: postgres({ connectionString: process.env.DATABASE_URL }),
  handlers: {
    /* ... */
  },
})
```

```package-install
npm i pg
```

Works with Neon, Supabase, RDS, or your own server, including transaction-mode
poolers.

## Create the tables

A2 doesn't create tables in your database. This is the SQL it needs:
[`schema.sql`](https://github.com/vercel-labs/a2/blob/main/packages/a2/sql/postgres/schema.sql),
also in the package at `node_modules/experimental-a2/sql/postgres/schema.sql`.
Apply it once before your first deploy, however you run SQL against your
database, as the role your app connects with.

## Upgrade

When a release changes the schema, its changelog says so. Apply that
release's `schema.sql` before you deploy it. The version you're running keeps
working against the new schema.

If the tables are missing or out of date, the store's first query throws
`STORE_NOT_CONFIGURED` naming the file to apply. Once it's applied, the next
request works, with no redeploy. The store checks with one read before its
first query, which never waits on or blocks writes.

## Live updates (polling)

By default, live streams poll every 25ms while a session is busy, backing off
to every two seconds when it's quiet, so an update from another client can take
up to two seconds, plus query latency, to reach a quiet stream. A client's own
`push()` refreshes its stream immediately, even when the write and the stream
run in different invocations.

## Live updates (notifications)

For instant updates, pass `listenConnectionString`, a direct connection to the
same database (not through a pooler), and apply
[`notifications.sql`](https://github.com/vercel-labs/a2/blob/main/packages/a2/sql/postgres/notifications.sql)
once, the same way as `schema.sql`.

Its triggers notify listeners of every write, including writes from stores
without the option. Notifications arrive after commit, a reconnect catches up
from the cursor, and subscriptions also reconcile every ten seconds. Every
serverless instance opens its own direct connection, so leave connection
headroom.

## Use postgres.js

`connectionString` creates a `pg` pool. To use
[postgres.js](https://github.com/porsager/postgres) instead, wrap it as
`client`:

```ts server/store.ts
import postgresJs from 'postgres'
import { postgres } from 'experimental-a2/store-postgres'

const url = process.env.DATABASE_URL
if (!url) throw new Error('set DATABASE_URL')

const sql = postgresJs(url, {
  // A2 sends JSON as text; this keeps postgres.js from encoding it again.
  types: {
    json: {
      to: 114,
      from: [114, 3802],
      serialize: (x: unknown) => (typeof x === 'string' ? x : JSON.stringify(x)),
      parse: (x: string) => JSON.parse(x),
    },
  },
})

export const store = postgres({
  client: {
    query: async (text, values = []) => ({
      rows: [...(await sql.unsafe(text, values))],
    }),
    connect: async () => {
      const connection = await sql.reserve()
      return {
        query: async (text, values = []) => ({
          rows: [...(await connection.unsafe(text, values))],
        }),
        release: () => connection.release(),
      }
    },
    end: () => sql.end(),
  },
})
```

`listenConnectionString` still opens its listener with `pg`, so live
notifications need `pg` installed too.

## Options

| Option | |
| --- | --- |
| `connectionString` | creates a `pg` pool, shared by stores with the same settings |
| `client` | your own pool instead: a `pg` `Pool`, PGlite, or a pooler wrapper. Its lifecycle is yours |
| `listenConnectionString` | a direct connection for notifications |
| `clock`, `ids` | test seams for timestamps and generated event ids |