Database & Migrations

VitNode uses PostgreSQL and Drizzle ORM. Plugins define typed schema files, compile them into SQL migrations, and query tables via c.get("db").

Quick startCopy Anchor Link

1. Define the SchemaCopy Anchor Link

Create a database model in plugins/{plugin}/src/database/{table}.ts:

import { camelCase } from "drizzle-orm/pg-core"

export const reactions = camelCase.table.withRLS("reactions", (t) => ({
  id: t.serial().primaryKey(),
  itemId: t.integer().notNull(),
  emoji: t.varchar({ length: 32 }).notNull(),
  createdAt: t.timestamp().notNull().defaultNow(),
}))

2. Query in an API RouteCopy Anchor Link

Access the typed Drizzle instance using c.get("db"):

import { buildRoute } from "@/api/builders"
import { reactions } from "@/database/reactions"

export const getReactionsRoute = buildRoute({
  route: {
    method: "get",
    path: "/",
    responses: { 200: { description: "List of reactions" } },
  },
  handler: async (c) => {
    const rows = await c.get("db").select().from(reactions)
    return c.json(rows)
  },
})

3. Build & Apply MigrationsCopy Anchor Link

bun run build:plugins && bun run db:migrate

build:plugins compiles your TypeScript models and exports, and db:migrate updates PostgreSQL.

Migration CommandsCopy Anchor Link

Run these with the VitNode CLI in the app that owns the database:

Connection poolCopy Anchor Link

VitNode talks to Postgres through postgres.js, and the default config hands it a bare connection string:

dbProvider: drizzle({
  connection: POSTGRES_URL,
  relations: coreRelations,
}),

That means postgres.js defaults: up to 10 connections per process, idle connections kept forever (idle_timeout: 0), and automatic prepared statements (prepare: true). Perfectly fine on your laptop. In production you probably want to pick the numbers yourself.

Swap the string for an object - url plus any postgres.js option:

Long-running serverCopy Anchor Link

A Node server, a Docker container, a VPS. One process lives for days and handles plenty of requests at once, so give it room:

dbProvider: drizzle({
  connection: POSTGRES_URL, 
  connection: { url: POSTGRES_URL, max: 20, idle_timeout: 20 }, 
  relations: coreRelations,
}),

Sizing rule of thumb: every process opens its own pool, and Postgres has a hard ceiling (SHOW max_connections; - often 100). So:

max ≈ (max_connections − headroom) ÷ number of processes

Keep 10-20 connections of headroom for migrations, db:studio, backups and you poking around in psql. With max_connections = 100, 4 instances and 20 spare, that is max: 20 each. Scale out to 8 instances and the same math says max: 10 - forget to redo it and the fifth instance greets you with too many connections.

ServerlessCopy Anchor Link

On Vercel, Netlify and friends every concurrent request may be its own process, so max × processes grows faster than you would like. Use your provider's pooled connection string, or keep each process to a single connection with max: 1. The Vercel guide has the full setup.

Behind a transaction poolerCopy Anchor Link

dbProvider: drizzle({
  connection: { url: POSTGRES_URL, prepare: false },
  relations: coreRelations,
}),

Direct connections and session-mode poolers keep prepare: true - it is the faster option. VitNode's own locks are transaction-scoped (pg_advisory_xact_lock), so they work behind either mode.

Learn MoreCopy Anchor Link