Boost.MySQL  0.0.1
A Boost.Asio-based client for MySQL
User manual

Boost.MySQL is a C++11 client for the MySQL database server, based on Boost.Asio.

Why another MySQL C++ client?

  • It is fully compatible with Boost.Asio and integrates well with any other library in the Boost.Asio ecosystem (like Boost.Beast).
  • It supports Boost.Asio's universal asynchronous model, which means you can go asyncrhonous using callbacks, futures or coroutines (including C++20 coroutines).
  • It is written in modern C++ (C++11) and takes advantage of the latest language features and standard library additions.
  • It is header only.

Building

As this is a header-only library, you do not need to build it. However, as it has a bunch of dependencies, we suggest you use CMake to pull them in as you build your application.

Download Boost.MySQL and make it available to your CMake script (we suggest you use CMake's FetchContent module to do this), and then call add_subdirectory() on the Boost.MySQL root directory. This will look for all the required dependencies.

Finally, link your target against the Boost::mysql interface library, and you will be done!

Requirements

  • C++11 capable compiler (tested with gcc 7.4, clang 7.0, Apple clang 11.0, MSVC 19.25).
  • Boost 1.72 or higher.
  • OpenSSL.
  • CMake 3.13.0 or higher, if using CMake to build against the library (this is the preferred way).
  • Tested with MySQL v5.7.29, MySQL v8.0.19, MariaDB v10.3 and MariaDB v10.5.

Features

  • Text queries (execution of text SQL queries and data retrieval). MySQL refers to this as the "text protocol", as all information is passed using text (as opposed to prepared statements, see below).
  • Prepared statements. MySQL refers to this as the "binary protocol", as the result of executing a prepared statement is sent in binary format rather than in text.
  • Authentication methods (authentication plugins): mysql_native_password and caching_sha2_password. These are the default methods in MySQL 5 and MySQL 8, respectively.
  • Encrypted connections (TLS).

Tutorial

This tutorial shows an example of how to use the Boost.MySQL library. It employs synchronous functions with exceptions as error handling strategy, which is the simplest.

Tutorial code

//
// Copyright (c) 2019-2020 Ruben Perez Hidalgo (rubenperez038 at gmail dot com)
//
// Distributed under the Boost Software License, Version 1.0. (See accompanying
// file LICENSE_1_0.txt or copy at http://www.boost.org/LICENSE_1_0.txt)
//
#include "boost/mysql/mysql.hpp"
#include <boost/asio/io_context.hpp>
#include <boost/system/system_error.hpp>
#include <iostream>
/**
* 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.
*/
#define ASSERT(expr) \
if (!(expr)) \
{ \
std::cerr << "Assertion failed: " #expr << std::endl; \
exit(1); \
}
/**
* Prints an employee to std::cout. An employee here is a mysql::row,
* which represents a row returned by a SQL query. You can access the values in
* the row using row::values(), which returns a vector of mysql::value.
*
* mysql::value represents a single value returned by MySQL, and is defined to be
* a std::variant of all the types MySQL supports.
*
* row::values() has the same number of elements as fields are in the SQL query,
* and in the same order.
*/
void print_employee(const boost::mysql::row& employee)
{
std::cout << "Employee '"
<< employee.values()[0] << " " // first_name (type boost::string_view)
<< employee.values()[1] << "' earns " // last_name (type boost::string_view)
<< employee.values()[2] << " dollars yearly\n"; // salary (type double)
}
void main_impl(int argc, char** argv)
{
if (argc != 3)
{
std::cerr << "Usage: " << argv[0] << " <username> <password>\n";
exit(1);
}
/**
* Connection parameters that tell us where and how to connect to the MySQL server.
* There are two types of parameters:
* - TCP-level connection parameters, identifying the host and port to connect to.
* - MySQL level parameters: database credentials and schema to use.
*/
boost::asio::ip::tcp::endpoint ep (
boost::asio::ip::address_v4::loopback(), // host
);
argv[1], // username
argv[2], // password
"boost_mysql_examples" // database to use; leave empty or omit the parameter for no database
);
// Note: by default, SSL will be used if the server supports it.
// connection_params accepts an optional ssl_options argument
// determining whether to use SSL or not. See ssl_options and ssl_mode
// documentation for further details on SSL.
boost::asio::io_context ctx;
/**
* Represents a single connection over TCP to a MySQL server.
* Before being able to use it, you have to connect to the server by:
* - Establishing the TCP-level session.
* - Authenticating to the MySQL server.
* connection::connect takes care of both.
*/
conn.connect(ep, params);
/**
* To issue a SQL query to the database server, use tcp_connection::query, which takes
* the SQL to be executed as parameter and returns a resultset object.
*
* Resultset objects represent the result of a query, in tabular format.
* They hold metadata describing the fields the resultset holds (in this case, first_name,
* last_name and salary). To get the actual data, use fetch_one, fetch_many or fetch_all.
* We will use fetch_all, which returns all the received rows as a std::vector.
*
* We will get all employees working for 'High Growth Startup'.
*/
const char* sql = "SELECT first_name, last_name, salary FROM employee WHERE company_id = 'HGS'";
boost::mysql::tcp_resultset result = conn.query(sql);
// Get all the rows in the resultset
std::vector<boost::mysql::owning_row> employees = result.fetch_all();
for (const auto& employee: employees)
{
print_employee(employee);
}
// We can issue any SQL statement, not only SELECTs. In this case, the returned
// resultset will have no fields and no rows
sql = "UPDATE employee SET salary = 10000 WHERE first_name = 'Underpaid'";
result = conn.query(sql);
ASSERT(result.fields().size() == 0); // fields() returns a vector containing metadata about the query fields
// Check we have updated our poor intern salary
result = conn.query("SELECT salary FROM employee WHERE first_name = 'Underpaid'");
auto rows = result.fetch_all();
ASSERT(rows.size() == 1);
double salary = rows[0].values()[0].get<double>();
ASSERT(salary == 10000);
// Close the connection. This notifies the MySQL we want to log out
// and then closes the underlying socket. This operation implies a network
// transfer and thus can fail
conn.close();
}
int main(int argc, char** argv)
{
try
{
main_impl(argc, argv);
}
catch (const boost::system::system_error& err)
{
std::cerr << "Error: " << err.what() << ", error code: " << err.code() << std::endl;
return 1;
}
catch (const std::exception& err)
{
std::cerr << "Error: " << err.what() << std::endl;
return 1;
}
}

Database setup

You can run the db_setup.sql file included with the example to set it up, or copy and paste the following commands:

--
-- Copyright (c) 2019-2020 Ruben Perez Hidalgo (rubenperez038 at gmail dot com)
--
-- Distributed under the Boost Software License, Version 1.0. (See accompanying
-- file LICENSE_1_0.txt or copy at http://www.boost.org/LICENSE_1_0.txt)
--
-- Connection system variables
SET NAMES utf8;
-- Database
DROP DATABASE IF EXISTS boost_mysql_examples;
CREATE DATABASE boost_mysql_examples;
USE boost_mysql_examples;
-- Tables
CREATE TABLE company(
id CHAR(10) NOT NULL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE employee(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
salary DOUBLE,
company_id CHAR(10) NOT NULL,
FOREIGN KEY (company_id) REFERENCES company(id)
);
INSERT INTO company (name, id) VALUES
("Award Winning Company, Inc.", "AWC"),
("Sector Global Leader Plc", "SGL"),
("High Growth Startup, Ltd", "HGS")
;
INSERT INTO employee (first_name, last_name, salary, company_id) VALUES
("Efficient", "Developer", 30000, "AWC"),
("Lazy", "Manager", 80000, "AWC"),
("Good", "Team Player", 35000, "HGS"),
("Enormous", "Slacker", 45000, "SGL"),
("Coffee", "Drinker", 30000, "HGS"),
("Underpaid", "Intern", 15000, "AWC")
;
-- User
DROP USER IF EXISTS 'example_user'@'%';
CREATE USER 'example_user'@'%' IDENTIFIED WITH 'mysql_native_password';
ALTER USER 'example_user'@'%' IDENTIFIED BY 'example_password';
GRANT ALL PRIVILEGES ON boost_mysql_examples.* TO 'example_user'@'%';
FLUSH PRIVILEGES;

Examples

Query, asynchronous with callbacks

This example demonstrates issuing text queries to the server and reading results, using callbacks.

//
// Copyright (c) 2019-2020 Ruben Perez Hidalgo (rubenperez038 at gmail dot com)
//
// Distributed under the Boost Software License, Version 1.0. (See accompanying
// file LICENSE_1_0.txt or copy at http://www.boost.org/LICENSE_1_0.txt)
//
#include "boost/mysql/mysql.hpp"
#include <boost/asio/io_context.hpp>
#include <boost/system/system_error.hpp>
#include <boost/asio/coroutine.hpp>
#include <boost/asio/yield.hpp>
#include <iostream>
/**
* 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 asynchronous functions with callbacks.
*
* This example assumes you are already familiar with the basic concepts
* of mysql-asio (tcp_connection, resultset, rows, values). If you are not,
* please have a look to the query_sync.cpp example.
*
* In this library, all asynchronous operations follow Boost.Asio universal
* asynchronous models, and thus may be used with callbacks, Boost stackful
* coroutines, C++20 coroutines or futures.
* The handler signature is always one of:
* - void(error_code): for operations that do not have a "return type" (e.g. handshake)
* - void(error_code, T): for operations that have a "return type" (e.g. query, for which
* T = resultset<StreamType>).
*
* There are two overloads for all asynchronous operations. One accepts an output error_info&
* parameter right before the completion token. This error_info will be populated
* in case of error if any extra information provided by the server. The other overload
* does not have this error_info& parameter.
*
* Design note: handler signatures in Boost.Asio should have two parameters, at
* most, and the first one should be an error_code - otherwise some of the asynchronous
* features (e.g. coroutines) won't work. This is why error_info is not part of any
* of the handler signatures.
*/
#define ASSERT(expr) \
if (!(expr)) \
{ \
std::cerr << "Assertion failed: " #expr << std::endl; \
exit(1); \
}
void print_employee(const boost::mysql::row& employee)
{
std::cout << "Employee '"
<< employee.values()[0] << " " // first_name (type boost::string_view)
<< employee.values()[1] << "' earns " // last_name (type boost::string_view)
<< employee.values()[2] << " dollars yearly\n"; // salary (type double)
}
void die_on_error(
const error_code& err,
)
{
if (err)
{
std::cerr << "Error: " << err << ": " << info.message() << std::endl;
exit(1);
}
}
class application
{
boost::asio::ip::tcp::endpoint ep; // Physical endpoint to connect to
boost::mysql::connection_params conn_params; // MySQL credentials and other connection config
boost::asio::io_context ctx; // boost::asio context
boost::mysql::tcp_connection connection; // Represents the connection to the MySQL server
boost::mysql::tcp_resultset resultset; // A result from a query
boost::mysql::error_info additional_info; // Will be populated with additional information about any errors
public:
application(const char* username, const char* password) :
ep (boost::asio::ip::address_v4::loopback(), boost::mysql::default_port),
conn_params(username, password, "boost_mysql_examples"),
connection(ctx)
{
}
void start() { connect(); }
void connect()
{
connection.async_connect(ep, conn_params, additional_info, [this](error_code err) {
die_on_error(err, additional_info);
query_employees();
});
}
void query_employees()
{
const char* sql = "SELECT first_name, last_name, salary FROM employee WHERE company_id = 'HGS'";
connection.async_query(sql, additional_info, [this](error_code err, tcp_resultset&& result) {
die_on_error(err, additional_info);
resultset = std::move(result);
resultset.async_fetch_all(additional_info, [this](error_code err, const std::vector<owning_row>& rows) {
die_on_error(err, additional_info);
for (const auto& employee: rows)
{
print_employee(employee);
}
update_slacker();
});
});
}
void update_slacker()
{
const char* sql = "UPDATE employee SET salary = 15000 WHERE last_name = 'Slacker'";
connection.async_query(sql, additional_info,
[this](error_code err, tcp_resultset&& result) {
die_on_error(err, additional_info);
ASSERT(result.fields().size() == 0);
query_intern();
});
}
void query_intern()
{
const char* sql = "SELECT salary FROM employee WHERE last_name = 'Slacker'";
connection.async_query(sql, additional_info, [this](error_code err, tcp_resultset&& result) {
die_on_error(err, additional_info);
resultset = std::move(result);
resultset.async_fetch_all(additional_info, [this](error_code err, const std::vector<owning_row>& rows) {
die_on_error(err, additional_info);
ASSERT(rows.size() == 1);
auto salary = rows[0].values()[0].get<double>();
ASSERT(salary == 15000);
close();
});
});
}
void close()
{
// Notify the MySQL server we want to quit and then close the socket
connection.async_close(additional_info, [this](error_code err) {
die_on_error(err, additional_info);
});
}
void run() { ctx.run(); }
};
void main_impl(int argc, char** argv)
{
if (argc != 3)
{
std::cerr << "Usage: " << argv[0] << " <username> <password>\n";
exit(1);
}
application app (argv[1], argv[2]);
app.start(); // starts the async chain
app.run(); // run the asio::io_context until the async chain finishes
}
int main(int argc, char** argv)
{
try
{
main_impl(argc, argv);
}
catch (const boost::system::system_error& err)
{
std::cerr << "Error: " << err.what() << ", error code: " << err.code() << std::endl;
return 1;
}
catch (const std::exception& err)
{
std::cerr << "Error: " << err.what() << std::endl;
return 1;
}
}

Query, asynchronous with futures

This example demonstrates issuing text queries to the server and reading results, using futures.

//
// Copyright (c) 2019-2020 Ruben Perez Hidalgo (rubenperez038 at gmail dot com)
//
// Distributed under the Boost Software License, Version 1.0. (See accompanying
// file LICENSE_1_0.txt or copy at http://www.boost.org/LICENSE_1_0.txt)
//
#include "boost/mysql/mysql.hpp"
#include <boost/asio/io_context.hpp>
#include <boost/system/system_error.hpp>
#include <boost/asio/use_future.hpp>
#include <iostream>
#include <thread>
using boost::asio::use_future;
/**
* 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 asynchronous functions with futures.
*
* This example assumes you are already familiar with the basic concepts
* of mysql-asio (tcp_connection, resultset, rows, values). If you are not,
* please have a look to the query_sync.cpp example.
*
* In this library, all asynchronous operations follow Boost.Asio universal
* asynchronous models, and thus may be used with callbacks, Boost stackful
* coroutines, C++20 coroutines or futures.
* The handler signature is always one of:
* - void(error_code): for operations that do not have a "return type" (e.g. handshake)
* - void(error_code, T): for operations that have a "return type" (e.g. query, for which
* T = resultset<StreamType>).
*
* There are two overloads for all asynchronous operations. One accepts an output error_info&
* parameter right before the completion token. This error_info will be populated
* in case of error if any extra information provided by the server. The other overload
* does not have this error_info& parameter.
*
* Design note: handler signatures in Boost.Asio should have two parameters, at
* most, and the first one should be an error_code - otherwise some of the asynchronous
* features (e.g. coroutines) won't work. This is why error_info is not part of any
* of the handler signatures.
*/
void print_employee(const boost::mysql::row& employee)
{
std::cout << "Employee '"
<< employee.values()[0] << " " // first_name (type boost::string_view)
<< employee.values()[1] << "' earns " // last_name (type boost::string_view)
<< employee.values()[2] << " dollars yearly\n"; // salary (type double)
}
/**
* A boost::asio::io_context plus a thread that calls context.run().
* We encapsulate this here to ensure correct shutdown even in case of
* error (exception), when we should first reset the work guard, to
* stop the io_context, and then join the thread. Failing to do so
* may cause your application to not stop (if the work guard is not
* reset) or to terminate badly (if the thread is not joined).
*/
class application
{
boost::asio::io_context ctx_;
boost::asio::executor_work_guard<boost::asio::io_context::executor_type> guard_;
std::thread runner_;
public:
application(): guard_(ctx_.get_executor()), runner_([this] { ctx_.run(); }) {}
application(const application&) = delete;
application(application&&) = delete;
application& operator=(const application&) = delete;
application& operator=(application&&) = delete;
~application()
{
guard_.reset();
runner_.join();
}
boost::asio::io_context& context() { return ctx_; }
};
void main_impl(int argc, char** argv)
{
if (argc != 3)
{
std::cerr << "Usage: " << argv[0] << " <username> <password>\n";
exit(1);
}
// Context and connections
application app; // boost::asio::io_context and a thread that calls run()
boost::mysql::tcp_connection conn (app.context());
boost::asio::ip::tcp::endpoint ep (
boost::asio::ip::address_v4::loopback(), // host
);
argv[1], // username
argv[2], // password
"boost_mysql_examples" // database to use; leave empty or omit the parameter for no database
);
/**
* Perform the TCP connect and MySQL handshake.
* Calling async_connect triggers the
* operation, and calling future::get() blocks the current thread until
* it completes. get() will throw an exception if the operation fails.
*/
std::future<void> fut = conn.async_connect(ep, params, use_future);
fut.get();
// Issue the query to the server
const char* sql = "SELECT first_name, last_name, salary FROM employee WHERE company_id = 'HGS'";
std::future<boost::mysql::tcp_resultset> resultset_fut = conn.async_query(sql, use_future);
boost::mysql::tcp_resultset result = resultset_fut.get();
/**
* Get all rows in the resultset. We will employ resultset::async_fetch_one(),
* which returns a single row at every call. The returned row is a pointer
* to memory owned by the resultset, and is re-used for each row. Thus, returned
* rows remain valid until the next call to async_fetch_one(). When no more
* rows are available, async_fetch_one returns nullptr.
*/
while (const boost::mysql::row* current_row = result.async_fetch_one(use_future).get())
{
print_employee(*current_row);
}
// Notify the MySQL server we want to quit, then close the underlying connection.
conn.async_close(use_future).get();
// application dtor. stops io_context and then joins the thread
}
int main(int argc, char** argv)
{
try
{
main_impl(argc, argv);
}
catch (const boost::system::system_error& err)
{
std::cerr << "Error: " << err.what() << ", error code: " << err.code() << std::endl;
return 1;
}
catch (const std::exception& err)
{
std::cerr << "Error: " << err.what() << std::endl;
return 1;
}
}

Query, asynchronous with coroutines

This example demonstrates issuing text queries to the server and reading results, using coroutines.

//
// Copyright (c) 2019-2020 Ruben Perez Hidalgo (rubenperez038 at gmail dot com)
//
// Distributed under the Boost Software License, Version 1.0. (See accompanying
// file LICENSE_1_0.txt or copy at http://www.boost.org/LICENSE_1_0.txt)
//
#include "boost/mysql/mysql.hpp"
#include <boost/asio/io_context.hpp>
#include <boost/system/system_error.hpp>
#include <boost/asio/spawn.hpp>
#include <iostream>
/**
* 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 asynchronous functions with coroutines.
*
* This example assumes you are already familiar with the basic concepts
* of mysql-asio (tcp_connection, resultset, rows, values). If you are not,
* please have a look to the query_sync.cpp example.
*
* In this library, all asynchronous operations follow Boost.Asio universal
* asynchronous models, and thus may be used with callbacks, Boost stackful
* coroutines, C++20 coroutines or futures.
* The handler signature is always one of:
* - void(error_code): for operations that do not have a "return type" (e.g. handshake)
* - void(error_code, T): for operations that have a "return type" (e.g. query, for which
* T = resultset<StreamType>).
*
* There are two overloads for all asynchronous operations. One accepts an output error_info&
* parameter right before the completion token. This error_info will be populated
* in case of error if any extra information provided by the server. The other overload
* does not have this error_info& parameter.
*
* Design note: handler signatures in Boost.Asio should have two parameters, at
* most, and the first one should be an error_code - otherwise some of the asynchronous
* features (e.g. coroutines) won't work. This is why error_info is not part of any
* of the handler signatures.
*/
void print_employee(const boost::mysql::row& employee)
{
std::cout << "Employee '"
<< employee.values()[0] << " " // first_name (type boost::string_view)
<< employee.values()[1] << "' earns " // last_name (type boost::string_view)
<< employee.values()[2] << " dollars yearly\n"; // salary (type double)
}
// Throws an exception if an operation failed
void check_error(
const error_code& err,
const error_info& info = {}
)
{
if (err)
{
throw boost::system::system_error(err, info.message());
}
}
void main_impl(int argc, char** argv)
{
if (argc != 3)
{
std::cerr << "Usage: " << argv[0] << " <username> <password>\n";
exit(1);
}
// Context and connections
boost::asio::io_context ctx;
boost::asio::ip::tcp::endpoint ep (
boost::asio::ip::address_v4::loopback(), // host
);
argv[1], // username
argv[2], // password
"boost_mysql_examples" // database to use; leave empty or omit the parameter for no database
);
/**
* The entry point. We spawn a stackful coroutine using boost::asio::spawn
* (see https://www.boost.org/doc/libs/1_72_0/doc/html/boost_asio/reference/spawn.html).
*
* The coroutine will actually start running when we call io_context::run().
* It will suspend every time we call one of the asynchronous functions, saving
* all information it needs for resuming. When the asynchronous operation completes,
* the coroutine will resume in the point it was left.
*
* The return type of a coroutine is the second argument to the handler signature
* for the asynchronous operation. For example, connection::query has a handler
* signature of void(error_code, resultset<Stream>), so the coroutine return
* type is resultset<Stream>.
*
*/
boost::asio::spawn(ctx.get_executor(), [&conn, ep, params](boost::asio::yield_context yield) {
// This error_code and error_info will be filled if an
// operation fails. We will check them for every operation we perform.
boost::mysql::error_info additional_info;
// Connect to server
conn.async_connect(ep, params, additional_info, yield[ec]);
check_error(ec);
// Issue the query to the server
const char* sql = "SELECT first_name, last_name, salary FROM employee WHERE company_id = 'HGS'";
boost::mysql::tcp_resultset result = conn.async_query(sql, additional_info, yield[ec]);
check_error(ec, additional_info);
/**
* Get all rows in the resultset. We will employ resultset::async_fetch_one(),
* which returns a single row at every call. The returned row is a pointer
* to memory owned by the resultset, and is re-used for each row. Thus, returned
* rows remain valid until the next call to async_fetch_one(). When no more
* rows are available, async_fetch_one returns nullptr.
*/
while (true)
{
const boost::mysql::row* row = result.async_fetch_one(additional_info, yield[ec]);
check_error(ec, additional_info);
if (!row) break; // No more rows available
print_employee(*row);
}
// Notify the MySQL server we want to quit, then close the underlying connection.
conn.async_close(additional_info, yield[ec]);
check_error(ec, additional_info);
});
// Don't forget to call run()! Otherwise, your program
// will not spawn the coroutine and will do nothing.
ctx.run();
}
int main(int argc, char** argv)
{
try
{
main_impl(argc, argv);
}
catch (const boost::system::system_error& err)
{
std::cerr << "Error: " << err.what() << ", error code: " << err.code() << std::endl;
return 1;
}
catch (const std::exception& err)
{
std::cerr << "Error: " << err.what() << std::endl;
return 1;
}
}

Query, asynchronous with C++20 coroutines

This example demonstrates issuing text queries to the server and reading results, using C++20 coroutines (boost::asio::use_awaitable).

//
// Copyright (c) 2019-2020 Ruben Perez Hidalgo (rubenperez038 at gmail dot com)
//
// Distributed under the Boost Software License, Version 1.0. (See accompanying
// file LICENSE_1_0.txt or copy at http://www.boost.org/LICENSE_1_0.txt)
//
#include "boost/mysql/mysql.hpp"
#include <boost/asio/io_context.hpp>
#include <boost/system/system_error.hpp>
#include <boost/asio/co_spawn.hpp>
#include <boost/asio/use_awaitable.hpp>
#include <boost/asio/awaitable.hpp>
#include <boost/asio/detached.hpp>
#include <boost/asio/use_future.hpp>
#include <iostream>
#ifdef BOOST_ASIO_HAS_CO_AWAIT
/**
* 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 asynchronous functions with C++20 coroutines
* (boost::asio::use_awaitable). It also demonstrates using
* defaulted completion tokens (so that you do not have to write
* boost::asio::use_awaitable in every async op).
*
* This example assumes you are already familiar with the basic concepts
* of mysql-asio (tcp_connection, resultset, rows, values). If you are not,
* please have a look to the query_sync.cpp example.
*
* In this library, all asynchronous operations follow Boost.Asio universal
* asynchronous models, and thus may be used with callbacks, Boost stackful
* coroutines, C++20 coroutines or futures.
* The handler signature is always one of:
* - void(error_code): for operations that do not have a "return type" (e.g. handshake)
* - void(error_code, T): for operations that have a "return type" (e.g. query, for which
* T = resultset<StreamType>).
*
* There are two overloads for all asynchronous operations. One accepts an output error_info&
* parameter right before the completion token. This error_info will be populated
* in case of error if any extra information provided by the server. The other overload
* does not have this error_info& parameter.
*
* Design note: handler signatures in Boost.Asio should have two parameters, at
* most, and the first one should be an error_code - otherwise some of the asynchronous
* features (e.g. coroutines) won't work. This is why error_info is not part of any
* of the handler signatures.
*/
void print_employee(const boost::mysql::row& employee)
{
std::cout << "Employee '"
<< employee.values()[0] << " " // first_name (type boost::string_view)
<< employee.values()[1] << "' earns " // last_name (type boost::string_view)
<< employee.values()[2] << " dollars yearly\n"; // salary (type double)
}
/**
* A boost::asio::io_context plus a thread that calls context.run().
* We encapsulate this here to ensure correct shutdown even in case of
* error (exception), when we should first reset the work guard, to
* stop the io_context, and then join the thread. Failing to do so
* may cause your application to not stop (if the work guard is not
* reset) or to terminate badly (if the thread is not joined).
*/
class application
{
boost::asio::io_context ctx_;
boost::asio::executor_work_guard<boost::asio::io_context::executor_type> guard_;
std::thread runner_;
public:
application(): guard_(ctx_.get_executor()), runner_([this] { ctx_.run(); }) {}
application(const application&) = delete;
application(application&&) = delete;
application& operator=(const application&) = delete;
application& operator=(application&&) = delete;
~application()
{
guard_.reset();
runner_.join();
}
boost::asio::io_context& context() { return ctx_; }
};
/**
* Default completion tokens are associated to executors.
* boost::mysql::socket_connection objects use the same executor
* as the underlying stream (socket). boost::mysql::tcp_connection
* objects use boost::asio::ip::tcp::socket, which use the polymorphic
* boost::asio::executor as executor type, which does not have a default
* completion token associated.
*
* We will use the io_context's executor as base executor. We will then
* use use_awaitable_t::executor_with_default on this type, which creates
* a new executor type acting the same as the base executor, but having
* use_awaitable_t as default completion token type.
*
* We will then obtain the connection type to use by rebinding
* the usual tcp_connection to our new executor type, coro_executor_type.
* This is equivalent to using a boost::mysql::connection<socket_type>,
* where socket_type is a TCP socket that uses our coro_executor_type.
*
* The reward for this hard work is not having to pass the completion
* token (boost::asio::use_awaitable) to any of the asynchronous operations
* initiated by this connection or any of the I/O objects (e.g. resultsets)
* associated to them.
*/
using base_executor_type = boost::asio::io_context::executor_type;
using coro_executor_type = boost::asio::use_awaitable_t<
base_executor_type>::executor_with_default<base_executor_type>;
/**
* Our coroutine. It must have a return type of boost::asio::awaitable<T>.
* Our coroutine does not communicate any result back, so T=void.
* Remember that you do not have to explicitly create any awaitable<void> in
* your function. Instead, the return type is fed to std::coroutine_traits
* to determine the semantics of the coroutine, like the promise type.
* Asio already takes care of all this for us.
*
* The coroutine will suspend every time we call one of the asynchronous functions, saving
* all information it needs for resuming. When the asynchronous operation completes,
* the coroutine will resume in the point it was left.
*
* The return type of an asynchronous operation that uses boost::asio::use_awaitable
* as completion token is a boost::asio::awaitable<T>, where T
* is the second argument to the handler signature for the asynchronous operation.
* For example, connection::query has a handler
* signature of void(error_code, resultset<Stream>), so async_query will return
* a boost::asio::awaitable<boost::mysql::resultset<Stream>>. The return type of
* calling co_await on such a expression would be a boost::mysql::resultset<Stream>.
* If any of the asynchronous operations fail, an exception will be raised
* within the coroutine.
*/
boost::asio::awaitable<void, base_executor_type> start_query(
const boost::asio::io_context::executor_type& ex,
const boost::asio::ip::tcp::endpoint& ep,
)
{
// Create the connection. We do not use the raw tcp_connection type
// alias to default the completion token; see above.
connection_type conn (ex);
// Connect to server. Note: we didn't have to pass boost::asio::use_awaitable:
// go default completion tokens brrrrr
co_await conn.async_connect(ep, params);
/**
* Issue the query to the server. Note that async_query returns a
* boost::asio::awaitable<boost::mysql::resultset<socket_type>, base_executor_type>,
* where socket_type is a TCP socket bound to coro_executor_type.
* Calling co_await on this expression will yield a boost::mysql::resultset<socket_type>.
* Note that this is not the same type as a boost::mysql::tcp_resultset because we
* used a custom socket type.
*/
const char* sql = "SELECT first_name, last_name, salary FROM employee WHERE company_id = 'HGS'";
auto result = co_await conn.async_query(sql);
/**
* Get all rows in the resultset. We will employ resultset::async_fetch_one(),
* which returns a single row at every call. The returned row is a pointer
* to memory owned by the resultset, and is re-used for each row. Thus, returned
* rows remain valid until the next call to async_fetch_one(). When no more
* rows are available, async_fetch_one returns nullptr.
*/
while (const boost::mysql::row* row = co_await result.async_fetch_one())
{
print_employee(*row);
}
// Notify the MySQL server we want to quit, then close the underlying connection.
co_await conn.async_close();
}
void main_impl(int argc, char** argv)
{
if (argc != 3)
{
std::cerr << "Usage: " << argv[0] << " <username> <password>\n";
exit(1);
}
// io_context plus runner thread
application app;
// Connection parameters
boost::asio::ip::tcp::endpoint ep (
boost::asio::ip::address_v4::loopback(), // host
);
argv[1], // username
argv[2], // password
"boost_mysql_examples" // database to use; leave empty or omit the parameter for no database
);
/**
* The entry point. We spawn a thread of execution to run our
* coroutine using boost::asio::co_spawn. We pass in a function returning
* a boost::asio::awaitable<void>, as required.
*
* We pass in a callback to co_spawn. It will be called when
* the coroutine completes, with an exception_ptr if there was any error
* during execution. We use a promise to wait for the coroutine completion
* and transmit any raised exception.
*/
auto executor = app.context().get_executor();
std::promise<void> prom;
boost::asio::co_spawn(executor, [executor, ep, params] {
return start_query(executor, ep, params);
}, [&prom](std::exception_ptr err) {
prom.set_exception(std::move(err));
});
prom.get_future().get();
}
#else
void main_impl(int, char**)
{
std::cout << "Sorry, your compiler does not support C++20 coroutines" << std::endl;
}
#endif
int main(int argc, char** argv)
{
try
{
main_impl(argc, argv);
}
catch (const boost::system::system_error& err)
{
std::cerr << "Error: " << err.what() << ", error code: " << err.code() << std::endl;
return 1;
}
catch (const std::exception& err)
{
std::cerr << "Error: " << err.what() << std::endl;
return 1;
}
}

Prepared statements

This example demonstrates preparing statements, executing them and reading back the results. It employs synchronous functions.

//
// Copyright (c) 2019-2020 Ruben Perez Hidalgo (rubenperez038 at gmail dot com)
//
// Distributed under the Boost Software License, Version 1.0. (See accompanying
// file LICENSE_1_0.txt or copy at http://www.boost.org/LICENSE_1_0.txt)
//
#include "boost/mysql/mysql.hpp"
#include <boost/asio/io_context.hpp>
#include <boost/system/system_error.hpp>
#include <iostream>
/**
* 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.
*
* This example assumes you are already familiar with the basic concepts
* of mysql-asio (tcp_connection, resultset, rows, values). If you are not,
* please have a look to the query_sync.cpp example.
*/
#define ASSERT(expr) \
if (!(expr)) \
{ \
std::cerr << "Assertion failed: " #expr << std::endl; \
exit(1); \
}
void main_impl(int argc, char** argv)
{
if (argc != 3)
{
std::cerr << "Usage: " << argv[0] << " <username> <password>\n";
exit(1);
}
// Connection parameters
boost::asio::ip::tcp::endpoint ep (
boost::asio::ip::address_v4::loopback(), // host
);
argv[1], // username
argv[2], // password
"boost_mysql_examples" // database to use; leave empty or omit the parameter for no database
);
boost::asio::io_context ctx;
// Declare the connection object and authenticate to the server
conn.connect(ep, params);
/**
* We can tell MySQL to prepare a statement using connection::prepare_statement.
* We provide a string SQL statement, which can include any number of parameters,
* identified by question marks. Parameters are optional: you can prepare a statement
* with no parameters.
*
* Prepared statements are stored in the server on a per-connection basis.
* Once a connection is closed, all prepared statements for that connection are deallocated.
*
* The result of prepare_statement is a mysql::prepared_statement object, which is
* templatized on the stream type of the connection (tcp_prepared_statement in our case).
*
* We prepare two statements, a SELECT and an UPDATE.
*/
const char* salary_getter_sql = "SELECT salary FROM employee WHERE first_name = ?";
boost::mysql::tcp_prepared_statement salary_getter = conn.prepare_statement(salary_getter_sql);
ASSERT(salary_getter.num_params() == 1); // num_params() returns the number of parameters (question marks)
const char* salary_updater_sql = "UPDATE employee SET salary = ? WHERE first_name = ?";
boost::mysql::tcp_prepared_statement salary_updater = conn.prepare_statement(salary_updater_sql);
ASSERT(salary_updater.num_params() == 2);
/*
* Once a statement has been prepared, it can be executed as many times as
* desired, by calling prepared_statement::execute(). execute takes as input a
* (possibly empty) collection of mysql::value's and returns a resultset.
* The returned resultset works the same as the one returned by connection::query().
*
* The parameters passed to execute() are replaced in order of declaration:
* the first question mark will be replaced by the first passed parameter,
* the second question mark by the second parameter and so on. The number
* of passed parameters must match exactly the number of parameters for
* the prepared statement.
*
* Any collection providing member functions begin() and end() returning
* forward iterators to mysql::value's is acceptable. We use mysql::make_values(),
* which creates a std::array with the passed in values converted to mysql::value's.
* An iterator version of execute() is also available.
*/
boost::mysql::tcp_resultset result = salary_getter.execute(boost::mysql::make_values("Efficient"));
std::vector<boost::mysql::owning_row> salaries = result.fetch_all(); // Get all the results
ASSERT(salaries.size() == 1);
double salary = salaries[0].values().at(0).get<double>(); // First row, first column
std::cout << "The salary before the payrise was: " << salary << std::endl;
/**
* Run the update. In this case, we must pass in two parameters.
* Note that MySQL is flexible in the types passed as parameters.
* In this case, we are sending the value 35000, which gets converted
* into a mysql::value with type std::int32_t, while the 'salary'
* column is declared as a DOUBLE. The MySQL server will do
* the right thing for us.
*/
salary_updater.execute(boost::mysql::make_values(35000, "Efficient"));
/**
* Execute the select again. We can execute a prepared statement
* as many times as we want. We do NOT need to call
* connection::prepare_statement() again.
*/
result = salary_getter.execute(boost::mysql::make_values("Efficient"));
salaries = result.fetch_all();
ASSERT(salaries.size() == 1);
salary = salaries[0].values().at(0).get<double>();
ASSERT(salary == 35000); // Our update took place, and the dev got his pay rise
std::cout << "The salary after the payrise was: " << salary << std::endl;
/**
* Close the statements. Closing a statement deallocates it from the server.
* Once a statement is closed, trying to execute it will return an error.
*
* Closing statements implies communicating with the server and can thus fail.
*
* Statements are automatically deallocated once the connection is closed.
* If you are re-using connection objects and preparing statements over time,
* you should close() your statements to prevent excessive resource usage.
* If you are not re-using the connections, or are preparing your statements
* just once at application startup, there is no need to perform this step.
*/
salary_updater.close();
salary_getter.close();
// Close the connection
conn.close();
}
int main(int argc, char** argv)
{
try
{
main_impl(argc, argv);
}
catch (const boost::system::system_error& err)
{
std::cerr << "Error: " << err.what() << ", error code: " << err.code() << std::endl;
return 1;
}
catch (const std::exception& err)
{
std::cerr << "Error: " << err.what() << std::endl;
return 1;
}
}

Metadata

This example demonstrates inspecting the metadata of a resultset.

//
// Copyright (c) 2019-2020 Ruben Perez Hidalgo (rubenperez038 at gmail dot com)
//
// Distributed under the Boost Software License, Version 1.0. (See accompanying
// file LICENSE_1_0.txt or copy at http://www.boost.org/LICENSE_1_0.txt)
//
#include "boost/mysql/mysql.hpp"
#include <boost/asio/io_context.hpp>
#include <boost/system/system_error.hpp>
#include <iostream>
/**
* 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 shows how to use the metadata contained in a resultset.
*
* This example assumes you are already familiar with the basic concepts
* of mysql-asio (tcp_connection, resultset, rows, values). If you are not,
* please have a look to the query_sync.cpp example.
*/
#define ASSERT(expr) \
if (!(expr)) \
{ \
std::cerr << "Assertion failed: " #expr << std::endl; \
exit(1); \
}
void main_impl(int argc, char** argv)
{
if (argc != 3)
{
std::cerr << "Usage: " << argv[0] << " <username> <password>\n";
exit(1);
}
// Connection params (host, port, user, password, database)
boost::asio::ip::tcp::endpoint ep (boost::asio::ip::address_v4::loopback(), boost::mysql::default_port);
boost::mysql::connection_params params (argv[1], argv[2], "boost_mysql_examples");
// TCP and MySQL level connect
boost::asio::io_context ctx;
conn.connect(ep, params);
// Issue the query
const char* sql = R"(
SELECT comp.name AS company_name, emp.id AS employee_id
FROM employee emp
JOIN company comp ON (comp.id = emp.company_id)
)";
boost::mysql::tcp_resultset result = conn.query(sql);
/**
* Resultsets allow you to access metadata about the fields in the query
* using the fields() function, which returns a vector of field_metadata
* (one per field in the query, and in the same order as in the query).
* You can retrieve the field name, type, number of decimals,
* suggested display width, whether the field is part of a key...
*/
ASSERT(result.fields().size() == 2);
const boost::mysql::field_metadata& company_name = result.fields()[0];
ASSERT(company_name.database() == "boost_mysql_examples"); // database name
ASSERT(company_name.table() == "comp"); // the alias we assigned to the table in the query
ASSERT(company_name.original_table() == "company"); // the original table name
ASSERT(company_name.field_name() == "company_name"); // the name of the field in the query
ASSERT(company_name.original_field_name() == "name"); // the name of the physical field in the table
ASSERT(company_name.type() == boost::mysql::field_type::varchar); // we created the field as a VARCHAR
ASSERT(!company_name.is_primary_key()); // field is not a primary key
ASSERT(!company_name.is_auto_increment()); // field is not AUTO_INCREMENT
ASSERT(company_name.is_not_null()); // field may not be NULL
const boost::mysql::field_metadata& employee_id = result.fields()[1];
ASSERT(employee_id.database() == "boost_mysql_examples"); // database name
ASSERT(employee_id.table() == "emp"); // the alias we assigned to the table in the query
ASSERT(employee_id.original_table() == "employee"); // the original table name
ASSERT(employee_id.field_name() == "employee_id"); // the name of the field in the query
ASSERT(employee_id.original_field_name() == "id"); // the name of the physical field in the table
ASSERT(employee_id.type() == boost::mysql::field_type::int_); // we created the field as INT
ASSERT(employee_id.is_primary_key()); // field is a primary key
ASSERT(employee_id.is_auto_increment()); // we declared the field as AUTO_INCREMENT
ASSERT(employee_id.is_not_null()); // field cannot be NULL
// Close the connection
conn.close();
}
int main(int argc, char** argv)
{
try
{
main_impl(argc, argv);
}
catch (const boost::system::system_error& err)
{
std::cerr << "Error: " << err.what() << ", error code: " << err.code() << std::endl;
return 1;
}
catch (const std::exception& err)
{
std::cerr << "Error: " << err.what() << std::endl;
return 1;
}
}

UNIX domain sockets

This example demonstrates connecting to a MySQL server over a UNIX domain socket. It employs synchronous functions with exceptions.

//
// Copyright (c) 2019-2020 Ruben Perez Hidalgo (rubenperez038 at gmail dot com)
//
// Distributed under the Boost Software License, Version 1.0. (See accompanying
// file LICENSE_1_0.txt or copy at http://www.boost.org/LICENSE_1_0.txt)
//
#include "boost/mysql/mysql.hpp"
#include <boost/asio/io_context.hpp>
#include <boost/system/system_error.hpp>
#include <iostream>
/**
* 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 MySQL server using
* a UNIX socket. The socket path can be configured using command line
* arguments, and defaults to /var/run/mysqld/mysqld.sock
*
* This example uses synchronous functions and handles errors using exceptions.
*/
void print_employee(const boost::mysql::row& employee)
{
std::cout << "Employee '"
<< employee.values()[0] << " " // first_name (type boost::string_view)
<< employee.values()[1] << "' earns " // last_name (type boost::string_view)
<< employee.values()[2] << " dollars yearly\n"; // salary (type double)
}
#define ASSERT(expr) \
if (!(expr)) \
{ \
std::cerr << "Assertion failed: " #expr << std::endl; \
exit(1); \
}
// UNIX sockets are only available in, er, UNIX systems. Typedefs for
// UNIX socket-based connections are only available in UNIX systems.
// Check for BOOST_ASIO_HAS_LOCAL_SOCKETS to know if UNIX socket
// typedefs are available in your system.
#ifdef BOOST_ASIO_HAS_LOCAL_SOCKETS
void main_impl(int argc, char** argv)
{
if (argc != 3 && argc != 4)
{
std::cerr << "Usage: " << argv[0] << " <username> <password> [<socket-path>]\n";
exit(1);
}
const char* socket_path = "/var/run/mysqld/mysqld.sock";
if (argc == 4)
{
socket_path = argv[3];
}
/**
* Connection parameters that tell us where and how to connect to the MySQL server.
* There are two types of parameters:
* - UNIX-level connection parameters, identifying the UNIX socket to connect to.
* - MySQL level parameters: database credentials and schema to use.
*/
boost::asio::local::stream_protocol::endpoint ep (socket_path);
argv[1], // username
argv[2], // password
"boost_mysql_examples" // database to use; leave empty or omit the parameter for no database
);
// Note: by default, SSL will be used if the server supports it.
// connection_params accepts an optional ssl_options argument
// determining whether to use SSL or not. See ssl_options and ssl_mode
// documentation for further details on SSL.
boost::asio::io_context ctx;
// Connection to the MySQL server, over a UNIX socket
conn.connect(ep, params); // UNIX socket connect and MySQL handshake
const char* sql = "SELECT first_name, last_name, salary FROM employee WHERE company_id = 'HGS'";
boost::mysql::unix_resultset result = conn.query(sql);
// Get all the rows in the resultset
std::vector<boost::mysql::owning_row> employees = result.fetch_all();
for (const auto& employee: employees)
{
print_employee(employee);
}
// We can issue any SQL statement, not only SELECTs. In this case, the returned
// resultset will have no fields and no rows
sql = "UPDATE employee SET salary = 10000 WHERE first_name = 'Underpaid'";
result = conn.query(sql);
ASSERT(result.fields().size() == 0); // fields() returns a vector containing metadata about the query fields
// Check we have updated our poor intern salary
result = conn.query("SELECT salary FROM employee WHERE first_name = 'Underpaid'");
auto rows = result.fetch_all();
ASSERT(rows.size() == 1);
double salary = rows[0].values()[0].get<double>();
ASSERT(salary == 10000);
// Notify the MySQL server we want to quit, then close the underlying connection.
conn.close();
}
#else
void main_impl(int, char**)
{
std::cout << "Sorry, your system does not support UNIX sockets" << std::endl;
}
#endif
int main(int argc, char** argv)
{
try
{
main_impl(argc, argv);
}
catch (const boost::system::system_error& err)
{
std::cerr << "Error: " << err.what() << ", error code: " << err.code() << std::endl;
return 1;
}
catch (const std::exception& err)
{
std::cerr << "Error: " << err.what() << std::endl;
return 1;
}
}