ts-dao-aston

Lightweight PostgreSQL DAO library for Node.js + TypeScript. No ORM, no magic — just clean SQL with named parameters, optional WHERE blocks, and entity helpers.

Installation

npm install ts-dao-aston

Quick Start

import { selectOne, select, execute, insertGetId } from 'ts-dao-aston'

const user = await selectOne<User>(
  'SELECT * FROM users WHERE id=:id',
  { id: '123' }
)

Core Functions

All core functions accept an optional tx parameter for transaction support.

selectOne<T>(sql, params?, tx?)

Expects exactly 1 row. Throws NoRowsError if 0 rows, TooManyRowsError if more than 1.

maybeOne<T>(sql, params?, tx?)

Returns T | null. Throws TooManyRowsError if more than 1 row returned.

select<T>(sql, params?, tx?)

Returns T[].

execute(sql, params?, tx?)

For INSERT / UPDATE / DELETE with no return value.

insertGetId(sql, params?, tx?)

INSERT with RETURNING id — returns the generated id as string.

Named Parameters

Use :name syntax instead of positional $1:

const user = await selectOne<User>(
  'SELECT * FROM users WHERE email=:email AND active=:active',
  { email: '[email protected]', active: true }
)

Optional WHERE Blocks

Wrap optional conditions in /** ... **/ comments. The block is removed if the parameter is null or undefined.

const users = await select<User>(`
  SELECT * FROM users WHERE 1=1
  /** AND id=:id **/
  /** AND ext_id=:extId **/
  /** AND active=:active **/
`, { id: null, extId: 'abc', active: true })

// generated SQL:
// SELECT * FROM users WHERE 1=1 AND ext_id=$1 AND active=$2

Array Parameters (IN clause)

Use spread() to expand an array into multiple positional parameters for IN clauses. Plain arrays are not auto-expanded — they are passed as-is to pg (PostgreSQL arrays or JSONB).

import { select, spread } from 'ts-dao-aston'

const users = await select<User>(
  'SELECT * FROM users WHERE id IN (:ids) AND status=:status',
  { ids: spread([10, 20, 30]), status: 'active' }
)

// generated SQL:
// SELECT * FROM users WHERE id IN ($1,$2,$3) AND status=$4
// values: [10, 20, 30, 'active']

spread() throws if the array is empty. Works inside optional /** **/ blocks — if the param is null, the block is removed as usual.

Entity Helpers

An entity is defined via EntityConfig — a Zod schema for type mapping + table metadata.

EntityConfig + DAO — single file

// userDao.ts
import { z } from 'zod'
import { EntityConfig, oneEntity, maybeEntity, insertEntity, insertEntityWithId, updateEntity, upsertEntity, deleteById, maybeOne, select } from 'ts-dao-aston'

const userSchema = z.object({
  id:         z.string().uuid(),
  name:       z.string(),
  email:      z.string().email(),
  ext_id:     z.string().nullable(),
  amount:     z.string().transform(parseFloat),   // numeric → number
  counter:    z.string().transform(parseInt),     // bigint → number
  active:     z.boolean(),
  created_at: z.date(),
  updated_at: z.date(),
})

export type User = z.infer<typeof userSchema>

const userConfig: EntityConfig<typeof userSchema> = {
  table:     'users',
  schema:    userSchema,
  pk:        'id',
  createdAt: 'created_at',
  updatedAt: 'updated_at',
}

export const userDao = {

  // entity helpers — standard CRUD
  loadById:    (id: string) => oneEntity(userConfig, id),
  maybeById:   (id: string) => maybeEntity(userConfig, id),
  insert:      (u: Partial<User>) => insertEntity(userConfig, u),
  insertGetId: (u: Partial<User>) => insertEntityWithId(userConfig, u),
  update:      (u: Partial<User>) => updateEntity(userConfig, u),
  save:        (u: User)          => upsertEntity(userConfig, u),
  deleteById:  (id: string) => deleteById(userConfig, id),

  // custom SQL
  loadByEmail: (email: string) =>
    maybeOne<User>('SELECT * FROM users WHERE email=:email', { email }),

  search: (params: { name?: string, active?: boolean }) =>
    select<User>(`
      SELECT * FROM users WHERE 1=1
      /** AND name ILIKE :name **/
      /** AND active=:active **/
    `, params),
}

Zod schema validates write input via schema.partial().parse() before insert/update/upsert. Read operations return raw rows without validation. Columns pk, createdAt, updatedAt are automatically excluded on insert — their values are generated by the DB.

Entity Functions

All entity functions accept an optional tx parameter for transaction support.

oneEntity(config, id, tx?)           → SELECT * WHERE pk=:id             — throws NoRowsError if not found
maybeEntity(config, id, tx?)         → SELECT * WHERE pk=:id             — returns null if not found
insertEntity(config, data, tx?)      → INSERT INTO (cols) VALUES (...)   — Partial<T>, excludes pk, createdAt, updatedAt
insertEntityWithId(config, data, tx?) → INSERT ... RETURNING pk          — Partial<T>, returns generated id
updateEntity(config, data, tx?)      → UPDATE SET ... WHERE pk=:id       — PATCH semantics, updatedAt = now()
upsertEntity(config, data, tx?)      → INSERT ... ON CONFLICT DO UPDATE  — updatedAt = now()
deleteById(config, id, tx?)          → DELETE WHERE pk=:id

updateEntity — PATCH semantics

updateEntity accepts Partial<T>. The primary key is required, everything else is optional.

| Property state | Meaning | SQL behavior | |---|---|---| | value (e.g. name: 'Jano') | Set column to this value | SET name=$1 | | null (e.g. ext_id: null) | Set column to NULL | SET ext_id=$1 → NULL | | undefined or missing key | Don't touch this column | Column excluded from SET |

PostgreSQL as Document-Relational DB

Entity properties can be complex objects — they are stored as jsonb columns. The Zod schema serves as the single source of truth — the library automatically detects which columns are JSON based on the Zod type, without any extra configuration.

Definition

const userSchema = z.object({
  id:      z.string().uuid(),
  name:    z.string(),
  active:  z.boolean(),

  // jsonb columns — auto-detected from Zod type
  address: z.object({
    street: z.string(),
    city:   z.string(),
    zip:    z.string(),
  }),
  tags:    z.array(z.string()),
  meta:    z.record(z.string(), z.unknown()),
})

How it works

The library introspects Zod schemas and identifies JSON columns by type:

| Zod type | JSON serialization | |---|---| | z.object() | yes | | z.array() | yes | | z.record() | yes | | z.string(), z.number(), z.boolean(), z.date() | no |

On read — pg deserializes jsonb automatically, Zod schema validates the structure.

On write — the library calls JSON.stringify() on detected columns before sending to DB.

EntityConfig stays the same

export const userConfig: EntityConfig<typeof userSchema> = {
  table:     'users',
  schema:    userSchema,   // jsonb columns are inferred automatically
  pk:        'id',
  createdAt: 'created_at',
  updatedAt: 'updated_at',
}

Transactions

Every core function and entity helper accepts an optional tx context. If provided, the query runs within that transaction.

import { withTransaction } from 'ts-dao-aston'

await withTransaction(async (tx) => {
  await userDao.insert(user, tx)
  await auditDao.log({ action: 'INSERT', entityId: user.id }, tx)
})

Configuration

import { configure } from 'ts-dao-aston'

configure({
  connectionString: process.env.DATABASE_URL,
  pool: { max: 10, idleTimeoutMillis: 30000 }
})

Or pass your own pg.Pool instance:

import { Pool } from 'pg'
import { configure } from 'ts-dao-aston'

const pool = new Pool({ connectionString: process.env.DATABASE_URL })
configure({ pool })

Error Handling

The library throws specific error types that can be caught and handled:

import { NoRowsError, TooManyRowsError, DaoError } from 'ts-dao-aston'

try {
  const user = await selectOne<User>('SELECT * FROM users WHERE id=:id', { id: '999' })
} catch (err) {
  if (err instanceof NoRowsError) {
    // no row found
  }
}

| Error | Thrown by | When | |---|---|---| | NoRowsError | selectOne, oneEntity, insertGetId | 0 rows returned | | TooManyRowsError | selectOne, maybeOne | more than 1 row returned | | DaoError | base class | parent of all DAO errors |

Cleanup

import { destroyPool } from 'ts-dao-aston'

await destroyPool() // closes all connections, e.g. on app shutdown

License

MIT