SQL over HTTP

Ponder natively supports SQL queries over HTTP using the @ponder/client and @ponder/react packages.

The SQL over HTTP is a more powerful alternative to GraphQL that offers zero-codegen type inference, live queries, and the flexibility of SQL directly in your client code.

Setup

import { db } from "ponder:api";
import schema from "ponder:schema";
import { Hono } from "hono";
import { client } from "ponder"; 
 
const app = new Hono();
 
app.use("/sql/*", client({ db, schema })); 
 
export default app;

pnpm add @ponder/client

import { createClient } from "@ponder/client";
 
const client = createClient("http://localhost:42069/sql");

import { createClient } from "@ponder/client";
import * as schema from "../../ponder/ponder.schema"; 
 
const client = createClient("http://localhost:42069/sql");
 
const result = await client.db.select().from(schema.account);
//   ^? { address: `0x${string}`; balance: bigint; }[]

Setup React

The @ponder/react package provides React hooks for querying your Ponder database, powered by @ponder/client. This package wraps TanStack Query, a popular library for managing async state in React.

pnpm add @ponder/react @ponder/client @tanstack/react-query

import { PonderProvider } from "@ponder/react";
import { client } from "../lib/ponder";
 
function App() {
  return (
    <PonderProvider client={client}>
      {/** ... */}
    </PonderProvider>
  );
}

import { PonderProvider } from "@ponder/react";
import { QueryClient, QueryClientProvider } from "@tanstack/react-query"; 
import { client } from "../lib/ponder";
 
const queryClient = new QueryClient(); 
 
function App() {
  return (
    <PonderProvider client={client}>
      <QueryClientProvider client={queryClient}> {}
        {/** ... */}
      </QueryClientProvider> {}
    </PonderProvider>
  );
}

Querying

The SQL over HTTP can be used to read indexed data from your Ponder database.

It's different than direct SQL in two key areas:

• Schema names are automatically applied on the server using Postgres's search_path. Table names should be unqualified.
• Only a subset of SQL statements are allowed, with limited resources. Read more about security.

import { desc } from "@ponder/client";
import * as schema from "../../ponder/ponder.schema";
 
const result = await client.db
  .select()
  .from(schema.account)
  .orderBy(desc(schema.account.balance));

Live queries

Live queries are automatically updated when the underlying data changes. Ponder uses Server-Sent Events to stream updates to the client every time a new block is processed.

import { desc } from "@ponder/client";
import * as schema from "../../ponder/ponder.schema";
 
await client.live(
  (db) => 
    db.select().from(schema.account).orderBy(desc(schema.account.balance)),
  (result) => {
    // ... handle result
  },
  (error) => {
    // ... handle error
  },
);

Untyped queries

It's also possible to query the Ponder database without relying on importing ponder.schema.ts using the sql operator.

import { sql } from "@ponder/client";
 
const result = await client.db.execute(sql`SELECT * FROM account limit 10;`);

Pagination

The SQL over HTTP supports both limit/offset and cursor-based pagination patterns to handle large result sets efficiently.

import { desc } from "@ponder/client";
import * as schema from "../../ponder/ponder.schema";
 
const count = await client.db.$count(schema.account);
 
const result = await client.db
  .select()
  .from(schema.account)
  .orderBy(desc(schema.account.balance))
  .limit(100)
  .offset(500);

Relational query builder

The SQL over HTTP also supports the Drizzle query builder with some additional setup required.

The createClient function accepts a schema option to enable the Drizzle query builder.

import { createClient } from "@ponder/client";
import * as schema from "../../ponder/ponder.schema";
 
const client = createClient("http://localhost:42069/sql", { schema });
 
const result = await client.db.query.account.findMany({
  orderBy: (account, { desc }) => desc(account.balance),
});

React

In addition to the step above, you need to "register" your schema globally with TypeScript using declaration merging.

import * as schema from "../../ponder/ponder.schema";
 
declare module "@ponder/react" { 
  interface Register { 
    schema: typeof schema; 
  } 
} 

usePonderQuery is now type-safe without the need to import schema in every query.

import { usePonderQuery } from "@ponder/react";
 
const query = usePonderQuery({
  queryFn: (db) =>
    db.query.account.findMany({
      orderBy: (account, { desc }) => desc(account.balance),
    }),
});

Examples

• Basic usage (@ponder/client only)
• Usage with Next.js (@ponder/client and @ponder/react)
• Usage with useInfiniteQuery

For this example, we'll use the following schema.

import { onchainTable } from "ponder";
 
export const person = onchainTable("person", (t) => ({
  id: t.integer().primaryKey(),
  name: t.text().notNull(),
  age: t.integer(),
}));

Get all person records with an age greater than 32.

import { asc, gt } from "@ponder/client";
import { usePonderClient } from "@ponder/react";
import { useInfiniteQuery } from "@tanstack/react-query";
 
const client = usePonderClient();
 
const personQuery = useInfiniteQuery({
  queryKey: ["persons"],
  queryFn: ({ pageParam }) =>
    client.db
      .select()
      .from(schema.person)
      .where(gt(schema.person.age, 32))
      .orderBy(asc(schema.person.id))
      .limit(100)
      .offset(pageParam),
  initialPageParam: 0,
  getNextPageParam: (lastPage, pages) =>
    lastPage.length === 100
      ? undefined
      : pages.length * 100,
});

• Uniswap v4

For this example, we'll use the following schema.

import { index, relations, primaryKey, onchainTable } from "ponder";
 
export const token = onchainTable("token", (t) => ({
  address: t.hex().notNull(),
  chainId: t.integer().notNull(),
  name: t.text().notNull(),
  symbol: t.text().notNull(),
  decimals: t.integer().notNull(),
  creationBlock: t.integer().notNull(),
}),
  (table) => ({
    pk: primaryKey({ columns: [table.address, table.chainId] }),
    addressIndex: index().on(table.address),
    chainIdIndex: index().on(table.chainId),
  })
);
 
export const tokenRelations = relations(token, ({ many }) => ({
  pools: many(pool),
}));
 
export const pool = onchainTable("pool", (t) => ({
  poolId: t.hex().notNull(),
  currency0: t.hex().notNull(),
  currency1: t.hex().notNull(),
  fee: t.integer().notNull(),
  tickSpacing: t.integer().notNull(),
  hooks: t.hex().notNull(),
  chainId: t.integer().notNull(),
  creationBlock: t.integer().notNull(),
}),
  (table) => ({
    pk: primaryKey({ columns: [table.poolId, table.chainId] }),
    poolIdIndex: index().on(table.poolId),
    chainIdIndex: index().on(table.chainId),
  })
);
 
export const poolRelations = relations(pool, ({ many, one }) => ({
  token0: one(token, { fields: [pool.currency0, pool.chainId], references: [token.address, token.chainId] }),
  token1: one(token, { fields: [pool.currency1, pool.chainId], references: [token.address, token.chainId] }),
}));

Get all pool records with a chainId of 1 and a fee of 0 and include the token0 and token1 data.

import { usePonderQuery } from "@ponder/react";
 
const query = usePonderQuery({
  queryFn: (db) =>
    qb.query.pool.findMany({
      where: (pool, { and, eq }) => and(eq(pool.chainId, 1), eq(pool.fee, 0)),
      orderBy: (pool, { desc }) => desc(pool.creationBlock),
      with: {
        token0: true,
        token1: true,
      },
    }),
});

Frequently asked questions

Use schema from a different repo

The @ponder/client package needs the onchainTable objects exported by ponder.schema.ts to properly compile the Drizzle queries client-side.

If the client project is in a different repo from the Ponder project, there are a few other options:

• Import the ponder.schema.ts locally using a monorepo.
• Publish the ponder.schema.ts to npm.
• Copy the ponder.schema.ts into the client project and install ponder to get access to the onchainTable function.
• Forfeit type safety and use untyped queries.

Security

Here are the measures taken by the client middleware to prevent malicious queries & denial-of-service attacks. These measures aim to achieve a similar level of risk as the GraphQL API.

• Read-only: Each query statement runs in a READ ONLY transaction using autocommit.

• Query validator: Each query is parsed using libpg_query and must pass the following checks.

  • The query AST root must be a SELECT statement. Queries containing multiple statements are rejected.
  • The query must only contain allowed AST node types and built-in SQL functions. For example, SELECT, WHERE, and max() are allowed, but DELETE, SET, and pg_advisory_lock() are not. Read more.
  • The query must not reference objects in schemas other than the current schema. Read more.

• Resource limits: The database session uses the following resource limit settings.

  SET work_mem = '512MB';
  SET statement_timeout = '500ms';
  SET lock_timeout = '500ms';