RFC: how should sqlx-cli handle migrations in dependencies?

[abonander]

A question that I ran into while implementing the multi-tenant example in #3383 is how to make it more convenient to run migrations from multiple crates at once.

sqlx-cli could gain a --recursive flag that checks subdirectories for sqlx.toml files, but that would only work for crates within the directory tree.

For dependencies from crates.io that need to run migrations (e.g. tower-sessions-sqlx-store), we would probably need to check dependencies through Cargo.

I'm imagining something like cargo sqlx db setup --deps which scans dependencies for migrations folders, but we don't want to just assume that a migrations/ folder existing means SQLx should run the scripts within; they could be using a different tool. Scanning dependencies' sources for sqlx.toml files would also likely be pretty inefficient.

For best results this should probably be explicitly opt-in per crate. The dependent crate could have something like this in its sqlx.toml:

[migrate]
# A list of package names of dependencies to additionally run migrations for.
# These migrations will be run first in the order specified, then the current crate's.
include-dependencies = ["foo", "bar"]

This would require running as cargo sqlx so we can use cargo metadata to look up the dependencies.

[esmevane]

Some thoughts just off the top of my head:

1. Is it possible that --recursive and db setup --deps (maybe db setup deps?) are complimentary? I've had cases where I needed both.
2. If there's an opt-in step, does it make sense to have a scan feature that surfaces the information to the developer? Like, "here's all the things we found in the workspace, and here's all the stuff we found in the dependency graph" in some kind of summary format?

[jssblck]

The main use case my team has for sqlx 0.9 is for workspaces that contain multiple services each using their own database; for this purpose it'd actually be ideal to have something like cargo sqlx -p <name> migrate run so that we don't have to cd into the package directory and it'd be perfect.

For this use case we don't care about external crates that aren't in the current workspace either so that's simpler.

[abonander]

Yeah, cargo sqlx -p <package> or cargo sqlx --workspace to match existing Cargo idioms kind of makes sense.