Is it possible to set connection options with SQLx SQLite pool

[wezm]

Specifically I'd like to be able to change the synchronous value.

[joenano]

I have a similiar question:

Is it possible to use an encrypted db with SqlitePool?

[wezm]

I think working with an encrypted db would be possible if you could supply custom SqliteConnectOptions (linked above), which is also what I'm after. You'd call the pragma method with one of key, hexkey, textkey as the first argument.

[joenano]

Hopefully the ability to use SqliteConnectOptions is added then, I find it quite surprising how little attention has been given to connecting to encrypted databases, id have thought it was a quite important detail but apparently not, no examples in Rocket or Sqlx.

I only started using Rust a few days ago specifically to use Rocket as a server framework, so digging into generated docs for this has been a bit of a nightmare given my lack of Rust knowledge.

[wezm]

I find it quite surprising how little attention has been given to connecting to encrypted databases

Rocket is still maturing and still working on core things like CSRF tokens. I'm sure that everything will get filled out over time.

[joenano]

Yeah, its a very promising project that looks like it has the community to keep improving. Id like to see it trimmed a bit, there seems to be 10 different ways to do everything which makes it difficult to know if you are doing it right.

[szymek156]

@wezm @joenano
I think you are able to do it with sqlx 0.6.0, like so:

let options = SqliteConnectOptions::from_str(&url)?
    .pragma("key", "password")
    // other options, like synchronous land here
    .foreign_keys(true);

let pool = SqlitePoolOptions::new().connect_with(options).await?;

[wezm]

I'm using the Database derive, which I don't think provides a way to supply SqliteConnectOptions:

use rocket_db_pools::{sqlx, Database};

#[derive(Database)]
#[database("cotton_db")]
pub struct Db(pub sqlx::SqlitePool);

[dylanhand]

I have the same question. I'm using Database derive, as @wezm has outlined, and want the database to be created if it doesn't exist using create_if_missing.

Any ideas?

I'm using the Database derive, which I don't think provides a way to supply SqliteConnectOptions:

use rocket_db_pools::{sqlx, Database};

#[derive(Database)]
#[database("cotton_db")]
pub struct Db(pub sqlx::SqlitePool);

[5-pebbles]

I don't know if it is the "right" way... but you can still use the Database derive if you implement your own pool.

Example:

use rocket_db_pools::{sqlx::{self, ConnectOptions}, Database, Pool};

use rocket::config::LogLevel;
use rocket::figment::Figment;
use std::time::Duration;

type Options<D> = <<D as sqlx::Database>::Connection as sqlx::Connection>::Options;

fn specialize(__options: &mut dyn std::any::Any, __config: &rocket_db_pools::Config) {
    if let Some(o) = __options.downcast_mut::<sqlx::sqlite::SqliteConnectOptions>() {
        *o = std::mem::take(o)
            .busy_timeout(Duration::from_secs(__config.connect_timeout))
            .create_if_missing(true);
    }
}

// So we can set our own pragma
// I mean I don't need to but I want to make sure I can...
pub struct MyPool(sqlx::SqlitePool);

#[rocket::async_trait]
impl Pool for MyPool {
    // Like everything(really everything(I mean not quite everything(but close))) relating to this is just yoinked from:
    // https://api.rocket.rs/v0.5/src/rocket_db_pools/pool.rs#278
    type Error = rocket_db_pools::Error<sqlx::Error>;

    type Connection = sqlx::pool::PoolConnection<sqlx::Sqlite>;

    async fn init(figment: &Figment) -> Result<Self, Self::Error> {
        let config = figment.extract::<rocket_db_pools::Config>()?;
        let mut opts = config
            .url
            .parse::<Options<sqlx::Sqlite>>()
            .map_err(rocket_db_pools::Error::Init)?;
        specialize(&mut opts, &config);

        opts = opts.disable_statement_logging();
        if let Ok(level) = figment.extract_inner::<LogLevel>(rocket::Config::LOG_LEVEL) {
            if !matches!(level, LogLevel::Normal | LogLevel::Off) {
                opts = opts
                    .log_statements(level.into())
                    .log_slow_statements(level.into(), Duration::default());
            }
        }

        // custom options
        let custom_opts = opts.pragma("foreign_keys", "OFF");
        // again I have no use for this right now but meh

        Ok(MyPool(
            sqlx::pool::PoolOptions::new()
                .max_connections(config.max_connections as u32)
                .acquire_timeout(Duration::from_secs(config.connect_timeout))
                .idle_timeout(config.idle_timeout.map(Duration::from_secs))
                .min_connections(config.min_connections.unwrap_or_default())
                .connect_with(custom_opts)
                .await
                .map_err(rocket_db_pools::Error::Init)?,
        ))
    }

    async fn get(&self) -> Result<Self::Connection, Self::Error> {
        self.0.get().await
    }

    async fn close(&self) {
        self.0.close().await;
    }
}

#[derive(Database)]
#[database("db")]
pub struct MyDatabase(MyPool);

[wezm]

Interesting. I'll try that out, thanks.