Database Migrations

Database migrations in Helper are managed using Drizzle ORM.

Normal Migration Workflow

When you need to make changes to the database schema:

1. Modify Schema Files

Update the TypeScript schema definitions in the db/schema/ directory. Each domain entity has its own schema file (e.g., mailboxes.ts, conversations.ts, userProfiles.ts).

// Example: Adding a new column to db/schema/mailboxes.ts
export const mailboxes = pgTable("mailboxes", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: text("name").notNull(),
  // Add your new column here
  newField: text("new_field"),
  ...withTimestamps,
});

2. Generate Migration

Run the generate command to create migration files based on your schema changes:

pnpm db:generate

This command will generate SQL migration files in db/drizzle/ to adjust the schema to match your changes.

3. Apply Migration

Apply the generated migration to your local database:

pnpm db:migrate

This command will update your local database to match the new schema definitions.

4. Commit Everything

Add and commit all generated files, including the JSON metadata that Drizzle creates:

git add db/drizzle/ db/schema/
git commit -m "Add new_field to mailboxes table"

Handling Migration Conflicts

You may encounter merge conflicts with migration files. Here's how to resolve them:

1. Delete Your Migration Files

Remove the migration(s) and JSON snapshot(s) in your PR from the db/drizzle/ directory:

rm db/drizzle/0XXX_your_migration_name.sql
rm db/drizzle/meta/0XXX_snapshot.json

2. Reset Migration Journal

Reset the db/drizzle/meta/_journal.json file to match the main branch:

git checkout main -- db/drizzle/meta/_journal.json

3. Reset Database

Clear and reseed your local database to ensure a clean state:

pnpm db:reset

4. Regenerate Your Changes

Now repeat the normal workflow:

1. Run pnpm db:generate
2. Run pnpm db:migrate
3. Commit all files