Skip to main content

Building: Migrations

Introduction​

Stability: 2 - Stable

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 build systems.

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).

info

Here is the finished Tom example application with a few migrations and seeders.

note

The tom is able to generate DDL queries for all the supported databases.

info

You can see the Tom showcase image of how the resulting tom console application will look like.

Install dependencies​

First, install the vcpkg package manager as is described here.

The range-v3 and tabulate libraries are required dependencies because TinyORM uses them in header files, you have to install them before you can use TinyORM. The tabulate library is only needed in the tom migrations it's used by the migrate:status command.

There are two ways how to install the range-v3 and tabulate libraries using vcpkg.

Also, don't forget to build the TinyORM library with the tom source code enabled (it's enabled by default) as is described here.

Using vcpkg.json (manifest mode)​

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

cd tom
vim vcpkg.json
vcpkg.json
{
  "$schema": "https://raw.githubusercontent.com/microsoft/vcpkg-tool/main/docs/vcpkg.schema.json",
  "name": "tom",
  "version-semver": "0.1.0",
  "maintainers": "Silver Zachara <silver.zachara@gmail.com>",
  "description": "Tom console application for TinyORM C++ library",
  "homepage": "https://github.com/silverqx/TinyORM",
  "documentation": "https://www.tinyorm.org/building/migrations",
  "supports": "!(uwp | arm | android | emscripten | osx | ios | xbox | freebsd | openbsd | wasm32)",
  "dependencies": [
    "range-v3",
    "tabulate"
  ]
}
note

Only CMake via the toolchain file supports this method.

Using vcpkg install (manually)​

This method can be used with both CMake and qmake.

cd ../../vcpkg

vcpkg search range-v3
vcpkg search tabulate
vcpkg install range-v3 tabulate
vcpkg list

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.

main.cpp
#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)

/*! Create the database manager instance and add a database connection. */
static std::shared_ptr<DatabaseManager> setupDatabaseManager(); // NOLINT(misc-use-anonymous-namespace)

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

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

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

        TomApplication::logException(e);
    }

    return EXIT_FAILURE;
}

std::shared_ptr<DatabaseManager> setupDatabaseManager()
{
    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, the overridden default is
           the QTimeZone::UTC, set to the QTimeZone::LocalTime or
           QtTimeZoneType::DontConvert to use the system local time. */
        {qt_timezone, QVariant::fromValue(QTimeZone::UTC)},
        {strict_,     true},
    },
        QStringLiteral("tinyorm_tom_mysql")); // 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.

database/migrations/2014_10_12_000000_create_posts_table.hpp
#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
tip

The TinyORM source tree contains the CreatePostsTable example migration that also acts as the full-fledged example migration. It has defined and also nicely commented all possible features that migration classes can use or define.

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.

database/seeders/databaseseeder.hpp
#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

The TinyORM source tree contains the DatabaseSeeder root seeder example class that also acts as the full-fledged example seeder. It has defined and also nicely commented all possible features that seeder classes can use or define.

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.

CMakeLists.txt
cmake_minimum_required(VERSION VERSION 3.22...3.31 FATAL_ERROR)

# Specify the C++ standard
set(CMAKE_CXX_STANDARD 20)
set(CMAKE_CXX_STANDARD_REQUIRED ON)
set(CMAKE_CXX_EXTENSIONS OFF)
# Set the AUTOMOC property explicitly only when needed (eg. unit tests need AUTOMOC)
set(CMAKE_AUTOMOC OFF)

# Initialize variables
# ---

set(Tom_ns tom)
set(Tom_target tom)

# Path to the PARENT TinyORM directory❗
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 application for TinyORM C++ library"
    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_TMPDIR}/"
    RESOURCES_DIR "${TinyOrmSourceDir}/tom/resources/"
)

# Resolve and link dependencies
# ---

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

# Unconditional dependencies
target_link_libraries(${Tom_target}
    PRIVATE
        # Never use versionless Qt targets
        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_CXX_SCAN_FOR_MODULES:BOOL=OFF `
-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.

tom.pro
QT -= gui

TEMPLATE = app
TARGET = tom

CONFIG *= cmdline

DEFINES *= PROJECT_TOM

SOURCES += $$PWD/main.cpp

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

# Auto-configure TinyORM library for the migrations purposes 🔥
include($$TINY_MAIN_DIR/TinyORM/qmake/tom.pri)
warning

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

warning

Please pay special attention to letter casing in paths, especially TinyOrm vs TinyORM!

Auto-configure using .qmake.conf and .env​

If you want to have properly configured DEFINES (C preprocessor macros), have Qt headers marked as system headers, or eg. have properly set properties of an executable file such as version and description, then you need to specify a path to the TinyORM qmake features (.prf files) which handle this correctly; this path is provided by the QMAKEFEATURES variable and can only be set in the .qmake.conf file.

tip

Read the Consume TinyOrm library (qmake) section, as everything that is described in that section applies here as well.

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

.qmake.conf
# Path to the PARENT folder of the TinyORM source folder
TINY_MAIN_DIR    = $$clean_path($$PWD/../../TinyORM/)
# To find .env and .env.$$QMAKE_PLATFORM files
TINY_DOTENV_ROOT = $$PWD
# Path to the current build tree (used to guess the TinyORM build tree)
#TINY_BUILD_TREE  = $$shadowed($$PWD)

# To find .prf files, needed by eg. CONFIG += tiny_system_headers inline/extern_constants
QMAKEFEATURES *= $$quote($$TINY_MAIN_DIR/TinyORM/qmake/features)

Then, create a .env.(win32|unix|mingw) file in the tom application root folder with the following content.

# Names and values of these qmake variables are crucial, they are used in the tom.pro
# Please pay special attention to letter casing in paths, especially TinyOrm vs TinyORM!

# Path to the TinyORM build folder
TINYORM_BUILD_TREE = $$quote($$TINY_MAIN_DIR/TinyORM-builds-qmake/build-TinyORM-Desktop_Qt_6_7_2_MSVC2022_64bit-Debug/)

# Path to the vcpkg - range-v3 and tabulate
# Will use the TINY_VCPKG_ROOT or VCPKG_ROOT environment variable if is empty
TINY_VCPKG_ROOT = $$clean_path($$PWD/../../../vcpkg/)
TINY_VCPKG_TRIPLET = x64-windows

# Enable ccache wrapper
#CONFIG *= ccache

Don't forget to update the TINYORM_BUILD_TREE and TINY_VCPKG_ROOT folder paths to your needs if you are not using the recommended Folders structure.

You can use the Partial guessing of the TINYORM_BUILD_TREE if you don't like to specify it manually. Just comment out the TINYORM_BUILD_TREE and uncomment the TINY_BUILD_TREE = $$shadowed($$PWD) in the .qmake.conf file.

tip

You can entirely avoid the .env files, just move the TINYORM_BUILD_TREE to the .qmake.conf or remove it by help of Partial guessing of the TINYORM_BUILD_TREE and set the VCPKG_ROOT environment variable at system level as is described in Set up vcpkg environment.

info

Configuring by the .qmake.conf and .env files has one big advantage, which is that you don't have to modify the project files.

Migrations source files​

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

database/migrations.pri
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.

database/seeders.pri
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
tip

You can force the QtCreator to generate a build folders structure as is described here.

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 project 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 of all the necessary configurations, sets up the build environment correctly, and also prepends dependency libraries on the system path on Windows and on the LD_LIBRARY_PATH on Linux.

The only 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 very similar to this if you add more migrations.

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