SQLite+Macros: relative paths for database files are resolved inconsistently

[abonander]

Superceded issues: #1811, #1399, #1260

Ultimately, the issue here is that relative paths are resolved in relation to the current working directory of the process, and that entirely depends on the context where the process is invoked.

When compiling with query!() in a workspace, Cargo invokes rustc, and thus rustc invokes the proc-macros, with the working directory set to the workspace root, whereas a cargo run or cargo test command is going to inherit the current working directory of your shell, and thus the runtime resolution of the path can be different.

Changing to resolve relative to CARGO_MANIFEST_DIR for the current crate isn't a solid fix, because it's going to break the workflows of users who are already building relative to their workspace roots. And that still won't guarantee that it will resolve correctly because you could be invoking that cargo test command from your workspace root, which will change the runtime resolution of the path. It's honestly kind of a crapshoot all around.

One potential improvement I can see is to change the behavior when loading DATABASE_URL from a .env file:

• Start searching for .env files in CARGO_MANIFEST_DIR, not the workspace root.
  • This will allow the user to define a separate DATABASE_URL for each crate in a workspace.
  • This will necessitate implementing our own code to search for .env files, as dotenvy's search functionality is hardcoded to start in the current working directory.
  • The user might be using .env files for other things as well, so we likely need to keep searching if we encounter a .env file without a DATABASE_URL key.
• When resolving a relative path for DATABASE_URL from a .env file, resolve relative to the path of the .env file.
  • This will automatically fall back to the existing behavior for compiling in a workspace if the only .env file is at the workspace root.
• Provide a constructor for SqliteConnectOptions that directly loads from a .env file so this behavior can be replicated at runtime.

For DATABASE_URL set directly in the environment, however, we have no way of knowing what directory the user intended it to be relative to. I think in this case, our only option is to improve the documentation, maybe recommend setting DATABASE_URL=sqlite:$PWD/path/to/file.db.

There's also a lot of confusion re. the difference between sqlite: and sqlite:// prefixes which is worth working out.

[MrGVSV]

Changing to resolve relative to CARGO_MANIFEST_DIR for the current crate isn't a solid fix, because it's going to break the workflows of users who are already building relative to their workspace roots. And that still won't guarantee that it will resolve correctly because you could be invoking that cargo test command from your workspace root, which will change the runtime resolution of the path. It's honestly kind of a crapshoot all around.

Maybe I'm testing it wrong, but #3097 works both when building from the workspace root as working directory or the sub-crate as working directory.

• Start searching for .env files in CARGO_MANIFEST_DIR, not the workspace root.
  • This will allow the user to define a separate DATABASE_URL for each crate in a workspace.
  • This will necessitate implementing our own code to search for .env files, as dotenvy's search functionality is hardcoded to start in the current working directory.
  • The user might be using .env files for other things as well, so we likely need to keep searching if we encounter a .env file without a DATABASE_URL key.

Afaik this is what currently happens: https://github.com/launchbadge/sqlx/blob/bbfd0d711aa3bfa4123b08b03d60ff08cadbc7dc/sqlx-macros-core/src/query/mod.rs#L112-L118

And #3097 used the fact that .env is searched relative to the crate currently being built in order to resolve the absolute path. For example, with #3097, the following should work:

my-app/
├── crates/
│   ├── client
│   ├── mysql/
│   │   └── .env # mysql://user:pass@host/database
│   ├── sqlite/
│   │   └── .env # sqlite:../test.db
│   └── sqlite-legacy/
│       ├── .env # sqlite:test.db
│       └── test.db
└── test.db

• When resolving a relative path for DATABASE_URL from a .env file, resolve relative to the path of the .env file.
  • This will automatically fall back to the existing behavior for compiling in a workspace if the only .env file is at the workspace root.

I wasn't aware this was the existing behavior. So if a workspace defines a .env file at the root, that one is used?

• Provide a constructor for SqliteConnectOptions that directly loads from a .env file so this behavior can be replicated at runtime.

Agreed!

For DATABASE_URL set directly in the environment, however, we have no way of knowing what directory the user intended it to be relative to. I think in this case, our only option is to improve the documentation, maybe recommend setting DATABASE_URL=sqlite:$PWD/path/to/file.db.

I just did a quick test and yeah #3097 doesn't handle it well :/

While the sqlx commands work fine, using cargo will break things because everything is built relative to its own crate. We'd need a way to differentiate the two (.env and direct export), which I'm not sure is possible 🤔

There's also a lot of confusion re. the difference between sqlite: and sqlite:// prefixes which is worth working out.

Don't forget sqlite:/// lol

[gabrielgrover]

@abonander Is there a work around to this issue? I have my .env in a workspace dir and not at the root.

DATABASE_URL=sqlite://mined_data/mined_data.db

Running sqlx migrate run works, but I get a code 14 when using sqlx::query! macro.

I have also tried using an absolute path and I get code 14 for both the migration command and the sqlx::query! macro

[SetsuikiHyoryu]

I referred to https://github.com/launchbadge/sqlx/issues/1399#issuecomment-936342111, and succeeded. I also tested changomg "///" to "//" and suceeded as well. I don't know if "$PWD" works in Unix-like systems, but it failed in Windows. And the "%CD%" in Windows CMD is said to be disallowed UTF-8 characters.

[FritzerMcQ]

I created a little work around using a symlink. That helped in my case as non of the solutions above worked out for me.

my-app/
├── crates/
│   └── my-app-db/
│       ├── Cargo.toml
│       └── test.db --> ../../test.db
├── .env # DATABASE_URL="sqlite://test.db"
├── Cargo.toml
└── test.db

Rust-analyzer is happy and cargo can build as well without any issues. Surely not a very nice solution, but I can continue to work for now.

[dominictobias]

I gave up and stopped using cargo workspaces so it works 😅

[videni]

Damn, this is my first try of sea_orm, got this error, even this simple case doesn't follow human instinct.

[chrisp60]

FWIW, putting the absolute path to a db in a .cargo/config.toml section in my workspace root seemed to work for rust-analyzer. Not perfect, but it works

[env]
DATABASE_URL = { relative = false, value = "sqlite3:////home/yourname/project/items.db" }

edit: just want to say while this DOES work, I forgot to mention that removing the DATABASE_URL from my .env file was also needed. I am unsure if sqlx unconditionally sources .env, but it may be worthwhile to look into. This does make it a bit more inconvenient to use sqlx-cli, but the url can always be set in other ways for that.

[jvatic]

Another way around this is to configure SQLX_OFFLINE=true in .cargo/config.toml and run sqlx prepare whenever queries change. And while this is a bit cumbersome when iterating on a query, it does make it hard to forget to run sqlx prepare if that's in your workflow.

[andrewchen5678]

Damn, this is my first try of sea_orm, got this error, even this simple case doesn't follow human instinct.

actually this works for me, add mode=rwc at the end will create the database file if not exists:

const DATABASE_URL: &str = "sqlite://test.db?mode=rwc";

async fn run() -> Result<(), DbErr> {
    let db = Database::connect(DATABASE_URL).await?;

    Ok(())
}

[jeancf]

I just started with sqlx and I have been welcomed by a wall of SqliteError { code: 14, message: "unable to open database file" } no matter how I wrote the connection string. I was about to give up when I found @andrewchen5678's message above. I added ?mode=rwc and boom, it works.

I thought that the problem was that the file did not exist yet and the c in ?mode=rwc would create it. but even with an existing file, removing the ?mode=rwc gives me again the dreaded SqliteError { code: 14, message: "unable to open database file" }. However, using ?mode=rw or ?mode=ro works fine. I conclude that the mode part of the connection string is mandatory.

[steveklabnik]

Came here to say what worked for me.

I have a project that's a workspace, and am using sqlite. What I had working when I was using query, but not query!, was:

DATABASE_URL=sqlite://${CARGO_MANIFEST_DIR}/../../db/database.db

Where db/database.db was in my project's root, and the web server crate was two directories deep.

The issue there is it then required passing --database-url directly to various sqlx commands, since there's no cargo manifest dir when you're not invoking via cargo.

After a lot of banging my head off the wall,

DATABASE_URL=sqlite:db/database.db

Seems to work correctly in all cases.

[JuliDi]

Is there a current "best practice" example of how to use sqlx in CI with cargo workspaces?

It seems like a setup with sqlx-cli working does not mean the macros are working during compilation (relative paths might be causing trouble, but there is no log output which db file path it tries to open).

Especially when trying to use a crate with sqlx macros as a dependency for another crate in the workspace, I couldn't get it to work. Any ideas?