I've searched and experimented, and can't find any way to use Postgres transitions with this library:
Are transactions for Postgres-Kit possible, does anyone know?
I see the higher-level Fluent library provides transactions, so I expected this lower-level library to provide a mechanism.
I can't see mention of transactions in the doco, in the API, in the source code's unit-tests. If I attempt to provide transactions in raw SQL queries, I receive this error:
"server: cannot insert multiple commands into a prepared statement (exec_parse_message)"
I think I've found my own answer, looking into the Fluent Postgres Driver code, we can isolate a single connection then call BEGIN / COMMIT or ROLLBACK separately
req.postgres.withConnection { conn in
conn.simpleQuery("BEGIN")
... insert or update
conn.simpleQuery("COMMIT") or conn.simpleQuery("ROLLBACK")
This was super helpful ... and, as hoped for and expected, it also works with MySQLKit. Just to add a bit of clarity (and hopefully help others), my code looks like this:
let mysql = pool.database(logger: self.logger)
_ = try mysql.withConnection{ connection in
_ = connection.simpleQuery("START TRANSACTION;")
_ = connection.simpleQuery("SET @registry_name = 'Testy McTestList';")
_ = connection.simpleQuery("SET @registry_author = 'richwolf';")
... etc. INSERTs, UPDATEs, whatevs ... and finally ...
return connection.simpleQuery("COMMIT;")
}.wait()
Basically, you have to return "some" kind of EventFuture or the compiler isn't happy. I mean, I wish there was a simple convenience method a la query() or simpleQuery() (transaction(), say)…but I'm okay with this way of doing it.