gem-hunter
Language: TypeScript
translates postgres schema into typescript and zod
Generate Zod schemas and TypeScript types from PostgreSQL database schemas.
pg-to-ts is a CLI tool that connects to a PostgreSQL database, introspects its schema, and generates a TypeScript file containing both Zod schemas (for runtime validation) and TypeScript interfaces (for compile-time type safety). This ensures your application types stay synchronized with your database schema.
belongs-to and has-many relationships using z.lazy() for circular referencessnake_case database names to camelCase properties and PascalCase type namesz.lazy() to handle self-referential and circular table relationships# Clone the repository git clone <repository-url> cd pg-to-ts # Install dependencies pnpm install # Build the project pnpm run build
pg-to-ts \
--connection-string "postgresql://user:password@localhost:5432" \
--db my_database \
--output ./types/database.ts| Option | Short | Description | Required |
|---|---|---|---|
--connection-string |
-c |
PostgreSQL connection URL | Yes |
--db |
-d |
Database name | Yes |
--output |
-o |
Output file path for generated types | Yes |
--help |
-h |
Show help information | No |
--version |
-V |
Show version number | No |
Given a database with users and posts tables where posts reference users:
import { z } from 'zod'; // ============================================================================ // TypeScript Interfaces (for proper type inference with z.lazy) // ============================================================================ export interface Users { id: number; email: string; createdAt?: Date | null; posts?: Posts[]; } export interface Posts { id: number; userId: number; title: string; user?: Users; } // ============================================================================ // Zod Schemas // ============================================================================ /** Table: users */ export const UsersSchema: z.ZodType<Users> = z.object({ id: z.number().int(), email: z.string(), createdAt: z.coerce.date().nullable().optional(), posts: z.lazy(() => PostsSchema).array().optional(), }); /** Table: posts */ export const PostsSchema: z.ZodType<Posts> = z.object({ id: z.number().int(), userId: z.number().int(), title: z.string(), user: z.lazy(() => UsersSchema).optional(), });
| PostgreSQL Type | TypeScript Type | Zod Schema |
|---|---|---|
text, varchar, char |
string |
z.string() |
integer, int, int4 |
number |
z.number().int() |
bigint, int8 |
bigint |
z.bigint() |
real, float4, numeric |
number |
z.number() |
boolean, bool |
boolean |
z.boolean() |
timestamp, timestamptz, date |
Date |
z.coerce.date() |
time, timetz |
string |
z.string() |
json, jsonb |
unknown |
z.unknown() |
uuid |
string |
z.string() |
bytea |
Buffer |
z.instanceof(Buffer) |
text[], integer[], etc. |
string[], number[] |
z.array(z.string()), etc. |
| Custom enums | union of string literals | z.enum(['value1', 'value2']) |
Nullable columns are represented as .nullable().optional() in Zod schemas and as property?: Type | null in TypeScript interfaces.
When a table has a foreign key column (e.g., posts.user_id referencing users.id), the tool generates:
user?: Users property in the TypeScript interfaceuser: z.lazy(() => UsersSchema).optional() property in the Zod schemaProperty names are derived from the foreign key column name:
user_id becomes userauthor_id becomes authoruserid becomes userRel (to avoid conflicts)When a table is referenced by another table's foreign key, the tool generates:
posts?: Posts[] property in the TypeScript interfaceposts: z.lazy(() => PostsSchema).array().optional() property in the Zod schemaTables that reference themselves (e.g., an employees table with manager_id referencing id) get a children property representing the has-many side of the relationship.
When a table has multiple foreign keys referencing the same table (e.g., created_by_id and updated_by_id both referencing users), the tool generates unique property names:
createdByUser for created_by_idupdatedByUser for updated_by_idsrc/
├── index.ts # Entry point and main execution flow
├── cli.ts # CLI argument parsing with Commander
├── db.ts # PostgreSQL connection management
├── introspect/ # Database introspection module
│ ├── index.ts # Module exports
│ ├── introspect.ts # SQL queries for schema introspection
│ └── types.ts # Introspection type definitions
└── generator/ # Code generation module
├── index.ts # Module exports
├── schema-generator.ts # Zod schema and interface generation
├── type-mapper.ts # PostgreSQL to Zod type mapping
├── name-utils.ts # snake_case to camelCase/PascalCase conversion
└── file-writer.ts # Output file writing utilities
information_schema, pg_constraint, etc.) to retrieve:
# Build the project pnpm run build # Run in development mode pnpm run dev -- --connection-string "..." --db "..." --output "..." # Run module self-tests node dist/generator/type-mapper.js node dist/generator/name-utils.js node dist/generator/schema-generator.js
.js extensions in importspublic schemastring representationPlanned features for future versions:
.pg-to-ts.json)publicISC
Contributions are welcome. Please ensure your code follows the existing patterns and includes appropriate tests.