When to Write Database Migration Scripts: Right Away or Later?

Introduction

One of the most common debates among backend developers, especially when starting a new project with PostgreSQL and an ORM, is: "Should we create migration scripts from the beginning, or wait until the schema stabilizes?"

The answer is not a simple yes or no. It depends heavily on the stage of your project, the nature of your data, and your future plans for scaling. Let's dive into a real-world perspective on when, why, and how you should approach database migrations.

Why You Should Create Migration Scripts From the Beginning

Even in your very first iteration, setting up migration scripts provides significant benefits:

• Consistency Across Environments: Migration scripts ensure your local, staging, and production databases remain synchronized. No more "it works on my machine" issues.
• Version Control for Your Database: Just as you track changes in code using Git, migrations track changes in your database schema.
• Ease of Onboarding New Developers: New team members can spin up the database with a single command, instead of manually replicating schema from documentation (or worse, guessing).
• Smooth CI/CD Integration: Automated deployments almost always assume that database migrations can be applied without manual intervention.
• Rollback Capability: If a migration introduces a bug, a well-written migration script allows easy rollback without catastrophic downtime.

But What About the Argument: "Let's Wait Until the Schema Stabilizes"?

You might hear:

"Let's not write migration scripts yet. If the schema changes, we might have to reset data."

This argument makes sense under specific circumstances:

• If you're still in pure prototyping phase, where tables change every day.
• If there's no real user data yet, so dropping tables isn't risky.

In such cases, deferring migration scripts is acceptable temporarily. You are trading short-term speed for long-term discipline. However, be warned: the longer you delay, the harder it gets to reintroduce migrations later without chaos.

Once real users interact with your app, you must stop manual schema changes and start using migrations exclusively.

How to Avoid "Resetting" Data When Schema Changes

Another common fear: "Won't migrations wipe out my data if the schema changes?"

The answer: only if you handle migrations incorrectly.

Modern migration tools (like Prisma Migrate, Sequelize, TypeORM, Flyway, Golang-Migrate, Diesel CLI) support non-destructive changes:

• Adding columns (with default values)
• Adding new tables
• Modifying indexes
• Adding constraints

All without deleting your existing data.

Good migrations use ALTER TABLE, not DROP TABLE.

If you need more complicated changes (like splitting a column into two), you can write "data migration" steps inside the migration script itself.

Best Practice Recommendation

When in doubt, default to creating migration scripts early. It's easier to delete an unnecessary migration later than to reverse-engineer schema changes from memory.

Other Important Considerations

• Migration Naming: Use clear, timestamped filenames. Example: 20240418_add_last_login_to_users.sql
• Migration Rollbacks: Always think about how to revert a migration before you write it. Some ORMs support automatic down-migrations; others require manual writing.
• Backup Your Data: Before applying complex migrations, especially in production, ensure you have a fresh backup.
• Data Migration Planning: Schema changes often need data updates. Plan your migrations to avoid downtime or inconsistencies.
• Continuous Delivery: In a mature pipeline, database migrations are applied automatically during deploys. Plan your migration process to be idempotent and safe for reruns.

Finally

Writing migration scripts early is not about bureaucracy; it is about investing in your project's long-term health.

If you're just hacking a weekend project, maybe you can skip them temporarily. But if you're serious about your product — especially anything involving user data — treat your database with the same care you treat your application code.

Your future self (and your team) will thank you.

Support Us