Using Nt SQL

How to connect to an SQL database, run statements with bound values, and show tables in models.

Nt SQL gives access to SQL databases: QSqlDatabase stands for a connection, QSqlQuery runs statements and reads their rows, and QSqlQueryModel and QSqlTableModel show them as item models. The library is Nt::Sql and needs only Nt Core:

find_package(Nt6 REQUIRED)
add_executable(contacts main.cpp)
target_link_libraries(contacts PRIVATE Nt::Sql)

Drivers

A driver connects Nt SQL to one kind of database. Nt comes with one, QSQLITE, for SQLite: a database in a single file, or in memory, with nothing to install or to run. It uses the SQLite library of the system and is there if Nt was built with it (the CMake option NT_USE_SQLITE, which is on by default); QSqlDatabase::drivers() tells at run time.

There are no driver plugins. A driver for another database is a subclass of QSqlDriver and QSqlResult in the application, made known with QSqlDatabase::registerSqlDriver().

Connecting

A connection is added once and then found by name from anywhere; the one without a name is the default connection, which queries and models use when they are given none:

static bool openDatabase(const QString &file) {
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(file);
    if (!db.open()) {
        std::fprintf(stderr, "cannot open %s: %s\n", qPrintable(file), qPrintable(db.lastError().text()));
        return false;
    }
    QSqlQuery query;
    return query.exec("CREATE TABLE IF NOT EXISTS contacts ("
                      "id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, email TEXT)");
}

For QSQLITE the database name is the path of the file, which is created if it does not exist, :memory: for a database that lives in memory and is gone when the connection is closed, or an empty name for a temporary one. QSqlDatabase::setConnectOptions() takes these options:

QSQLITE_BUSY_TIMEOUT: how long, in milliseconds, to wait for a lock of another connection; 5000 by default.
QSQLITE_OPEN_READONLY: open the file for reading only; it has to exist.
QSQLITE_OPEN_URI: the database name is a file: URI.
QSQLITE_OPEN_NOFOLLOW: refuse a database name that is a symbolic link.
QSQLITE_ENABLE_SHARED_CACHE: share the cache with the other connections of the process.
QSQLITE_ENABLE_REGEXP: make the SQL operator REGEXP work, with the patterns of QRegularExpression. A number after it, as in QSQLITE_ENABLE_REGEXP=50, is how many compiled patterns are kept; 25 by default.
QSQLITE_NO_USE_EXTENDED_RESULT_CODES: report the plain result codes of SQLite in QSqlError::nativeErrorCode(), 19 for any constraint and not 2067 for a unique one.

A connection, with its queries, is for one thread at a time. A thread that needs the database gets its own connection, with QSqlDatabase::cloneDatabase() for example.

Running Statements

QSqlQuery::exec() runs a statement. A statement that takes values is prepared with placeholders, and the values are bound to them:

static bool addContact(const QString &name, const QString &email) {
    QSqlQuery query;
    query.prepare("INSERT INTO contacts (name, email) VALUES (:name, :email)");
    query.bindValue(":name", name);
    query.bindValue(":email", email);
    if (!query.exec()) {
        std::fprintf(stderr, "cannot add %s: %s\n", qPrintable(name), qPrintable(query.lastError().text()));
        return false;
    }
    return true;
}

Bind values, do not paste them into the text of the statement: a bound value is data whatever characters it has, so a name with a quote in it, or one written to break the statement, does no harm. Placeholders have names, as above, or are written ? and bound in order:

static void listContacts(const QString &filter) {
    QSqlQuery query;
    query.prepare("SELECT name, email FROM contacts WHERE name LIKE ? ORDER BY name");
    query.addBindValue("%" + filter + "%");
    query.exec();
    while (query.next())
        std::printf("%-20s %s\n", qPrintable(query.value(0).toString()), qPrintable(query.value(1).toString()));
}

After exec() a SELECT is positioned before its first row. QSqlQuery::next() moves through the rows and QSqlQuery::value() reads a field of the current one, as a QVariant of the type that the database stored: with SQLite a 64-bit integer, a double, a QString or a QByteArray, or a null QVariant. A date is stored as ISO 8601 text, which QVariant::toDate() and QVariant::toDateTime() read back.

When something fails, the function returns false and QSqlQuery::lastError(), or QSqlDatabase::lastError() for the connection, tells why.

Transactions

Several statements become one change, which is written completely or not at all, between QSqlDatabase::transaction() and QSqlDatabase::commit(); QSqlDatabase::rollback() gives it up. With SQLite a transaction also makes many inserts much faster, as the file is synchronized once and not for each row:

QSqlDatabase db = QSqlDatabase::database();
db.transaction();
QSqlQuery query;
query.prepare("INSERT INTO samples (at, value) VALUES (?, ?)");
for (const Sample &sample : samples) {
    query.addBindValue(sample.at);
    query.addBindValue(sample.value);
    query.exec();
}
db.commit();

Models

QSqlQueryModel shows the rows of any SELECT, read-only. QSqlTableModel works on one table and writes changes back: items that are set, rows that are inserted and rows that are removed, at once or when QSqlTableModel::submitAll() is called, as its edit strategy says:

static bool removeContact(const QString &name) {
    QSqlTableModel model;
    model.setTable("contacts");
    model.setEditStrategy(QSqlTableModel::OnManualSubmit);
    model.select();
    const int nameColumn = model.fieldIndex("name");
    for (int row = 0; row < model.rowCount(); ++row) {
        if (model.data(model.index(row, nameColumn)).toString() == name) {
            model.removeRows(row, 1);
            return model.submitAll();
        }
    }
    return false;
}

Both are item models like any other, so they fit the views of Nt Widgets and the proxy models of Nt Core, such as QSortFilterProxyModel. With SQLite they read their rows in steps of 255, as a view asks for them; see QSqlQueryModel::fetchMore().

QSqlRelationalTableModel adds foreign keys to that: a column that holds the key of a row in another table shows a value from that row, and is changed by giving it another key:

QSqlRelationalTableModel model;
model.setTable("employee");
model.setRelation(model.fieldIndex("city"), QSqlRelation("city", "id", "name"));
model.select();

With the edit strategies that write at once, a row that was written is read again by itself, so the model is not reset and a view keeps its selection; QSqlTableModel::selectRow() does the same on request.

Notifications and Handles

The QSQLITE driver tells when a table changes through its own connection. Subscribe to the name of the table with QSqlDriver::subscribeToNotification(); QSqlDriver::notification() is then emitted from the event loop for each row that is inserted, updated or deleted, with the row id as its payload. Changes that another connection or another process makes are not seen.

QSqlDriver::handle() and QSqlResult::handle() give the sqlite3 * of a connection and the sqlite3_stmt * of a prepared statement, for code that calls SQLite directly:

Q_DECLARE_METATYPE(sqlite3 *)

sqlite3 *handle = db.driver()->handle().value<sqlite3 *>();

What Is Missing

Drivers for other databases than SQLite, driver plugins, QSqlRelationalDelegate, and the deprecated QSqlDatabase::exec(). The class pages list what differs from Qt in each class.