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 type	SQLite column type	Notes
`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()`	`INTEGER`	`0` 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 type	Postgres column type	Notes
`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()`	`INTEGER`	`0` 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.

GraphQL Deploy

Query the database

SQLite

Database files

Connect with `sqlite3`

File path

Display tables

Display schema

Select rows

Aggregate data

Column types

SQLite bigint encoding

Indexes

Postgres

Database schema

Connect using `psql`

Connection string

Display tables

Select rows

Aggregate data

Column types

Indexes

Resources

More

Connect

Query the database

SQLite

Database files

Connect with sqlite3

File path

Display tables

Display schema

Select rows

Aggregate data

Column types

SQLite bigint encoding

Indexes

Postgres

Database schema

Connect using psql

Connection string

Display tables

Select rows

Aggregate data

Column types

Indexes

Connect with `sqlite3`

Connect using `psql`