Schema Definition

Example

Create a collection by defining a Zod schema:

import { connect, z } from 'zodgres';

const db = connect('postgres://localhost:5432/mydb');

const users = db.collection('users', {
  id: z.number().optional(),        // Auto-incrementing primary key
  name: z.string().max(100),        // Required string with max length
  email: z.string().email(),        // Required email validation
  age: z.number().min(0).optional(), // Optional positive number
});

// Open the database connection and run migrations
await db.open();

Important: Always call db.open() after defining all your collections. This establishes the database connection and runs any necessary migrations to ensure your database schema matches your collection definitions.

Schema Definition

Data Types

Zodgres maps Zod types to PostgreSQL column types:

Zod Type	PostgreSQL Type	Example
`z.string()`	`TEXT`	`z.string()`
`z.string().max()`	`CHARACTER VARYING`	`z.string().max(255)`
`z.number()`	`NUMERIC`	`z.number().int()`
`z.boolean()`	`BOOLEAN`	`z.boolean()`
`z.date()`	`TIMESTAMP`	`z.date()`
`z.enum()`	`ENUM`	`z.enum(['active', 'inactive'])`
`z.array()`	`JSONB`	`z.array(z.string())`
`z.object()`	`JSONB`	`z.object({ key: z.string() })`

Primary Keys

Define auto-incrementing primary keys with optional numbers:

const products = db.collection('products', {
  id: z.number().optional(), // Auto-incrementing primary key
  name: z.string(),
  price: z.number().positive(),
});

Convention: Use id: z.number().optional() for auto-incrementing primary keys.

String Constraints

Apply various string constraints:

const articles = db.collection('articles', {
  id: z.number().optional(),
  title: z.string().max(200),           // Max length
  slug: z.string().min(3).max(50),      // Min and max length
  content: z.string(),                   // Unlimited text
  status: z.enum(['draft', 'published']), // Enum values
});

Number Constraints

Define numeric constraints and types:

const orders = db.collection('orders', {
  id: z.number().optional(),
  total: z.number().positive(),          // Must be positive
  quantity: z.number().int().min(1),     // Integer >= 1
  discount: z.number().min(0).max(1),    // Between 0 and 1
  rating: z.number().min(1).max(5).optional(), // Optional rating
});

Optional Fields

Make fields optional with .optional():

const profiles = db.collection('profiles', {
  id: z.number().optional(),
  user_id: z.number(),                   // Required
  bio: z.string().optional(),            // Optional
  avatar_url: z.string().url().optional(), // Optional URL
  birth_date: z.date().optional(),       // Optional date
});

Advanced Schema Features

Unique Constraints

Create unique constraints using the custom .unique() method:

const users = db.collection('users', {
  id: z.number().optional(),
  email: z.string().email().unique(),    // Unique email
  username: z.string().min(3).unique(),  // Unique username
  name: z.string(),
});

JSON Fields

Store complex data as JSON:

const settings = db.collection('user_settings', {
  id: z.number().optional(),
  user_id: z.number(),
  preferences: z.object({
    theme: z.enum(['light', 'dark']),
    notifications: z.boolean(),
    language: z.string(),
  }),
  tags: z.array(z.string()),             // Array of strings
});

Date and Time

Handle timestamps and dates:

const events = db.collection('events', {
  id: z.number().optional(),
  name: z.string(),
  start_time: z.date(),                  // Timestamp
  end_time: z.date(),
  created_at: z.date().optional(),
  updated_at: z.date().optional(),
});

Type Inference

Zodgres provides full TypeScript type inference:

const users = db.collection('users', {
  id: z.number().optional(),
  name: z.string(),
  age: z.number().optional(),
});

// TypeScript infers the correct types
const user = await users.create({
  name: 'John',  // ✅ Required
  age: 30,       // ✅ Optional
  // email: 'test' // ❌ TypeScript error - not in schema
});

// Return type is inferred as:
// { id: number, name: string, age: number | null }

Dropping Tables

Remove tables when needed:

// Drop the entire table
await users.drop();

drop() permanently deletes the table and all its data. Use with caution.

Best Practices

Naming Conventions

Use snake_case for field names to match PostgreSQL conventions
Use plural names for collection/table names
Be explicit with constraints

const user_profiles = db.collection('user_profiles', {
  id: z.number().optional(),
  user_id: z.number(),
  first_name: z.string().max(50),
  last_name: z.string().max(50),
  created_at: z.date().optional(),
  updated_at: z.date().optional(),
});

Schema Definition

On this page