How to setup Hasura migrations between DEV and PROD without loosing data

Question

I'm using hasura migration guide to sync two servers - DEV and PROD. Before we manually transferred the changes (as in 'using UI to copy all the changes'), so now databases are 90% similar.

We decided to set up proper migrations, but based on my tests doing an initial sync requires a 'clean slate'.

Example of the problem:

We have users table on both DEV and PROD. On DEV there is additional field age. We do

1 hasura migrate create --init (on dev)

2 hasura migrate apply --endpoint PRODUCTION

We get error relation \"users\" already exists.

The question is - how can we sync the DBs without cleaning PROD first?

Answer 1

You're currently receiving that issue since running migrate apply is trying to execute on tables which already exist.

If you use the --skip-execution flag you can mark all of your relevant migrations as completed in the PRODUCTION environment and the migrate apply as usual to apply the new migration.

More information is available in the CLI documentation:
https://hasura.io/docs/latest/graphql/core/hasura-cli/hasura_migrate_apply.html

---

After re-reading the question to clarify - creating the initial migration using create --init will create a snapshot of your database as it is now (won't diff between STAGING and PRODUCTION).

To migrate this between STAGING and PRODUCTION you'd need to manually change the initial migration created to match staging and prod, and then manually create an incremental migration to bring PRODUCTION in line with STAGING.

After this, if you're working with Hasura Console through the CLI (using https://hasura.io/docs/latest/graphql/core/hasura-cli/hasura_console.html) -- it will automatically create future incremental migrations for you in the directory.

---

As an aside - you can also create resilient migrations manually as well using IF NOT EXISTS (these aren't automatically created by Hasura, but you can edit their SQL file migrations).

For example:

ALTER TABLE users
ADD COLUMN IF NOT EXISTS age INT

---

Edit 2: One other tool which I came upon which may be helpful is Migra (for Postgres, outside of Hasura). It can help with diff-ing your dev and production databases to help create the initial migration state: https://github.com/djrobstep/migra

Answer 2

It's a bit buried, but the section on migrations covers this scenario (you haven't been tracking/creating migrations and now need to initialize them for the first time):

https://hasura.io/docs/latest/graphql/core/migrations/migrations-setup.html#step-3-initialize-the-migrations-and-metadata-as-per-your-current-state

Hope this helps =)