CODEWITHSUNDEEP
rustrust-sqlx
cdaringe
cdaringe

Reputation: 1538

How to create a SQLite database with rust sqlx

I'm trying out a conversion from rusqlite => sqlx.

Opening a connection from rusqlite calls SQLite::open, and creates the db files. The following works:

use rusqlite::Connection;

Connection::open(db_filename)

However, I'm following the docs on the sqlx side (https://github.com/launchbadge/sqlx#connecting), and they launch me immediately into creating a pool:

use sqlx::sqlite::{SqlitePoolOptions};

SqlitePoolOptions::new()
            .max_connections(1)
            .connect(&format!("sqlite://{}", db_filename))
            .await
            .map_err(|err| format!("{}\nfile: {}", err.to_string(), db_filename))?;

which in fact yields the Result<_, String> message of:

Error: "error returned from database: unable to open database file\nfile: /path/to/my.db"

I'm not clear how in the sqlx world to actually write those db files on first boot.

Tips?

Upvotes: 13

Views: 12076

Answers (3)

Joshua Megnauth
Joshua Megnauth

Reputation: 461

You can also use SqliteConnectOptions in conjunction with Pool::connect_with with newer versions of sqlx.

use sqlx::{sqlite::SqliteConnectOptions, Error, SqlitePool};
use std::{future::Future, path::Path};

async fn connect(filename: impl AsRef<Path>) -> impl Future<Output = Result<SqlitePool, Error>> {
    let options = SqliteConnectOptions::new()
        .filename(filename)
        .create_if_missing(true);

    SqlitePool::connect_with(&options)
}

SqliteConnectOptions is very flexible and supports many useful options such as loading extensions.

Upvotes: 10

Dave Horner
Dave Horner

Reputation: 423

Standard practice is to set your DATABASE_URL

sqlx db create

then run

sqlx migrate run

Upvotes: 0

cdaringe
cdaringe

Reputation: 1538

I found an issue in their issue tracker, where you can use ?mode=rwc query style param on the filename.

https://github.com/launchbadge/sqlx/issues/1114#issuecomment-827815038

Adding the query solved the problem.

Upvotes: 7

Related Questions