Library

sqlpp11

Overview

sqlpp11 is developed as "A type safe embedded domain specific language for SQL queries and results in C++" - a type-safe SQL DSL (Domain Specific Language) library for C++. Leveraging C++11+ template features and compile-time computation, it allows writing SQL queries directly within C++ code and checks SQL syntax and type consistency at compile time rather than runtime. It auto-generates C++ code from database schemas, achieving SQL injection attack prevention, elimination of bugs from typos, and powerful auto-completion support in IDEs.

Details

sqlpp11 2025 edition leverages the latest C++20/23 features (Concepts, consteval, Modules, etc.) to provide even more powerful type safety and compile-time optimization. It supports major databases including PostgreSQL, MySQL, SQLite, and SQL Server, enabling type-safe usage of database-specific features and SQL dialects. Distributed as a header-only library, it easily integrates with modern build systems like CMake, vcpkg, and Conan. It comprehensively supports connection pooling, prepared statements, transaction management, and other features necessary for enterprise-level application development.

Key Features

• Compile-time Type Safety: Complete type checking through C++ templates
• Zero Overhead: No runtime performance cost
• Schema Synchronization: Auto-generation of C++ code from database schemas
• SQL DSL: Writing SQL queries with natural C++ syntax
• Modern C++: Leveraging latest C++11/14/17/20/23 features
• Multi-DB Support: Comprehensive support for major database engines

Pros and Cons

Pros

• Detects SQL errors at compile time, significantly reducing runtime errors
• Structurally prevents SQL injection attacks
• Powerful auto-completion and refactoring support in IDEs
• Zero-overhead high-performance SQL execution
• Complete utilization of SQL expressiveness while maintaining type safety
• Unified interface for multiple databases

Cons

• C++-specific learning costs and template complexity
• Increased compile times and debugging difficulties
• Unsuitable for dynamic query construction
• Limited to C++, unusable in multi-language environments
• Large generated code increases project size
• Requires latest compiler versions

Reference Pages

• sqlpp11 GitHub Repository
• sqlpp11 Documentation
• sqlpp11 Tutorial

Code Examples

Setup

# CMakeLists.txt
cmake_minimum_required(VERSION 3.16)
project(sqlpp11_example)

set(CMAKE_CXX_STANDARD 20)
set(CMAKE_CXX_STANDARD_REQUIRED ON)

# Get sqlpp11 (FetchContent or submodule)
include(FetchContent)

FetchContent_Declare(
    sqlpp11
    GIT_REPOSITORY https://github.com/rbock/sqlpp11.git
    GIT_TAG main
)

FetchContent_MakeAvailable(sqlpp11)

# Database connector (PostgreSQL example)
find_package(PostgreSQL REQUIRED)

add_executable(sqlpp11_example 
    main.cpp
    generated/Users.h
    generated/Posts.h
)

target_link_libraries(sqlpp11_example 
    sqlpp11::sqlpp11
    ${PostgreSQL_LIBRARIES}
)

target_include_directories(sqlpp11_example PRIVATE 
    ${CMAKE_CURRENT_SOURCE_DIR}/generated
    ${PostgreSQL_INCLUDE_DIRS}
)

# Using vcpkg
vcpkg install sqlpp11[postgresql,mysql,sqlite3]:x64-linux

# Using Conan
# conanfile.txt
[requires]
sqlpp11/0.62

[generators]
cmake_find_package
cmake_paths

Schema Generation

# Install DDL2CPP (schema generator)
git clone https://github.com/rbock/sqlpp11.git
cd sqlpp11/scripts/ddl2cpp

# From SQL schema file
./ddl2cpp schema.sql generated schema

# From PostgreSQL database
./ddl2cpp postgresql://user:pass@localhost/dbname generated schema

# From MySQL database  
./ddl2cpp mysql://user:pass@localhost/dbname generated schema

-- schema.sql example
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author_id INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'draft'
);

CREATE INDEX idx_posts_author_id ON posts(author_id);
CREATE INDEX idx_posts_status ON posts(status);

Basic Usage

#include <sqlpp11/sqlpp11.h>
#include <sqlpp11/postgresql/connection.h>
#include <iostream>
#include <string>

#include "generated/Users.h"
#include "generated/Posts.h"

int main() {
    try {
        // Database connection configuration
        auto config = std::make_shared<sqlpp::postgresql::connection_config>();
        config->host = "localhost";
        config->port = 5432;
        config->dbname = "blog_db";
        config->user = "postgres";
        config->password = "password";
        config->connect_timeout = 30;
        
        // Create connection
        sqlpp::postgresql::connection db(config);
        
        // Table instances
        schema::Users users{};
        schema::Posts posts{};
        
        // Simple SELECT
        for (const auto& row : db(select(all_of(users)).from(users).unconditionally())) {
            std::cout << "User: " << row.username 
                      << " (" << row.email << ")" << std::endl;
        }
        
        // Conditional SELECT
        auto active_users = db(select(all_of(users))
                              .from(users)
                              .where(users.isActive == true));
        
        for (const auto& user : active_users) {
            std::cout << "Active user: " << user.username << std::endl;
        }
        
        // INSERT
        db(insert_into(users).set(
            users.username = "john_doe",
            users.email = "[email protected]",
            users.passwordHash = "hashed_password_here",
            users.isActive = true
        ));
        
        // INSERT with returning (PostgreSQL-specific)
        auto new_user_result = db(insert_into(users)
                                 .set(users.username = "jane_doe",
                                      users.email = "[email protected]",
                                      users.passwordHash = "hashed_password")
                                 .returning(users.id, users.username));
        
        if (!new_user_result.empty()) {
            auto new_user = new_user_result.front();
            std::cout << "Created user: " << new_user.username 
                      << " with ID: " << new_user.id << std::endl;
        }
        
    } catch (const std::exception& e) {
        std::cerr << "Database error: " << e.what() << std::endl;
        return 1;
    }
    
    return 0;
}

Complex Queries and JOINs

#include <sqlpp11/sqlpp11.h>
#include <sqlpp11/postgresql/connection.h>
#include <vector>
#include <chrono>

class BlogService {
private:
    sqlpp::postgresql::connection& db;
    schema::Users users{};
    schema::Posts posts{};
    
public:
    explicit BlogService(sqlpp::postgresql::connection& connection) 
        : db(connection) {}
    
    // Get posts with author information
    std::vector<PostWithAuthor> getPostsWithAuthors(const std::string& status = "published") {
        auto results = db(
            select(posts.id, posts.title, posts.content, posts.createdAt,
                   users.username, users.email)
            .from(posts.join(users).on(posts.authorId == users.id))
            .where(posts.status == status)
            .order_by(posts.createdAt.desc())
        );
        
        std::vector<PostWithAuthor> post_list;
        for (const auto& row : results) {
            PostWithAuthor post;
            post.id = row.id;
            post.title = row.title.value();
            post.content = row.content.value_or("");
            post.createdAt = row.createdAt.value();
            post.authorName = row.username.value();
            post.authorEmail = row.email.value();
            post_list.push_back(post);
        }
        
        return post_list;
    }
    
    // Get user statistics
    struct UserStats {
        int64_t userId;
        std::string username;
        int postCount;
        std::string latestPost;
    };
    
    std::vector<UserStats> getUserStatistics() {
        auto results = db(
            select(users.id, users.username, 
                   count(posts.id).as(sqlpp::alias::a),
                   max(posts.createdAt).as(sqlpp::alias::b))
            .from(users.left_outer_join(posts).on(users.id == posts.authorId))
            .group_by(users.id, users.username)
            .having(count(posts.id) > 0)
            .order_by(count(posts.id).desc())
        );
        
        std::vector<UserStats> stats;
        for (const auto& row : results) {
            UserStats stat;
            stat.userId = row.id;
            stat.username = row.username.value();
            stat.postCount = row.a.value();
            
            if (row.b.is_null()) {
                stat.latestPost = "No posts";
            } else {
                auto time_point = row.b.value();
                auto time_t = std::chrono::system_clock::to_time_t(time_point);
                stat.latestPost = std::ctime(&time_t);
            }
            
            stats.push_back(stat);
        }
        
        return stats;
    }
    
    // Subquery example
    void getActiveUsersWithRecentPosts() {
        auto recent_date = std::chrono::system_clock::now() - std::chrono::hours(24 * 7); // 1 week ago
        
        auto subquery = select(posts.authorId)
                       .from(posts)
                       .where(posts.createdAt > recent_date);
        
        auto results = db(
            select(all_of(users))
            .from(users)
            .where(users.isActive == true and 
                   users.id.in(subquery))
        );
        
        for (const auto& user : results) {
            std::cout << "Active user with recent posts: " 
                      << user.username.value() << std::endl;
        }
    }
};

Transaction Management

#include <sqlpp11/sqlpp11.h>
#include <sqlpp11/postgresql/connection.h>
#include <stdexcept>

class UserPostService {
private:
    sqlpp::postgresql::connection& db;
    schema::Users users{};
    schema::Posts posts{};
    
public:
    explicit UserPostService(sqlpp::postgresql::connection& connection) 
        : db(connection) {}
    
    // Create user and initial post in a transaction
    bool createUserWithPost(const std::string& username, 
                           const std::string& email,
                           const std::string& password_hash,
                           const std::string& post_title,
                           const std::string& post_content) {
        
        // Start transaction
        auto transaction = start_transaction(db);
        
        try {
            // Insert user
            auto user_result = db(insert_into(users)
                                 .set(users.username = username,
                                      users.email = email,
                                      users.passwordHash = password_hash,
                                      users.isActive = true)
                                 .returning(users.id));
            
            if (user_result.empty()) {
                throw std::runtime_error("Failed to create user");
            }
            
            auto user_id = user_result.front().id;
            
            // Insert post
            auto post_result = db(insert_into(posts)
                                 .set(posts.title = post_title,
                                      posts.content = post_content,
                                      posts.authorId = user_id,
                                      posts.status = "published")
                                 .returning(posts.id));
            
            if (post_result.empty()) {
                throw std::runtime_error("Failed to create post");
            }
            
            // Commit transaction
            transaction.commit();
            std::cout << "Successfully created user and post" << std::endl;
            return true;
            
        } catch (const std::exception& e) {
            // Rollback on error
            transaction.rollback();
            std::cerr << "Transaction failed: " << e.what() << std::endl;
            return false;
        }
    }
    
    // Batch operations with transaction
    bool updateMultiplePostStatuses(const std::vector<int64_t>& post_ids,
                                   const std::string& new_status) {
        if (post_ids.empty()) {
            return true;
        }
        
        auto transaction = start_transaction(db);
        
        try {
            // Update all posts in batch
            auto affected_rows = db(update(posts)
                                   .set(posts.status = new_status,
                                        posts.updatedAt = std::chrono::system_clock::now())
                                   .where(posts.id.in(sqlpp::value_list(post_ids))));
            
            if (affected_rows != post_ids.size()) {
                throw std::runtime_error("Not all posts were updated");
            }
            
            transaction.commit();
            std::cout << "Updated " << affected_rows << " posts" << std::endl;
            return true;
            
        } catch (const std::exception& e) {
            transaction.rollback();
            std::cerr << "Batch update failed: " << e.what() << std::endl;
            return false;
        }
    }
};

Prepared Statements and Performance Optimization

#include <sqlpp11/sqlpp11.h>
#include <sqlpp11/postgresql/connection.h>
#include <memory>
#include <unordered_map>

class OptimizedBlogService {
private:
    sqlpp::postgresql::connection& db;
    schema::Users users{};
    schema::Posts posts{};
    
    // Prepared statement cache
    mutable std::unordered_map<std::string, 
                              std::unique_ptr<sqlpp::prepared_statement_t<
                                  sqlpp::postgresql::connection, 
                                  sqlpp::select_t</* ... */>>>> prepared_cache;
    
public:
    explicit OptimizedBlogService(sqlpp::postgresql::connection& connection) 
        : db(connection) {}
    
    // Prepared statement for user lookup
    std::optional<User> getUserById(int64_t user_id) const {
        static auto prepared_stmt = db.prepare(
            select(all_of(users))
            .from(users)
            .where(users.id == parameter(users.id))
        );
        
        auto results = db(prepared_stmt(user_id));
        
        if (results.empty()) {
            return std::nullopt;
        }
        
        const auto& row = results.front();
        User user;
        user.id = row.id;
        user.username = row.username.value();
        user.email = row.email.value();
        user.isActive = row.isActive.value();
        
        return user;
    }
    
    // Prepared statement for posts by user
    std::vector<Post> getPostsByUserId(int64_t user_id, 
                                      const std::string& status = "published") const {
        static auto prepared_stmt = db.prepare(
            select(all_of(posts))
            .from(posts)
            .where(posts.authorId == parameter(posts.authorId) and
                   posts.status == parameter(posts.status))
            .order_by(posts.createdAt.desc())
        );
        
        auto results = db(prepared_stmt(user_id, status));
        
        std::vector<Post> post_list;
        for (const auto& row : results) {
            Post post;
            post.id = row.id;
            post.title = row.title.value();
            post.content = row.content.value_or("");
            post.authorId = row.authorId.value();
            post.status = row.status.value();
            post_list.push_back(post);
        }
        
        return post_list;
    }
    
    // Bulk insert with prepared statements
    bool bulkInsertPosts(const std::vector<NewPost>& new_posts) {
        if (new_posts.empty()) {
            return true;
        }
        
        static auto prepared_stmt = db.prepare(
            insert_into(posts)
            .set(posts.title = parameter(posts.title),
                 posts.content = parameter(posts.content),
                 posts.authorId = parameter(posts.authorId),
                 posts.status = parameter(posts.status))
        );
        
        auto transaction = start_transaction(db);
        
        try {
            for (const auto& new_post : new_posts) {
                db(prepared_stmt(new_post.title, 
                               new_post.content, 
                               new_post.authorId, 
                               new_post.status));
            }
            
            transaction.commit();
            std::cout << "Bulk inserted " << new_posts.size() << " posts" << std::endl;
            return true;
            
        } catch (const std::exception& e) {
            transaction.rollback();
            std::cerr << "Bulk insert failed: " << e.what() << std::endl;
            return false;
        }
    }
};

Advanced Features and Error Handling

#include <sqlpp11/sqlpp11.h>
#include <sqlpp11/postgresql/connection.h>
#include <sqlpp11/postgresql/exception.h>
#include <memory>
#include <chrono>
#include <exception>

// Connection pool implementation
class ConnectionPool {
private:
    std::vector<std::unique_ptr<sqlpp::postgresql::connection>> connections;
    std::queue<sqlpp::postgresql::connection*> available_connections;
    std::mutex pool_mutex;
    std::shared_ptr<sqlpp::postgresql::connection_config> config;
    size_t max_connections;
    
public:
    explicit ConnectionPool(std::shared_ptr<sqlpp::postgresql::connection_config> config,
                          size_t max_connections = 10) 
        : config(config), max_connections(max_connections) {
        
        // Pre-create connections
        for (size_t i = 0; i < max_connections; ++i) {
            auto conn = std::make_unique<sqlpp::postgresql::connection>(config);
            available_connections.push(conn.get());
            connections.push_back(std::move(conn));
        }
    }
    
    class ConnectionGuard {
    private:
        ConnectionPool& pool;
        sqlpp::postgresql::connection* connection;
        
    public:
        ConnectionGuard(ConnectionPool& pool, sqlpp::postgresql::connection* conn)
            : pool(pool), connection(conn) {}
        
        ~ConnectionGuard() {
            pool.returnConnection(connection);
        }
        
        sqlpp::postgresql::connection& operator*() { return *connection; }
        sqlpp::postgresql::connection* operator->() { return connection; }
    };
    
    ConnectionGuard getConnection() {
        std::lock_guard<std::mutex> lock(pool_mutex);
        
        if (available_connections.empty()) {
            throw std::runtime_error("No available connections");
        }
        
        auto* conn = available_connections.front();
        available_connections.pop();
        
        return ConnectionGuard(*this, conn);
    }
    
private:
    void returnConnection(sqlpp::postgresql::connection* conn) {
        std::lock_guard<std::mutex> lock(pool_mutex);
        available_connections.push(conn);
    }
};

// Advanced blog service with comprehensive error handling
class AdvancedBlogService {
private:
    ConnectionPool& pool;
    schema::Users users{};
    schema::Posts posts{};
    
public:
    explicit AdvancedBlogService(ConnectionPool& connection_pool) 
        : pool(connection_pool) {}
    
    // Comprehensive error handling example
    enum class OperationResult {
        Success,
        DatabaseError,
        ValidationError,
        NotFound,
        DuplicateKey,
        ConnectionError
    };
    
    struct ServiceResult {
        OperationResult result;
        std::string message;
        std::optional<int64_t> affected_id;
    };
    
    ServiceResult createUserSafely(const std::string& username,
                                  const std::string& email,
                                  const std::string& password_hash) {
        try {
            // Input validation
            if (username.empty() || email.empty() || password_hash.empty()) {
                return {OperationResult::ValidationError, 
                       "Username, email, and password cannot be empty", 
                       std::nullopt};
            }
            
            if (username.length() > 50) {
                return {OperationResult::ValidationError, 
                       "Username too long (max 50 characters)", 
                       std::nullopt};
            }
            
            auto conn_guard = pool.getConnection();
            auto& db = *conn_guard;
            
            // Check for duplicate username/email
            auto existing_check = db(
                select(count(users.id))
                .from(users)
                .where(users.username == username or users.email == email)
            );
            
            if (existing_check.front().count.value() > 0) {
                return {OperationResult::DuplicateKey, 
                       "Username or email already exists", 
                       std::nullopt};
            }
            
            // Insert user
            auto result = db(insert_into(users)
                           .set(users.username = username,
                                users.email = email,
                                users.passwordHash = password_hash,
                                users.isActive = true)
                           .returning(users.id));
            
            if (result.empty()) {
                return {OperationResult::DatabaseError, 
                       "Failed to create user", 
                       std::nullopt};
            }
            
            auto new_id = result.front().id;
            return {OperationResult::Success, 
                   "User created successfully", 
                   new_id};
            
        } catch (const sqlpp::postgresql::unique_violation& e) {
            return {OperationResult::DuplicateKey, 
                   std::string("Unique constraint violation: ") + e.what(), 
                   std::nullopt};
                   
        } catch (const sqlpp::postgresql::connection_error& e) {
            return {OperationResult::ConnectionError, 
                   std::string("Database connection error: ") + e.what(), 
                   std::nullopt};
                   
        } catch (const sqlpp::exception& e) {
            return {OperationResult::DatabaseError, 
                   std::string("Database error: ") + e.what(), 
                   std::nullopt};
                   
        } catch (const std::exception& e) {
            return {OperationResult::DatabaseError, 
                   std::string("Unexpected error: ") + e.what(), 
                   std::nullopt};
        }
    }
    
    // Performance monitoring
    struct QueryPerformanceMetrics {
        std::chrono::milliseconds execution_time;
        size_t rows_affected;
        std::string query_type;
    };
    
    template<typename Query>
    auto executeWithMetrics(Query&& query, const std::string& query_type) 
        -> std::pair<decltype(std::declval<sqlpp::postgresql::connection>()(query)), 
                    QueryPerformanceMetrics> {
        
        auto conn_guard = pool.getConnection();
        auto& db = *conn_guard;
        
        auto start_time = std::chrono::high_resolution_clock::now();
        
        auto result = db(std::forward<Query>(query));
        
        auto end_time = std::chrono::high_resolution_clock::now();
        auto duration = std::chrono::duration_cast<std::chrono::milliseconds>(
            end_time - start_time);
        
        QueryPerformanceMetrics metrics;
        metrics.execution_time = duration;
        metrics.rows_affected = result.size();
        metrics.query_type = query_type;
        
        // Log slow queries
        if (duration.count() > 1000) { // Log queries slower than 1 second
            std::cout << "SLOW QUERY WARNING: " << query_type 
                      << " took " << duration.count() << "ms" << std::endl;
        }
        
        return {result, metrics};
    }
};

// Usage example with comprehensive error handling
int main() {
    try {
        auto config = std::make_shared<sqlpp::postgresql::connection_config>();
        config->host = "localhost";
        config->port = 5432;
        config->dbname = "blog_db";
        config->user = "postgres";
        config->password = "password";
        config->connect_timeout = 30;
        
        ConnectionPool pool(config, 10);
        AdvancedBlogService service(pool);
        
        auto result = service.createUserSafely("john_doe", 
                                              "[email protected]", 
                                              "hashed_password");
        
        switch (result.result) {
            case AdvancedBlogService::OperationResult::Success:
                std::cout << "Success: " << result.message 
                          << " (ID: " << result.affected_id.value() << ")" << std::endl;
                break;
                
            case AdvancedBlogService::OperationResult::DuplicateKey:
                std::cout << "Duplicate key error: " << result.message << std::endl;
                break;
                
            case AdvancedBlogService::OperationResult::ValidationError:
                std::cout << "Validation error: " << result.message << std::endl;
                break;
                
            default:
                std::cout << "Error: " << result.message << std::endl;
                break;
        }
        
    } catch (const std::exception& e) {
        std::cerr << "Application error: " << e.what() << std::endl;
        return 1;
    }
    
    return 0;
}