@oneunit/database

v1.0.0

Published

a month ago

PostgreSQL client with pooling, transactions, streaming, schema tools, models, and migrations.

0High
0Medium
0Low

bootstrap framework node typescript postgresql postgres database pool transaction query-builder migrations models streaming

Built on pg, with optional pg-cursor and pg-query-stream for streaming workloads. Ships as a standalone ESM package — no sibling-package runtime dependencies.

Monorepo — github.com/mayank040902/oneunit

Installation

npm install @oneunit/database

For streaming support, add the optional peer dependencies:

npm install pg-cursor pg-query-stream

Quick Start

import { createDatabase } from "@oneunit/database";

const db = createDatabase();

// Simple query
const { rows } = await db.query(
    "SELECT * FROM users WHERE status = $1",
    ["active"],
);

// Transaction with savepoints
await db.transaction(async (client) => {
    await client.query(
        "INSERT INTO users (email) VALUES ($1)",
        ["[email protected]"],
    );
});

// Graceful shutdown
await db.shutdown();

Connection settings are read from the environment by default, or pass them explicitly:

const db = createDatabase({
    connectionString: "postgresql://user:pass@localhost:5432/app",
    max: 10,
    application_name: "billing-api",
});

Configuration

All environment access is centralized in loadDatabaseConfig(). Explicit options always take precedence over environment values.

Tip — Copy .env.example and fill in values for local development.

import { loadDatabaseConfig, createDatabase } from "@oneunit/database";

const config = loadDatabaseConfig({ max: 8 });
const db = createDatabase(config);

Credentials are never written to logs.

Queries

Every query is parameterized — use $1, $2, ... placeholders. Clients are acquired and released automatically.

const result = await db.query(
    "SELECT id, email FROM users WHERE id = $1",
    [userId],
);

// Returns the first row, or null
const user = await db.queryOne(
    "SELECT * FROM users WHERE email = $1",
    ["[email protected]"],
);

Prepared Statements

const findUser = db.prepare(
    "find_user",
    "SELECT * FROM users WHERE id = $1",
);

const { rows } = await findUser.execute([userId]);

Manual Checkout

const client = await db.getClient();
try {
    await client.query("SELECT 1");
} finally {
    db.releaseClient(client);
}

Timeouts & Retries

Retries are off by default. Enable them only for idempotent statements. Only transient PostgreSQL errors (serialization failures, deadlocks, connection loss) are retried.

await db.query("SELECT pg_advisory_lock($1)", [42], {
    timeout: 2_000,
    retry: { retries: 3, baseDelay: 50 },
});

Transactions

Transactions always BEGIN / COMMIT / ROLLBACK and always release the client.

const created = await db.transaction(async (client) => {
    const { rows } = await client.query(
        "INSERT INTO users (email) VALUES ($1) RETURNING *",
        ["[email protected]"],
    );

    // Nested savepoint
    await db.savepoint(client, "profile", async (tx) => {
        await tx.query(
            "INSERT INTO profiles (user_id) VALUES ($1)",
            [rows[0].id],
        );
    });

    return rows[0];
});

Options:

await db.transaction(work, {
    isolation: "SERIALIZABLE",
    readOnly: false,
    timeout: 5_000,
    retry: { retries: 2 },
});

Supported isolation levels: READ UNCOMMITTED · READ COMMITTED · REPEATABLE READ · SERIALIZABLE

SQL Fragments & Query Builder

Tagged Template

import { createDatabase, sql } from "@oneunit/database";

const db = createDatabase();
const statuses = ["active", "pending"];

const fragment = sql`
    SELECT * FROM users
    WHERE status IN (${sql.join(statuses)})
    AND created_at > ${since}
`;

await db.query(fragment.text, fragment.values);

Fluent Query Builder

const { rows } = await db.from("users")
    .select(["id", "email"])
    .where({ status: "active" })
    .orderBy("created_at", "desc")
    .limit(20);

Identifiers are quoted. Values are never interpolated into SQL text.

Models

Lightweight CRUD helpers for common table operations:

const users = db.model("users");

await users.insert({ email: "[email protected]" });
await users.findOne({ email: "[email protected]" });
await users.updateById(id, { status: "active" });
await users.deleteById(id);
await users.count({ status: "active" });

Pass { client } to run inside an existing transaction.

Batch Inserts

await db.batch.insertMany("events", rows, {
    chunkSize: 500,
    onConflict: { columns: ["id"], do: "nothing" },
    returning: ["id"],
});

Rows are parameterized and chunked so the statement stays under PostgreSQL's 65 535 parameter limit.

Streaming & Cursors

Use streams or cursors for large result sets instead of loading everything into memory.

// Node.js readable stream
const stream = await db.stream(
    "SELECT * FROM audit_logs WHERE created_at < $1",
    [cutoff],
);
stream.on("data", (row) => processRow(row));
stream.on("error", console.error);

// Async iterator (cursor-based)
const cursor = await db.cursor("SELECT * FROM analytics_events");
for await (const row of cursor) {
    processRow(row);
}

The pooled client is released automatically on end, error, close, or iterator completion.

Requires optional peer dependencies: pg-query-stream (streams), pg-cursor (cursors).

Schema Helpers

import { createDatabase, id, timestamp } from "@oneunit/database";

const db = createDatabase();

await db.schema.create.table("users", {
    ...id(),
    email: "VARCHAR(255) NOT NULL",
    status: "VARCHAR(50) DEFAULT 'active'",
    ...timestamp("created_at", "updated_at"),
});

await db.schema.create.index("idx_users_email", "users", ["email"]);
await db.schema.constrain.unique("users", "uq_users_email", ["email"]);
await db.schema.alter.addColumns("users", { bio: "TEXT" });
await db.schema.drop.table("users", { cascade: true });

Migrations

await db.migrate.run({
    migrations: [
        {
            id: "001_users",
            up: "CREATE TABLE users (id uuid PRIMARY KEY DEFAULT gen_random_uuid())",
            down: "DROP TABLE users",
        },
    ],
});

await db.migrate.status({ directory: "./migrations" });
await db.migrate.rollback({ directory: "./migrations", steps: 1 });

SQL files and ESM/CJS modules in a directory are loaded in filename order. Each migration runs inside its own transaction under an advisory lock.

Health Checks & Shutdown

// Detailed health info for readiness probes
const health = await db.check();
// { status: "up", latency: { value: 2, unit: "ms" }, pool: { total, idle, waiting } }

// Simple boolean health check
const ready = await db.health();
if (!ready.healthy) {
    throw new Error(ready.error);
}

// Graceful shutdown (idempotent, safe to call concurrently)
process.on("SIGTERM", async () => {
    await db.shutdown();
    process.exit(0);
});

Error Handling

Driver errors are wrapped as DatabaseError with the original preserved on .cause.

import {
    DatabaseError,
    TimeoutError,
    isUniqueViolation,
    isTransientError,
} from "@oneunit/database";

try {
    await db.query("INSERT INTO users (email) VALUES ($1)", [email]);
} catch (error) {
    if (isUniqueViolation(error)) {
        return { conflict: true };
    }
    throw error;
}

Available error helpers:

Logging & Metrics

Pass any logger with info / warn / error / debug methods. Parameter values are not logged unless logParameters: true.

const db = createDatabase({
    logger: console,
    instrumentation: {
        logQueries: true,
        slowQueryMs: 200,
        onQuery({ durationMs, rowCount }) {
            metrics.timing("db.query", durationMs, { rowCount });
        },
    },
});

// Snapshot current metrics
db.metrics.snapshot();

TypeScript

The package ships with full TypeScript declarations. ESM-only ("type": "module").

import { createDatabase } from "@oneunit/database";

interface User {
    id: string;
    email: string;
}

const db = createDatabase();
const user = await db.queryOne<User>(
    "SELECT id, email FROM users WHERE id = $1",
    [id],
);

Sub-path Exports

For tree-shaking or targeted imports, the package exposes granular entry points:

import { createClient }       from "@oneunit/database/client";
import { createModelFactory } from "@oneunit/database/model";
import { createQueryBuilder }  from "@oneunit/database/query";
import { createSchemaManager } from "@oneunit/database/schema";
import { id, timestamp }       from "@oneunit/database/column";
import { encode, decode }      from "@oneunit/database/json-serialization";

Examples

Runnable examples live in the examples/ directory:

DATABASE_URL=postgresql://localhost:5432/app node examples/basic.js

Testing

npm test

Tests use Node's built-in test runner and do not require a live PostgreSQL instance.

Contributing

See CONTRIBUTING.md for development setup, coding standards, and the PR workflow.