Integrating SQLite with Qt Quick

Question

I’m trying to include a SQLite database with QT Quick but I can’t find any examples. I just want to be able to access items from a database. Does anyone know of any example programs I can play with?

Solution 1

You could take QSqlQueryModel for example, or any other SQL model and add it to the QML context and use it in the ListView for example.

Create a model

Define the role names that will be used by delegates to access data by role in QtQuick components (data method is bit naive as there is no kind of error checking):

class SqlQueryModel: public QSqlQueryModel
{
    Q_OBJECT
    QHash<int,QByteArray> *hash;
public:
    explicit SqlQueryModel(QObject * parent) : QSqlQueryModel(parent)
    {
        hash = new QHash<int,QByteArray>;
        hash->insert(Qt::UserRole,      QByteArray("someRoleName"));
        hash->insert(Qt::UserRole + 1,  QByteArray("otherRoleName"));
    }
    QVariant data(const QModelIndex &index, int role) const
    {
       if(role < Qt::UserRole) {
          return QSqlQueryModel::data(index, role);
       }
       QSqlRecord r = record(index.row());
       return r.value(QString(hash->value(role))).toString();
    }
    inline RoleNameHash roleNames() const { return *hash; }
};

Add it to the QML

Create an instance of newly defined model class and add it to the QML context

view = new QQuickView();

QSqlQueryModel *someSqlModel = new SqlQueryModel(this);
someSqlModel->setQuery("SELECT someRoleName, otherRoleName FROM some_table");

QQmlContext *context = view->rootContext();
context->setContextProperty("someSqlModel", someSqlModel);

view->setSource(QUrl("qrc:///MainView.qml"));
view->show();

Bind the model to the QML View

Bind your model to the ListViews model property and use previously defined role names to access the data in the delegate.

ListView {
    id: someListView
    model: someSqlModel
    delegate: Text {
        anchors.fill: parent
        text: someRoleName
    }
}

---

Some example links:

How to use a QSqlQueryModel in QML

QML and QSqlTableModel

Solution 2

rebus' answer is a great start, but it me left wondering how to connect to an existing (SQLite) database.

So here's a complete example for Qt 5.x, no prerequisites needed.

Notice that the code avoids the role name hashtable lookup in SqlModel.data() and all superfluous heap allocations.

main.cpp

#include <QtDebug>
#include <QString>
#include <QHash>
#include <QGuiApplication>
#include <QtQml>
#include <QQuickView>
#include <QtSql>
#include <QSqlQueryModel>

QSqlError initDb()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    // Open database file. The driver creates a new file if it doesn't exist yet.
    db.setDatabaseName("mylibrary.sqlite");
    if (!db.open())
        return db.lastError();

    QStringList tables = db.tables();
    if (tables.contains("books", Qt::CaseInsensitive)) {
        // DB has already been populated
        return QSqlError();
    }

    QSqlQuery query;
    if (!query.exec(QLatin1String("create table books(title varchar, author varchar)")))
        return query.lastError();
    if (!query.prepare(QLatin1String("insert into books(title, author) values(?, ?)")))
        return query.lastError();

    auto addBook = [&] (const QString& title, const QString& author) {
        query.addBindValue(title);
        query.addBindValue(author);
        query.exec();
    };

    addBook("Mademoiselle de Maupin", "T. Gautier");
    addBook("Der Nachsommer", "A. Stifter");
    addBook("L'Education sentimentale", "G. Flaubert");
    addBook("Voyna i mir", "L. Tolstoy");
    addBook("Mysterier", "K. Hamsun");
    addBook("The Sound and the Fury", "W. Faulkner");
    addBook("Tender is the Night", "F. Scott Fitzgerald");

    return QSqlError();
}

class SqlModel : public QSqlQueryModel
{
    Q_OBJECT

public:
    SqlModel(QObject* parent = 0)
        : QSqlQueryModel(parent)
    {
        roleNamesHash.insert(Qt::UserRole,      QByteArray("title"));
        roleNamesHash.insert(Qt::UserRole + 1,  QByteArray("author"));
    }

    QVariant data(const QModelIndex& index, int role) const
    {
        if(role < Qt::UserRole)
            return QSqlQueryModel::data(index, role);

        QSqlRecord r = record(index.row());
        return r.value(role - Qt::UserRole);
    }

    QHash<int, QByteArray> roleNames() const { return roleNamesHash; }

private:
    QHash<int, QByteArray> roleNamesHash;
};

int main(int argc, char **argv)
{
    QGuiApplication app(argc, argv);

    auto err = initDb();
    if (err.type() != QSqlError::NoError) {
        qCritical() << err.text();
        return 1;
    }

    SqlModel sqlModel;
    sqlModel.setQuery("SELECT title, author FROM books");

    QQuickView view;
    QQmlContext *context = view.rootContext();
    context->setContextProperty("sqlModel", &sqlModel);
    view.setResizeMode(QQuickView::SizeRootObjectToView);
    view.setSource(QUrl("qrc:///main.qml"));
    view.show();

    return app.exec();
}

#include "main.moc"

main.qml

import QtQuick 2.1

Item {
    width: 500
    height: 300

    ListView {
        anchors { fill: parent; margins: 20 }
        model: sqlModel
        delegate: Text {
            text: author + ' - ' + title
        }
    }
}

resources.qrc

<RCC>
    <qresource prefix="/">
        <file>main.qml</file>
    </qresource>
</RCC>

minimal-qml-sql-app.pro

CONFIG += c++11

QT += qml \
      quick \
      sql
SOURCES += main.cpp
OTHER_FILES += main.qml
RESOURCES += resources.qrc

Solution 3

In Qt 5.3, there's an example in

Examples/Qt-5.3/quick/controls/calendar

One interesting note compared to rebus and Kay's answers: The calendar example exposes the SqlModel using

qmlRegisterType()

This allows for a more declarative programming style: there's no need to instantiate an SqlModel in main.cpp. Instead, you declare it in your qml.

Comments

[Andrej Repiský]

You don't have any instance of "SqlQueryModel" in your code. What's the point of declaring the class SqlQueryModel?

[Davor Lucic]

@ondrejandrej It was a typo, I've fixed it, thanks for noticing. Using QSqlQueryModel made defining SqlQueryModel pointless and the example probably would not work since QML wouldn't find the someRoleName role.

[NeoMorfeo]

Great point of entry to learn the SQL integration using QML in a proper way. So many thanks

[Mitch]

This is a good point about QML integration, but unfortunately the model in that example doesn't really behave like a model, because Calendar already has its own model (for days in the month). Instead, it just gets a list of events (a QList<QObject*> model) for a particular day. It was recently changed to simply be a QObject because of this fact: codereview.qt-project.org/#/c/145253

[Mitch]

I had to remove Q_OBJECT and #include "main.moc". Don't know if these are important, but it won't build unless I remove them.