@crane-technologies/database

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

Features

• Full autocomplete with createQueries()
• Automatic connection pooling
• ACID transactions with query dependencies
• Support for flat, nested, and mixed queries
• 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)

// Raw SQL still works
const result = await db.query("SELECT COUNT(*) FROM users WHERE active = $1", [
  true,
]);
const count = result.rows[0].count;

5. Simple Transactions

const results = await db.transaction(
  [queries.users.create, queries.users.create],
  [
    ["Alice", "[email protected]"],
    ["Bob", "[email protected]"],
  ],
);

console.log("Users created:", results.length);

6. Transactions with Dependencies

import { Dependency } from "@crane-technologies/database";

const queries = createQueries({
  users: {
    create: "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *",
  },
  profiles: {
    create: "INSERT INTO profiles (user_id, bio) VALUES ($1, $2) RETURNING *",
  },
});

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

// The user ID is automatically injected into the profile
const dependencies: Dependency[] = [
  { sourceIndex: 0, targetIndex: 1, targetParamIndex: 0 },
];

const results = await db.transaction(
  [queries.users.create, queries.profiles.create],
  [
    ["John", "[email protected]"],
    [null, "John's bio"], // null will be replaced by user.id
  ],
  dependencies,
);

console.log("User created:", results[0].rows[0]);
console.log("Profile created:", results[1].rows[0]);

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,
  Dependency,
  Logger,
  LogLevel,
  QueryList,
} from "@crane-technologies/database";

API Reference

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

Performs a bulk insert using PostgreSQL's COPY ... FROM STDIN. Optionally returns specified fields from inserted rows.

Parameters:

• table: string - Target table name
• columns: string[] - Columns to insert
• rows: any[][] - Array of row values
• options?: BulkInsertOptions & { returnFields?: string[] } - CSV formatting and fields to return

Returns: { inserted: number, fields?: any[] }

Example:

const result = await db.bulkInsert(
  "users",
  ["name", "email"],
  [
    ["Alice", "[email protected]"],
    ["Bob", "[email protected]"],
  ],
  { header: false, returnFields: ["id", "email"] },
);
// result.fields will be an array of objects with the requested fields

createQueries(queries)

Creates a query object with autocomplete.

Parameters:

• queries: Record<string, any> - Object with queries (flat, nested, or mixed)

Returns: Object with queries and internal __flatMap

Example:

const queries = createQueries({
  users: {
    getById: "SELECT * FROM users WHERE id = $1",
  },
});

Database.getInstance(config, queries)

Gets the singleton Database instance.

Parameters:

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

Returns: Database

Example:

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>

Examples:

// 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.transaction(queryArray, paramsArray, dependencies?)

Executes multiple queries in an atomic transaction.

Parameters:

• queryArray: (string | QueryReference)[] - Array of queries
• paramsArray: any[][] - Parameters for each query
• dependencies?: Dependency[] - Dependencies between queries

Returns: Promise<QueryResult[]>

Example:

await db.transaction(
  [queries.users.create, queries.profiles.create],
  [
    ["John", "[email protected]"],
    [null, "Bio"],
  ],
  [{ sourceIndex: 0, targetIndex: 1, targetParamIndex: 0 }],
);

db.close()

Closes all pool connections.

Returns: Promise<void>

Example:

await db.close();

db.setLogLevel(level)

Dynamically changes the log level.

Parameters:

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

Example:

db.setLogLevel(1); // Only errors

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

Uses COPY … FROM STDIN to insert many records natively.

Parameters:

• table: target table name
• columns: columns to populate (ordered)
• rows: matrix with values (each row can have null)
• options?: BulkInsertOptions to set delimiter and add header

Example:

await db.bulkInsert(
  "test_users",
  ["name", "email"],
  [
    ["Bulk 1", "[email protected]"],
    ["Bulk 2", "[email protected]"],
  ],
  { header: false },
);

🔒 Closing Connections

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

📄 License

MIT

🤝 Contributing

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

📝 Changelog

v3.0.0 (Current)

• ✨ 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.
  • Efficient handling of large inserts compared to multiple INSERT statements.
• 📚 Documentation and usage examples for bulkInsert() added.

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
• 📚 Updated documentation with examples

v1.0.0

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