QT

Asynchronous database access with QtSql

In the past I’ve always been struggeling with asynchronous and yet simple database access with QtSql. I now came up with a quite nice solution and I hope I can help you with it.

Multithreading in Qt

When working with Qt most of the time you do not need to care about threading. Most things already work asynchronously, they don’t block and there’s no reason to mess with additional threads. This is the case for network requests via the QNetworkAccessManager where signals are used (as pretty much everywhere). If you’ve got other tasks like hash calculation of large files or image scaling, then there’s QtConcurrent::run() for you which will execute a function on the application’s thread pool.

QtConcurrent uses QFutures to report the results. In Qt 5 QFutures are not very handy (you need a QFutureWatcher to get the results asynchronously and you manually need to create and delete it). With Qt 6 this has changed and now there’s a pretty nice QFuture::then() function where you can directly pass a lambda handling the result:

QtConcurrent::run([]() {
    // do heavy calculations ...
    return value;
}).then([](Result value) {
    qDebug() << "Calculation done:" << value;
});

This simple way to chain QFutures creates a nice flow in your code. Instead of having (maybe multiple) slots that all need to be connected, here you can just write your code directly behind the QtConcurrent::run() call.

Databases and threading

That’s all pretty nice, but it doesn’t work with databases. You can’t just open a QSqlDatabase and execute queries on it via QtConcurrent. Most (if not all) SQL database drivers are going to complain that you’re using the database from the wrong thread. So, what you need to do is creating the database on the same thread as where the queries are executed. This means we can’t use QtConcurrent (at least not without some adjustments) since we don’t know on which thread our job is going to be executed.

Solution #1: Thread-Worker model with signals

My first idea on how to solve this and how I also did it in the past was creating a normal QThread and a database worker class running on the thread. The database is opened on this new thread and all queries are also executed on it. Now with this approach we somehow need to trigger queries and receive their results. Qt offers signals and slots with queued connections for this. My approach was to create two signals and a slot on the database worker. Let’s look at an example here:

class DatabaseWorker : public QObject
{
    Q_OBJECT
public:
    DatabaseWorker(QObject *parent = nullptr);

signals:
    void fetchAllUsersRequested();
    void allUsersFetched(const QList<User> &users);

private slots:
    void fetchAllUsers();
};

DatabaseWorker::DatabaseWorker(QObject *parent)
    : QObject(parent)
{
    connect(this, &DatabaseWorker::fetchAllUsersRequested,
            this, &DatabaseWorker::fetchAllUsers);
}

DatabaseWorker::fetchAllUsers()
{
    // ... do query ..
    emit allUsersFetched(users);
}

If you want to fetch the users, you’d do the following:

emit database->fetchAllUsersRequested();

As soon as the database has executed the query, it will emit the allUsersFetched() signal, which you can handle.

However this approach has some problems:

  1. You need to care about threading manually.
  2. You need to create signals and connect them for every new function.
  3. The caller (of fetchAllUsersRequested()) doesn’t know which request belonged to the results received from the signal (allUsersFetched()). This is not a problem in this case, but as soon as you’ve got multiple requests at the same time, this will get important.

The second point can be workarounded, but the code won’t be nice.

Solution #2: QtConcurrent with a one thread QThreadPool

Using QtConcurrent with QFutures would solve all three problems here, so we should have a deeper look at QtConcurrent. In the documentation we can see that QtConcurrent also provides the option to use a specific QThreadPool for the execution.

This helps us since with a custom thread pool we can set the maximum thread count to 1 and so this way can guarantee that everything is executed on the same thread. QThreadPool automatically deletes threads when they’re unused. We also need to prevent this, because the used thread of course must not change:

QThreadPool pool;
// limit to one thread
pool.setMaxThreadCount(1);
// prevent automatic deletion and recreation
pool.setExpiryTimeout(-1);

This basically already solved our problem. We now just need to do everything via QtConcurrent and our QThreadPool and we don’t need to care about threads at all anymore.

Our Database now could look like this:

class Database : public QObject
{
    Q_OBJECT
public:
    QFuture<void> open();

private:
    QThreadPool m_pool;
}

QFuture<void> Database::open()
{
    return QtConcurrent::run(m_pool, []() {
        auto database = QSqlDatabase::addDatabase("QSQLITE", "main-connection");
        database.setDatabaseName("/home/me/data.sqlite");
        database.open();
        // of course you should do some more checks here to see
        // whether everything went well :)
    });
}

Other queries can be done like this now:

QFuture<QList<User>> Database::fetchUsers()
{
    return QtConcurrent::run(m_pool, [this]() {
        QSqlQuery query(QSqlDatabase::database("main-connection"));
        query.exec("SELECT * FROM users");

        QList<User> users;
        while (query.next()) {
            // ...
        }
        return users;
    });
}

And with Qt 6 you can pretty easily handle the results now:

database->fetchUsers().then([](const QList<User> &users) {
    // do something
});

Unfortunately with Qt 5 it’s not so nice:

auto *watcher = new QFutureWatcher<QList<User>>();
connect(watcher, &QFutureWatcherBase::finished, [watcher]() {
    QList<User> users = watcher->result();
    // do something with the result
    watcher->deleteLater();
});
watcher->setFuture(database->fetchUsers());

This is not very nice and things can go wrong, you could forget to delete the QFutureWatcher for example. Thus, we use a template function to simplify this:

template<typename T, typename Handler>
void await(const QFuture<T> &future, QObject *context, Handler handler)
{
    auto *watcher = new QFutureWatcher<T>(context);
    QObject::connect(watcher, &QFutureWatcherBase::finished,
                     context, [watcher, handler { std::move(handler) }]() {
        handler(watcher->result());
        watcher->deleteLater();
    });
    watcher->setFuture(future);
}

await(database->fetchUsers(), this, [](QList<User> users) {
    // do something
});

And that already looks much better! :)

We now got a solution with the following features:

  1. A database class running in the main thread. No manual thread management.
  2. No signals and slots for each new function. Just QFuture::then / await.
  3. Request and result handling can be easily ‘linked’ by using lambda captures.

This is a very nice solution for most of the use cases in our applications.

Do we want anything more? – Okay, maybe parallel queries with multiple threads, for building servers or a high-performing application, but that’s probably irrelevant in most of the cases. However, maybe we’ll see this in one of the next blog posts. :)

Comments