Sqlx-cli sqlite database reset: "error (code: 14)" with v0.7.1 but not v0.6.3

[Atheyon]

Bug Description

Running > sqlx database reset --database-url sqlite://<full path to file> -y outputs error: error returned from database: (code: 14) unable to open database file with sqlx-cli v0.7.1 but works fine with v0.6.3

the issue also appears with relative file paths

Minimal Reproduction

see above

Info

• SQLx version: 0.7.1
• SQLx features enabled: ["sqlite", "macros", "chrono", "runtime-tokio-rustls", "migrate"]
• Database server and version: SQLite (the version built into sqlx)
• Operating system: Windows 10 22H2 & Windows 11 22H2
• rustc --version: "rustc 1.72.1 (d5c2e9c34 2023-09-13)"

[xtodpep]

Also occurs in sqlx-cli 0.7.2 with

• Windows 10 22H2
• rustc 1.73.0-nightly (439d066bc 2023-08-10)

[pdelanauze]

Looks like the database file needs to exist beforehand. On my end a touch data.db does the trick, then works with relative or absolute paths.

EDIT: Nevermind, found the create_if_missing flag on the ConnectOptions which is false by default

[linky00]

Still occuring on sqlx-cli v0.8.5 with

• Windows 11 24H2
• rustc 1.86.0

[linky00]

Actually, this worked when I ran it for DATABASE_URL="sqlite:D:/tmp/archive.db" but not DATABASE_URL="sqlite://D:/tmp/archive.db", even though sqlx db create ran fine with the //.

[naascimento0]

I encountered the same issue. Initially, I was using the following code:

let pool = SqlitePool::connect_with(options).await?;
sqlx::migrate!("./migrations")
    .run(&pool)
    .await?;

To resolve it, I added SqliteConnectOptions with create_if_missing(true), which fixed the problem:

let options = SqliteConnectOptions::from_str(database_url)?
    .create_if_missing(true);
let pool = SqlitePool::connect_with(options).await?;
sqlx::migrate!("./migrations")
    .run(&pool)
    .await?;

[Oungseik]

I have the same issue. I fix that with the database url should like this sqlite:./database/expanse-tracker.sqlite, which is prefix with sqlite: and the path should be relative path.

The create a database with sqlite3 to make sure. sqlite3 database/expense-tracker.sqlite

To create a sql file run some command in the sqlite. In my case I run .tables. This command check the tables and also create the database in the specified location.

Then you can connect from rust. I connect like this.

pub async fn setup_db() -> Result<SqlitePool, sqlx::Error> {
    let pool = sqlx::sqlite::SqlitePoolOptions::new()
        .max_connections(5)
        .connect(get_config().database_url.as_str())
        .await?;

    Ok(pool)
}