How to avoid messing up squash migration in Hasura
Tags: graphql, database, hasura, migrations
Published: 2023-01-05 20:54:27

[Versão em Português Brasileiro](https://dev.to/ferkarchiloff/reduzindo-as-chances-de-errar-um-squash-de-migracoes-no-hasura-3c23)

#Summary
1. [Technology versions](#technology-versions)
2. [Background knowledge needed](#background-knowledge-needed)
3. [Why I decided to write it](#why-i-decided-to-write-it)
4. [Too long; didn't read](#too-long-didnt-read)
5. [Database example](#database-example)
6. [Squash guide](#squash-guide)
7. [Conclusion](#conclusion)

##Technology versions

- Hasura v2.0.9
- Hasura CLI v2.0.9
- PostgreSQL 12

##Background knowledge needed

- Understand SQL and it's commands
- Understand how migrations work
- How [Hasura GraphQL Engine](https://hasura.io/docs/latest/graphql/core/getting-started/index.html#getting-started) works
- How [Git](https://git-scm.com/) works

##Why I decided to write it

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.

##Too long; didn't read

1. Make your changes using migrations.
2. Using Git or similar, make a commit to save your migrations.
3. Make sure that the `up.sql` and `down.sql` files execute as desired using `hasura migrate apply` and `hasura migrate apply --down <N>`. Do manual fixes (mistakes can happen) using the `SQL` tab without using migrations.
4. Commit your complete migrations to the version control system.
5. 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).
6. 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).
7. 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.
8. Commit your changes to the version control system.
9. (Optional) Do optimizations if needed, for example: remove an `ALTER TABLE` if there's a `DROP TABLE` (without losing data of course).

##Database example

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:

```sql
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](https://hasura.io/docs/latest/graphql/core/migrations/migrations-setup.html) of _Migrations & Metadata (CI/CD)_ in the documentation for the _Hasura GraphQL Engine_.

Let's say that this table was filled with some student data:
![Table with three columns called: "id", "name", "score" and some filled data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g1goo3fgozuc3tasrzz1.png)

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 `Numeric` column.

![Modify tab selected with option to add new column appearing with filled data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6iop7eaqomy9s2yt4u2o.png)
 
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.

![Databases being displayed in the top tab and below it the SQL tab](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7euulbm2lsd9aq5v9gm2.png)

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:

```sql
UPDATE students
SET score_decimal=CAST(score AS DECIMAL)
WHERE students.id = students.id;
```

![SQL tab with query filled in the text field and migration function enabled](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7izhheuev7kw4gpd6ugo.png)

**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 `rename_columns_and_remove_old_migration`.

```sql
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";
```

![SQL tab with query filled in the text field and migration function enabled](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m85kjzac5sndhxbljb4k.png)

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 guide

For the `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.

1. Save the made migrations in the repository
2. Ensure that the migrations can be made and undone without errors
3. Save the complete migrations
4. Squash the migrations
5. Mark the migration as executed
6. Save the changes
7. (Optional) Modify the compressed migration as desired (and save the changes)

###1. Save the made migrations in the repository

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.

###2. Ensure that the migrations can be made and undone without errors

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:

```sql
-- 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 `up.sql`. 

```sql
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.

```sql
ALTER TABLE "public"."students" DROP COLUMN "score_decimal";
```

With that, we can use the `--up` and `--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: 
```bash
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.**

###3. Save the complete migrations

Since you have tested your migrations and they are working, save them again in a commit, now with the complete migrations.

###4. Squash the 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:
```bash
hasura migrate squash --from <num_migracao> --name <name>
```
It compress the migration files for us. In this example, I will do like this: 
```bash
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.

###5. Mark the migration as executed

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:
```bash
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.

###6. Save the changes

Once this is done, save the changes again in the repository's version control to avoid losing your changes.

###7. (Optional) Modify the compressed migration as desired (and save the 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.

##Conclusion

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.