@azlib/persistence

Type-safe ORM, schema definition DSL, relational graph hydration, AST query builders, and database adapter layer for TypeScript and the @azlib monorepo.

Capabilities

• Type-Safe Schema Definition: Define tables, columns, indexes, and relations with zero-codegen TypeScript type inference (createTable, pgTable, column.*, index, uniqueIndex, InferSelect, InferInsert).
• Relational Query & Graph Hydration: Drizzle/Prisma-style nested relational querying (db.query.users.findMany({ with: { posts: true, profile: true } })) without N+1 query overhead.
• AST Query Builders: Type-safe query building for select(), insert(), update(), and delete() across SQL dialects with support for joins, aggregations (count, sum, avg, min, max), grouping (groupBy, having), distinct, unions, and subqueries.
• Dialect-Accurate Upserts: onConflictDoUpdate() and onConflictDoNothing() compiling to ON CONFLICT (PostgreSQL / SQLite) or ON DUPLICATE KEY UPDATE (MySQL).
• Nested Transactions & Savepoints: Multi-level db.transaction() with automatic SAVEPOINT and ROLLBACK TO SAVEPOINT isolation.
• Pluggable SQL Client Adapter: Wrap any external database driver (pg, mysql2, better-sqlite3, mssql) via createSqlClientAdapter().
• Dialect Adapters & DDL Generation: Cross-dialect table and index DDL generation for PostgreSQL, MySQL, SQLite, and Microsoft SQL Server.
• Schema Bootstrapping & Readiness: Schema compatibility validation and table initialization (BootstrapService).

AI Agent Quick Reference

Core Exports

| Export | Type | Description | | :-------------------------------------------------------------------------------------------------------------------------------------------------------- | :------- | :------------------------------------------------------------------------------------------------------------------------------------ | | createTable, pgTable, mysqlTable, sqliteTable, sqlServerTable | Function | Defines typed database tables, columns, and indexes. | | column (text, varchar, integer, serial, bigint, boolean, decimal, enumType, blob, json, timestamp, uuid) | Object | Column builder functions with chaining methods (notNull, primaryKey, autoIncrement, default, references, unique). | | index, uniqueIndex | Function | Defines composite or single-column indexes on a table. | | relations(table, helpers) | Function | Declares One-to-One (one) and One-to-Many (many) entity relationships. | | createDatabaseClient(config): DatabaseClient | Function | Instantiates fluent query client with db.query, db.select, db.insert, db.update, db.delete, db.raw, and db.transaction. | | createSqlClientAdapter(driver): SqlClientAdapter | Function | Wraps third-party database drivers into a standard execution contract. | | createPersistenceConfig(client, dialect, ns?): PersistenceConfig | Function | Bundles client adapter, SQL dialect rules, and table namespace. | | eq, ne, gt, gte, lt, lte, like, ilike, isNull, isNotNull, inArray, notInArray, between, notBetween, and, or, not, sql | Function | SQL expression operators and template literal tag. | | count, countDistinct, sum, avg, min, max, exists, notExists | Function | SQL aggregate and subquery functions. | | generateCreateTableDdl(table, dialect): string | Function | Generates CREATE TABLE IF NOT EXISTS DDL for the target dialect. | | generateTableIndexesDdl(table, dialect): string[] | Function | Generates CREATE INDEX IF NOT EXISTS statements for defined table indexes. | | createBootstrapService(dialect): BootstrapService | Function | Evaluates storage readiness and runs bootstrap DDL. |

Usage Guide

1. Defining Tables & Relations

import { column, createTable, index, relations } from "@azlib/persistence";
import type { InferInsert, InferSelect } from "@azlib/persistence";

// Define users table
export const users = createTable(
  "users",
  {
    id: column.serial("id"),
    email: column.varchar("email", { length: 255 }).notNull().unique(),
    name: column.text("name").notNull(),
    role: column.enumType("role", ["admin", "editor", "user"]).default("user"),
    age: column.integer("age"),
    salary: column.decimal("salary", { precision: 10, scale: 2 }),
    createdAt: column.timestamp("created_at").notNull(),
  },
  (t) => [index("idx_users_role_age").on(t.role, t.age)],
);

// Define posts table with foreign key
export const posts = createTable("posts", {
  id: column.serial("id"),
  title: column.varchar("title", { length: 200 }).notNull(),
  content: column.text("content"),
  authorId: column.integer("author_id").references(() => users.id, {
    onDelete: "CASCADE",
  }),
});

// Infer TypeScript types
export type User = InferSelect<typeof users>;
export type NewUser = InferInsert<typeof users>;
export type Post = InferSelect<typeof posts>;

// Declare relations
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

2. Initializing the Database Client

import {
  createDatabaseClient,
  createPersistenceConfig,
  createPostgresDialectAdapter,
  createSqlClientAdapter,
} from "@azlib/persistence";

const db = createDatabaseClient(
  createPersistenceConfig(
    createSqlClientAdapter(myPgPool),
    createPostgresDialectAdapter(),
    "production", // optional namespace prefix
  ),
);

3. Relational Queries (Graph Hydration)

// Find users along with their nested posts
const usersWithPosts = await db.query.users.findMany({
  where: eq(users.role, "admin"),
  with: {
    posts: true,
  },
  orderBy: [users.id, "desc"],
  limit: 20,
});

// Find a single record
const singleUser = await db.query.users.findFirst({
  where: eq(users.email, "[email protected]"),
  with: {
    posts: true,
  },
});

4. Query Builders (CRUD, Aggregates, Upserts)

Select & Aggregates

import { and, avg, count, eq, gt, sum } from "@azlib/persistence";

const stats = await db
  .select({
    role: users.role,
    totalCount: count(users.id),
    totalSalary: sum(users.salary),
    avgAge: avg(users.age),
  })
  .from(users)
  .where(and(gt(users.age, 18), eq(users.role, "user")))
  .groupBy(users.role)
  .having(gt(count(users.id), 2))
  .orderBy(users.role, "asc")
  .limit(10)
  .execute();

Upsert (On Conflict)

await db
  .insert(users)
  .values({
    email: "[email protected]",
    name: "John Doe",
    createdAt: new Date(),
  })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: "John Doe Updated" },
  })
  .returning("id", "email")
  .execute();

5. Nested Transactions & Savepoints

await db.transaction(async (tx1) => {
  await tx1
    .insert(users)
    .values({ name: "Dan", email: "[email protected]", createdAt: new Date() })
    .execute();

  // Nested transaction creates SQL SAVEPOINT
  await tx1.transaction(async (tx2) => {
    await tx2
      .insert(posts)
      .values({ title: "First Post", authorId: 1 })
      .execute();
  });
});