IntrospeQL

IntrospeQL reads information about the schemas, tables, columns, functions, and enums in your PostgreSQL database and produces a TypeScript file detailing type information for each database object.

Installation

Install IntrospeQL as a dev dependency:

npm install --save-dev introspeql

Quick Start

Install tsx and @types/node:

npm install --save-dev tsx @types/node

Install dotenv:

npm install --save dotenv

Add the following entry to the scripts object in package.json:

"gen-types": "npx tsx scripts/gen-types.ts"

Create a scripts directory at the root level of your project, and inside this folder, create a new TypeScript file called gen-types.ts.

Add the following content to scripts/gen-types.ts:

import 'dotenv/config';
import path from 'node:path';
import { introspeql, type IntrospeQLConfig } from 'introspeql';

const outFile = '/generated/type-definitions.ts';

const config: IntrospeQLConfig = {
  schemas: ['public'], // Add other schemas as necessary
  dbConnectionParams: {
    host: process.env.DB_HOST,
    port: +process.env.DB_PORT!,
    database: process.env.DB_NAME,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
  },
  writeToDisk: true,
  outFile: path.join(__dirname, '..' + outFile),
  header: 
    '/* This file has been generated by IntrospeQL and should not be edited directly. */',
  types: {
    'pg_catalog.void': 'void'
  }
};

genTypes();

async function genTypes() {
  await introspeql(config);
  console.log("Type definition file created at " + outFile);
}

Create a .gitignore file at the root of your project and make sure it includes node_modules and .env:

# .gitignore
node_modules/
.env

Create a .env file at the root of your project and add your database connection information:

# .env
DB_HOST= # Add your host here, often just localhost
DB_PORT= # Add your port here
DB_NAME= # Add the name of the database you wish to connect to here
DB_USER= # Add the PostgreSQL username that should be used to connect to the db
DB_PASSWORD= # Add the password for this user here

Execute your script:

npm run gen-types

A type definition file will have been generated at generated/type-definitions.ts.

⚠️ Important
Never hard code sensitive information directly into your project. Instead, load such information from environment variables and ensure that .env files are not committed to source control.

Output

The type definitions file consists of nested namespaces. Each top-level namespace represents a schema and contains the name of the schema as well as type definitions for its tables, functions, and enums, grouped by category into nested namespaces. Each table and function, in turn, consists of a namespace.

Table namespaces contain the name of the table, a union of string literals representing the names of the columns of the table, and an object-type representing the structure of each row.

Function namespaces contain the name of the function and an array of overloads. Each overload contains an array of parameter types and (separately from the array of parameter types) the return type.

Enums are represented as unions of string literals, with each string literal representing one possible value of the enum.

A header can be added to the file. This is useful to import types that will be used later in the file. In the example, we simply added a comment to the top of the file indicating that the file was autogenerated and should not be changed manually.

Configuration

Root-Level Configuration

The following options exist directly on the top-level configuration object.

| Option | Type | Required | Default | Description | | -------------------- | ------------------------ | ----------- | ------------ | ----------------------------------------------------------------------------------------------- | | schemas | string[] | No | ['public'] | PostgreSQL schemas to introspect. At least one schema must be specified. | | header | string | No | — | Text inserted at the top of the generated output file (e.g., additional type imports or definitions). | | types | Record<string, string> | No | — | Custom PostgreSQL-to-TypeScript type mappings that override built-in defaults. These should be specified in the format <schema>.<type>, e.g. 'pg_catalog.int8' | | copyComments | boolean | No | true | Whether database comments are copied into generated TypeScript documentation comments. | | dbConnectionString | string | Conditional | — | PostgreSQL connection string. Exactly one of this or dbConnectionParams must be provided. | | dbConnectionParams | object | Conditional | — | PostgreSQL connection parameters. Exactly one of this or dbConnectionString must be provided. | | writeToDisk | boolean | Yes | — | Controls whether generated output is written to disk. | | outFile | string | Conditional | — | Output file path. Required when writeToDisk is true. | | tables | TableOptions | No | — | Table filtering configuration. | | functions | FunctionOptions | No | — | Function filtering and typing configuration. |

dbConnectionParams

| Option | Type | Required | Description | | ---------- | -------- | -------- | ------------------- | | user | string | No | Database user name. | | password | string | No | Database password. | | host | string | No | Database host. | | port | number | No | Database port. | | database | string | No | Database name. |

Table Options

Controls which database tables are included in the generated output.

| Option | Type | Required | Default | Description | | --------------- | ---------------------------- | -------- | ------------- | --------------------------------------------------- | | mode | 'inclusive' \| 'exclusive' | No | 'inclusive' | Determines how table filtering is applied. | | excludeTables | string[] | No | [] | Tables to exclude when operating in inclusive mode. | | includeTables | string[] | No | [] | Tables to include when operating in exclusive mode. |

Function Options

Controls which PostgreSQL functions are included and how their types are generated.

| Option | Type | Required | Default | Description | | --------------------- | ---------------------------- | -------- | ------------- | ----------------------------------------------------------- | | mode | 'inclusive' \| 'exclusive' | No | 'inclusive' | Determines how function filtering is applied. | | excludeFunctions | string[] | No | [] | Functions to exclude when operating in inclusive mode. | | includeFunctions | string[] | No | [] | Functions to include when operating in exclusive mode. | | nullableArgs | boolean | No | false | Treat function arguments as nullable in generated types. | | nullableReturnTypes | boolean | No | true | Treat function return types as nullable in generated types. |

Types

Default type mappings are based on the types produced by node-postgres. These defaults can be added to or overridden using configuration options described above. Type definitions for enums will be generated automatically, provided that the enum is used somewhere in a table or function definition.

To define custom types, use the header to import or define them and use the types configuration option to map PostgreSQL types to your custom types.

Multi-dimensional-array-type columns are recognized. Columns to which a NOT NULL constraint has been applied will be non-nullable. Optional and variadic function parameters are recognized.

By default, node-postgres will transform the return value of a function that returns void into an empty string. Therefore, by default, the generated TypeScript return type of PostgreSQL functions that return void will be string. If desired, this can be overridden using the types configuration option.

The default value for unrecognized types is string.

Functions

Only functions whose prokind is 'f' in pg_catalog.pg_proc are recognized (i.e. normal functions, not procedures or aggregate functions).

Function overloads are recognized.

Directives

IntrospeQL supports the inclusion of several directives which, when included in a PostgreSQL comment, can modify how IntrospeQL interacts with the database object to which that comment is applied.

Directives are case-insensitive but must be separated from each other and from other text by a whitespace character (a space, newline, etc).

Here is an example of adding a directive to the config_key column of a table called config in the reporting schema:

COMMENT ON COLUMN reporting.config.config_key IS
'@introspeql-enable-tsdoc-comments
Unique configuration key.';

Below, please find a table of all valid directives:

| Category | Directive | Effect | | -------- | ----------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Objects | @introspeql-exclude | Excludes a table or function overload when mode is 'inclusive' | | Objects | @introspeql-include | Includes a table or function overload when mode is 'exclusive' | | Types | @introspeql-enable-nullable-args | Makes each parameter of the function overload to which it is applied nullable when nullableArgs is false in config.functions | | Types | @introspeql-disable-nullable-args | Makes each parameter of the function overload to which it is applied non-nullable when nullableArgs is true in config.functions | | Types | @introspeql-enable-nullable-return-types | Makes the return type of the function overload to which it is applied nullable when nullableReturnTypes is false in config.functions | | Types | @introspeql-disable-nullable-return-types | Makes the return type of the function overload to which it is applied non-nullable when nullableReturnTypes is true in config.functions | | Comments | @introspeql-enable-tsdoc-comments | Copies the comments of the table, column, or enum to which it is applied even when config.copyComments is false or is an array that does not include the given database object type. | | Comments | @introspeql-disable-tsdoc-comments | Ignores the comments of the table, column, or enum to which it is applied even when config.copyComments is true or is an array that includes the given database object type. | | Comments | @introspeql-begin-tsdoc-comment | Copies a portion of a PostgreSQL comment into the generated type definition file beginning after this directive and ending at the next @introspeql-end-tsdoc-comment directive or at the end of the comment. | | Comments | @introspeql-end-tsdoc-comment | Omits a portion of a PostgreSQL comment from the generated type definition file beginning after this directive and ending at the next @introspeql-begin-tsdoc-comment directive or at the end of the comment. |

The package also exports a TypeScript enum of all valid directives:

import { Directives } from 'introspeql';

Comments

Comments can be applied to PostgreSQL database objects with the following syntax:

COMMENT ON COLUMN auth.users.user_id IS
'Unique identifier for the user.';

If IntrospeQL is configured to copy the comments for a given table, enum, or column, PostgreSQL comments applied to that database object will be copied into the type definition file as TSDoc comments.

Note that all @introspeql- directives will always be removed when the copied comment is formatted.

Comments cannot contain */ or a combination of characters that results in */ after directives are removed. Such comments will trigger a warning and will not be included in the output.

Assuming comment copying is enabled for a particular database object, you can use the @introspeql-begin-tsdoc-comment and @introspeql-end-tsdoc-comment directives to copy only certain excerpts of the comment:

No Directive

If neither @introspeql-begin-tsdoc-comment nor @introspeql-end-tsdoc-comment is included, the entire comment will be copied.

Using Only @introspeql-begin-tsdoc-comment

If only @introspeql-begin-tsdoc-comment is included, everything before the directive will be excluded, and everything after it will be included.

Using Only @introspeql-end-tsdoc-comment

If only @introspeql-end-tsdoc-comment is included, everything before the directive will be included, and everything after it will be excluded.

Using Both Directives

You can alternate @introspeql-begin-tsdoc-comment and @introspeql-end-tsdoc-comment.

You can begin or end with either directive, but you cannot include multiple instances of the same directive in a row within the same comment (in this case, a warning will be printed to the console and the comment will not be copied).

When alternating between directives, content before @introspeql-end-tsdoc-comment or after @introspeql-begin-tsdoc-comment will be included and all other content will be excluded.

Contributing

If you notice a bug or would like to request a feature, please submit an issue.

License

MIT License

Copyright (c) 2025 Joseph Dvorak

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.