Skip to main content

Building: Migrations

Introduction​

We will try to create a working migrations console application called as tom in the terminal with the CMake and in the QtCreator IDE with the qmake.

The tom console application also expects the following folders structure, let's create them.

cd 
mkdir tom/tom
cd tom

TinyORM source tree contains the tom example application, you can inspire or look at the source code. Also TinyORM unit tests use a tom migrations internally to create the database structure, internally called as the tom migrations for unit tests.

All these three console applications the tom example, tom migrations for unit tests, and the application described in this tutorial have practically identical source code (the main.cpp file).

Prerequisites​

Any of the supported databases, tom is able to generate DDL queries for all these databases.

Install required dependencies and build the TinyORM library with the tom (it's enabled by default) as is described here and here.

vcpkg.json manifest​

Whole section about the vcpkg dependencies is described in the Install dependencies.

Create a vcpkg.json file with the following content. CMake example below uses this method.

cd tom
vim vcpkg.json
{
  "$schema": "https://raw.githubusercontent.com/microsoft/vcpkg/master/scripts/vcpkg.schema.json",
  "name": "tom",
  "version-semver": "0.1.0",
  "description": "Tom console for TinyORM.",
  "maintainers": "Silver Zachara <silver.zachara@gmail.com>",
  "supports": "!(uwp | arm | android | emscripten)",
  "dependencies": [
    "range-v3",
    "tabulate"
  ]
}
note

Only CMake via the toolchain file supports this method.

Source code​

Let's start in the tom project folder.

cd /tom/tom

Main file​

Create main.cpp source file.

vim main.cpp
tip

To paste a source code correctly in vim, press Shift + p.

And paste the following code.

#include <orm/db.hpp>

#include <tom/application.hpp>

#include "migrations/2014_10_12_000000_create_posts_table.hpp"

#include "seeders/databaseseeder.hpp"

using Orm::DatabaseManager;
using Orm::DB;

using TomApplication = Tom::Application;

using namespace Migrations; // NOLINT(google-build-using-namespace)
using namespace Seeders;    // NOLINT(google-build-using-namespace)

/*! Build the database manager instance and add a database connection. */
std::shared_ptr<DatabaseManager> setupManager();

/*! c++ main function. */
int main(int argc, char *argv[])
{
    try {
        // Ownership of the shared_ptr()
        auto db = setupManager();

        return TomApplication(argc, argv, std::move(db), "TOM_MIGRATIONS_ENV")
                .migrations<CreatePostsTable>()
                .seeders<DatabaseSeeder>()
                // Fire it up 🔥🚀✨
                .run();

    } catch (const std::exception &e) {

        TomApplication::logException(e);
    }

    return EXIT_FAILURE;
}

std::shared_ptr<DatabaseManager> setupManager()
{
    using namespace Orm::Constants; // NOLINT(google-build-using-namespace)

    // Ownership of the shared_ptr()
    return DB::create({
        {driver_,         QMYSQL},
        {host_,           qEnvironmentVariable("DB_MYSQL_HOST", H127001)},
        {port_,           qEnvironmentVariable("DB_MYSQL_PORT", P3306)},
        {database_,       qEnvironmentVariable("DB_MYSQL_DATABASE", EMPTY)},
        {username_,       qEnvironmentVariable("DB_MYSQL_USERNAME", EMPTY)},
        {password_,       qEnvironmentVariable("DB_MYSQL_PASSWORD", EMPTY)},
        {charset_,        qEnvironmentVariable("DB_MYSQL_CHARSET", UTF8MB4)},
        {collation_,      qEnvironmentVariable("DB_MYSQL_COLLATION", UTF8MB40900aici)},
        {timezone_,       TZ00},
        // Specifies what time zone all QDateTime-s will have
        {"qt_timezone",     QVariant::fromValue(Qt::UTC)},
        {prefix_,         EMPTY},
        {prefix_indexes,  false},
        {strict_,         true},
        {isolation_level, QStringLiteral("REPEATABLE READ")},
        {engine_,         InnoDB},
        {Version,         {}}, // Autodetect
        {options_,        QVariantHash()},
    },
        QStringLiteral("tinyorm_tom")); // shell:connection
}
tip

If you have defined more database connections then you can tag the lines with the database connection names with the // shell:connection comment and this connection names will be provided to the bash, zsh, pwsh completions for the --database= option 😎, example.

Migrations​

If you have already built the tom application then you can generate a migrations using the make:migration command 😎.

tom make:migration create_posts_table

Below is the expected folders structure for the migrations. The migrations.pri file is used only by the qmake build system and is not needed with CMake builds.

tom/
└── database/
    ├── migrations/
    ├── seeders/
    ├── migrations.pri
    └── seeders.pri

Let's create the first migration manually.

mkdir database/migrations

vim database/migrations/2014_10_12_000000_create_posts_table.hpp

And paste the following code.

#pragma once

#include <tom/migration.hpp>

namespace Migrations
{

    struct CreatePostsTable : Migration
    {
        /*! Filename of the migration file. */
        T_MIGRATION

        /*! Run the migrations. */
        void up() const override
        {
            Schema::create("posts", [](Blueprint &table)
            {
                table.id();

                table.string(NAME);
                table.timestamps();
            });
        }

        /*! Reverse the migrations. */
        void down() const override
        {
            Schema::dropIfExists("posts");
        }
    };

} // namespace Migrations
info

If you want, you can also build the tom application without the migrations, simply comment out the migrations method and the corresponding #include "migrations/xyz.hpp" files.

Seeders​

If you have already built the tom application then you can generate a seeder using the make:seeder command 😎.

tom make:seeder PostSeeder

The expected folders structure is described a few paragraphs above. The seeders.pri file is used only by the qmake build system and is not needed with CMake builds.

Let's create the root seeder class manually.

mkdir database/seeders

vim database/seeders/databaseseeder.hpp

And paste the following code.

#pragma once

#include <tom/seeder.hpp>

namespace Seeders
{

    /*! Main database seeder. */
    struct DatabaseSeeder : Seeder
    {
        /*! Run the database seeders. */
        void run() override
        {
            DB::table("posts")->insert({
                {{"name", "1. post"}},
                {{"name", "2. post"}},
            });
        }
    };

} // namespace Seeders
tip

You can create more seeder classes like this and use the call<>() method to invoke them as is described in the Calling Additional Seeders section.

Migrations with CMake​

Create a folder for the CMake build.

cd ..
mkdir tom-builds-cmake/build-debug

cd tom

CMake project​

Create CMakeLists.txt file with the following content. I leave the comments in the CMakeLists.txt file because it's not as simple as the Hello world example; to make it clear what's going on.

cmake_minimum_required(VERSION VERSION 3.22...3.25 FATAL_ERROR)

# Specify the C++ standard
set(CMAKE_CXX_STANDARD 20)
set(CMAKE_CXX_STANDARD_REQUIRED ON)
set(CMAKE_CXX_EXTENSIONS OFF)

set(CMAKE_AUTOMOC ON)

# Tom command-line application
# ---

set(Tom_ns tom)
set(Tom_target tom)

file(REAL_PATH "../../TinyORM" TinyMainDir)

set(TinyOrmSourceDir "${TinyMainDir}/TinyORM")
set(TinyOrmBuildDir "${TinyMainDir}/TinyORM-builds-cmake/build-debug")

# TinyORM CMake modules (needed to set the executable version and RC file on Windows)
list(APPEND CMAKE_MODULE_PATH "${TinyOrmSourceDir}/cmake/CommonModules")

# build tree
list(APPEND CMAKE_PREFIX_PATH "${TinyOrmBuildDir}")

# Initialize Project Version
# ---

include(TinyHelpers)
tiny_read_version(TINY_VERSION
    TINY_VERSION_MAJOR TINY_VERSION_MINOR TINY_VERSION_PATCH TINY_VERSION_TWEAK
    VERSION_HEADER "${TinyOrmSourceDir}/tom/include/tom/version.hpp"
    PREFIX TINYTOM
    HEADER_FOR "${Tom_ns}"
)

# Basic project
# ---

project(${Tom_ns}
    DESCRIPTION "Tom console for TinyORM"
    HOMEPAGE_URL "https://www.tinyorm.org"
    LANGUAGES CXX
    VERSION ${TINY_VERSION}
)

# Tom command-line application
# ---

add_executable(${Tom_target}
    main.cpp
)
add_executable(${Tom_ns}::${Tom_target} ALIAS ${Tom_target})

# Tom command-line application specific configuration
# ---

set_target_properties(${Tom_target}
    PROPERTIES
        C_VISIBILITY_PRESET "hidden"
        CXX_VISIBILITY_PRESET "hidden"
        VISIBILITY_INLINES_HIDDEN YES
        VERSION ${PROJECT_VERSION}
)

target_include_directories(${Tom_target} PRIVATE
    "$<BUILD_INTERFACE:${PROJECT_SOURCE_DIR}/database>"
)

# Tom command-line application defines
# ---

target_compile_definitions(${Tom_target}
    PRIVATE
        PROJECT_TOM
)

# Windows resource and manifest files
# ---

# Find icons, tom/version.hpp, and Windows manifest file for MinGW
if(CMAKE_SYSTEM_NAME STREQUAL "Windows")
    tiny_set_rc_flags("-I \"${TinyOrmSourceDir}/tom/resources\"")
endif()

include(TinyResourceAndManifest)
tiny_resource_and_manifest(${Tom_target}
    OUTPUT_DIR "${TINY_BUILD_GENDIR}/tmp/"
    RESOURCES_DIR "${TinyOrmSourceDir}/tom/resources"
)

# Resolve and link dependencies
# ---

find_package(QT NAMES Qt6 Qt5 COMPONENTS Core REQUIRED)
find_package(Qt${QT_VERSION_MAJOR} COMPONENTS Core REQUIRED)
find_package(TinyOrm 0.16.0 CONFIG REQUIRED)

# Unconditional dependencies
target_link_libraries(${Tom_target}
    PRIVATE
        Qt${QT_VERSION_MAJOR}::Core
        TinyOrm::TinyOrm
)

Build migrations​

Now you are ready to configure tom CMake application. Don't forget to prepare the build environment with the qtenv6.ps1 command if you are building with the msvc.

cd ../tom-builds-cmake/build-debug
cmake.exe `
-S "/tom/tom" `
-B "/tom/tom-builds-cmake/build-debug" `
-G 'Ninja' `
-D CMAKE_BUILD_TYPE:STRING='Debug' `
-D CMAKE_TOOLCHAIN_FILE:FILEPATH="/vcpkg/scripts/buildsystems/vcpkg.cmake" `
-D CMAKE_INSTALL_PREFIX:PATH="/tmp/tom"

And build.

cmake --build . --target all

Execute migrations​

Do not forget to add TinyOrm0d.dll on the path on Windows and on the LD_LIBRARY_PATH on Linux, so tom application can find it during execution, as is described here.

$env:Path = "\TinyORM\TinyORM-builds-cmake\build-debug;" + $env:Path

Execute tom application.

.\tom.exe migrate:status

The output will look something like this.

Tom migrations - migrate:status command output

See also the final thoughts on how to verify the tom executable file properties.

Happy migrating 🎉👌

Migrations with qmake​

Create a folder for the qmake build.

cd /tom

mkdir tom-builds-qmake

The source code is the same as for the Migrations with CMake console application.

qmake project​

Create tom.pro qmake file with the following content.

cd tom
vim tom.pro
tip

To paste a source code correctly in vim, press Shift + p.

QT *= core sql
QT -= gui

TEMPLATE = app
TARGET = tom

CONFIG *= console

DEFINES += PROJECT_TOM

SOURCES += $$PWD/main.cpp

# Database migrations
include($$PWD/database/migrations.pri)
# Database seeders
include($$PWD/database/seeders.pri)

# Configure TinyORM library for the migrations purposes
include($$TINY_MAIN_DIR/TinyORM/qmake/tom.pri)

# vcpkg - range-v3 and tabulate
win32-msvc: \
    INCLUDEPATH += $$quote($$TINY_VCPKG_INSTALLED/x64-windows/include/)
mingw: \
    QMAKE_CXXFLAGS += -isystem $$shell_quote($$TINY_VCPKG_INSTALLED/x64-mingw-dynamic/include/)
unix:!macx: \
    QMAKE_CXXFLAGS += -isystem $$shell_quote($$TINY_VCPKG_INSTALLED/x64-linux/include/)
caution

The exact folders structure is crucial in this example because the paths to the TinyORM source and build folders are relative.

tip

On Linux -isystem marks the directory as a system directory, it prevents warnings.

On Windows you can use QMAKE_CXXFLAGS_WARN_ON = -external:anglebrackets -external:W0, it applies a warning level 0 to the angel bracket includes; #include <file>.

Configure using .qmake.conf​

To correctly set a file properties as the version, description, ... you have to provide the path to the TinyORM qmake features (.prf files) which handle this correctly, this path is provided by the QMAKEFEATURES variable and can be set only in the .qmake.conf file.

Create .qmake.conf in the tom application root folder with the following content.

TINY_MAIN_DIR = $$clean_path($$PWD/../../TinyORM)
# Name of this qmake variable is crucial
TINYORM_BUILD_TREE = $$quote($$TINY_MAIN_DIR/TinyOrm-builds-qmake/build-TinyOrm-Desktop_Qt_6_3_1_MSVC2019_64bit-Debug)
# vcpkg - range-v3 and tabulate
TINY_VCPKG_INSTALLED = $$clean_path($$PWD/../../../vcpkg/installed)

QMAKEFEATURES *= $$quote($$TINY_MAIN_DIR/TinyORM/qmake/features)
info

Configuring with the .qmake.conf file has one big advantage that is that you do not have to modify the project files.

Migrations source files​

Create database/migrations.pri file and paste the following code.

INCLUDEPATH += $$PWD

HEADERS += \
    $$PWD/migrations/2014_10_12_000000_create_posts_table.hpp \

Seeders source files​

Create database/seeders.pri file and paste the following code.

INCLUDEPATH += $$PWD

HEADERS += \
    $$PWD/seeders/databaseseeder.hpp \

Build migrations​

tip

I recommend creating a new Session in the QtCreator IDE as is described here.

Now you can open the tom.pro project in the QtCreator IDE.

This will open the Configure Project tab, select some kit and update build folder paths to meet our folders structure or like you want.

tom - QtCreator - Configure Project

You are ready to configure build options, hit Ctrl+5 to open Project Settings tab and select Build in the left sidebar to open the Build Settings, it should look similar to the following picture.

tom - QtCreator - Build Settings

Disable QML debugging and profiling and Qt Quick Compiler, they are not used.

In the left sidebar open Dependencies and check TinyOrm and Synchronize configuration, this setting ensures that the current project will be rebuilt correctly when the TinyORM library source code changes.

Everything is ready to build, you can press Ctrl+b to build the project.

Execute migrations​

The QtCreator takes care about all the necessary configuration, sets up the build environment correctly and also prepends dependency libraries on the path on Windows and on the LD_LIBRARY_PATH on Linux.

Only one thing you might want to change is to run the tom application in the new terminal window. To do so, hit Ctrl+5 to open the Project Settings tab and select Run in the left sidebar to open the Run Settings, then in the Run section select the Run in terminal checkbox.

You can also set the Command line arguments in this Run section, eg. the migrate:status.

To execute the tom application press Ctrl + r.

The output will look something like this.

Tom migrations - migrate:status command output

Happy migrating 🎉👌

Finish​

As the last thing, you can check that all the file properties were correctly set by the rc compiler.

Find the tom.exe file and press Alt + Enter to open the file properties. To check the executable manifest you can use eg. the Resource Hacker.

tom.exe file properties detail