PostgreSQL Driver

@hedystia/db supports native PostgreSQL databases. This driver is ideal for production-grade applications requiring ACID compliance, advanced indexing, and JSONB support.

Installation

Install the pg package as a peer dependency:

bun add pg
# or
bun add @types/pg --dev

Configuration

To use the PostgreSQL driver, specify "postgres" as the database name and provide a connection object with host, port, user, password, and database.

import { database } from "@hedystia/db";
import { users } from "./schema";

const db = database({
  schemas: { users },
  database: {
    name: "postgres",
    provider: "pg",
  },
  connection: {
    host: "localhost",
    port: 5432,
    user: "postgres",
    password: "password",
    database: "mydb",
  },
  syncSchemas: true,
});

await db.initialize();

Features

The PostgreSQL driver supports the full @hedystia/db feature set:

• Full CRUD operations (find, insert, update, delete, upsert)
• Transactions
• Query caching
• Schema synchronization
• Migrations
• Relations with eager loading
• Connection pooling (automatic via pg)

Indexes

The PostgreSQL driver supports creating and dropping indexes via migrations:

export const addEmailIndex = migration("users_email_index", {
  async up({ schema }) {
    await schema.addIndex("users", ["email"], true);
  },
  async down({ schema }) {
    await schema.dropIndex("users", "users_email_index");
  },
});

Dialect-Specific Behavior

The SQL compiler handles PostgreSQL-specific syntax:

• Quoted identifiers ("column") for case-sensitive table/column names
• $1, $2 parameter placeholders instead of ?
• GENERATED BY DEFAULT AS IDENTITY for auto-increment columns
• RETURNING clause for insert operations (preserves generated IDs)
• PostgreSQL-specific type handling

Connection Pooling

Connection pooling is handled automatically by the pg package. Call await db.close() to cleanly terminate the pool on application shutdown.