Database Migrations

This guide covers how schema changes are applied to the PostgreSQL database using Drizzle Kit.

How Schema Changes Are Applied

The application uses Drizzle ORM with the following configuration:

Component	File Path	Description
Schema definitions	shared/schema.ts	All table definitions, types, and Zod schemas
Drizzle config	drizzle.config.ts	Configuration for Drizzle Kit (outputs to ./migrations)
Push command	npm run db:push	Applies schema changes directly to the database

Schema File

All database tables are defined in shared/schema.ts. This file contains:

• Table definitions using pgTable()
• Insert schemas using createInsertSchema() from drizzle-zod
• TypeScript types for each model

Drizzle Configuration

The drizzle.config.ts file configures Drizzle Kit:

• Dialect: PostgreSQL
• Schema path: ./shared/schema.ts
• Output directory: ./migrations
• Database URL: Read from DATABASE_URL environment variable

Push Command

From package.json (line 12):

npm run db:push

This runs drizzle-kit push which applies schema changes directly to the database without generating migration files.

Verification Steps

1. Check DATABASE_URL is Set

Ensure the DATABASE_URL environment variable is configured before running migrations:

echo $DATABASE_URL

If not set, the drizzle.config.ts will throw an error: "DATABASE_URL, ensure the database is provisioned".

2. Run the Migration

npm run db:push

Drizzle Kit will compare your schema in shared/schema.ts against the live database and apply any differences.

3. Verify with SQL Queries

After pushing, verify tables exist by querying the database:

SELECT table_name FROM information_schema.tables 
WHERE table_schema = 'public';

Or check a specific table structure:

\d users
\d documents
\d runs

Rollback Guidance

Important Limitations

Drizzle Kit's push command does not auto-generate down migrations. This means:

• There is no automatic rollback mechanism
• Schema changes are applied directly to the database
• Destructive changes (dropping columns/tables) cannot be automatically reversed

Manual Rollback Process

If you need to rollback a schema change, you must write reverse SQL manually:

-- Example: Revert adding a column
ALTER TABLE documents DROP COLUMN IF EXISTS new_column;

-- Example: Revert dropping a column (requires knowing the original definition)
ALTER TABLE documents ADD COLUMN old_column TEXT;

Best Practices

1. Create a backup before migrations

   • Export critical data before applying schema changes
   • Use pg_dump or Replit's database backup features

2. Use Replit's checkpoint feature

   • Before running migrations, create a Replit checkpoint
   • This allows you to restore both code and database state if something goes wrong

3. Test in development first

   • Always run npm run db:push in development before production
   • Verify application functionality after schema changes

4. Review schema changes carefully

   • Drizzle Kit will show you what changes will be applied
   • Review destructive operations (DROP) before confirming