Boost C++ Libraries

Welcome to Boost.MySQL's tutorial. We will go through the simplest possible piece of code using Boost.MySQL: a program that connects to the MySQL server and issues the query SELECT "Hello World!".

To run this tutorial, you need a running MySQL server listening in localhost on port 3306 (the default one). You should have the credentials of a valid MySQL user (username and password). No further setup is needed.

This tutorial assumes you have a basic familiarity with Boost.Asio (e.g. you know what a boost::asio::io_context is).

You can find the full source code for this tutorial here.

Connection object

The first step is to create a connection object, which represents a single connection over TCP to the MySQL server. We will connect to the server using TCP over TLS, using port 3306, so we will use tcp_ssl_connection. If you're using the latest MySQL version with its default configuration, you will need to use TLS to successfully establish a connection.

A tcp_ssl_connection is an I/O object. It can be constructed from a boost::asio::io_context::executor_type and a boost::asio::ssl::context:

// The execution context, required to run I/O operations.
boost::asio::io_context ctx;

// The SSL context, required to establish TLS connections.
// The default SSL options are good enough for us at this point.
boost::asio::ssl::context ssl_ctx(boost::asio::ssl::context::tls_client);

// Represents a connection to the MySQL server.
boost::mysql::tcp_ssl_connection conn(ctx.get_executor(), ssl_ctx);

Connecting to the server

The next step is to connect to the server. We will use the function tcp_ssl_connection::connect, which accepts two parameters:

// Resolve the hostname to get a collection of endpoints
boost::asio::ip::tcp::resolver resolver(ctx.get_executor());
auto endpoints = resolver.resolve(argv[3], boost::mysql::default_port_string);

// The username, password and database to use
boost::mysql::handshake_params params(
    argv[1],                // username
    argv[2],                // password
    "boost_mysql_examples"  // database
);

// Connect to the server using the first endpoint returned by the resolver
conn.connect(*endpoints.begin(), params);

	Note
	Read-only strings, like the ones used in handshake_params's constructor, are represented as string_view's, which are similar to std::string_view's but do not require C++17 to work.

Issuing the SQL query

The next step is to issue the query to the server. We will use tcp_ssl_connection::query, which accepts a string containing a single SQL query and instructs the server to run it. It returns a results object, containing the rows returned by the query:

// Issue the SQL query to the server
const char* sql = "SELECT 'Hello world!'";
boost::mysql::results result;
conn.query(sql, result);

Obtaining the results

results is a class that holds the result of a query in memory. To obtain the value we selected, we can write:

// Print the first field in the first row
std::cout << result.rows().at(0).at(0) << std::endl;

Let's break this into steps:

Closing the connection

Once we are done with the connection, we close it by calling tcp_ssl_connection::close. Note that this will send a final quit packet to the MySQL server to notify we are closing the connection, and thus may fail.

// Close the connection
conn.close();

Final notes

This concludes our tutorial! You can now learn more about the core functionality of this library in the overview section. You can also look at more complex examples.

Full listing

Here is the full source code for the above steps:

#include <boost/mysql.hpp>

#include <boost/asio/io_context.hpp>
#include <boost/asio/ssl/context.hpp>
#include <boost/system/system_error.hpp>

#include <iostream>
#include <string>

/**
 * For this example, we will be using the 'boost_mysql_examples' database.
 * You can get this database by running db_setup.sql.
 * This example assumes you are connecting to a localhost MySQL server.
 *
 * This example uses synchronous functions and handles errors using exceptions.
 */

void main_impl(int argc, char** argv)
{
    if (argc != 4)
    {
        std::cerr << "Usage: " << argv[0] << " <username> <password> <server-hostname>\n";
        exit(1);
    }

    // The execution context, required to run I/O operations.
    boost::asio::io_context ctx;

    // The SSL context, required to establish TLS connections.
    // The default SSL options are good enough for us at this point.
    boost::asio::ssl::context ssl_ctx(boost::asio::ssl::context::tls_client);

    // Represents a connection to the MySQL server.
    boost::mysql::tcp_ssl_connection conn(ctx.get_executor(), ssl_ctx);

    // Resolve the hostname to get a collection of endpoints
    boost::asio::ip::tcp::resolver resolver(ctx.get_executor());
    auto endpoints = resolver.resolve(argv[3], boost::mysql::default_port_string);

    // The username, password and database to use
    boost::mysql::handshake_params params(
        argv[1],                // username
        argv[2],                // password
        "boost_mysql_examples"  // database
    );

    // Connect to the server using the first endpoint returned by the resolver
    conn.connect(*endpoints.begin(), params);

    // Issue the SQL query to the server
    const char* sql = "SELECT 'Hello world!'";
    boost::mysql::results result;
    conn.query(sql, result);

    // Print the first field in the first row
    std::cout << result.rows().at(0).at(0) << std::endl;

    // Close the connection
    conn.close();
}

int main(int argc, char** argv)
{
    try
    {
        main_impl(argc, argv);
    }
    catch (const boost::mysql::error_with_diagnostics& err)
    {
        // Some errors include additional diagnostics, like server-provided error messages.
        // Security note: diagnostics::server_message may contain user-supplied values (e.g. the
        // field value that caused the error) and is encoded using to the connection's encoding
        // (UTF-8 by default). Treat is as untrusted input.
        std::cerr << "Error: " << err.what() << '\n'
                  << "Server diagnostics: " << err.get_diagnostics().server_message() << std::endl;
        return 1;
    }
    catch (const std::exception& err)
    {
        std::cerr << "Error: " << err.what() << std::endl;
        return 1;
    }
}