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 three 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 new database connection by create method provided by 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"},
{"prefix", ""},
{"strict", true},
{"engine", InnoDB},
{"version", {}}, // Autodetect
{"options", QVariantHash()},
{"prefix_indexes", true},
{"isolation_level", "REPEATABLE READ"},
});

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.

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.

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'");
caution

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.

caution

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