
# Typesafe SQL Statements in C++

C++ is an awesome language when it comes to its typesafety - even in template programming. This is different in languages like Java or C#, which have Generics, but do not support typesafety after compilation, because each template argument is converted into the (Java) base type Object. This allows handy programming, but isn't enough, when we need to know which column is of which type in our application.

Before we start, the code in this article has the following requirements:
- Template Programming
- Support for Variadic Templates

Most of the database libraries for C/C++, store their values after a query in an object, which can be requested by a template function like the following in PQXX (postgresql C++ library). To get an overview, this is the original example from libpqxx; 
```cpp

 <iostream>
 <pqxx/pqxx>

int main()
{
    try
    {
        pqxx::connection c;
        pqxx::work w(c);
        pqxx::result r = w.exec("SELECT 1");
        w.commit();
        std::cout << r[0][0].as<int>() << std::endl;
    }
    catch (const std::exception &e)
    {
        std::cerr << e.what() << std:: endl;
        return 1;
    }
}
```
The most important part is this line: This is how to get the first element of the first row:

```cpp
std::cout << r[0][0].as<int>() << std::endl;
```

<br />

### Introduction
If you query a SQL based database, you always want something back from the server. To begin with the simplest query, consider we want the count of a table. This means we give our function a table name (string) and get an integer value:
```cpp
long countRows(const std::string& tableName) 
{
    std::string query = "SELECT count(*) from " + tableName;

    pqxx::work w(c);
    pqxx::result r = w.exec(query);
    w.commit();

    // count is always of integer type
    return r[0][0].as<long>()
}
```

To create a fluent transition to more generic typesafe functions, let us introduce template to the previous one. We select only one field (count), so we only need one template parameter:
```cpp
template<typename R>
R countRows(const std::string& tableName) 
{
    // ...

    // return value of type R
    return r[0][0].as<R>()
}
```

### Fields, lots of fields

What do we do if we've got more fields? And how do we integrate them into a query? Let's select a specific, but a variadic number of fields in a table. This means, we need to add a variadic number of string parameters which fit the number of fields in our template. To accomplish the restriction of the same number of template arguments and function arguments, we need to define a little helper:
```cpp
// this allows to create variadic templates (first argument, but return another type)
template<typename, typename T>
using skip_first = T;
```
Our function declaration now looks now like this: Each type in Args is also required to be in the constructor of R.

```cpp
template<typename R, typename ... Args>
R selectFields(const std::string& tableName, 
                                 skip_first<Args, std::string> ... fields)
{
    R r(/* ... */);
    return r;
}
```
There are two parts here, which we need to resolve: 

1. build the query string
2. build the result instance of type R

#### Building the Query String
We loop over all of our fields and join them to one comma separated string:

```cpp
template<typename T>
std::string join(T str)
{
   return str;
}

template<typename T, typename ... Args>
std::string join(T str, Args... args)
{
   return str + ", " + join(args...);
}

template<typename R, typename ... Args>
R selectFields(const std::string &tableName,
                                 skip_first<Args, std::string> ... fields)
{
   std::string selectFields = join(fields...);
   std::string query = "select " + selectFields + " from " + tableName;
   
   // ... 
}

```


#### Resolving the Return type
Resolving the return type is basically the same as joining fields - except the other way around. We've got a tuple as the query result and need to extract the row data to pass it to the constructor of R.

As this example is with pqxx, here's a simplification/standardization from their own tuple type into std::tuples, so you can more easily work with your own database provider:
```cpp
template<typename ...T, std::size_t... Indices>
auto map_helper(const pqxx::tuple &t, std::tuple<T...> &result, std::index_sequence<Indices...>)
{
   return std::make_tuple(t.at((int) Indices).as<T>()...);
}


// standardize tuple
template<typename ...Tuple>
auto map_pqxx_tuple(const pqxx::tuple &t)
{
   std::tuple<Tuple...> result;

   auto I = std::make_index_sequence<sizeof...(Tuple)>();
   return map_helper(t, result, I);
}
```

To create an instance of a specific type, we need also a variadic template function, which converts the standardized tuple into a constructor call:

```cpp

template<typename T, typename Tuple, size_t... Is>
T gen(Tuple &&tuple, std::index_sequence<Is...>)
{
   return T{std::get<Is>(std::forward<Tuple>(tuple))...};
}

template<typename T, typename Tuple>
T gen(Tuple &&tuple)
{
   return gen<T>(std::forward<Tuple>(tuple),
                 std::make_index_sequence<std::tuple_size<std::decay_t<Tuple>>::value>{}
   );
}
```

After all our helper functions are finished, we can now implement `selectFields()`:

```cpp
template<typename R, typename ... Args>
R selectFields(const std::string &tableName,
                                 skip_first<Args, std::string> ... fields)
{
   pqxx::connection c{"dbname=myDb user=postgres password=password host=127.0.0.1 port=5432"};

   std::string selectFields = join(fields...);
   std::string query = "select " + selectFields + " from " + tableName;

   pqxx::work w(c);
   pqxx::result r = w.exec(query);
   w.commit();

   return gen<R, std::tuple<Args...> >(map_pqxx_tuple<Args...>(r.at(0)));
}
```
And voilá: 

```cpp

class User
{

public:
   User(const std::string &firstname, const std::string &lastname)
         : firstname(firstname),
           lastname(lastname)
   { }
   
private:
   std::string firstname;
   std::string lastname;
};

int main()
{
   auto user = selectFields<User, std::string, std::string>("user", "firstname", "lastname");

   return 0;
}
```


C++ templates can be used to create typesafe SQL-Statements.