Views Beta
Views are declarative SQL tables that auto-index blockchain data. Define a schema, write event handlers, deploy, and query — like a materialized view over the chain.
Install with bun add @secondlayer/views.
Getting started
A view definition has three parts: sources (what events to listen for), a schema (what tables to create), and handlers (how to process each event into rows).
import { defineView } from "@secondlayer/views"
export default defineView({
name: "token-transfers",
version: 1,
sources: [
{ type: "stx-transfer" },
],
schema: {
transfers: {
columns: {
sender: { type: "principal", indexed: true },
recipient: { type: "principal", indexed: true },
amount: { type: "uint" },
memo: { type: "text", nullable: true },
},
},
},
handlers: {
"*": async (event, ctx) => {
await ctx.insert("transfers", {
sender: event.tx.sender,
recipient: event.data.recipient,
amount: event.data.amount,
memo: event.data.memo,
})
},
},
})Schema
Each view gets its own PostgreSQL schema (view_<name>). Tables are defined declaratively with typed columns. System columns are added automatically: _id, _blockHeight, _txId, _createdAt.
schema: {
balances: {
columns: {
address: { type: "principal", indexed: true },
token: { type: "text", indexed: true },
amount: { type: "uint" },
},
uniqueKeys: [["address", "token"]], // enables upsert
indexes: [["token", "amount"]], // composite index
},
}Handlers
Handlers process events into table rows. Each handler receives the event and a context object with write and read operations. Use "*" as a catch-all or match specific source keys.
handlers: {
"*": async (event, ctx) => {
// Write operations (batched, flushed atomically):
await ctx.insert("transfers", { ... })
await ctx.upsert("balances", { ... }) // requires uniqueKeys
await ctx.update("balances", { amount: 0 }, { address: "SP..." })
await ctx.delete("balances", { address: "SP..." })
// Read operations (immediate):
const row = await ctx.findOne("balances", { address: "SP..." })
const rows = await ctx.findMany("balances", { token: "usda" })
// Block/tx metadata:
ctx.blockHeight // current block
ctx.txId // current transaction
ctx.timestamp // block timestamp
ctx.sender // tx sender
},
}Querying
Once deployed, query views through the SDK or CLI. The query API supports filtering, comparison operators, ordering, pagination, and field selection.
// Via SDK
const { data, meta } = await sl.views.queryTable(
"token-transfers",
"transfers",
{
sort: "_block_height",
order: "desc",
limit: 25,
offset: 0,
filters: { sender: "SP1234..." },
}
)
// Comparison operators via dot notation
const { data } = await sl.views.queryTable(
"token-transfers",
"transfers",
{ filters: { "amount.gte": "1000000" } }
)
// Get row count
const { count } = await sl.views.queryTableCount(
"token-transfers",
"transfers",
{ filters: { sender: "SP1234..." } }
)
// Via CLI
sl views query token-transfers transfers --sort _block_height --order desc --limit 25
sl views query token-transfers transfers --filter sender=SP1234... --countTyped client
The SDK can infer TypeScript types from your view definition, giving you typed queries with autocompletion for table names, column names, and filter operators.
import { getView } from "@secondlayer/sdk"
import myView from "./views/token-transfers"
const client = getView(myView, { apiKey: "sk-sl_..." })
// Fully typed — table names, column names, where operators
const rows = await client.transfers.findMany({
where: { sender: { eq: "SP1234..." } },
orderBy: { _blockHeight: "desc" },
limit: 25,
})
const total = await client.transfers.count({
sender: { eq: "SP1234..." },
})
// Or via the SecondLayer instance
const sl = new SecondLayer({ apiKey: "sk-sl_..." })
const typed = sl.views.typed(myView)
const rows = await typed.transfers.findMany({ ... })Search
Enable full-text search on any text column with the search: true flag. This creates a PostgreSQL trigram index (pg_trgm) for fast fuzzy matching.
schema: {
contracts: {
columns: {
name: { type: "text", search: true },
deployer: { type: "principal", indexed: true },
},
},
}
// Query with search via REST API
const { data } = await sl.views.queryTable("contracts", "contracts", {
search: "token",
})Deploy
Deploy views via the CLI. The CLI bundles your handler code with esbuild and posts it to the API. Schema changes are diffed automatically — additive changes are applied in place, breaking changes require a reindex.
# Deploy to Second Layer
sl views deploy views/token-transfers.ts
# Dev mode — watches for changes, auto-redeploys
sl views dev views/token-transfers.ts
# Force reindex (drops and recreates schema)
sl views reindex token-transfers
# Reindex from a specific block range
sl views reindex token-transfers --from 150000 --to 160000
# Scaffold a view from a deployed contract's ABI
sl views scaffold SP1234...::my-contract --output views/my-contract.ts