search

db-migrate / node-db-migrate

Database migration framework for node
Other
2.32k stars 360 forks source link

SQL dump should **NEVER** include the migrations table #742

Closed Severino closed 3 years ago

Severino commented 3 years ago

Currently I got the issue, that I have multiple setups, where I often update the data with a dump file. Currently I just take the whole dump file, with the migrations table, pop it in and hope it works. Now I got some misalignment and found the following post from you:

@Mojo90 The failure is on your side. Look through your sql dump. This should NEVER include the migrations table. This is ok as long as you don't restore your dump via db-migrate, but in this case you try to manipulate the migrations table from a migration itself. This will of course fail. Just remove this portion and it should work.

Originally posted by @wzrdtales in https://github.com/db-migrate/node-db-migrate/issues/589#issuecomment-456756411

What is the alternative? I would rather run the migrations and have them do nothing, if the table/column already exists. But I don't find any informations in the documentation how to do that.

When not having (or removing) the migration table I just get the following error at my first migration:

[ERROR] AssertionError [ERR_ASSERTION]: ifError got unwanted exception: Relation »material« already exists

Thanks for your help :)

wzrdtales commented 3 years ago

Yeah, if db-migrate is executing the sql for you, then it shouldn't contain its own tables. If you're dumping straightforward a table, the first question for me is, why don't you play it in with the appropriate command instead? db-migrate is a migration tool and doesn't have any backup functionality yet. That is planned however but not implemented and thus currently not intended.

wzrdtales commented 3 years ago

so the comment there ment. you shouldn't just dump your db and use the output as a migration sql file without removing the db-migrate system tables. however if you restore the dump over normal procedures and not by abusing the migrations somehow for it you shouldn't have any issues.

Severino commented 3 years ago

So when I remove the migrations table manually and run 

db-migrate up

It should restore the structure (or ignore it if it already exists) and not give me any errors? I thought maybe I have to set a 'IF NOT EXIST' somewhere in the migration files or there is something like:

db-migrate restore

Then I must check my code again. Thank you for your quick reply!

wzrdtales commented 3 years ago

db-migrate will create the table, but it can't restore the knowledge in it, so that might not be sufficient. so if your purpose is dump and restore, please dump and restore outside of db-migrate since as said a migration is not a backup, it is a state transition. and db-migrates real backup functionality I planned for is not there yet.

Severino commented 3 years ago

It's perfectly clear to me, that the migration just provides the structure, but has nothing to do with the data inside. But I would need the migration to run on a restored set of data and ignore the migration file if it is already applied (table exist, column was added,...). But this doesn't seem possible at the moment.

It worked for me to just restore the data with the migrations table in place. But when there is one divergance it fails, thats why, as you mentioned, it's a bad idea to do so.

Then I have to finde some other solution :) Thank you for your hard work and the thorough explanation