@avelon/postgres

@avelon/postgres is the Postgres database driver for Avelon. It compiles the frozen QueryIR into parameterized SQL, runs it through Bun's SQL client, and maps vendor failures into the framework error taxonomy. Reach for this package when your application needs transactions, deep relation loads, upserts with returning, or an independent Postgres connection beside Supabase.

Installation

bun add @avelon/postgres

Set a connection URL in the environment or pass one when constructing the driver:

export POSTGRES_URL=postgresql://postgres:avelon@127.0.0.1:5432/avelon

Basic Usage

import { createPostgresDatabase } from '@avelon/postgres'
import type { QueryIR } from '@avelon/core'

const db = createPostgresDatabase({
  url: process.env.POSTGRES_URL,
  instance: 'primary',
})

const latest: QueryIR = {
  table: 'posts',
  mode: 'select',
  select: ['id', 'title'],
  where: [{ kind: 'null', column: 'published_at', negated: true }],
  relations: [],
  order: [{ column: 'published_at', direction: 'desc' }],
  limit: 10,
}

const result = await db.execute<{ id: string; title: string }>(latest)

Capabilities

Query Compilation

The driver validates IR shape, rejects unknown public-schema identifiers, normalizes predicates, and compiles positional SQL. Empty AND is true, empty OR is false, empty IN matches nothing unless negated, and a constant-false ward or where short-circuits without a database round trip.

import { compilePostgres, combinedPredicate } from '@avelon/postgres'
import type { QueryIR } from '@avelon/core'

const query: QueryIR = {
  table: 'assay_users',
  mode: 'select',
  select: ['id'],
  where: [{ kind: 'compare', column: 'age', op: '>=', value: 18 }],
  ward: { kind: 'compare', column: 'age', op: '<', value: 30 },
  relations: [],
  order: [],
}

const compiled = compilePostgres(query, combinedPredicate(query))
// compiled.text binds age thresholds as $1 and $2

Transactions

import { createPostgresDatabase } from '@avelon/postgres'

const db = createPostgresDatabase()

await db.transaction(async (tx) => {
  await tx.execute({
    table: 'assay_users',
    mode: 'insert',
    select: [],
    where: [],
    relations: [],
    order: [],
    values: { id: 'u1', email: 'one@example.test', name: 'One', age: 20, nickname: null },
  })
})

Migrations

Migrations are driver-owned SQL pairs registered on the driver instance.

import { createPostgresDatabase } from '@avelon/postgres'

const db = createPostgresDatabase({
  migrations: [
    {
      id: '20260804_create_posts',
      up: [
        'CREATE TABLE posts (id text PRIMARY KEY, title text NOT NULL)',
      ],
      down: ['DROP TABLE posts'],
    },
  ],
})

await db.plan()
await db.apply()
await db.status()

Error Mapping

Vendor SQLSTATE values never leave the driver. Unique violations become Conflict, unknown tables/columns/routines become Invalid, and connection failures become Unavailable. Every other code becomes DriverFault.

Live Conformance

Fixture provisioning is owned by this package. Reset the assay schema, then run the shared database suite against a live Postgres:

export POSTGRES_URL=postgresql://postgres:avelon@127.0.0.1:5432/avelon_test
bun run fixtures:reset
bun test

Method Reference

Testing

Use the shared database conformance suite with a live database. The package ships fixture reset helpers so tests do not rely on hand-maintained schema.

import { databaseSuite } from '@avelon/conformance/suites'
import { createPostgresDatabase } from '@avelon/postgres'

databaseSuite({
  name: 'live postgres',
  create: async () => {
    const driver = createPostgresDatabase()
    await driver.resetFixtures()
    return driver
  },
})

Unit tests cover SQL compilation and error mapping without a network round trip. Live tests require Postgres and fail closed when the database is unreachable.