SQL client queries

The @ponder/client package provides an SQL client for querying a Ponder app over HTTP, with end-to-end type inference and live updates. It's an alternative to the GraphQL API.

Enable on the server

To enable client queries, register the client middleware in your API function file.

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

Read more about how the client middleware protects against malicious queries and denial-of-service attacks.

@ponder/client

The @ponder/client package works in any JavaScript environment, including the browser, server-side scripts, and both client and server code from web frameworks like Next.js. If you're using a React framework, use the @ponder/react package instead.

Guide

pnpm add @ponder/client

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

import { createClient } from "@ponder/client";
import * as schema from "../../ponder/ponder.schema";
 
const client = createClient("https://.../sql", { schema });
 
const result = await client.db.select().from(schema.account).limit(10);

API Reference

client.db

This method uses server side query validation and a database session with strict query limits. It provides a Drizzle query builder, similar to API functions.

import { createClient } from "@ponder/client";
import * as schema from "../../ponder/ponder.schema";
 
const client = createClient("https://.../sql", { schema });
 
const result = await client.db.select().from(schema.account).limit(10);

client.live

Subscribe to live updates.

This method intiates a HTTP connection with the server using server-sent events (SSE). The server notifies the client whenever a new block gets indexed. If a query result is no longer valid, the client immediately refetches it to receive the latest result. This approach achieves low-latency updates with minimal network traffic.

To avoid browser quotas, each client instance uses at most one SSE connection at a time.

import { createClient } from "@ponder/client";
import * as schema from "../../ponder/ponder.schema";
 
const client = createClient("https://.../sql", { schema });
 
const { unsubscribe } = client.live(
  (db) => db.select().from(schema.account),
  (result) => {
    // ...
  },
  (error) => {
    // ...
  }
);

client.getStatus

Get the indexing progress of each chain.

import { createClient } from "@ponder/client";
import * as schema from "../../ponder/ponder.schema";
 
const client = createClient("https://.../sql", { schema });
 
const status = await client.getStatus();

Query from React

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

import { usePonderQuery } from "@ponder/react";
 
export function Deposits() {
  const { data, isError, isPending } = usePonderQuery({
    queryFn: (db) => db.select().from(schema.depositEvent).limit(10),
  });
 
  // ...
}

Guide

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

import { createClient } from "@ponder/client";
import * as schema from "../../ponder/ponder.schema";
 
const client = createClient("http://localhost:42069/sql", { schema });
 
export { client, schema };

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

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

import { usePonderQuery } from "@ponder/react";
import { schema } from "../lib/ponder";
 
export function Deposits() {
  const { data, isError, isPending } = usePonderQuery({
    queryFn: (db) =>
      db.select()
        .from(schema.depositEvent)
        .orderBy(schema.depositEvent.timestamp)
        .limit(10),
  });
 
  if (isPending) return <div>Loading deposits</div>;
  if (isError) return <div>Error fetching deposits</div>;
  return <div>Deposits: {data}</div>;
}

API Reference

usePonderQuery

Hook for querying a Ponder app with live updates.

import { usePonderQuery } from "@ponder/react";
 
const { data } = usePonderQuery({
  queryFn: (db) =>
    db
      .select()
      .from(schema.depositEvent)
      .orderBy(schema.depositEvent.timestamp)
      .limit(10),
});

usePonderStatus

Hook for querying the indexing status of each chain.

import { usePonderStatus } from "@ponder/react";
 
const { data } = usePonderStatus();

getPonderQueryOptions

Helper function to build the Tanstack queryFn and queryKey for a SQL query.

import { getPonderQueryOptions } from "@ponder/react";
 
const { queryFn, queryKey } = getPonderQueryOptions(client, (db) =>
  db.select().from(schema.depositEvent).limit(10)
);

PonderProvider

React Context Provider for @ponder/react.

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

Example projects

These example apps demonstrate how to use @ponder/client and @ponder/react.

• Basic
• Next.js

Security

Here are the measures taken by the client middleware to prevent malicious queries & denial-of-service attacks.

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

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

  • The root of the AST must be a SELECT statement. Queries containing multiple statements are rejected.
  • The query must only contain allowlisted AST nodes 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 contain references to 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';

Together, these measures aim to achieve a similar level of risk as the GraphQL API.