pre-/post-migration hooks from Go package

[josharian]

We'd like the ability to run arbitrary Go code as part of (before or after) a migration. One key point is that we want to be able to use the same Tx as the rest of the sql, so that the entire change is atomic.

I'd be happy to speculate about API and potentially contribute code, but I wanted to check in about the idea first.

Note that this could be a building block for #47, particularly if combined with https://github.com/mvdan/sh (which has the downside of being quite a large dependency).

Thanks!

[jackc]

I can see this being useful. I presume that for this use case you are embedding tern rather than using the CLI?

From the tern side exposing a hook seems fairly simple, but getting the usability right might be a little tricky. Ideally, you would want make it obvious that there was Go code attached to a migration.

In addition, this might create a foot-gun regarding reproducible migrations. With SQL migration files it is well understood that they should be immutable once finalized / released / deployed. Arbitrary code could reach anywhere into your system. This means it would be easy to inadvertently change the meaning of a migration.

...

Another approach might be to expose more of the connection control to the calling application. It could manage the transactions itself and call its own code whenever it wished.

---

Regarding #47, it has the advantage of avoiding the risk of coupling to mutable application logic as well as being runnable from the CLI.

As far as https://github.com/mvdan/sh, I don't think that would be necessary. What I envision is tern literally creating a PostgreSQL proxy that relays commands to the PostgreSQL server and shelling out to the script with the PG* environment variables set to point to its proxy. tern wouldn't need to interpret the script.

[josharian]

I presume that for this use case you are embedding tern rather than using the CLI?

Yep.

Ideally, you would want make it obvious that there was Go code attached to a migration.

True. Instead of pre/post, one (not great?) option here would be to put a magic comment in the migration, and then tie magic comments to registered functions.

This means it would be easy to inadvertently change the meaning of a migration.

It's true. What we have done in the past is address this through conventions, and then delete the Go half as soon as possible, leaving behind only comments.

Another approach might be to expose more of the connection control to the calling application. It could manage the transactions itself and call its own code whenever it wished.

I like it. More obvious that it is a footgun. :)

---

Not 100% related, but I'd like to say thank you. Maintaining OSS is a serious undertaking, and I really appreciate all your work. pgx (et al) is a real boon to the ecosystem, and we are indebted to you for it.

[davidmdm]

Seconded. Your work is amazing.