{"id":17871,"library":"pg-to-ts","title":"Postgres to TypeScript Schema Generator","description":"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.","status":"active","version":"4.1.1","language":"javascript","source_language":"en","source_url":"https://github.com/danvk/pg-to-ts","tags":["javascript","postgres","schema","typescript","sql"],"install":[{"cmd":"npm install pg-to-ts","lang":"bash","label":"npm"},{"cmd":"yarn add pg-to-ts","lang":"bash","label":"yarn"},{"cmd":"pnpm add pg-to-ts","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Required peer dependency for TypeScript compilation and understanding generated types.","package":"typescript","optional":false},{"reason":"Commonly used for interacting with PostgreSQL databases in Node.js applications that consume the generated types and runtime constants.","package":"pg","optional":true}],"imports":[{"note":"Output files are ESM modules, use `import` syntax. The path './dbschema' is an example, replace with your actual output file name.","wrong":"const Product = require('./dbschema').Product;","symbol":"Product","correct":"import { Product } from './dbschema';"},{"note":"Interface containing a map of all table types. Imported from your generated schema file, not the `pg-to-ts` package itself.","wrong":"import type { TableTypes } from 'pg-to-ts';","symbol":"TableTypes","correct":"import { TableTypes } from './dbschema';"},{"note":"A runtime constant object containing metadata for all tables, where individual table metadata (like `product`) is accessed as a property (e.g., `tables.product`).","wrong":"import { product } from './dbschema';","symbol":"tables","correct":"import { tables } from './dbschema';"}],"quickstart":{"code":"import { Pool } from 'pg';\nimport { Product, ProductInput, tables } from './dbschema'; // Assuming this is your generated file\n\n// Step 1: Generate your schema file (dbschema.ts)\n// Run this command once after database schema changes:\n// npm install pg-to-ts\n// pg-to-ts generate -c \"postgresql://user:password@host:5432/mydb\" -o ./dbschema.ts\n\n// For this example, we assume dbschema.ts has already been generated based on a 'product' table:\n/*\n// Example content of dbschema.ts:\nexport interface Product {\n  id: string;\n  name: string;\n  description: string;\n  created_at: Date;\n}\nexport interface ProductInput {\n  id?: string;\n  name: string;\n  description: string;\n  created_at?: Date;\n}\nconst product = {\n  tableName: 'product',\n  columns: ['id', 'name', 'description', 'created_at'],\n  requiredForInsert: ['name', 'description'],\n} as const;\nexport const tables = {\n  product\n};\n*/\n\nasync function main() {\n  // Step 2: Use the generated types in your application\n  const connectionString = process.env.DATABASE_URL ?? 'postgresql://user:pass@localhost:5432/mydb';\n  const pool = new Pool({ connectionString });\n\n  try {\n    const newProduct: ProductInput = {\n      name: 'Example Product',\n      description: 'A product generated by pg-to-ts quickstart.'\n    };\n\n    // Use runtime constants for query construction, ensuring type safety\n    const insertColumns = tables.product.requiredForInsert.join(', ');\n    const placeholders = tables.product.requiredForInsert.map((_, i) => `$${i + 1}`).join(', ');\n    const insertValues = tables.product.requiredForInsert.map(key => newProduct[key]);\n\n    const insertRes = await pool.query<Product>(\n      `INSERT INTO ${tables.product.tableName} (${insertColumns}) VALUES (${placeholders}) RETURNING *`,\n      insertValues\n    );\n    const createdProduct = insertRes.rows[0];\n    console.log('Created product:', createdProduct);\n\n    // Fetch all products using the generated type\n    const selectRes = await pool.query<Product>(`SELECT * FROM ${tables.product.tableName}`);\n    const products: Product[] = selectRes.rows;\n    console.log('All products:', products.map(p => p.name));\n\n  } catch (error) {\n    console.error('Database operation failed:', error);\n  } finally {\n    await pool.end();\n  }\n}\n\nmain();","lang":"typescript","description":"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."},"warnings":[{"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.","message":"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.","severity":"gotcha","affected_versions":">=1.0"},{"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.","message":"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`.","severity":"gotcha","affected_versions":">=1.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=4.1.0"}],"env_vars":null,"last_verified":"2026-04-23T00:00:00.000Z","next_check":"2026-07-22T00:00:00.000Z","problems":[{"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.","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`).","error":"Property 'name' is missing in type '{ id: string; description: string; }' but required in type 'ProductInput'."},{"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.","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.","error":"Type 'Date' is not assignable to type 'string'."},{"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.","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.","error":"Property 'metadata' does not exist on type 'Product'. Did you mean 'name'?"}],"ecosystem":"npm","meta_description":null,"install_score":null,"install_tag":null,"quickstart_score":null,"quickstart_tag":null}