Schema

The database schema is defined in Drizzle schema files located in lib/db/schema/. The schema is organized into multiple files for better maintainability, with each file representing a domain or feature.

Schema Structure

The schema is typically organized as follows:

lib/db/schema/
├── index.ts          # Main schema export
├── users.ts          # User table definition
├── organizations.ts  # Organization tables
└── ...               # Other domain schemas

Updating the Schema

To update your database schema, edit the appropriate schema file. More information about Drizzle schema definitions can be found in the Drizzle documentation.

Example: Adding a Field

For example, to add a new phone field to the users table:

import { pgTable, varchar } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: varchar('id', { length: 255 }).primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: varchar('name', { length: 255 }),
  phone: varchar('phone', { length: 32 }) // New field
  // ... other fields
});

The field is defined as an optional string with a maximum length of 32 characters. Now you need to create a migration to apply this change to the database.

Migration Workflow

Drizzle uses migrations to track and apply database schema changes. The migration workflow consists of three steps:

1. Generate Migration

Create a new migration by running:

npm run db:generate

This command:

• Analyzes your schema files
• Compares them with the current database state
• Generates SQL migration files in lib/db/migrations/
• Creates a migration metadata file

2. Review Migration

Before applying, review the generated migration file to ensure it contains the expected changes:

ALTER TABLE "users" ADD COLUMN "phone" varchar(32);

3. Apply Migration

To apply the migration to your database, run:

npm run db:migrate

This command:

• Executes the migration SQL against your database
• Updates the migration history table
• Ensures your database schema matches your code

Alternative: Push Changes (Development Only)

For rapid prototyping during development, you can push schema changes directly without creating a migration:

npm run db:push

Migration Best Practices

1. Always generate migrations for production deployments
2. Review migration files before applying them
3. Test migrations on a staging database first
4. Commit migration files to version control
5. Never edit existing migrations - create new ones instead

Schema Relationships

Drizzle supports defining relationships between tables. For example:

import { relations } from 'drizzle-orm';

import { organizations } from './organizations';

export const usersRelations = relations(users, ({ many }) => ({
  organizations: many(organizations)
}));

For more information on relationships, see the Drizzle relations documentation.

Regenerating Migrations

If you need to regenerate migrations (e.g., after restoring from a backup), you can use:

npm run db:regenerate

This restores the migration files from the main branch and regenerates new migrations based on your current schema.