@avelonjs/postgres

@avelonjs/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 @avelonjs/postgres

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

export POSTGRES_URL=postgresql://postgres:[email protected]:5432/avelon

Basic Usage

import { createPostgresDatabase } from '@avelonjs/postgres'
import type { QueryIR } from '@avelonjs/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

| Capability | Value | Notes | | ------------------ | ------- | --------------------------------------------------------------------- | | transactions | true | Interactive transactions through db.transaction() | | rowSecurity | false | Ward-to-RLS compilation belongs to @avelonjs/supabase | | maxRelationDepth | 8 | Application-side nested loads; measured against nested assay fixtures | | fullTextSearch | false | No portable search() surface in v1 | | upsert | true | Requires explicit conflict target and update list | | returning | true | Write queries may project rows | | windowFunctions | true | Informational; available through raw() | | jsonOperators | true | Informational; available through raw() |

Node Without Bun

The query IR algorithm, migrations, schema checks, and error mapping live behind the @avelonjs/postgres/sql subpath, whose import graph contains no bun specifier. The package root stays on Bun's SQL client. A driver on another runtime supplies its own client through SqlRunner and SqlBatchRunner; @avelonjs/neon uses this to run on Node.

import { executeQueryIR, loadSchemaCache, type SqlRunner } from '@avelonjs/postgres/sql'
import type { QueryIR } from '@avelonjs/core'

declare const vendor: {
  query(
    text: string,
    params: unknown[],
  ): Promise<{ rows: Record<string, unknown>[]; rowCount: number }>
}

const runner: SqlRunner = {
  unsafe: async (text, parameters) => {
    const result = await vendor.query(text, parameters === undefined ? [] : [...parameters])
    return { rows: result.rows, count: result.rowCount }
  },
}

declare const query: QueryIR
const schema = await loadSchemaCache(runner)
const result = await executeQueryIR(runner, schema, query, 8, () => undefined)

SqlBatchRunner adds batch(), which applies a fully known statement list atomically. Migrations use it so every statement of one migration, plus its history row, reaches the database in a single transaction.

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. On insert and upsert a ward the values row does not satisfy raises Invalid instead of returning an empty result.

import { compilePostgres, combinedPredicate } from '@avelonjs/postgres'
import type { QueryIR } from '@avelonjs/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 '@avelonjs/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: '[email protected]', name: 'One', age: 20, nickname: null },
  })
})

Migrations

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

import { createPostgresDatabase } from '@avelonjs/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.

| SQLSTATE | Framework error | Meaning | | -------- | --------------- | ------------------ | | 23505 | Conflict | unique_violation | | 42P01 | Invalid | undefined_table | | 42703 | Invalid | undefined_column | | 42883 | Invalid | undefined_function | | 08006 | Unavailable | connection_failure |

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:[email protected]:5432/avelon_test
bun run fixtures:reset
bun test

Method Reference

| Method | Signature | Description | | -------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------- | | createPostgresDatabase | (options?: PostgresDatabaseOptions) => PostgresDatabase | Constructs a driver from options or POSTGRES_URL / DATABASE_URL. | | postgresDatabaseCapabilities | { transactions: true, rowSecurity: false, maxRelationDepth: 8, fullTextSearch: false, upsert: true, returning: true, windowFunctions: true, jsonOperators: true } | Exact capability declaration for the Postgres driver. | | PostgresDatabaseOptions | interface | Connection URL, instance name, and optional migrations. | | PostgresDatabase.execute | (query: QueryIR) => Promise<QueryResult> | Validates, compiles, and executes one query IR operation. | | PostgresDatabase.rpc | (routine: string, args: Readonly<Record<string, unknown>>) => Promise<T> | Invokes a Postgres routine; missing routines raise Invalid. | | PostgresDatabase.transaction | (callback: (tx: DatabaseTransaction) => Promise<T>) => Promise<T> | Runs the callback atomically and returns its result. | | PostgresDatabase.plan | () => Promise<MigrationPlan> | Returns pending migration identifiers and SQL steps. | | PostgresDatabase.apply | () => Promise<readonly MigrationStatus[]> | Applies pending migrations inside transactions. | | PostgresDatabase.rollback | (steps?: number) => Promise<readonly MigrationStatus[]> | Rolls back the newest applied migration batches. | | PostgresDatabase.status | () => Promise<readonly MigrationStatus[]> | Lists applied and pending migration states. | | PostgresDatabase.raw | () => SQL | Returns the Bun SQL client at the vendor boundary. | | PostgresDatabase.resetFixtures | () => Promise<void> | Recreates assay tables and assay_echo for live conformance. | | PostgresDatabase.close | () => Promise<void> | Closes the underlying SQL client pool. | | compilePostgres | (ir: QueryIR, predicate?: Predicate) => CompiledSql | Compiles IR to parameterized SQL without executing it. | | compileSqlPredicate | (predicate: Predicate, bind: (value: unknown) => string) => string | Compiles a normalized predicate to a SQL boolean expression. | | CompiledSql | interface | Parameterized text plus positional parameters. | | normalizePredicate | (predicate: Predicate) => Predicate | Applies empty-list and constant identities. | | combinedPredicate | (ir: Pick<QueryIR, 'where' \| 'ward'>) => Predicate | ANDs where and ward, then normalizes. | | mapPostgresError | (error: unknown, operation: string) => never | Maps vendor failures into framework errors. | | POSTGRES_ERROR_MAP | readonly { sqlstate, framework, meaning }[] | SQLSTATE values this driver maps into the taxonomy. | | validateQueryIR | (ir: QueryIR, maxRelationDepth: number) => void | Rejects malformed IR before compilation. | | assertIdentifier | (value: unknown, path: string) => asserts value is string | Rejects identifiers that are not simple SQL names. | | assertQueryAgainstSchema | (cache: SchemaCache, query: QueryIR) => void | Rejects unknown public-schema tables and columns. | | loadSchemaCache | (sql: SQL) => Promise<SchemaCache> | Loads public base tables and their columns. | | executeQueryIR | (runner: SqlRunner, schema: SchemaCache, ir: QueryIR, maxRelationDepth: number, onRoundTrip: RoundTripCounter) => Promise<QueryResult> | Runs one query IR operation, relation loads included, against any client. | | executeRpc | (runner: SqlRunner, routine: string, args: Readonly<Record<string, unknown>>, onRoundTrip: RoundTripCounter) => Promise<unknown> | Invokes a routine with a single jsonb argument against any client. | | RoundTripCounter | () => void | Called once per statement so a driver can keep its round-trip counter. | | SqlRunner | interface | unsafe(text, parameters?) returning normalized rows; the seam every client implements. | | SqlBatchRunner | interface | A SqlRunner that also applies a known statement list atomically through batch(). | | SqlRows | interface | Normalized rows plus the affected-row count. | | SchemaCache | type | Map of table name to column set. | | planMigrations | (sql: SQL, migrations: readonly PostgresMigration[]) => Promise<MigrationPlan> | Builds a pending plan from registered migrations and history. | | applyMigrations | (sql: SQL, migrations: readonly PostgresMigration[]) => Promise<readonly MigrationStatus[]> | Applies pending migrations inside transactions. | | rollbackMigrations | (sql: SQL, migrations: readonly PostgresMigration[], steps?: number) => Promise<readonly MigrationStatus[]> | Rolls back the newest applied migration batches. | | statusMigrations | (sql: SQL, migrations: readonly PostgresMigration[]) => Promise<readonly MigrationStatus[]> | Returns applied/pending status for every registered migration. | | PostgresMigration | interface | Driver-owned id, up, and down SQL pair. | | resetAssayFixtures | (sql: SQL) => Promise<void> | Provisions empty assay fixtures on a SQL client. | | ASSAY_FIXTURE_SQL | string | SQL that drops and recreates the database conformance fixtures. | | ASSAY_FIXTURE_STATEMENTS | readonly string[] | The same fixtures as one statement per entry, for clients that reject multi-statement text. |

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 '@avelonjs/conformance/suites'
import { createPostgresDatabase } from '@avelonjs/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.