sqlpp11 Documentation

To use sqlpp11, you need three distinct components working together:

1. Table Structs: Representations of your database tables (usually generated by sql2cpp). These structs provide compile-time metadata about column names and data types.
2. Core Library: Provides the SQL embedded language for C++. It enables writing SQL statements that are checked at compile-time against your table structs.
3. Connector Library: The bridge to the physical database. It handles the connection, query execution, and result retrieval.

Ensure you have a connector library installed that matches your target database.

sqlpp11 attempts to determine if a result field can be NULL based on the query structure. If the library cannot guarantee a value is non-null, it assumes the field can be NULL.

To safely process results:

1. Use .is_null() to check if the field contains a NULL value.
2. Use .value() to retrieve the actual data. If .is_null() is true, .value() returns a default-constructed value of the underlying type.

for (const auto& row :  db(select(all_of(tab)).from(tab).unconditionally()))
{
  if (not row.alpha.is_null())
  {
    const auto a = row.alpha.value();
  }
}

You can use package managers to install sqlpp11.

Homebrew (macOS): Use the marvin182/zapfhahn/sqlpp11 tap.

vcpkg: Install using the sqlpp11 port with specific database features (e.g., [mysql]).

# Homebrew
brew install marvin182/zapfhahn/sqlpp11

# vcpkg
./vcpkg install 'sqlpp11[mysql]'

Use dynamic_insert_into when the structure of your insert statement depends on runtime information (e.g., user input) or when you only want to provide values for a subset of the table's fields, leaving others to their default values or NULL.

Unlike static inserts, dynamic_insert_into requires a database connection as its first argument to evaluate dynamic parts as they are added. You can use .dynamic_set() for initial fields and .insert_list.add() to append additional fields at runtime.

// Initialize with the database connection and the target table
auto s = dynamic_insert_into(db, foo).dynamic_set(
    foo.name = name,
);

// Add fields dynamically based on runtime logic
s.insert_list.add(foo.id = runtimeStatement.id);
s.insert_list.add(foo.hasFun = runtimeStatement.hasFun);

// Execute the statement
const int64_t result = db(s);

Connection pools can prevent multiple threads from using the same connection simultaneously using two main patterns:

1. New connection per request

Fetch a connection handle immediately before each operation. This is safe for individual queries but requires care with transactions: all queries within a single transaction must use the same connection object.

pool.get()(insert_into(mytable)....)
pool.get()(remove_from(mytable)....)

2. One connection per thread

Use a thread_local wrapper that lazily fetches a connection from the pool the first time it is used. This ensures each thread has its own dedicated connection from the pool, avoiding multi-threading conflicts. See examples/connection_pool in the repository for a concrete implementation.

To add tables or joins to a query at runtime, use .dynamic_from(table) to prepare the clause. You can then add joins using s.from.add(dynamic_join(table).on(condition)).

auto s = dynamic_select(db, all_of(foo)).dynamic_from(foo).dynamic_where();
if (someOtherCondition)
  s.from.add(dynamic_join(bar).on(foo.barId == bar.id));

To build SQL statements with sqlpp11, you must represent your database tables and columns as C++ structs. This allows the library to understand the schema during compile time.

The recommended approach is to use a code generator to translate your Data Definition Language (DDL) into C++ code, rather than writing these structs manually.

When performing insert or update operations, you can represent a database NULL by using sqlpp::null.

Warning: You cannot assign sqlpp::null to columns that are defined as non-nullable in your table schema.

To manage database transactions, use the start_transaction(db) function, where db is your connection object.

To successfully complete a transaction, you must explicitly call .commit(). If you need to abort changes, call .rollback().

Automatic Rollback Behavior: If a transaction object goes out of scope without commit() or rollback() being called, it will automatically trigger a rollback() in its destructor. This automatic rollback is reported by the connection by default.

auto tx = start_transaction(db);
// do something
tx.commit();

To use sqlpp11, you must first define your tables as C++ types. You can generate these headers from SQL DDL files using the provided ddl2cpp Python script.

1. Export your table schema (e.g., using SHOW CREATE TABLE in MySQL or mysqldump).
2. Run the ddl2cpp script against the .ddl file.

Command Syntax: %sqlpp11_dir%/scripts/ddl2cpp <input_ddl_file> <output_header_prefix> <namespace>

SQLPP11 provides connection pools as centralized caches of database connections to improve performance. Each connector has its own pool class:

• sqlpp::mysql::connection_pool
• sqlpp::postgresql::connection_pool
• sqlpp::sqlite3::connection_pool

Constructors accept two parameters:

1. A std::shared_ptr to a configuration object (the same type used for non-pooled connections).
2. An integer specifying the initial size of the connection cache (the cache grows automatically).

You can also instantiate a pool without a configuration and call .initialize(config, initial_size) later.

auto config = std::make_shared<sqlpp::postgresql::connection_config>();
config->dbname = "my_database";
config->user = "my_user";
config->password = "my_password";
config->debug = true;
auto pool = sqlpp::postgresql::connection_pool{config, 5};