Typed Pg Best Practices

Guidelines

• Query Builder Guide
• Table Types Guide
• Schema and Migration Guide
• Raw SQL and Client Guide
• Testing Guide

Specs

• JSDoc Authoring Specification
• Query Builder Surface Specification
• Migration File Specification
• Table Type Extension Specification

Packages

• Package Overview
• typed-pg
• typed-pg-dev

Query Builder Guide

When implementing or reviewing typed-pg query code, default to the fluent QueryBuilder surface instead of handwritten SQL.

Use This Guide When

• creating or updating SELECT, INSERT, UPDATE, DELETE, or UPSERT queries
• changing query-builder runtime behavior or type inference
• adding operators, joins, ordering, or JSONB selection
• reviewing whether a query can stay within the typed fluent API

Default Workflow

1. Start from client.query('<table>'). 2. Keep the query in builder methods for select, where, join, orderBy, limit, and offset. 3. Narrow results with select(...), first(), or pluck(...) when the caller does not need full rows. 4. Use whereRaw, orWhereRaw, or orderByRaw only for expressions the builder cannot represent directly. 5. If you change the query-builder surface, update both SQL generation and type-level coverage.

Minimal Example

const rows = await client
  .query('users')
  .select('id', 'name', { column: 'metadata', fields: ['age'] })
  .leftJoin('profiles', 'users.id', 'profiles.user_id')
  .where('name', 'ILIKE', 'a%')
  .orderBy('id', 'ASC')

Rules

1. Preserve fluent API and inference together

• A query-builder change is not done until runtime SQL and TypeScript inference both match.
• When adding or changing a clause, update the overloads or generics, the SQL builder, and the

related runtime or type tests together.

2. Prefer typed clauses before raw SQL

• Use where, orWhere, join, leftJoin, orderBy, count, first, and pluck first.
• Prefer built-in operators for equality, ranges, arrays, pattern matching, and JSONB

containment.

• Use raw clauses for expressions such as CASE, SQL functions, or predicates that do not map to

the built-in surface.

3. Keep raw fragments parameterized

• Raw clause values SHOULD still go through placeholders and params.
• rawSql SHOULD only be used for trusted SQL fragments or identifiers that cannot be represented

otherwise.

• Never interpolate end-user values into raw SQL strings.

4. Narrow result shapes intentionally

• Use select(...) to avoid fetching wider row shapes than needed.
• Use JSONB field selection when the caller only needs a subset of a JSONB column.
• Use first() for a single row and pluck('<column>') for a single column.

5. Keep write queries guarded

• update() and delete() MUST keep explicit where conditions.
• Do not remove or bypass the missing-where protection.
• When reviewing write queries, treat an unbounded mutation as a bug unless it is an explicit,

deliberate migration or maintenance action.

6. Use returning only when the caller needs changed rows

• insert, update, and upsert return an empty result shape unless returning is requested.
• Keep returning columns explicit so the result type stays narrow and predictable.

Review Checklist

• the query uses builder methods before falling back to raw SQL
• clause changes keep runtime SQL and inference in sync
• raw fragments still use parameters for values
• result shape is narrowed with select, first, or pluck when appropriate
• update and delete stay guarded by where
• query-builder changes include tests under src/__tests__/query-builder/

Read Next

• Query Builder Surface Specification
• Raw SQL and Client Guide
• Testing Guide

Raw SQL and Client Guide

When typed-pg needs SQL outside the fluent builder, default to client.raw(...) with parameters, and only use raw fragments deliberately.

Use This Guide When

• writing client.raw(...) queries
• touching rawSql, escapeIdentifier, or value escaping behavior
• building custom joins, predicates, or schema statements
• reviewing query logging or transaction behavior

Default Workflow

1. Set DATABASE_URL in the environment and prefer getClient() so production and tests share one bootstrap path. 2. Prefer client.query(...) when the fluent API already supports the query. 3. Use client.raw(...) with template strings or ? placeholders for custom SQL. 4. Use escapeIdentifier or rawSql(...) only for trusted identifiers or SQL fragments that cannot be parameterized. 5. Wrap multi-step data mutations in client.transaction(...).

Minimal Example

import { getClient } from 'typed-pg'

const client = getClient()

await client.transaction(async (trx) => {
  await trx.raw('UPDATE users SET name = ? WHERE id = ?', 'Alice', 1)
  await trx.raw`INSERT INTO audit_logs (action, user_id) VALUES (${'rename_user'}, ${1})`
})

Rules

1. Parameterize values by default

• Use placeholders or template parameters for runtime values.
• Prefer builder helpers or client.raw(...) over manual string concatenation.
• Treat value interpolation in SQL strings as a bug unless the input is a trusted static literal.

2. Keep database bootstrap consistent across environments

• Prefer getClient() for the default application client so the shared bootstrap path stays tied

to process.env.DATABASE_URL.

• Reach for createClient(process.env.DATABASE_URL, options) only when custom postgres.js

options or multiple database connections are required.

• Treat getClient() throwing as a signal that the shared bootstrap path was not configured.
• In tests, let TypedPgVitestPlugin() populate DATABASE_URL instead of building a separate

testing-only connection path.

• Keep SSL, pool, or logging tweaks explicit, but avoid branching to a completely different source

for the connection string unless there is a strong reason.

3. Escape identifiers and fragments explicitly

• SQL identifiers cannot be parameterized; use escapeIdentifier(...) or a carefully bounded

rawSql(...) fragment instead.

• rawSql(...) is for trusted SQL only and MUST NOT wrap untrusted user input.

4. Choose the right transactional boundary

• Use client.transaction(...) for multi-step DML or mixed read-write flows.
• Use SchemaBuilder.run() for batched DDL generated by schema helpers.
• Keep all-or-nothing behavior explicit in review and tests.

5. Use query logging selectively

• The client logger is optional.
• Debug logging is appropriate for query timing, troubleshooting, or temporary diagnostics.
• Avoid coupling normal application logic to debug logging side effects.

6. Keep raw SQL aligned with the typed surface

• If a raw query becomes common or reusable, consider whether it belongs in the fluent API instead.
• If a library change adds a new clause helper, update guidance and tests so consumers can leave raw

SQL behind.

Review Checklist

• values are parameterized
• the client bootstrap goes through getClient() and process.env.DATABASE_URL
• identifiers or SQL fragments are escaped or explicitly trusted
• raw SQL is used only where the builder surface is insufficient
• multi-step writes use transaction(...) when atomicity matters
• debug logging changes do not affect normal runtime behavior

Read Next

• Query Builder Guide
• Query Builder Surface Specification
• typed-pg

Schema and Migration Guide

When implementing or reviewing DDL in typed-pg, default to SchemaBuilder, TableBuilder, and timestamped migration files.

Use This Guide When

• creating or updating migrations
• changing tables, columns, indexes, or constraints
• reviewing SchemaBuilder or TableBuilder behavior
• deciding whether a schema change should use builder helpers or raw SQL

Default Workflow

1. Create a timestamped .ts migration file, usually with typed-pg new <name>. 2. Implement up(builder) with SchemaBuilder and TableBuilder helpers first. 3. Implement down(builder) for rollback when practical. 4. Keep related DDL in one builder run so it stays transactional. 5. Fall back to raw() only for SQL the current helpers do not support.

Minimal Example

import type { SchemaBuilder } from 'typed-pg'

export function up(builder: SchemaBuilder) {
  builder.createTable('users', (table) => {
    table.string('id').primary()
    table.string('name')
    table.jsonb('metadata').defaultTo('{}')
    table.timestamps()
    table.index('name')
  })
}

export function down(builder: SchemaBuilder) {
  builder.dropTable('users')
}

Rules

1. Keep migration filenames lexically sortable

• Migration files MUST remain timestamp-based and sortable by filename.
• Avoid custom naming schemes that break lexical ordering.
• Prefer the generated CLI naming pattern unless there is a strong reason not to.

2. Prefer builder helpers over handwritten DDL

• Use createTable, alterTable, renameTable, dropTable, and TableBuilder column helpers

first.

• Use specificType(...) when the schema needs a PostgreSQL type not covered by a built-in helper.
• Use raw DDL only for unsupported features or carefully scoped one-off statements.

3. Preserve transactional schema execution

• SchemaBuilder.run() executes accumulated statements in a single transaction.
• Write migrations assuming the batch should succeed or fail as one unit.
• Do not split one logical schema change across unrelated builder runs unless partial application is

intentional.

4. Keep migrations deterministic and reversible

• up and down should be direct, readable descriptions of the schema transition.
• Avoid time-sensitive or environment-sensitive SQL inside migrations unless it is explicitly

required.

• Prefer reversible changes when practical so down() can restore the previous state.

5. Keep migration history semantics stable

• typed_pg_migrations is the source of migration history.
• migrate() applies all pending files, up() applies the next pending file, and down() rolls

back the latest recorded file.

• Changes to migrator behavior should preserve those mental models unless the feature explicitly

redefines them.

Review Checklist

• migration file name remains timestamp-sorted
• up and down are both present and easy to reason about
• builder helpers are used before raw DDL
• schema changes expect SchemaBuilder.run() to be atomic
• migration or schema changes include tests under schema-builder or migrator

Read Next

• Migration File Specification
• Testing Guide
• typed-pg

Table Types Guide

When implementing or reviewing typed-pg table typing, default to declaration merging on Tables.

Use This Guide When

• defining application tables for typed-pg
• adding columns or JSONB shapes
• changing exported type helpers such as TableType, ColumnName, or ColumnValue
• reviewing query inference regressions

Default Workflow

1. Extend Tables in app code with declare module 'typed-pg'. 2. Model each table as its runtime row shape. 3. Let client.query, TableType, ColumnName, and ColumnValue infer from that source. 4. Add or update expectTypeOf coverage when library type behavior changes.

Minimal Example

declare module 'typed-pg' {
  interface Tables {
    users: {
      id: number
      name: string
      metadata: {
        age: number
        timezone?: string
      }
    }
  }
}

Rules

1. Treat Tables as the source of truth

• Tables drives table-name inference and column-level type inference.
• When table shape changes, update the merged interface before adjusting query code.

2. Keep row shapes concrete

• Model row fields with their actual runtime names and value shapes.
• Prefer exact object types for JSON or JSONB columns instead of any.
• Include optional properties only when the stored JSON shape is genuinely optional.

3. Preserve the consumer extension pattern

• Library changes MUST keep module augmentation on Tables working.
• Do not replace declaration merging with an app-specific registry or runtime-only typing.
• When changing public types, keep the fallback behavior for untyped tables deliberate and

documented.

4. Keep helper types aligned

• TableType<T> should represent the row shape for a known table.
• ColumnName<T> should stay aligned with actual keys of the table type.
• ColumnValue<T, C> should resolve to the value type for that column.

5. Update public type tests when the surface changes

• Add or update expectTypeOf assertions for changes to declaration merging or query inference.
• If a new query-builder feature affects result shape, test both runtime output and inferred types.

Review Checklist

• Tables contains the new or changed table shape
• JSON and JSONB columns use concrete object types
• declaration merging still works from consumer code
• helper types stay aligned with the merged table definition
• public type changes include expectTypeOf coverage

Read Next

• Table Type Extension Specification
• Query Builder Guide
• Testing Guide

Testing Guide

When changing typed-pg, every behavior change should come with runtime tests, and public type surface changes should come with type assertions.

Use This Guide When

• adding or changing query-builder behavior
• changing public types or declaration merging behavior
• updating schema or migration helpers
• writing integration tests for typed-pg packages

Default Workflow

1. Prefer TypedPgVitestPlugin() so Vitest boots a temporary database, provisions one database per worker when file parallelism is enabled, runs migrations, and clears table contents before each test. 2. Let TypedPgVitestPlugin() inject DATABASE_URL, then use getClient() to seed data and run assertions so production and test code share the same connection bootstrap path. 3. Add only the suite-specific setup or fixtures that the plugin does not already provide. 4. Pair runtime assertions with expectTypeOf(...) when a change affects inference. 5. Run npm test for behavior changes, and npm run build when exports or CLI entrypoints change.

Minimal Example

// vitest.config.ts
import { defineConfig } from 'vitest/config'
import { TypedPgVitestPlugin } from 'typed-pg-dev'

export default defineConfig({
  plugins: [TypedPgVitestPlugin()],
})

import { describe, expect, it } from 'vitest'
import { getClient } from 'typed-pg'

async function seedUser() {
  await getClient().query('users').insert({
    id: 1,
    name: 'Alice',
  })
}

describe('users query', () => {
  it('selects seeded rows', async () => {
    const client = getClient()

    await seedUser()

    await expect(client.query('users').where({ id: 1 })).resolves.toMatchObject([{ name: 'Alice' }])
  })
})

Rules

1. Every behavior change needs a test update

• Add a new test or update an existing one for every runtime behavior change.
• Prefer focused tests near the feature you changed instead of broad catch-all suites.

2. Public type changes need expectTypeOf

• Add or update type assertions when changing inference, overloads, or declaration merging.
• If a query-builder method changes result shape, test the inferred result type directly.

3. Put tests next to the feature area

• Query-builder clause and operator changes belong under src/__tests__/query-builder/.
• Schema behavior belongs under src/schema-builder/__tests__/.
• Migration behavior belongs under src/migrator/__tests__/.
• CLI behavior belongs under src/cli/__tests__/.

4. Keep tests isolated even without file parallelism

• The test runner uses fileParallelism: false, but tests should still clean up after themselves.
• Prefer TypedPgVitestPlugin() to reset rows automatically before each test.
• Create extra tables or fixture data explicitly when a suite goes beyond the default migrations.
• Do not rely on hidden state from another file.

5. Use typed-pg-dev through the Vitest plugin

• Prefer TypedPgVitestPlugin() for workspace test runs.
• In tests, let the plugin inject DATABASE_URL and use getClient() directly for fixture setup

and assertions.

• Because getClient() throws when no shared client can be resolved, test examples should not add

redundant undefined guards around the plugin-managed client.

• Reach for createClient(process.env.DATABASE_URL, options) only when a suite genuinely needs

custom postgres.js options or an extra connection.

• Keep lower-level database bootstrapping internal to the repo; public examples should only show the plugin.

Review Checklist

• runtime behavior changes have test coverage
• public type changes have expectTypeOf coverage
• tests live in the feature area that changed
• suites either rely on the plugin reset or clean up their own extra tables/temp folders
• validation commands match the change surface

Read Next

• typed-pg-dev
• Query Builder Guide
• Schema and Migration Guide

Overview of official packages

typed-pg-dev / TypedPgVitestPlugin

Function: TypedPgVitestPlugin()

TypedPgVitestPlugin(): Plugin

Creates the Vitest plugin that wires typed-pg-dev into the test runner.

The plugin starts worker-isolated temporary databases, runs migrations from ./migrations, injects the connection string into process.env.DATABASE_URL, and clears table contents before each test.

Returns

Plugin

Vitest/Vite plugin instance.

typed-pg-dev

Functions

• TypedPgVitestPlugin

typed-pg / Client

Class: Client

Constructors

Constructor

new Client(sql, options?): Client

Parameters

sql

Sql

options?

`ClientOptions` = {}

Returns

Client

Methods

query()

query\<T\>(table): QueryBuilder\<T, Flatten\<UnionToIntersection\<InferColumnType\<T, `ColumnName`\<T\>\>\>\>[]\>

Initiates a query builder for the specified table.

Type Parameters

T

T _extends_ `TableName`

The type of the table name.

Parameters

table

T

The name of the table to query.

Returns

QueryBuilder\<T, Flatten\<UnionToIntersection\<InferColumnType\<T, `ColumnName`\<T\>\>\>\>[]\>

A new instance of the QueryBuilder for the specified table.

Example

const users = await client.query('users').select('*').where({ id: userId })

quit()

quit(): Promise\<void\>

Returns

Promise\<void\>

raw()

raw\<T\>(query, ...params): Promise\<T[]\>

Executes a raw SQL query and returns the result as an array of objects.

Type Parameters

T

T _extends_ Record\<string, any\> = any

The type of the result objects. Defaults to Record<string, any>.

Parameters

query

string \| TemplateStringsArray

The SQL query to execute. Can be a string or a template string array.

params

...any[]

The parameters to pass to the SQL query.

Returns

Promise\<T[]\>

A promise that resolves to an array of objects of type T.

Example

// using a template string array
const users = await client.raw<User[]>`SELECT * FROM users`
// using a string
const users = await client.raw<User[]>('SELECT * FROM users')
// template string array with parameters
const users = await client.raw<User[]>`SELECT * FROM users WHERE id = ${userId}`
// string with parameters
const users = await client.raw<User[]>('SELECT * FROM users WHERE id = $1', userId)

transaction()

transaction\<T\>(fn): Promise\<UnwrapPromiseArray\<T\>\>

Executes a function within a database transaction.

Type Parameters

T

T

The type of the result returned by the transaction function.

Parameters

fn

(client) => Promise\<T\>

A function that takes a Client instance and returns a promise.

Returns

Promise\<UnwrapPromiseArray\<T\>\>

• A promise that resolves to the result of the transaction function.

Example

const result = await client.transaction(async (trx) => {
  return await trx.query('users').insert({ name: 'Alice' })
})

Properties

logger?

readonly optional logger?: Logger

options

readonly options: `ClientOptions`

postgres

readonly postgres: Sql

typed-pg / Migrator

Class: Migrator

The Migrator class is responsible for handling database migrations. It provides methods to check migration status, apply migrations, and roll back migrations.

Param

The options for the migrator.

Param

The database client.

Param

The folder containing migration files.

Constructors

Constructor

new Migrator(options): Migrator

Parameters

options

client

`Client`

folder

string

Returns

Migrator

Methods

createMigrationTable()

createMigrationTable(): Promise\<any[]\>

Returns

Promise\<any[]\>

down()

down(): Promise\<undefined\>

Returns

Promise\<undefined\>

migrate()

migrate(): Promise\<undefined\>

Returns

Promise\<undefined\>

status()

status(): Promise\<any[]\>

Returns

Promise\<any[]\>

up()

up(): Promise\<undefined\>

Returns

Promise\<undefined\>

typed-pg / SchemaBuilder

Class: SchemaBuilder

Builds and executes schema changes against a Client.

Accumulated statements are executed in a single transaction by run.

Constructors

Constructor

new SchemaBuilder(client): SchemaBuilder

Parameters

client

`Client`

Returns

SchemaBuilder

Methods

alterTable()

alterTable(tableName, callback): SchemaBuilder

Parameters

tableName

string

callback

(table) => void

Returns

SchemaBuilder

createTable()

createTable(tableName, callback): SchemaBuilder

Parameters

tableName

string

callback

(table) => void

Returns

SchemaBuilder

dropTable()

dropTable(tableName): SchemaBuilder

Parameters

tableName

string

Returns

SchemaBuilder

raw()

raw(sql): SchemaBuilder

Parameters

sql

string

Returns

SchemaBuilder

renameTable()

renameTable(oldTableName, newTableName): SchemaBuilder

Parameters

oldTableName

string

newTableName

string

Returns

SchemaBuilder

run()

run(): Promise\<void\>

Returns

Promise\<void\>

toSQL()

toSQL(): string[]

Returns

string[]

typed-pg / createClient

Function: createClient()

createClient(url, options?): `Client`

Creates a new instance of the Client class from a PostgreSQL connection string.

Parameters

url

string

The PostgreSQL connection string.

options?

postgres.Options<Record<string, never>>

Optional postgres.js options when url is provided.

Returns

`Client`

A new Client instance.

Example

import { createClient } from 'typed-pg'

const client = createClient('postgres://user:pass@localhost:5432/db')

typed-pg / createTemplateStringsArray

Function: createTemplateStringsArray()

createTemplateStringsArray(str): TemplateStringsArray

Normalizes a SQL string or template input into a TemplateStringsArray.

Parameters

str

string \| TemplateStringsArray

SQL source string or template literal array.

Returns

TemplateStringsArray

Template-strings representation compatible with postgres.js.

typed-pg / escapeIdentifier

Function: escapeIdentifier()

escapeIdentifier(identifier): string

Escapes a SQL identifier, preserving trusted RawSql fragments.

Parameters

identifier

string \| `RawSql`

Table name, column name, dotted identifier, or trusted raw fragment.

Returns

string

Escaped identifier string ready to be embedded into SQL text.

typed-pg / escapeValue

Function: escapeValue()

escapeValue(value): string

Escapes a literal value for inline SQL generation.

Prefer bound parameters for runtime values whenever possible.

Parameters

value

any

Value to serialize into SQL text.

Returns

string

SQL literal representation of the value.

typed-pg / getClient

Function: getClient()

getClient(url?): `Client`

Returns a cached client created by createClient.

When url is omitted and the cache contains exactly one client, that client is returned. When the cache is empty and process.env.DATABASE_URL is set, a client is created from that URL, cached, and returned. Throws when no client can be resolved.

Parameters

url?

string

Returns

`Client`

Throws

When the requested URL is not cached.

Throws

When multiple cached clients exist and url is omitted.

Throws

When no cached client exists and process.env.DATABASE_URL is not set.

Example

import { getClient } from 'typed-pg'

const client = getClient()
const users = await client.query('users')

typed-pg / getClients

Function: getClients()

getClients(): `Client`[]

Returns all cached clients created by createClient.

Returns

`Client`[]

typed-pg / isTemplateStringsArray

Function: isTemplateStringsArray()

isTemplateStringsArray(value): value is TemplateStringsArray

Checks whether a value is a TemplateStringsArray.

Parameters

value

any

Returns

value is TemplateStringsArray

typed-pg / rawSql

Function: rawSql()

rawSql(value): `RawSql`

Creates a raw SQL value object.

This function is used to mark a string as a raw SQL value, which can be useful when you need to include raw SQL in a query without any escaping or processing.

Parameters

value

string

The raw SQL string.

Returns

`RawSql`

An object representing the raw SQL value with a custom toString method.

typed-pg / Tables

Interface: Tables

Consumer-extended table map used by typed-pg declaration merging.

Extend this interface in application code with declare module 'typed-pg'.

Properties

mutation

mutation: User

query

query: User

typed-pg

Functions

• createClient
• createTemplateStringsArray
• escapeIdentifier
• escapeValue
• getClient
• getClients
• isTemplateStringsArray
• rawSql

Classes

• Client
• Migrator
• SchemaBuilder

Interfaces

• Tables

Type Aliases

• ClientOptions
• ColumnName
• ColumnValue
• RawSql
• TableName
• TableType

typed-pg / ClientOptions

Type Alias: ClientOptions

ClientOptions = object

Properties

logger?

optional logger?: false \| \{ label?: string; level?: Level; \}

typed-pg / ColumnName

Type Alias: ColumnName\<T\>

ColumnName\<T\> = T _extends_ keyof `Tables` ? Extract\<keyof `Tables`\[T\], string\> : string

Column-name union for a known table, or string for unknown tables.

Type Parameters

T

T _extends_ string = string

typed-pg / ColumnValue

Type Alias: ColumnValue\<T, C\>

ColumnValue\<T, C\> = T _extends_ `TableName` ? C _extends_ keyof `Tables`\[T\] ? `Tables`\[T\]\[C\] : any : any

Value type for a known table column, or any when the table or column is unknown.

Type Parameters

T

T _extends_ string = string

C

C _extends_ string = string

typed-pg / RawSql

Type Alias: RawSql

RawSql = string & object

Trusted SQL fragment marker used to bypass identifier or value escaping.

Type Declaration

\_\_raw

\_\_raw: true

typed-pg / TableName

Type Alias: TableName

TableName = Extract\<keyof `Tables`, string\>

Known table names from the merged Tables interface.

typed-pg / TableType

Type Alias: TableType\<T\>

TableType\<T\> = T _extends_ `TableName` ? `Tables`\[T\] : Record\<string, any\>

Row type for a known table name, or a permissive record for unknown tables.

Type Parameters

T

T _extends_ string = string

JSDoc Authoring Specification

Background

typed-pg now generates API Markdown from source JSDoc with TypeDoc and typedoc-plugin-markdown.

build-docs.ts generates Markdown from package entrypoints and sync-skill-references.ts mirrors the generated files into this skill's package references.

This specification defines the authoring baseline for public API docs in typed-pg.

Goals

• keep public API docs close to the exported TypeScript source
• make generated Markdown predictable across typed-pg packages
• make examples clear enough for both users and AI coding agents

Non-goals

• replacing README or tutorial-style documentation
• documenting private helpers or test-only code
• standardizing prose outside source JSDoc and generated API Markdown

Normative Rules

1. Source of truth and scope

1. Public API documentation MUST be authored in JSDoc next to exported declarations in packages/*/src. 2. Generated Markdown under tmp/api-docs/ and mirrored skill references under skills/typed-pg-best-practices/references/packages/ MUST be treated as derived output and MUST NOT be hand-edited. 3. Every exported class, function, interface, type alias, and public variable intended for package consumption SHOULD have a JSDoc block. 4. Package entrypoints SHOULD include a package overview comment in src/index.ts.

2. Language and prose

1. Public JSDoc MUST be written in English. 2. The first sentence SHOULD summarize what the symbol is or does. 3. Additional text SHOULD explain observable behavior, constraints, or caveats instead of duplicating the TypeScript signature. 4. When touching public docs, keep the prose aligned with actual runtime and type behavior.

3. Tag conventions

1. @param SHOULD use {Type} name - description style. 2. @returns SHOULD be used when the returned value is not obvious from the summary. 3. @example SHOULD be provided for public APIs whose usage is non-trivial. 4. @throws SHOULD document user-visible errors that callers may need to handle. 5. @property SHOULD be used for exported object-shaped types when inline member docs are not enough. 6. {@link Symbol} SHOULD be preferred for cross references to nearby API symbols.

4. Generation and maintenance

1. When exported APIs or public JSDoc change, contributors SHOULD run npm run doc. 2. Generated output SHOULD be reviewed to confirm headings, links, and examples render correctly. 3. Touched comments SHOULD move toward this style even if nearby legacy comments are older.

Example

````ts /**

• Creates a testing postgres.js client configured for typed-pg suites.

*

• @param {CreateTestingPostgresOptions<T>} [options] - Optional connection and postgres.js
• overrides.
• @returns Configured postgres.js client instance.
• @example
• ```ts
• const sql = createTestingPostgres()
• const rows = await sqlSELECT 1
• ```

*/ export function createTestingPostgres< T extends Record<string, PostgresType> = Record<string, never>, >(options: CreateTestingPostgresOptions<T> = {} as CreateTestingPostgresOptions<T>) { // ... } ````

Migration File Specification

This reference defines the migration-file contract assumed by typed-pg.

Location

• Migration files live under the configured migration folder.
• The CLI defaults to the local migrations/ directory.

Filename Rules

1. Migration files MUST end with .ts. 2. Filenames MUST remain lexically sortable. 3. The normal pattern is YYYYMMDDHHMMSS...-name.ts. 4. Timestamp prefixes are the ordering mechanism; do not replace them with unsorted names.

File Shape

Each migration file exports:

import type { SchemaBuilder } from 'typed-pg'

export function up(builder: SchemaBuilder) {}

export function down(builder: SchemaBuilder) {}

Runtime Semantics

1. Migrator resolves the folder path up front and fails fast if it does not exist. 2. migrate() applies every pending .ts migration that is not recorded in typed_pg_migrations. 3. up() applies the next pending migration after the latest recorded migration. 4. down() rolls back the latest recorded migration by loading the matching file. 5. Applied migration names are stored in typed_pg_migrations. 6. Each migration normally uses SchemaBuilder.run() so batched DDL stays transactional.

Authoring Guidelines

• Keep up() and down() deterministic.
• Prefer builder helpers before raw().
• Keep names descriptive after the timestamp so migration intent is obvious in history.

Query Builder Surface Specification

This reference captures the stable QueryBuilder surface that the skill assumes.

Read Methods

• select(...columns)
• where(...)
• orWhere(...)
• whereRaw(sql, ...params)
• orWhereRaw(sql, ...params)
• join(...)
• leftJoin(...)
• orderBy(column, direction?)
• orderByRaw(sql, ...params)
• limit(number)
• offset(number)
• count()
• first()
• pluck(column)

Write Methods

• insert(values, { returning? })
• update(values, { returning? })
• delete()
• upsert(values, { conflict, update?, returning? })

Supported Operators

Comparison

• =
• !=
• <
• <=
• >
• >=

Array

• IN
• NOT IN

Null

• IS NULL
• IS NOT NULL

Pattern

• LIKE
• ILIKE
• NOT LIKE
• NOT ILIKE

JSONB

• @>

Execution Notes

1. QueryBuilder is thenable and executes through client.raw(...). 2. Omitting select(...) means SELECT *. 3. JSONB partial selection uses { column, fields, alias? }. 4. join(table, left, right) and leftJoin(table, left, right) default to =. 5. orderBy(...) accepts ASC, DESC, asc, and desc. 6. update() and delete() reject when there are no where conditions. 7. Raw clauses still support bound params and should keep values parameterized.

Table Type Extension Specification

This reference defines how consumers extend table typing in typed-pg.

Extension Mechanism

typed-pg uses declaration merging on the exported Tables interface.

declare module 'typed-pg' {
  interface Tables {
    users: {
      id: number
      name: string
      metadata: {
        age: number
      }
    }
  }
}

Derived Helper Types

• TableName -> string union of known table names
• TableType<'users'> -> row shape for that table
• ColumnName<'users'> -> string union of column names for that table
• ColumnValue<'users', 'name'> -> value type for that column

Fallback Behavior

1. Unknown or untyped tables fall back to permissive string or any behavior. 2. Known tables should produce narrow inference through the merged Tables interface. 3. Library changes should preserve this extension model unless a deliberate breaking change is introduced.

JSON and JSONB Guidance

• Represent JSON-like columns with concrete object types whenever possible.
• Optional nested properties should reflect real runtime optionality.
• Avoid any unless the stored document shape is intentionally unconstrained.