database-validator-generator

Generate Zod schemas and JSDoc types from your PostgreSQL database.

database-validator-generator (also callable as dbvg) is a CLI. Point it at a Postgres database and it writes a schemas.mjs file for your tables.

database-validator-generator is intentionally narrow: no ORM, no migrations, no query builder, and no database abstraction. It generates best-effort Zod v4 contracts from PostgreSQL metadata.

Quick Start

Install the CLI and Zod:

npm install --save-dev database-validator-generator
npm install zod

Generate schemas from your database:

DATABASE_URL="postgres://user:password@localhost:5432/app" npx database-validator-generator generate
# Or using the shorthand:
DATABASE_URL="postgres://user:password@localhost:5432/app" npx dbvg generate

This creates:

schemas.mjs

Use the generated schemas in your app:

import { usersSchema, schemas } from "./schemas.mjs";

usersSchema.parse({ id: 1, email: "[email protected]" });
schemas.users.parse({ id: 1, email: "[email protected]" });

Or run without installing the CLI first:

npx database-validator-generator generate --connection "postgres://user:password@localhost:5432/app"
# Or:
npx dbvg generate --connection "postgres://user:password@localhost:5432/app"

The generated file imports zod, so your app should have zod installed.

database-validator-generator targets Zod v4.

Commands

Generate schemas:

database-validator-generator generate
# Or: dbvg generate

List tables:

database-validator-generator list tables
# Or: dbvg list tables

Show help:

database-validator-generator --help
database-validator-generator generate --help
database-validator-generator list tables --help
# Or: dbvg --help, dbvg generate --help, dbvg list tables --help

Connecting To Postgres

Use DATABASE_URL:

DATABASE_URL="postgres://user:password@localhost:5432/app" database-validator-generator generate
# Or: DATABASE_URL="..." dbvg generate

Or pass the connection string directly:

database-validator-generator generate --connection "postgres://user:password@localhost:5432/app"
# Or: dbvg generate --connection "..."

For hosted databases that require SSL, use the connection string your provider gives you:

database-validator-generator generate --connection "postgresql://user:password@host/db?sslmode=require"
# Or: dbvg generate --connection "postgresql://..."

Choosing A Schema

By default, database-validator-generator reads from the public schema.

Use a different PostgreSQL schema:

database-validator-generator generate --schema app
database-validator-generator list tables --schema app
# Or: dbvg generate --schema app, dbvg list tables --schema app

Choosing An Output File

By default, database-validator-generator generate writes schemas.mjs.

Write somewhere else:

database-validator-generator generate --out src/db/schemas.mjs
# Or: dbvg generate --out src/db/schemas.mjs

Check whether a generated file is stale without writing it:

database-validator-generator generate --check
database-validator-generator generate --out src/db/schemas.mjs --check
# Or: dbvg generate --check, dbvg generate --out src/db/schemas.mjs --check

--check exits nonzero if the output file does not exist or differs from what database-validator-generator would generate. This is useful in CI.

Generating Only Some Tables

Generate one table:

database-validator-generator generate --table users
# Or: dbvg generate --table users

Generate multiple tables:

database-validator-generator generate --table users --table posts
database-validator-generator generate --table users,posts
# Or: dbvg generate --table users --table posts, etc.

Use glob-style patterns:

database-validator-generator generate --table "app_*"
database-validator-generator generate --table "*" --exclude-table "audit_*"
# Or: dbvg generate --table "app_*", etc.

Filters can match table names like users or schema-qualified names like public.users.

What Gets Generated

For each table, database-validator-generator generates:

• A named Zod schema, like usersSchema
• A row schema, like usersRowSchema
• An insert schema, like usersInsertSchema
• An update schema, like usersUpdateSchema
• A JSDoc typedef, like Users
• A schemas registry keyed by table name
• A metadata export with keys, relationships, constraints, and column metadata

Here is a small PostgreSQL schema:

create type user_status as enum ('active', 'disabled');

create table users (
  id integer primary key,
  email varchar(320) not null,
  username varchar(30) not null check (char_length(username) >= 3),
  status user_status not null,
  age integer check (age >= 13),
  bio text,
  created_at timestamptz not null default now()
);

comment on table users is 'Application users.';
comment on column users.email is 'Email address used for login.';
comment on column users.username is 'Public handle shown in the app.';
comment on column users.status is 'Whether the user can access the app.';

Run:

database-validator-generator generate
# Or: dbvg generate

database-validator-generator writes schemas.mjs like this:

/* eslint-disable */
// Generated by database-validator-generator. Do not edit by hand.
// PostgreSQL schema: public

import { z } from "zod";

/**
 * Application users.
 * @typedef {object} Users
 * @property {number} id
 * @property {string} email - Email address used for login.
 * @property {string} username - Public handle shown in the app.
 * @property {"active" | "disabled"} status - Whether the user can access the app.
 * @property {number | null} age
 * @property {string | null} bio
 * @property {Date} created_at
 */
export const usersRowSchema = z.object({
  id: z.number().int().gte(-2147483648).lte(2147483647),
  email: z.string().max(320).describe("Email address used for login."),
  username: z.string().min(3).max(30).describe("Public handle shown in the app."),
  status: z.enum(["active", "disabled"]).describe("Whether the user can access the app."),
  age: z.number().int().gte(13).lte(2147483647).nullable(),
  bio: z.string().nullable(),
  created_at: z.coerce.date(),
}).describe("Application users.");
export const usersSchema = usersRowSchema;

export const usersInsertSchema = z.object({
  id: z.number().int().gte(-2147483648).lte(2147483647),
  email: z.string().max(320).describe("Email address used for login."),
  username: z.string().min(3).max(30).describe("Public handle shown in the app."),
  status: z.enum(["active", "disabled"]).describe("Whether the user can access the app."),
  age: z.number().int().gte(13).lte(2147483647).nullable().optional(),
  bio: z.string().nullable().optional(),
  created_at: z.coerce.date().optional(),
});

export const usersUpdateSchema = z.object({
  email: z.string().max(320).describe("Email address used for login.").optional(),
  username: z.string().min(3).max(30).describe("Public handle shown in the app.").optional(),
  status: z.enum(["active", "disabled"]).describe("Whether the user can access the app.").optional(),
  age: z.number().int().gte(13).lte(2147483647).nullable().optional(),
  bio: z.string().nullable().optional(),
  created_at: z.coerce.date().optional(),
});

export const schemas = {
  users: usersSchema,
};

export const metadata = {
  users: {
    primaryKey: ["id"],
    unique: [],
    foreignKeys: [],
    exclusionConstraints: [],
  },
};

PostgreSQL Metadata Support

database-validator-generator tries to make schemas match your real table definitions:

Complex PostgreSQL constraints are always preserved in the exported metadata, but only simple single-column checks are translated into Zod validation.

• NOT NULL becomes required fields. Example column: email text not null.
• Nullable columns become .nullable(). Example column: bio text.
• varchar(n) becomes .max(n). Example column: email varchar(320).
• char(n) becomes .length(n). Example column: sku char(12).
• PostgreSQL enum types become z.enum([...]). Example column: status user_status not null.
• Simple text enum checks like status in ('draft', 'published') become z.enum([...]). Example column: status text check (status in ('draft', 'published')).
• Simple range checks like age >= 0 become .gte(0). Example column: age integer check (age >= 13).
• Simple length checks like char_length(name) <= 80 become .max(80). Example column: name text check (char_length(name) <= 80).
• Table and column comments become JSDoc descriptions and Zod .describe(...) metadata. Example column comment: comment on column users.email is 'Email address used for login.'.
• Defaults, identity columns, and generated columns shape InsertSchema and UpdateSchema output. Example column: created_at timestamptz not null default now().
• Primary keys, unique constraints, foreign keys, exclusion constraints, check constraints, and constraint comments are exported in metadata. Example column: user_id integer references users(id).
• PostgreSQL domains reuse their domain CHECK constraints in generated column schemas. Example column: slug slug_text not null.
• Simple regex checks like slug ~ '^[a-z0-9-]+$' become .regex(...) when the PostgreSQL regex is also valid JavaScript regex. Example column: slug text check (slug ~ '^[a-z0-9-]+$').
• Non-empty checks like name <> '' become .min(1). Example column: name text check (name <> '').
• Array length checks like array_length(tags, 1) <= 5 become z.array(...).max(5). Example column: tags text[] check (array_length(tags, 1) <= 5).
• Comment hints like @format email become .email() and @format ip becomes .ip(). Example column comment: comment on column users.email is 'Login email. @format email'.
• Comment hints like @zod z.object({ kind: z.string() }) can override the generated Zod expression for JSON or other custom columns. Example column comment: comment on column events.payload is '@zod z.object({ kind: z.string() })'.
• PostgreSQL range types are preserved as string schemas and described in metadata. Example column: available_during tstzrange.

Metadata Conventions

database-validator-generator supports metadata from standard PostgreSQL definitions and a few optional comment hints.

Nullability

email text not null
bio text

• NOT NULL columns are required in row schemas.
• Nullable columns become .nullable().

String Lengths

email varchar(320)
sku char(12)

• varchar(n) becomes z.string().max(n).
• char(n) becomes z.string().length(n).

PostgreSQL Enums

create type user_status as enum ('active', 'disabled');

status user_status not null

Generates:

z.enum(["active", "disabled"])

Check-Based Enums

status text check (status in ('draft', 'published'))
kind text check (kind = any (array['physical'::text, 'digital'::text]))

These become z.enum([...]) when the check is a simple single-column enum check.

Numeric Ranges

age integer check (age >= 13)
price numeric(8, 2) check (price > 0 and price < 10000)

Simple single-column comparisons become Zod range checks:

z.number().gte(13)
z.number().gt(0).lt(10000)

smallint and integer also include their native PostgreSQL bounds.

Numeric Precision And Scale

price numeric(8, 2)

When safe to represent in JavaScript, database-validator-generator derives a precision range and scale check:

z.number().multipleOf(0.01)

String Length Checks

username text check (char_length(username) >= 3)
bio text check (length(bio) <= 500)

Supported functions:

• char_length(column)
• character_length(column)
• length(column)

These become .min(...), .max(...), or .length(...).

Non-Empty Strings

name text check (name <> '')

Generates:

z.string().min(1)

Regex Checks

slug text check (slug ~ '^[a-z0-9-]+$')
email text check (email ~* '^[^@]+@[^@]+$')

Simple PostgreSQL regex checks become .regex(...) when the pattern is also valid JavaScript regex.

• ~ becomes a case-sensitive JavaScript regex.
• ~* becomes a case-insensitive JavaScript regex.

Array Length Checks

tags text[] check (array_length(tags, 1) <= 5)
items text[] check (cardinality(items) >= 1)

Supported array length functions:

• array_length(column, 1)
• cardinality(column)

These become z.array(...).min(...), .max(...), or .length(...).

Domains

create domain slug_text as text check (VALUE ~ '^[a-z0-9-]+$');

slug slug_text not null

Domain CHECK constraints are applied to columns that use the domain.

Table And Column Comments

comment on table users is 'Application users.';
comment on column users.email is 'Email address used for login.';

Comments become:

• JSDoc descriptions
• Zod .describe(...) metadata
• metadata export descriptions

Format Comment Hints

Use comments to request extra Zod string formats:

comment on column users.email is 'Email address used for login. @format email';
comment on column servers.ip_address is 'Public server address. @format ip';

Supported format hints:

• @format email becomes .email().
• @format ip becomes .ip().
• @format inet becomes .ip().
• @database-validator-generator format email also works.

Custom Zod Comment Hints

Use @zod when PostgreSQL metadata is not enough, especially for json or jsonb columns:

comment on column events.payload is '@zod z.object({ kind: z.string() })';
comment on column events.payload is '@database-validator-generator zod z.object({ kind: z.string() })';

The expression must start with z.. When present, it overrides the generated base Zod expression for that column.

Only use @zod hints in schemas you trust. The hint is copied into generated JavaScript.

Defaults, Identity, And Generated Columns

id integer generated always as identity primary key
created_at timestamptz not null default now()
display_name text generated always as (first_name || ' ' || last_name) stored

These shape generated mutation schemas:

• RowSchema represents selected database rows.
• InsertSchema omits DB-owned generated always identity columns and generated columns.
• InsertSchema makes default-backed columns optional.
• UpdateSchema omits primary keys, identity columns, and generated columns.

Keys And Relationships

primary key (id)
unique (email)
foreign key (user_id) references users(id)

These are exported in metadata:

metadata.posts.primaryKey
metadata.users.unique
metadata.posts.foreignKeys

Exclusion Constraints

exclude using gist (room_id with =, during with &&)

Exclusion constraints are exported in metadata.exclusionConstraints.

Constraint Comments

comment on constraint users_email_key on users is 'Email addresses are unique.';

Constraint comments are included in the metadata export.

PostgreSQL Range Types

available_during tstzrange
price_window numrange

Range columns are currently generated as strings and preserved in metadata with their PostgreSQL type information.

Current Scope

database-validator-generator supports PostgreSQL first. Other databases are not supported yet.

database-validator-generator is not an ORM, migration tool, or query builder. It reads PostgreSQL metadata and writes generated Zod v4 contract files.