# Tables \[Define database tables and columns]

Ponder's schema definition API is built on [Drizzle](https://orm.drizzle.team/), a modern TypeScript ORM. To define a table, use the `onchainTable` function and include column definitions.

```ts [ponder.schema.ts]
import { onchainTable } from "ponder";

export const pets = onchainTable("pets", (t) => ({
  name: t.text().primaryKey(),
  age: t.integer().notNull(),
}));
```

Each table *must* be a named export from the `ponder.schema.ts` file. The build step ignores tables that are not exported.

## Column types

The schema definition API supports most PostgreSQL data types – here's a quick reference for the most common options. Read the [Drizzle documentation](https://orm.drizzle.team/docs/column-types/pg) for a complete list.

| name             | description                        | TypeScript type          | SQL data type     |
| :--------------- | :--------------------------------- | :----------------------- | :---------------- |
| `text{:ts}`      | UTF‐8 character sequence           | `string`                 | `TEXT{:sql}`      |
| `integer{:ts}`   | Signed 4‐byte integer              | `number`                 | `INTEGER{:sql}`   |
| `real{:ts}`      | Signed 4-byte floating‐point value | `number`                 | `REAL{:sql}`      |
| `boolean{:ts}`   | `true` or `false`                  | `boolean`                | `BOOLEAN{:sql}`   |
| `timestamp{:ts}` | Date and time value (no time zone) | `Date`                   | `TIMESTAMP{:sql}` |
| `json{:ts}`      | JSON object                        | `any` or [custom](#json) | `JSON{:sql}`      |

Ponder also includes a few extra column types built specifically for EVM indexing.

| name          | description                                  | TypeScript type | SQL data type         |
| :------------ | :------------------------------------------- | :-------------- | :-------------------- |
| `bigint{:ts}` | Large integer (holds `uint256` and `int256`) | `bigint`        | `NUMERIC(78,0){:sql}` |
| `hex{:ts}`    | UTF‐8 character sequence with `0x` prefix    | `0x${string}`   | `TEXT{:sql}`          |

### `bigint`

Use the `bigint` column type to store EVM `uint256` or `int256` values.

```ts [ponder.schema.ts]
import { onchainTable } from "ponder";

export const accounts = onchainTable("accounts", (t) => ({
  address: t.hex().primaryKey(),
  balance: t.bigint().notNull(), // [!code focus]
}));
```

:::info
Ponder's `bigint` type takes precedence over the Drizzle
[`bigint`](https://orm.drizzle.team/docs/column-types/pg#bigint) type, which
is an 8-byte integer (too small for EVM integer values). To
create an 8-byte integer column, use the `int8` alias.
:::

### `hex`

Use the `hex` column type to store EVM `address`, `bytes`, or any other hex-encoded value.

```ts [ponder.schema.ts]
import { onchainTable } from "ponder";

export const accounts = onchainTable("accounts", (t) => ({
  address: t.hex().primaryKey(), // [!code focus]
  balance: t.bigint().notNull(), 
}));
```

## Enums

To define an enum, use the `onchainEnum` function. Then, use the value returned by `onchainEnum` as a column type. Under the hood, `onchainEnum` creates a PostgreSQL [enumerated type](https://www.postgresql.org/docs/current/datatype-enum.html).

```ts [ponder.schema.ts]
import { onchainEnum, onchainTable } from "ponder"; // [!code focus]

export const color = onchainEnum("color", ["ORANGE", "BLACK"]); // [!code focus]

export const cats = onchainTable("cats", (t) => ({
  name: t.text().primaryKey(),
  color: color("color"), // [!code focus]
}));
```

## Arrays

To define an array column, use the `.array(){:ts}` modifier. Arrays are a good fit for small one-dimensional collections. Don't use arrays for [relationships](/docs/0.10/schema/relations) between records.

```ts [ponder.schema.ts]
import { onchainTable } from "ponder";

export const cats = onchainTable("cats", (t) => ({
  name: t.text().primaryKey(),
  vaccinations: t.text().array(), // ["rabies", "distemper", "parvo"] // [!code focus]
}));
```

## Not null

To mark a column as not null, use the `.notNull(){:ts}` modifier. If you attempt to insert a row that does not include a value for a `NOT NULL{:sql}` column, the database will throw an error.

```ts [ponder.schema.ts]
import { onchainTable } from "ponder";

export const cats = onchainTable("cats", (t) => ({
  name: t.text().primaryKey(),
  age: t.integer().notNull(), // [!code focus]
}));
```

## Default value

To set a default value for a column, use the `.default(){:ts}` modifier and pass a string, number, boolean, or `null`.

```ts [ponder.schema.ts]
import { onchainTable } from "ponder";

export const cats = onchainTable("cats", (t) => ({
  name: t.text().primaryKey(),
  livesRemaining: t.integer().default(9), // [!code focus]
}));
```

Alternatively, use the `.$default(){:ts}` modifier to specify a JavaScript function that returns the default value. With this approach, the database driver calls the function to generate a default value for each row dynamically.

```ts [ponder.schema.ts]
import { onchainTable } from "ponder";
import { generateId } from "../utils"; // [!code focus]

export const cats = onchainTable("cats", (t) => ({
  name: t.text().primaryKey(),
  age: t.integer().$default(() => generateId()), // [!code focus]
}));
```

## Primary key

Every table **must** have a primary key. To define a primary key on a single column, use the `.primaryKey(){:ts}` modifier.

```ts [ponder.schema.ts]
import { onchainTable } from "ponder";

export const tokens = onchainTable("tokens", (t) => ({
  id: t.bigint().primaryKey(), // [!code focus]
}));
```

### Composite primary key

To create a composite primary key, use the `primaryKey()` function exported by `ponder`. Each column that forms the primary key must be not null. [Read more](https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-PRIMARY-KEYS) about composite primary keys.

```ts [ponder.schema.ts]
import { onchainTable, primaryKey } from "ponder"; // [!code focus]

export const poolStates = onchainTable(
  "pool_states",
  (t) => ({
    poolId: t.bigint().notNull(),
    address: t.hex().notNull(),
    balance: t.bigint().notNull(),
  }),
  (table) => ({ // [!code focus]
    pk: primaryKey({ columns: [table.poolId, table.address] }), // [!code focus]
  }) // [!code focus]
);
```

## Indexes

To create a database index, use the `index(){:ts}` function. This example defines B-tree indexes on the `persons.name` column to support search queries, and on the `dogs.ownerId` column to support the `persons.dogs` relational query.

```ts [ponder.schema.ts]
import { onchainTable, relations, index } from "ponder";

export const persons = onchainTable(
  "persons",
  (t) => ({
    id: t.text().primaryKey(),
    name: t.text(),
  }),
  (table) => ({
    nameIdx: index().on(table.name),
  })
);

export const personsRelations = relations(persons, ({ many }) => ({
  dogs: many(dogs),
}));

export const dogs = onchainTable(
  "dogs",
  (t) => ({
    id: t.text().primaryKey(),
    ownerId: t.text().notNull(),
  }),
  (table) => ({
    ownerIdx: index().on(table.ownerId),
  })
);

export const dogsRelations = relations(dogs, ({ one }) => ({
  owner: one(persons, { fields: [dogs.ownerId], references: [persons.id] }),
}));
```

The `index(){:ts}` function supports specifying multiple columns, ordering, and custom index types like GIN and GIST. Read more in the [Drizzle](https://orm.drizzle.team/docs/indexes-constraints#indexes) and [PostgreSQL](https://www.postgresql.org/docs/current/indexes.html) documentation.

:::info
To improve performance, the indexing engine creates database indexes *after* historical
indexing is complete, just before the app becomes healthy.
:::

## Best practices

### Primary keys

Select a primary key that matches the access pattern of your indexing logic. If a table has two or more columns that together form a unique identifier for a row, use a composite primary key – don't use a concatenated string.

```ts [ponder.schema.ts]
import { onchainTable, primaryKey } from "ponder";

// ❌ Don't concatenate strings to form a primary key  [!code focus]
export const allowances = onchainTable("allowances", (t) => ({
  id: t.string().primaryKey(), // `${owner}_${spender}` // [!code focus]
  owner: t.hex(),
  spender: t.hex(),
  amount: t.bigint(),
}));

// ✅ Use a composite primary key  // [!code focus]
export const allowances = onchainTable(
  "allowances",
  (t) => ({
    owner: t.hex(),
    spender: t.hex(),
    amount: t.bigint(),
  }),
  (table) => ({ pk: primaryKey({ columns: [table.owner, table.spender] }) })  // [!code focus]
);
```

### Timestamps

Use the `bigint` column type to store block timestamps using their EVM-native Unix timestamp representation. This maintains consistency with Viem's approach, and avoids error-prone timezone manipulation code.

```ts [ponder.schema.ts]
import { onchainTable } from "ponder";

export const events = onchainTable("events", (t) => ({
  id: t.text().primaryKey(),
  timestamp: t.bigint(), // Unix timestamp in seconds [!code focus]
}));
```

If you strongly prefer working with JavaScript `Date` objects, you can also use the `timestamp` column type, but we recommend doing this conversion in the view layer.

```ts [ponder.schema.ts]
import { onchainTable } from "ponder";

export const events = onchainTable("events", (t) => ({
  id: t.text().primaryKey(),
  timestamp: t.timestamp(), // JavaScript Date object [!code focus]
}));
```

### Custom types

Use the `.$type()` modifier to customize the TypeScript type for a column. Note that the `.$type()` modifier does not validate data at runtime or in the database, it only enforces a TypeScript type.

```ts [ponder.schema.ts]
import { onchainTable } from "ponder";

export const tokens = onchainTable("tokens", (t) => ({
  id: t.bigint().primaryKey(),
  metadata: t.json().$type<{ name: string; symbol: string; decimals: number }>(), // [!code focus]
}));
```

### `camelCase` vs `snake_case`

Use `camelCase` for TypeScript names and `snake_case` for SQL names. This guideline applies to all database objects and properties, including tables, columns, relations, and indexes.

```ts [ponder.schema.ts]
import { onchainTable } from "ponder";

export const registrationEvents = onchainTable(
  "registration_events", // Use snake_case for the SQL table name
  (t) => ({
    createdAt: t.bigint(), // Drizzle automatically converts this to `created_at`
    invitedBy: t.text("invited_by"), // Avoid manual case conversion for columns
    // ...
  })
);
```

## Examples

### ERC20

Here's a schema for a simple ERC20 app.

```ts [ponder.schema.ts]
import { index, onchainTable, primaryKey } from "ponder";

export const account = onchainTable("account", (t) => ({
  address: t.hex().primaryKey(),
  balance: t.bigint().notNull(),
  isOwner: t.boolean().notNull(),
}));

export const allowance = onchainTable(
  "allowance",
  (t) => ({
    owner: t.hex(),
    spender: t.hex(),
    amount: t.bigint().notNull(),
  }),
  (table) => ({
    pk: primaryKey({ columns: [table.owner, table.spender] }),
  })
);

export const transferEvent = onchainTable(
  "transfer_event",
  (t) => ({
    id: t.text().primaryKey(),
    amount: t.bigint().notNull(),
    timestamp: t.integer().notNull(),
    from: t.hex().notNull(),
    to: t.hex().notNull(),
  }),
  (table) => ({
    fromIdx: index().on(table.from),
  })
);

export const approvalEvent = onchainTable("approval_event", (t) => ({
  id: t.text().primaryKey(),
  amount: t.bigint().notNull(),
  timestamp: t.integer().notNull(),
  owner: t.hex().notNull(),
  spender: t.hex().notNull(),
}));
```