Database Migration Guide

Quick Start

Essential workflow for creating database migrations:

Create/modify JPA entities with proper annotations
Generate migration:

npm run db:draft-changelog

VALIDATE & edit the generated changelog_new.xml (critical!)
Rename and move the file:

# Use the changeset ID from the XML file as filename
mv server/application-server/src/main/resources/db/changelog_new.xml \
   server/application-server/src/main/resources/db/changelog/1749286026779_changelog.xml

Update documentation:

npm run db:generate-erd-docs

Commit and create PR:

git add . && git commit -m "Add migration for user entity changes"

Danger

⚠️ ALWAYS VALIDATE: Generated migrations can contain data-destructive operations! See validation checklist below.

Why Migrations?

Database migrations ensure schema changes are versioned, reviewable, and safely deployable across all environments. Without them, team members would have inconsistent database states leading to errors and deployment issues.

Development Workflow

1. Prototyping Phase

Modify JPA entities as needed
Use spring.jpa.hibernate.ddl-auto=update (default) for automatic schema updates
Test locally without creating migrations

2. Production-Ready Phase

When ready to share changes:

# Generate migration from your entity changes
npm run db:draft-changelog

3. Validate Generated Migration

This is the most critical step - generated migrations often contain errors:

Validation Checklist

[ ] No data loss: Check for dropColumn + addColumn pairs (should be renameColumn)
[ ] Correct author: Replace “user (generated)” with your GitHub username
[ ] Clean sequences: New sequences start at 1, not current DB values
[ ] No system objects: Remove any pg_* or information_schema* tables
[ ] Correct order: Columns before indexes before constraints

Common Issues to Fix

<!-- ❌ Data loss pattern -->
<dropColumn tableName="user" columnName="first_name"/>
<addColumn tableName="user">
    <column name="firstName" type="VARCHAR(255)"/>
</addColumn>

<!-- ✅ Safe rename -->
<renameColumn tableName="user"
              oldColumnName="first_name"
              newColumnName="firstName"/>

4. Finalize Migration

# Rename using changeset ID from the XML
mv server/application-server/src/main/resources/db/changelog_new.xml \
   server/application-server/src/main/resources/db/changelog/1749286026779_changelog.xml

# Update documentation
npm run db:generate-erd-docs

# Commit changes
git add . && git commit -m "Add migration for user entity changes"

Examples

Adding a New Field

1. Modify Entity:

@Entity
public class User {
    // ...existing fields...
    @NonNull
    private String email;  // ← New field
}

2. Generate & Validate Migration:

npm run db:draft-changelog

Then review changelog_new.xml, fix author, validate content.

3. Expected Migration:

<changeSet author="yourusername" id="1749286026779-1">
    <addColumn tableName="user">
        <column name="email" type="VARCHAR(255)">
            <constraints nullable="false"/>
        </column>
    </addColumn>
</changeSet>

Creating a New Entity

1. Create Entity:

@Entity
@Table(name = "notification")
public class Notification {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @NonNull
    private String message;

    @ManyToOne
    @JoinColumn(name = "user_id")
    private User user;
}

2. Generate migration and follow the same validation process.

Best Practices

One logical change per migration
Test migrations on fresh database
Keep changeset IDs descriptive
Always validate generated content
Update ERD documentation after moving migration files

Advanced Topics

Manual Migration Editing

For complex data transformations:

<changeSet author="yourusername" id="1234567890123-1">
    <!-- Add new column -->
    <addColumn tableName="user">
        <column name="status_new" type="VARCHAR(50)"/>
    </addColumn>

    <!-- Convert data -->
    <sql>
        UPDATE user SET status_new = CASE
            WHEN status = '1' THEN 'ACTIVE'
            WHEN status = '0' THEN 'INACTIVE'
            ELSE 'UNKNOWN'
        END;
    </sql>

    <!-- Replace old column -->
    <dropColumn tableName="user" columnName="status"/>
    <renameColumn tableName="user"
                  oldColumnName="status_new"
                  newColumnName="status"/>
</changeSet>

CI Pipeline Integration

The CI pipeline validates:

All migrations apply successfully
ERD documentation matches actual schema
No schema drift exists