Migrations

Migrations

A migration-tracking system is built-in to the core of the library that allows tracking of changes that a user makes to their models.

After defining models, as shown in the previous chapter on model definitions, and registering them using the REGISTER_MODEL macro, tracking them can be done as shown below:

#include <strata/models.hpp>
#include <strata/db_adapters.hpp>

//Model definitions go here or may be defined in a separate header file and included here.

int main(){

  Model model {};
  nlohmann::json model_renames {};
  nlohmann::json column_renames {};
  std::string sql_filename {"migrations.sql"};

  model.make_migrations(model_renames, column_renames, sql_filename);

  std::optional<pqxx::result> result = db_adapter::execute_sql(sql_filename);

  return 0;
}

A Model object is created to manage the migrations. This allows for use of functions such as make_migrations() which is at the core of the migration system.

The make_migrations function takes three arguments:

• model_renames of the type nlohmann::json,
• column_renames of the type nlohmann::json,
• and sql_filename which is the name of the file you want sqlized migrations to be output to.

Both the model_renames and column_renames are used to track model renames and column renames if the user should do so. They are of the type nlohmann:: json which is a json library provided by the header <strata/db_adapters.hpp>. If the user makes a rename to a model, then for the rename to be tracked, and entry of the following form has to be made to the model_renames:

model_renames = {
    {"old_model_name", "new_model_name"}
}

If any rename is made to a column in any model, then the following entry has to be made to column_renames:

column_renames = {
    {"model_name", {
        {"old_column_name", "new_column_name"}
    }}
}

Note

Whether or not any renames are provided, both objects have to be initialized and provided to the make_migrations function as arguments.

When this file is run, the following output files will be produced:

• A file containing the SQL for the migrations that have been performed. e.g. migrations.sql in this case. e.g

# migrations.sql

CREATE TABLE users (
  users_id SERIAL NOT NULL,
  pin INTEGER NOT NULL,
  email VARCHAR(50) NOT NULL,
  username VARCHAR(24) NOT NULL,
  CONSTRAINT uq_email UNIQUE (email),
  CONSTRAINT uq_username UNIQUE (username),
  CONSTRAINT pk_users PRIMARY KEY (users_id)
);

Note

Each model has an added column, that is the model_id, which is an auto-incrementing integer column. This acts as the primary key whether or not you defined your own keys. This is prefixes with the model name, then suffixed with "_id".

• A schema.json file which contains the current state of the schema, meaning it will contain the models serialized into json.

{
  "users": {
    "email": {
      "datatype": "VARCHAR",
      "length": 50,
      "not_null": true,
      "primary_key": false,
      "unique": true
    },
    "pin": {
      "check_condition": "default",
      "check_constraint": 0,
      "datatype": "INTEGER",
      "not_null": true,
      "primary_key": false,
      "unique": false
    },
    "username": {
      "datatype": "VARCHAR",
      "length": 24,
      "not_null": true,
      "primary_key": false,
      "unique": true
    }
  }
}

Note

This file (schema.json) is VERY important to the working of the migration system, meaning one shouldn't touch it at all as

it might introduce unprecedented changes to the structure of the database itself.

Also, the schema.json file holds only the latest version of the schema, no versioning is implemented yet. Note that versioning may be implemented later.

• A models.hpp file which contains the C++ representation of the models. Datatypes are converted to primitive and non-primitive types in C++. This will become useful when we get to performing queries using the library.

#include <string>
#include <pqxx::row>
#include <tuple>
#include <vector>

class users{
public:
  std::string table_name = "users";
  int id;
  int pin;
  std::string email;
  std::string username;
  std::vector<pqxx::row> records;
  std::string col_str = "id,pin,email,username";
  int col_map_size = 4;

  users() = default;
  template <typename tuple_T>
  users(tuple_T tup){
    std::tie(id,pin,email,username) = tup;
  }

  auto get_attr() const{
    return std::make_tuple(id,pin,email,username);
  }
};

models.hpp stores metadata about the actual defined model. e.g. col_str which is important for use in inserts.

As it is, if we stop at the make_migrations function, no SQL will be executed. Here is where the last function comes in.

//USAGE
std::string sql_filename {"migrations.sql"};
std::optional<pqxx::result> result = db_adapter::execute_sql(sql_filename);

or

//USAGE: with a sql string
std::string query {"select * from users"};
std::optional<pqxx::result> result = db_adapter::execute_sql(query, false);

This function takes is of the following signature:

std::optional<pqxx::result> execute_sql(std::string& sql_file_or_str, bool is_file_name = true);

The arguments:

• sql_file_or_str: This takes a string. This string can either be a sql file name, or a raw sql string to execute raw sql.
• is_file_name: This is defaulted to true. It assumes that the default string passed into the first argument is a filename and not raw sql. Set it to false if you have provided raw sql as the first argument.

To execute SQL, such as the one in the file returned by make_migrations, pass the exact file name such as the one you specified in the third argument of the make_migrations function.

The return type of the execute_sql function is an optinal, meaning it can either return a pqxx::result containing rows requested, or a std::nullopt if result is empty. Should execute_sql fail, then a std::runtime_error shall be thrown with error details.

Warning

• The schema.json file shouldn't be changed or modified in any way, any changes to be made should be made inside the models.
• In models.hpp, one should only aim to change only that which is applicable to the model, e.g. fields whose names are in the models, adding helper functions we will see later, etc.