This chapter reveals how to regain control over data through SQL and QtSql: you’ll discover the simple logic “table → query → model”, learn the secret of safe data insertion without manual string concatenation, and uncover why professional Qt developers almost always start with the right driver and update strategy — this saves hours of debugging and rescues releases.
Here we’ll show 4 basic SQL commands (CREATE/INSERT/SELECT/UPDATE+DELETE), examine 3 QtSql levels, and get hands-on with QSqlDatabase, QSqlQuery, QSqlTableModel, and QSqlRelationalTableModel — including prepare()+bindValue() and strategies OnFieldChange / OnManualSubmit.
If you skip this chapter, it’s easy to also skip that crucial moment when a “simple database” turns into a bug source.
This chapter includes ready-to-use code examples.
Chapter Self-Check
What is a primary key and why is it important for a database table?Answer
Correct answer: A primary key is a unique record identifier that can consist of one or more columns. It guarantees uniqueness of each record and is used to link tables together.
What three class levels does the QtSql module provide and what is each responsible for?Answer
Correct answer: Driver level (physical data access), programming level (API for database access through classes like QSqlDatabase and QSqlQuery), and user interface level (models for displaying data in views).
What’s the difference between selection and projection in the SELECT command?Answer
Correct answer: Selection is choosing table rows (defined in WHERE), while projection is choosing columns (specified after SELECT). Together they form the result table with the needed data.
Why check the result of the QSqlDatabase::open() method?Answer
Correct answer: The method can return false on failed connection (incorrect credentials, database unavailable, driver issues). Checking allows timely error detection and getting details via lastError().
Why is using prepare() and bindValue() preferable to direct data substitution in SQL queries?Answer
Correct answer: These methods protect against SQL injection, automatically escape special characters, and ensure correct data typing. Additionally, prepared queries can execute faster with repeated use.
Why must database driver extension files be included when distributing applications to clients?Answer
Correct answer: By default, database drivers connect to Qt as plug-ins and are not automatically included in the executable. Without them, the application won’t be able to connect to the database on the client’s system.
Why is the QSqlQueryModel class intended only for reading data?Answer
Correct answer: The query model displays the result of an arbitrary SELECT query, which can contain JOINs, aggregation, and computed fields. Such data cannot always be unambiguously written back to the source tables.
In which cases is SQLite preferable to server DBMS like MySQL or PostgreSQL?Answer
Correct answer: SQLite is ideal for single-user local data, embedded applications, and mobile devices. It doesn’t require installing a separate server, the driver is included in Qt6 by default, and the database is stored in a single file.
Why can only one row be inserted at a time with OnFieldChange and OnRowChange editing strategies?Answer
Correct answer: These strategies automatically save changes when switching cells or rows. When inserting multiple empty rows, the first attempt to switch will trigger saving of an unfilled record, which may violate database constraints.
Which editing strategy should be chosen if you need full control over when changes are saved?Answer
Correct answer: The OnManualSubmit strategy. Data is saved only when calling the submitAll() slot, and changes can be canceled via revertAll(). This is convenient for implementing “Save” and “Cancel” functions.
How can you display only specific table columns when using QSqlTableModel?Answer
Correct answer: Call the removeColumn() method for each column that shouldn’t be displayed. The model loads all columns by default, so unwanted ones must be explicitly hidden.
What happens if you forget to call select() after setTable() in QSqlTableModel?Answer
Correct answer: The model won’t execute a database query and will remain empty. The select() method actually loads data from the specified table into the model — without it, the view will be empty.
In which scenario is using QSqlRelationalTableModel preferable to QSqlTableModel?Answer
Correct answer: When you need to display data from related tables via foreign keys. For example, instead of showing a status ID, display its text description from a lookup table. The relational model automatically performs JOINs and displays readable values.
Practical Assignments
Easy Level
Notes App with SQLite
Create a Qt application with an SQLite database for storing personal notes. The table should contain fields: id (primary key), title (heading), content (note text), created_at (creation date). Implement adding new notes and displaying all notes in QTableView.
Hints: Use QSqlTableModel for simple display. Add QT += sql to the project file. For insertion, use insertRow() and setData(). Don’t forget to call submitAll() to save changes. Set the editing strategy via setEditStrategy().
Medium Level
Library with Search and Filtering
Develop a book tracking application with two tables: books (id, title, author, year, genre_id) and genres (id, name). Use QSqlRelationalTableModel to display genre by name instead of ID. Add QLineEdit for searching by author or title via setFilter() and QComboBox for filtering by genres. Implement the ability to edit data with save confirmation.
Hints: Use setRelation() to link with the genres table. For search, form a WHERE condition with LIKE. Apply the OnManualSubmit strategy with “Save”/”Cancel” buttons calling submitAll()/revertAll(). Handle errors via lastError().
Hard Level
Project Management System with Tasks
Create a full-featured project management application with three related tables: projects (id, name, description, status_id), tasks (id, project_id, title, description, priority, completed), statuses (id, name, color). Implement: hierarchical display of projects and tasks, drag-and-drop for changing task priority, filtering by status and completion, export of selected data to CSV, input validation (checking required fields), indication of unsaved changes.
Hints: Use QTreeView with a custom model inherited from QSqlRelationalTableModel. For drag-and-drop, override dropMimeData() and mimeData() methods. Implement a “dirty” data flag for changes. For export, iterate through selected rows via selectionModel(). Implement validation by overriding setData(). Apply transactions (database().transaction(), commit(), rollback()) for operation atomicity.
💬 Join the Discussion!
Got a handle on the QtSql module and its three class levels? Have questions about choosing between QSqlQueryModel and QSqlTableModel?
Share your experience working with different DBMS, talk about pitfalls when using relational models, or ask questions about SQL query optimization!