pg2ts

Generate TypeScript types from your PostgreSQL database schema. Zero runtime overhead, just pure type generation.

Why?

• No runtime dependencies — Types are generated at build time
• Lightweight — Single file, no complex setup
• Practical — Also generates Insert types with proper optional fields
• Enum Support — PostgreSQL enums become TypeScript union types
• Zod Support — Generate runtime-validated Zod schemas
• Drizzle ORM — Generate complete Drizzle schema with relations
• JSDoc Comments — Preserves PostgreSQL COMMENT as JSDoc
• Agent-Friendly — JSON output for automation pipelines
• Watch Mode — Auto-regenerate on schema changes

Install

pip install psycopg2-binary
# or: pip install psycopg2

Usage

# Using connection URL
./pg2ts.py --url "postgresql://user:pass@localhost:5432/mydb" -o types.ts

# Using individual params
./pg2ts.py -H localhost -d mydb -U postgres -o types.ts

# Multiple schemas
./pg2ts.py --url "..." --schemas public,app -o types.ts

Output Example

Given this table:

CREATE TYPE status AS ENUM ('active', 'inactive', 'pending');

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    name TEXT,
    status status DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE users IS 'User account information';
COMMENT ON COLUMN users.id IS 'Primary key';
COMMENT ON COLUMN users.email IS 'User email address';

You get:

// Enum types
export type Status = 'active' | 'inactive' | 'pending';
export const StatusValues = ['active', 'inactive', 'pending'] as const;

/** User account information */
export interface Users {
  /** Primary key */
  id: number;
  /** User email address */
  email: string;
  name?: string;
  status?: Status;  // Uses the enum type!
  created_at?: string;
}

export type UsersInsert = {
  /** User email address */
  email: string;
  /** Primary key */
  id?: number;
  name?: string;
  status?: Status;
  created_at?: string;
};

Features

Enum Support

PostgreSQL enums are automatically converted to TypeScript union types:

CREATE TYPE status AS ENUM ('active', 'inactive', 'pending');
CREATE TYPE priority AS ENUM ('low', 'medium', 'high');

Generated TypeScript:

// Enum types
export type Status = 'active' | 'inactive' | 'pending';
export const StatusValues = ['active', 'inactive', 'pending'] as const;

export type Priority = 'low' | 'medium' | 'high';
export const PriorityValues = ['low', 'medium', 'high'] as const;

// In interfaces, columns use the enum type
export interface Tasks {
  id: number;
  status: Status;      // Not string!
  priority: Priority;  // Not string!
}

With --zod flag, enums also get Zod schemas:

export type Status = 'active' | 'inactive' | 'pending';
export const StatusValues = ['active', 'inactive', 'pending'] as const;
export const StatusSchema = z.enum(['active', 'inactive', 'pending']);

Drizzle ORM Schema Generation (--drizzle)

Generate a complete Drizzle ORM schema instead of plain interfaces:

./pg2ts.py --url "..." --drizzle -o schema.ts

Output:

import { pgTable, pgEnum, serial, text, varchar, integer, timestamp, boolean } from 'drizzle-orm/pg-core';

// Enums
export const statusEnum = pgEnum('status', ['active', 'inactive', 'pending']);

// Tables
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull(),
  name: text('name'),
  status: statusEnum('status').default('active'),
  createdAt: timestamp('created_at').defaultNow(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  userId: integer('user_id').references(() => users.id),
  title: text('title').notNull(),
  published: boolean('published').default(false),
});

// Inferred types
export type Users = typeof users.$inferSelect;
export type UsersInsert = typeof users.$inferInsert;
export type Posts = typeof posts.$inferSelect;
export type PostsInsert = typeof posts.$inferInsert;

Drizzle type mapping:

| PostgreSQL | Drizzle | |------------|---------| | serial | serial() | | integer, int4 | integer() | | bigint, int8 | bigint({ mode: 'number' }) | | varchar(n) | varchar('col', { length: n }) | | text | text() | | boolean | boolean() | | timestamp, timestamptz | timestamp() | | date | date() | | json | json() | | jsonb | jsonb() | | uuid | uuid() | | numeric(p,s) | numeric({ precision: p, scale: s }) |

Drizzle features:

• Primary keys → .primaryKey()
• NOT NULL → .notNull()
• DEFAULT values → .default() / .defaultNow() / .defaultRandom()
• Foreign keys → .references(() => table.column)
• Enums → pgEnum() definitions

Zod Schema Generation (--zod)

Generate Zod schemas for runtime validation alongside TypeScript types:

./pg2ts.py --url "..." --zod -o types.ts

Output:

import { z } from 'zod';

// Enum types
export type Status = 'active' | 'inactive' | 'pending';
export const StatusValues = ['active', 'inactive', 'pending'] as const;
export const StatusSchema = z.enum(['active', 'inactive', 'pending']);

/** User account information */
export const UsersSchema = z.object({
  /** Primary key */
  id: z.number(),
  /** User email address */
  email: z.string(),
  name: z.string().nullable(),
  status: StatusSchema.nullable(),
  created_at: z.string().nullable(),
});

export type Users = z.infer<typeof UsersSchema>;

export const UsersInsertSchema = z.object({
  /** User email address */
  email: z.string(),
  /** Primary key */
  id: z.number().optional(),
  name: z.string().nullable().optional(),
  status: StatusSchema.nullable().optional(),
  created_at: z.string().nullable().optional(),
});

export type UsersInsert = z.infer<typeof UsersInsertSchema>;

Use --zod-dates to generate z.coerce.date() instead of z.string() for date/timestamp columns:

./pg2ts.py --url "..." --zod --zod-dates -o types.ts

Table Metadata (--with-metadata)

Generate table metadata exports for runtime introspection:

./pg2ts.py --url "..." --with-metadata -o types.ts

Output:

export const usersTable = {
  tableName: 'users',
  columns: ['id', 'email', 'name', 'status', 'created_at'] as const,
  requiredForInsert: ['email'] as const,
} as const;

export const tables = {
  users: usersTable,
} as const;

JSON Output (--json)

Get machine-readable metadata about generated types (useful for CI/CD and agent pipelines):

./pg2ts.py --url "..." --json -o types.ts

Output:

{
  "tables": [
    {
      "name": "users",
      "schema": "public",
      "columns": ["id", "email", "name", "status", "created_at"],
      "required": ["email"],
      "optional": ["id", "name", "status", "created_at"],
      "comment": "User account information"
    }
  ],
  "enums": [
    {"name": "status", "values": ["active", "inactive", "pending"]}
  ],
  "enums_count": 1,
  "types_generated": 2,
  "output_file": "types.ts"
}

Watch Mode (--watch / -w)

Automatically regenerate types when database schema changes:

./pg2ts.py --url "..." --watch -o types.ts

# Custom interval (default: 5 seconds)
./pg2ts.py --url "..." --watch --watch-interval 10 -o types.ts

Press Ctrl+C to stop watching.

JSDoc Comments from PostgreSQL

PostgreSQL COMMENT statements are automatically converted to JSDoc comments:

COMMENT ON TABLE users IS 'User account information';
COMMENT ON COLUMN users.email IS 'User email address';

This works automatically with all output modes (interfaces, Zod, Drizzle, metadata).

Type Mapping

| PostgreSQL | TypeScript | Zod | |------------|------------|-----| | integer, bigint, real, etc. | number | z.number() | | varchar, text | string | z.string() | | uuid | string | z.string().uuid() | | boolean | boolean | z.boolean() | | timestamp, date, time | string | z.string() (or z.coerce.date() with --zod-dates) | | json, jsonb | unknown | z.unknown() | | bytea | Buffer | z.instanceof(Buffer) | | Arrays (_type) | type[] | z.array(...) | | Enums | union type | z.enum() |

CLI Reference

Usage: pg2ts.py [OPTIONS]

Connection:
  --url URL                  PostgreSQL connection URL
  -H, --host HOST           Database host (default: localhost)
  -p, --port PORT           Database port (default: 5432)
  -d, --database DATABASE   Database name
  -U, --user USER           Database user (default: postgres)
  -W, --password PASSWORD   Database password

Output:
  -o, --output FILE         Output file (default: stdout)
  --schemas SCHEMAS         Comma-separated schemas (default: public)
  --include-schema          Include schema name in interface names

Features:
  --json                    Output JSON metadata about generated types
  --with-metadata           Generate table metadata exports
  --zod                     Generate Zod schemas instead of plain interfaces
  --zod-dates               Use z.date() for date/timestamp types (requires --zod)
  --drizzle                 Generate Drizzle ORM schema (mutually exclusive with --zod)
  -w, --watch               Watch for schema changes and regenerate
  --watch-interval SECONDS  Watch interval in seconds (default: 5)

Examples

# Basic usage
./pg2ts.py --url "postgresql://user:pass@localhost/mydb" -o types.ts

# Generate Drizzle ORM schema
./pg2ts.py --url "..." --drizzle -o schema.ts

# Generate Zod schemas with date coercion
./pg2ts.py --url "..." --zod --zod-dates -o schema.ts

# Generate with metadata for ORM-like usage
./pg2ts.py --url "..." --with-metadata -o types.ts

# CI/CD: Generate and get JSON report
./pg2ts.py --url "..." --json -o types.ts > report.json

# Development: Watch for changes
./pg2ts.py --url "..." --watch --watch-interval 3 -o types.ts

# All features combined (except drizzle)
./pg2ts.py --url "..." --zod --with-metadata --json -o types.ts

License

MIT