Direct SQL

It's often useful to query Ponder tables directly in Postgres.

Limitations

As a rule of thumb, the only supported operation is SELECT queries against Ponder tables.

• Direct SQL queries are read-only. Direct SQL queries should not insert, update, or delete rows from Ponder tables.
• Direct SQL with zero-downtime deployments requires additional setup. Read more about the views pattern in the production guide.
• Triggers on Ponder tables are not supported.

Database schema

Ponder uses database schemas to isolate deployments. Read more in the self-hosting guide.

Direct SQL queries should target the database schema corresponding to a specific deployment. Or, if your project uses the views pattern (recommended), queries should target the views schema.

Here are a few strategies to configure the database schema.

Manual / hard-coded

The easiest way to target a specific database schema is to specify it manually in each SQL query. This works well for projects using the views pattern, because the schema changes less frequently.

SELECT *
FROM my_ponder_project.accounts -- Database schema specified
ORDER BY created_at ASC
LIMIT 100;

Search path

Another approach is to write direct SQL queries using unqualified table names (no database schema specified), then update the search_path setting to include the target database schema.

SET search_path TO my_ponder_project, "$user", public;

Drizzle (setDatabaseSchema)

If you're writing direct SQL queries using Drizzle (e.g. from a downstream Node.js application), you can use the setDatabaseSchema utility function to target a specific database schema.

This function mutates a Drizzle schema such that any queries built using that schema will target the specified database schema. Read more in the Drizzle documentation.

import { drizzle } from "drizzle-orm/node-postgres";
import * as schema_ from "../../ponder/ponder.schema"; 
 
const schema = setDatabaseSchema(schema_, "target_schema"); 
 
const db = drizzle(process.env.DATABASE_URL, { schema, casing: "snake_case" });
 
const oldAccounts = await db
  .select()
  .from(schema.accounts)
  .orderBy(asc(schema.accounts.createdAt))
  .limit(100);