Use system variables in migrations

[fabioDMFerreira]

I am encrypting a set of fields with AES_ENCRYPT and I would like to use an environment variable to specify the secret key instead of hard-coding it.

Example:

 UPDATE users SET enc_last_name = AES_ENCRYPT(last_name,'1234');

I would like 1234 to be some variable like @secret_key.

Does dbmate support this use case?

[fabioDMFerreira]

duplicated of #118

Will this feature be added in a near future? @amacneil

[amacneil]

No current plans to add this. Open to a PR.

[davidecavestro]

About #118 (comment)

After thinking about this some more, I think the easiest way to add it would be to use the Go template library. So your migrations would end up something like this:

create role {{ .Role }} login password {{ .RolePass }}

I like the idea of exposing go template syntax as it generally stands out within SQL snippets.

I noticed dbmate migration parsing logic already has support for optional arguments
that are parsed as generic key:value entries

and then exposed with a specific sematic

So the parsing output mapping could be enhanced to support declaring exposed env vars, migration per migration.

i.e. a syntax like
-- migrate:up env-var:ROLE env-var:ROLEPASS
would limit both the set of exposed env vars (just explicitly declares ones) and the scope (just that particular up migration)

This would somewhat mitigate the point

We don't do any sql parsing, so it's not safe to simply inject variables directly

IMHO the complexity added this way would be negligible: add the option to existing ParsedMigrationOptions and use its values to pass the raw sql through go templating (just if/when needed).

Side effects like templating syntax conflicts would be limited to migrations having that option enabled, of which migration author should be aware.

If it makes sense and need some help I could propose a PR.

[amacneil]

I like the suggestion of explicitly opting in to expose env vars to the migration. I would simplify it to just env:

-- migrate:up env:ROLE env:ROLEPASS

[davidecavestro]

Sketched PR #439