Generate a migration script

Last modified: March 28, 2025

Managing database changes is crucial for any database development project. There are two main approaches to database version control:

• State-based version control
• Migration-based version control

Thus, choosing the right approach can significantly impact the efficiency and accuracy of the development and deployment processes.

State-based vs. migration-based version control

The state-based approach involves storing the desired final version of the entire database schema in the code repository and generating all database objects as state-based scripts in separate SQL files. When developers make changes to the database schema, they deploy the database locally and generate scripts to synchronize the current state of the database with the desired one using a third-party compare tool. The scripts can then be uploaded to the version control system to ensure that all team members have access to the latest version of the database schema.

On the other hand, the migration-based approach stores the migration scripts in the repository. The scripts include a collection of DDL statements (CREATE/ALTER/DROP), which can be used to transfer the current database schema and data from one version to another. By executing the migration scripts, developers can make changes to the database schema and data in a controlled and repeatable way. Each migration script represents a specific version of the database. As new migrations are added to the repository, the database can be updated to the latest version by running all applicable scripts in sequence.

While both approaches have their benefits and drawbacks, dbForge Source Control favors the state-based approach for database development and deployment. This approach allows developers to easily compare and synchronize the database schema with the desired version, ensuring that all changes are stored accurately and efficiently.

To generate a migration script using dbForge Schema Compare for MySQL, developers can use the Schema Synchronization wizard as follows:

• Save database data, which includes the Data Definition Language (DDL) statements for each object and their dependencies
• Store each database object as a separate SQL file grouped into folders by object type

After the script has been generated, developers can commit it to the version control repository using any third-party version control client.

For more information about the benefits and drawbacks of both the state-based and migration-based approaches to database version control, see Database Version Control — State-based or Migration-based.

Cases to use migration scripts

When comparing the actual database with the version in source control, sometimes, it is impossible to distinguish if the schema changes require any data changes. Therefore, deploying data changes from source control can result in deployment failure or data loss.

Migration scripts can be of great assistance to ensure correct database deployment. The required steps are to create your deployment script and execute it during automated deployment from source control.

Here are some examples of using migration scripts to deploy the database correctly:

Examples	Description	Solution
Adding a NOT NULL constraint to a column	When you insert a NOT NULL constraint into a column holding NULL entries without providing a default value, the deployment process may fail because the new constraint conflicts with the existing NULL values in the column.	Create a migration script to update all the NULL entries of the column with a NOT NULL value and execute it before adding a NOT NULL constraint.
Splitting a column	If you split a column into two columns and drop the original column along with its data, the deployment will lead to data loss of the dropped column.	Create a migration script to copy data to the new columns and execute it before dropping the original column.
Merging columns	When you merge a column, a new column is created, while the original column is dropped. In this case, the deployment will also lead to data loss of the dropped column.	Create a migration script to transfer data to the new columns and execute it before dropping the original column.
Splitting a table	If you split a table into two tables and drop the original table along with its data, the deployment will lead to data loss of the dropped table.	Create a migration script to transfer data to the new table and execute it before dropping the original table.
Merging tables	When you merge a table, a new table is created, while the original table is dropped. In this case, the deployment will lead to data loss of the dropped table.	Create a migration script to copy data to the new tables and execute it before dropping the original table.
Changing the data type or size of a column	When you change the column data type or size, the data will be truncated during the deployment.	Create a migration script to modify rows in such a manner to avoid truncation.
Renaming a table	When you rename a SQL table, it is dropped and then recreated. In this case, the deployment will lead to data loss of the renamed table.	Create a migration script to rename the table using the sp_rename stored procedure.

Note

The comparison engine of the tools doesn’t handle all cases automatically, which is why the cases mentioned above must be resolved manually.