{"id":17223,"library":"extract-pg-schema","title":"PostgreSQL Schema Extractor","description":"extract-pg-schema is a utility library designed to programmatically extract comprehensive metadata from a PostgreSQL database and return it as a structured JavaScript object. Currently stable at version 5.8.1, the package maintains an active release schedule, frequently delivering patch and minor updates to enhance features and ensure compatibility. It serves as a foundational component for other tools like Kanel, which leverages its output to generate TypeScript types, and Schemalint, used for database schema linting. A key differentiator is its ability to integrate seamlessly with standard `node-postgres` connection configurations and its provision of both a flexible programmatic API and a convenient command-line interface, making it adaptable for various use cases ranging from automated code generation to direct schema inspection.","status":"active","version":"5.8.1","language":"javascript","source_language":"en","source_url":"ssh://git@github.com/kristiandupont/extract-pg-schema","tags":["javascript","postgresql","schema","typescript"],"install":[{"cmd":"npm install extract-pg-schema","lang":"bash","label":"npm"},{"cmd":"yarn add extract-pg-schema","lang":"bash","label":"yarn"},{"cmd":"pnpm add extract-pg-schema","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"This package requires a compatible `node-postgres` (pg) client for database connectivity. While not always a direct dependency, users must ensure `pg` is installed in their project if directly using the library's functions. The library manages its internal `pg` dependency.","package":"pg","optional":true}],"imports":[{"note":"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.","wrong":"const { extractSchemas } = require('extract-pg-schema');","symbol":"extractSchemas","correct":"import { extractSchemas } from 'extract-pg-schema';"},{"note":"TypeScript types for configuration options are available for precise type checking and IDE auto-completion.","symbol":"ExtractSchemasOptions","correct":"import type { ExtractSchemasOptions } from 'extract-pg-schema';"},{"note":"TypeScript type representing the structure of the extracted PostgreSQL schema, useful for type-safe manipulation of the result object.","symbol":"PgSchema","correct":"import type { PgSchema } from 'extract-pg-schema';"}],"quickstart":{"code":"import { extractSchemas } from 'extract-pg-schema';\n\nasync function run() {\n  // Ensure your PostgreSQL server is running and accessible\n  // Environment variables (PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE)\n  // are also supported by node-postgres for connection configuration.\n  const connection = {\n    host: process.env.PGHOST ?? 'localhost',\n    port: parseInt(process.env.PGPORT ?? '5432', 10),\n    database: process.env.PGDATABASE ?? 'postgres',\n    user: process.env.PGUSER ?? 'postgres',\n    password: process.env.PGPASSWORD ?? 'postgres',\n  };\n\n  try {\n    console.log('Connecting to PostgreSQL database...');\n    const result = await extractSchemas(connection, {\n      includeSchemas: ['public'], // Only extract schemas matching 'public'\n      excludeTables: ['^pg_'], // Exclude tables starting with 'pg_'\n    });\n\n    console.log('Successfully extracted schemas.');\n    // console.log(JSON.stringify(result, null, 2)); // Uncomment to see full schema object\n\n    // Example: Accessing a specific table from the 'public' schema\n    const publicSchema = result.find(s => s.name === 'public');\n    if (publicSchema) {\n      const usersTable = publicSchema.tables.find(t => t.name === 'users');\n      if (usersTable) {\n        console.log(`Found 'users' table in 'public' schema with ${usersTable.columns.length} columns.`);\n      } else {\n        console.log(\"No 'users' table found in 'public' schema.\");\n      }\n    } else {\n      console.log(\"No 'public' schema found.\");\n    }\n  } catch (error) {\n    console.error('Failed to extract schema:', error.message);\n    process.exit(1);\n  }\n}\n\nrun();","lang":"typescript","description":"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."},"warnings":[{"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.","message":"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.","severity":"breaking","affected_versions":">=5.3.4"},{"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.","message":"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.","severity":"breaking","affected_versions":">=5.7.2"},{"fix":"If encountering issues related to `pglite`, install it explicitly: `npm install pglite` or `yarn add pglite`.","message":"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.","severity":"gotcha","affected_versions":">=5.7.4"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=5.0.0"}],"env_vars":null,"last_verified":"2026-04-22T00:00:00.000Z","next_check":"2026-07-21T00:00:00.000Z","problems":[{"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.","cause":"The PostgreSQL database server is not running or is not accessible at the specified host and port.","error":"Error: connect ECONNREFUSED 127.0.0.1:5432"},{"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.","cause":"The database name provided in the connection configuration does not correspond to an existing database on the server.","error":"Error: Database 'nonexistent_db' does not exist"},{"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.","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.","error":"TypeError: extractSchemas is not a function"}],"ecosystem":"npm","meta_description":null}