Qt Database (SQLite)

QSqlDatabase, QSqlQuery, QSqlTableModel — connecting to SQLite, running queries, and building data-driven Qt applications.

6 min read
48914 chars

Overview

Qt’s SQL module provides a unified API for relational databases. SQLite is the most common choice for embedded and desktop Qt apps — no server required, single file database.

Qt SQL Stack
┌──────────────────────────────────────┐
│  QSqlTableModel / QSqlQueryModel     │  ← for Model/View
│  QSqlQuery                           │  ← direct queries
│  QSqlDatabase                        │  ← connection
│  Qt SQL Driver (QSQLITE, QMYSQL...)  │  ← plugin layer
└──────────────────────────────────────┘

CMakeLists.txt Setup

find_package(Qt6 REQUIRED COMPONENTS Sql)
target_link_libraries(MyApp PRIVATE Qt6::Sql)

Connect to SQLite

#include <QSqlDatabase>
#include <QSqlError>

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("/var/data/sensors.db");  // file path, or ":memory:"

if (!db.open()) {
    qWarning() << "DB open failed:" << db.lastError().text();
    return;
}
qDebug() << "Database opened";

Create Table

#include <QSqlQuery>

QSqlQuery q;
q.exec(R"(
    CREATE TABLE IF NOT EXISTS sensor_readings (
        id         INTEGER PRIMARY KEY AUTOINCREMENT,
        timestamp  TEXT    NOT NULL,
        sensor_id  TEXT    NOT NULL,
        value      REAL    NOT NULL,
        unit       TEXT    NOT NULL DEFAULT '°C'
    )
)");

if (q.lastError().isValid())
    qWarning() << "Create table error:" << q.lastError().text();

Insert — Prepared Statement

QSqlQuery ins;
ins.prepare(R"(
    INSERT INTO sensor_readings (timestamp, sensor_id, value, unit)
    VALUES (:ts, :sid, :val, :unit)
)");

ins.bindValue(":ts",   QDateTime::currentDateTime().toString(Qt::ISODate));
ins.bindValue(":sid",  "temp-001");
ins.bindValue(":val",  36.5);
ins.bindValue(":unit", "°C");

if (!ins.exec())
    qWarning() << "Insert failed:" << ins.lastError().text();

Select & Iterate

QSqlQuery sel("SELECT timestamp, sensor_id, value, unit "
              "FROM sensor_readings ORDER BY id DESC LIMIT 20");

while (sel.next()) {
    QString ts  = sel.value("timestamp").toString();
    QString sid = sel.value("sensor_id").toString();
    double  val = sel.value("value").toDouble();
    QString unit= sel.value("unit").toString();
    qDebug() << ts << sid << val << unit;
}

QSqlTableModel — Model/View Integration

#include <QSqlTableModel>
#include <QTableView>

QSqlTableModel *model = new QSqlTableModel(this, db);
model->setTable("sensor_readings");
model->setEditStrategy(QSqlTableModel::OnManualSubmit);
model->select();                     // load data

// Rename column headers
model->setHeaderData(0, Qt::Horizontal, "ID");
model->setHeaderData(1, Qt::Horizontal, "Time");
model->setHeaderData(2, Qt::Horizontal, "Sensor");
model->setHeaderData(3, Qt::Horizontal, "Value");
model->setHeaderData(4, Qt::Horizontal, "Unit");

// Filter
model->setFilter("sensor_id = 'temp-001'");
model->select();

// Sort
model->setSort(1, Qt::DescendingOrder);  // sort by timestamp

// Show in view
QTableView *view = new QTableView(this);
view->setModel(model);
view->resizeColumnsToContents();

Transactions — Batch Inserts

db.transaction();

QSqlQuery ins;
ins.prepare("INSERT INTO sensor_readings (timestamp,sensor_id,value,unit) "
            "VALUES (?,?,?,?)");

for (const auto &reading : readings) {
    ins.addBindValue(reading.ts);
    ins.addBindValue(reading.sensorId);
    ins.addBindValue(reading.value);
    ins.addBindValue(reading.unit);
    if (!ins.exec()) {
        qWarning() << "Insert error:" << ins.lastError().text();
        db.rollback();
        return;
    }
}

db.commit();
qDebug() << "Batch insert complete";

Visual: Qt SQL Architecture

Application Code
       │
       ▼
QSqlDatabase::addDatabase("QSQLITE")
       │
       ▼
QSqlQuery  ◄──────────────────────────────────┐
       │                                        │
       ▼                                        │
Qt SQL Driver Plugin (QSQLITE)           QSqlTableModel
       │                                 (wraps QSqlQuery)
       ▼
   SQLite Engine
       │
       ▼
  sensors.db  (file on disk / :memory:)

Lab 1 — Sensor Reading Database

class SensorDB : public QObject {
    Q_OBJECT
    QSqlDatabase m_db;

public:
    explicit SensorDB(const QString &path, QObject *p = nullptr)
        : QObject(p)
    {
        m_db = QSqlDatabase::addDatabase("QSQLITE", "sensorconn");
        m_db.setDatabaseName(path);
        if (!m_db.open()) {
            qWarning() << "DB error:" << m_db.lastError().text();
            return;
        }
        createSchema();
    }

    bool insert(const QString &sensorId, float value, const QString &unit) {
        QSqlQuery q(m_db);
        q.prepare("INSERT INTO readings (ts, sensor_id, value, unit) "
                  "VALUES (?, ?, ?, ?)");
        q.addBindValue(QDateTime::currentMSecsSinceEpoch());
        q.addBindValue(sensorId);
        q.addBindValue(value);
        q.addBindValue(unit);
        if (!q.exec()) {
            qWarning() << q.lastError().text();
            return false;
        }
        return true;
    }

    QList<QPair<qint64,float>> history(const QString &sensorId, int limit = 100) {
        QList<QPair<qint64,float>> result;
        QSqlQuery q(m_db);
        q.prepare("SELECT ts, value FROM readings "
                  "WHERE sensor_id=? ORDER BY ts DESC LIMIT ?");
        q.addBindValue(sensorId);
        q.addBindValue(limit);
        q.exec();
        while (q.next())
            result.append({q.value(0).toLongLong(), q.value(1).toFloat()});
        return result;
    }

    float average(const QString &sensorId, int lastN = 10) {
        QSqlQuery q(m_db);
        q.prepare("SELECT AVG(value) FROM ("
                  "  SELECT value FROM readings "
                  "  WHERE sensor_id=? ORDER BY ts DESC LIMIT ?"
                  ")");
        q.addBindValue(sensorId);
        q.addBindValue(lastN);
        q.exec();
        return q.next() ? q.value(0).toFloat() : 0.0f;
    }

private:
    void createSchema() {
        QSqlQuery q(m_db);
        q.exec("CREATE TABLE IF NOT EXISTS readings ("
               "  id        INTEGER PRIMARY KEY AUTOINCREMENT,"
               "  ts        INTEGER NOT NULL,"
               "  sensor_id TEXT    NOT NULL,"
               "  value     REAL    NOT NULL,"
               "  unit      TEXT    NOT NULL"
               ")");
        q.exec("CREATE INDEX IF NOT EXISTS idx_sensor ON readings(sensor_id, ts)");
    }
};

// Usage
SensorDB db("/var/data/sensors.db");
db.insert("temp-001", 36.5f, "°C");
db.insert("temp-001", 37.0f, "°C");

auto hist = db.history("temp-001", 50);
qDebug() << "Average:" << db.average("temp-001");

Lab 2 — Live Table View with Auto-Refresh

class SensorTableWindow : public QWidget {
    Q_OBJECT
public:
    SensorTableWindow(QWidget *p = nullptr) : QWidget(p) {
        // Setup DB
        QSqlDatabase db = QSqlDatabase::database("sensorconn");

        auto *lay   = new QVBoxLayout(this);
        auto *view  = new QTableView;
        auto *model = new QSqlTableModel(this, db);
        model->setTable("readings");
        model->setSort(0, Qt::DescendingOrder);
        model->select();

        view->setModel(model);
        view->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
        view->setAlternatingRowColors(true);

        // Filter by sensor
        auto *filterEdit = new QLineEdit;
        filterEdit->setPlaceholderText("Filter by sensor ID...");
        connect(filterEdit, &QLineEdit::textChanged, [model](const QString &t) {
            model->setFilter(t.isEmpty() ? "" : "sensor_id LIKE '%" + t + "%'");
            model->select();
        });

        lay->addWidget(filterEdit);
        lay->addWidget(view);

        // Auto-refresh
        auto *timer = new QTimer(this);
        connect(timer, &QTimer::timeout, model, &QSqlTableModel::select);
        timer->start(2000);
    }
};

Interview Questions

Q1: What is the difference between QSqlQuery and QSqlTableModel?

QSqlQuery executes arbitrary SQL and gives you raw row-by-row access. QSqlTableModel wraps a single table and integrates with Qt’s Model/View framework — you can show it directly in a QTableView with sorting, filtering, and editing.

Q2: Why use prepared statements instead of string concatenation?

SQL injection prevention and performance. Prepared statements separate SQL code from data — the driver compiles the query once and executes it many times with different bound values. String concatenation is vulnerable to injection if inputs come from users.

Q3: When should you use transactions?

For batch inserts/updates (performance — SQLite wraps every exec() in its own transaction by default, which is slow). Use explicit transactions for atomicity: either all records insert or none do (data integrity).

Q4: What is :memory: database name?

An in-memory SQLite database — exists only while the connection is open, no file created. Useful for tests, caches, or temporary data. Very fast.

Q5: How do you use multiple database connections?

Pass a connection name to QSqlDatabase::addDatabase("QSQLITE", "connName"). Access it later with QSqlDatabase::database("connName"). Each QSqlQuery can specify which connection to use. Useful for multiple simultaneous databases.

Application: IoT Data Historian

class IoTHistorian : public QObject {
    Q_OBJECT
    SensorDB     *m_db;
    QTimer       *m_collectTimer;
    QTimer       *m_pruneTimer;
    int           m_maxDays = 30;

public:
    IoTHistorian(const QString &dbPath, QObject *p = nullptr)
        : QObject(p)
    {
        m_db = new SensorDB(dbPath, this);

        // Collect every 5s
        m_collectTimer = new QTimer(this);
        m_collectTimer->setInterval(5000);
        connect(m_collectTimer, &QTimer::timeout,
                this, &IoTHistorian::collectAll);

        // Prune old data daily
        m_pruneTimer = new QTimer(this);
        m_pruneTimer->setInterval(24 * 3600 * 1000);
        connect(m_pruneTimer, &QTimer::timeout,
                this, &IoTHistorian::pruneOld);
    }

    void start() {
        m_collectTimer->start();
        m_pruneTimer->start();
    }

    void addSensor(const QString &id, std::function<float()> readFn) {
        m_sensors[id] = readFn;
    }

    void generateReport(const QString &sensorId) {
        auto hist = m_db->history(sensorId, 1000);
        if (hist.isEmpty()) { qDebug() << "No data for" << sensorId; return; }

        float sum = 0, mn = hist[0].second, mx = hist[0].second;
        for (const auto &[ts, v] : hist) {
            sum += v; mn = qMin(mn, v); mx = qMax(mx, v);
        }
        qDebug() << "=== Report:" << sensorId << "===";
        qDebug() << "Samples:" << hist.size();
        qDebug() << "Min:" << mn << "  Max:" << mx
                 << "  Avg:" << sum / hist.size();
    }

signals:
    void newReading(const QString &sensorId, float value);

private slots:
    void collectAll() {
        for (auto it = m_sensors.begin(); it != m_sensors.end(); ++it) {
            float v = it.value()();
            m_db->insert(it.key(), v, "raw");
            emit newReading(it.key(), v);
        }
    }

    void pruneOld() {
        qint64 cutoff = QDateTime::currentMSecsSinceEpoch()
                        - (qint64)m_maxDays * 86400000LL;
        QSqlQuery q(QSqlDatabase::database("sensorconn"));
        q.prepare("DELETE FROM readings WHERE ts < ?");
        q.addBindValue(cutoff);
        q.exec();
        qDebug() << "Pruned" << q.numRowsAffected() << "old records";
    }

    QHash<QString, std::function<float()>> m_sensors;
};

// Usage
IoTHistorian hist("/var/data/iot.db");
hist.addSensor("temp-001", []() -> float { return 20 + rand()%20; });
hist.addSensor("volt-001", []() -> float { return 3.0f + rand()%30/100.0f; });
hist.start();

References

ResourceLink
Qt Docs: Qt SQLhttps://doc.qt.io/qt-6/qtsql-index.html
Qt Docs: QSqlDatabasehttps://doc.qt.io/qt-6/qsqldatabase.html
Qt Docs: QSqlQueryhttps://doc.qt.io/qt-6/qsqlquery.html
Qt Docs: QSqlTableModelhttps://doc.qt.io/qt-6/qsqltablemodel.html

Next tutorial → Qt State Machine