Neon Realtime

<!-- Generated by scripts/sync-skill-docs.mjs from website@docs/neon-auth-package-rename:bf3595986 (docs/realtime/guides/queries). Do not edit: change the docs and run npm run sync:docs. sha256:ece6ed71de561220661f3b36acb1c6721884a650d2229d65f96afd305b1afcfd -->

Canonical page: https://neon.com/docs/realtime/guides/queries.md

Summary: Define live queries in your backend with raw SQL or Drizzle, fill in their parameters from the authenticated request, and seal them so clients can subscribe without being able to read or change them.

Defining queries

Define live SQL queries that you can subscribe to.

Contents

• Setup

• Defining live queries

  • Table requirements

  • Using raw SQL

  • Using a query builder or ORM

• Sealing queries

  • Renewing sealed queries

• Authorization

  • Sealing authorized queries

  • Live authorization

  • Why not row-level security

• Exposing queries

  • Exposing an endpoint

  • Using server functions

Realtime works by defining and subscribing to live SQL queries. Queries are designed to be defined securely in your backend code and subscribed‑to safely in your frontend.

Define live queries using raw SQL or a query builder or ORM. Seal the queries and expose them to your frontend via an API endpoint or a server function, using your normal authorization context.

Setup

The guides all share one running example: a chat app where users read the messages in the channels they belong to. You can see the source code in the examples repo or expand the steps below.

<details>

<summary>Setup steps</summary>

Install Drizzle with the node-postgres driver (pg), Hono, ws, and jose, which validates Better Auth tokens:

npm install drizzle-orm pg hono jose ws
npm install -D @types/pg @types/ws

Install the Realtime TypeScript SDK and its Drizzle adapter:

npm install @neon/realtime @neon/realtime-drizzle

Install the Managed Better Auth client for the frontend:

npm install @neon/auth

Add a neon.ts file that enables Managed Better Auth and Realtime on your branch:

import { defineConfig } from "@neon/config/v1"

export default defineConfig({
  auth: true,
  preview: { realtime: { allowedOrigins: ["http://localhost:5173"] } }
})

List every origin your browser app runs on in allowedOrigins, for example http://localhost:3000 for the SSR frameworks.

Install the config package, link the directory to your Neon project and deploy the config:

npm install -D @neon/config
neon link
neon deploy

neon deploy enables Managed Better Auth and Realtime, waits until they're ready, and writes the four server variables below to .env.local. If Realtime isn't ready before the deploy stops waiting, run neon env pull once it is.

These are the environment variables on the server:

• DATABASE_URL: your Lakebase Postgres connection string

• NEON_REALTIME_SECRET: the secret from the Realtime page in the Neon Console

• NEON_AUTH_BASE_URL: your Managed Better Auth URL

• NEON_REALTIME_URL: the URL from the same Realtime page

And these in the frontend, with the same values as NEON_AUTH_BASE_URL and NEON_REALTIME_URL:

• VITE_NEON_AUTH_URL

• VITE_NEON_REALTIME_URL

Vite expands $VAR in env files, and neon env pull keeps lines it doesn't manage, so point them at the server variables:

echo 'VITE_NEON_AUTH_URL=$NEON_AUTH_BASE_URL' >> .env.local
echo 'VITE_NEON_REALTIME_URL=$NEON_REALTIME_URL' >> .env.local

Define the example schema and apply your usual Drizzle migrations:

import { integer, pgTable, primaryKey, text, timestamp, uuid } from "drizzle-orm/pg-core"

export const messages = pgTable(
  "messages", {
    id: uuid("id").primaryKey(),
    channel_id: integer("channel_id").notNull(),
    author_id: text("author_id").notNull(),
    body: text("body").notNull(),
    created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow()
  }
)

export const channelMembers = pgTable(
  "channel_members", {
    channel_id: integer("channel_id").notNull(),
    user_id: text("user_id").notNull()
  },
  (table) => [primaryKey({ columns: [table.channel_id, table.user_id] })]
)

export type Message = typeof messages.$inferSelect

Configure your Drizzle client (db):

import { drizzle } from "drizzle-orm/node-postgres"
import { Pool } from "pg"
import * as schema from "./schema"

const pool = new Pool({
  // Neon URLs say sslmode=require. Asking pg for verify-full keeps today's checks and skips its warning
  connectionString: process.env.DATABASE_URL!.replace("sslmode=require", "sslmode=verify-full")
})
export const db = drizzle(pool, { schema })

// So an idle connection error, for example when the compute scales to zero, doesn't crash the API
pool.on("error", (error: Error) => console.error(error))

Great, that's the main setup to follow along with the guides. Or for the full boilerplate, see the example source code on GitHub.

</details>

Defining live queries

Neon Realtime supports any deterministic SQL query that is supported by Postgres.

This includes where clauses, order by and limit, joins, aggregates, group by and distinct, functions (built-in and user-defined), extensions, window functions, sub-queries, etc.

The only limitations are that:

1. queries must be read-only, i.e.: must not have side effects

2. any functions used in your queries must be deterministic

So, for example, you can't SELECT over updated rows and you can't use functions like now() or gen_random_uuid().

Table requirements

Tables need a stable replica identity. If your tables have a primary key, they're fine.

Optional: REPLICA IDENTITY FULL:

Setting REPLICA IDENTITY FULL can make live queries marginally faster, at the cost of writing the whole old row to the Postgres WAL on every update and delete. That may save Realtime a lookup when it processes those changes. If latency matters for a write-heavy table, benchmark both settings with your own workload before you switch.

ALTER TABLE messages REPLICA IDENTITY FULL;

Using raw SQL

Create a RealtimeServer instance (secret is the NEON_REALTIME_SECRET generated when enabling Realtime and db should be the dbname in your Postgres connection string):

import { createRealtime } from "@neon/realtime/server"

export const realtime = createRealtime({
  secret: process.env.NEON_REALTIME_SECRET!,
  db: "neondb"
})

createRealtime() also takes a debugMode option.

Then define queries using rawSql. For example:

import { rawSql } from "@neon/realtime/server"

import type { Message } from "./schema"

export const allMessages = rawSql<Message>(
  `SELECT author_id, body, channel_id, created_at, id FROM messages`
)

A raw SQL query has to be a single SELECT statement with no ;. With at most 32 KiB of SQL and at most 256 parameters of up to 16 KiB each.

Query parameters

When your query has parameters, wrap it in a function so they can be provided when using it. Put placeholders (like $1, $2, etc.) in the SQL and pass the values in the array.

For example:

import { rawSql } from "@neon/realtime/server"

import type { Message } from "./schema"

export function messagesQuery(userId: string, channelId: number) {
  return rawSql<Message>(
    `SELECT messages.author_id, messages.body, messages.channel_id,
       messages.created_at, messages.id FROM messages
     INNER JOIN channel_members ON channel_members.channel_id = messages.channel_id
     WHERE channel_members.user_id = $1 AND messages.channel_id = $2
     ORDER BY messages.created_at DESC
     LIMIT 50`,
    [userId, channelId]
  )
}

Warning: SQL injection

Never use literal interpolation for user input like `WHERE user_id = '${userId}'`. Always use placeholders like `WHERE user_id = $1` instead.

Parameter types

You can pass strings, numbers, booleans, bigints and null as they are. Other value types need a pgParam helper to specify the Postgres type. A Date could be a date, a timestamp or a timestamptz, so wrap it in pgParam.date(), pgParam.timestamp() or pgParam.timestamptz().

For example, this filters messages since a timestamp with timezone:

import { pgParam, rawSql } from "@neon/realtime/server"

import type { Message } from "./schema"

export function messagesSinceQuery(userId: string, channelId: number, since: Date) {
  return rawSql<Message>(
    `SELECT messages.author_id, messages.body, messages.channel_id,
       messages.created_at, messages.id FROM messages
     INNER JOIN channel_members ON channel_members.channel_id = messages.channel_id
     WHERE channel_members.user_id = $1 AND messages.channel_id = $2 AND messages.created_at > $3
     ORDER BY messages.created_at DESC
     LIMIT 50`,
    [userId, channelId, pgParam.timestamptz(since)]
  )
}

There are helpers for dates, JSON, bytea and arrays, such as pgParam.array("text", ["a"]). pgParam.text() covers any other named type, for example pgParam.text("uuid", id).

Using a query builder or ORM

Realtime supports an adapter pattern to convert any typed query object to raw SQL. This works by passing an adapter to createRealtime.

The rest of these guides use the Drizzle versions of backend/realtime.ts and backend/queries.ts below in place of the raw SQL files above.

For example, configure Realtime to use the drizzleAdapter:

import { createRealtime } from "@neon/realtime/server"
import { drizzleAdapter } from "@neon/realtime-drizzle"

export const realtime = createRealtime({
  secret: process.env.NEON_REALTIME_SECRET!,
  db: "neondb",
  adapter: drizzleAdapter()
})

You can now define queries using Drizzle:

import { and, desc, eq, getTableColumns } from "drizzle-orm"

import { db } from "./db"
import { channelMembers, messages } from "./schema"

export function messagesQuery(userId: string, channelId: number) {
  return db
    .select(getTableColumns(messages))
    .from(messages)
    .innerJoin(channelMembers, eq(channelMembers.channel_id, messages.channel_id))
    .where(and(eq(channelMembers.user_id, userId), eq(messages.channel_id, channelId)))
    .orderBy(desc(messages.created_at))
    .limit(50)
}

See the Drizzle and Kysely examples for more details.

Tip: Column name casing

The Drizzle adapter needs each selected key to match its column name. That's why the schema uses snake_case keys and the join selects getTableColumns(messages).

The Drizzle example shows how to alias fields to match the column key.

Sealing queries

Once you've defined a query, if you're using it in trusted backend code, you can just subscribe to it right away. However, most of the time, you'll want to subscribe to live queries in an untrusted environment, such as a frontend app or agent runtime.

This is where sealing comes in. Sealing signs and encrypts the exact query, its parameters and the target database into a short-lived capability (similar to a JWT), so it can be used safely in an untrusted environment.

Warning: Keep your Realtime secret on the server

Anyone with NEON_REALTIME_SECRET can seal any query. Treat it like your DATABASE_URL, and never ship it in a browser bundle. In a Vite app, that means never giving it a VITE_ prefix. To replace it, run neon realtime rotate-secret.

To seal a query you pass your query instance, e.g.:

const query = messagesQuery(userId, channelId)

To the seal method of the RealtimeServer instance you created above:

const sealedQuery = await realtime.seal({ query })

It's local Web Crypto work with no network call, so it's cheap to run on every request. It returns a SealedLiveQuery<Row>, which has three fields:

• capability: the encrypted query

• expiresAt: when it expires, in Unix milliseconds

• queryFingerprint: a stable identity for the exact query

The client can subscribe to a sealed query, but it can't read or change it. Your secret, your SQL and the parameter values bound to it are not readable anywhere in it. The Live SQL API verifies the capability itself, so subscribing doesn't need a call back to your server.

Important: Sealed queries are bearer tokens

Sealed queries are typically user-specific and sealed per request, where they can bake in parameters like the authenticated user ID.

Anyone holding a sealed query can subscribe to it until it expires. Send it over HTTPS, and keep it out of URLs, logs and persistent browser storage.

Renewing sealed queries

Sealed queries are short-lived (currently 60 seconds). To keep a subscription running, renew its sealed query before expiresAt, or the subscription goes stale when it expires. The reactivity bindings renew for you when you pass them a refreshQuery function. The subscribing guide shows how to renew manually.

A renewal has to be for the same query. Different parameter values make a different query, with a different fingerprint. So, for example, to switch channels in the chat app, you would seal the new query and subscribe again.

Authorization

Once you've sealed a query, you'll want to get it to your frontend to subscribe to.

You do this by exposing the query from a request handler in your backend. So that's where you authenticate the user and decide what they can see.

Sealing authorized queries

The flow is as follows:

1. read the auth context from the request

2. authorize access using arbitrary business logic (e.g.: your auth middleware)

3. construct the query, passing in any relevant parameters from the auth context (e.g.: filtering or joining on the authenticated userId)

4. seal the query so these can't be changed

This allows you to authorize live query subscriptions using existing web auth logic, middleware and frameworks, such as Neon Auth.

Live authorization

In our running example, messagesQuery() joins through channel_members and filters on the userId, so it only returns messages from channels the user belongs to:

.where(and(eq(channelMembers.user_id, userId), eq(messages.channel_id, channelId)))

The join is part of the live query, so it stays live. If you remove someone from a channel, they'll immediately lose access to its messages.

That's why Realtime authorizes the query, not the connection. Your request handler checks who's asking and seals a query that only matches the rows that the user is allowed to see. The authorization is defined at request time but it dynamically adapts to your data in realtime.

Warning: Never trust user input from the client

Take authorization context from a verified session or token and validate everything else (like channelId) that the client sends in the request data.

Why not row-level security

Postgres row-level security (RLS) is often slow and becomes a bottleneck. It's also awkward to develop with, because your access rules live in migrations, rather than your application code.

It's simpler and more efficient to reuse the auth logic you already have in your backend.

Many existing apps do use RLS, though. So, while it isn't our recommended approach to auth, we have designed Neon Realtime's architecture to be able to support it and we aim to implement support before GA.

Exposing queries

There are two main ways to expose a sealed, authorized query to your frontend:

1. exposing an endpoint from your backend API

2. using server functions (often with SSR)

These are both request handlers. They run in a trusted environment in your backend with auth context available from the request.

With Vite and Hono, the browser's auth client talks to Managed Better Auth directly, so its session cookie never reaches your API. The browser sends the session's JWT as a bearer token, and the API verifies it. Next.js and TanStack Start mount Managed Better Auth on your app's own origin, so server code reads the session from its cookie and the browser holds no token. Either way, the Realtime connection carries no user credential: the sealed query is the authorization.

Exposing an endpoint

Expose the sealedQuery from an API endpoint, using any web framework.

Our running example uses Hono and Managed Better Auth. The client sends its auth token in an Authorization: Bearer header. The JWT is verified against your Managed Better Auth JWKS in a requireUser helper:

import type { Context } from "hono"
import { HTTPException } from "hono/http-exception"
import { createRemoteJWKSet, jwtVerify } from "jose"

const authUrl = process.env.NEON_AUTH_BASE_URL!
const origin = new URL(authUrl).origin
const jwks = createRemoteJWKSet(new URL(`${authUrl}/.well-known/jwks.json`))

async function verifyUserId(token: string): Promise<string | undefined> {
  try {
    const { payload } = await jwtVerify(token, jwks, { issuer: origin, audience: origin })

    return payload.sub
  } catch {
    return undefined
  }
}

export async function getUser(headers: Headers): Promise<{ id: string } | undefined> {
  const header = headers.get("Authorization")
  const token = header?.startsWith("Bearer ") ? header.slice("Bearer ".length) : undefined
  const userId = token ? await verifyUserId(token) : undefined

  return userId ? { id: userId } : undefined
}

export async function requireUser(c: Context): Promise<{ id: string }> {
  const user = await getUser(c.req.raw.headers)

  if (!user) {
    throw new HTTPException(401, { message: "Unauthorized" })
  }

  return user
}

We then use this auth helper in a route to securely build and expose the sealedQuery:

import { Hono } from "hono"
import { validator } from "hono/validator"

import { requireUser } from "./auth"
import { messagesQuery } from "./queries"
import { realtime } from "./realtime"

const app = new Hono().post(
  "/api/v1/messages/live",
  validator("json", (value, c) => {
    const channelId: unknown = value?.channelId

    if (typeof channelId !== "number" || !Number.isInteger(channelId)) {
      return c.json({ error: "channelId must be an integer" }, 400)
    }

    return { channelId }
  }),
  async (c) => {
    const user = await requireUser(c)
    const { channelId } = c.req.valid("json")

    const query = messagesQuery(user.id, channelId)
    const sealedQuery = await realtime.seal({ query })

    return c.json(sealedQuery)
  }
)

export type AppType = typeof app

export default app

The validator() middleware checks the JSON body before the handler runs and types it, so the frontend's hc client knows this endpoint takes { channelId: number }. The handler takes the user's ID from requireUser() and the channelId from the validated body, builds the query, seals it and returns it as JSON. Chain every route onto new Hono() in one expression, because AppType only carries the routes chained into it.

seal() doesn't depend on request or response types and a sealed query is plain JSON. The subscribing guide shows how to fetch it from the client and subscribe to it.

Using server functions

If you're using a framework that supports server functions, you can return the sealedQuery from one. For example, with TanStack Start you can create a server function.

The server function reads the session from the Managed Better Auth cookie with requireUser, a server-only middleware. The SSR guide sets it up. The server function then seals the query for context.user.id:

import { createServerFn } from "@tanstack/react-start"

import { messagesQuery } from "./backend/queries"
import { realtime } from "./backend/realtime"
import { requireUser } from "./session-middleware"

export const sealMessages = createServerFn({ method: "POST" })
  .middleware([requireUser])
  .validator((data: { channelId: number }) => {
    if (!Number.isInteger(data.channelId)) {
      throw new Error("channelId must be an integer")
    }

    return data
  })
  .handler(({ data, context }) =>
    realtime.seal({ query: messagesQuery(context.user.id, data.channelId) })
  )

The browser calls sealMessages to get the sealed query, and calls it again to renew it.

To seal the query while the page renders on the server, see the Server-side rendering guide and the Next.js and TanStack examples.