How to Generate a Migration Script in Vertabelo

Database migration scripts make it possible to move from one version to another. Learn how to generate SQL migration scripts quickly and easily with the Vertabelo data modeler.

Databases evolve; as applications mature over time, new features get added and existing features get modified. Consequently, just like the application source code, the database also needs to move from one version to the next. This movement is called database migration. It is one of the most common tasks given to database engineers.

What Is a Database Migration Script?

A migration script is a collection of SQL statements that migrate the database structure from one version to another. A database might undergo several changes over time. Dropping tables, creating new tables, renaming or adding new columns, creating indexes, or changing column data types are examples of changes that require a database migration.

Although you can do database migration manually, it is not efficient, reliable, or scalable. Using a modeling tool’s database migration feature is the best way to go. This article will demonstrate the migration process using a sample physical data model for a food ordering app created in Vertabelo. You can learn how to create physical diagrams in Vertabelo here.

Before Generating a Migration Script

Before we get into the actual generation process, there are a few concepts we need to go over: data model versioning and creating a new model version.

Data Model Versioning

The initial version is this app’s bare-bones data model. As you work on your data model, Vertabelo automatically saves your changes. This acts as Vertabelo’s own version control system. (You can also use Ctrl + S or Cmd + S to save the database model manually.) You can see the autosaved versions by selecting the Show autosaved versions checkbox as shown in the image below:

When the data model’s changes are complete, you can tag the model with a version number, branch name, or something else. You can see that we’ve tagged the two versions of the database as v1.0 and v1.1. Assuming we’ve already deployed v1.0 to our database instance, we need to migrate our database schema to v1.1 using the new model.

First, let’s look at v1.0 of the food ordering app in the image below:

v1.0 of the data model can be found here.

Creating a New Version of the Data Model

After you commit this basic model to the repository, you discuss it with the developers and find out that some changes are required. You implement the following changes in the data modeling tool:

1. Modify the column created_at to include a default value as CURRENT_TIMESTAMP in all of the tables.
2. Add a column updated_at in all the tables with a default value as CURRENT_TIMESTAMP with the condition ON UPDATE CURRENT_TIMESTAMP.
3. Create a BTREE index on the updated_at column in the order. The index should be in DESC order, as most of the queries to this table will fetch the latest records.
4. Modify the phone column in the customer table from VARCHAR(15) to VARCHAR(40), as the former character limit might not be enough to save phone numbers of different formats.

After making the changes, save the data model and tag it with the new version number, v1.1. The updated data model can be seen in the image below:

v1.1 of the data model can be found here.

Generating the Migration Script

Now that you have the two versions of the same data model in Vertabelo, you need to generate a migration script to deploy the differences. There are two ways to do this, as shown in the image below:

1. Right-click on the physical data model and choose Model migration.
2. Click on the dropdown menu in the right corner on any given data model version and choose Generate migration from this version.

Choosing any of the two options will lead to the following screen, where you can select the source and destination data models. Vertabelo will analyze the differences between the two models and generate a SQL migration script. You can save this script in your Vertabelo drive or export it to a local disk as a SQL file.

Tip: It’s a best practice to commit all database changes to your Git repository and add the same tags to the repository using Git tags. Using this repository, you can build a pipeline to deploy your changes on the database instance.

Migration Script Features

Notice that the Show all models checkbox is not selected in the above image. If you select that checkbox, you can see all the other models in your Vertabelo drive. Vertabelo also allows you to generate database migration scripts for two different data models, not just two different versions of the same model.

Check the Include comments for database objects option if you want to include the comments added to the database model to be exported too. Also, check the Quote all SQL identifiers option if you're going to have backticks (`restaurant`), or double quotes ("restaurant") around specific database object names to comply with the formatting requirements of your database.

Once you generate the migration script, you’ll see the following screen. It contains a list of warnings, meant as a final manual check, and a preview of the SQL migration script.

Tip: Always review the migration script manually before checking the new data model into your repository. You can edit the script in the code preview window.

Once saved, this script can be found in the Vertabelo drive, as shown in the image below:

Share Your Thoughts on Database Migrations

Database migration is central to database development. It is also an error-prone activity, as the manual conversion of models to scripts leaves plenty of room for mistakes. Using a data model migration tool helps solve these problems. To learn more about Vertabelo’s database migration features, visit this article.

We’d be happy to learn about your experiences in designing databases. Let us know in the comments section or write to us at contact@vertabelo.com.