Schema Migrations with Hasura Console

The first part of this series is available “Evolutionary Database Design, GraphQL, APIs, and Database Schema Migrations”. The second post “Automated Schema Migrations” is available, and in this third post I’ll conclude with the steps to expand our schema migrations using the Hasura Console, a graphical interface in which to automate the build out of migrations in our database, and by proxy our Hasura API Server.

A prerequisite to follow along in this post is that you’ve covered the material in the previous two posts, and have the base tables available after executing the first migration. Now we’ll expand on those tables!

The original schema we’re working toward looks like this. At this time though, after our first migration has been implemented, we only have the tables themselves and not the relationships as drawn between those tables. In this post I’ll show you how to add those using the console.

First, if you don’t have it open already, open the folder structure back up in your preferred editor. I’ll be using Visual Studio Code for the following examples. The folder structure and existing migration, and if you open up the config.yaml file, it should look something like this.

From here, to build the next migration open up the locally instantiated Hasura console by executing the following command.

hasura console

If you’ve got the publicly accessible console open and this newly launched console, they’re indiscernible. However there is one quick way to determine which console is open by noting the URI that is open. The locally instantiated instance, launched by the hasura console command will have a URI of http://localhost:9695/ and the publicly accessible console is a URI that looks something like this https://zee-newest-project.hasura.app/console/. For creating migrations the locally instantiated instance of the console is what we’ll need to use, since it will record all of the changes and mutations that we make.

With the locally instantiated console open, click on the data tab.

Within this tab, any of the changes we make will get put into versioned migrations. Within the data tab, click on the create button to create a new schema.

Enter the name of the new schema and then click the create button again to create this new schema. In the upper left the new schema will be selected in the Schema drop down, which will confirm its creation and that any changes at this point will be made to that schema.

Now navigate back to the editor, to review the migration that has now been added to the project folder. In the folder, again we have the standard up.sql and down.sql files, except this time the up and down steps have been populated with the generated SQL code necessary to execute those steps.

Next up, add a new table via the console. In this example, I’ve added a table with some columns, just to provide an example.

When I click add table and then navigate back to my editor to review the migration, I’ll find the following now in my project structure.

Notice the new migration, up.sql and down.sql files as expected, and now the create table statement in the up.sql file for creating the table and columns in the table.

At this point we have now stepped through some pivotal examples of how the graphical interface of the console can be used to create new migrations. As you step through and create relationships, new tables, and also remove or add changes to those tables, migrations will be created for each of the changes.

Many companies, and practices, often work toward enforcing an additive-only approach to schema migration changes in a database. That is, only additions can be made to tables and data is not mutated in any way during schema migrations. Relationships tend to be open for changes however. The reasoning covers a range of scenarios from ensuring the integrity of data in tables for audits, to the important matter of reducing conflicts in table structure when making changes. Whenever using migrations, this is an important practice to put into place, or to determine you want to put into place. If changes routinely mutate columns, column names, or otherwise a team will find themselves regularly running into conflicts moving back and forth between versions. Even worse, if you find yourself with a wrathful auditor because data has been deleted, the legal ramifications can grow rapidly. With that said, I’d argue, it’s a great idea to enforce additive-only changes for schema migrations and for your data in general.

This wraps up the series of articles around evolutionary database design and database schema migrations. From the evolutionary ideas presented in the first article, to the manual hand coded SQL of the second article, to this article showing how to use the graphical interface the console provides to create migrations we’ve covered the idea, the practice, and the implementation of database schema migrations using Hasura. It’s a great way to streamline and ensure integrity in your development practices, specifically around integration and cooperation between the database and application tier of development efforts. I hope this series of articles has been of use, and if you’ve got any questions, comments, or suggestions feel free to reach out to us at Hasura and let’s have a conversation!