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.



name = "demo"
version = "1.0.0"
authors = ["Eric Semeniuc <>"]
edition = "2018"

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


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:");

    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.")