Schema Migrations

Introduction

As we make changes to the schema for our PostgreSQL and MySQL databases, Neurelo also helps us auto-generate corresponding schema migrations and review/edit them, add migrations manually, and manage migrations in a version-controlled way. These migrations can be applied to our databases directly from within Neurelo, or we can download these migration files and apply them using schema migration systems we may be using like Liquibase, Flyway, or others.

In this guide, we will look at how to create, edit, and apply migrations as we work on our schema.

Video Guide

If you prefer a more visual experience, please check out the video version of this guide.

Creating and Editing Migrations

For the purposes of this guide, we will start with an empty project and build a schema and the migrations for that schema.

Let’s start with a “New Empty Schema” from the Quick Start guide as we start a project, or by going to "Definitions" in the left sidebar for our project. We will see a dialog box like the following -

We will create a simple schema with a books object, with id and title properties. This is how the schema will look in the Schema Builder view.

Once we have our schema changes done, the corresponding migration file for this schema can be generated in a couple of ways -

1. The migration file will be auto-generated if we commit the schema without manually creating an associated migration file.

2. Creating a migration file by going to the "Migrations" tab under Definitions

Both of these will be detailed in this guide. Let's start with the case where the migration file is auto-generated on commit

Auto-Generate on Commit

The next step after we have made our schema changes is to commit the schema into the version control system. After clicking on the “Commit” button at the top right under the Definitions view, the “Commit changes” dialog box will be opened, and Neurelo will detect the schema changes and generate a migration automatically, if a migration has not been manually created by the user.

Notice the information bar in blue in this modal is indicating that a migration (10_migration, in this case) has been auto-generated to correspond with the schema changes we are committing.

Creating a Migration

Let's make some additional schema changes to review how to add a migration manually. Let’s add a summary property to our books object. After adding the summary property, here is what the schema diff looks like.

Now, to generate the migration for this schema change, navigate to the “Migrations" tab and click “+” to add a new Migration. If this is the first migration, we can also use the "New" button to add a new Migration. In our case, we already have a "10_migration" which was auto-generated in the earlier step.

This will bring up the “New Migration” dialog box. Here we can name the migration and enable the "Auto-Generate" option to generate the migration from the schema changes we just made. If we do not select the auto-generate option, then an empty migration file will be created for us to edit.

In our case, let’s name the migration as “20_add_summary” and click on “Create”. Since we selected the "Auto-Generate" option, the migration is automatically generated based on the schema edits.

We can review the generated migration and edit it to make any adjustments. Once done, we can "Commit" these changes, but notice that there is a warning in this generated migration.

Let’s solve that in the next section.

Editing a Migration

Here is the warning from the above step.

Added the required column `summary` to the `books` table without a default value. This is not possible if the table is not empty.

Since we have added a non-nullable property summary without any default, it will cause issues during the migration because if there are existing rows, the summary column may not have any data and it won't be successfully migrated.

There are two ways to solve this in the schema -

1. We can make the summary nullable

2. We can add a default string value

Let’s go with Option 1 to make the property nullable. We will go back to the "Schema" tab and mark the summary as nullable. Here is how the property and the schema diff look after this change.

Then, we can navigate back to the “Migrations Tab” and select the migration for this commit which is 20_add_summary. Here we can click on the "Regenerate migration" icon to auto-generate the migration again with the latest schema changes. We could also manually edit the migration as well, if we don't want to auto-generate this migration again.

Here is the diff of the migration that is now generated. In the new migration, the NOT NULL instruction is removed; therefore, the warning is gone. Click on “Continue” to use this.

We can now commit the schema with it's associated migration and use it in our Environments.

Using Migrations in Environments

In the Create and Edit Migrations section, we created two commits, each with schema changes and the corresponding migrations. Let’s look at how to use them in the environments.

Let's say we already have an environment (if not, please create one -- see the Environments section for more information). Let's navigate to the environment we want to work on by selecting "Environments" from the left sidebar for our project.

Once the environment is loaded, let's make sure the environment is using the latest commit of the definitions. Then navigate to the “Migrations Tab", and we can see that there are two migrations available there for that commit.

Apply Migrations using Neurelo

We can apply these Migrations to the database attached to this environment by clicking the “Apply 2 Migrations” button.

This will open the Apply Migrations confirmation dialog.

Once confirmed, the migrations will start to be applied to the database

After the migration process completes successfully, the “Your data source is up to date with your current schema" message will be displayed.

And this is how we can use Neurelo to directly migrate our data source. Now, let’s see how to migrate the data source manually.

Manually Applying the Migrations

For this let's make another change to the schema first. Let's navigate back to "Definitions" and add an authors object to the schema with id and name as properties, and commit it.

Notice that the migration was autogenerated in this case.

Now, let's go back to the environment we were using and update the commit using the “Commit Selector” to this latest commit.

Once we update the commit, let's navigate to the "Migrations" tab. You will notice that the sidebar now reflects the new 30_migration. To apply it manually, we can copy the migration contents from the editor or download the migrations using the download button in the sidebar.

This will download all the migration files as .sql in a zip file. The downloaded files will look something like this

Now we can use these migrations and apply them manually to the database using tools like TablePlus, Liquibase, Flyway, and others.

Once we execute 30_migration.sql, we can mark the migration as applied in Neurelo to keep the migrations in sync. To do that, we can click on the “Mark as Applied” option.

This will open the “Mark as Applied” dialog box where we can select which migrations we want to mark as applied outside of Neurelo.

In our case, we will select the 30_migration and click on “Mark 1”

After that, we can see that the green checkbox against the 30_migration and the “data source is up to date with the schema" message as before.