# 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](https://www.liquibase.com/), [Flyway](https://github.com/flyway/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. {% embed url="" %} Creating and Editing Migrations {% endembed %} {% embed url="" %} Applying Migrations in Environments {% endembed %} ## 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.
{% hint style="info" %} The *number* in the migration name determines the order in which migrations will be executed against your data source. When a new migration is created, we generate the number in the name for you based on the last migration number. The previous migration was 10\_migration, so the next recommended migration name is 20\_migration. You can edit this number, but make sure it's sequentially after the number used in the previous migration, if there is one. The part of the name after the "number\_" can be anything you prefer to help you understand this migration. It defaults to the text "migration" {% endhint %} 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. {% code overflow="wrap" %} ``` Added the required column `summary` to the `books` table without a default value. This is not possible if the table is not empty. ``` {% endcode %} 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. {% hint style="info" %} The "Regenerate migration" option is only available for new migrations being created. It is not available for already committed migrations. {% endhint %}
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](#creating-and-editing-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. {% hint style="info" %} In the Commit History, you might also see an "Initial empty commit" which is created when the project is first initialized. This is an empty commit and does not have any schema or migrations in it. {% endhint %} Let's say we already have an environment (if not, please create one -- see the [Environments](https://docs.neurelo.com/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](https://tableplus.com/), [Liquibase](https://www.liquibase.com/), [Flyway](https://www.red-gate.com/products/flyway/community/), 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.