Add api to support high level transaction

[pardeike]

Is your feature request related to a problem? Please describe.
Having a simple Swift API to run multiple sql statements/actions in a transaction would be nice. I write micro services that interact with MySQL without any user requests and sending raw commands isn’t great.

Describe the solution you'd like
Something in the line of

conn.withTransaction { … }
//or
db.transaction { conn in … }

would be nice.

[wassup-]

I too would like to see transaction functionality implemented.

In the meantime, how would one go about implementing this theirselves, without having to resort to raw commands?

[gwynne]

Easiest option if you want a pure-SQLKit API is to just adapt the implementation provided by FluentMySQLDriver to SQLKit. This was not previously possible directly, as the withSession() API was added to SQLKit quite recently. Be warned that this example does NOT guard against nested transactions the way the Fluent API does, and will crash if invoked on an SQLDatabase that is not ultimately backed by a MySQLConnection:

extension SQLDatabase {
    /// Legacy EventLoopFuture-based wrapper
    func transaction<T>(_ closure: @escaping @Sendable (any SQLDatabase) -> EventLoopFuture<T>) -> EventLoopFuture<T> {
        self.eventLoop.makeFutureWithTask {
            try await self.transaction { try await closure($0).get() }
        }
    }

    /// Actual implementation
    func transaction<T>(_ closure: @escaping @Sendable (any SQLDatabase) async throws -> T) async throws -> T {
        try await self.withSession { conn in
            let mysql = conn as! any MySQLDatabase

            _ = try await mysql.simpleQuery("START TRANSACTION").get()
            do {
                let result = try await closure(conn)
                _ = try await mysql.simpleQuery("COMMIT").get()
               return result
           } catch {
               try? await mysql.simpleQuery("ROLLBACK").get()
           }
        }
    }
}

If you also want the nested transaction guarding and guarantee that the database on which the methods are invoked is a MySQL database underneath, it gets a little more involved (you basically have to duplicate what the _FluentMySQLDatabase type from FluentMySQLDriver does, but using the semantics of the MySQLSQLDatabase type from MySQLKit).

Of course, if you're able to import FluentKit and just want to use SQLKit, it's even simpler to just downcast an SQLDatabase to any Database, call transaction(_:) on that, and upcast the closure parameter back to any SQLDatabase.