...one of the most highly
regarded and expertly designed C++ library projects in the
world.
— Herb Sutter and Andrei
Alexandrescu, C++
Coding Standards
This section briefly explains the library main classes and functions, and how to use them.
Boost.MySQL exposes sync and async functions implementing functionality involving I/O. As explained in the second tutorial, it's advisable to use async functions when possible, because they are more flexible.
Boost.MySQL supports the Boost.Asio universal async model. This means that a variety of async programming paradigms can be used with the library, including callbacks, stackful coroutines and C++20 coroutines. We will use C++20 coroutines throughout the document because they're easy to use.
![]() |
Note |
---|---|
Still not using C++20? Don't worry, you can use stackful coroutines and callbacks even in C++11. |
any_connection
is the most primitive I/O object in the library. It can establish and close
connections, run queries and manage prepared statements. Like most I/O objects,
any_connection
can be constructed
from an execution context:
// The execution context, required to run I/O operations. asio::io_context ctx; // Represents a connection to the MySQL server. mysql::any_connection conn(ctx);
any_connection
is named like
this for historic reasons: a templated connection class came before it. We
currently recommend using any_connection
for new code because it's simpler and more powerful.
The MySQL client/server protocol is session-oriented. Before anything else,
you must perform session establishment by calling any_connection::async_connect
:
// The hostname, username, password and database to use. mysql::connect_params params; params.server_address.emplace_host_and_port(server_hostname); // hostname params.username = mysql_username; params.password = mysql_password; params.database = "boost_mysql_examples"; // Connect to the server co_await conn.async_connect(params);
async_connect
performs the hostname resolution, TCP session establishment, TLS handshake
and MySQL handshake. By default, TLS is used if the server supports it.
You can configure a number of parameters here, including the database to use, TLS options and buffer sizes. See this section for more info.
Boost.MySQL also supports using UNIX-domain sockets.
To cleanly terminate a connection, use async_close
.
This sends a packet informing of the imminent close and shuts down TLS. The
connection destructor will also close the socket, so no leak occurs.
The simplest way to run a SQL query is using any_connection::async_execute
.
You can execute queries by passing a string as first parameter:
// Executes 'SELECT 1' and reads the resulting rows into memory mysql::results result; co_await conn.async_execute("SELECT 1", result);
Most queries contain user-supplied input. Never use raw string concatenation to build queries, since this is vulnerable to SQL injection. Boost.MySQL provides two interfaces to run queries with parameters:
Feature |
Code |
---|---|
|
// If employee_id is 42, executes 'SELECT first_name FROM employee WHERE id = 42' mysql::results result; co_await conn.async_execute( mysql::with_params("SELECT first_name FROM employee WHERE id = {}", employee_id), result ); |
|
// First prepare the statement. Parsing happens server-side. mysql::statement stmt = co_await conn.async_prepare_statement( "SELECT first_name FROM employee WHERE company_id = ?" ); // Now execute it. Parameter substitution happens server-side. mysql::results result; co_await conn.async_execute(stmt.bind(employee_id), result); |
By default, we recommend using with_params
because it's simpler and entails less round-trips to the server. See the comparison section for
more info.
Client-side SQL formatting can also be used to expand queries without sending them to the server.
In MySQL, a resultset refers to the results generated by a SQL query. A resultset is composed of rows, metadata and additional info, like the last insert ID.
There are two different interfaces to access resultsets. You can use the
results
class to access rows using a dynamically-typed interface, using variant-like
objects to represent values retrieved from the server. On other other hand,
static_results
is statically-typed. You specify the shape of your rows at compile-time,
and the library will parse the retrieved values for you into the types you
provide.
You can use almost every feature in this library (including text queries and prepared statements) with both interfaces.
For example, given the following table :
CREATE TABLE employee( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, ... -- other fields not relevant for us );
This is how you would access its contents using either of the interfaces:
Interface |
Description |
Example |
---|---|---|
Dynamic interface: |
|
// Passing a results to async_execute selects the dynamic interface mysql::results result; co_await conn.async_execute("SELECT id, first_name, last_name FROM employee", result); // Every employee is a collection of fields, which are variant-like objects // that represent data. We use as_string() to cast them to the appropriate type for (mysql::row_view emp : result.rows()) { std::cout << "First name: " << emp.at(1).as_string() << ", last name: " << emp.at(2).as_string() << std::endl; } |
Static interface: |
|
// This must be placed at namespace scope. // Should contain a member for each field of interest present in our query. // Declaration order doesn't need to match field order in the query. // Field names should match the ones in our query struct employee { std::int64_t id; std::string first_name; std::string last_name; };
// // This must be placed inside your function or method: // // Passing a static_results to async_execute selects the static interface mysql::static_results<mysql::pfr_by_name<employee>> result; co_await conn.async_execute("SELECT id, first_name, last_name FROM employee", result); // Query results are parsed directly into your own type for (const employee& emp : result.rows()) { std::cout << "First name: " << emp.first_name << ", last name: " << emp.last_name << std::endl; } |
Prefer using the static interface when possible.
The same APIs explained above can be used for SQL statements that don't retrieve data:
mysql::results result; co_await conn.async_execute( mysql::with_params("UPDATE employee SET first_name = {} WHERE id = {}", new_name, employee_id), result );
When performing INSERTs, you might find results::last_insert_id
handy, which retrieves the last AUTO INCREMENT ID generated by the executed
statement.
See our tutorial on UPDATEs and transactions for more info.
At any given point in time, a any_connection
may only have a single async operation outstanding. Because MySQL sessions
are stateful, and to keep the implementation simple, messages are written
to the underlying transport without any locking or queueing. If you perform
several async operations concurrently on a single connection without any
serialization, messages from different operations will be interleaved, leading
to undefined behavior.
For example, doing the following is illegal and should be avoided:
// Coroutine body // DO NOT DO THIS!!!! mysql::results result1, result2; co_await asio::experimental::make_parallel_group( conn.async_execute("SELECT 1", result1, asio::deferred), conn.async_execute("SELECT 2", result2, asio::deferred) ) .async_wait(asio::experimental::wait_for_all(), asio::deferred);
If you need to perform queries in parallel, open more connections to the server.
An operation fails if a network error happens, a protocol violation is encountered,
or the server reports an error. For instance, SQL syntax errors make async_execute
fail.
When the server reports an error, it provides a diagnostic string describing
what happened. The diagnostics
class encapsulates this message. Some library functions generate diagnostics
strings, too.
Both the sync functions in the first
tutorial and the coroutines in this exposition throw exceptions when
they fail. The exception type is error_with_diagnostics
,
which inherits from boost::system::system_error
and adds a diagnostics
object. Async functions use with_diagnostics
,
a completion token adapter, to transparently include diagnostics in exceptions.
You can avoid exceptions when using coroutines with asio::redirect_error
:
mysql::error_code ec; mysql::diagnostics diag; mysql::results result; // The provided SQL is invalid. The server will return an error. // ec will be set to a non-zero value, and diag will be populated co_await conn.async_execute("this is not SQL!", result, diag, asio::redirect_error(ec)); if (ec) { // The error code will likely report a syntax error std::cout << "Operation failed with error code: " << ec << '\n'; // diag.server_message() will contain the classic phrase // "You have an error in your SQL syntax; check the manual..." // Bear in mind that server_message() may contain user input, so treat it with caution std::cout << "Server diagnostics: " << diag.server_message() << std::endl; }
mysql::error_code
is an alias for boost::system::error_code
.
Until now, we've been using async_execute
,
which executes some SQL and reads all generated data into an in-memory object.
Some use cases may not fit in this simple pattern. For example:
TEXT
or BLOB
fields, it may
not be adequate to copy these values from the network buffer into the
results
object. A view-based
approach may be better.
For these cases, we can break the execute operation into several steps, using a multi-function operation (the term is coined by this library). This example reads an entire table in batches, which can be the case in an ETL process:
// execution_state stores state about our operation, and must be passed to all functions mysql::execution_state st; // Writes the query request and reads the server response, but not the rows co_await conn.async_start_execution("SELECT first_name, last_name FROM employee", st); // Reads all the returned rows, in batches. // st.should_read_rows() returns false once there are no more rows to read while (st.should_read_rows()) { // row_batch will be valid until conn performs the next network operation mysql::rows_view row_batch = co_await conn.async_read_some_rows(st); for (mysql::row_view emp : row_batch) { // Process the employee as required std::cout << "Name:" << emp.at(0) << " " << emp.at(1) << std::endl; } }
![]() |
Warning |
---|---|
Once you start a multi-function operation with |
Multi-function operations are powerful but complex. Only use them when there is a strong reason to do so. Multi-function operations also work with the static interface. Please refer to this section for more information on these operations.
Connection pooling is a technique where several long-lived connections are re-used for independent logical operations. When compared to establishing individual connections, it has the following benefits:
This is how you can create a pool of connections:
// pool_params contains configuration for the pool. // You must specify enough information to establish a connection, // including the server address and credentials. // You can configure a lot of other things, like pool limits boost::mysql::pool_params params; params.server_address.emplace_host_and_port(server_hostname); params.username = mysql_username; params.password = mysql_password; params.database = "boost_mysql_examples"; // The I/O context, required by all I/O operations boost::asio::io_context ctx; // Construct a pool of connections. The context will be used internally // to create the connections and other I/O objects boost::mysql::connection_pool pool(ctx, std::move(params)); // You need to call async_run on the pool before doing anything useful with it. // async_run creates connections and keeps them healthy. It must be called // only once per pool. // The detached completion token means that we don't want to be notified when // the operation ends. It's similar to a no-op callback. pool.async_run(boost::asio::detached);
connection_pool::async_run
must be called exactly once per pool. This function takes care of actually
keeping connections healthy.
To retrieve a connection, use connection_pool::async_get_connection
:
// Use connection pools for functions that will be called // repeatedly during the application lifetime. // An HTTP server handler function is a good candidate. boost::asio::awaitable<std::int64_t> get_num_employees(boost::mysql::connection_pool& pool) { // Get a fresh connection from the pool. // pooled_connection is a proxy to an any_connection object. boost::mysql::pooled_connection conn = co_await pool.async_get_connection(); // Use pooled_connection::operator-> to access the underlying any_connection. // Let's use the connection results result; co_await conn->async_execute("SELECT COUNT(*) FROM employee", result); co_return result.rows().at(0).at(0).as_int64(); // When conn is destroyed, the connection is returned to the pool }
For more info, see this section.