Database Migrations

Overview

Autonome uses Drizzle Kit to manage database migrations. The workflow is:

Edit schema: Modify src/db/schema.ts
Generate migration: Run bun run db:generate
Review SQL: Check generated migration in drizzle/ directory
Apply migration: Run bun run db:migrate

Configuration

Drizzle Kit configuration is in drizzle.config.ts:

import { env } from '@/env';
import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  dialect: "postgresql",
  out: './drizzle',              // Migration files output directory
  schema: './src/db/schema.ts',  // Schema source file
  dbCredentials: {
    url: env.DATABASE_URL || "",
    ssl: {
      rejectUnauthorized: false,
    },
  },
})

Key Settings:

dialect: PostgreSQL database
out: Migrations stored in drizzle/ directory
schema: Single source of truth at src/db/schema.ts
dbCredentials.url: Read from DATABASE_URL environment variable

Available Commands

All database commands are defined in package.json:

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:push": "drizzle-kit push",
    "db:seed": "bun run scripts/seed.ts",
    "db:studio": "drizzle-kit studio"
  }
}

db:generate

Generate migration files from schema changes:

bun run db:generate

What it does:

Compares src/db/schema.ts to current database state
Generates SQL migration file in drizzle/ directory
Creates a metadata file (.json) for tracking

Example output:

📦 Generating migration...
✅ Migration generated: drizzle/0001_add_exit_plan_to_orders.sql

When to use:

After modifying tables, columns, or indexes in src/db/schema.ts
Before committing schema changes to Git

db:migrate

Apply pending migrations to the database:

bun run db:migrate

What it does:

Reads all migration files in drizzle/
Checks which migrations have been applied (via __drizzle_migrations table)
Applies pending migrations in order

Example output:

📦 Applying migrations...
✅ Applied: 0001_add_exit_plan_to_orders.sql
✅ Migration complete!

When to use:

After generating a new migration
On production deployment to sync database
When setting up a new development environment

db:push

Push schema changes directly to database (skip migration generation):

bun run db:push

What it does:

Compares src/db/schema.ts to database
Applies changes directly without generating migration files

⚠️ Warning: Use only in development. This bypasses migration history. When to use:

Rapid prototyping in local development
Throwaway feature branches
Never on production

db:seed

Reset database and seed with default models:

bun run db:seed

What it does:

Drops all tables
Recreates schema
Seeds with default AI models and variants

⚠️ Warning: Destructive operation. Only use in development.

db:studio

Launch Drizzle Studio (database GUI):

bun run db:studio

What it does:

Starts local web server
Opens browser at https://local.drizzle.studio
Provides GUI for browsing/editing database data

When to use:

Debugging database state
Manual data inspection
Quick one-off updates

Migration Workflow

Step 1: Modify Schema

Edit src/db/schema.ts with your changes:

// Example: Add new column to Orders table
export const orders = pgTable(
	"Orders",
	{
		id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
		modelId: text("modelId").notNull(),
		symbol: text("symbol").notNull(),
		// ... existing columns ...
		
		// NEW: Add trailing stop percentage
		trailingStopPercent: numeric("trailingStopPercent", { 
			precision: 5, 
			scale: 2 
		}),
	},
);

Step 2: Generate Migration

bun run db:generate

Review the generated SQL file in drizzle/:

-- drizzle/0002_add_trailing_stop.sql
ALTER TABLE "Orders" ADD COLUMN "trailingStopPercent" numeric(5, 2);

Check for:

Correct column types
Proper table names (quoted if capitalized)
Safe default values (avoid breaking existing data)

Step 3: Apply Migration

bun run db:migrate

Verify migration succeeded:

bun run db:studio
# Check that new column appears in Orders table

Step 4: Commit Changes

git add src/db/schema.ts drizzle/
git commit -m "feat: add trailing stop to orders"

Always commit both:

src/db/schema.ts (TypeScript schema)
drizzle/XXXX_*.sql (generated SQL migration)

Common Migration Scenarios

Adding a Column

// src/db/schema.ts
export const orders = pgTable("Orders", {
	// ... existing columns ...
	newColumn: text("newColumn"), // Nullable by default
});

Then:

bun run db:generate
bun run db:migrate

Adding a Required Column with Default

// src/db/schema.ts
export const orders = pgTable("Orders", {
	// ... existing columns ...
	newColumn: text("newColumn").notNull().default("default_value"),
});

Why default? Existing rows need a value for NOT NULL columns.

Renaming a Column

Drizzle cannot detect renames automatically. You must:

Generate SQL for new column:

export const orders = pgTable("Orders", {
	// oldName: text("oldName"),  ← Comment out
	newName: text("newName"),     ← Add new
});

Manually edit migration:

-- drizzle/XXXX_rename_column.sql
ALTER TABLE "Orders" RENAME COLUMN "oldName" TO "newName";

Apply migration:
bun run db:migrate

Dropping a Column

// src/db/schema.ts
export const orders = pgTable("Orders", {
	// Remove column definition
	// oldColumn: text("oldColumn"), ← Delete this line
});

Then:

bun run db:generate
bun run db:migrate

⚠️ Warning: Data loss is permanent. Consider:

Backing up data first
Using a two-step migration (make nullable → drop later)

Adding an Index

// src/db/schema.ts
export const orders = pgTable(
	"Orders",
	{ /* columns */ },
	(table) => ({
		// Add new index
		symbolStatusIdx: index("Orders_symbol_status_idx")
			.on(table.symbol, table.status),
	}),
);

Then:

bun run db:generate
bun run db:migrate

Creating a New Table

// src/db/schema.ts
export const trades = pgTable("Trades", {
	id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
	orderId: text("orderId").notNull().references(() => orders.id),
	executedAt: timestamp("executedAt").defaultNow().notNull(),
});

Then:

bun run db:generate
bun run db:migrate

Migration Best Practices

1. Always Review Generated SQL

Don’t blindly apply migrations:

bun run db:generate
cat drizzle/XXXX_*.sql  # Review the SQL

Check for:

Destructive operations (DROP, ALTER)
Missing defaults for NOT NULL columns
Incorrect data types

2. Test Migrations Locally First

# Local testing
bun run db:generate
bun run db:migrate

# Verify with Studio
bun run db:studio

Only deploy to production after verifying locally.

3. Use Transactions for Complex Migrations

For multi-step migrations, wrap in a transaction:

-- drizzle/XXXX_complex_migration.sql
BEGIN;

ALTER TABLE "Orders" ADD COLUMN "newColumn" text;
UPDATE "Orders" SET "newColumn" = 'default';
ALTER TABLE "Orders" ALTER COLUMN "newColumn" SET NOT NULL;

COMMIT;

If any step fails, entire migration rolls back.

4. Never Edit Applied Migrations

Once a migration is applied:

Don’t edit the SQL file
Don’t rename the file
Create a new migration to fix issues

Why? Migration history must be immutable for consistency across environments.

5. Handle Breaking Changes Carefully

For destructive changes:

Add new column (nullable)
Migrate data from old → new
Make required (add NOT NULL constraint)
Drop old column (in separate migration)

This allows rollback at each step.

Troubleshooting

”Migration already applied”

Migration file exists but hasn’t been applied:

# Check applied migrations
psql $DATABASE_URL -c "SELECT * FROM __drizzle_migrations;"

# Force re-apply (dangerous!)
rm -rf drizzle/meta  # Delete local meta cache
bun run db:migrate

“Column already exists”

Schema out of sync with database:

# Option 1: Drop column manually
psql $DATABASE_URL -c 'ALTER TABLE "Orders" DROP COLUMN "columnName";'

# Option 2: Reset database (dev only)
bun run db:seed

“SSL connection required”

PostgreSQL SSL configuration issue:

// drizzle.config.ts
dbCredentials: {
  url: env.DATABASE_URL,
  ssl: {
    rejectUnauthorized: false,  // Allow self-signed certs
  },
}

“Cannot read properties of undefined”

Missing DATABASE_URL environment variable:

# Check .env.local
cat .env.local | grep DATABASE_URL

# Set manually
export DATABASE_URL="postgresql://..."
bun run db:migrate

Production Deployment

For production deployments:

Commit migrations to Git
Deploy code with new schema file

Run migrations on production database:

DATABASE_URL="postgresql://prod..." bun run db:migrate

Restart application to use new schema

Rollback strategy:

Keep previous deployment available
Test rollback migrations in staging first
Consider blue-green deployments for zero downtime

Next Steps

Database Schema - Review complete schema documentation
Repositories - Learn data access patterns

Architecture

Database

Configuration

Development

Database Migrations

Overview

Configuration

Available Commands

db:generate

db:migrate

db:push

db:seed

db:studio

Migration Workflow

Step 1: Modify Schema

Step 2: Generate Migration

Step 3: Apply Migration

Step 4: Commit Changes

Common Migration Scenarios

Adding a Column

Adding a Required Column with Default

Renaming a Column

Dropping a Column

Adding an Index

Creating a New Table

Migration Best Practices

1. Always Review Generated SQL

2. Test Migrations Locally First

3. Use Transactions for Complex Migrations

4. Never Edit Applied Migrations

5. Handle Breaking Changes Carefully

Troubleshooting

”Migration already applied”

“Column already exists”

“SSL connection required”

“Cannot read properties of undefined”

Production Deployment

Next Steps

​Overview

​Configuration

​Available Commands

​db:generate

​db:migrate

​db:push

​db:seed

​db:studio

​Migration Workflow

​Step 1: Modify Schema

​Step 2: Generate Migration

​Step 3: Apply Migration

​Step 4: Commit Changes

​Common Migration Scenarios

​Adding a Column

​Adding a Required Column with Default

​Renaming a Column

​Dropping a Column

​Adding an Index

​Creating a New Table

​Migration Best Practices

​1. Always Review Generated SQL

​2. Test Migrations Locally First

​3. Use Transactions for Complex Migrations

​4. Never Edit Applied Migrations

​5. Handle Breaking Changes Carefully

​Troubleshooting

​”Migration already applied”

​“Column already exists”

​“SSL connection required”

​“Cannot read properties of undefined”

​Production Deployment

​Next Steps

Overview

Configuration

Available Commands

db:generate

db:migrate

db:push

db:seed

db:studio

Migration Workflow

Step 1: Modify Schema

Step 2: Generate Migration

Step 3: Apply Migration

Step 4: Commit Changes

Common Migration Scenarios

Adding a Column

Adding a Required Column with Default

Renaming a Column

Dropping a Column

Adding an Index

Creating a New Table

Migration Best Practices

1. Always Review Generated SQL

2. Test Migrations Locally First

3. Use Transactions for Complex Migrations

4. Never Edit Applied Migrations

5. Handle Breaking Changes Carefully

Troubleshooting

”Migration already applied”

“Column already exists”

“SSL connection required”

“Cannot read properties of undefined”

Production Deployment

Next Steps