A simple asynchronous MySQL wrapper for D++ bots

Simply take the source files and add them to your D++ bot project. This is a slightly modified version of what is used in my own bots.

This wrapper supports both synchronous (blocking) API and asynchronous (coroutine) API, alongside callback based async. All queries done through this wrapper use cached prepared statements, this will consume a very small amount of ram for a sometimes drastic increase in performance.

It is thread safe, however be aware that different threads may run queries that may intrude into other threads transactions. If you need ACID transaction safety, you should only use db::co_transaction() or db::transaction() and ensure all queries within use db::query().

No support is offered for this software at present. Your mileage may vary. I have only ever used this wrapper on Linux.

Detecting and linking the dependencies (libmysqlclient.so etc) is currently your responsibility. No package mangagement or build script is provided.

Documentation

Doxygen documentation can be found on github pages. It can be re-generated by running 'doxygen'

Dependencies

• libmysqlclient-dev
• D++
• fmtlib
• A C++ compiler capable of building D++ bots with coroutine support, if you want to use the asynchronous interface

Using the wrapper

Simple Queries

This is an example of using the asynchronous interface:

#include <dpp/dpp.h>
#include "database.h"
#include "config.h"

int main(int argc, char const *argv[]) {
	config::init("config.json");
	dpp::cluster bot(config::get("token"));

	bot.on_ready([&bot](const dpp::ready_t& event) -> dpp::task<void> {

		auto rs = co_await db::co_query("SELECT * FROM bigtable WHERE bar = ?", { "baz" });
		if (!rs.ok()) {
			std::cout << "SQL error: " << rs.error << "\n";
			co_return;
		}

		std::cout << "Number of rows returned: " << rs.size() << "\n";
		if (!rs.empty()) {
			std::cout << "First row 'bar' value: " << rs[0].at("bar") << "\n";
		}

		co_return;
	});

	db::init(bot);
	bot.start(dpp::st_wait);
}

Also create a config.json file. To use unix sockets to connect, set the port value to 0 and the hostname value to localhost.

{
    "token": "discord bot token",
    "database": {
        "host": "hostname",
        "username": "database username",
        "password": "database password",
        "database": "schema name",
        "port": 0,
        "socket": "/path/to/mysqld.sock"
    }
}

Using Transactions

To use transactions, wrap the transaction in the db::transaction function, and use only the db::query function within it for queries. Return true to commit the transaction, or throw any exception or return false to roll back the transaction.

Note that during a transaction all other queries will be forced to wait until the transaction is completed. Transactions are asynchronous, so use the callback to be notified when it completes, or use co_transaction as below:

#include <dpp/dpp.h>
#include "database.h"
#include "config.h"

int main(int argc, char const *argv[]) {
	config::init("config.json");
	dpp::cluster bot(config::get("token"));

	bot.on_ready([&bot](const dpp::ready_t& event) -> dpp::task<void> {
		co_await db::co_transaction([event]() -> bool {
			auto rs = db::query("SELECT current FROM data");
			db::query("UPDATE data SET previous = ?", { rs[0].at("data") });
			return true;
		});
	});

	db::init(bot);
	bot.start(dpp::st_wait);
}