PostgreSQL Schema Extractor

Common errors

• Error: connect ECONNREFUSED 127.0.0.1:5432

  cause The PostgreSQL database server is not running or is not accessible at the specified host and port.
  fix Verify that your PostgreSQL server is running. Check your connection configuration (host, port, user, password) for accuracy. Ensure no firewall is blocking the connection to the database port.

• Error: Database 'nonexistent_db' does not exist

  cause The database name provided in the connection configuration does not correspond to an existing database on the server.
  fix Double-check the `database` property in your connection object or the `-d` option when using the CLI to ensure it matches an actual database name.

• TypeError: extractSchemas is not a function

  cause This typically occurs when attempting to `require` or incorrectly `import` the function from an ESM-first package in a CommonJS context, or if using incorrect named/default import syntax.
  fix For ESM projects (Node.js >=16, `"type": "module"` in `package.json`), use `import { extractSchemas } from 'extract-pg-schema';`. If strictly in CommonJS, you might need to ensure your environment supports dynamic `import()` or adjust your build system if direct `require` fails.

Warnings

• breaking Support for extracting window and aggregate functions was partially removed. If your application relied on detailed metadata for these function types, this change might affect your schema analysis.
  Fix: Review your usage of extracted function metadata. If specific details about window or aggregate functions are crucial, consider alternative methods for obtaining this information directly from PostgreSQL's `pg_catalog` or `information_schema` views.
• breaking The method for extracting triggers was changed in v5.7.2 to no longer rely on `information_schema`. While intended as a fix for certain edge cases, this change might alter the structure or completeness of trigger data for some database setups.
  Fix: After upgrading, re-evaluate the extracted trigger metadata for accuracy and completeness, especially if you have complex or non-standard trigger definitions. Adjust downstream processes that consume trigger data as necessary.
• gotcha Starting with version 5.7.4, `knex-pglite` was updated to declare `pglite` as a peer dependency. If you were implicitly relying on `extract-pg-schema` to provide `pglite` and are using features that leverage it (e.g., in testing environments), you might now need to explicitly install `pglite` in your project.
  Fix: If encountering issues related to `pglite`, install it explicitly: `npm install pglite` or `yarn add pglite`.
• gotcha The package maintains compatibility with Node.js >=16.0.0 and ships TypeScript types. While the README might show CommonJS `require()` examples, the primary and recommended way to import is using ESM `import` statements. Mixing ESM and CJS in some projects can lead to `TypeError: extractSchemas is not a function` or similar import errors.
  Fix: Ensure your project is configured for ESM (`"type": "module"` in `package.json`) and use `import { extractSchemas } from 'extract-pg-schema';`. If sticking with CJS, be aware of potential interoperability issues and ensure your build process handles module resolution correctly.

Install

• npm install extract-pg-schema npm
• yarn add extract-pg-schema yarn
• pnpm add extract-pg-schema pnpm

Imports

• extractSchemas
  wrong:

  const { extractSchemas } = require('extract-pg-schema');

  correct:

  import { extractSchemas } from 'extract-pg-schema';

  The package officially supports Node.js >=16.0.0 and primarily uses ESM. While CommonJS `require` might work via transpilation or dual packaging, direct ESM `import` is the recommended and most reliable method.
• ExtractSchemasOptions

  import type { ExtractSchemasOptions } from 'extract-pg-schema';

  TypeScript types for configuration options are available for precise type checking and IDE auto-completion.
• PgSchema

  import type { PgSchema } from 'extract-pg-schema';

  TypeScript type representing the structure of the extracted PostgreSQL schema, useful for type-safe manipulation of the result object.

Quickstart

This quickstart demonstrates how to connect to a PostgreSQL database using environment variables or a direct connection config, extract specific schemas, and then log basic information about the extracted schema, such as table and column counts.

import { extractSchemas } from 'extract-pg-schema';

async function run() {
  // Ensure your PostgreSQL server is running and accessible
  // Environment variables (PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE)
  // are also supported by node-postgres for connection configuration.
  const connection = {
    host: process.env.PGHOST ?? 'localhost',
    port: parseInt(process.env.PGPORT ?? '5432', 10),
    database: process.env.PGDATABASE ?? 'postgres',
    user: process.env.PGUSER ?? 'postgres',
    password: process.env.PGPASSWORD ?? 'postgres',
  };

  try {
    console.log('Connecting to PostgreSQL database...');
    const result = await extractSchemas(connection, {
      includeSchemas: ['public'], // Only extract schemas matching 'public'
      excludeTables: ['^pg_'], // Exclude tables starting with 'pg_'
    });

    console.log('Successfully extracted schemas.');
    // console.log(JSON.stringify(result, null, 2)); // Uncomment to see full schema object

    // Example: Accessing a specific table from the 'public' schema
    const publicSchema = result.find(s => s.name === 'public');
    if (publicSchema) {
      const usersTable = publicSchema.tables.find(t => t.name === 'users');
      if (usersTable) {
        console.log(`Found 'users' table in 'public' schema with ${usersTable.columns.length} columns.`);
      } else {
        console.log("No 'users' table found in 'public' schema.");
      }
    } else {
      console.log("No 'public' schema found.");
    }
  } catch (error) {
    console.error('Failed to extract schema:', error.message);
    process.exit(1);
  }
}

run();