Database and migrations

OpenShop uses PostgreSQL through Drizzle and node-postgres.

Connection

DATABASE_URL is required. getDb() throws [openshop] DATABASE_URL not set when it is absent.

Environment variable	Default
PGPOOL_MAX	10
PGPOOL_IDLE_TIMEOUT_MS	30000
PGPOOL_CONNECTION_TIMEOUT_MS	5000

Values are converted with Number(). OpenShop creates one lazy process-local pool and reuses its Drizzle client.

App models

import { defineModel, index, integer, text, uniqueIndex } from 'openshop/schema'

export const reviews = defineModel(
  'reviews',
  {
    title: text('title').notNull(),
    rating: integer('rating').notNull(),
  },
  {
    indexes: (table) => [
      index('reviews_shop_rating_idx').on(table.shop, table.rating),
      uniqueIndex('reviews_shop_title_unique').on(table.shop, table.title),
    ],
  },
)

defineModel() always adds a UUID id primary key with defaultRandom().

Option	Default	Added column
shop	true	Non-null text('shop').
createdAt	true	Time-zone-aware timestamp, defaultNow(), non-null.
updatedAt	true	Time-zone-aware timestamp, defaultNow(), non-null.
indexes	None	Drizzle index or constraint callback.

Set an option to false to omit its column. updatedAt has a creation default; OpenShop does not automatically change it on updates to app-owned rows.

The schema entry point also exports Drizzle column builders, index, uniqueIndex, and query operators such as eq, and, inArray, sql, asc, and desc.

Queries and transactions

Flows receive ctx.db; server modules can import getDb():

import { getDb } from 'openshop'
import { eq } from 'openshop/schema'
import { reviews } from '#models/reviews'

const db = getDb()
const rows = await db.select().from(reviews).where(eq(reviews.shop, shop))

await db.transaction(async (tx) => {
  await tx.insert(reviews).values({ shop, title: 'Fast', rating: 5 })
})

Transactions are normal Drizzle transactions. Do not perform slow network calls inside them: they retain a PostgreSQL connection and may keep locks open.

Client-owned migrations

Projects own their Drizzle migrations in ./drizzle. The migration generated by openshop init includes framework tables for installations, flow runs, steps, logs, providers, crons, and MCP.

pnpm exec openshop migrate generate
pnpm exec openshop migrate check
pnpm exec openshop migrate

• migrate generate creates SQL from the current schema in development or CI.
• migrate check checks committed migration history.
• migrate applies committed SQL only.

openshop migrate does not run generation tooling. openshop start and openshop worker never generate or apply migrations. Commit generated SQL and apply it before starting production processes.