Apply Hasura migrations on current database pipeline

[adventurini]

I have three databases - dev, staging, and prod. There are 50+ tables and 10+ functions on these databases. They have a diff between them.

I ran hasura migrate create to get my up migrations.

Then I run, hasura migrate apply --endpoint STAGING_ENDPOINT --admin-secret STAGING_SECRET

I get a really long error that says the tables already exist. There is really nothing in the docs that explain this situation well. I have literally wiped my metadata, migrations, and entire dev database trying to get it to work. Luckily, I had created backups just in case.

I have also tried to start on a completely new Database from Heroku. I created the same steps, but the new error says that none of the tables exist.

What are the steps for bringing your own databases to apply migrations / metadata to all of them, so that the database pipeline works properly?

[adventurini]

Also, if we try to migrate to a fresh db and Hasura instance... We get this error.

bad pointer in frame github.com/konsorten/go-windows-terminal-sequences.EnableVirtualTerminalProcessing at 0xc00092f640: 0x1a0 fatal error: invalid pointer found on stack

[nickdurbin]

Very interested in a solution to this issue.

[m-rgba]

This may not be the most proper answer, but here are some of the steps I use for my migrations.

hasura migrate create init --from-server - uses pg_dump as its base to generate those initial .sql files. It works, but I usually need to cleanup the initial migration before I run apply.

2 things I've seen:

• To account for the tables already being present I'd add if exists statements to upsert the tables created.
• Functions and stored proc dependancies - pg_dump will just look to dump those and not really care about the order-of-operations, I've had to make some tweaks to make sure everything will load up in the right order.

After the initial creation I usually edit the migrations much less frequently (usually just to ensure that migrations and concise and the down.sql looks good). In the end though, migrations are just a system for managing applying sql (up), rolling back (down), and checking status. They're mainly SQL scripts, not too much magic - definitley safe to edit.

[leoalves]

@mm-io suggestions are pretty much the way to go about it.

I saw in the Discord channel that @adventurini was struggling with this issue for a few days and I took a look at his servers.

There were basically two issues:

• Just like @mm-io pointed out, the --from-server initial migration had CREATE FUNCTION commands before the CREATE TABLE. I had to manually edit the up.sql file and move all CREATE FUNCTION commands to the end of it.
• He had 3 functions that were giving errors when creating because the output of the function was different than the table it was referencing. My guess is that he created the function and after changed the structure of the referenced table making the function invalid.

I will outline here the steps that I took to migrate the state of his dev environment to the staging one.

1. To reset the staging database to a blank state. Run the following commands in the PostgreSQL database. Do not use the console because it won't have the permissions to do it. It has to be run directly in the database. He only had the public schema if there are more those commands also needs to be added. This will reset the Hasura state to a blank state. Make sure you are running in the correct environment, otherwise you will loose all your data.

   Drop schema public cascade;
   Drop schema hdb_views cascade;
   Drop schema hdb_catalog cascade;
   Create schema public;
   

2. Export the migrations from the dev environment using the Hasura CLI (make sure you use the same CLI version as the graphql-engine. If there are other schemas than the public it also needs to be added to the command.
   hasura migrate create "init" --from-server --admin-secret "<admin-secret>" --endpoint "<endpoint>"

3. Export the Hasura metadata from the dev environment using the Hasura CLI
   hasura metadata export --admin-secret "<admin-secret>" --endpoint "<endpoint>"

4. Apply the migrations in the staging environment using the Hasura CLI
   hasura migrate apply --admin-secret "<admin-secret>" --endpoint "<endpoint>"

If this command return errors you need to manually edit the up.sql file. In his case it was complaining that the table in the CREATE FUNCION command didn't exist. I moved all CREATE FUNCTION commands to the end of the file.
After this edit I ran the command again and the error was different. It was complaining that the return of the function was invalid. I removed that function from the up.sql file and ran the command again. Had to do that 3 times (3 different functions had the same issue). After deleting those 3 functions I was able to apply the migrations in the staging environment.

5. Apply the metadata in the staging environment using the Hasura CLI
   hasura metadata apply --admin-secret "<admin-secret>" --endpoint "<endpoint>"

Now the staging environment has the same state of the dev without those 3 functions. They will have to be looked at individually to pinpoint the issue.

Those are the steps to apply the migrations and metadata from one Hasura server into another. Just note that the errors faced in the migrate apply command were specific to @adventurini issue. But they were just SQL errors and following the erros and editing the up.sql file to fix them should solve the issue.

I hope this is clear and can help someone else that has similar issues.