{"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.","language":"javascript","status":"active","last_verified":"Thu Apr 23","install":{"commands":["npm install pg-to-ts"],"cli":{"name":"pg-to-ts","version":null}},"imports":["import { Product } from './dbschema';","import { TableTypes } from './dbschema';","import { tables } from './dbschema';"],"auth":{"required":false,"env_vars":[]},"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.","tag":null,"tag_description":null,"last_tested":null,"results":[]},"compatibility":null}