Database

EffectStream uses a SQL Database and tooling to support dApps:

• Define custom tables and migrations.
• Define at what block number migrations get applied.
• Write custom SQL queries, and they get compiled to typescript.

Tables & Migrations

1. Create your SQL files at: /backend/database/src/migrations/*.sql

CREATE TABLE my_table (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  counter INTEGER NOT NULL DEFAULT 0
);

2. Define the order they will executed at: /backend/database/src/migration-order.ts

import type { DBMigrations } from "@effectstream/runtime";
import firstSql from "./migrations/first.sql" with { type: "text" };
import secondSql from "./migrations/second.sql" with { type: "text" };
import thirdSql from "./migrations/third.sql" with { type: "text" };
import fourthSql from "./migrations/fourth.sql" with { type: "text" };

export const migrationTable: DBMigrations[] = [
  {
    name: "initial-database",
    sql: firstSql,
  },
  {
    versionDependency: "0.3.10",
    name: "add-rows",
    sql: secondSql,
  },
  {
    blockHeight: 2,
    name: "add-indexes",
    sql: thirdSql,
  },
  {
    blockHeight: 3,
    versionDependency: "0.3.20",
    name: "further-changes",
    sql: fourthSql,
  },
];

You can define the "versionDependency" field to enforce that that version or greater of EffectStream will be running when the migration is applied.

"blockHeight" field allows to define in what exact blockHeight the migration will be applied. 1 is default.

TS type-safe SQL Queries

And uses PGTyped to convert SQL into Typescript functions.

To convert the SQL for your project:
bun run --cwd packages/database pgtyped:update

• SQL Files are located at /packages/backend/database/src/sql/*.sql

This uses PGTyped format, where "@name" will be used for the Typescript name.

/* @name myQuery */
SELECT * from my_table;

Now you can use the type-safe query in your TS Code

...
const data = await myQuery.run(undefined, dbConnection);
console.log(data);
...

Primitive Tables

If you have primitives as ERC20, ERC721, ERC1155 for example - these will automatically aggregate the data for you and expose them in tables you can query.

For example ERC20's primitives will create a table called:
erc20_balances_view_<YOUR_PRIMITIVE_NAME>
This table will contain rows with wallet address, token count.

address	wallet
0xabcd	100
0x1111	0
0xdead	590
0xbeaf	201

Table naming

The <YOUR_PRIMITIVE_NAME> suffix is the primitive's configured name, lowercased, with every character other than a letter, a digit, or _ removed — because the name becomes part of a SQL identifier. So a primitive named Midnight-TokenMint publishes its table as midnight_token_mint_view_midnighttokenmint.

One table is created per configured primitive instance, not per token, address, or event: the entities the primitive tracks are the rows.

Midnight token mints

The PrimitiveTypeMidnightTokenMint primitive creates midnight_token_mint_view_<YOUR_PRIMITIVE_NAME>, the registry that maps a wallet-visible token id ("color") back to the contract that minted it — the mapping a wallet cannot give you. One row per (token_type, kind), where kind is shielded or unshielded:

token_type	kind	contract_address	domain_sep	total_minted	tx_hash	block_height
cd12…	shielded	0200ab…	ab34…	1000	ef56…	42
9f77…	unshielded	0200ab…	7c81…	250	ef56…	42

total_minted accumulates across every mint of that token, while tx_hash and block_height keep the provenance of the first mint. Set persist: false on the primitive to skip creating the table on a fresh database.

More info in available in Chains