Access `MySQLQueryMetadata` from `SQLDatabase` interface

[florianreinhart]

I am using MySQLKit and want to access the MySQLQueryMetadata to get lastInsertID and affectedRows when running a query. I know that I can do this using the MySQLDatabase.query(...) method with the onMetadata callback. This method requires a raw SQL string.

I want to use the SQLDatabase interface to build my queries using select(), insert(), etc. and then use all(), run(), first() to execute the queries. It seems to me that there is no way to access the metadata when using the SQLDatabase interface.

[gwynne]

The problem with exposing the query metadata through the generic SQLDatabase interface is that each of the databases for which drivers exist offer their metadata in different forms. For PostgreSQL, query metadata consists of a command, an OID, and a row count (some or all of which are sometimes not present); for MySQL it's the last insert ID and the affected rows. SQLite, meanwhile, doesn't directly provide query metadata at all but requires invoking additional functions on the database handle to get at such information as is available. Representing these differences in a generic interface is difficult. That being said, I'm very much open to suggestions; I'd love to make this data available, even if I end up only being able to do so in Fluent 5.

Meanwhile, a workaround I've used with MySQL in the past, since for some reason they refuse to add RETURNING support, is to obtain a single connection using .withSession() (unless I already have an open transaction via Fluent's API), then issue my query on that connection, followed immediately by SELECT LAST_INSERT_ID() AS lastInsertID, ROW_COUNT() AS rowCount (or its SQLKit counterpart), which yields the same information as the query metadata. Of course, this comes at the cost of a second query; it's a functioning workaround, but I don't claim it's an actual "solution" as such.