Simple ORM over SOCI Based on MySQL Backend

SOCI is a free database access library that’s written in C++. The library itself is distributed via SourceForge and the documentation can be found both on the official website and SourceForge.

Initially designed as a C++ alternative to Oracle Call Interface, the scope of SOCI has greatly expanded over time. This library now serves as an easy way to access databases with C++ and supports different types of databases including MySQL, ODBC, Oracle, PostgreSQL, and SQLite3 with plans to add support for other popular database types as well.

As a company specializing in C++ development, we often use SOCI when we need to work with MySQL in C++, as it allows us to essentially create SQL queries within standard C++ code. In this article, we’ll show you a practical example of how to use SOCI with MySQL in order to create a simple SOCI MySQL backend.

Written by

Sergey Stepanchuk,

Software Developer,

System Programming Team

Contents:

Creating a DBTableBase Class

Creating a Table

Insert

Update

Delete

Transaction

Select

Conclusion

References

Creating a DbTableBase Class

One of the prerequisites to implementing a full-fledged application with MySQL and SOCI is having a class that contains Insert, Update, and Delete functions, among others. Let’s name this class DBTableBase.

template<typename RowType = soci::row>
class DBTableBase
{
    explicit DBTableBase(soci::session& session, const std::string& tableName)
        : m_session(session)
        , m_tableName(tableName)
{
}
   /* Other methods */
}

Our DBTableBase class must contain a member with the name of the:

std::string m_tableName; 

It must also include the main interface for communicating with SOCI:

soci::session& m_session;

Now we need to implement this template class for each table in the database that inherits the base class. In our example, it will look like this:

class Students: public DBTableBase<Student>
{
     Students(soci::session&am; session):
        DBTableBase(session, g_studentsTableName)
    {
    }
   /* Other methods */
}

Creating a Table

As an example, let’s create a small database called Students that will contain information on current students at a university. Let’s add id, name, last_name, and faculty fields.

In order to create the table, first we need to define the structure of the fields within it. The best way to do this is with BOOST_FUSION_DEFINE_STRUCT, which defines and adapts the structure as a model of Random Access Sequence.

typedef boost::optional<int64_t> IdType;
BOOST_FUSION_DEFINE_STRUCT((db), Student,
	(db::IdType, id)
	(std::string, name)
	(std::string, last_name)
	(std::string, faculty)
)

The id field is set with the help of boost::optional. This is necessary to determine how this field is initialized. If the id field hasn’t been initialized, it will contain the value boost::none. This function is very useful for fields that can contain a null value (keeping in mind that in MySQL, null and 0 are different).

Next, we’ll define several constants:

const char g_nameId[] = "id";
const char g_nameIdType[] = "BIGINT PRIMARY KEY AUTO_INCREMENT";
const char g_notNull[] = "NOT NULL";
const char g_studentsTableName[] = "Students_table";
const std::string g_nameType("VARCHAR(255)");

Then we’ll create a Students object:

Students students(session);

And then create the table:

students.CreateTable();

Next, with the help of SOCI, we can construct a query to create the Students table:

void Students::CreateTable() const
{
    m_session << BeginCreateTableQuery()
        << ", " << GetColName<1>() << " " << g_nameType << g_notNull
        << ", " << GetColName<2>() << " " << g_nameType << g_notNull
        << ", " << GetColName<3>() << " " << g_nameType << g_notNull
        << ", " << DeleteCascadeQuery(GetColName<3>(), "Faculty_table")
        << EndCreateTableQuery();
}

The method DBTableBase::BeginCreateTableQuery can be used as follows:

std::string DBTableBase::BeginCreateTableQuery() const
{
    return std::string("CREATE TABLE" + m_tableName + " (" + g_nameId + " " + g_nameIdType;
}

The GetColName template function is defined like this:

template<int col>
static constexpr std::string DBTableBase::GetColName()
{
    return boost::fusion::extension::struct_member_name<RowType, col>::call();
}

This construct will return the name of the specified column.

If you need to set a column for cascade deletion, you can use this:

std::string DBTableBase::DeleteCascadeQuery(const std::string& colName, const std::string& tableName) const
{
    return std::string("CONSTRAINT fk_") + m_tableName + "_"+ colName + " FOREIGN KEY(" + colName +") REFERENCES " + tableName + "(" + g_nameId + ") ON DELETE CASCADE";
}

At the end of our query, we need to specify:

std::string DBTableBase::EndCreateTableQuery() const
{
    return ");";
}

We’ve now finished creating the Students_table.

Insert

Next, we need to fill this table with data. Let’s look at how we can insert a few lines into our Students table so it looks like this:

First, let’s create three objects in db::Student:

db::Student studentTaylor(db::IdType(), "William", "Taylor", "FEIT");
db::Student studentDavies(db::IdType(), "Mary", "Davies", "FEIT");
db::Student studentSmith(db::IdType(), "Jack", "Smith", "FEIT");

Then we’ll call the Insert function to add them to DB:

students.Insert(studentTaylor);
students.Insert(studentDavies);
students.Insert(studentSmith);

Here’s the implementation of the Insert function:

int64_t DBTableBase::Insert(const RowType& row) const
{       	 
    std::stringstream query;
    query << "INSERT INTO " << m_tableName << " (";
    std::vector<std::string> colNames = GetColNames<RowType>();
    std::stringstream colsNames;
    std::stringstream colsValues;
    for (const auto& col : colNames)
    {
        if (!colsNames.str().empty())
        {
            colsNames << ", ";
            colsValues << ", ";
        }
        colsNames << col;
        colsValues << ":" << col;
    }
    query << colsNames.str() << ") VALUES (" << colsValues.str() << ")";
    DB_TRY
    {
        m_session <<query.str(), soci::use(row);
        int64_t id = 0;
        m_session.get_last_insert_id(m_tableName, id);
        return id;
    }
    DB_CATCH
    return 0;
}

Here’s the implementation of the GetColNames function:

static std::vector<std::string> DBTableBase::GetColNames()
{
    std::vector<std::string> res;
    GetColNames<RowType>::Call(res);
    return res;
}

And here’s the implementation of the Call function:

template<typename RowType, int index = boost::fusion::size(*(RowType*)nullptr) - 1>
struct GetColNames
{
    static void Call(std::vector<std::string>& val)
    {
        GetColNames<RowType, index - 1>::Call(val);
        std::string name = boost::fusion::extension::struct_member_name<RowType, index>::call();
        val.push_back(name);
    }
};
 

Update

Now let’s consider a situation when we’ve entered incorrect data into the table. For example, we’ve entered the first name of a student by the last name Davies as Emily instead of Mary. To correct this mistake, we need to update the entry for studentDavies.

studentDavies.name = "Emily";
students.Update(studentDavies);
 

Here’s the implementation of Update:

bool DBTableBase::Update(const RowType& row) const
{
    std::stringstream query;
    query << "UPDATE " << m_tableName << " SET ";
    std::vector<std::string> colNames = GetColNames();
    for (const auto& col : colNames)
    {
        if (col != colNames.front())
        {
            query << ", ";
        }
        query << col << " = :" << col;
    }
    query << " WHERE " << g_nameId << " = " << row.id;
    m_session << query.str(), soci::use(row);
    return true;
}

After this operation, we can see the change in the table:

Delete

We can use the following command to delete students’ information by id:

students.Delete(0);
students.Delete(1);
students.Delete(2);

where:

bool DBTableBase::Delete(int64_t id) const
{
    m_session << "DELETE FROM " << m_tableName << " WHERE " << g_nameId << " = :id", soci::use(id);
    return true;
}

After this operation, we’ll have an empty table.

Transaction

If you have a series of operations that need to be performed together where an error in one operation must cause the cancellation of all previous operations, then you need to use soci::transaction:

std::unique_ptr<soci::transaction> transaction(new soci::transaction(m_session));
try
{
    students.Insert(studentTaylor);
    students.Insert(studentDavies);
    students.Insert(studentSmith);
    transaction->commit();
}
catch(const std::exception& ex)
{
    std::cout << “Transaction error” << ex.what() << std::endl;
    transaction->rollback();
}

If an error occurs when adding a student to the database, all insert operations will be cancelled.

Select

Let’s consider a situation when we need to get the records for a student with id 2. In order to do this with SOCI, we need to use:

soci::rowset<db::Student> student = students.SelectByID(2);

As a result, we’ll get the following table:

We can implement the SelectByID function as follows:

soci::rowset<db::Student> DBTableBase::SelectByID(const int64_t id) const
{
    std::stringstream query;
    query << "SELECT id  FROM " << m_tableName << " WHERE id = " << id;
    return m_session.prepare << query.str();
}

Let’s try to make a more complex query now – for example, let’s say we need to select all students with the name William Taylor:

typedef std::map<std::string, std::stringstream> KeyValuePairs;
KeyValuePairs value;
value["name"] << "William";
value["last_name"] << "Taylor";
soci::rowset<db::Student> rows = students.SelectByValues(value);

By executing this query, we’ll get:

We can implement an easy way to get the desired string using several search parameters with the help of this function:

soci::rowset<rowtype> DBTableBase::SelectByValues(const KeyValuePairs& pairs) const
{
    std::stringstream query;
    query << "SELECT * FROM " << m_tableName << " WHERE ";
    bool first = true;
    for (const auto& pair: pairs)
    {
        if (first)
        {
            first = false;
        }
        else
        {
            query << " AND ";
        }
        query <<  pair.first << " = \"" << pair.second.str() << "\"";
    }
    return m_session.prepare << query.str();
}

Conclusion

SOCI is one of the most popular and refined database access library examples out there. Our article shows only a few ways in which you can use SOCI to work with databases, but it’s a nice illustration of the basic methodology. We hope that this information will prove useful and will encourage you to look more into SOCI in the future.

References

1. SOCI - The C++ Database Access Library
2. Boost Fusion Define Struct
3. Boost.Optional
4. MySQL