Using Foreign Keys With Sqlite and Diesel + R2D2

Eric Semeniuc

Eric Semeniuc

May 6, 2020

Using Foreign Keys With Sqlite and Diesel + R2D2

SQLite requires each connection to enable foreign key support (due to backward compatiblity). We want to enable foreign key support in a single location only. We can do that using connection_customizer(). Let’s look at the code below.

Environment

Cargo.toml

[package]
name = "demo"
version = "1.0.0"
authors = ["Eric Semeniuc <3838856+esemeniuc@users.noreply.github.com>"]
edition = "2018"


[dependencies]
diesel = { version = "1.4.4", features = ["sqlite", "r2d2", "chrono"] }

Code

use diesel::prelude::*;
use diesel::r2d2::{Pool, ConnectionManager};
use diesel::connection::SimpleConnection;
pub fn establish_connection_temp_db() -> Pool<ConnectionManager<SqliteConnection>> {
    let manager = ConnectionManager::<SqliteConnection>::new(":memory:");

    #[derive(Debug)]
    struct SqliteForeignKey {}
    impl diesel::r2d2::CustomizeConnection::<SqliteConnection, diesel::r2d2::Error> for SqliteForeignKey {
        fn on_acquire(&self, conn: &mut SqliteConnection) -> Result<(), diesel::r2d2::Error> {
            conn.batch_execute("PRAGMA foreign_keys = ON").map_err(diesel::r2d2::Error::QueryError)
        }
    }

    Pool::builder().connection_customizer(Box::new(SqliteForeignKey {})).build(manager).expect("Failed to create pool.")
}

Documentation

https://docs.diesel.rs/diesel/r2d2/struct.Builder.html#method.connection_customizer