Pg-typegen NPM

Generate TypeScript type definitions from Postgres database.

Install

$ npm install -D pg-typegen
# or
$ yarn add -D pg-typegen
# or
$ pnpm add -D pg-typegen

Usage

Usage: pg-typegen [options] <connection>

Options:
  -V, --version              output the version number
  -f, --suffix  <suffix>     suffix to append to generated table type, e.g. item -> ItemEntity (default: "Entity")
  -s, --schema  <schema>     schema (default: "public")
  -h, --header  <header>     header content (default: "")
  -o, --output  <output>     file output path (default: "stdout")
  -e, --exclude <exclude>    excluded tables and enums as comma separated string e.g. knex_migrations,knex_migrations_lock (default: [])
  --type                     use type definitions instead of interfaces in generated output (default: false)
  --semicolons               use semicolons in generated types (default: false)
  --ssl                      use ssl (default: false)
  --optionals                use optionals "?" instead of null (default: false)
  --comments                 generate table and column comments (default: false)
  --pascal-enums             transform enum keys to pascal case (default: false)
  --bigint                   use bigint for int8 types instead of strings (default: false)
  --date-as-string           use string for date types instead of javascript Date object (default: false)
  --insert-types             generate separate insert types with optional fields for columns allowing NULL value or having default values (default: false)
  --table-names              generate string literal type with all table names (default: false)
  --help                     display help for command

Example:
  $ pg-typegen -o ./entities.ts postgres://username:password@localhost:5432/database

Given database table

CREATE TYPE user_state AS ENUM (
  'asleep',
  'awake'
);

CREATE TABLE users (
    id int4 NOT NULL,
    name varchar(255),
    state user_state,
    is_enabled bool NOT NULL DEFAULT FALSE
);

Running pg-typegen -o ./entities.ts postgres://username:password@localhost:5432/database Will generate the following type definitions

enum UserState {
  asleep = 'asleep',
  awake = 'awake'
}

interface UserEntity {
  id: number;
  name: string | null;
  state: UserState | null;
  is_enabled: boolean;
}

By default, the types will be generated based on how pg returns the values.

Insert types

To simplify database inserts, separate types can be generated with optional values where NULL is allowed or default values for column exist in postgres.

Given database table

CREATE TYPE user_state AS ENUM (
  'asleep',
  'awake'
);

CREATE TABLE users (
    id serial4 PRIMARY KEY,
    name varchar(255) NOT NULL,
    state user_state,
    is_enabled bool NOT NULL DEFAULT FALSE
);

Running pg-typegen -o ./entities.ts --insert-types postgres://username:password@localhost:5432/database Will generate the following type definitions

enum UserState {
  asleep = 'asleep',
  awake = 'awake'
}

interface UserEntity {
  id: number;
  name: string;
  state: UserState | null;
  is_enabled: boolean;
}

interface UserInsertEntity {
  id?: number;
  name: string;
  state?: UserState | null;
  is_enabled?: boolean;
}

Which should allow working with insert objects without having to define all optional and nullable fields.

const user: UserInsertEntity = { name: 'foo' }
knex<UserInsertEntity>('users').insert(user)

Running from code

import { join } from 'path'
import generate from 'pg-typegen'
;(async () => {
  const output = join(__dirname, 'entities.ts')
  await generate({ connection: 'postgres://username:password@localhost:5432/database', output })
})()

Loading database config

from .env file

export $(grep -v '^#' .env | xargs) && pg-typegen -o ./entities.ts postgres://$DB_USERNAME:$DB_PASSWORD@$DB_HOST:$DB_PORT/$DB_NAME

from json file

pg-typegen -o ./entities.ts postgres://$(jq -r '.DB.USERNAME' config.json):$(jq -r '.DB.PASSWORD' config.json)@$(jq -r '.DB.HOST' config.json):$(jq -r '.DB.PORT' config.json)/$(jq -r '.DB.NAME' config.json)