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.

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.

id	name	last name	faculty
...	...	...	...

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:

id	name	last_name	faculty
0	William	Taylor	FEIT
1	Mary	Davies	FEIT
2	Jack	Smith	FEIT

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:

id	name	last_name	faculty
0	William	Taylor	FEIT
1	Emily	Davies	FEIT
2	Jack	Smith	FEIT

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:

id	name	last_name	faculty
2	Jack	Smith	FEIT

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:

id	name	last_name	faculty
0	William	Willson	FEIT
1	William	Taylor	FEIT
2	Taylor	Davies	FEIT
3	Jack	Taylor	FEIT

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:

id	name	last_name	faculty
1	William	Taylor	FEIT

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