Boost.MySQL 1.87 features: with_params and with_diagnostics
Oct 20, 2024Easy client-side SQL using with_params
In previous posts, we had seen how Boost.MySQL client-side SQL formatting can be used to implement a wide variety of use cases. It was originally created for complex cases, like batch inserts, but it can also be applied for simple ones. Up to Boost 1.86, to retrieve a user by ID with client-side SQL, you could write this:
void lookup(mysql::any_connection& conn, int id)
{
// Compose the query. May generate "SELECT * FROM user WHERE id = 10"
auto query = mysql::format_sql(conn.format_opts().value(), "SELECT * FROM user WHERE id = {}", id);
// Run it
mysql::static_results<user> res;
conn.execute(query, res);
// Do something with the results
}
This is still verbose, and has 3 points where an error can happen:
in format_opts, in format_sql and in execute.
Handling these 3 without exceptions is much more involved.
Boost 1.86 adds a new execution request, with_params, than can be used to simplify
most queries:
void lookup(mysql::any_connection& conn, int id)
{
// Compose the query in the client and run the request
mysql::static_results<user> res;
conn.execute(mysql::with_params("SELECT * FROM user WHERE id = {}", id), res);
// Do something with the results
}
When executing with_params, the given query string is formatted
(as per format_sql) and then sent to the client for execution.
This is much less verbose, and gathers all possible errors under the execute function,
making using exception-less code much easier. It’s also more efficient, as
the query is serialized directly to the network buffer.
All constructs available in format_sql are available when using with_params.
Exceptions with diagnostics in async functions
When using throwing sync functions, like conn.execute("SELECT 1", res),
an exception of type mysql::error_with_diagnostics is thrown on error.
This is a system::system_error, with enhanced error information
provided by the server. Implementing this is easy because the
exception is thrown directly by Boost.MySQL.
The async case is not that straightforward. Boost.Asio throws the exceptions for us, and has no way to access our diagnostics. Until Boost 1.86, we recommended the following:
asio::awaitable<void> handle_request(mysql::connection& conn)
{
mysql::results r;
mysql::diagnostics diag;
auto [ec] = co_await conn.async_execute("SELECT 1", r, diag, asio::as_tuple(asio::deferred));
mysql::throw_on_error(ec, diag);
}
This is error-prone. The new with_diagnostics completion token
can be used with async throwing schemes to simplify this task:
asio::awaitable<void> handle_request(mysql::connection& conn)
{
mysql::results r;
co_await conn.async_execute("SELECT 1", r, mysql::with_diagnostics(asio::deferred));
}
Since this is a very common case, this is actually the default completion
token for any_connection, and the above can be written as:
asio::awaitable<void> handle_request(mysql::connection& conn)
{
mysql::results r;
co_await conn.async_execute("SELECT 1", r);
}
Which makes coroutines in throwing schemes actually useful.
I’ve also developed another custom completion token to aid with tests. I’ve learnt a lot and I can tell you: developing a completion token is not for the faint-hearted, and requires a lot of testing.
Timeouts: support for asio::cancel_after, asio::cancel_at
These tokens require special support from libraries to work. Starting with Boost 1.87, Boost.MySQL supports these tokens. From now on, setting a timeout to a query is as simple as:
asio::awaitable<void> handle_request(mysql::connection& conn)
{
mysql::results r;
co_await conn.async_execute("SELECT 1", r, asio::cancel_after(std::chrono::seconds(5)));
}
Thread-safety in connection_pool
connection_pool tried to provide easy thread-safety by using special executor
semantics. Bug reports indicated that this is a feature that people use, but
that the design wasn’t entirely correct. Now that I’ve learnt more about
Asio, executors and cancellation, I’ve re-written thread-safety support
for connection_pool.
The interface is slightly different, specifying a boolean flag to enable or disable it. When disabled (the default), pools exhibit the usual Asio executor semantics, with no overhead. When enabled, a strand is internally created, and cancellation signals are appropriately wired to ensure robust thread-safety semantics. The thread sanitizer has helped a lot in detecting problems.
I’ve also removed the connection_pool::async_get_connection overloads
involving timeouts. This is now better handled by asio::cancel_after.
With these changes done, connection_pool is ready to commit
for API stability in Boost 1.87.
pydocca migration
The Boost.JSON author has kindly implemented a new version of the
docca toolchain. It’s faster, has less dependencies, and
produces much better output.
I’ve migrated Boost.MySQL to use it. In the process, I’ve learnt the tool internals, filed a lot of issues that have been promptly solved, and in general improved the quality of my reference docs.
I also developed a proof-of-concept Asciidoc generator, based on pydocca. You can see an example of the resulting docs https://anarthal.github.io/pydocca-asciidoc/mysql/boost/mysql/any_connection/connect.html:[here]. This has helped me understand the things I don’t like about the current reference doc templates, so I can provide feedback on the new ones currently under development.
Modular Boost (b2)
Like other libraries, Boost.MySQL has been adapted to use the new modular Boost infrastructure. As expected, some things broke, but we’ve managed to bring them up again.
Other contributions
I have performed some other tasks in Boost.MySQL:
- I’ve heavily refactored testing infrastructure, with increased support to detect non-conformities with the Asio universal model.
- I’ve implemented immediate executor support in
connectionandany_connection. - I’ve performed the required preparation for the 1.86 Boost release.
- I’ve fixed a number of other small issues.
I’ve managed to perform small contributions to other Boost libraries, including Boost.Process, Boost.Redis and Boost.Pfr.