[boost] sqlpp11: SQL for C++

Subject: [boost] sqlpp11: SQL for C++
From: Roland Bock (rbock_at_[hidden])
Date: 2013-11-09 17:03:35

Hi,

over the last four or five years I developed several SQL libraries for
C++. With C++11 I was finally able to create an SQL library that meets
my own expectations and requirements. It is being used in production, I
recently put it on github, and I would really like to hear from you guys
whether this is something that could be interesting for you personally
or for boost?

https://github.com/rbock/sqlpp11
https://github.com/rbock/sqlpp11/wiki

sqlpp11 is a template library representing an embedded domain specific
language (EDSL) that allows you to

  * define types representing tables and columns,
  * construct type safe queries checked at compile time for syntax
    errors, type errors, name errors and even some semantic errors,
  * interpret results by iterating over query-specific structs with
    appropriately named and typed members.

This results in several benefits, e.g.

  * the library user operates comfortably on structs and functions,
  * the compiler reports many kinds of errors long before the code
    enters unit testing or production,
  * the library hides the gory details of string construction for
    queries and interpreting string based results returned by select
    calls. I.e. you don't need to use strings in query construction
    where you wouldn't use them in SQL and there is no need to use
    positional arguments or to parse strings when obtaining fields from
    a result row (the latter being true unless you do not know which
    columns to select at compile time).

The library supports both static and dynamic queries. The former offers
greater benefit in terms of type and consistency checking. The latter
makes it easier to construct queries on the flight.

Specific traits of databases (e.g. unsupported or non-standard features)
are known at compile time as well. This way, the compiler can tell the
developer at compile time if a query is not accepted by the database
(e.g. if a feature is missing). And the library can form the query in
the correct manner, for instance if the engine uses concat instead of
operator|| to concatenate strings.

Two Examples:
=============
Static Select:
--------------
// selecting zero or more results, iterating over the results
for (const auto& row : db.run(
                        select(foo.name, foo.hasFun)
                        .from(foo)
                        .where(foo.id > 17 and foo.name.like("%bar%"))))
{
  if (row.name.is_null())
    std::cerr << "name will convert to empty string" << std::endl;
  std::string name = row.name;  // text fields are implicitly
                                   convertible to string
  bool hasFun = hasFun;         // bool fields are implicitly
                                   convertible to bool
}

Dynamic Select:
----------------
auto s = dynamic_select(db, tab.foo).dynamic_columns().from(tab);
if (userWantsBar)
  s.add_column(tab.bar);
for(const auto& row : run(s))
{
  std::cerr << "row.foo: " << row.foo;
  if (userWantsBar)
    std::cerr << "row.bar" << row.at("bar");
  std::cerr << std::endl;
};

Please let me know your questions/thoughts/suggestions/rants.
Contributions welcome, of course :-)

Regards,

Roland