danvk / pg-to-ts

Generate TypeScript interface definitions from your Postgres schema

pg-to-ts

pg-to-ts generates TypeScript types that match your Postgres database schema. It works by querying the Postgres metadata schema (pg_catalog) and generating equivalent TypeScript types, as well as some JavaScript values that can be helpful for generating queries at runtime.

Usage:

npm install pg-to-ts
pg-to-ts generate -c postgresql://user:pass@host/db -o dbschema.ts

The resulting file looks like:

// Table product
export interface Product {
  id: string;
  name: string;
  description: string;
  created_at: Date;
}
export interface ProductInput {
  id?: string;
  name: string;
  description: string;
  created_at?: Date;
}
const product = {
  tableName: 'product',
  columns: ['id', 'name', 'description', 'created_at'],
  requiredForInsert: ['name', 'description'],
} as const;

export interface TableTypes {
  product: {
    select: Product;
    input: ProductInput;
  };
}

export const tables = {
  product,
};

This gives you most of the types you need for static analysis and runtime.

This is a fork of PYST/schemats, which is a fork of SweetIQ/schemats. Compared to those projects, this fork:

• Drops support for MySQL in favor of deeper support for Postgres.
• Significantly modernizes the infrastructure and dependencies.
• Adds a few new features (see below).

Schema Features

Comments

If you set a Postgres comment on a table or column:

COMMENT ON TABLE product IS 'Table containing products';
COMMENT ON COLUMN product.name IS 'Human-readable product name';

Then these come out as JSDoc comments in the schema:

/** Table containing products */
export interface Product {
  id: string;
  /** Human-readable product name */
  name: string;
  description: string;
  created_at: Date;
}

The TypeScript language service will surface these when it's helpful.

Dates as strings

node-postgres returns timestamp columns as JavaScript Date objects. This makes a lot of sense, but it can lead to problems if you try to serialize them as JSON, which converts them to strings. This means that the serialized and de- serialized table types will be different.

By default pg-to-ts will put Date types in your schema file, but if you'd prefer strings, pass --datesAsStrings. Note that you'll be responsible for making sure that timestamps/dates really do come back as strings, not Date objects. See https://github.com/brianc/node-pg-types for details.

JSON types

By default, Postgres json and jsonb columns will be typed as unknown. This is safe but not very precise, and it can make them cumbersome to work with. Oftentimes you know what the type should be.

To tell pg-to-ts to use a specific TypeScript type for a json column, use a JSDoc @type annotation:

ALTER TABLE product ADD COLUMN metadata jsonb;
COMMENT ON COLUMN product.metadata is 'Additional information @type {ProductMetadata}';

On its own, this simply acts as documentation. But if you also specify the --jsonTypesFile flag, these annotations get incorporated into the schema:

pg-to-ts generate ... --jsonTypesFile './db-types' -o dbschema.ts

Then your dbschema.ts will look like:

import {ProductMetadata} from './db-types';

interface Product {
  id: string;
  name: string;
  description: string;
  created_at: Date;
  metadata: ProductMetadata | null;
}

Presumably your db-types.ts file will either re-export this type from elsewhere:

export {ProductMetadata} from './path/to/this-type';

or define it itself:

export interface ProductMetadata {
  year?: number;
  designer?: string;
  starRating?: number;
}

Note that, on its own, TypeScript cannot enforce a schema on your json columns. For that, you'll want a tool like postgres-json-schema.

Prefix tableNames with their corresponding schemaName

--prefixWithSchemaNames

It will prefix all exports with the schema name. i.e schemaname_tablename. This allows you to easily namespace your exports.

If the schema name is: maxi, then the following exports will be generated for you when using the --prefixWithSchemaNames:

// Table product
export interface MaxiProduct {
  id: string;
  name: string;
  description: string;
  created_at: Date;
}
export interface MaxiProductInput {
  id?: string;
  name: string;
  description: string;
  created_at?: Date;
}
const maxi_product = {
  tableName: 'maxi.product',
  columns: ['id', 'name', 'description', 'created_at'],
  requiredForInsert: ['name', 'description'],
} as const;

export interface TableTypes {
  maxi_product: {
    select: MaxiProduct;
    input: MaxiProductInput;
  };
}

export const tables = {
  maxi_product,
};

Command Line Usage

There are a few ways to control pg-to-ts:

Command line flags

pg-to-ts generate -c postgresql://user:pass@host/db -o dbschema.ts

JS / JSON file

pg-to-ts generate --config path/to/config.json
pg-to-ts generate --config  # defaults to pg-to-ts.json
cat pg-to-ts.json

The JSON file has configuration options as top-level keys:

{
  "conn": "postgres://user@localhost:5432/postgres",
  "output": "/tmp/cli-pg-to-ts-json.ts"
}

Environment variables

Flags may also be specified using environment variables prefixed with PG_TO_TS:

PG_TO_TS_CONN=postgres://user@localhost:5432/postgres
PG_TO_TS_OUTPUT=/tmp/cli-env.ts
pg-to-ts generate

Development Quickstart

You'll need a Postgres instance running to do most development work with pg-to-ts.

git clone https://github.com/danvk/pg-to-ts.git
cd pg-to-ts
yarn
yarn build

You can iterate using your own DB schema. Or, to load the test schema, run:

psql postgres://user:pass@host/postgres -a -f test/fixture/pg-to-ts.sql

Then generate a dbschema.ts file by running:

node dist/cli.js generate -c postgresql://user:pass@host/db -o dbschema.ts

You can use yarn build --watch to run tsc in watch mode.

To run the unit tests:

yarn build
POSTGRES_URL=postgres://user@localhost:5432/postgres yarn test

To run ESLint:

yarn lint

See SweetIQ/schemats for the original README.

pg-to-ts's Issues

Support multiple schemas, similar to multiple tables

Allow passing multiple -s and generating several schemas at once into the same file, similar to the table flag. currently have to run the command multiple times for different schemas and since they aren't aware of each other can sometimes cause issues.

possible issue not directly related but could be solved with this feature:
this could be a bug in type generation, where its seeing the type but not finding it in other schemas (just creates a ts file with errors) but if I could just generate -s public -s custom, they would be in the same fine and it would be fine. Not sure which is correct.

Support @optional annotations

Sometimes a column is optional for insert but pg-to-ts isn't able to tell that from the schema (e.g. it's filled in by a trigger). For cases like this it would be helpful to support an @optional annotation on the column comment that overrides the default.

Rework tooling

• Switch to yarn
• Switch to eslint
• CircleCI or GitHub Actions
• Add an integration test that exercises some of the features from this fork (dates as strings, @type)
• Generate .js / .js.map files in a dist directory
• Remove postinstall hook
• Add code coverage
• prettier for formatting
• Switch from Mocha → Jest
• Remove postgres/ subdirectories
• Rationalize test names
• Drop I prefix from interfaces
• Enable recommended eslint rules
• Add back an integration test akin to https://github.com/PSYT/schemats/blob/master/test/fixture/postgres/usecase.ts

Connection string fails with special characters in password.

This database server is being used with node-postgres perfectly with same credentials.
on remote server postgresql://myuser:Ex@mp!#[email protected]/mydbname fails
on local docker container postgresql://myuser:administrator@localhost:5431/mydbname works fine
As you can see, the same user and database name is used, yet, when on the local machine when using "administrator" as a password without any special characters, then db connects just fine.

Is there any way to escape special characters?

Support @type annotations for text columns

If you have a varchar / text column that actually corresponds to a particular TS type (a union of string literal types) then it could be useful to annotate it with an @type comment, similar to how we do JSON types.

Support for materialized views

I tried to use this against my database and it looks pretty good, but none of the materialized views are coming through. I think the reason is most of the queries looking for the definitions of things are looking at information_schema.columns which won't include materialized views.

If these were updated to use similar queries referencing pg_catalog, they'd come through and for the most part do the correct thing. I've tried it out, and unfortunately, all the columns come through as nullable, but that's just how matviews work in postgres... (a matview will have all columns nullable even if the underlying query references a non-null column) can be worked around with alter table statements

Export type including all options (`OptionValues` fields as well as cli fields)

Configuring the tool via a JS should support typing the exported object with an accurate type exported by this module. The closest existing type currently seems to be OptionValues, which does not include all the fields that are available (e.g. conn, output).

There should be an exported type that includes all options.

Unable to use pg-to-ts with typescript 5.0.4

i cant add pg-to-ts to my project, because the typescript reference is too old and i dont want to force npm to do that.

is there any possbilty that these references can get updated?

Bug: composite primary keys are returned incorrectly

Tables with multiple fields as part of the primary key incorrectly report the primary key
consider:

CREATE TABLE public.friends (
	userid int8 NOT NULL,
	friendid int8 NOT NULL,
	relationship text not null,
  -- more columns
	CONSTRAINT friends_pkey PRIMARY KEY (userid, friendid),

Will incorrectly generate the following: (abbreviated for clarity)

const friends = {
  tableName: 'friends',
  columns: ['userid', 'friendid', 'relationship'],
  primaryKey: 'userid',  // should be ['userid', 'friendid']
} as const;

I had hoped to use your wonderful tool to automatically generate some extremely simple CRUD

friends_insert_one(); 
friends_update_one(userid, friendid, { relationship });
friends_get_one(userid, friendid);
friends_get_by_userid();   // primaryKey[0]
friends_get_by_friendid(); //primaryKey[1]

Support enum arrays

Apparently these get typed to any but they should get typed to EnumType[].

Feature request: Return table unique constraint

If there is a unique constraint on a table, we should return it in the const for each table.

const table = {
tableName:
columns:
requiredForInsert:
primaryKey:
foreignKeys:
uniqueConstraint/s
} as const;