I have created an wrapper, DatabaseMiddlewareFactory, that wraps a service endpoint in a connection. That connection may have a transaction started on it or it may not. I've done this for Postgresql with no problem. The wrapper does this;
- Get a connection from the global connecton pool
- Start a transaction by executing the 'BEGIN' statement.
- Add the connection as a Data<> to the request
- Execute the request
- If the result is success, execute the 'COMMIT' statement on the connection. If the result is not success, execute the 'ROLLBACK' statement on the connection.
The code looks like this:
#[actix_web::main]
async fn main() -> std::io::Result<()> {
config::load_config();
env_logger::init();
// create connection pool and pass to each request handler so it can get a connection or block while waiting
let connector = PoolConnector::from_config_lazy(CONFIG.clone())
.await
.ok()
.expect("Failed to create connection pool");
info!("Running server on {}", &CONFIG.server);
HttpServer::new(move || {
App::new()
.service(web::scope("/health")
.route("/alive", web::get().to(health::alive))
.route("/ready", web::get().to(health::ready)))
.wrap(DatabaseMiddlewareFactory::new(connector.clone()))
.configure(add_authenticated_routes)
})
.bind(&CONFIG.server)?
.run()
.await
}
This provides a lot of nice things:
- Endpoint methods get a connection rather than a connection pool. They don't have to get a connection from the pool and then clean it up. They don't have to start a transaction and then decide whether to rollback or commit.
- Read-only endpoints can get a connection without a transaction
- Mutating endpoints get a connection with a transaction
- All integration tests are wrapped in a connection and that transaction is rolled back after the test, so the database is not altered.
Put another way:
- I don't want endpoints to have to get a connection from a pool; they only need a connection.
- I don't want endpoints to have to start a transaction and then commit it; that breaks tests which need to rollback.
- I don't want to have to repeat the boilerplate code that commits on success and rollsback on failure.
- I want tests to always rollback.
Another way to handle this would be to use the TransactionManager and always hand the request a Transaction. However, that would be overkill for many of my endpoints, which are simple reads.
The code for a connection looks like this;
pub type NativeConnection = sqlx::pool::PoolConnection<Postgres>;
///
/// Wrapper for a native database connection.
/// This keeps track of nested transactions.
///
pub struct PoolConnection {
connection: NativeConnection,
transactions: i32
}
impl PoolConnection {
///
/// Borrow the underlying native connection
/// **returns:**
/// - a mutable reference to the
/// underlying native database connection
///
pub fn get(&mut self) -> &mut NativeConnection {
return &mut self.connection;
}
///
/// Determine if a transaction if running on the connection
/// **returns:**
/// - true if a transaction if running
/// - false if a transaction is not running
///
pub fn transaction(&self) -> bool {
return self.transactions > 0;
}
///
/// Start a transaction on the connection.
///
/// - **returns**:
/// - if successful, an Ok result of true
/// - if unsuccessful then an Err result with an Error
/// from the underlying database layer.
///
pub async fn begin(&mut self) -> Result<bool, Error> {
match sqlx::query("BEGIN").execute(&mut self.connection).await {
Ok(_) => {
self.transactions += 1;
Ok(true)
},
Err(e) => {
error!("Unable to begin transaction: {}", &e);
Err(e)
}
}
}
///
/// Commit the connection's transaction started with begin().
///
/// - **returns**:
/// - on success, an Ok result of true
/// if a nested transaction is running on the connection,
/// or an Ok result of false if no transaction is running
/// on the connection.
/// - on failure, an Err result with an Error
/// from the underlying database layer.
///
pub async fn commit(&mut self) -> Result<bool, Error> {
if self.transactions > 0 {
if self.transactions == 1 {
match sqlx::query("COMMIT").execute(&mut self.connection).await {
Ok(_) => {
self.transactions = 0;
Ok(false)
}
Err(e) => {
error!("Unable to commit transaction: {}", &e);
Err(e)
}
}
} else {
self.transactions -= 1;
Ok(self.transaction())
}
} else {
panic!("Attempt to commit on a connection with no transaction open.");
}
}
///
/// Rollback the connection's transaction started with begin().
///
/// - **returns**:
/// - if successful, an Ok result of true
/// if a nested transaction is running on the connection,
/// or an Ok result of false if no transaction is running
/// on the connection.
/// - if the rollback fails then an Err result with an Error
/// from the underlying database layer.
///
pub async fn rollback(&mut self) -> Result<bool, Error> {
if self.transactions > 0 {
match sqlx::query("ROLLBACK").execute(&mut self.connection).await {
Ok(_) => {
self.transactions = 0;
Ok(false)
},
Err(e) => {
error!("Unable to rollback transaction: {}", &e);
Err(e)
}
}
} else {
panic!("Attempt to rollback on a connection with no transaction open.");
}
}
}
This works well in Postgresql. I can execute the "BEGIN", "COMMIT" and "ROLLBACK" statements using sqlx::query().
The problem is that this works in Postgres, but the same code does NOT work with MySql. MySql does not want to execute 'BEGIN', 'COMMIT' or 'ROLLBACK' as part of a prepared statement.
So I change the code to us a fetch like this;
pub async fn begin(&mut self) -> Result<bool, Error> {
// match sqlx::query("BEGIN").execute(&mut self.connection).await {
match (&mut self.connection).fetch_optional("BEGIN").await {
Ok(_) => {
self.transactions += 1;
Ok(true)
},
Err(e) => {
error!("Unable to begin transaction: {}", &e);
Err(e)
}
}
}
If I change the code to use a fetch, then I get the error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RETURNING *' at line 1.
So I like this design that wraps each request in a connection, but the method I am using to start a transaction is not working with mysql. Is there something I am missing? Is there another way to do this same thing? Thank you for your help.
