pgzod - PostgreSQL to Zod Schema Generator

3.3.0 verified Thu Apr 23 auth: no javascript

pgzod is a command-line utility for generating TypeScript-first Zod schemas and types directly from a live PostgreSQL database schema. It aims to synchronize your application's data validation and typing with your database structure, reducing manual effort and potential mismatches. The current stable version is 3.3.0, with minor and patch releases occurring regularly based on feature additions and bug fixes. Key differentiators include its focused integration with PostgreSQL, leveraging Zod for robust validation, and the ability to define different 'strategies' (like `write` or `readwrite`) for schema generation based on usage context. While primarily a CLI tool, it also offers a programmatic API for direct integration into Node.js applications, supporting database connection details via environment variables or configuration objects, making it suitable for both local development and CI/CD pipelines.

Common errors

error Error: connect ECONNREFUSED <DB_HOST>:<DB_PORT> ↓

cause The pgzod CLI tool or programmatic function failed to establish a connection to the PostgreSQL database. This typically indicates incorrect connection details or a network issue.

fix

Verify that PGHOST, PGPORT, PGUSER, PGPASSWORD, and PGDATABASE environment variables (or corresponding command-line options/config properties) are correct. Ensure the database server is running and accessible from the machine where pgzod is executed, and check for any firewall restrictions.

error No tables found in the provided schema: 'your_schema_name' ↓

cause pgzod connected to the database but could not find any tables within the specified PostgreSQL schema. This can occur if the schema name is incorrect or the schema is genuinely empty.

fix

Double-check the provided schema name for typos. Confirm that the specified schema (--schema option or Config.schema) actually contains tables you intend to generate Zod schemas for.

error ZodError: Invalid input (when validating data with pgzod-generated schemas) ↓

cause Data being validated against a pgzod-generated Zod schema does not conform to the schema's defined rules. This often points to a mismatch between the current database schema, the generated Zod schema, and the input data, possibly after a database migration or due to misinterpretation of types.

fix

Review the database schema and the corresponding generated Zod schema (generated_schemas.ts). Ensure your input data matches the expected types, required fields, and constraints. Pay particular attention to changes in pgzod v3.0.1 regarding foreign key optionality, which might require adjustments to your input data or validation logic.

Warnings

breaking pgzod v3.0.0 introduced support for generated columns. While this is a feature, it represents a significant change in how schemas are processed and might affect existing generated schemas if not handled, particularly if you were previously working around the lack of support. ↓

fix Review your database schema for tables with generated columns and inspect the newly generated Zod schemas. Ensure your application's validation logic accommodates these changes.

breaking In v3.0.1, pgzod changed its default behavior, no longer marking foreign keys as strictly required. This alters the generated Zod schema for relationships, potentially making fields that were implicitly required optional. ↓

fix Inspect your Zod schemas related to foreign keys generated by pgzod v3.0.1 or newer. Adjust your application's validation logic if you relied on foreign keys being implicitly required, or customize `customZodTypes` if specific foreign keys must remain required.

breaking Node.js v12 support was officially dropped in pgzod v2.1.1. Users on older Node.js versions must upgrade to at least Node.js 14 (or newer supported LTS versions) to use pgzod v2.1.1 and beyond. ↓

fix Upgrade your Node.js environment to version 14 or higher to ensure compatibility and receive library updates.

gotcha By default, pgzod will only process tables within the 'public' schema of your PostgreSQL database. If your tables are organized into a different schema (e.g., 'app', 'private'), pgzod will not find them without explicit instruction. ↓

fix When using the CLI, specify your schema with `--schema your_schema_name`. Programmatically, set the `schema` property in the `Config` object (e.g., `schema: 'your_schema_name'`).

Install

npm install pgzod

yarn add pgzod

pnpm add pgzod

Imports

generateZodSchemas
wrong
```
const generateZodSchemas = require('pgzod');
```
correct
```
import { generateZodSchemas } from 'pgzod';
```
This is the primary function for programmatic schema generation. ESM import is preferred.
Config
wrong
```
import { Config } from 'pgzod';
```
correct
```
import type { Config } from 'pgzod';
```
The type definition for the configuration object passed to `generateZodSchemas`. Use `import type` for type-only imports.
getConfig
wrong
```
import getConfig from 'pgzod';
```
correct
```
import { getConfig } from 'pgzod';
```
Utility function to retrieve the current pgzod configuration, typically for CLI usage or advanced programmatic setups.

Quickstart

Demonstrates how to programmatically generate Zod schemas from a PostgreSQL database using `pgzod`, connecting via environment variables and specifying an output path and generation strategy. This script requires environment variables for PostgreSQL connection details.

import { generateZodSchemas, type Config } from 'pgzod';
import * as path from 'path';
import * as fs from 'fs'; // Required for fs.existsSync to check output, but not strictly for pgzod itself.

async function main() {
  // Ensure your .env file or environment variables are set:
  // PGHOST=localhost
  // PGPORT=5432
  // PGUSER=postgres
  // PGPASSWORD=mysecretpassword
  // PGDATABASE=your_database_name
  // PGSCHEMA=public

  const outputFilePath = path.join(__dirname, 'generated_schemas.ts');

  const config: Config = {
    host: process.env.PGHOST ?? 'localhost',
    port: parseInt(process.env.PGPORT ?? '5432', 10),
    user: process.env.PGUSER ?? 'postgres',
    password: process.env.PGPASSWORD ?? 'mysecretpassword',
    database: process.env.PGDATABASE ?? 'your_database_name',
    schema: process.env.PGSCHEMA ?? 'public',
    output: outputFilePath,
    strategy: 'readwrite', // Options: 'write' or 'readwrite'
    // customZodTypes: { // Example for mapping custom PostgreSQL types
    //   'jsonb': 'z.any()',
    // },
  };

  try {
    console.log(`Attempting to generate Zod schemas from DB '${config.database}' (schema: '${config.schema}')...`);
    await generateZodSchemas(config);
    console.log(`Successfully generated Zod schemas to: ${outputFilePath}`);

    // To demonstrate usage of generated schemas (requires a 'user' table in your DB)
    // if (fs.existsSync(outputFilePath)) {
    //   const { UserReadSchema } = await import(outputFilePath); // Adjust based on your table names
    //   console.log("Example UserReadSchema shape:", UserReadSchema.shape);
    // }

  } catch (error) {
    console.error("Failed to generate Zod schemas:", error);
    process.exit(1);
  }
}

main();