Boost C++ Libraries

...one of the most highly regarded and expertly designed C++ library projects in the world. Herb Sutter and Andrei Alexandrescu, C++ Coding Standards

PrevUpHomeNext

Overview

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] 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

Client-side SQL formatting:

  • Securely expands queries client-side.
  • Text-based protocol.
  • Adequate for general use.
// 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
);

Prepared statements:

  • Parsed and executed in two different operations.
  • Binary protocol.
  • Adequate when running a query several times or retrieving lots of numeric data.
// 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: results

// 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: static_results

  • Parses rows into your own types
  • Requires C++20 when using Boost.Pfr, C++14 when using Boost.Describe
  • Learn more
// 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:

  • When reading a very big resultset, it may not be efficient (or even possible) to completely load it in memory. Reading rows in batches may be more adequate.
  • If rows contain very long 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] Warning

Once you start a multi-function operation with async_start_execution, the server immediately sends all rows to the client. You must read all rows before engaging in further operations. Otherwise, you will encounter packet mismatches, which can lead to bugs and vulnerabilities!

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:

  • It provides better performance. Please consult our benchmarks for more info.
  • It simplifies connection management. The connection pool will establish sessions, perform retries and apply timeouts out of the box.

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.


PrevUpHomeNext