How to connect to another database with `sqlx`?

Hi there. I'm using sqlx with Postgres as my database and want to implement a test wrapper around my database struct. The purpose of this wrapper is to create a temporary database, let user work with it, and then drop the database on the drop of the wrapper.

Part of the code:

// Create temporary database (make sure database user has
// enough priviliges).
let uuid = Uuid::new_v4().simple();
let name = format!("test_{}", uuid);
database
    .pool
    .execute(format!("CREATE DATABASE {};", &name).as_str())
    .await?;
database
    .pool
    .execute(format!(r#"\connect {}"#, &name).as_str())
    .await?;
sqlx::migrate!().run(&database.pool).await?;

The problem is, I can't execute \connect using pool.execute, it gives me this error:

Error: error returned from database: syntax error at or near "\"

Caused by:
    syntax error at or near "\"

So I wonder, is it possible? If not, how else can I implement this functionality (besides connecting to the main database, creating new test database, disconnect from main database and connect to the new database)?

I don't think you can switch the database a sqlx connection is connected to (also what would happen with the other possibly existing connections in the pool?).
What you could do instead is to create the new database and then create a new sqlx connection or pool and specify the database to connect to in the connection string (like "postgres://user:password@host/database")

The pool has maximum 1 connection since it’s exclusive to one unit test. I will try your option, although the fact that it needs 3 connections (don’t forget about dropping the database on Drop) is a bit sad

In sqlx's model, you'd create a new pool to connect to the new database, rather than reconfiguring the pool you already have.

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.