Insert / pre-populate migrations schema to reflect migrations manually applied before Postgrator first use

[joelbernstein]

Assume a real-world scenario where the production database has had migrations applied by hand.

Does Postgrator expose enough interface to be able to use it to enter ros in the migration schema as though they had been inserted by running the migrations? That is, assuming I certify that the migration has been manually applied, how can I tell postgrator that this is the case, and have it recorded with md5sums in the appropriate db table?

I would be willing to make the necessary modifications, if this isn't already possible, if someone could give me some pointers.

[joelbernstein]

Does this make sense? I mean:

Assuming I have:
migrations/000.do.schema.sql
migrations/001.do.migrate-foo.sql
migrations/002.do.migrate-something-else.sql

And I have applied 000 and 001 to an existing database, without using Postgrator. Only 002 is pending. But Postgrator can't tell this because the migrations schema is empty. What can I do to avoid this?

How would I tell Postgrator that the first have already been applied, so that it will not attempt to run them again? I presume I need to insert some rows into the migration table. I think this should probably be a Postgrator feature. Can you advise how to implement it please?

[rickbergfalk]

In this case you'd need to populate the necessary rows in the migrations table. Postgrator itself doesn't expose an interface for these manual updates at all.

Is this just a one-off case or are you frequently applying migrations manually?

In my experience, applying migrations out-of-band like that kind of limits the benefit of using a migration framework/tool like Postgrator. By only updating a database via migration tooling you are guaranteed to have a known schema and increase confidence in future changes.

I know that's a pretty strict stance to take and not all organizations can make that commitment.

I've worked in both kinds of orgs though, and I just want to share that it is so much better when a database schema is consistently reproducible in any environment you need it to be without manual intervention. :)

[joelbernstein]

Perhaps I didn't make the use case clear. This is about regularising the existing state so that Postgrator can run migrations from here onward. We already have a production deployment with a database, which has already been migrated manually. We wish to use Postgrator in future. The issue is that we need to avoid duplicating migrations that were already run manually. So of course we need to pre-populate the migrations table. Given that postgrator and its common client lib already have 90% of this functionality, and given it must be a relatively common requirement, it seems a good piece of interface to add to Postgrator, some method call that loads the relevant migrations, calculates their hash, and inserts rows into the migration log table to imply that they have been applied. Does that make more sense?

…

On Thu, 19 Sep 2019 at 17:48, Rick Bergfalk ***@***.***> wrote: In this case you'd need to populate the necessary rows in the migrations table. Postgrator itself doesn't expose an interface for these manual updates at all. Is this just a one-off case or are you frequently applying migrations manually? In my experience, applying migrations out-of-band like that kind of limits the benefit of using a migration framework/tool like Postgrator. By only updating a database via migration tooling you are guaranteed to have a known schema and increase confidence in future changes. I know that's a pretty strict stance to take and not all organizations can make that commitment. I've worked in both kinds of orgs though, and I just want to share that it is *so much better* when a database schema is consistently reproducible in any environment you need it to be without manual intervention. :) — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#91?email_source=notifications&email_token=AAALAJYQXDEEGDUJMWM7PLTQKOUMNA5CNFSM4IYM6UB2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD7EDYAY#issuecomment-533216259>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAALAJ4KQJIXTB75MJ6F7F3QKOUMNANCNFSM4IYM6UBQ> .

-- Kind regards, Joel Bernstein

[rickbergfalk]

Ah - that does.

The function to log a migration is persistActionSql on the common client. When running migrations that is called here https://github.com/rickbergfalk/postgrator/blob/master/postgrator.js#L206

What migrations exactly should be logged as already migrated will be up to you to provide I'd imagine. If I had some databases already created manually I'd likely manually insert the rows necessary as well.

Depending on how you plan on running migrations you might be interested in https://www.npmjs.com/package/postgrator-cli

[joelbernstein]

Thank you. I'm not using postgrator-cli, largely because I need the schemaTable option which is only exposed to CLI via a config file. As we are in a Docker environment this isn't ideal - although it's possible to provide that file via Kubernetes it makes testing more complicated. So I have ended up using the library directly. I would look at sending a patch to postgrator-cli to expose that option instead, perhaps. I'd be happy to have Postgrator able to insert rows corresponding to any migrations it considers to be pending, which I tell it are actually already applied.

…

On Thu, 19 Sep 2019 at 18:21, Rick Bergfalk ***@***.***> wrote: Ah - that does. The function to log a migration is persistActionSql on the common client. When running migrations that is called here https://github.com/rickbergfalk/postgrator/blob/master/postgrator.js#L206 What exactly should be logged as already migrated will be up to you to provide I'd imagine. If I had some databases already created manually I'd likely manually insert the rows necessary as well. Depending on how you plan on running migrations you might be interested in https://www.npmjs.com/package/postgrator-cli — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#91?email_source=notifications&email_token=AAALAJ6QMKAWP2F6JFMJHN3QKOYJ7A5CNFSM4IYM6UB2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD7EGV2I#issuecomment-533228265>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAALAJZZHUZLMQR6DFFRA43QKOYJ7ANCNFSM4IYM6UBQ> .

-- Kind regards, Joel Bernstein