Description
Is your feature request related to a problem? Please describe.
When running migrations from Rust code, the only way (as far as I know) is passing the path to the migrations directory. After this, it is not possible to touch the migrations contents. This makes it impossible to parameterize the migrations. E.g. if a migration needs to be run for multiple schemas.
Describe the solution you'd like
Two complementary solutions could be applied.
The first (easier) step would be modifying the Migrator
trait to allow for manual insertion of migrations via string. This way, migration contents would be pushed by the application with arbitrary content. This would solve the problem because the substitution step would be done by the application before passing the migration contents to Migrator
.
The second (more complex) step would be to implement in sqlx a variable substitution feature for migrations. So that the application can register the variables and their replacement to the Migrator
, which can continue to work with a Path
, given that it will take care of variable substitution.
The two solutions could then live side by side for maximum flexibility.
Describe alternatives you've considered
I am trying to work around the problem by manually replacing the content of the migrations
field of the Migrator
struct, but that does not look like a very clean solution.
Activity
adriangb commentedon Aug 14, 2024
We're interested in this feature as well. Might even be able to implement it if there's buy in from maintainers.
peter-lockhart-pub commentedon Sep 12, 2024
Also interested. I was intending on providing end users our application binary and the same set of SQL migrations to run as we run on our tech stack. Ideally these would be identical for consistency, but then how might ourselves or end users configure e.g. the password of a particular role? Some substitution of an environment variable in this case would be a solution
krlohnes commentedon Mar 22, 2025
goose implements env substitution with a toggle via comment to maintain backwards compatibility with scripts that may use
${ENV_VAR}
like syntax in their SQL already. It might be a reasonable approach to implement in sqlx as well.abonander commentedon Mar 22, 2025
See also #2726
Add migration parameters
Add migration parameters
krlohnes commentedon Mar 22, 2025
I took a quick pass at implementing something similar to goose's implementation. I'll take a look at #2726 as well, but at a quick glance, you could achieve the goals of either with something along the lines of the following. I'm happy to add some tests, documentation, and push up a PR for this functionality.
krlohnes commentedon Mar 22, 2025
The code above probably needs to get moved over to the
Migrator
, but the general approach still stands.abonander commentedon Apr 11, 2025
The solution proposed by @krlohnes above / in #3805 is a non-starter for the following reasons:
migrate!()
or may even expect variables to be expanded at compile-time (or at least to have that option).env::set_var()
, which is not thread-safe on most platforms.regex
crate to find the start and end of substitution-enabled sections is kind of overkill.The templating syntax in the migrations is fine, although the
-- sqlx envsub on
switch needs work. The style doesn't match the existing-- no-transaction
annotation or the-- transaction-break
annotation we've been working on in #3694.For bikeshedding purposes, in order of personal preference:
-- enable-substitution
/-- disable-substitution
-- enable-templating
/-- disable-templating
-- expand-templates
/-- no-expand-templates
-- arg-substitution-on
/-- arg-substitution-off
It's an open question whether we should namespace these annotations: IMO, it just adds noise, and users should not be mixing and matching migration tooling anyway. I'm open to discussing that, but if we're going to namespace one annotation, we should namespace them all, and that's an orthogonal concern.
It should not require the
regex
crate to parse these annotations. Just scan line by line and check if the whole line consists of the annotation or not.The
Migrator
should have a builder method that allows the user to add key-value pairs for templating arguments. When run, it should parse migrations for argument substitutions and search the set it was given, returning an error if one is missing without a default value.Ideally, the
migrate!()
macro would parse template arguments out at compile-time and require them to be passed in its input (constant keys with value expressions that are evaluated at runtime). That could theoretically be implemented later, though.krlohnes commentedon Apr 11, 2025
@abonander I'm going to take another pass at this. Do you have any thoughts on how to support this with
sqlx-cli
? With environment variables, it was simply "set the environment variables". My initial thought is a toml file with the key value pairs in it. I'd add a flag to set the path to the file.abonander commentedon Apr 12, 2025
@krlohnes sqlx-cli could still use environment variables, I would just require a command-line switch to enable that. You could add that as a switch to
Migrator
as well. The biggest issue was making that the default behavior.Passing arguments on the command line should also be possible, bikeshedding:
-p<key>=<value>
or--parameters <key>=<value>,<key>=<value>
. There's an example of how to do that with Clap: clap-rs/clap#4291 (reply in thread)That can always be added later though.