Reset Hasura migrations and squash files

Using GraphQL for creating REST APIs is nowadays popular and there are different tools you can use. One of them is Hasura which is an open-source engine that gives you realtime GraphQL APIs on new or existing Postgres databases. Hasura is quite easy to work with but if your GraphQL schemas change a lot it creates plentiful of migration files. This has some unwanted consequences (for example slowing down the hasura migrate apply or even blocking it). Here’s some notes how to reset the state and create new migrations from the state that is on the server.

Note: From Hasura 1.0.0 onwards squashing is easier with hasura migrate squash command. It’s still in preview. But before Hasura 1.0.0 version you have to squash migrations manually and this blog post explains how. The results are the same: squashing multiple migrations into a single one.

Hasura documentation provides a good guide how to squash migrations but in practice there are couple of other things you may need to address. So let’s combine the steps Hasura gives and some extra steps.

Reset Hasura migrations

First make a backup branch:

1. $ git checkout master
2. Create a backup branch:
   $ git checkout -b backup/migrations-before-resetting-20XX-XX-XX
3. Update the backup branch to origin:
   $ git push origin backup/migrations-before-resetting-20XX-XX-XX

We are assuming you’ve local Hasura running on Docker with something like the following docker-compose.yml

version: "3.6"
services:
  postgres:
    image: postgres:11-alpine
    restart: always
    ports:
      - "5432:5432"
    volumes:
      - db_data:/var/lib/postgresql/data
    command: postgres -c max_locks_per_transaction=2000
  graphql-engine:
    image: hasura/graphql-engine:v1.0.0-beta.6
    ports:
      - "8080:8080"
    depends_on:
      - "postgres"
    restart: always
    environment:
      HASURA_GRAPHQL_DATABASE_URL: postgres://postgres:@postgres:5432/postgres
      HASURA_GRAPHQL_ENABLE_CONSOLE: "true" # set to "false" to disable console
      HASURA_GRAPHQL_ADMIN_SECRET: changeme
      HASURA_GRAPHQL_ENABLED_LOG_TYPES: startup, http-log, webhook-log, websocket-log, query-log
volumes:
  db_data:

Create local instance of Hasura with up to date migrations:

1. $ docker-compose down -v
2. $ docker-compose up
3. $ hasura migrate apply --endpoint=http://localhost:8080 --admin-secret=changeme

Reset migrations to master:

1. git checkout master
2. git checkout -b reset-hasura-migrations
3. rm -rf migrations/*

Reset the migration history on server. On hasura SQL console, http://localhost:8080/console:

TRUNCATE hdb_catalog.schema_migrations;

Setup fresh migrations by taking the schema and metadata from the server. By default init only takes public schema if others not mentioned with the –schema “your schema” parameter. Note down the version for later use.

1. Create migration file:
   $ hasura migrate create "init" --from-server
2. Mark the migration as applied on this server:
   $ hasura migrate apply --version "" --skip-execution
3. Verify status of migrations, should show only one migration with Present status:
   $ hasura migrate status
4. You have brand new migrations now!

Resetting migrations on other environments

1. Checkout the reset branch on local machine:
   $ git checkout -b reset-hasura-migrations
2. Reset the migration history on remote server. On Hasura SQL console:
   TRUNCATE hdb_catalog.schema_migrations;
3. Apply migration status to remote server:
   $ hasura migrate apply --version "<version>" --skip-execution

Local environment Hasura status

For other developers please refer these instructions in order to get the backend into same state.

Option 1: Keep old data

1. Checkout the backup branch on local machine:
   $ git checkout backup/migrations-before-resetting-20XX-XX-XX
2. Reset the migration history on local server. On Hasura SQL console:
   TRUNCATE hdb_catalog.schema_migrations;
3. Apply migration status to local server:
   $ hasura migrate apply --version "<version>" --skip-execution

Option 2: Remove all and start from beginning

1. Clean up the old docker volumes:
   $ docker-compose down -v
2. Start up services:
   $ docker-compose up
3. Checkout master:
   $ git checkout master
4. Apply migrations:
   $ hasura migrate apply --endpoint=http://localhost:8080 --admin-secret=changeme

Possible extra steps

Now your Hasura migrations and database tables are in one migration init file but sometimes things don’t work out when applying it to empty database. We are using Hasura audit-trigger and had to reorder the SQL clauses done by the migrate init and add some missing parts.

1. Move schema creations after audit clauses
2. Move audit.audit_table(target_table regclass) to last audit clause and copy it from audit.sql
3. Add pg_trgm extension as done previously (fixes “operator does not exist: text <%!t(MISSING)ext” in public.search_customers_by_name)
4. Drop session constraints / index before creating new
5. Create session table only if not exists