How do I run a few PRAGMAs against SQLite when the connection is created?

[tibbe]

I'd like to run the following SQL statements against my SQLite database to run it in WAL mode:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = ...;

In bb8 this would be a connection_customizer on the pool builder. What's the equivalent in deadpool?

[bikeshedder]

You can add a post_create hook which is called right after a new connection is created.

[bikeshedder]

https://docs.rs/deadpool/latest/deadpool/managed/struct.PoolBuilder.html#method.post_create

[tibbe]

@bikeshedder thanks. I'm very new to Rust so I hope you ignore my ignorance. :)

In post_create I seems to have access to the PoolBuilder and the Manager. How do I get hold of the actual connection?

[bikeshedder]

The documentation for hooks is suboptimal. You can see an example of the usage in the tests: https://github.com/bikeshedder/deadpool/blob/master/tests/managed_hooks.rs

[tibbe]

This is what I eventually came up with:

    let pool = deadpool_diesel::sqlite::Pool::builder(manager)
        .post_create(Hook::sync_fn(|obj, _| {
            let mut conn = obj.lock().unwrap();
            let res = conn.batch_execute(
                r#"
                PRAGMA journal_mode = WAL;
                PRAGMA synchronous = NORMAL;
                PRAGMA foreign_keys = ON;
                PRAGMA busy_timeout = 5000;
                "#,
            );
            match res {
                Ok(_) => Ok(()),
                // hopeless wrangling with error types here, idk how to get the diesel error
                // into a HookError
                Err(_err) => Err(HookError::message("error configuring database connection")),
            }
        }))
        .build()
        .unwrap();

I don't really know what I'm doing here. Not sure if taking that lock is a bad idea. Also the error handling is suboptimal.

[bikeshedder]

It should be fine 99% of the time. This code blocks the async runtime while the query is running. You could use async_fn instead and use the interact method inside the hook function.

[tibbe]

Here's what I finally came up with with @bikeshedder's help:

    let pool = deadpool_diesel::sqlite::Pool::builder(manager)
        .post_create(Hook::async_fn(|obj, _| {
            Box::pin(async move {
                let res = obj
                    .interact(|conn| {
                        conn.batch_execute(
                            r#"
                            PRAGMA journal_mode = WAL;
                            PRAGMA synchronous = NORMAL;
                            PRAGMA foreign_keys = ON;
                            PRAGMA busy_timeout = 5000;
                            "#,
                        )
                    })
                    .await;
                match res {
                    Ok(_) => Ok(()),
                    // hopeless wrangling with error types here, idk how to get the diesel error
                    // into a HookError
                    Err(_err) => Err(HookError::message("error configuring database connection")),
                }
            })
        }))
        .build()
        .unwrap();

[bikeshedder]

You're absolutely right. 🤦

Usually you should never see an InteractError in the wildlife. It can only happen if a panic occurs (then unwrapping it should be okay) or if the callback was aborted (which should never happen for a hook either).

It SHOULD be fine to unwrap() the first result layer. When writing that API I didn't want to decide for the user what should happen in the case of a panic. Now that I think about it would probably better to just escalate this case to the caller and get rid of any error handling here.

In the case of a panic bad things happened... It's probably not a good idea to just return a HookError and leave the application running. I guess most of the time letting the application crash with a panic that is raised on the main thread would be the better choice. 🤔

This is somewhat related to:

• Unwind safety #139

[bikeshedder]

I've opened yet another tracking issue for that:

• Remove Result<_, InteractError> from interact signature #365

[bikeshedder]

btw. right now you could rewrite it like that:

 obj
     .interact(|conn| conn.batch_execute(PRAGMA_SQL))
     .await
+    .expect("Interact error")
     .map_err(HookError::Backend)?;

I currently don't see how handling an InteractError except for panicking will ever be useful in production code. 🙈

[tibbe]

        .post_create(Hook::async_fn(|obj, _| {
            Box::pin(async move {
                obj.interact(|conn| conn.batch_execute(PRAGMA_SQL))
                    .await
                    .expect("Interact error")
                    .map_err(HookError::Backend)
            })
        }))

gives

type mismatch in function arguments
expected function signature `fn(diesel::result::Error) -> _`
   found function signature `fn(deadpool_diesel::Error) -> _`

I tried to figure out if a deadpool_diesel::Error could be wrapped up as a diesel::result::Error but couldn't see a way.

[bikeshedder]

Oh my... you're totally right. I wonder why nobody ever complained about this. I just opened a tracking issue for that:

• Add error variant to deadpool_diesel::Error for diesel errors #366

Other deadpool-* implementations don't provide their own error type but use the error of the underlying DB client library.

I'll fix that in the next release and will probably add a Box<dyn Error> too for good measure as it doesn't really need to be fully typed. It's just used to log the error and discard the connection afterwards.