pg-introspection

pg-introspection

A strongly-typed PostgreSQL introspection library for PostgreSQL built automatically from the PostgreSQL system catalog documentation, with the TypeScript documentation for each attribute/type also pulled from the PostgreSQL documentation for easy reference directly in your editor.

Crowd-funded open-source software

To help us develop this software sustainably, we ask all individuals and businesses that use it to help support its ongoing maintenance and development via sponsorship.

Click here to find out more about sponsors and sponsorship.

And please give some love to our featured sponsors 🤩:

The Guild *	Dovetail *	Stellate *	Steelhead *
LatchBio *

* Sponsors the entire Graphile suite

Usage

Issue the makeIntrospectionQuery() SQL query to your database, then feed the first row's introspection field into parseIntrospectionResults() to get your strongly typed introspection results.

Example usage with pg module:

import {
  makeIntrospectionQuery,
  parseIntrospectionResults,
} from "pg-introspection";
import { Pool } from "pg";

const pool = new Pool({
  connectionString: "postgres://localhost:5432/my_database",
});

async function main() {
  const sql = makeIntrospectionQuery();
  const { rows } = await pool.query(sql);
  const introspection = parseIntrospectionResults(rows[0].introspection);

  console.log(
    `The ${introspection.database.datname} DBA is ${
      introspection.database.getDba()?.rolname ?? "-"
    }`,
  );
}

main();

Accessors

Into the introspection results we mix "accessor" functions to make following relationships easier. Note that these functions are typically evaluated lazily - the first time you call them they may need to do an expensive lookup (e.g. finding the relevant record from the list of records) but they cache the result so that the next call will be near-instant.

Examples:

const myTable = introspection.classes.find((rel) => rel.relname === "my_table");
const myTableAttributes = myTable.getAttributes();
const myColumn = myTable.getAttribute({ name: "my_column" });
const myColumnDescription = myColumn.getDescription();

You can use the TypeScript autocompletion to see what accessors are available, or look in the index.ts file.

Naming

Using the PostgreSQL column names is by design, even though some are hard to read if you're not familiar with the system catalog.

We use _id rather than oid because older versions of PostgreSQL did not explicitly list the oid columns when you select * from so we explicitly list them among the selection set.