introduction to a simple 
C++ database access library


London C++ Meetup

May 2013

Mateusz Loskot | @mloskot

Slides licensed under the Creative Commons Attribution License


C++ hacker, geospatial developer

Open Source developer

Boost.Geometry, GDAL, libLAS, PostGIS, SOCI, ...

Charter Member @OSGeo

Senior Programmer @Cadcorp


It's a library 

Written in standard C++ (C++03)

Database access interface

Boost Software License (BSL 1.0)

SOCI (IPA: [ˈsokɪ]) 

Sochi  (IPA: [ˈsotɕɪ])

Photo by Mikhail Mordasov | Copyright (C) Mikhail Mordasov (AFP - Getty Images)


Originally created by Maciej Sobczak in CERN
as an abstraction for Oracle

Simple Oracle Call Interface


Simple Open (Database) Call Interface

  • 2006-01: 2.0.0 PostgreSQL backend
  • 2006-03: 2.0.1 MySQL and SQLite backends
  • (@mloskot joined the project)
  • 2006-12: 2.2.0 ODBC and Firebird backends
  • 2008-07: 3.0.0 New naming convention
  • 2011-10: 3.1.0 Ada bindings
  • 2013-03: 3.2.0 DB2 backend, moved to GitHub
  • 2013-04: 3.2.1 Bugfix release (current)
  • ????-??:       4.0.0 Lots of plans!

Canonical Machinery

class statement {public:
statement(session& s);
void exchange(/*...*/); void define_and_bind();
void prepare(string q);
bool execute(); bool fetch();};
class session {public: // open connection session(string c);
void begin(); void commit();
void rollback();


  1. Connect
  2. Collect exchange variables
    • Bind* local objects to SQL   placeholders
    • Define* local objects for query output 
    • Prepare statement
    • Execute statement
    • Fetch data from result set

    bind define  terminology from   Oracle Call Interface  heritage; SOCI documentation use  binding for directions.


     std::string name; // define variable int id = 101;     // bind variable
    session db("dbname=test"); statement st(db);; // bind input; // define output
    st.define_and_bind(); st.prepare("SELECT name FROM person " "WHERE id = ?"); st.execute(); while (st.fetch()) { std::cout << name << std::endl; }

    What's the 'canonical' problem?

    Lots of code with

    for technical purpose only
    conceptually disconnected
    and irrelevant to the problem at hand

    function calls

    Why that is a problem?

    Basic facade,
    "back-end to front-end" wrapper
    with low-level concepts revealed,
    low-level conventions required
    and distracting elements displayed

    Has real value been added?

    express yourself

    I'm expressin' with my full capabilities
    Now I'm livin' in correctional facilities
    ...I get straight, meditate, like a Buddhist
    ...But my technique is very necessary

    N.W.A - Express Youself, 1988

    The SYNTAX nirvana

     int v = 7; std::cout << v;

    C++ natural syntax

    Simple to write
    Easy to learn and understand
    Operation complexity kept out of sight

    syntax-first development

    XP and TDD inspired

    setting up syntax



    holy grail

     person p; session sql(...); sql << "SELECT name,age FROM person",        into(p);

    or, monty python?

    C++ techniques

    operator overloading


    lifetime rules of temporary objects

    what's the SOCI magic?

    Illusion of embedding SQL in C++

    anatomy of database session

    connect on construction

    using namespace soci;
    session sql([BACKEND], [CONNECTION STRING]);

    connect in two steps

    session sql;[BACKEND], [CONNECTION STRING]); 


    Database-specific connection session

    string connstr("dbname=test user=skippy");
    // uses factory object providedsession sql(postgresql, connstr);
    // loads backend by name at run-timesession sql("postgresql", connstr);
    session sql("postgresql://dbname=test");

    advanced backend parameters

    connection_parameters p("odbc","DSN=test");
    // Force SQL_DRIVER_NOPROMPT p.set_option( odbc_option_driver_complete,"0"); session sql(p);

    SIMPLE SQL statements

    Executed only once, immediately

    session sql;sql << "DROP TABLE employee";
    string table_name("employee");sql << "DROP TABLE " << table_name;
    int id = 7;sql << "DELETE FROM "    << table_name    << " WHERE id="    << id;

    anatomy of the staTement SYNTAX

    sql << "DROP TABLE " << table_name;
    sql.once << "DROP TABLE " << table_name;
    class session {public: once_type once;
    template <typename T> once_temp_type operator<<(T t) { return once << t; }};


    sql.once << "DROP TABLE " << table_name;
    class once_type {
     session* session_;
     template <typename T>
     once_temp_type operator<<(T t) {   once_temp_type once_temp(*session_);
       return once_temp << t; }

    anatomy of the temporary

    sql.once << "DROP TABLE " ? << table_name ?;
    class once_temp_type { ref_counted_statement* st_;public:
    template <typename T> once_temp_type& operator<<(T t) { // push into buffer, stringstream
    st_->accumulate(t); return *this; } ~once_temp_type() { st_->dec_ref(); }

    death of the TEMPORARY

    sql.once << "DROP TABLE " << table_name; ?
    class ref_counted_statement { statement st_;
    public: void def_ref() {
    if (--counter == 0) final_action(); } void final_action() { st_.prepare(get_query());
    st_.define_and_bind(); st_.execute(); }};

    statement execution summary

    1. sql << "..." << ... ;
    2. sql. once << "..." << ... ;
    3. start up the temporary object
    4. pass the temporary through expression
      • collecting all streamable elements
    5. reach end of the expression
    6. destroy the temporary object          
      • prepare statement
      • execute statement
      • exchange data
      • clean up

    C++11 caveats

    Destructors default to 


    N3166 , N3204

    SOCI must address this issue with:


    Thanks to Roger Orr and Anthony Williams
    for reporting this issue in SOCI.

    ELEMENTS of data exchange

    • Bind local output data (aka define)
    • Bind local input data (aka bind)
    • Bind objects of static types
    • Bind objects in dynamic result set
    • Bind objects of user-defined types
    • Object-Relational Mapping (ORM)
    • Handle  NULL  and data conditions

    SQL with DATA exchange

    Executed only  once , immediately
    string name; // output variable
    sql << "SELECT name FROM person",into(name);
    int id = 7;  // input variable
    sql << "SELECT name FROM person WHERE id=:i", into(name), use(id);
    indicator ind;
    sql << "SELECT name FROM person", into(name, ind);if (ind == soci::i_null) { ... }


    class once_temp_type { ref_counted_statement* st_;public: once_temp_type& operator,(into_type_ptr i){   // push onto statement intos
    st_->exchange(i); } once_temp_type& operator,(use_type_ptr u){ // push onto statement uses st_->exchange(u); }};

    anatomy of into

    sql << "SELECT name FROM p", into(name);
    template <typename T>
    into_type_ptr into(T& t) { return do_into(t, typename    exchange_traits<T>::type_family());}
    template <typename T>
    into_type_ptr do_into(T& t,basic_type_tag) { return into_type_ptr(new into_type<T>(t));}

    anatomy of INTO_TYPE

    class into_type {  // statement backend define_by_pos  void define(statement* s, int& position);
    // convert data to exchange type void post_fetch(); void* data_; indicator* ind_; exchange_type type_;};

    machinery of into

    template <typename T>
    into_type_ptr do_into(T& t,basic_type_tag);
    template <typename T>
    into_type_ptr do_into(T& t,indicator& ind,                           basic_type_tag);
    template <typename T>
    into_type_ptr do_into(T& t,user_type_tag);
    template <typename T>
    into_type_ptr do_into(T& t,indicator& ind,                           user_type_tag);
    Plus, overloads for vector<T> and vector<indicator>

    anatomy of use

    sql<<"SELECT n FROM p WHERE id=:i", use(id);
    template <typename T>
    use_type_ptr use(T& t, string name) { return do_use(t, name, typename    exchange_traits<T>::type_family());}
    template <typename T>
    use_type_ptr do_use(T& t, string name, basic_type_tag) { return use_type_ptr(  new use_type<T>(t, name));}

    anatomy of use_type

    class use_type {  // statement backend bind_by_pos  void bind(statement* s, int& position);
    // convert data to exchange type void pre_use(); void* data_; indicator* ind_; exchange_type type_; string name_;

    machinery of use

    template <typename T>
    use_type_ptr do_use(T& t, string name, basic_type_tag);
    template <typename T>
    use_type_ptr do_use(T& t,indicator& ind, string name, basic_type_tag);
    template <typename T>
    use_type_ptr do_use(T& t,string name, user_type_tag);
    template <typename T>
    use_type_ptr do_use(T& t,indicator& ind, string name, user_type_tag);
    Plus, overloads for vector<T> and vector<indicator>

    static types for  INTO AND USE

    Predefined exchange_traits<T> for
    basic C++ types:

    • char, char*, char[], std::string
    • (unsigned) short, int, long long
    • double
    • std::tm
    • std::vector<T> for T as above

    Binding dynamic result set

    row r;sql << "SELECT name,age FROM p", into(r);
    for (size_t i=0; i<r.size(); ++i)
     column_properties& c = r.get_properties();
     if (c.get_data_type() == dt_string)
       string name = r.get<string>(i);
     else if (c.get_data_type() == dt_integer)
       int age = r.get<int>(i);
    string name; int age;
    r >> name >> age;

    AVAILABLE (LIMITED) dt_* mappingS

    SOCI Base Types

    • dt_string
    • dt_integer
    • dt_long_long
    • dt_unsigned_long_long
    • dt_double
    • dt_date

    user-defined types support

    template<> struct type_conversion<MyX> {
    typedef string base_type;
    void from_base(string s,indicator i,MyX& x){
    if (i == i_null) { ... } x.from_string(s); // convert}
    void to_base(MyX x,string& s,indicator& i){ sx.to_string();// convert i = i_ok; }};

    object-relational mapping

    database table

    CREATE TABLE person (  name VARCHAR(60),  age INT)

    c++ type

    struct Person {   string name;  int age;};

    MAP soci::values via conversion

    template<> struct type_conversion<Person>{typedef values base_type;
    from_base(values v,indicator i,Person& p){ = v.get<string>(); p.age = v.get<int>();}to_base(Person p,values& v,indicator& i){
    v.set("name",; v.set("age", p.age);
    i = i_ok;}};
    template<> struct type_conversion<MyX> {
    typedef string base_type;
    void from_base(string s,indicator i,MyX& x){
    if (i == i_null) { ... } x.from_string(s); // convert}
    void to_base(MyX x,string& s,indicator& i){ sx.to_string();// convert i = i_ok; }};


    Prepared statements and repeated execution

    int i;statement st = (sql.prepare  << "INSERT INTO n(v) VALUES(:v)",  use(i));
    for (i = 0 i < 100; ++i){ st.execute(true);}

    soci::rowset for SELECT QUERIES

    statement wrapper with STL-like iterator interface

    rowset<row> rs = (sql.prepare
    << "SELECT name,age FROM person");
    for (auto it=rs.begin(); it!=rs.end(); ++it){
    // dynamic data extraction from each row cout << "name: " << it->get<string>(0) << ",age: " << it->get<int>(1) << endl;}

    THERE is EVEN more

    Bulk operations
    Transactions interface
    Stored procedures support
    Binary large objects (BLOB) API 
    Query string transformation

    Integration with Boost    libraries: optional, tuple,  fusion::vector, gregorian::date

    Thank you!

    SOCI project website at

    SOCI git repository, bug tracker and wiki at

    SOCI talk slides at

    Slides licensed under the Creative Commons Attribution License


    By Mateusz Loskot


    Introduction to SOCI - the C++ database access library

    • 6,546