asql

KO | EN

gitlite — search

#javascript #python #react #go #golang #android #deep-learning #nodejs #cli #kubernetes #vue #awesome-list #security #mcp

(★ 71)

Qt Async Sql library

asql Latest Version Download

Download Latest Version (.zip)

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# ASql
Qt Async Sql library

## Features
* 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)

## Requirements
* Qt 6.5 or later
* C++23 capable compiler (g++-14 or newer is required).

## Usage

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

### Creating a Pool
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()`.

```c++
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

```

#### Simple query via APool
The easiest entry point: `APool::exec()` grabs a connection, runs the query and resumes the coroutine — all in one `co_await`.
```c++
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();
    }
}
```

#### Getting a connection and running multiple queries
`APool::database()` suspends until a pooled connection is available and then returns it wrapped in `std::expected`.
```c++
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);
    }
}
```

### Performing a prepared query
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.
```c++
// 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}
     });
});
```

#### Transactions
`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:
```c++
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:
```c++
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();
    }
}
```

#### Lifetime management with co_yield
`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.
```c++
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();
    }
}
```

#### Cached queries
Use `ACache::coExec()` inside a coroutine the same way as `ADatabase::exec()`.
```c++
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);
```

### Cancelation
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.

```c++
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 error
```

### Notifications (Postgres only)

Subscribe with a per-channel callback. If the connection drops, re-subscribe after reconnecting:

```c++
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 &notification) {
                        qDebug() << "DB notification:" << notification.self
                                 << notification.name << notification.payload;
                    });
            }
        });

co_await db.coOpen(&receiver);
```

### Migrations
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.

```c++
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();
    }
}
```

asql

asql Latest Version Download

Today's picks

Was this content helpful?

Recent Feedback

asql

asql Latest Version Download

Today's picks

Was this content helpful?

Recent Feedback

Download README