How to embed a database in your application with SQLite and Qt

This tutorial will show you how to use SQLite and Qt SQL to have a relational database in your application. It will cover all the basic operations needed to work with a database like creating a table, inserting data, executing a query and checking the results. Full project and source code are provided.

SQLite and Qt SQL

SQLite and Qt SQL

This tutorial is based on two main components: Qt SQL and SQLite.

The Qt SQL module provides a driver layer, SQL API layer, and a user interface layer for SQL databases.

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. Basically a database stored in a single file (or memory).

Qt offers support (drivers) for other popular databases like MySQL and PostgreSQL, but SQLite is the easiest and quickest option for a simple application and it should be more than enough in most cases.

Example application

In general you want to embed a database in your application to handle data in a simple and well organised way using SQL (Structured Query Language).

The application discussed in this tutorial is the simplest possible I could think of.

SQLite and Qt SQL example application

There’s a text field to enter a number which represents an ID of an entry in the database. Then clicking the SEARCH button will show the corresponding entry or an error message if none is found.

I won’t be discussing the code defining the GUI of this simple Qt application because I want to focus on the one handling the database.

If you need help to start with Qt you can check out my previous post about how to create an application with Qt and C++.

Setting up Qt SQL

To use Qt SQL in a Qt project all you need to do is adding “sql” to the QT configuration variable in the .pro file:

QT       += core gui sql

Obviously you will need to include the right headers when needed in your source code. For this application I added the following headers in MainWindow.cpp:

#include <QSqlDatabase>
#include <QSqlDriver>
#include <QSqlError>
#include <QSqlQuery>

Connecting to a database

The first step to use a database is establishing a connection to one.

Qt is a bit confusing here as a database connection is defined by a QSqlDatabase object, but normally you associate a connection to a database, so it’s not too confusing when considering that.

We start the process checking if the SQLite driver is available as expected using the static function isDriverAvailable:

const QString DRIVER("QSQLITE");

if(QSqlDatabase::isDriverAvailable(DRIVER))

This should never be a problem, but, in coding as in life, it’s always better safe than sorry.

Then we create a connection calling the static function addDatabase:

	QSqlDatabase db = QSqlDatabase::addDatabase(DRIVER);

We could also pass a connection name as second argument if we wanted to use multiple connections. If we don’t, this will be the default (unnamed) one.

Once we have a connection we need to set its configuration parameters:

	db.setDatabaseName(":memory:");

For SQLite the only configuration needed is the database name. We can set that with setDatabaseName.

It’s important to notice that “:memory:” is a special name for SQLite. It will create a temporary database existing entirely in memory until the connection is closed. In case you wanted to create a permanent SQLite database on disk, you have to pass a full file path to setDatabaseName.

Finally we can open the database calling the function open.

	if(!db.open())
		qWarning() << "ERROR: " << db.lastError();

In case of any error open returns false and we can get an error message using the function lastError which returns a QSqlError object with details about what went wrong.

Another important difference between SQLite and other database engines is that opening a database also creates it, whereas for other engines you can only open an existing database.

Creating a table

Our new database is empty, so we need to create a table to store data in it.

To create a table in SQL we need to execute a CREATE statement.

QSqlQuery query("CREATE TABLE people (id INTEGER PRIMARY KEY, name TEXT)");

It’s possible to pass an SQL statement to the constructor of a QSqlQuery object and it will be automatically executed.

We can check if the statement was successful with the following code:

if(!query.isActive())
		qWarning() << "ERROR: " << query.lastError().text();

When a query is successfully executed the function isActive returns true, false otherwise. In case of error we can use lastError as seen before to know more about what happened.

Inserting data into a table

To populate a table with data we can use an INSERT statement:

QSqlQuery query;

if(!query.exec("INSERT INTO people(name) VALUES('Eddie Guerrero')"))
	qWarning() << "ERROR: " << query.lastError().text();

This is another way to execute an SQL statement, using the exec function of a QSqlQuery object.

You might have noticed that the SQL statement does not include a value for the id field. That’s because that field is defined as INTEGER PRIMARY KEY which makes it auto-increment when no value is specified (or NULL is passed).

Querying a table

Querying a table of a database requires a SELECT statement and we’re going to use another way offered by QSqlQuery to execute it.

QSqlQuery query;

query.prepare("SELECT name FROM people WHERE id = ?");
query.addBindValue(mInputText->text().toInt());

The first step is defining the query with the function prepare and then passing a value to use as id with addBindValue. The latter function will replace the “?” in the query string with the value passed as parameter, an int number in this case. A query string can have multiple “?” which will be replaced by the following addBindValue calls based on their order. In this case we are passing the value of the input field converted to int.

After the query is prepared, it can be executed:

if(!query.exec())
	qWarning() << "ERROR: " << query.lastError().text();

Not passing any parameter to exec makes it execute the last prepared query.

After the query has been executed we can retrieve the result, if any:

if(query.first())
	mOutputText->setText(query.value(0).toString());
else
	mOutputText->setText("person not found");

After a SELECT query is executed we have to browse the records (result rows) returned to access the data. In this case we try to retrieve the first record calling the function first which returns true when the query has been successful and false otherwise.

It’s possible to access the fields of a result record using the function value which returns the field corresponding to the index passed as parameter. The object returned by the function value is a QVariant, which acts like a generic container (union) for most common Qt data types.

In this simple example we didn’t have to navigate through different records, but in case we needed to do that we can use a loop:

while(query.next())
{
	// access fields of current record
}

As expected, next will navigate through the records returning true until a valid record is found.

Source code

The full source code of this tutorial is available on GitHub and released under the Unlicense license.

Reference

Check out the Qt SQL C++ reference for more details on using Qt SQL and the SQLite website for detailed documentation on the database engine.

Conclusion

I hope you enjoyed this tutorial explaining how to embed a database in your application with SQLite and Qt. If you have any question feel free to leave a comment.

If you found it useful feel free to share it on social media using the social buttons below.

Subscribe

Don’t forget to subscribe to the blog newsletter to get notified of future posts.

You can also get updates following me on Google+, LinkedIn and Twitter.

4 Comments

  1. S.M.Mousavi

    Very simple and helpful.
    Is it possible to get result value of a column in current row using its name?
    Thank you.

    Reply
    1. Davide Coppola (Post author)

      yes, you can pass a QString to QSqlQuery::value().

      This is less efficient than passing an index as showed in the tutorial though,

      Reply
  2. mMeikZz

    why can’t i use mInputText? it seems there is something missing the compiler doesn’t know it.

    Reply
    1. Davide Coppola (Post author)

      have you downloaded my source code?

      What error are you getting?

      Reply

Leave a Comment

Your email address will not be published. Required fields are marked *