Reading DATABASE_URL from .env files implicitly can be very dangerous/potentially destructive

[benwilber]

I have found these related issues/pull requests

Somewhat related to #3061

Description

$ sqlx migrate run --help
Run all pending migrations
...
  -D, --database-url <DATABASE_URL>
          Location of the DB, by default will be read from the DATABASE_URL env var or `.env` files
...

sqlx cli reads DATABASE_URL from .env files implicitly which can be very dangerous and potentially destructive when running database commands against different environments (prod, staging, dev, etc.)

It is easy to accidentally run a migrate run/revert against a database that you did not intend to just by accidentally being in the wrong directory. Is it not sufficient for the developer to use something like dotenv, foreman, etc. like they normally do to explicitly load their .env files into the environment when running sqlx commands? This is much safer and less surprising. "Principle of Least Surprise" and all that.

Reproduction steps

Run an sqlx migrate command whilst in a directory with a .env file pointing to a server different than the one you thought (maybe you just changed directories, or are working in a script).

SQLx version

sqlx-cli 0.7.4

Enabled SQLx features

"chrono", "postgres", "runtime-tokio"

Database server and version

PG 14

Operating system

macOS

Rust version

rustc 1.84.0 (9fc6b4312 2025-01-07)

[abonander]

A lot of commands can be dangerous/destructive if run in the wrong folder. If this is a serious concern for you, perhaps you might consider not using .env files in general.

For the most destructive commands (db drop/db reset), we have a confirmation prompt. I could see adding one for migrate revert, but having to do it every time for migrate run might get a little annoying. I would also probably consider adding confirmation prompts to commands that didn't previously have them to be a breaking behavior change, since it would break any kind of automated script.

[benwilber]

If this is a serious concern for you, perhaps you might consider not using .env files in general.

I like using .env files. I just don't expect every command I run to load and run with them implicitly.

A lot of commands can be dangerous/destructive if run in the wrong folder.

I agree that this is probably true. But I'm struggling to think of one that works this way just by implicitly reading a magic (and hidden) file in that folder

[abonander]

Well, I'm happy to discuss alternatives but otherwise there isn't really anything actionable here.

[benwilber]

I think a reasonable and non-breaking change would be to have a top-level switch like --no-dotenv that will disable the automatic reading of .env files. This would make it safer for scripts to run without any of the "auto-detection" features for DATABASE_URL. A "safe mode" of sorts. It would also allow paranoid people like me to just alias sqlx like

alias sqlx="sqlx --no-dotenv"

[benwilber]

@abonander I have opened a PR that addresses my concerns about this. Please let me know if this is something you're interested in. Thanks!

#3724