Back to articles list
The long-awaited generation of SQL migration scripts is now a part of Vertabelo! You can use these scripts to easily move from one database model version to another. Read this article to learn more.
What are SQL Migration Scripts?
An SQL migration script is a script that alters an existing database structure: it can add or drop a table, rename columns, or change column types. A migration script can change a given database from one schema version to another – hence its name. It migrates database schemas.
Vertabelo offers both simple migration scripts that add a column or two and extremely complex ones that split entire tables or introduce other changes that can affect the way data is stored.
Of course, you can write scripts yourself whenever your database schema changes, but it’s a lot of work. And there’s always a risk that you will forget something. This is where automatic migration scripts come in handy.
Using Vertabelo Migration Scripts: An Example Case
This simple example will show you how to tackle migration scripts in Vertabelo. Let’s say you want to keep track of singers and the albums they have created. Initially, we have the following very simple model for a music database:
The model looks okay to us, so we generate the SQL script using the button in Vertabelo’s main toolbar and start working with our database.
At some point, however, we decide that there are a number of changes we want to introduce into our model. Check out the new version:
Clearly, things have changed! Some columns have appeared (e.g. the published column in the album table); others have disappeared. For example, you’ll notice that the genre column in the album table has been replaced with a many-to-many relationship using two new tables, album_genre and genre. Still other things have been updated: the title column in the album table can now be up to 128 characters long.
So at this point we would like to generate the proper migration script to update our database. Let’s see how to do it.
Generating Migration Scripts
From the Edit mode in Vertabelo, click on the model name on the left (in this case, “migration_example”) and choose Model details:
In the Model details screen, click on GENERATE MIGRATION SQL, located on the right:
A new screen will appear. Our current model is now selected as the target model, so we must specify the source model too. On the left, click the model you’re interested in and then choose the specific version of the model from the list on the right. The source and target models may be just different versions of the same model in Vertabelo, or they may be completely separate.
Once the source model is ready, you will see the Target model section, which describes the target model of the migration script. Your database will be updated from the source model listed above to the target model listed below.
If everything’s correct, click on the GENERATE button:
Once the migration script is ready, you will see the SQL migration result screen:
You can click the DOWNLOAD button on the top to download your SQL migration script. Below that, you will find a clickable warning list that will show you the exact places in the code where warnings have been triggered. On the right, you can get a preview of your entire SQL code.
Be sure to verify the migration script and adjust it to your needs. For example, if a column name has changed, Vertabelo drops it and creates a new column. This may not be what you want in a production database!
Once you’re ready, just run the script on your database and its structure will be updated. The next time you want to introduce changes, you can generate another migration script.
What do you think about this new feature in Vertabelo? Please let us know how we can further improve migration scripts to meet your expectations.
