- Technology versions
- Background knowledge needed
- Why I decided to write it
- Too long; didn't read
- Database example
- Squash guide
- Hasura v2.0.9
- Hasura CLI v2.0.9
- PostgreSQL 12
- Understand SQL and it's commands
- Understand how migrations work
- How Hasura GraphQL Engine works
- How Git works
During the development of one project at the e-commerce company I'd worked for, we had a task that would require to change the database structure, many tables and columns had to be changed to complete the task.
As Hasura GraphQL Engine offers a web page to make the structural changes at the database, I made the changes using this interface and executed the manual queries that were necessary. All of these modifications created migrations in the repository, as expected for a project with migrations enabled in Hasura.
After the modifications, it was noticeable the amount of folders with modifications that were being saved in the migration format. I decided to use the command to compress all of them into a single file, which would be more readable. That's when my mistake happened: without making basic validations beforehand, I executed the command and deleted the previous migrations. That's how I spent more than a day trying to fix a mistake that could have been avoided with a simple checklist before using the command. How can we avoid this? I will explain in the next topics.
- Make your changes using migrations.
- Using Git or similar, make a commit to save your migrations.
- Make sure that the
down.sqlfiles execute as desired using
hasura migrate applyand
hasura migrate apply --down <N>. Do manual fixes (mistakes can happen) using the
SQLtab without using migrations.
- Commit your complete migrations to the version control system.
- Use the command
hasura migrate squash --from <num_migration> --name <name>to execute the squash, delete the old migrations (you already have them at the version control).
- Mark the created migration as fulfilled at the database using
hasura migrate apply --skip-execution(if the removed migrations weren't applied, don't use the flag).
- Use again the command
hasura migrate apply --down <N>to remove the migrations, and then use it without the flag to make sure that they're working.
- Commit your changes to the version control system.
- (Optional) Do optimizations if needed, for example: remove an
ALTER TABLEif there's a
DROP TABLE(without losing data of course).
To demonstrate how to do a squash correctly, I will use a simple database with a single table, but in a real case you can have various operations among N tables, which may lead to a larger and complex squash.
Our database contains the
students table defined through this structure:
CREATE TABLE "public"."students" ( "id" serial NOT NULL, "name" Text NOT NULL, "score" text NOT NULL, PRIMARY KEY ("id") );
It was created after we had already connected to our database created in PostgreSQL, disabled the console, and started migrations in the project. You can see more details on how to enable migrations in this section of Migrations & Metadata (CI/CD) in the documentation for the Hasura GraphQL Engine.
Somewhere along the way, we realize that the data in the
score column, which cannot be text because we would like to do some arithmetic with them. However, we cannot change this data if it is being used in production! (Assume that you have thousands of important data and cannot lose them)
What to do then? In the development environment, we will create a new column to accommodate this data. We will name it
score_decimal because it represents the score as a decimal number allowing arithmetic operations. Go to the
students table's Modify tab and click Add new column. We will define it as a
Once the new column is created, we can migrate the data from the
score column to
score_decimal. To do this, we will use the
SQL section located just below the tables in our database.
In this tab you can run SQL code directly against the selected database, marking these queries as migrations or not. Depending on the code, Hasura may be smart enough to identify if it is a structural or data change and mark it as a migration automatically, so always pay attention before running the query to see if the "This is a migration" box is enabled.
Putting the query that converts the scores in the text field, we can run it without adding it as a migration in order to verify that the query is being executed as described. After checking, we can enable the migration box and write a name for it, I used a descriptive name
cast_students_score_to_decimal_migration. This is the query made to convert the data:
UPDATE students SET score_decimal=CAST(score AS DECIMAL) WHERE students.id = students.id;
NOTE: always be careful when making structural changes that change field categories, so do it in steps and update the view layer (front-end), if used, to change the new type without breaking the application for clients.
After running the update of the data, we can rename the columns and delete the old one. The name used for the migration was
ALTER TABLE students RENAME COLUMN "score" TO "score_text"; ALTER TABLE students RENAME COLUMN "score_decimal" TO "score"; ALTER TABLE students DROP COLUMN "score_text";
With that, we finished our modification to run our arithmetic operations, but we ended up with some separate migrations that can be joined through a
squash operation, as they can be executed all together.
squash process of the migrations to occur "correctly" (there can always be an unexpected error in the middle of the process), we need to ensure that some steps are followed. I will list some of them to avoid errors in this process.
- Save the made migrations in the repository
- Ensure that the migrations can be made and undone without errors
- Save the complete migrations
- Squash the migrations
- Mark the migration as executed
- Save the changes
- (Optional) Modify the compressed migration as desired (and save the changes)
To keep track of our migrations and not lose progress, we save the changes in a commit, we can then proceed to change them without the risk of losing what we have done, this was one of the mistakes I made earlier, not saving in the version control.
With the migrations saved in the repository, we can start modifying those that have incomplete
down.sql files. Many of the changes we make to the database do not always reflect in the files due to being complex changes, such as renaming columns and deleting a column in the same migration. For example, in the last migration, the
down.sql file was filled out as follows:
-- Could not auto-generate a down migration. -- Please write an appropriate down migration for the SQL below: -- ALTER TABLE students RENAME COLUMN "score" TO "score_text"; -- ALTER TABLE students RENAME COLUMN "score_decimal" TO "score"; -- ALTER TABLE students DROP COLUMN "score_text";
For the migration to work back, that is, remove the changes, we will reverse the commands made in
ALTER TABLE students ADD COLUMN "score_text" text; ALTER TABLE students RENAME COLUMN "score" TO "score_decimal"; ALTER TABLE students RENAME COLUMN "score_text" TO "score";
We will do the same with the migration that creates the new column and reverse the command.
ALTER TABLE "public"."students" DROP COLUMN "score_decimal";
With that, we can use the
--down flags of
hasura migrate apply. The command changes the database according to the
.sql defined in the migrations. If you relate the files to their respective commands, it will make more sense, the command that uses
--down will remove the applied migration changes, while
--up will apply them (
--up is applied by default if not defined). To remove only some, use:
hasura migrate apply --down <N>
In the command, N is the number of migrations that will be removed. Use
hasura migrate apply --help to see more details.
Note: If you ran a migration and it failed, if you have more than one SQL command in the same file, try to fix it through the
SQL tab (without creating a new one), correct it and try again.
Since you have tested your migrations and they are working, save them again in a commit, now with the complete migrations.
With all migrations completed and working, we can squash them, that is, compress them into a single migration. To do this, we will run the command:
hasura migrate squash --from <num_migracao> --name <name>
It compress the migration files for us. In this example, I will do like this:
hasura migrate squash --from 1638852925395 --name "changing_column_type"
This command will merge all migrations starting from the prefix
1638852925395 into a single one, named "changing_column_type". I recommend you delete the previous migrations because they are already saved in the repository.
Once we have created our new compressed migration as a result of the operation, if the previous migrations were not applied to the database, we will run the command to mark it as if the migration we just generated had already been applied:
hasura migrate apply --skip-execution
Remember that this command should only be run if we actually applied the changes of the other migrations to the database, otherwise, run the command without the
--skip-execution flag to apply it normally.
Once this is done, save the changes again in the repository's version control to avoid losing your changes.
If your migration has many commands, evaluate the file and see if, depending on the changes made, you can simplify the executed SQL code. For example, you create a table, and then in another migration you add a column, if you have not added any data in the middle of the way, or it has not gone to production yet, directly add the column when creating the table, which will simplify the code.
I hope that these instructions serve as a guide so that you don't make the same mistakes I did during the project I was developing. I tried to put as many details as possible in the step-by-step instructions and the reasons behind them.
These instructions can be replicated in other projects, even if they don't follow the same technology, but if their concepts are similar, it's possible. If you don't apply them immediately, at least I hope you've learned something from what I demonstrated in this article.
Any positive criticism that can enhance this article is welcome! Share with other people who need this content!
Thank you for your attention.