PostgreSQL Database Structure Reverse Engineer

Common errors

• Error: connect ECONNREFUSED 127.0.0.1:5432

  cause The PostgreSQL server is not running, is not accessible at the specified host/port, or firewall rules are blocking the connection.
  fix Ensure the PostgreSQL server is running and listening on the correct host and port. Verify network connectivity and any relevant firewall settings. Double-check `host` and `port` in your connection configuration.

• Error: database "non_existent_db" does not exist

  cause The database name provided in the connection configuration does not correspond to an existing database on the PostgreSQL server.
  fix Correct the `database` name in your connection configuration to match an existing PostgreSQL database. If the database is truly missing, create it first.

• TypeError: Cannot read properties of undefined (reading 'columns')

  cause This error typically occurs when `db.get('table_name')` returns `undefined`, meaning the requested table was not found in the introspected schema or was explicitly excluded.
  fix Verify that the table name is correct and exists in the database. Check the `includeSchemas` and `includeTables` options passed to `pgStructure` to ensure the table is part of the introspection scope. Add null/undefined checks before accessing properties like `.columns`.

• Error: Cannot find module 'pg-structure'

  cause The `pg-structure` package has not been installed in your project's dependencies, or there's an issue with module resolution.
  fix Run `npm install pg-structure` or `yarn add pg-structure` to add the package to your project. Ensure your project's `tsconfig.json` (for TypeScript) or build configuration correctly resolves modules.

Warnings

• breaking Major version updates, such as the jump to v7, likely introduce breaking changes to the API, reflecting architectural updates or internal refactorings. Developers should consult the full changelog on GitHub when upgrading across major versions to identify specific API changes.
  Fix: Refer to the specific major version upgrade guide (if available) or the GitHub changelog for detailed migration steps and API changes.
• gotcha Introspecting a large PostgreSQL database can be memory and time-intensive, especially for schemas with numerous tables, columns, or complex relations. This can impact application startup time or execution of schema analysis tasks.
  Fix: Utilize the `includeSchemas` and `includeTables` options during introspection to limit the scope to only the necessary parts of the database. Consider caching the generated structure in production environments.
• gotcha A bug in older versions (prior to 7.15.3) caused issues with correctly escaping underscores in schema names, potentially leading to unintended schemas being ignored during introspection.
  Fix: Upgrade to `pg-structure@7.15.3` or a newer version to ensure that schema names containing underscores are correctly handled and introspected.
• gotcha Directly embedding database credentials in code or plain environment variables can pose security risks. Secure handling of sensitive connection information is critical to prevent data breaches.
  Fix: Always use secure methods for managing credentials, such as dedicated secrets management services (e.g., AWS Secrets Manager, HashiCorp Vault), encrypted configuration files, or robust environment variable management. Avoid hardcoding credentials in source code.
• gotcha Older versions (prior to 7.13.1) had a bug that prevented correct introspection of PostgreSQL triggers that did not include a `WHEN` clause, leading to incomplete schema information.
  Fix: Upgrade to `pg-structure@7.13.1` or newer to ensure accurate and complete introspection of all PostgreSQL triggers, regardless of their `WHEN` clause status.

Install

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

Imports

• pgStructure
  wrong:

  import { pgStructure } from 'pg-structure';

  correct:

  import pgStructure from 'pg-structure';

  pg-structure is primarily consumed as a default export, providing the main function to initiate database introspection.
• Db
  wrong:

  import { Db } from 'pg-structure';

  correct:

  import type { Db } from 'pg-structure';

  The 'Db' symbol represents the main database object returned by pgStructure, providing access to schemas, tables, and other elements. It's typically imported as a type.
• Table
  wrong:

  import { Table } from 'pg-structure';

  correct:

  import type { Table } from 'pg-structure';

  The 'Table' symbol represents a database table object within the introspected structure. It's commonly imported as a type for explicit type hinting.

Quickstart

This example connects to a PostgreSQL database, reverse engineers its structure, and demonstrates how to access specific tables, columns, their types, indexes, and relationships using the programmatic API.

import pgStructure from "pg-structure";
import type { Db, Table } from "pg-structure";

async function demo() {
  // It's highly recommended to use environment variables for sensitive credentials.
  // Example uses process.env for security best practices.
  const connectionConfig = {
    host: process.env.PG_HOST ?? "localhost",
    database: process.env.PG_DATABASE ?? "your_db_name",
    user: process.env.PG_USER ?? "your_username",
    password: process.env.PG_PASSWORD ?? "your_password",
    port: parseInt(process.env.PG_PORT ?? "5432", 10),
  };

  try {
    // Establish a connection and reverse engineer the database structure
    const db: Db = await pgStructure(connectionConfig, { includeSchemas: ["public"] });

    // Access a specific table by its name
    const contactTable: Table | undefined = db.get("contact");

    if (contactTable) {
      console.log(`Successfully introspected table: ${contactTable.name}`);

      // Get column names for the 'contact' table
      const columnNames = contactTable.columns.map((c) => c.name);
      console.log("Column Names for 'contact' table:", columnNames);

      // Get the type name of a specific column (e.g., 'options' column)
      const optionsColumn = contactTable.columns.get("options");
      if (optionsColumn) {
        console.log(`Type name of 'options' column: ${optionsColumn.type.name}`);
      }

      // Get columns involved in a specific index (e.g., 'ix_mail' index)
      const ixMailIndex = contactTable.indexes.get("ix_mail");
      if (ixMailIndex) {
        const indexColumnNames = ixMailIndex.columns.map(c => c.name);
        console.log("Columns in 'ix_mail' index:", indexColumnNames);
      }

      // Get tables related to 'contact' via hasMany relationship
      const relatedTables = contactTable.hasManyTables;
      console.log("Tables related to 'contact' via hasMany:", relatedTables.map(t => t.name));
    } else {
      console.log("Table 'contact' not found or not included in introspection.");
    }
  } catch (error) {
    console.error("Failed to connect or introspect database:", error);
  }
}

demo();