SQL database access

When I first set out to make a post about database access from C++ I was going to write about MySQL Connector/C++. But for some reason that didn’t sit well with me. After sleeping on it I realized it didn’t appeal to me because it was 1) limited to only one database backend and 2) too low-level of an API. I wanted to write about a library that supports multiple database backends and abstracts the connection details as much as possible. Ideally I wanted a library that brings you closest to SQL syntax rather than deal in C++ mechanics. So in my quest for cool and portable C++ libraries I decided to keep looking…

And then I came across SOCI – The C++ Database Access Library 🙂 It has everything I was looking for: multiple backend support (DB2, Firebird, MySQL, ODBC, Oracle, PostgresSQL, and SQLite3), and a very natural way of issuing SQL queries thanks to operator overloading and template sorcery. Even their first example is purposely left without comments because it is that easy to read and understand.

Besides a very natural way of talking to a SQL backend what I like most about it is that it allows you, in a non-intrusive way (no existing code change needed), to store and retrieve your custom data structures to and from database tables.

So I installed MySQL server on my Mac using brew package manager and started coding. Within 30 minutes I had a working example that connected to my database server, created a database and a table, inserted rows into the table from my custom Person data structure, counted the rows, retrieved a table entry with a given ID, and finally cleaned up after itself.

The only code that requires explaining is the struct type_conversion<Person>; object. It is SOCI’s mechanism of converting to and from custom data structures and requires 2 methods: from_base which converts from a set of row values to a structure, and to_base which goes the other way. The rest is self explanatory! Here’s how you can get started:

#include <iostream>
#include <string>
#include <exception>
#include <soci/soci.h>
#include <soci/mysql/soci-mysql.h>

using namespace std;
using namespace soci;

struct Person
{
	int ID;
	string FirstName;
	string LastName;
	int DOB;
	string EMail;
};

namespace soci
{
	template<>
	struct type_conversion<Person>
	{
		typedef values base_type;

		static void from_base(const values& v, indicator, Person& p)
		{
			p.ID = v.get<int>("ID");
			p.FirstName = v.get<string>("FirstName");
			p.LastName = v.get<string>("LastName");
			p.DOB = v.get<int>("DOB");
			p.EMail = v.get<string>("EMail");
		}

		static void to_base(const Person& p, values& v, indicator& ind)
		{
			v.set("ID", p.ID);
			v.set("FirstName", p.FirstName);
			v.set("LastName", p.LastName);
			v.set("DOB", p.DOB);
			v.set("EMail", p.EMail);
			ind = i_ok;
		}
	};
}

int main()
{
	try
	{
		session sql(mysql, "host=localhost user=root password=''");

		sql << "CREATE DATABASE blog";
		sql << "USE blog";
		sql << "CREATE TABLE people (ID INT, FirstName TEXT, LastName TEXT, DOB INT, EMail TEXT)";

		Person him{1, "Martin", "Vorbrodt", 19800830, "martin@vorbrodt.blog"};
		Person her{2, "Dorota", "Vorbrodt", 19800127, "dorota@vorbrodt.blog"};
		sql << "INSERT INTO people (ID, FirstName, LastName, DOB, EMail) VALUES (:ID, :FirstName, :LastName, :DOB, :EMail)", use(him);
		sql << "INSERT INTO people (ID, FirstName, LastName, DOB, EMail) VALUES (:ID, :FirstName, :LastName, :DOB, :EMail)", use(her);

		int count{};
		sql << "SELECT COUNT(*) FROM people", into(count);
		cout << "Table 'people' has " << count << " row(s)" << endl;

		Person pOut{};
		sql << "SELECT * FROM people WHERE ID = 1", into(pOut);
		cout << pOut.FirstName << ", " << pOut.LastName << ", " << pOut.DOB << ", " << pOut.EMail << endl;

		sql << "DROP TABLE people";
		sql << "DROP DATABASE blog";
	}
	catch (exception& e)
	{
		cerr << e.what() << endl;
	}
}

#include <iostream>

#include <string>

#include <exception>

#include <soci/soci.h>

#include <soci/mysql/soci-mysql.h>

using namespace std;

using namespace soci;

struct Person

{

int ID;

string FirstName;

string LastName;

int DOB;

string EMail;

};

namespace soci

{

template<>

struct type_conversion<Person>

{

typedef values base_type;

static void from_base(const values& v, indicator, Person& p)

{

p.ID = v.get<int>("ID");

p.FirstName = v.get<string>("FirstName");

p.LastName = v.get<string>("LastName");

p.DOB = v.get<int>("DOB");

p.EMail = v.get<string>("EMail");

}

static void to_base(const Person& p, values& v, indicator& ind)

{

v.set("ID", p.ID);

v.set("FirstName", p.FirstName);

v.set("LastName", p.LastName);

v.set("DOB", p.DOB);

v.set("EMail", p.EMail);

ind = i_ok;

}

};

}

int main()

{

try

{

session sql(mysql, "host=localhost user=root password=''");

sql << "CREATE DATABASE blog";

sql << "USE blog";

sql << "CREATE TABLE people (ID INT, FirstName TEXT, LastName TEXT, DOB INT, EMail TEXT)";

Person him{1, "Martin", "Vorbrodt", 19800830, "[email protected]"};

Person her{2, "Dorota", "Vorbrodt", 19800127, "[email protected]"};

sql << "INSERT INTO people (ID, FirstName, LastName, DOB, EMail) VALUES (:ID, :FirstName, :LastName, :DOB, :EMail)", use(him);

sql << "INSERT INTO people (ID, FirstName, LastName, DOB, EMail) VALUES (:ID, :FirstName, :LastName, :DOB, :EMail)", use(her);

int count{};

sql << "SELECT COUNT(*) FROM people", into(count);

cout << "Table 'people' has " << count << " row(s)" << endl;

Person pOut{};

sql << "SELECT * FROM people WHERE ID = 1", into(pOut);

cout << pOut.FirstName << ", " << pOut.LastName << ", " << pOut.DOB << ", " << pOut.EMail << endl;

sql << "DROP TABLE people";

sql << "DROP DATABASE blog";

}

catch (exception& e)

{

cerr << e.what() << endl;

}

Table ‘people’ has 2 row(s)
Martin, Vorbrodt, 19800830, [email protected]
Program output.

5 Replies to “SQL database access”

Eric Lemanisser says:

March 18, 2019 at 9:25 am

On the same topic, one could be interested in https://github.com/rbock/sqlpp11 which avoids having to write SQL requests.
I don’t know SOCI, but it seems to use comma operator overloading. this is unusual enough to scare some people away (including me)!

Loading...

1. Martin Vorbrodt says:
  
  March 18, 2019 at 10:10 am
  
  Thanks I’ll check it out. Would you be interested in rewriting my example to SQLPP11 to do the same exact queries? I would publish it and give you credit for the work 🙂
  
  Loading...
  
bmahr says:

March 18, 2019 at 3:28 pm

Nice. I would be interested in a implementation of the repository pattern (https://www.codeproject.com/articles/526874/repository-pattern-done-right) wich provides another very helpful abstraction over QR-Mappers which are, in my opinion, are still better (but les performing) than pure SQL. With repositories and mappers it’s also much easier to handle SQL injections and other issues.

Loading...

khanh says:

April 15, 2020 at 3:56 am

i can’t build project with soci 4.0 ( i receive error: can’t see soci-config.h from header file soci-platform.h

Loading...

Philippe says:

February 3, 2021 at 3:17 pm

Hi, I’m using Clion IDE and have the same problem than khanh.
Does someone know the solution?

Thank you!

Loading...