What is SQL to pg-promise Types Generator?
pg-promise is a feature-rich PostgreSQL client for Node.js built on top of the node-postgres driver, offering promise-based queries, advanced query formatting with named parameters, automatic connection management, and extensive TypeScript support. Unlike ORMs that add abstraction layers, pg-promise gives you direct SQL access with type safety through TypeScript interfaces. This converter generates Row interfaces that mirror your table columns, Insert types where auto-generated and defaulted columns are optional, Update types with all-optional fields, a DatabaseSchema interface for typed database access, and optional helper functions for common CRUD operations all from your SQL CREATE TABLE statements.
How to Use
- Toggle the Include Insert types checkbox to generate separate insert-friendly interfaces where SERIAL and DEFAULT columns are optional
- Enable Include Update types to produce partial-update interfaces where every field is optional
- Toggle Include helper functions to generate typed findById, findAll, insert, update, and delete functions using pg-promise query formatting
- Choose whether timestamps map to Date objects or string types based on how your application handles dates
- Paste your SQL CREATE TABLE statements and click Generate pg-promise Types to produce the TypeScript definitions
Why Use This Tool?
Tips & Best Practices
- Use the Date type for timestamps if your application works with JavaScript Date objects, or keep string if you serialize dates as ISO strings in your API
- The update helper function dynamically builds SET clauses from defined fields only, skipping undefined values to avoid overwriting existing data with nulls
- For array columns like TEXT[] or INTEGER[], the converter produces Array<string> or Array<number> types that match how pg-promise returns PostgreSQL arrays
- Set up your typed database once at application startup and pass it through dependency injection rather than creating new instances in each module
Frequently Asked Questions
How are SQL types mapped to TypeScript in pg-promise types?
The converter follows pg-promise conventions: INTEGER, BIGINT, SERIAL, and numeric types map to number; VARCHAR, CHAR, TEXT, UUID, and network types map to string; BOOLEAN maps to boolean; TIMESTAMP and DATE map to Date or string depending on your preference; JSON and JSONB map to unknown for maximum type safety; BYTEA maps to Buffer; and array types like INTEGER[] map to Array<number>. Nullable columns get union types like string | null.
When should I use pg-promise instead of an ORM like Prisma or TypeORM?
Choose pg-promise when you want full SQL control with lightweight type safety, not a heavy abstraction layer. It is ideal for projects that need raw SQL performance, complex queries, stored procedures, or database-specific features. If you want auto-migrations, relation resolvers, and a schema-first development workflow, Prisma or TypeORM may be more appropriate.
Is my SQL data sent to a server?
No. All parsing and code generation runs entirely in your browser. Your SQL schema never leaves your device, and no network requests are made during the conversion process.
What is the DatabaseSchema interface for?
DatabaseSchema maps each table name to its corresponding Row interface. When you create a typed pg-promise instance with pgp<DatabaseSchema>(connectionString), the database object knows the shape of every table, enabling IntelliSense and type checking on query results without additional casting.
How do Insert and Update types differ from Row types?
Row types represent the full database row including auto-generated columns. Insert types make SERIAL and DEFAULT columns optional since the database provides those values. Update types make every column optional so you can perform partial updates — only the fields you include will be changed in the database.
Real-world Examples
Type-safe API for a task management application
A project management tool needs pg-promise types for tasks with nullable deadline dates, JSONB metadata, and auto-generated IDs, plus helper functions for CRUD operations.
CREATE TABLE tasks ( id SERIAL PRIMARY KEY, title VARCHAR(255) NOT NULL, deadline TIMESTAMP, completed BOOLEAN DEFAULT false, metadata JSONB );
export interface TasksRow {
id: number;
title: string;
deadline: Date | null;
completed: boolean | null;
metadata: unknown | null;
}
export interface TasksInsert {
id?: number; // auto-generated (SERIAL)
title: string;
deadline?: Date | null;
completed?: boolean; // has default
metadata?: unknown | null;
}Generating typed helpers for a PostgreSQL analytics dashboard
An analytics platform needs typed query helpers with RETURNING * support so that insert and update operations return the complete modified row for real-time UI updates.
CREATE TABLE events ( id BIGSERIAL PRIMARY KEY, event_type VARCHAR(50) NOT NULL, payload JSONB, created_at TIMESTAMP DEFAULT NOW() );
export async function insertEvents(db: TypedDatabase, data: EventsInsert): Promise<EventsRow> {
return db.one('INSERT INTO events (event_type, payload) VALUES ($1, $2) RETURNING *', [data.eventType, data.payload]);
}