Running migration during integration tests

[feliwir]

Backend

PostgreSQL

Diesel version

2.3.0

Diesel Features

"chrono", "uuid", "serde_json"

Operating System Version

Ubuntu 25.10

Third party libraries

diesel_migrations, diesel-async, c

What do you want to do?

I have an empty database that is created by my test environment. When running the integration tests i want to setup a connection pool and run all embedded migrations.

Compile time error

I get a runtime error when running cargo test:

[ERROR db] Failed to run database migrations: task 18 panicked with message "called `Result::unwrap()` on an `Err` value: DatabaseError(UniqueViolation, \"duplicate key value violates unique constraint \\\"pg_type_typname_nsp_index\\\"\")"

What code do you already have?

use diesel_async::AsyncConnection;
use diesel_async::pooled_connection::AsyncDieselConnectionManager;
use diesel_async::pooled_connection::deadpool::Pool;
use diesel_async::{AsyncPgConnection, async_connection_wrapper::AsyncConnectionWrapper};
use diesel_migrations::{EmbeddedMigrations, MigrationHarness, embed_migrations};

pub type DbConn = AsyncPgConnection;
pub type DbPool = Pool<diesel_async::AsyncPgConnection>;

pub const MIGRATIONS: EmbeddedMigrations = embed_migrations!();

pub async fn establish_connection(url: &str) -> std::io::Result<DbConn> {
    let conn = DbConn::establish(url).await.map_err(|e| {
        log::error!("Failed to establish database connection: {}", e);
        std::io::ErrorKind::Other
    })?;

    Ok(conn)
}

pub async fn run_migrations(conn: DbConn) -> std::io::Result<()> {
    let mut async_wrapper: AsyncConnectionWrapper<DbConn> = AsyncConnectionWrapper::from(conn);

    tokio::task::spawn_blocking(move || {
        async_wrapper.run_pending_migrations(MIGRATIONS).unwrap();
    })
    .await
    .map_err(|e| {
        log::error!("Failed to run database migrations: {}", e);
        std::io::ErrorKind::Other
    })?;

    Ok(())
}


pub async fn init_test_database(url: &str) -> std::io::Result<DbPool> {
    use diesel_async::pooled_connection::ManagerConfig;

    let conn = establish_connection(url).await?;
    run_migrations(conn).await?;

    let mut config = ManagerConfig::default();
    config.custom_setup = Box::new(establish_test_connection);
    let db_config = AsyncDieselConnectionManager::<DbConn>::new_with_config(url, config);
    let pool = Pool::builder(db_config).max_size(1).build().map_err(|e| {
        log::error!("Failed to create database connection pool: {}", e);
        std::io::ErrorKind::Other
    })?;

    Ok(pool)
}

My SQL table setup looks like this:

-- Your SQL goes here
CREATE TYPE user_type AS ENUM ('local', 'ldap');

CREATE TABLE "user" (
    uid UUID PRIMARY KEY,
    user_type user_type NOT NULL,
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    login TEXT UNIQUE NOT NULL,
    fullname TEXT,
    email TEXT,
    last_login TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE "user_local" (
    uid UUID PRIMARY KEY REFERENCES "user"(uid)
        ON DELETE CASCADE,
    srp_verifier BYTEA NOT NULL, -- SRP6a verifier
    srp_salt BYTEA NOT NULL      -- SRP6a salt, SHA-512
);

CREATE TABLE "user_ldap" (
    uid UUID PRIMARY KEY REFERENCES "user"(uid)
        ON DELETE CASCADE,
    dn TEXT NOT NULL,               -- Distinguished Name
    ext_uid TEXT UNIQUE NOT NULL    -- External unique ID
);

-- Automatically update the updated_at column
SELECT diesel_manage_updated_at('user');

Inside my tests i always perform init_test_database at startup (on the same URL always)

Additional details

Checklist

• I have already looked over the issue tracker and the discussion forum for similar possible closed issues.

[weiznich]

Seems like your tests are concurrently trying to apply migrations, which then fails in postgres with a race condition. There is nothing diesel can do there.

[feliwir]

Yes, the tests run concurrently. Isn't it possible to run the entire migration in a single transaction? So when 4 migrations run in parallel atleast 1 will succeed and the other 3 can "fail" gracefully?

[weiznich]

Diesel already runs each migration in a single transaction if you don't explicitly opt out of that behavior so it doesn't seem to help there.