{"id":16485,"library":"pg-structure","title":"PostgreSQL Database Structure Reverse Engineer","description":"pg-structure is a TypeScript-first library designed to reverse engineer a PostgreSQL database schema into a detailed JavaScript object structure. It provides an API to programmatically access and navigate details about databases, schemas, tables, columns, foreign keys, relations, indexes, and custom types. The current stable version is 7.15.3. Releases are somewhat infrequent, with several bug fix releases in 2024 and feature additions in 2023 and prior years, indicating active maintenance. Its key differentiator is offering a comprehensive, introspected object model of the entire PostgreSQL schema, which is useful for ORM generators, database documentation tools, schema analysis scripts, or custom code generation, rather than just basic table listings. It handles complex PostgreSQL features like custom types and generated columns.","status":"active","version":"7.15.3","language":"javascript","source_language":"en","source_url":"https://github.com/ozum/pg-structure","tags":["javascript","postgresql","reverse engineer","pg","structure","database","object","typescript"],"install":[{"cmd":"npm install pg-structure","lang":"bash","label":"npm"},{"cmd":"yarn add pg-structure","lang":"bash","label":"yarn"},{"cmd":"pnpm add pg-structure","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Required for connecting to and querying PostgreSQL databases.","package":"pg","optional":false}],"imports":[{"note":"pg-structure is primarily consumed as a default export, providing the main function to initiate database introspection.","wrong":"import { pgStructure } from 'pg-structure';","symbol":"pgStructure","correct":"import pgStructure from 'pg-structure';"},{"note":"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.","wrong":"import { Db } from 'pg-structure';","symbol":"Db","correct":"import type { Db } from 'pg-structure';"},{"note":"The 'Table' symbol represents a database table object within the introspected structure. It's commonly imported as a type for explicit type hinting.","wrong":"import { Table } from 'pg-structure';","symbol":"Table","correct":"import type { Table } from 'pg-structure';"}],"quickstart":{"code":"import pgStructure from \"pg-structure\";\nimport type { Db, Table } from \"pg-structure\";\n\nasync function demo() {\n  // It's highly recommended to use environment variables for sensitive credentials.\n  // Example uses process.env for security best practices.\n  const connectionConfig = {\n    host: process.env.PG_HOST ?? \"localhost\",\n    database: process.env.PG_DATABASE ?? \"your_db_name\",\n    user: process.env.PG_USER ?? \"your_username\",\n    password: process.env.PG_PASSWORD ?? \"your_password\",\n    port: parseInt(process.env.PG_PORT ?? \"5432\", 10),\n  };\n\n  try {\n    // Establish a connection and reverse engineer the database structure\n    const db: Db = await pgStructure(connectionConfig, { includeSchemas: [\"public\"] });\n\n    // Access a specific table by its name\n    const contactTable: Table | undefined = db.get(\"contact\");\n\n    if (contactTable) {\n      console.log(`Successfully introspected table: ${contactTable.name}`);\n\n      // Get column names for the 'contact' table\n      const columnNames = contactTable.columns.map((c) => c.name);\n      console.log(\"Column Names for 'contact' table:\", columnNames);\n\n      // Get the type name of a specific column (e.g., 'options' column)\n      const optionsColumn = contactTable.columns.get(\"options\");\n      if (optionsColumn) {\n        console.log(`Type name of 'options' column: ${optionsColumn.type.name}`);\n      }\n\n      // Get columns involved in a specific index (e.g., 'ix_mail' index)\n      const ixMailIndex = contactTable.indexes.get(\"ix_mail\");\n      if (ixMailIndex) {\n        const indexColumnNames = ixMailIndex.columns.map(c => c.name);\n        console.log(\"Columns in 'ix_mail' index:\", indexColumnNames);\n      }\n\n      // Get tables related to 'contact' via hasMany relationship\n      const relatedTables = contactTable.hasManyTables;\n      console.log(\"Tables related to 'contact' via hasMany:\", relatedTables.map(t => t.name));\n    } else {\n      console.log(\"Table 'contact' not found or not included in introspection.\");\n    }\n  } catch (error) {\n    console.error(\"Failed to connect or introspect database:\", error);\n  }\n}\n\ndemo();","lang":"typescript","description":"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."},"warnings":[{"fix":"Refer to the specific major version upgrade guide (if available) or the GitHub changelog for detailed migration steps and API changes.","message":"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.","severity":"breaking","affected_versions":">=7.0.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":"*"},{"fix":"Upgrade to `pg-structure@7.15.3` or a newer version to ensure that schema names containing underscores are correctly handled and introspected.","message":"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.","severity":"gotcha","affected_versions":"<7.15.3"},{"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.","message":"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.","severity":"gotcha","affected_versions":"*"},{"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.","message":"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.","severity":"gotcha","affected_versions":"<7.13.1"}],"env_vars":null,"last_verified":"2026-04-22T00:00:00.000Z","next_check":"2026-07-21T00:00:00.000Z","problems":[{"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.","cause":"The PostgreSQL server is not running, is not accessible at the specified host/port, or firewall rules are blocking the connection.","error":"Error: connect ECONNREFUSED 127.0.0.1:5432"},{"fix":"Correct the `database` name in your connection configuration to match an existing PostgreSQL database. If the database is truly missing, create it first.","cause":"The database name provided in the connection configuration does not correspond to an existing database on the PostgreSQL server.","error":"Error: database \"non_existent_db\" does not exist"},{"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`.","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.","error":"TypeError: Cannot read properties of undefined (reading 'columns')"},{"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.","cause":"The `pg-structure` package has not been installed in your project's dependencies, or there's an issue with module resolution.","error":"Error: Cannot find module 'pg-structure'"}],"ecosystem":"npm"}