PostgreSQL transactions

Run multiple repository operations in one transaction using request-scoped PgDbContext.

When a workflow makes two repository calls, sharing the same PgDbContext instance lets them use the same transaction. Both workflow and repository services must be Scoped for this pattern.

Prerequisites#

Complete the PostgreSQL setup. The example requires an existing users(id BIGINT, name TEXT) table with records whose IDs are 1 and 2. The code does not create the table.

From controller to transaction#

use std::sync::Arc;
use lilyrs::http_api::Injectable;
use lilyrs::http_api::{
    controller, Controller, ControllerInitError, ControllerTrait, Extensions,
    ExecutionCancellation, HttpApiError, NoContent, Service, ServiceTrait,
};
use lilyrs::postgresql::{diesel, diesel_async::RunQueryDsl, PgDbContext, PgResult};

#[derive(Injectable)]
#[service(lifetime = "Scoped")]
struct UserRepository {
    #[inject]
    context: Arc<PgDbContext>,
}
impl ServiceTrait for UserRepository {}

impl UserRepository {
    async fn rename(&self, id: i64, name: String) -> PgResult<()> {
        self.context.with_connection(move |connection, _| Box::pin(async move {
            diesel::sql_query("UPDATE users SET name = $1 WHERE id = $2")
                .bind::<diesel::sql_types::Text, _>(name)
                .bind::<diesel::sql_types::BigInt, _>(id)
                .execute(connection).await?;
            Ok(())
        }), None).await
    }
}

#[derive(Injectable)]
#[service(lifetime = "Scoped")]
struct UserService {
    #[inject]
    context: Arc<PgDbContext>,
    #[inject]
    users: Arc<UserRepository>,
}
impl ServiceTrait for UserService {}

impl UserService {
    async fn rename_pair(&self, cancellation: ExecutionCancellation) -> PgResult<()> {
        let users = self.users.clone();
        self.context.transaction(move |_| async move {
            users.rename(1, "Ada".into()).await?;
            users.rename(2, "Deniz".into()).await?;
            Ok(())
        }, Some(cancellation)).await
    }
}

#[derive(Controller)]
#[base_path("/postgresql-users")]
struct UsersController;

#[lilyrs::http_api::async_trait::async_trait]
impl ControllerTrait for UsersController {
    async fn new(_: Arc<Extensions>) -> Result<Self, ControllerInitError> { Ok(Self) }
}

#[controller]
impl UsersController {
    #[post("/rename-pair")]
    async fn rename_pair(&self, Service(service): Service<UserService>, cancellation: ExecutionCancellation)
        -> Result<NoContent, HttpApiError>
    {
        service.rename_pair(cancellation).await
            .map_err(|_| HttpApiError::ServiceUnavailable("database operation failed".into()))?;
        Ok(NoContent)
    }
}

POST /postgresql-users/rename-pair runs both updates in the same transaction and returns 204 on success. Cancellation supplied to the outer transaction takes precedence over None in the inner repository calls.

Commit, rollback, and errors#

A workflow returning Ok selects commit; Err selects rollback. Catching a query error and returning Ok does not clear a rollback-only state. Concurrent/overlapping operations on the same context produce ContextBusy; nested transactions are rejected.

The example maps database errors to a safe 503. In a real API, express domain outcomes such as not-found, conflict, and validation through separate HTTP error contracts.

Cancellation boundaries#

A PgDbContext callback can preserve application errors implementing E: From<PgError> + Send + 'static. This generic error support does not apply to every PostgreSQL API.

A COMMIT that has begun cannot be considered undone because cancellation arrives later. Dropping a PgDatabaseService::transaction future through an outer select! is not proof of completed rollback either. Distinguish the guarantees of the scoped context's owner-retained cleanup path from those of a direct connection callback.