@datazod/zod-sql v0.2.9

@datazod/zod-sql

A library for converting Zod schemas to SQL table definitions, helping you work with databases in a type-safe way.

Experimental Status

This package is currently in experimental status. While I strive for stability and have comprehensive tests, the API may change in future versions as I continue to improve and add features based on community feedback.

Related Packages

This package is part of the @datazod ecosystem, which includes complementary tools for working with Zod schemas and databases:

• @datazod/zod-turso - A type-safe Turso/SQLite ORM with data flattening, batch operations, and query building capabilities
• @datazod/zod-pinecone - A bridge between Turso/SQLite databases and Pinecone vector search for AI applications

Together, these packages provide a complete solution for schema-driven database design, data operations, and vector search integration.

Contributing

Contributions are really welcome! As a solo developer, I'm actively looking for help to make this package better. Whether you want to:

• Report bugs or issues
• Suggest new features
• Improve documentation
• Add support for more SQL dialects
• Optimize performance
• Write more tests

Please feel free to:

• Open issues on GitHub
• Submit pull requests
• Share your use cases and feedback

Every contribution, no matter how small, helps make this package better for everyone.

Thanks

Special thanks to all contributors and users who have provided feedback, reported issues, and helped improve this package. Your input is invaluable in making @datazod/zod-sql better!

Note: This package is optimized for Bun but works with any JavaScript runtime.

Features

Core Functionality

• Convert Zod schemas to SQL table definitions - Generate CREATE TABLE statements from your Zod schemas
• Multi-dialect support - SQLite/Turso, PostgreSQL, and MySQL with dialect-specific optimizations
• Intelligent type mapping - Correctly map Zod types to their SQL equivalents with proper constraints
• Nested object flattening - Flatten nested objects to any depth with configurable strategies

Advanced Features

• Auto-generated fields - Auto-incrementing IDs, UUIDs, timestamps (created_at, updated_at)
• Flexible column control - Control column ordering, add extra columns, define foreign keys
• Index generation - Create single and compound indexes with proper naming
• Primary key support - Single column or compound primary keys
• Nullable/Optional handling - Proper NOT NULL constraints based on Zod schema

New Enhanced Capabilities

• Pre-built schema utilities - timeStampsSchema, autoIdSchema, autoIdSchemaWithTimestamps for common patterns
• JSON schema generation - createFlattenedSchemaJson() for schema introspection and tooling
• Advanced type inference - TableTypes<T> for TypeScript type-level flattening of interfaces
• Enhanced structure extraction - extractTableStructure() for programmatic schema analysis
• Comprehensive testing - Well-tested with extensive test coverage using Bun's native test runner

Installation

# With Bun (recommended)
bun add @datazod/zod-sql

# With npm
npm install @datazod/zod-sql

# With yarn
yarn add @datazod/zod-sql

# With pnpm
pnpm add @datazod/zod-sql

Basic Usage

import { z } from 'zod'
import {
	createTableDDL,
	createTable,
	createFlattenedSchemaJson,
	timeStampsSchema,
	autoIdSchema,
	autoIdSchemaWithTimestamps
} from '@datazod/zod-sql'

// Define your schema with Zod
const UserSchema = z.object({
	id: z.string().uuid(),
	name: z.string().min(1),
	email: z.string().email(),
	isActive: z.boolean().default(true),
	createdAt: z.date()
})

// Option 1: Generate just the SQL DDL statement
// Generate SQLite table definition (default dialect)
const sqliteSQL = createTableDDL('users', UserSchema, {
	primaryKey: 'id'
})

// Generate PostgreSQL table definition
const postgresSQL = createTableDDL('users', UserSchema, {
	dialect: 'postgres',
	primaryKey: 'id'
})

// Generate MySQL table definition
const mysqlSQL = createTableDDL('users', UserSchema, {
	dialect: 'mysql',
	primaryKey: 'id'
})

console.log(sqliteSQL)
// CREATE TABLE IF NOT EXISTS "users" (
//   "id" TEXT NOT NULL PRIMARY KEY,
//   "name" TEXT NOT NULL,
//   "email" TEXT NOT NULL,
//   "isActive" BOOLEAN NOT NULL,
//   "createdAt" TEXT NOT NULL
// );

// Option 2: Get SQL and schema types together
const { table, indexes, schema, structure } = createTable('users', UserSchema, {
	dialect: 'postgres',
	primaryKey: 'id'
})

// Use the schema type for validation and type inference
type User = z.infer<typeof schema>

New Schema Utilities

Pre-built Schemas

import { z } from 'zod'
import {
	timeStampsSchema,
	autoIdSchema,
	autoIdSchemaWithTimestamps
} from '@datazod/zod-sql'

// Use pre-built schemas for common patterns
const UserSchema = z
	.object({
		name: z.string(),
		email: z.string().email()
	})
	.merge(autoIdSchemaWithTimestamps)

// Or build your own combinations
const PostSchema = z
	.object({
		title: z.string(),
		content: z.string()
	})
	.merge(timeStampsSchema)

// Generate SQL with these merged schemas
const sql = createTableDDL('users', UserSchema, { dialect: 'postgres' })
// Automatically includes id, created_at, updated_at columns

JSON Schema Generation

import { createFlattenedSchemaJson } from '@datazod/zod-sql'

const NestedSchema = z.object({
	user: z.object({
		profile: z.object({
			name: z.string(),
			age: z.number()
		})
	}),
	active: z.boolean()
})

// Generate flattened JSON representation
const flatJson = createFlattenedSchemaJson(NestedSchema, { flattenDepth: 2 })
console.log(flatJson)
// {
//   user_profile_name: "string",
//   user_profile_age: "number",
//   active: "boolean"
// }

Advanced Type Inference

import type { TableTypes } from '@datazod/zod-sql'

// Advanced flattening types for complex interfaces
type MyInterface = {
	user: {
		name: string
		profile: {
			bio: string
			age: number
		}
	}
	active: boolean
}

// TableTypes automatically flattens nested structures
type FlattenedTable = TableTypes<MyInterface>
// Result: {
//   user_name: string
//   user_profile_bio: string
//   user_profile_age: number
//   active: boolean
// }

Advanced Features

Compound Primary Keys

const UserRoleSchema = z.object({
	userId: z.string().uuid(),
	roleId: z.string().uuid(),
	assignedAt: z.date()
})

const sql = createTableDDL('user_roles', UserRoleSchema, {
	primaryKey: ['userId', 'roleId']
})

Indexes

const UserSchema = z.object({
	id: z.string().uuid(),
	email: z.string().email(),
	name: z.string()
})

const sql = createTableDDL('users', UserSchema, {
	primaryKey: 'id',
	indexes: {
		users_email_idx: ['email'],
		users_name_email_idx: ['name', 'email']
	}
})

Flatten Nested Objects

const UserSchema = z.object({
	id: z.string().uuid(),
	profile: z.object({
		firstName: z.string(),
		lastName: z.string(),
		address: z.object({
			street: z.string(),
			city: z.string(),
			country: z.object({
				code: z.string(),
				name: z.string(),
				details: z.object({
					continent: z.string(),
					population: z.number().int()
				})
			})
		})
	})
})

// Default flattening depth is 2
const sql = createTableDDL('users', UserSchema, {
	primaryKey: 'id',
	// Override default flattening depth to flatten deeply
	flattenDepth: 5
})

// Will generate flattened columns with proper types:
// - id TEXT PRIMARY KEY
// - profile_firstName TEXT
// - profile_lastName TEXT
// - profile_address_street TEXT
// - profile_address_city TEXT
// - profile_address_country_code TEXT
// - profile_address_country_name TEXT
// - profile_address_country_details_continent TEXT
// - profile_address_country_details_population INTEGER

// With flattenDepth: 0, objects are stored as JSON:
const noFlattenSql = createTableDDL('users', UserSchema, {
	primaryKey: 'id',
	flattenDepth: 0
})
// - id TEXT PRIMARY KEY
// - profile TEXT (stored as JSON)

Extra Columns and Foreign Keys

const PostSchema = z.object({
	id: z.string().uuid(),
	title: z.string(),
	content: z.string()
})

const sql = createTableDDL('posts', PostSchema, {
	primaryKey: 'id',
	extraColumns: [
		{
			name: 'author_id',
			type: 'TEXT',
			notNull: true,
			references: {
				table: 'users',
				column: 'id',
				onDelete: 'CASCADE'
			}
		},
		{
			name: 'status',
			type: 'TEXT',
			defaultValue: "'draft'",
			notNull: true
		}
	]
})

Automatic ID and Timestamps

const PostSchema = z.object({
	title: z.string(),
	content: z.string()
})

const sql = createTableDDL('posts', PostSchema, {
	autoId: true, // Adds an auto-incrementing primary key 'id' column
	timestamps: true // Adds created_at and updated_at columns
})

// Results in:
// CREATE TABLE IF NOT EXISTS "posts" (
//   "id" INTEGER PRIMARY KEY AUTOINCREMENT,
//   "created_at" TEXT NOT NULL DEFAULT (datetime('now')),
//   "updated_at" TEXT NOT NULL DEFAULT (datetime('now')),
//   "title" TEXT NOT NULL,
//   "content" TEXT NOT NULL
// );

// With UUID as primary key (uses uuid() function in Turso):
const sqlWithUuid = createTableDDL('posts', PostSchema, {
	autoId: {
		enabled: true,
		type: 'uuid',
		name: 'post_id' // Custom ID column name (default is 'id')
	},
	timestamps: true
})

// Results in:
// CREATE TABLE IF NOT EXISTS "posts" (
//   "post_id" TEXT PRIMARY KEY DEFAULT (uuid()),
//   "created_at" TEXT NOT NULL DEFAULT (datetime('now')),
//   "updated_at" TEXT NOT NULL DEFAULT (datetime('now')),
//   "title" TEXT NOT NULL,
//   "content" TEXT NOT NULL
// );

Separate Table and Index Creation

const UserSchema = z.object({
	id: z.string().uuid(),
	email: z.string().email(),
	name: z.string()
})

const { table, indexes, schema, structure } = createTable('users', UserSchema, {
	primaryKey: 'id',
	indexes: {
		users_email_idx: ['email']
	}
})

console.log(table) // Execute this first in a transaction
indexes.forEach((idx) => console.log(idx)) // Then execute each index

// You can also use the schema for validation
const userData = {
	id: 'f8c3de3d-1fea-4d7c-a8b0-29f63c4c3454',
	name: 'John Doe',
	email: 'john@example.com'
}

// Validates the data against the schema
const validUser = schema.parse(userData)

Type-Safe Data Handling

One of the key benefits of @datazod/zod-sql is the ability to get TypeScript types that match your database schema:

import { z } from 'zod'
import { createTable } from '@datazod/zod-sql'

// Define your schema
const ProductSchema = z.object({
	name: z.string(),
	price: z.number().positive(),
	description: z.string().optional(),
	categories: z.array(z.string())
})

// Generate SQL and get the table schema
const { table, schema, structure } = createTable('products', ProductSchema, {
	autoId: true,
	timestamps: true
})

// Get TypeScript type for your table rows
type Product = z.infer<typeof schema>

// Now you can use this type with your database operations
function insertProduct(
	product: Omit<Product, 'id' | 'created_at' | 'updated_at'>
) {
	// These fields will be auto-generated
	console.log(`Inserting: ${product.name}`)
	// In a real app: db.insert('products', product);
}

// Correctly typed - TypeScript knows about all fields including nested structures
insertProduct({
	name: 'Laptop',
	price: 999.99,
	description: 'Powerful laptop',
	categories: ['electronics', 'computers']
})

API Reference

Main Functions

• createTableDDL(tableName, schema, options) - Generate SQL for creating tables only
• createTable(tableName, schema, options) - Generate SQL and get type information

Schema Type Functions

• extractTableSchema(schema, options) - Get Zod schema for table rows
• extractTableStructure(tableName, schema, options) - Get detailed table structure

Options

interface TableOptions {
	dialect?: 'sqlite' | 'postgres' | 'mysql'
	primaryKey?: string | string[]
	indexes?: Record<string, string[]>
	flattenDepth?: number
	extraColumns?: ExtraColumn[]
	timestamps?: boolean
	autoId?: boolean | AutoIdConfig
}

interface AutoIdConfig {
	enabled: boolean
	name?: string // Default: 'id'
	type?: 'integer' | 'uuid' // Default: 'integer'
}

interface ExtraColumn {
	name: string
	type: string
	notNull?: boolean
	defaultValue?: string
	primaryKey?: boolean
	unique?: boolean
	position?: 'start' | 'end'
	references?: {
		table: string
		column: string
		onDelete?: 'CASCADE' | 'SET NULL' | 'RESTRICT' | 'NO ACTION' | 'SET DEFAULT'
		onUpdate?: 'CASCADE' | 'SET NULL' | 'RESTRICT' | 'NO ACTION' | 'SET DEFAULT'
	}
}

Type Mapping

Column Ordering

Columns are arranged in a specific order:

1. Auto ID Column: If autoId is enabled, it appears first
2. Timestamp Columns: If timestamps is enabled, created_at/updated_at appear next
3. Start Position Columns: Extra columns with position: 'start'
4. Schema Columns: Fields from your Zod schema
5. End Position Columns: Extra columns with position: 'end' or no position specified

This ordering helps maintain a consistent table structure with important fields at the beginning.

Type Inference for Table Schemas

One of the key features of this package is its ability to provide TypeScript types that match your database schema:

Understanding the Return Values

When you call createTable(), you get an object with these properties:

const {
	table, // SQL DDL statement for creating the table
	indexes, // Array of SQL statements for creating indexes
	schema, // Zod schema for validating data against the table structure
	structure // Detailed information about table columns and properties
} = createTable('users', UserSchema, options)

Using the Schema for Type Safety

// Get the TypeScript type for your table rows
type User = z.infer<typeof schema>

// This type includes all columns in your table:
// - Original fields from your schema
// - Flattened nested objects based on flattenDepth
// - Auto-generated fields (id, timestamps)
// - Extra columns you defined

Working with Table Structure

The structure property gives you programmatic access to column information:

// Inspect column details
structure.columns.forEach((column) => {
	console.log(
		`${column.name}: ${column.type} ${column.nullable ? 'NULL' : 'NOT NULL'}`
	)
})

// Generate dynamic queries
function buildSelectQuery(fields: string[] = []) {
	const columns =
		fields.length > 0
			? fields.join(', ')
			: structure.columns.map((c) => c.name).join(', ')
	return `SELECT ${columns} FROM ${structure.tableName}`
}

Performance

This package is optimized for Bun with native ESM support. Tests and benchmarks run significantly faster in Bun compared to Node.js, providing a better developer experience.

License

MIT