Qt Async Sql library
- Drivers
- PostgreSQL
- MySQL/MariaDB (threaded)
- SQLite (threaded)
- ODBC (threaded)
- Navigate on your data with iterators
- Scoped transactions objects
- Prepared queries
- Cancellabel queries
- Thread local Connection pool
- Notifications
- Database maintainance with AMigrations class
- Conveniently converts your query data to JSON/CBOR or QVariantHash
- Cache support
- Single row mode (useful for very large datasets)
- Qt 6.5 or later
- C++23 capable compiler (g++-14 or newer is required).
ASql fully supports C++20/23 coroutines. Use ACoroTerminator as the return type for fire-and-forget coroutines. Every exec() / coDatabase() call returns an awaitable directly — just co_await it and check the std::expected<AResult, QString> value.
Note: Always define coroutines as free functions or static member functions, not as local lambdas. Coroutine frames outlive the statement that starts them, so a lambda that captures local variables by reference will produce dangling references.
Make sure to read (C++ Coro Guidelines on Coroutines)[https://isocpp.github.io/CppCoreGuidelines/CppCoreGuidelines#sscp-coro]
A connection pool is a convenient way of getting new connections without worrying about configuring it and it's lifetime, once you are done with it the connection returns to the pool. It's also possible to have a single database connection without it being attached to a pool, by creating an ADatabase object directly and calling co_await db.coOpen().
using namespace ASql;
// No new connection is created at this moment
APool::create(APg::factory("postgres://user:pass@server/dbname?target_session_attrs=read-write"));
APool::create(APg::factory("postgres://user:pass@server/dbname"), "my_read_only_pool");
// Defines the maximum number of idle connections (defaults to 1)
APool::setMaxIdleConnections(10);
APool::setMaxIdleConnections(15, "my_read_only_pool");
{
// Grabs a connection, it might be a new connection or one from the idle pool
auto db = co_await APool::database();
// Grabs a connection from a read-only pool
auto dbRO = co_await APool::database("my_read_only_pool");
} // The scope is over, now once ADatabase db variables are
// done with the queries they will return to the pool
The easiest entry point: APool::exec() grabs a connection, runs the query and resumes the coroutine — all in one co_await.
ACoroTerminator runQuery()
{
auto result = co_await APool::exec(u8"SELECT id, message FROM messages LIMIT 5");
if (result.has_value()) {
for (auto row : *result) {
qDebug() << "id" << row[0].toInt() << "msg" << row["message"].toString();
}
} else {
qDebug() << "Query error:" << result.error();
}
}APool::database() suspends until a pooled connection is available and then returns it wrapped in std::expected.
ACoroTerminator runQueries()
{
auto db = co_await APool::database();
if (!db) {
qDebug() << "Could not get a connection:" << db.error();
co_return;
}
auto result1 = co_await db->exec(u"SELECT now()"_s);
if (result1.has_value()) {
qDebug() << "now:" << result1->toJsonObject();
}
auto result2 = co_await db->exec(u"SELECT count(*) FROM messages WHERE date > $1"_s, { startingDate });
if (result2.has_value() && result2->size()) {
qDebug() << "count:" << result2->begin().value(0);
}
}Prepared queries allows the database server to avoid to repeatedly parse and plan your query execution, this doesn't always means faster execution, this is because the planner can often make better planning when the data is known.
Our advice is that you try to mesure your execution with real data, switching from prepared to not prepared is also very trivial.
It's very important that the APreparedQuery object doesn't get deleted (by getting out of scope), this is because it holds an unique identification for your query, in order to make this easier one can use the APreparedQueryLiteral macro. You can also manually create a static APreparedQuery object or have your prepared query as a member of a class that isn't going to be deleted soon.
// PostgreSQL uses numered place holders, and yes you can repeat them :)
db->exec(APreparedQuery(u"INSERT INTO temp4 VALUE ($1, $2, $3, $4, $5, $6, $7) RETURNING id"),
{
true,
u"foo"_s,
qint64(1234),
QDateTime::currentDateTime(),
123456.78,
QUuid::createUuid(),
QJsonObject{
{"foo", true}
});
});ADatabase::begin() and APool::begin() return an AExpectedTransaction. co_await it to start
the transaction; the returned ATransaction will automatically roll back when it goes out of scope
unless commit() is called.
APool::begin() grabs a pooled connection and starts the transaction in one step:
ACoroTerminator runPoolTransaction()
{
auto transaction = co_await APool::begin();
if (!transaction) {
qDebug() << "BEGIN error:" << transaction.error();
co_return;
}
auto result = co_await transaction->database().exec(
u"INSERT INTO messages (message) VALUES ($1) RETURNING id"_s,
{u"Hello from pool begin!"_s});
if (!result) {
qDebug() << "INSERT error:" << result.error();
co_return; // transaction rolls back automatically
}
auto commit = co_await transaction->commit();
if (!commit) {
qDebug() << "COMMIT error:" << commit.error();
}
}ADatabase::begin() returns an AExpectedTransaction on an existing connection:
ACoroTerminator runTransaction()
{
auto db = co_await APool::database();
if (!db) {
qDebug() << "Connection error:" << db.error();
co_return;
}
auto transaction = co_await db->begin();
if (!transaction) {
qDebug() << "BEGIN error:" << transaction.error();
co_return;
}
auto result = co_await db->exec(u"INSERT INTO messages (message) VALUES ($1) RETURNING id"_s,
{u"Hello from coroutine!"_s});
if (!result) {
qDebug() << "INSERT error:" << result.error();
co_return; // transaction rolls back automatically
}
qDebug() << "Inserted id:" << result->begin().value(0).toInt();
auto commit = co_await transaction->commit();
if (!commit) {
qDebug() << "COMMIT error:" << commit.error();
}
}co_yield a QObject* pointer to tie the coroutine's lifetime to that object. If the object is destroyed while the coroutine is suspended (e.g. waiting for a slow query), the coroutine is destroyed and the function is never resumed — preventing dangling-pointer crashes.
ACoroTerminator runWithLifetime()
{
auto *guard = new QObject;
QTimer::singleShot(500, guard, [guard] { delete guard; }); // simulate early teardown
co_yield guard; // coroutine is destroyed when guard is destroyed
auto result = co_await APool::exec(u8"SELECT now(), pg_sleep(1)", guard);
if (result.has_value()) {
qDebug() << "result:" << result->toJsonObject();
} else {
qDebug() << "error (or cancelled):" << result.error();
}
}Use ACache::coExec() inside a coroutine the same way as ADatabase::exec().
ACoroTerminator runCached(ACache *cache)
{
// First call hits the database
auto result = co_await cache->exec(u"SELECT now()"_s);
if (result.has_value()) {
qDebug() << "fresh result:" << result->toJsonObject();
}
// Second call with the same query returns the cached result immediately
auto cached = co_await cache->exec(u"SELECT now()"_s);
if (cached.has_value()) {
qDebug() << "cached result:" << cached->toJsonObject();
}
}
auto cache = new ACache;
auto db = co_await ::database();
cache->setDatabase(*db);
runCached(cache);ASql was created with web usage in mind, namely to be used in Cutelyst but can also be used on Desktop/Mobile apps too. Pass a QObject* receiver to exec() or coOpen(); if that object is destroyed while a query or open is in flight, the operation is cancelled and the coroutine receives an error instead of resuming with a dangling pointer.
auto cancelator = new QObject;
auto result = co_await db->exec(u"SELECT pg_sleep(5)", cancelator);
delete cancelator; // cancels the in-flight query; co_await resumes with an errorSubscribe with a per-channel callback. If the connection drops, re-subscribe after reconnecting:
ADatabase db;
QObject receiver;
connect(db.driver(), &ADriver::stateChanged, &receiver,
[&db](ADatabase::State state, const QString &status) {
qDebug() << "state changed" << state << status << db.isOpen();
if (state == ADatabase::Disconnected) {
qDebug() << "state disconnected";
} else if (state == ADatabase::Connected) {
db.subscribeToNotification(
u"my_awesome_notification"_s,
&receiver,
[](const ADatabaseNotification ¬ification) {
qDebug() << "DB notification:" << notification.self
<< notification.name << notification.payload;
});
}
});
co_await db.coOpen(&receiver);This feature allows for easy migration between database versions, ASql doesn't try to be smart at detecting your changes, it's up to you to write and test for proprer SQL.
Each migration must have a positive integer number with up and down scripts, the current migration version is stored at asql_migrations table that is automatically created if you load a new migration.
ACoroTerminator runMigrations()
{
AMigrations mig;
mig.fromString(uR"V0G0N(
-- 1 up
create table messages (message text);
insert into messages values ('I ♥ Cutelyst!');
-- 1 down
drop table messages;
-- 2 up
create table log (message text);
insert into log values ('logged');
-- 2 down
drop table log;
-- 3 up
create table log (message text);
)V0G0N");
const auto loaded = co_await mig.load();
if (!loaded) {
qDebug() << "LOAD error:" << loaded.error();
co_return;
}
// Migrate to version 2; omit targetVersion to migrate to the latest version
const auto migrated = co_await mig.migrate(2);
if (!migrated) {
qDebug() << "MIGRATE error:" << migrated.error();
}
}