PostgreSQL adapter

PostgreAdapter implements DatabaseAdapter for Drizzle's asynchronous PostgreSQL database.

Create an adapter

import { createPostgreAdapter } from '@opensya/persistence'

const adapter = createPostgreAdapter(db)

The database must support Drizzle's select, insert, update, delete, execute, and transaction APIs.

Schema creation

const engine = createQueryEngine({ registry, adapter })
const result = await engine.schema.createTables()

The adapter creates missing tables, static database defaults, primary keys, single-column unique constraints, declared indexes and owning-side foreign keys inside a PostgreSQL transaction. Function defaults remain runtime defaults and are not converted into frozen database values.

Tables are created with IF NOT EXISTS, indexes with IF NOT EXISTS, and existing foreign keys are detected before creation. Running the operation more than once is safe and existing data is not modified.

Migrations

PostgreAdapter renders logical migration operations as PostgreSQL DDL and executes pending migrations inside a transaction.

const plan = await engine.migrations.plan(migrations)
const result = await engine.migrations.apply(migrations)

Applied migrations are stored in _opensya_migrations. A PostgreSQL advisory lock prevents concurrent deployment processes from applying the same migration. Checksums protect applied artifacts from later modification.

Destructive and irreversible plans require explicit approval:

await engine.migrations.apply(migrations, {
  allowDestructive: true
})

Runtime tables

for (const metadata of registry.getAll()) {
  adapter.buildTable(metadata)
}

buildTable() converts each TableMetadata into a Drizzle pgTable and indexes it by the logical metadata name.

Column mapping

The adapter maps primary keys, nullability, uniqueness, and static or factory defaults to the corresponding Drizzle builder methods.

Query translation

The adapter compiles QueryFilter recursively:

• conditions at the same level are joined with AND;
• nested and groups use Drizzle and();
• nested or groups use or();
• not wraps the compiled nested expression;
• operators map to eq, ne, inArray, notInArray, comparisons, isNull, and isNotNull.

await adapter.findMany('users', {
  where: {
    and: [
      {
        conditions: [
          { field: 'active', operator: 'eq', value: true }
        ]
      },
      {
        or: [
          {
            conditions: [
              { field: 'role', operator: 'eq', value: 'admin' }
            ]
          },
          {
            conditions: [
              { field: 'role', operator: 'eq', value: 'owner' }
            ]
          }
        ]
      }
    ]
  },
  orderBy: [{ field: 'createdAt', direction: 'desc' }],
  limit: 20,
  offset: 0
})

Adapter-level guards

Before executing SQL, the adapter verifies:

• filter, sort, insert, and update fields exist on the built table;
• limit and offset are not negative;
• in and notIn receive non-empty arrays;
• update and delete filters contain at least one effective constraint.

These checks also apply when the adapter is used directly. Direct calls do not, however, run Query Engine validators or lifecycle hooks.

Transactions

await adapter.transaction(async tx => {
  const user = await tx.insert('users', input)

  await tx.insert('profiles', {
    id: crypto.randomUUID(),
    userId: user.id
  })
})

The callback receives a new adapter bound to the Drizzle transaction while sharing the previously built table map.

PostgreSQL introspection

const actualSchema = await adapter.introspect()

The adapter queries information_schema directly. Introspection does not depend on the tables previously passed to buildTable().

Introspected SQL types

When to use the adapter directly

Use QueryEngine for domain operations. Use the adapter directly for infrastructure code that intentionally needs lower-level access, such as schema inspection or transaction-scoped operations inside hooks.