@crane-technologies/database

PostgreSQL database component with connection pooling, transactions, dependency management, and TypeScript query autocomplete.

Features

• Full autocomplete with createQueries()
• Automatic connection pooling
• Manual ACID transactions via ITransaction
• Support for flat, nested, and mixed queries
• Bulk inserts with COPY ... FROM STDIN
• Raw SQL when you need it
• Backward compatible with existing code
• Configurable logs (4 levels)
• 100% TypeScript with complete types

Installation

npm install @crane-technologies/database

Quick Start

With createQueries() (Recommended)

import Database, {
  createQueries,
  DatabaseConfig,
} from "@crane-technologies/database";

// 1. Create queries with autocomplete
const queries = createQueries({
  users: {
    getById: "SELECT * FROM users WHERE id = $1",
    create: "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *",
    update: "UPDATE users SET name = $1 WHERE id = $2 RETURNING *",
    delete: "DELETE FROM users WHERE id = $1",
  },
  products: {
    getAll: "SELECT * FROM products",
    getById: "SELECT * FROM products WHERE id = $1",
  },
});

// 2. Configure database
const config: DatabaseConfig = {
  connectionString: process.env.DATABASE_URL!,
  ssl: { rejectUnauthorized: false },
  max: 10,
  logLevel: 2, // 0=NONE, 1=ERROR, 2=DEBUG, 3=ALL
};

// 3. Initialize
const db = Database.getInstance(config, queries);

// 4. Use with autocomplete!
const user = await db.query(queries.users.getById, [123]);
const newUser = await db.query(queries.users.create, [
  "John",
  "[email protected]",
]);
const products = await db.query(queries.products.getAll);

Use Cases

1. Flat Queries (no nesting)

const queries = createQueries({
  getUser: "SELECT * FROM users WHERE id = $1",
  createUser: "INSERT INTO users (name) VALUES ($1) RETURNING *",
  deleteUser: "DELETE FROM users WHERE id = $1",
});

const db = Database.getInstance(config, queries);

await db.query(queries.getUser, [123]);
await db.query(queries.createUser, ["Alice"]);
await db.query(queries.deleteUser, [456]);

2. Nested Queries (domain-organized)

const queries = createQueries({
  users: {
    getById: "SELECT * FROM users WHERE id = $1",
    create: "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *",
    update: "UPDATE users SET name = $1 WHERE id = $2 RETURNING *",
  },
  products: {
    getAll: "SELECT * FROM products",
    getById: "SELECT * FROM products WHERE id = $1",
    create: "INSERT INTO products (name, price) VALUES ($1, $2) RETURNING *",
  },
});

const db = Database.getInstance(config, queries);

// Autocomplete works at all levels!
await db.query(queries.users.getById, [123]);
await db.query(queries.products.create, ["Laptop", 999.99]);

3. Mixed Queries (flat + nested)

const queries = createQueries({
  // Flat queries for simple things
  testConnection: "SELECT NOW() as time",
  checkHealth: "SELECT 1",

  // Nested queries for complex domains
  users: {
    getById: "SELECT * FROM users WHERE id = $1",
    create: "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *",
  },
  products: {
    getAll: "SELECT * FROM products",
  },
});

const db = Database.getInstance(config, queries);

// Flat
await db.query(queries.testConnection);
await db.query(queries.checkHealth);

// Nested
await db.query(queries.users.getById, [123]);
await db.query(queries.products.getAll);

4. Raw SQL (when you need it)

const result = await db.rawQuery(
  "SELECT COUNT(*) FROM users WHERE active = $1",
  [true],
);
const count = result.rows[0].count;

5. Manual Transactions

Use db.transaction() to get full control over a transaction. The returned ITransaction object lets you run queries, bulk inserts, and raw SQL atomically, with explicit commit() and rollback().

const txn = await db.transaction();
try {
  // Execute multiple operations atomically
  const userRes = await txn.query(queries.users.create, [
    "Alice",
    "[email protected]",
  ]);
  const userId = userRes.rows[0].id;

  // Bulk insert within the same transaction
  await txn.bulkInsert(
    "items",
    ["user_id", "name"],
    [
      [userId, "Item 1"],
      [userId, "Item 2"],
    ],
  );

  // Raw SQL within the same transaction
  const check = await txn.rawQuery(
    "SELECT COUNT(*) FROM items WHERE user_id = $1",
    [userId],
  );

  await txn.commit();
} catch (error) {
  await txn.rollback();
  throw error;
}

6. Bulk Insert

const result = await db.bulkInsert(
  "users",
  ["name", "email"],
  [
    ["Alice", "[email protected]"],
    ["Bob", "[email protected]"],
  ],
  { header: false, returnFields: ["id", "email"] },
);

console.log(`Inserted: ${result.inserted}`);
console.log("Returned fields:", result.fields);

Log Levels

export type LogLevel = 0 | 1 | 2 | 3;

// 0 = NONE   - No logs
// 1 = ERROR  - Only errors
// 2 = DEBUG  - Errors + debug
// 3 = ALL    - All logs (errors + debug + warnings + info)

const config: DatabaseConfig = {
  connectionString: process.env.DATABASE_URL!,
  logLevel: 2, // DEBUG
};

// Change dynamically
db.setLogLevel(1); // Only errors

Backward Compatibility

The package is 100% compatible with existing code:

// Old style (still works)
const db = Database.getInstance(config, {
  getUser: "SELECT * FROM users WHERE id = $1",
});
await db.query("getUser", [123]);

// New style (with autocomplete)
const queries = createQueries({
  users: {
    getById: "SELECT * FROM users WHERE id = $1",
  },
});
const db = Database.getInstance(config, queries);
await db.query(queries.users.getById, [123]);

TypeScript

The package includes complete type definitions:

import {
  Database,
  createQueries,
  QueryReference,
  DatabaseConfig,
  ITransaction,
  Logger,
  LogLevel,
  QueryList,
} from "@crane-technologies/database";

Generic Query Typing

All query methods support TypeScript generics for typed results:

type UserSession = { id: number; email: string };

const result = await db.query<UserSession>(queries.users.getById, [123]);
const session = result.rows[0]; // session is UserSession

// Also works in transactions
const txn = await db.transaction();
const res = await txn.query<UserSession>(queries.users.getById, [123]);

This works for all query methods:

• db.query<T>()
• db.rawQuery<T>()
• db.queryList<T>()
• Transaction: txn.query<T>(), txn.rawQuery<T>()

If you omit <T>, the result will be any (default).

API Reference

Database.getInstance(config, queries)

Gets the singleton Database instance.

Parameters:

• config: DatabaseConfig - Connection configuration
• queries?: any - Result of createQueries() or plain object

Returns: Database

const db = Database.getInstance(config, queries);

db.query(query, params?)

Executes a query (with QueryReference, string key, or raw SQL).

Parameters:

• query: string | QueryReference - Query to execute
• params?: any[] - Query parameters

Returns: Promise<QueryResult<T>>

// With QueryReference
await db.query(queries.users.getById, [123]);

// With string key
await db.query("users.getById", [123]);

// Raw SQL
await db.query("SELECT * FROM users WHERE id = $1", [123]);

db.rawQuery(sqlText, params?)

Executes raw SQL directly, bypassing the query dictionary.

Parameters:

• sqlText: string - Raw SQL string
• params?: any[] - Query parameters

Returns: Promise<QueryResult<T>>

const result = await db.rawQuery(
  "SELECT COUNT(*) FROM users WHERE active = $1",
  [true],
);

db.transaction()

Begins a manual transaction. Returns an ITransaction object for full control over the transaction lifecycle.

Returns: Promise<ITransaction>

ITransaction methods:

| Method | Description | | -------------------------------------------- | ---------------------------------- | | query<T>(query, params?) | Execute a predefined or raw query | | rawQuery<T>(sqlText, params?) | Execute raw SQL | | bulkInsert(table, columns, rows, options?) | Bulk insert within the transaction | | commit() | Commit the transaction | | rollback() | Roll back the transaction |

Operations on a finalized transaction (after commit or rollback) will throw an error.

const txn = await db.transaction();
try {
  await txn.query(queries.users.create, ["Alice", "[email protected]"]);
  await txn.commit();
} catch (error) {
  await txn.rollback();
  throw error;
}

db.bulkInsert(table, columns, rows, options?)

Performs a bulk insert using PostgreSQL's COPY ... FROM STDIN. Significantly faster than multiple INSERT statements.

Parameters:

• table: string - Target table name
• columns: string[] - Columns to insert
• rows: (string | number | boolean | Date | null | undefined)[][] - Array of row values
• options?: BulkInsertOptions & { returnFields?: string[] } - CSV formatting and fields to return

Returns: Promise<{ inserted: number; fields?: any[] }>

const result = await db.bulkInsert(
  "users",
  ["name", "email"],
  [
    ["Alice", "[email protected]"],
    ["Bob", "[email protected]"],
  ],
  { header: false, returnFields: ["id", "email"] },
);
// result.inserted === 2
// result.fields === [{ id: 1, email: "[email protected]" }, ...]

db.setLogLevel(level)

Dynamically changes the log level.

Parameters:

• level: 0 | 1 | 2 | 3 - New log level

db.setLogLevel(1); // Only errors

db.close()

Closes all pool connections and resets the singleton, allowing re-initialization.

Returns: Promise<void>

process.on("SIGTERM", async () => {
  await db.close();
  process.exit(0);
});

Changelog

v3.3.0

• NEW: db.transaction() now returns an ITransaction object for full manual control over the transaction lifecycle.
  • txn.query(), txn.rawQuery(), txn.bulkInsert(), txn.commit(), txn.rollback()
  • Operations after commit()/rollback() throw an error to prevent misuse.
• NEW: ITransaction interface exported from the package.
• NEW: interfaces/index.ts barrel export for all interfaces.

v3.2.0

• NEW: Generic typing for query results. All query methods (query, rawQuery, queryList, transaction) now support TypeScript generics for typed rows.
  • Example: const result = await db.query<User>(queries.users.getById, [id]);

v3.1.0

• NEW: bulkInsert() now supports returnFields option to return specified columns from inserted rows.

v3.0.0

• NEW: bulkInsert() method for high-performance bulk data loading using PostgreSQL's native COPY ... FROM STDIN.
• Bulk insert supports:
  • Streaming large datasets as CSV directly to the database.
  • Custom CSV delimiter and optional header row via BulkInsertOptions.
  • Automatic quoting of table and column identifiers for safety.
  • Full TypeScript typings for all parameters and options.

v2.0.0

• NEW: createQueries() with full autocomplete
• NEW: Support for flat, nested, and mixed queries
• NEW: QueryReference type for better DX
• Backward compatible with v1.x.x

v1.0.0

• Initial release
• Connection pooling
• Transactions with dependencies
• Configurable logging system

License

MIT

Contributing

Issues and Pull Requests are welcome at: https://github.com/Crane/database