Documentation
Query
Direct SQL

Query the database

This guide explains how to connect to the database (either SQLite or Postgres) and run direct SQL queries.

SQLite

Database files

During local development, Ponder uses SQLite database files located in the .ponder/sqlite directory.

    • public.db
    • ponder_sync.db
    • public.db: Contains a live indexed table for each table in ponder.schema.ts. It's safe to read data from this database during local development and testing.
    • ponder_sync.db: Contains raw blockchain data that has been cached locally.

    Note: SQLite sometimes creates a -shm and -wal file alongside database files. Do not modify or delete them.

    Connect with sqlite3

    To get oriented, connect to the SQLite database using sqlite3 (installation guide) and run a few test queries. For the queries that follow, assume there is a Ponder app running with this ponder.schema.ts.

    Example ponder.schema.ts

    ponder.schema.ts
    import { createSchema } from "@ponder/core";
     
    export default createSchema((p) => ({
      Account: p.createTable({
        id: p.hex(),
        balance: p.bigint(),
        isOwner: p.boolean(),
      }),
      TransferEvent: p.createTable({
        id: p.string(),
        amount: p.bigint(),
        timestamp: p.int(),
        fromId: p.hex().references("Account.id"),
        toId: p.hex().references("Account.id"),
      }),
    }));

    File path

    Connect to the database by passing a file path to sqlite3.

    shell
    # From your project root
    sqlite3 .ponder/sqlite/public.db
     
    # Or, an absolute path
    sqlite3 /workspace/my-app/.ponder/sqlite/public.db

    Once connected, you'll see a sqlite> prompt.

    shell
    SQLite version 3.37.0 2021-12-09 01:34:53
    Enter ".help" for usage hints.
    sqlite> 

    Display tables

    Use the .tables command to list all tables in the database.

    sqlite3
    .tables
    sqlite3 (result)
    sqlite> .tables
    Account        TransferEvent

    Display schema

    Use the .schema command to print CREATE TABLE statements for each table.

    sqlite3
    .schema
    sqlite3 (result)
    sqlite> .schema
    CREATE TABLE IF NOT EXISTS "Account" ("id" blob not null primary key, "balance" varchar(79) not null, "isOwner" integer not null);
    CREATE TABLE IF NOT EXISTS "TransferEvent" ("id" text not null primary key, "amount" varchar(79) not null, "timestamp" integer not null, "fromId" blob not null, "toId" blob not null);

    Select rows

    First, adjust some settings to make sqlite3 results easier to read.

    sqlite3
    .headers ON
    .mode columns

    Select a few TransferEvent rows.

    sqlite3
    SELECT * FROM TransferEvent LIMIT 3;
    sqlite3 (result)
    sqlite> SELECT * FROM TransferEvent LIMIT 3;
    id                                                                        amount                                                                           timestamp   fromId  toId             
    ------------------------------------------------------------------------  -------------------------------------------------------------------------------  ----------  ------  -----------------
    0x3783f4bba84605ffa296db73fcbdb010caf0542b460035d7bed4129db02fd0e2-0x1a5  0000000000000000000000000000000000000000000000000000000010000000000000000000000  1630539461          ???\???<0???%d?? 
    0xd8612316d81529ccc73faf26318594a50cc4dffbbe0553ccf7a14a514f889fff-0xd5   0000000000000000000000000000000000000000000000000000000010000000000000000000000  1630541604          ??ÉhP/w?~?mrrW???R
    0x1605392466b635d4eb1552f66832ade6da40a904b00566502b4ba3bdb08ab6a8-0x1a3  0000000000000000000000000000000000000000000000000000000010000000000000000000000  1630541770          ?7g?ʲo&??<}?x

    Clearly, raw sqlite3 results are not easy on the eyes. The balance column is encoded as VARCHAR(79), and the toId column contains hex values encoded as BLOB. See the column types section for more details.

    Aggregate data

    Select all Account records that have received more than 10 transfers.

    sqlite3
    SELECT *
      FROM Account
      WHERE id IN (
        SELECT toId
          FROM TransferEvent
          GROUP BY toId
          HAVING COUNT(*) > 10
      )
      LIMIT 3;
    sqlite3 (result)
    sqlite> SELECT * FROM Account WHERE id IN ( SELECT toId FROM TransferEvent GROUP BY toId HAVING COUNT(*) > 10 ) LIMIT 3;
    id                balance                                                                          isOwner
    ----------------  -------------------------------------------------------------------------------  -------
    .?Ëź???(?4[W?      0000000000000000000000000000000000000000000000000000000140000000000000000000000  0      
    y?D???ꎇ ?[?>      0000000000000000000000000000000000000000000000000000000290000000000000000000000  0      
    3?ek{3F4oi}??B?   0000000000000000000000000000000000000000000000000000000230000000000000000000000  0      

    Column types

    These are the SQLite data types used by each ponder.schema.ts column type.

    Schema column typeSQLite column typeNotes
    p.string()TEXT
    p.hex()BLOB
    p.int()INTEGER
    p.bigint()VARCHAR(79)Supports EVM uint256 and int256 (custom encoding)
    p.float()REAL
    p.boolean()INTEGER0 is false, 1 is true
    p.json()JSONB

    SQLite bigint encoding

    SQLite does not natively support integers larger than 8 bytes. To safely store and compare large integers (such as 32-byte EVM uint256 values) in SQLite, we designed an encoding that uses VARCHAR(79) and takes advantage of SQLite's native lexicographic sort. Here is the reference implementation used by Ponder internally.

    Indexes

    To create indexes on specific columns, use the p.index() function in ponder.schema.ts Do not manually construct database indexes. Read more.

    Postgres

    Database schema

    By default, Ponder uses the public database schema for indexed data, one table for each table in ponder.schema.ts. To use a different schema, set the schema option in ponder.config.ts.

    ponder.config.ts
    import { createConfig } from "@ponder/core"
     
    export default createConfig({
      database: {
        kind: "postgres",
        schema: "dev", // Defaults to "public"
      },
    });

    Like with SQLite, Ponder also uses the ponder_sync Postgres database schemas for cached RPC data.

    Connect using psql

    To get oriented, connect to the database using psql (installation guide) and run a few test queries. For the queries that follow, assume there is a Ponder app running with this ponder.schema.ts.

    Example ponder.schema.ts

    ponder.schema.ts
    import { createSchema } from "@ponder/core";
     
    export default createSchema((p) => ({
      Account: p.createTable({
        id: p.hex(),
        balance: p.bigint(),
        isOwner: p.boolean(),
      }),
      TransferEvent: p.createTable({
        id: p.string(),
        amount: p.bigint(),
        timestamp: p.int(),
        fromId: p.hex().references("Account.id"),
        toId: p.hex().references("Account.id"),
      }),
    }));

    Connection string

    Connect using the same connection string that your Ponder app uses (the DATABASE_URL environment variable).

    shell
    psql 'postgresql://username:password@localhost:5432/your_database'

    Display tables

    Use the \dt command to list all tables in the public schema.

    psql
    \dt
    psql (result)
    username=# \dt
                  List of relations
     Schema |     Name      | Type  |  Owner    
    --------+---------------+-------+----------
     public | Account       | table | username
     public | TransferEvent | table | username
    (4 rows)

    If you're using a schema other than public, you'll need to include a pattern:

    psql
    \dt another_schema.*

    Select rows

    Select a few Account rows.

    psql
    SELECT * FROM "Account" LIMIT 5;
    psql (result)
    username=# SELECT * FROM "Account" LIMIT 5;
                         id                     |         balance         | isOwner 
    --------------------------------------------+-------------------------+---------
     \xf73fe15cfb88ea3c7f301f16ade3c02564aca407 | 10000000000000000000000 |       0
     \xb0659bc97ed61b37d6b140f3e12a41d471781714 | 20000000000000000000000 |       0
     \x52932f5b2767d917c3134140168f2176c94e8b2c | 10000000000000000000000 |       0
     \xfb7ca75b3ce099120602b5ab7104cff030ee43f8 |                       0 |       0
     \x9ccc6c5a9d25429f55ad9af6363c1c4f16b179ad |  7000000000000000000000 |       0
    (5 rows)

    Note that the id column uses the p.hex() column type in ponder.schema.ts, which corresponds to the Postgres BYTEA data type. See the column types section for more details.

    Aggregate data

    Find the total number of transfers sent to each account.

    psql
    SELECT "toId", COUNT(*) AS transfer_count
      FROM "TransferEvent"
      GROUP BY "toId"
      ORDER BY transfer_count DESC
      LIMIT 5;
    psql (result)
    username=# SELECT "toId", COUNT(*) AS transfer_count FROM "TransferEvent" GROUP BY "toId" ORDER BY transfer_count DESC LIMIT 5;
                        toId                    | transfer_count 
    --------------------------------------------+----------------
     \x5d752f322befb038991579972e912b02f61a3dda |           2342
     \x1337f7970e8399ccbc625647fce58a9dada5aa66 |            313
     \x9726041047644626468922598128349778349982 |            306
     \x27239549dd40e1d60f5b80b0c4196923745b1fd2 |            256
     \x450638daf0caedbdd9f8cb4a41fa1b24788b123e |            238
    (5 rows)

    Column types

    These are the Postgres data types used by each ponder.schema.ts column type.

    Schema column typePostgres column typeNotes
    p.string()TEXT
    p.hex()BYTEA
    p.int()INTEGER
    p.bigint()NUMERIC(78, 0)Supports EVM uint256 and int256
    p.float()FLOAT8/DOUBLE
    p.boolean()INTEGER0 is false, 1 is true
    p.json()JSONB

    Indexes

    To create indexes on specific columns, use the p.index() function in ponder.schema.ts Do not manually construct database indexes. Read more.