📊 Schema Definition (schema/*.ts)
    ↓ (drizzle-kit generate)
📄 Migration Files (migrations/*.sql)
    ↓ (drizzle-kit push/migrate)
🐘 PostgreSQL Database

# Push schema changes directly (development only)
pnpm db:push

# Generate migration files from schema changes
pnpm db:generate

# Check for schema issues
pnpm db:check

# Open visual database browser
pnpm db:studio

# Run migrations (production safe)
pnpm db:migrate

# Run migrations with custom script
pnpm db:migrate:dev    # Generate migrations
pnpm db:migrate:prod   # Apply migrations in production

// packages/database/schema/users.ts
export const users = pgTable(
  'users',
  {
    id: text('id').primaryKey().$defaultFn(() => randomUUID()),
    email: text('email').notNull().unique(),
    name: text('name').notNull(),
    
    // ✅ Add new columns here
    bio: text('bio'), // New column - nullable by default
    isActive: boolean('is_active').default(true), // With default value
    
    // Timestamps
    createdAt: timestamp('created_at', { mode: 'date' }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { mode: 'date' }).notNull().defaultNow(),
  },
  (table) => ({
    // Add indexes for new columns
    emailIdx: index('users_email_idx').on(table.email),
    isActiveIdx: index('users_is_active_idx').on(table.isActive), // New index
  })
);

# 1. Edit schema files
# 2. Push changes directly to development database
pnpm db:push

# 3. Verify changes in Drizzle Studio
pnpm db:studio

# 1. Generate migration files from schema changes
pnpm db:generate

# 2. Review generated migration files
ls packages/database/drizzle/

# 3. Test migrations on staging database
NODE_ENV=staging pnpm db:migrate

# 4. Commit migration files to git
git add packages/database/drizzle/
git commit -m "feat: add user bio and active status fields"

pnpm db:generate

-- packages/database/drizzle/0001_add_user_bio.sql
ALTER TABLE "users" ADD COLUMN "bio" text;
ALTER TABLE "users" ADD COLUMN "is_active" boolean DEFAULT true;
CREATE INDEX "users_is_active_idx" ON "users" ("is_active");

// packages/database/drizzle/meta/_journal.json
{
  "version": "7",
  "dialect": "postgresql", 
  "entries": [
    {
      "idx": 0,
      "version": "7",
      "when": 1234567890,
      "tag": "0001_add_user_bio",
      "breakpoints": false
    }
  ]
}

# Direct schema synchronization
pnpm db:push

# Apply migration files
pnpm db:migrate

// scripts/migrate.ts (Solo Kit's custom migration runner)
async function runMigrations() {
  console.log('🚀 Starting database migrations...');
  
  // Create PostgreSQL connection
  const client = postgres(process.env.DATABASE_URL, {
    max: 1, // Single connection for migrations
    prepare: false, // Better compatibility
    connect_timeout: 60, // Longer timeout for migrations
  });

  const db = drizzle(client);
  
  // Apply migrations from drizzle folder
  await migrate(db, { migrationsFolder: './packages/database/drizzle' });
  
  console.log('✅ Migrations completed successfully!');
}

// Add nullable columns
newField: text('new_field'), // Safe - nullable by default

// Add columns with defaults
isActive: boolean('is_active').default(true), // Safe - default provided

// Add indexes (PostgreSQL creates them concurrently)
// In migration SQL:
CREATE INDEX CONCURRENTLY "users_email_idx" ON "users" ("email");

// Add new tables
export const userProfiles = pgTable('user_profiles', {
  // ... table definition
});

// Remove columns (loses data)
// Don't do this: remove existing fields

// Rename columns (requires coordinated deployment)
// Don't do this directly - use multi-step process

// Change column types (may require data conversion)
// Be careful with: text to integer, etc.

// Add NOT NULL constraints to existing columns
// Don't do this: .notNull() on existing nullable columns

export const users = pgTable('users', {
  // Existing columns...
  email: text('email').notNull().unique(),
  
  // Add new column alongside old
  newEmail: text('new_email'), // Nullable initially
});

-- Data migration
UPDATE users SET new_email = email WHERE new_email IS NULL;

// Update application code to use newEmail

export const users = pgTable('users', {
  // Remove old column, make new one required
  newEmail: text('new_email').notNull().unique(),
});

-- 0002_user_name_split.sql

-- Add new columns
ALTER TABLE "users" ADD COLUMN "first_name" text;
ALTER TABLE "users" ADD COLUMN "last_name" text;

-- Populate new columns from existing data
UPDATE "users" 
SET 
  "first_name" = split_part("name", ' ', 1),
  "last_name" = CASE 
    WHEN array_length(string_to_array("name", ' '), 1) > 1 
    THEN substring("name" from position(' ' in "name") + 1)
    ELSE ''
  END
WHERE "first_name" IS NULL;

-- Add constraints after data population
ALTER TABLE "users" ALTER COLUMN "first_name" SET NOT NULL;

// scripts/migrations/custom-user-migration.ts
import { db } from '@packages/database';
import { users } from '@packages/database/schema';

export async function migrateUserData() {
  console.log('🔄 Running custom user data migration...');
  
  // Custom migration logic
  const usersToUpdate = await db.select().from(users).where(/* conditions */);
  
  for (const user of usersToUpdate) {
    // Complex data transformation
    await db.update(users)
      .set({
        // Custom updates
        updatedAt: new Date(),
      })
      .where(eq(users.id, user.id));
  }
  
  console.log(`✅ Migrated ${usersToUpdate.length} users`);
}

-- PostgreSQL: Use CONCURRENTLY for zero-downtime index creation
CREATE INDEX CONCURRENTLY "users_email_idx" ON "users" ("email");

-- Composite indexes for common query patterns
CREATE INDEX CONCURRENTLY "users_active_created_idx" 
ON "users" ("is_active", "created_at" DESC);

-- Partial indexes for filtered queries
CREATE INDEX CONCURRENTLY "active_users_email_idx" 
ON "users" ("email") WHERE "is_active" = true;

# Test migration generation
pnpm db:generate --verbose

# Test on staging database first
NODE_ENV=staging pnpm db:migrate

# Verify schema after migration
pnpm db:studio

# Create database backup (Neon example)
neonctl branches create --parent main backup-$(date +%Y%m%d)

# Or traditional pg_dump
pg_dump $DATABASE_URL > backup_$(date +%Y%m%d).sql

// Add timing to migration script
const startTime = Date.now();
await migrate(db, { migrationsFolder: migrationsPath });
const duration = Date.now() - startTime;
console.log(`Migration completed in ${duration}ms`);

# Keep rollback migrations ready
# 0003_remove_user_bio_rollback.sql
ALTER TABLE "users" DROP COLUMN "bio";
DROP INDEX "users_is_active_idx";

# Always review generated SQL before production
cat packages/database/drizzle/0001_*.sql

# Check for potentially dangerous operations
grep -i "DROP\|ALTER.*NOT NULL\|TRUNCATE" packages/database/drizzle/*.sql

# Error: column "bio" of relation "users" already exists
# Solution: Reset migration state or create new migration
pnpm db:push --force

# Detect schema drift
pnpm db:check

# Fix by pushing current schema
pnpm db:push

// Increase timeout in drizzle config
export default defineConfig({
  // ... other config
  dbCredentials: {
    url: enhancedDatabaseUrl(), // Includes timeout parameters
  },
});

# Check DATABASE_URL
echo $DATABASE_URL | head -c 50

# Test connection
pnpm db:studio

# Run migration with verbose logging
pnpm db:migrate --verbose

-- Manually reset migration tracking (emergency only)
DELETE FROM "__drizzle_migrations" WHERE tag = 'problematic_migration';

# Force push current schema (development only)
pnpm db:push --force

# Regenerate migration files from scratch
rm -rf packages/database/drizzle/
pnpm db:generate

# .github/workflows/deploy.yml
- name: Run Database Migrations  
  run: |
    pnpm db:migrate
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}

// Add migration verification
const tablesResult = await client`
  SELECT table_name 
  FROM information_schema.tables 
  WHERE table_schema = 'public'
`;

console.log(`Database has ${tablesResult.length} tables after migration`);