pg-god: PostgreSQL Database Management

Common errors

• Password authentication failed for user "your_user"

  cause The PostgreSQL server rejected the connection attempt due to incorrect credentials (username or password) or an invalid entry in `pg_hba.conf`.
  fix Ensure the `user` and `password` in your `DbCredential` object (or CLI flags/environment variables) match the PostgreSQL server configuration. Verify `pg_hba.conf` allows connections from your host with the specified authentication method.

• database "your-db-name" does not exist

  cause You are attempting to drop a database that does not exist, and `errorIfNonExist` is set to `true` (or implied by CLI usage).
  fix If it's acceptable for the database not to exist, set `errorIfNonExist: false` in the `dropDatabase` call. Otherwise, ensure the database exists before attempting to drop it.

• cannot drop a database that is currently in use

  cause Despite the default behavior since v1.0.6, this error can still occur if `dropConnections: false` is explicitly set, and there are active connections to the database you are trying to drop.
  fix Ensure `dropConnections: true` (the default since v1.0.6) is used when calling `dropDatabase`. If this is not an option, you must manually terminate all connections to the database before dropping it.

Warnings

• breaking The default behavior for `dropDatabase` (both API and CLI `db-drop` command) changed in v1.0.6. It now automatically kills all active connections to the target database before dropping it. Previously, this required manual intervention or would fail if connections existed.
  Fix: If you relied on `dropDatabase` failing when connections were active, you must now explicitly set `dropConnections: false` in the API call or use `--no-dropConnections` with the CLI. For most users, this change improves reliability and is the desired default.
• gotcha By default, `pg-god` uses 'postgres' for the user and 'localhost:5432' for the host/port, with an empty password. This often works for local development setups, but will likely cause 'Password authentication failed' errors in production or secured environments if not explicitly configured.
  Fix: Always provide explicit `DbCredential` objects to the API calls or use environment variables/CLI flags to specify correct user, password, host, and port for your PostgreSQL instance. For the CLI, consider using a connection URL with `--url`.
• gotcha When using `createDatabase` or `dropDatabase`, the `errorIfExist` (for create) and `errorIfNonExist` (for drop) options default to `false` for the programmatic API, meaning it will silently succeed even if the database state doesn't change. However, if set to `true`, attempting to create an existing DB or drop a non-existent DB will throw an error.
  Fix: Be mindful of these options. If you need strict behavior (e.g., ensuring a database is *only* created if it doesn't exist), set `errorIfExist: true`. For idempotent scripts where you don't care if the database exists or not, the defaults are usually fine.

Install

• npm install pg-god npm
• yarn add pg-god yarn
• pnpm add pg-god pnpm

Imports

• createDatabase
  wrong:

  const { createDatabase } = require('pg-god')

  correct:

  import { createDatabase } from 'pg-god'

  pg-god is primarily used with ES Modules due to its TypeScript origins and shipped types. While CommonJS might technically work, named imports are the idiomatic approach.
• dropDatabase
  wrong:

  const dropDatabase = require('pg-god').dropDatabase

  correct:

  import { dropDatabase } from 'pg-god'

  Use named imports for programmatic API functions.
• DbCredential
  wrong:

  import { DbCredential } from 'pg-god'

  correct:

  import type { DbCredential } from 'pg-god'

  DbCredential is a TypeScript type; use 'import type' for type-only imports to ensure it's stripped from the JavaScript output.

Quickstart

Demonstrates programmatic creation and immediate dropping of a PostgreSQL database using environment variables for credentials and handling potential errors.

import { createDatabase, dropDatabase, DbCredential } from 'pg-god';

async function main() {
  const dbConfig = { databaseName: 'my_app_test_db' };
  const credentials: Partial<DbCredential> = {
    user: process.env.PG_USER ?? 'postgres',
    password: process.env.PG_PASSWORD ?? '',
    host: process.env.PG_HOST ?? 'localhost',
    port: parseInt(process.env.PG_PORT ?? '5432', 10),
    database: process.env.PG_INITIAL_DB ?? 'postgres', // Initial database to connect to
  };

  try {
    console.log(`Attempting to create database: ${dbConfig.databaseName}`);
    await createDatabase(dbConfig, credentials);
    console.log(`Database '${dbConfig.databaseName}' created successfully.`);

    // --- Your application logic or tests would go here ---

    console.log(`Attempting to drop database: ${dbConfig.databaseName}`);
    // dropConnections: true is default since v1.0.6, but explicit for clarity
    await dropDatabase({ ...dbConfig, dropConnections: true }, credentials);
    console.log(`Database '${dbConfig.databaseName}' dropped successfully.`);
  } catch (error) {
    console.error('An error occurred during database operation:', error);
    // Attempt to clean up even if an error occurred after creation
    try {
        await dropDatabase({ ...dbConfig, dropConnections: true, errorIfNonExist: false }, credentials);
        console.log(`Attempted cleanup for database '${dbConfig.databaseName}'.`);
    } catch (cleanupError) {
        console.warn(`Failed to clean up database '${dbConfig.databaseName}':`, cleanupError);
    }
    process.exit(1);
  }
}

main();