Setting SQLX_OFFLINE=true in .env breaks cargo sqlx prepare --workspace

[mikeparisstuff]

I have found these related issues/pull requests

• cargo sqlx prepare expecting DATABASE_URL in workspace scenario #1223
• Question: best way to use sqlx with connections to two different databases? #121

Description

I've been happily using sqlx w/ the CLI & offline mode for ~ a year now, but am just now running into some tooling issues in SQLx CLI. I suspect there is a subtle bug in SQLx, but I am not sure.

I have tried this with both SQLx CLI 7.4 & the most recent 8.5 and get the same issue. Please let me know if you know whats wrong.

I have had these two lines in my .env for a year:

SQLX_OFFLINE=true
DATABASE_URL=postgres://local:local@localhost:5432/local

And I have been able to run cargo sqlx prepare --workspace which generates the .sqlx files and then cargo build/watch works as expected using the offline mode. All is great.

However, recently, if I try to run cargo sqlx prepare --workspace while SQLX_OFFLINE=true is in my .env, the command fails with many failures that imply it cannot find the .sqlx files, e.g. these errors:

error: `SQLX_OFFLINE=true` but there is no cached data for this query, run `cargo sqlx prepare` to update the query cache or unset `SQLX_OFFLINE`

I can fix this error by deleting SQLX_OFFLINE=true from my .env, and then running cargo sqlx prepare --workspace again. Everything works fine and I see the normal affirmative:

query data written to .sqlx in the workspace root; please check this into version control

I can then add SQLX_OFFLINE=true back to my .env and run cargo build/watch. I would much prefer to not need to do this dance of deleting the line from .env and then adding it back anytime I need to run prepare.

I haven't made any significant architectural changes to my app and I suspect the issue may be related to the effort to support multiple crates/db urls, but I am not sure.

I have a single .env file at the top level of my monorepo and would expect this to work given the documentation implying the prepare is meant to ignore the offline env var and will always connect to the DB.

Anyone have any ideas? Am I doing something wrong? Did somethinge change that I need to update?

I appreciate all the work you all put into SQLx. Thanks!

Reproduction steps

I suspect will be hard to reproduce my exact setup but the basic setup is:

1. Put these lines into .env. Assume the database exists at the URL and we are able to connect.

SQLX_OFFLINE=true
DATABASE_URL=postgres://local:local@localhost:5432/local

2. I run cargo sqlx prepare --workspace and it fails with errors:

`SQLX_OFFLINE=true` but there is no cached data for this query, run `cargo sqlx prepare` to update the query cache or unset `SQLX_OFFLINE`

3. I delete SQLX_OFFLINE=true, leading the DATABASE_URL in .env

4. I re-run cargo sqlx prepare --workspace, it succeeds

SQLx version

SQLX - 0.8.5

Tried multiple CLI versions:
SQLxCLI - 7.4 & 8.4 & 8.5

Enabled SQLx features

I installed via cargo install sqlx-cli --no-default-features --features native-tls,postgres. Only use postgres.

Database server and version

Postgres 16.x

Operating system

M4 Mac. Postgres running in AMD64 docker container.

Rust version

rustc 1.85.1 (4eb161250 2025-03-15)

[mikeparisstuff]

Made some progress debugging this. I noticed that if I pin all my libraries to use sqlx = { version = "=0.8.3", ... } and also explicitly install cargo install sqlx-cli --no-default-features --features native-tls,postgres --version 0.8.3 then things work again.

[abonander]

Likely caused by #3815. In retrospect, unconditionally overriding the environment with values from .env was objectively the wrong choice, but it wasn't obvious at the time. I'll take the blame for that.

The core bug that #3815 was trying to fix was the statefulness of environment variables. This is inherently unsound anyway due to glibc (and other libc implementations I think) not requiring any synchronization in updating them, which is exactly what prompted the change of std::env::set_var() to require unsafe in the 2024 edition.

dotenvy is currently undergoing a major rewrite to address this, and it's going to become unsafe to modify environment variables unless you use its new attribute for main().

Ultimately, we shouldn't need to modify the environment anyway; that's not really idiomatic to Rust. The current API of dotenvy (inherited from dotenv) just makes it unnecessarily annoying not to.

It looks like the rewrite of dotenvy is going to have a much better API, but it's not clear how far that is from a stable release and I don't want to place any undue pressure on the author.

Ultimately, it seems like we need to implement our own routine to read an environment variable or fall back to reading from the .env. Then that can be replaced with the updated dotenvy once it's out.

cc @joeydewaal

[joeydewaal]

Completely missed this, the suggested solution sounds trivial. I'll try to fix this.

[martcpp]

pls how have this issue gone
@joeydewaal are you able to fine a walk around because i am still facing same