Skip to main content

Database: Getting Started

Introduction​

Almost every modern application interacts with a database. TinyORM makes interacting with a database extremely simple using raw SQL, a fluent query builder, and the TinyORM. Currently, TinyORM provides first-party support for four databases:

TinyORM internally uses QtSql module, you can look for supported databases.

note

TinyORM's code is ready and designed to simply add support for the SQL Server.

Configuration​

You can create and configure a new database connection using the create method provided by the DB facade:

#include <orm/db.hpp>

using Orm::DB;

// Ownership of a shared_ptr()
auto manager = DB::create({
    {"driver",          "QMYSQL"},
    {"host",            qEnvironmentVariable("DB_HOST", "127.0.0.1")},
    {"port",            qEnvironmentVariable("DB_PORT", "3306")},
    {"database",        qEnvironmentVariable("DB_DATABASE", "")},
    {"username",        qEnvironmentVariable("DB_USERNAME", "root")},
    {"password",        qEnvironmentVariable("DB_PASSWORD", "")},
    {"charset",         qEnvironmentVariable("DB_CHARSET", "utf8mb4")},
    {"collation",       qEnvironmentVariable("DB_COLLATION", "utf8mb4_0900_ai_ci")},
    {"timezone",        "+00:00"},
    /* Specifies what time zone all QDateTime-s will have, the overridden default is
       the Qt::UTC, set to the Qt::LocalTime or QtTimeZoneType::DontConvert to use
       the system local time. */
    {"qt_timezone",     QVariant::fromValue(Qt::UTC)},
    {"prefix",          ""},
    {"prefix_indexes",  false},
    {"strict",          true},
    {"engine",          "InnoDB"},
    {"options",         QVariantHash()},
});
#include <orm/db.hpp>
#include <orm/utils/configuration.hpp>

using Orm::DB;

using ConfigUtils = Orm::Utils::Configuration;

using namespace Orm::Constants; // NOLINT(google-build-using-namespace)

// Ownership of a shared_ptr()
auto manager = DB::create({
    {driver_,         QMYSQL},
    {host_,           qEnvironmentVariable("DB_HOST", H127001)},
    {port_,           qEnvironmentVariable("DB_PORT", P3306)},
    {database_,       qEnvironmentVariable("DB_DATABASE", EMPTY)},
    {username_,       qEnvironmentVariable("DB_USERNAME", ROOT)},
    {password_,       qEnvironmentVariable("DB_PASSWORD", EMPTY)},
    {charset_,        qEnvironmentVariable("DB_CHARSET", UTF8MB4)},
    {collation_,      qEnvironmentVariable("DB_COLLATION", UTF8MB40900aici)},
    // SSL-related
    {ssl_ca,          QStringLiteral("C:/mysql/data/ca.pem")},
    {ssl_cert,        QStringLiteral("C:/mysql/data/client-cert.pem")},
    {ssl_key,         QStringLiteral("C:/mysql/data/client-key.pem")},
    {ssl_mode,        VerifyCA},
    // Or
//    {options,         ConfigUtils::mysqlSslOptions()},
    {timezone_,       TZ00},
    /* Specifies what time zone all QDateTime-s will have, the overridden default is
       the Qt::UTC, set to the Qt::LocalTime or QtTimeZoneType::DontConvert to use
       the system local time. */
    {qt_timezone,     QVariant::fromValue(Qt::UTC)},
    {prefix_,         EMPTY},
    {prefix_indexes,  false},
    {strict_,         true},
//    {isolation_level, QStringLiteral("REPEATABLE READ")}, // MySQL default is REPEATABLE READ for InnoDB
    {engine_,         InnoDB},
    {Version,         {}}, // Autodetect
    {options_,        QVariantHash()},
    // Examples
//    {options_,        QStringLiteral("MYSQL_OPT_CONNECT_TIMEOUT = 1 ; MYSQL_OPT_READ_TIMEOUT=1")},
//    {options_,        QVariantHash {{QStringLiteral("MYSQL_OPT_CONNECT_TIMEOUT"), 1},
//                                    {QStringLiteral("MYSQL_OPT_READ_TIMEOUT"),    1}}},
});

The first argument is configuration hash which is of type QVariantHash and the second argument specifies the name of the connection, this connection will also be a default connection. You can configure multiple database connections at once and choose the needed one before executing SQL query, section Using Multiple Database Connections describes how to create and use multiple database connections.

You may also configure connection options by options key as QVariantHash or QString, you can pass any connection options supported by QSqlDatabase.

You can also configure Transaction Isolation Levels for MySQL connection with the isolation_level configuration option.

The version option is relevant only for the MySQL connections and you can save/avoid one database query (select version()) if you provide it manually. On the base of this version will be decided which session variables will be set if strict mode is enabled and whether to use an alias during the upsert method call.

Breaking values are as follows; use an upsert alias on the MySQL >=8.0.19 and remove the NO_AUTO_CREATE_USER sql mode on the MySQL >=8.0.11 if the strict mode is enabled.

info

A database connection is resolved lazily, which means that the connection configuration is only saved after the DB::create method call. The connection will be resolved after you run some query or you can create it using the DB::connection method.

tip

You can also use predefined string constants to avoid unnecessary QString instantiations, as used in the tom migrations example.

SQLite Configuration​

SQLite databases are contained within a single file on your filesystem. You can create a new SQLite database using the touch command in your terminal: touch database.sqlite3. After the database has been created, you may configure SQLite database connection:

#include <orm/db.hpp>

// Ownership of a shared_ptr()
auto manager = DB::create({
    {"driver",    "QSQLITE"},
    {"database",  qEnvironmentVariable("DB_DATABASE", "/absolute/path/to/database.sqlite3")},
    {"foreign_key_constraints", qEnvironmentVariable("DB_FOREIGN_KEYS", "true")},
    {"check_database_exists",   true},
    {"prefix",                  ""},
});

The database configuration value is the absolute path to the database. To enable foreign key constraints for SQLite connections, you should set the foreign_key_constraints configuration value to true, if this configuration value is not set, then the default of the SQLite driver will be used (currently the default is disabled).

If the check_database_exists configuration value is set to the true value, then the database connection throws an Orm::InvalidArgumentError exception, when the SQLite database file doesn't exist. If it is set to the false value and the SQLite database file doesn't exist, then it will be created for you by SQLite driver. The default value is true.

SSL Connections​

SSL connections are supported for the MySQL and PostgreSQL databases. They can be set using the options configuration option.

info

This feature is heavily dependent on the underlying QSqlDatabase module. What means that you can pass the same connection options to the TinyORM that the QSqlDatabase accepts.

MySQL​

You have to pass the SSL_CA, SSL_CERT, SSL_KEY, and MYSQL_OPT_SSL_MODE options.

#include <orm/db.hpp>

using Orm::DB;

// Ownership of a shared_ptr()
auto manager = DB::create({
    {"driver",  "QMYSQL"},
    {"host",    qEnvironmentVariable("DB_MYSQL_HOST", "127.0.0.1")},
    ...
    {"options", QVariantHash({{"SSL_CA",   "C:/mysql/data/ca.pem"},
                              {"SSL_CERT", "C:/mysql/data/client-cert.pem"},
                              {"SSL_KEY",  "C:/mysql/data/client-key.pem"},
                              {"MYSQL_OPT_SSL_MODE", "VERIFY_CA"}})},
});

You may also use the ConfigUtils::mysqlSslOptions() or the ConfigUtils::insertMySqlSslOptions() methods to insert these options for you and define them using the DB_MYSQL_SSL_CA, DB_MYSQL_SSL_CERT, DB_MYSQL_SSL_KEY, and DB_MYSQL_SSL_MODE environment variables.

#include <orm/db.hpp>
#include <orm/utils/configuration.hpp>

using Orm::DB;

using ConfigUtils = Orm::Utils::Configuration;

// Ownership of a shared_ptr()
auto manager = DB::create({
    {"driver",  "QMYSQL"},
    {"host",    qEnvironmentVariable("DB_MYSQL_HOST", "127.0.0.1")},
    ...
    {"options", ConfigUtils::mysqlSslOptions()},
});

You can define these SSL-related options in the top-level configuration, they will be copied to the options option hash during configuration parsing. The top-level configuration takes precedence and overwrites the options in the options hash.

#include <orm/db.hpp>

using Orm::DB;

// Ownership of a shared_ptr()
auto manager = DB::create({
    {"driver",   "QMYSQL"},
    {"host",     qEnvironmentVariable("DB_MYSQL_HOST", "127.0.0.1")},
    ...
    {"SSL_CA",   "C:/mysql/data/ca.pem"},
    {"SSL_CERT", "C:/mysql/data/client-cert.pem"},
    {"SSL_KEY",  "C:/mysql/data/client-key.pem"},
    {"MYSQL_OPT_SSL_MODE", "VERIFY_CA"},
});
tip

You can take a look at the GitHub actions how the MySQL certificates are generated in the CI pipeline for Windows and Linux.

tip

You can also pass the QString to the options configuration separated by the ; semicolon character and use the = to assign values.

PostgreSQL​

You have to pass the sslmode or the deprecated requiressl options.

#include <orm/db.hpp>

using Orm::DB;

// Ownership of a shared_ptr()
auto manager = DB::create({
    {"driver",  "QPSQL"},
    {"host",    qEnvironmentVariable("DB_PGSQL_HOST", "127.0.0.1")},
    ...
    {"options", QVariantHash({{"sslmode", "verify-full"}})},
});

And place your client certificates to the ~/.postgres/ on Linux and $env:APPDATA/postgres/ on Windows. Everything is described in the PostgreSQL's libpq client and server documentation.

If you want to keep your client certificates in your own location, you can set the sslcert, sslkey, and sslrootcert options.

#include <orm/db.hpp>

using Orm::DB;

// Ownership of a shared_ptr()
auto manager = DB::create({
    {"driver",  "QPSQL"},
    {"host",    qEnvironmentVariable("DB_PGSQL_HOST", "127.0.0.1")},
    ...
    {"options", QVariantHash({{"sslmode",     "verify-full"},
                              {"sslcert",     "C:/example/postgres.crt"},
                              {"sslkey",      "C:/example/postgres.key"},
                              {"sslrootcert", "C:/example/root.crt"}})},
});

You can define these SSL-related options in the top-level configuration, they will be copied to the options option hash during a configuration parsing. The top-level configuration takes precedence and overwrites the options in the options hash.

#include <orm/db.hpp>

using Orm::DB;

// Ownership of a shared_ptr()
auto manager = DB::create({
    {"driver",  "QPSQL"},
    {"host",    qEnvironmentVariable("DB_PGSQL_HOST", "127.0.0.1")},
    ...
    {"sslmode",     "verify-full"},
    {"sslcert",     "C:/example/postgres.crt"},
    {"sslkey",      "C:/example/postgres.key"},
    {"sslrootcert", "C:/example/root.crt"},
});

You may also use the ConfigUtils::postgresSslOptions() or the ConfigUtils::insertPostgresSslOptions() methods to insert the sslmode, sslcert, sslkey, and sslrootcert options for you and define them using the DB_PGSQL_SSLMODE, DB_PGSQL_SSLCERT, DB_PGSQL_SSLKEY, and DB_PGSQL_SSLROOTCERT environment variable.

#include <orm/db.hpp>
#include <orm/utils/configuration.hpp>

using Orm::DB;

using ConfigUtils = Orm::Utils::Configuration;

// Ownership of a shared_ptr()
auto manager = DB::create({
    {"driver",  "QPSQL"},
    {"host",    qEnvironmentVariable("DB_PGSQL_HOST", "127.0.0.1")},
    ...
    {"options", ConfigUtils::postgresSslOptions()},
});
info

The PostgreSQL's libpq client library provides the PGSSLMODE, PGSSLCERT, PGSSLKEY, and PGSSLROOTCERT environment variables, so you don't have to use TinyORM's options configuration and may use these environment variables instead.

tip

You can take a look at the GitHub actions how the PostgreSQL certificates are generated in the CI pipeline for Windows and Linux.

Running SQL Queries​

Once you have configured your database connection, you may run queries using the DB facade. The DB facade provides methods for each type of query: select, update, insert, delete, and statement.

Running A Select Query​

To run a basic SELECT query, you may use the select method on the DB facade:

auto users = DB::select("select * from users where active = ?", 1);

The first argument passed to the select method is the SQL query, while the second argument is any parameter bindings that need to be bound to the query. Typically, these are the values of the where clause constraints. Parameter binding provides protection against SQL injection.

The select method returns a QSqlQuery containing the results of the query, where each result can be accessed by QSqlQuery::next method. Look into the QSqlQuery documentation on how to obtain results from the "query". You may access each column's value by QSqlQuery::value method. The first bool return value is the value returned from QSqlQuery::exec method:

#include <QDebug>

#include <orm/db.hpp>

auto users = DB::select("select * from users");

while(users.next())
    qDebug() << users.value("name").toString();

Selecting Scalar Values​

Sometimes your database query may result in a single, scalar value. Instead of being required to retrieve the query's scalar result from a record instance, TinyORM allows you to retrieve this value directly using the scalar shortcut method:

#include <orm/db.hpp>

auto states = DB::scalar(
    "select count(case when state = 'pending' then 1 end) as states "
      "from comments"
);

// With binding
auto states = DB::scalar(
    "select count(case when state = ? then 1 end) as states from comments",
    {"pending"}
);

Running An Insert Statement​

To execute an insert statement, you may use the insert method on the DB facade. Like select, this method accepts the SQL query as its first argument and bindings as its second argument and returns QSqlQuery:

#include <orm/db.hpp>

DB::insert("insert into users (id, name) values (?, ?)", {1, "Marc"});

Running An Update Statement​

The update method should be used to update existing records in the database. The number of rows affected by the statement and QSqlQuery is returned by the method as std::tuple<int, QSqlQuery>:

#include <QDateTime>

#include <orm/db.hpp>

auto [affected, query] = DB::update(
    "update users set updated_at = ? where name = ?",
    {QDateTime::currentDateTimeUtc(), "Anita"}
);

if (!affected)
    qDebug() << "Any record was updated.";

Running A Delete Statement​

The remove method should be used to delete records from the database. Like update, the number of affected rows and QSqlQuery will be returned by the method as std::tuple<int, QSqlQuery>:

#include <orm/db.hpp>

auto [affected, query] = DB::remove("delete from users");
note

delete can not be used as the method name because it is the reserved word.

Running A General Statement​

Some database statements do not return any value. For these types of operations, you may use the statement method on the DB facade:

DB::statement("drop table users");
tip

DB::statement method should be used for DDL queries, don't use it for "select" queries because it internally calls recordsHaveBeenModified method.

Running An Unprepared Statement​

Sometimes you may want to execute an SQL statement without binding any values. You may use the DB facade's unprepared method to accomplish this:

DB::unprepared("update users set votes = 100 where name = 'Dries'");
warning

Since unprepared statements do not bind parameters, they may be vulnerable to SQL injection. You should never allow user controlled values within an unprepared statement.

Implicit Commits​

When using the DB facade's statement methods within transactions, you must be careful to avoid statements that cause implicit commits. These statements will cause the database engine to indirectly commit the entire transaction, leaving TinyORM unaware of the database's transaction level. An example of such a statement is creating a database table:

DB::statement("create table users (name varchar(255) null)");

Please refer to the MySQL manual for a list of all statements that trigger implicit commits.

Using Multiple Database Connections​

You can configure multiple database connections at once during DatabaseManager instantiation using the DB::create overload, where the first argument is a hash of multiple connections and is of type QHash<QString, QVariantHash> and the second argument is the name of the default connection:

#include <orm/db.hpp>

// Ownership of a shared_ptr()
auto manager = DB::create({
    {"mysql", {
        {"driver",    "QMYSQL"},
        {"host",      qEnvironmentVariable("DB_MYSQL_HOST", "127.0.0.1")},
        {"port",      qEnvironmentVariable("DB_MYSQL_PORT", "3306")},
        {"database",  qEnvironmentVariable("DB_MYSQL_DATABASE", "")},
        {"username",  qEnvironmentVariable("DB_MYSQL_USERNAME", "root")},
        {"password",  qEnvironmentVariable("DB_MYSQL_PASSWORD", "")},
        {"charset",   qEnvironmentVariable("DB_MYSQL_CHARSET", "utf8mb4")},
        {"collation", qEnvironmentVariable("DB_MYSQL_COLLATION", "utf8mb4_0900_ai_ci")},
        {"strict",    true},
        {"options",   QVariantHash()},
    }},
    {"sqlite", {
        {"driver",    "QSQLITE"},
        {"database",  qEnvironmentVariable("DB_SQLITE_DATABASE", "")},
        {"foreign_key_constraints", qEnvironmentVariable("DB_SQLITE_FOREIGN_KEYS", "true")},
        {"check_database_exists",   true},
        {"prefix",                  ""},
    }},
}, "mysql");

If your application needs to use multiple connections, you may access each connection via the connection method provided by the DB facade. The connection name passed to the connection method should correspond to one of the connections key listed in your configuration:

#include <orm/db.hpp>

auto query = DB::connection("mysql_test").select(...);

You may access the raw underlying QSqlQuery instance of a connection using the getQtQuery method on a connection instance:

auto query = DB::connection().getQtQuery();

Or you can use the shortcut method qtQuery provided by the DB facade:

auto query = DB::qtQuery();

Database Transactions​

Manually Using Transactions​

If you would like to begin a transaction manually and have complete control over rollbacks and commits, you may use the beginTransaction method provided by the DB facade:

#include <orm/db.hpp>

DB::beginTransaction();

You can rollback the transaction via the rollBack method:

DB::rollBack();

Lastly, you can commit a transaction via the commit method:

DB::commit();

All transaction methods accept a connection name as the optional argument:

DB::beginTransaction("mysql_test");
tip

The DB facade's transaction methods control the transactions for both the query builder and TinyORM.

Multi-threading support​

The TinyORM supports multi-threading for the MSVC and GCC on Linux compilers. Multi-threading is disabled for the Clang <14.0.3 compiler on MSYS2, Clang <14.0.4 on Linux and for the GCC compiler on MSYS2. The reason are bugs in the TLS wrapper that is generated by the thread_local keyword.

A connection can only be used from within the thread that created it. Moving connections between threads or creating queries from a different thread where the connection was created is not supported.

In addition, the third party libraries used by the QSqlDrivers can impose further restrictions on using the SQL Module in a multithreaded program.

In short, if you create a DB::connection in some thread then you have to use this connection only from this particular thread and of course all queries that will be executed on this connection.

If you want to execute some query from another thread for the same connection then you have to create a new connection first and if you have a new connection you can send a query from this new thread to the database.

warning

The schema builder and migrations don't support multi-threading.