Inserts

Inserting Data to the Database.

This operation can be done in two simple functions:

db_adapter::prepare_insert()

This function has the following signature:

template <typename Model_T>
pqxx::connection prepare_insert();

It prepares the insert statement by utilizing libpqxx's parametarized inserts. It requires the Model_T object because of the col_str which contains the names of the columns of the table. It then prepares the insert statement by creating a connection then parameterizing the statement inside the connection, then returns the prepared connection.

This function will throw a std::runtime_error if it fails.

---

db_adapter::exec_insert()

void exec_insert(pqxx::connection& cxn, pqxx::params& param);

This function takes the prepared connection from the prepare_insert() returned connection, and a pqxx::params instance. Hence, anything you wish to insert to a table using this function, must be provided in the form of pqxx::params instance.

This function will throw a std::runtime_error if it fails.

---

Note

The order of values in an instance of pqxx::params must be the same as that in the model or in col_str. This is because that is the same order as the columns in the database table.

The col_str is a comma-separated string containing column names for the model, save for the 'id' column as that is an auto-incrementing primary key. It is used to build the parameterized insert statemetnt, e.g. insert into model(col_str) values ($1, $2, $3, $4). This is what is in the prepared connection;

Usage of inserts

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

int main(){
  users user {};
  message m {};

  using params = std::vector<pqxx::params>;
  params user_rows = user.parse_json_rows();
  params message_rows = m.parse_json_rows();

  pqxx::connection cxn = db_adapter::prepare_insert<users>();
  for(pqxx::params& user_row : user_rows){
    db_adapter::exec_insert(cxn, user_row);
  }

  cxn = db_adapter::prepare_insert<message>();
  for(pqxx::params& message_r : message_rows){
    db_adapter::exec_insert(cxn, message_r);
  }

  return 0;
}

This example uses a user-defined function .parse_json_rows() defined inside the corresponding classes to convert json objects into pqxx::params which exec_insert needs to insert into the database.

An example of the parse_json_rows() function as a helper to parse json data into pqxx::params and return a vector of them:

std::vector<pqxx::params> parse_json_rows(){
  std::vector<pqxx::params> rows {};
  std::ifstream json_row_file("users.json");
  if(!json_row_file.is_open()) std::runtime_error("Couldn't open file attempting to parse rows");

  nlohmann::json json_data = nlohmann::json::parse(json_row_file);

  for(auto& json_row: json_data){
    rows.push_back(pqxx::params{json_row["pin"].get<int>(),
                                json_row["email"].get<std::string>(),
                                json_row["username"].get<std::string>()
                               });
  }
  return rows;
}

Warning

Note that one can define their own functions inside the model classes in models.hpp but they will be removed or overwritten if you make migrations in the original model classes with the datatypes.

You should not call prepare_insert() on the same model, this will cause an error to be throws because you prepared the same statement more than once.

Also, the connection returned from prepare_insert() is the same one that must be used when calling exec_insert() because it contains the statement to be used for insert (unless you can create your own).