Drizzle ORM Database Schema

Create a complete Drizzle ORM setup for [Application Type] using PostgreSQL.

Requirements

1. Schema Definition

Define the database schema with:

• [Number] main tables for [entities]
• Proper relationships between tables
• Type-safe column definitions
• Timestamps for created/updated tracking
• Appropriate constraints (unique, not null, etc.)

2. Table Structure

For each table, include:

• Primary key (auto-incrementing serial)
• Required fields with .notNull()
• Optional fields where appropriate
• Foreign key references
• Default values where needed
• Indexes for frequently queried fields

3. Queries Module

Create a queries file with common operations:

• Select all - Fetch all records
• Select by ID - Find single record
• Insert - Create new records with .returning()
• Update - Modify existing records
• Delete - Remove records
• Joins - Query related data across tables

4. Migration Configuration

Set up Drizzle Kit config:

• Schema path configuration
• Output directory for migrations
• Database driver specification
• Connection settings

5. Type Safety

• Export all table schemas
• Export inferred TypeScript types using InferModel
• Ensure full type coverage for insert/update operations

Example Structure

// schema.ts
import { pgTable, serial, text, timestamp, boolean, integer } from 'drizzle-orm/pg-core'

export const [tableName] = pgTable('[table_name]', {
  id: serial('id').primaryKey(),
  // Add columns based on requirements
})

// db.ts
import { drizzle } from 'drizzle-orm/node-postgres'
import { Pool } from 'pg'

const pool = new Pool({ connectionString: process.env.DATABASE_URL })
export const db = drizzle(pool)

// queries.ts - Common operations
import { db } from './db'
import { eq } from 'drizzle-orm'
import { [tableName] } from './schema'

export async function getAll() {
  return await db.select().from([tableName])
}

Best Practices

• Use proper foreign key constraints
• Add indexes for performance
• Use transactions for multi-step operations
• Implement proper error handling
• Type all operations with TypeScript