Postgres to TypeScript Schema Generator

4.1.1 verified Thu Apr 23 auth: no javascript

pg-to-ts is a utility that generates TypeScript interfaces and constants directly from your PostgreSQL database schema. It queries the `pg_catalog` metadata to produce `dbschema.ts` files, providing static type checking for database interactions and runtime constants for constructing queries. The current stable version is 4.1.1, with releases occurring as new features are developed or improvements are made, such as the recent addition of schema-prefixing and type links in runtime values. This package is a focused fork of the earlier `schemats` project, distinguishing itself by dropping MySQL support for deeper, modernized Postgres-specific features, improved infrastructure, and incorporating JSDoc comments from SQL. It offers flexible typing for `Date` and `JSON` columns, allowing developers to manage serialization concerns or integrate custom JSON types.

Common errors

error Property 'name' is missing in type '{ id: string; description: string; }' but required in type 'ProductInput'. ↓

cause Attempting to create a record without providing all non-nullable, non-defaulted columns required for insertion (as defined by the generated `TableInput` interface, e.g., `ProductInput`).

fix

Ensure all properties listed in the generated TableInput interface that are not marked as optional (?) are provided when inserting new records. Check the requiredForInsert property on the generated runtime table object for clarity.

error Type 'Date' is not assignable to type 'string'. ↓

cause This error occurs when the generated schema uses `string` for date/timestamp columns (due to `--datesAsStrings`), but the database driver (e.g., `node-postgres`) is returning `Date` objects.

fix

Either remove the --datesAsStrings flag during generation to use Date objects in your types, or configure your database driver's type parsers to serialize date/timestamp columns as ISO 8601 strings to match the generated types.

error Property 'metadata' does not exist on type 'Product'. Did you mean 'name'? ↓

cause Trying to access a JSON/JSONB column without configuring `pg-to-ts` to use a specific TypeScript type for it, resulting in the column being typed as `unknown` (which might not be explicitly reflected as a property) or a less precise type.

fix

Add a JSDoc @type comment to the JSON column in your SQL schema (e.g., COMMENT ON COLUMN product.metadata IS '@type {ProductMetadata}') and use the --jsonTypesFile './path/to/my-db-types' option when generating your schema file.

Warnings

gotcha PostgreSQL 'Date' and 'timestamp' columns default to `Date` objects in generated types, but can be configured as `string` types using `--datesAsStrings`. If this flag is used, you are responsible for ensuring that your database driver (e.g., `node-postgres`) is configured to return these columns as strings, otherwise runtime type mismatches will occur. ↓

fix To use strings, configure your `node-postgres` `types` to parse dates as strings. Alternatively, remove `--datesAsStrings` to use `Date` objects, which is the default and often safer for direct date handling.

gotcha JSON and JSONB columns are typed as `unknown` by default. To provide specific TypeScript types, you must use JSDoc `@type` annotations in your SQL comments (e.g., `COMMENT ON COLUMN mytable.data IS '@type {MyJsonType}'`) and pass the `--jsonTypesFile` flag to `pg-to-ts`. ↓

fix Add JSDoc `@type` comments to your SQL schema for JSON columns and use the `--jsonTypesFile './path/to/types'` flag during generation. Ensure the specified file exports the necessary types.

gotcha Using the `--prefixWithSchemaNames` option (introduced in 4.1.0) will alter the generated symbol names by adding the schema name as a prefix (e.g., `public_Product` instead of `Product`). Enabling this after initial setup can introduce breaking changes in existing TypeScript code that references the original symbol names. ↓

fix Update all import paths and symbol usages in your application code to reflect the schema-prefixed names (e.g., `import { public_Product } from './dbschema'`), or ensure this option is consistently used from the start of a project.

Install

npm install pg-to-ts

yarn add pg-to-ts

pnpm add pg-to-ts

Imports

Product
wrong
```
const Product = require('./dbschema').Product;
```
correct
```
import { Product } from './dbschema';
```
Output files are ESM modules, use `import` syntax. The path './dbschema' is an example, replace with your actual output file name.
TableTypes
wrong
```
import type { TableTypes } from 'pg-to-ts';
```
correct
```
import { TableTypes } from './dbschema';
```
Interface containing a map of all table types. Imported from your generated schema file, not the `pg-to-ts` package itself.
tables
wrong
```
import { product } from './dbschema';
```
correct
```
import { tables } from './dbschema';
```
A runtime constant object containing metadata for all tables, where individual table metadata (like `product`) is accessed as a property (e.g., `tables.product`).

Quickstart

Demonstrates how to generate TypeScript types from a PostgreSQL schema using `pg-to-ts`, then use these generated types and runtime constants with `node-postgres` to insert and query data safely. Note: requires `pg` installed.

import { Pool } from 'pg';
import { Product, ProductInput, tables } from './dbschema'; // Assuming this is your generated file

// Step 1: Generate your schema file (dbschema.ts)
// Run this command once after database schema changes:
// npm install pg-to-ts
// pg-to-ts generate -c "postgresql://user:password@host:5432/mydb" -o ./dbschema.ts

// For this example, we assume dbschema.ts has already been generated based on a 'product' table:
/*
// Example content of dbschema.ts:
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 const tables = {
  product
};
*/

async function main() {
  // Step 2: Use the generated types in your application
  const connectionString = process.env.DATABASE_URL ?? 'postgresql://user:pass@localhost:5432/mydb';
  const pool = new Pool({ connectionString });

  try {
    const newProduct: ProductInput = {
      name: 'Example Product',
      description: 'A product generated by pg-to-ts quickstart.'
    };

    // Use runtime constants for query construction, ensuring type safety
    const insertColumns = tables.product.requiredForInsert.join(', ');
    const placeholders = tables.product.requiredForInsert.map((_, i) => `$${i + 1}`).join(', ');
    const insertValues = tables.product.requiredForInsert.map(key => newProduct[key]);

    const insertRes = await pool.query<Product>(
      `INSERT INTO ${tables.product.tableName} (${insertColumns}) VALUES (${placeholders}) RETURNING *`,
      insertValues
    );
    const createdProduct = insertRes.rows[0];
    console.log('Created product:', createdProduct);

    // Fetch all products using the generated type
    const selectRes = await pool.query<Product>(`SELECT * FROM ${tables.product.tableName}`);
    const products: Product[] = selectRes.rows;
    console.log('All products:', products.map(p => p.name));

  } catch (error) {
    console.error('Database operation failed:', error);
  } finally {
    await pool.end();
  }
}

main();