gem-hunter

zod-dbs is a code generation tool that creates Zod schemas and TypeScript types from your database schema. It generates validation schemas that match your database structure, helping keep your TypeScript types synchronized with your database schema.

If you are looking for a PostgreSQL solution only, then you can also use simpler zod-pg cli.

• Key Features
• Requirements
• Why zod-dbs?
• Supported Database Providers
• Installation
• Quickstart
• CLI usage
  • With connection string
  • With options
  • With environment variables
  • Using .env files
  • Exclude / Include Tables
  • CLI Options
  • Configuration File
• Programmatic usage
• Output File Structure
• Schema Output
  • The Read Schemas
  • The Write Schemas
  • Casing
  • Singularization
• JSON Schema Support
  • Setting up JSON Schema Integration
• Customizing Generated Models with Hooks
• Extending schemas
• Custom Provider
• Contributing

• Database-First Development – Generate Zod schemas directly from your database schema.
• Multiple Schema Types – Separate read, insert, and update schemas for clearer intent.
• Multiple Zod Versions – Generate for Zod v3, v4, or the lightweight v4-mini build.
• Db Coverage – Arrays, enums, custom types, materialized views, foreign tables.
• Type Detection – Detects serial, enum, array, and nullable characteristics automatically.
• Customization – Hooks, casing transformations, singularization control.
• Organized Output – Predictable file structure (constants, types, per-table schemas).
• No Runtime Dependencies – Generated artifacts only depend on Zod.

• Node.js 22+
• TypeScript 4+

Verified to work with latest versions of Bun and Deno (as of 2025-09-09)

Manually writing and maintaining TypeScript types and Zod schemas for database tables is time-consuming and error-prone.

zod-dbs automates this process by generating type-safe validation schemas directly from your database schema. This approach ensures your validation logic stays synchronized with your database structure, eliminating the manual work of writing and updating schemas when your database changes. Whether you're building APIs that need request validation, working with complex features like arrays and enums, or maintaining type safety across your entire stack, zod-dbs bridges the gap between your database and TypeScript application.

• PostgreSQL: zod-dbs-pg
  • Compatible with managed PostgreSQL (AlloyDB, Amazon RDS, Cloud SQL, Azure Database for PostgreSQL, Neon, Supabase, etc.). See the provider README for details.
• MySQL: zod-dbs-mysql
• Microsoft SQL Server: zod-dbs-mssql
• Oracle: zod-dbs-oracle
• SQLite: zod-dbs-sqlite
• Snowflake: zod-dbs-snowflake
• MongoDB: zod-dbs-mongodb (experimental)
• DynamoDB: zod-dbs-dynamodb (experimental)

zod-dbs comes with a CLI tool to generate Zod schemas from your database schema. The CLI supports multiple database providers and allows you to customize the output.

npm install --save-dev zod-dbs-cli
# or
pnpm add -D zod-dbs-cli

Then the provider you need:

npm install --save-dev zod-dbs-pg
# or
pnpm add -D zod-dbs-pg

1. Install the CLI and a provider

pnpm add -D zod-dbs-cli zod-dbs-pg

2. Generate schemas

npx zod-dbs --provider pg \
  --connection-string "postgres://user:password@localhost:5432/dbname" \
  --output-dir ./zod-schemas --silent

3. Explore the output in ./zod-schemas.

npx zod-dbs --provider pg --connection-string "postgres://user:password@localhost:5432/dbname" --ssl --output-dir ./src/output

You can also specify options directly:

npx zod-dbs --provider pg --user postgres --password secret --host localhost --port 5432 --database mydb --ssl --output-dir ./src/output

zod-dbs can read connection details from environment variables. Set the following variables:

• ZOD_DBS_USER
• ZOD_DBS_PASSWORD
• ZOD_DBS_HOST
• ZOD_DBS_PORT
• ZOD_DBS_DATABASE
• ZOD_DBS_SSL (optional, defaults to false)

Then run:

npx zod-dbs --provider pg --output-dir ./src/output

zod-dbs does not automatically load .env files, but you can use a package like dotenv-cli to load them before running zod-dbs. For example:

dotenv -e .env npx zod-dbs --provider pg --output-dir ./src/output

You can exclude specific tables from schema generation using the --exclude option with a regex pattern. For example, to exclude all tables starting with "temp":

npx zod-dbs --exclude '^temp_' --output-dir ./src/output

To include only specific tables, use the --include option with a regex pattern. For example, to include only tables starting with "user" or "account":

npx zod-dbs --include '^(user|account)' --output-dir ./src/output

Note that if you use both --exclude and --include options together, the --include option is applied first, then the --exclude option is applied to the included tables.

Unless you specify the provider through a config file, the --provider option is required as each provider defines its own options for the CLI. To view options run: zod-dbs --provider [provider] --help, or view the provider documentation.

Order of precedence for options:

• CLI flags (highest precedence)
• Environment variables (connection fields)
• Config file (zod-dbs.config.{ts,js,json})
• Built-in defaults

Booleans are passed explicitly as <true|false> for clarity.

In addition to CLI options, you can use configuration files to set your options. zod-dbs-cli uses cosmiconfig. The configuration options are the same as the cli options, but defined in camelCase properties. Each provider defined its own set of configuration options so if you want a type safe config file, you need to import the provider library as well.

.zod-dbsrc

{
  "provider": "mysql",

  "user": "test",
  "password": "secret",
  "host": "localhost",
  "database": "mydb",

  "zodVersion": "4"
}

Example of a TypeScript configuration file with proper typed provider options. Add the types for your chosen provider in the tsconfig.json file:

tsconfig.json:

{
  "compilerOptions": {
    "types": ["zod-dbs-pg"]
  }
}

zod-dbs.config.ts:

import type { ZodDbsCliConfig } from 'zod-dbs-cli';

const config: ZodDbsCliConfig = {
  provider: "pg"

  // Database options
  user: 'postgres',
  password: 'secret',
  host: 'localhost',
  port: 5432,
  database: 'mydb',

  // Output options
  outputDir: './src/generated',
  zodVersion: '4',
  stringifyDates: true,
  defaultEmptyArray: true,

  // NOTE: When providing an array of strings for the "include" / "exclude" option, it uses exact table name matching. Regex expressions are only supported when providing a single string value.
  include: ['users', 'posts'], 
  exclude: '^temp_',
};

export default config;

When you define a configuration file, you can run the CLI without any options:

npx zod-dbs

Use the library directly in Node/TypeScript if you prefer code over the CLI.

npm install zod-dbs zod-dbs-pg

import { generateZodSchemas } from 'zod-dbs';
import { createProvider } from 'zod-dbs-pg';

await generateZodSchemas({
  provider: createProvider(),
  config: {
    host: 'localhost',
    port: 5432,
    database: 'mydb',
    user: 'postgres',
    password: 'secret',
    schemaName: 'public',
    outputDir: './zod-schemas',
    zodVersion: '4',
  },
});

The generator creates a predictable structure:

• output/constants.ts – Constants for all table and view names.
• output/types.ts – TypeScript types for all tables and views.
• output/tables/<table>/schema.ts – Zod schemas for the table (read / insert / update; plus base + transform when casing enabled).
• output/tables/<table>/index.ts – Re-exports for the table.
• output/tables/index.ts – Aggregated exports of all table schemas & types.
• (Folders views/ and materialized_views/ are generated similarly when those relation types exist.)

The generated Zod schemas will look something like this: (example for a "users" table)

// output/tables/users/schema.ts
import { z } from "zod";

export const UsersTableSchema = z.object({..});
export const UsersTableInsertSchema = z.object({..});
export const UsersTableUpdateSchema = UsersTableInsertSchema.partial();

export interface UserRecord {
  // TypeScript interface with proper types
}

export interface UserInsertRecord {
  // TypeScript interface for insert operations
}

export type UserUpdateRecord = Partial<UserInsertRecord>;

Since reading and writing are two different operations, zod-dbs generates separate schemas for reads, inserts and updates. The UsersTableInsertSchema is used for creating new records, while the UsersTableUpdateSchema is a partial version of the insert schema, allowing you to update only specific fields.

• Used for reading data from the database.
• Does not enforce write constraints (e.g., max length).
• By default transforms nulls to undefined, making it easier to work with optional fields in TypeScript.
• Optionally defaults nullable array fields to empty arrays.

• Enforces field constraints such as max length, ensuring that your data adheres to the database schema.
• By default transforms json fields to strings.
• Optionally transforms date fields to ISO strings using .toISOString().
• Excludes only SERIAL/auto-incrementing columns and columns from non-table relations (views, etc.).

zod-dbs supports different casing styles for generated schemas and types. By default zod-dbs uses PascalCase for object names and camelCase for properties. You can specify the desired casing for field names and object names using the --field-name-casing and --object-name-casing options.

If your database column names are in e.g snake_case and the field name casing is set to camelCase (the default), zod-dbs automatically detects whether a case transformation is needed for each table. If so it generates additional "base" schemas and transform functions to convert between the database column names and the desired casing. You can disable this behavior by specifying --no-case-transform. This means that the generated schemas will use the original database column names as-is without any transformation. Note that object casing is not affected by this flag, only fields / properties.

By default zod-dbs converts plural table / view names into singular, PascalCase identifiers when generating TypeScript record types, insert/update record types, enum names, and related constants / transform helpers. This keeps generated symbols concise and aligned with typical TypeScript naming conventions.

Example:

If you would prefer the generated identifiers to preserve the original (often plural / snake_case) names, disable singularization with the CLI flag:

npx zod-dbs --singularization false

Or in a config file:

export default {
  // ...other config
  singularization: false,
};

When disabled, names are still cased according to your casing settings, but the plural form is retained (e.g. UsersRecord).

zod-dbs provides hooks to customize the generated models during generation. These hooks allow you to add custom validation, transformations, or modifications to your schemas.

This hook is called for each column after its initial model is created, allowing you to modify individual column properties.

onColumnModelCreated: (column) => {
  // Add email validation to email columns
  if (column.name === 'email') {
    // Note that changing the "zodType" to a string field with extra validation logic,
    // only applies to the write schema.
    // The read schema will still output the field as a z.string.
    column.zodType = 'email';

    // Additional validation / transformation
    column.writeTransforms = ['trim', 'lowercase'];
  }

  // Add minimum length to password fields
  if (column.name === 'password') {
    // Note that this only applies to the write schema.
    column.minLen = 8;
  }

  // Mark column as deprecated
  if (column.name === 'profile' && column.tableName === 'users') {
    column.isDeprecated = true;
    column.isDeprecatedReason = 'This column is replaced by individual columns';
  }

  // Fix unmapped type
  if (column.dataType === 'custom' && column.zodType === 'any') {
    column.zodType = 'string';
  }

  // Add custom transformations based on table name
  if (column.tableName === 'users') {
    // Add any table-specific customizations
  }

  return column;
};

This hook is called for each table after all its columns have been processed, allowing you to modify the table model.

onTableModelCreated: (table) => {
  // Add custom transformations based on table name
  if (table.name === 'users') {
    // Add any table-specific customizations

    // e.g remove a column
    table.columns = table.columns.filter(
      (column) => column.name !== 'not_in_use'
    );
  }

  return table;
};

zod-dbs cannot determine the structure of JSON fields in your database. To use Zod schemas for JSON fields, you can use the --json-schema-import-location option. When this option is provided, zod-dbs will import Zod schemas from the specified location for JSON fields in your database.

Say you have a "user" table with a JSON field called "profile", and you want to use a Zod schema for that JSON field.

Step 1: Run zod-dbs with JSON schema import location

Start by running, e.g.,

npx zod-dbs --json-schema-import-location '../../json' --output-dir ./schema/generated

Step 2: Generated schema imports your JSON schemas

This will create a ./schema/generated/tables/users/schema.ts file looking similar to this:

import { z } from 'zod';

import { UserProfileSchema } from '../../json';

export const UsersTableSchema = z.object({
  id: z.number().int(),
  name: z.string(),
  profile: UserProfileSchema,
});

The JSON Zod schema name is derived from [TableName][FieldName]Schema, so in this case, it will look for UserProfileSchema in the specified import location.

Step 3: Create your JSON schemas

Then you can create a Zod schema for the JSON field in your specified import location.

e.g

// src/schema/json.ts
import { z } from 'zod';

export const UserProfileSchema = z.object({
  firstName: z.string(),
  lastName: z.string(),
  age: z.number().optional(),
});

It is possible to extend the generated Zod schemas with additional fields / rules / transformations. This is especially handy if you are doing a joined query.

To extend a read schema you need to import the base read schema and apply the casing transformations afterwards (if needed). e.g.

import {
  transformUserBaseRecord,
  UsersTableBaseSchema,
} from '[output]/tables/users';

const ExtendedSchema = UsersTableBaseSchema.extend({
  permissions: z.array(z.string()).nullish().optional(),
  signed_in_at: z.coerce.date().nullish().optional(),
}).transform((data) => ({
  ...transformUserBaseRecord(data),
  permissions: data.permissions,
  signedInAt: data.signed_in_at,
}));

If you have disabled case transforms (--no-case-transform) or no casing transforms were detected, then there are no "base" schemas or transform functions. And you can just extend the read schema like:

import { UsersTableSchema } from '[output]/tables';

const ExtendedSchema = UsersTableSchema.extend({
  permissions: z.array(z.string()).nullish().optional(),
  signedInAt: z.coerce.date().nullish().optional(),
});

If you want to support a database that is not supported out of the box, you can create a custom provider by implementing the ZodDbsProvider interface.

zod-dbs-cli provides a base class ZodDbsBaseProvider that you can extend to create a custom provider. This base class provides default implementations for some methods, so you only need to implement the methods that are specific to your database.

// zod-dbs.config.ts
import { ZodDbsBaseProvider } from 'zod-dbs-cli';

import type {
  ZodDbsCliConfig,
  ZodDbsColumnInfo,
  ZodDbsProviderConfig,
} from 'zod-dbs-cli';

class CustomProvider extends ZodDbsBaseProvider {
  constructor() {
    super({
      name: 'my-custom-provider',
      displayName: 'My Custom Provider',
    });
  }

  protected override async fetchSchemaInfo(
    config: ZodDbsProviderConfig
  ): Promise<ZodDbsColumnInfo[]> {
    // Implement logic to fetch column information from your database.
    // Looking at existing providers can be helpful.
    return [];
  }
}

const config: ZodDbsCliConfig = {
  provider: new CustomProvider(),
  // Other config options
};

export default config;

Contributions are welcome! If you find a bug or have a feature request, please open an issue or submit a pull request.