ライブラリ

sqlpp11

概要

sqlpp11は「A type safe embedded domain specific language for SQL queries and results in C++」として開発された、C++のための型安全なSQL DSL（Domain Specific Language）ライブラリです。C++11以降のテンプレート機能とコンパイル時計算を活用し、SQLクエリをC++コード内に直接記述でき、実行時ではなくコンパイル時にSQLの構文と型の整合性をチェックします。データベーススキーマからC++コードを自動生成し、SQLインジェクション攻撃の防止、タイプミスによるバグの排除、IDEでの強力な自動補完サポートを実現します。

詳細

sqlpp11 2025年版は、C++20/23の最新機能（Concepts、consteval、Modules等）を活用し、さらに強力な型安全性とコンパイル時最適化を提供します。PostgreSQL、MySQL、SQLite、SQL Serverなどの主要データベースに対応し、各データベースの固有機能やSQL方言を型安全に利用できます。ヘッダーオンリーライブラリとして配布され、CMake、vcpkg、Conanなどのモダンなビルドシステムとの統合が容易です。接続プール、プリペアドステートメント、トランザクション管理など、エンタープライズレベルのアプリケーション開発に必要な機能を包括的にサポートします。

主な特徴

• コンパイル時型安全性: C++テンプレートによる完全な型チェック
• ゼロオーバーヘッド: 実行時のパフォーマンスコストなし
• スキーマ同期: データベーススキーマからC++コードの自動生成
• SQL DSL: 自然なC++構文でSQLクエリを記述
• モダンC++: C++11/14/17/20/23の最新機能を活用
• マルチDB対応: 主要データベースエンジンの包括的サポート

メリット・デメリット

メリット

• SQLエラーをコンパイル時に検出し実行時エラーを大幅削減
• SQLインジェクション攻撃を構造的に防止
• IDEの強力な自動補完とリファクタリングサポート
• ゼロオーバーヘッドの高性能SQL実行
• 型安全性を保ちながらSQLの表現力を完全活用
• 複数データベースの統一的なインターフェース

デメリット

• C++特有の学習コストとテンプレートの複雑性
• コンパイル時間の増加とデバッグの困難さ
• 動的クエリ構築には不向き
• C++に限定されマルチ言語環境での使用不可
• 生成されるコードが大量でプロジェクトサイズが増大
• コンパイラの最新バージョンが必要

参考ページ

• sqlpp11 GitHub リポジトリ
• sqlpp11 ドキュメント
• sqlpp11 チュートリアル

書き方の例

セットアップ

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

set(CMAKE_CXX_STANDARD 20)
set(CMAKE_CXX_STANDARD_REQUIRED ON)

# sqlpp11の取得（FetchContentまたはサブモジュール）
include(FetchContent)

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

FetchContent_MakeAvailable(sqlpp11)

# データベースコネクタ（PostgreSQL例）
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}
)

# vcpkg使用の場合
vcpkg install sqlpp11[postgresql,mysql,sqlite3]:x64-linux

# Conan使用の場合
# conanfile.txt
[requires]
sqlpp11/0.62

[generators]
cmake_find_package
cmake_paths

[options]
sqlpp11:with_postgresql=True
sqlpp11:with_mysql=True
sqlpp11:with_sqlite3=True

スキーマとコード生成

-- schema.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INTEGER NOT NULL CHECK (age >= 0),
    active BOOLEAN NOT NULL DEFAULT true,
    balance DECIMAL(10,2) DEFAULT 0.00,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    published BOOLEAN NOT NULL DEFAULT false,
    published_at TIMESTAMP,
    view_count INTEGER NOT NULL DEFAULT 0,
    rating DECIMAL(3,2) DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_active ON users(active);
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_published ON posts(published);

# DDL2CPPツールでC++コード生成
python3 ddl2cpp.py schema.sql generated Users users
python3 ddl2cpp.py schema.sql generated Posts posts

# または手動でヘッダー作成（例）

生成されるヘッダーファイル

// generated/Users.h
#pragma once

#include <sqlpp11/table.h>
#include <sqlpp11/data_types.h>
#include <sqlpp11/char_sequence.h>

namespace sqlpp_example {
namespace tables {

    struct Users {
        struct _alias_t {
            static constexpr const char _literal[] = "users";
            using _name_t = sqlpp::make_char_sequence<sizeof(_literal), _literal>;
            template<typename T>
            struct _member_t {
                T users;
                T& operator()() { return users; }
                const T& operator()() const { return users; }
            };
        };

        struct Id {
            struct _alias_t {
                static constexpr const char _literal[] = "id";
                using _name_t = sqlpp::make_char_sequence<sizeof(_literal), _literal>;
                template<typename T>
                struct _member_t {
                    T id;
                    T& operator()() { return id; }
                    const T& operator()() const { return id; }
                };
            };
            using _traits = sqlpp::make_traits<
                sqlpp::bigint,
                sqlpp::tag::must_not_insert,
                sqlpp::tag::must_not_update,
                sqlpp::tag::can_be_null
            >;
        };

        struct Name {
            struct _alias_t {
                static constexpr const char _literal[] = "name";
                using _name_t = sqlpp::make_char_sequence<sizeof(_literal), _literal>;
                template<typename T>
                struct _member_t {
                    T name;
                    T& operator()() { return name; }
                    const T& operator()() const { return name; }
                };
            };
            using _traits = sqlpp::make_traits<sqlpp::varchar>;
        };

        struct Email {
            struct _alias_t {
                static constexpr const char _literal[] = "email";
                using _name_t = sqlpp::make_char_sequence<sizeof(_literal), _literal>;
                template<typename T>
                struct _member_t {
                    T email;
                    T& operator()() { return email; }
                    const T& operator()() const { return email; }
                };
            };
            using _traits = sqlpp::make_traits<sqlpp::varchar>;
        };

        struct Age {
            struct _alias_t {
                static constexpr const char _literal[] = "age";
                using _name_t = sqlpp::make_char_sequence<sizeof(_literal), _literal>;
                template<typename T>
                struct _member_t {
                    T age;
                    T& operator()() { return age; }
                    const T& operator()() const { return age; }
                };
            };
            using _traits = sqlpp::make_traits<sqlpp::integer>;
        };

        struct Active {
            struct _alias_t {
                static constexpr const char _literal[] = "active";
                using _name_t = sqlpp::make_char_sequence<sizeof(_literal), _literal>;
                template<typename T>
                struct _member_t {
                    T active;
                    T& operator()() { return active; }
                    const T& operator()() const { return active; }
                };
            };
            using _traits = sqlpp::make_traits<sqlpp::boolean>;
        };

        struct Balance {
            struct _alias_t {
                static constexpr const char _literal[] = "balance";
                using _name_t = sqlpp::make_char_sequence<sizeof(_literal), _literal>;
                template<typename T>
                struct _member_t {
                    T balance;
                    T& operator()() { return balance; }
                    const T& operator()() const { return balance; }
                };
            };
            using _traits = sqlpp::make_traits<sqlpp::floating_point, sqlpp::tag::can_be_null>;
        };

        struct CreatedAt {
            struct _alias_t {
                static constexpr const char _literal[] = "created_at";
                using _name_t = sqlpp::make_char_sequence<sizeof(_literal), _literal>;
                template<typename T>
                struct _member_t {
                    T createdAt;
                    T& operator()() { return createdAt; }
                    const T& operator()() const { return createdAt; }
                };
            };
            using _traits = sqlpp::make_traits<sqlpp::time_point>;
        };

        struct UpdatedAt {
            struct _alias_t {
                static constexpr const char _literal[] = "updated_at";
                using _name_t = sqlpp::make_char_sequence<sizeof(_literal), _literal>;
                template<typename T>
                struct _member_t {
                    T updatedAt;
                    T& operator()() { return updatedAt; }
                    const T& operator()() const { return updatedAt; }
                };
            };
            using _traits = sqlpp::make_traits<sqlpp::time_point>;
        };

        using _column_tuple_t = std::tuple<Id, Name, Email, Age, Active, Balance, CreatedAt, UpdatedAt>;

        template<typename AliasProvider>
        struct _table_t {
            using _traits = sqlpp::make_traits<sqlpp::tag::table, sqlpp::tag::require_insert>;
            using _recursive_traits = sqlpp::make_recursive_traits<Id, Name, Email, Age, Active, Balance, CreatedAt, UpdatedAt>;
            using _alias_t = typename AliasProvider::_alias_t;

            Id id;
            Name name;
            Email email;
            Age age;
            Active active;
            Balance balance;
            CreatedAt createdAt;
            UpdatedAt updatedAt;
        };
    };

    constexpr Users users{};

} // namespace tables
} // namespace sqlpp_example

基本的な使い方

#include <iostream>
#include <sqlpp11/sqlpp11.h>
#include <sqlpp11/postgresql/postgresql.h>
#include <sqlpp11/connection_pool.h>

#include "Users.h"
#include "Posts.h"

using namespace sqlpp_example::tables;

// データベース接続設定
class DatabaseConfig {
public:
    static sqlpp::postgresql::connection_config getConfig() {
        auto config = std::make_shared<sqlpp::postgresql::connection_config>();
        config->host = "localhost";
        config->port = 5432;
        config->dbname = "sqlpp11_example";
        config->user = "postgres";
        config->password = "password";
        return *config;
    }
    
    static std::shared_ptr<sqlpp::postgresql::connection_pool> createPool() {
        auto config = getConfig();
        return std::make_shared<sqlpp::postgresql::connection_pool>(config, 10);
    }
};

// ユーザーサービス
class UserService {
private:
    std::shared_ptr<sqlpp::postgresql::connection_pool> pool_;

public:
    UserService(std::shared_ptr<sqlpp::postgresql::connection_pool> pool) 
        : pool_(pool) {}

    // ユーザー作成
    int64_t createUser(const std::string& name, const std::string& email, int age, double balance = 0.0) {
        auto conn = pool_->get();
        
        auto id = conn(
            insert_into(users)
            .set(
                users.name = name,
                users.email = email,
                users.age = age,
                users.balance = balance,
                users.active = true,
                users.createdAt = std::chrono::system_clock::now(),
                users.updatedAt = std::chrono::system_clock::now()
            )
        );
        
        std::cout << "Created user with ID: " << id << std::endl;
        return id;
    }

    // 全ユーザー取得
    void getAllUsers() {
        auto conn = pool_->get();
        
        for (const auto& row : conn(select(all_of(users)).from(users).order_by(users.createdAt.desc()))) {
            std::cout << "ID: " << row.id 
                      << ", Name: " << row.name 
                      << ", Email: " << row.email 
                      << ", Age: " << row.age 
                      << ", Active: " << (row.active ? "true" : "false")
                      << ", Balance: " << row.balance.value_or(0.0) << std::endl;
        }
    }

    // ID指定でユーザー取得
    void getUserById(int64_t userId) {
        auto conn = pool_->get();
        
        for (const auto& row : conn(select(all_of(users)).from(users).where(users.id == userId))) {
            std::cout << "Found user: " << row.name << " (" << row.email << ")" << std::endl;
            return;
        }
        std::cout << "User not found: " << userId << std::endl;
    }

    // ユーザー更新
    bool updateUser(int64_t userId, const std::string& name, const std::string& email, int age) {
        auto conn = pool_->get();
        
        auto affected = conn(
            update(users)
            .set(
                users.name = name,
                users.email = email,
                users.age = age,
                users.updatedAt = std::chrono::system_clock::now()
            )
            .where(users.id == userId)
        );
        
        bool success = affected > 0;
        if (success) {
            std::cout << "Updated user " << userId << std::endl;
        } else {
            std::cout << "Failed to update user " << userId << std::endl;
        }
        return success;
    }

    // ユーザー削除
    bool deleteUser(int64_t userId) {
        auto conn = pool_->get();
        
        auto affected = conn(remove_from(users).where(users.id == userId));
        
        bool success = affected > 0;
        if (success) {
            std::cout << "Deleted user " << userId << std::endl;
        } else {
            std::cout << "Failed to delete user " << userId << std::endl;
        }
        return success;
    }

    // 名前で検索
    void searchUsersByName(const std::string& searchTerm) {
        auto conn = pool_->get();
        
        std::cout << "=== Search Results for '" << searchTerm << "' ===" << std::endl;
        
        for (const auto& row : conn(
            select(all_of(users))
            .from(users)
            .where(users.name.like("%" + searchTerm + "%"))
            .order_by(users.name)
        )) {
            std::cout << "Found: " << row.name << " (" << row.email << ")" << std::endl;
        }
    }

    // 年齢範囲で検索
    void getUsersByAgeRange(int minAge, int maxAge) {
        auto conn = pool_->get();
        
        std::cout << "=== Users aged " << minAge << "-" << maxAge << " ===" << std::endl;
        
        for (const auto& row : conn(
            select(all_of(users))
            .from(users)
            .where(users.age >= minAge and users.age <= maxAge)
            .order_by(users.age, users.name)
        )) {
            std::cout << row.name << ": " << row.age << " years old" << std::endl;
        }
    }

    // アクティブユーザー数
    int getActiveUserCount() {
        auto conn = pool_->get();
        
        for (const auto& row : conn(select(count(users.id)).from(users).where(users.active == true))) {
            return static_cast<int>(row.count);
        }
        return 0;
    }

    // 総ユーザー数
    int getTotalUserCount() {
        auto conn = pool_->get();
        
        for (const auto& row : conn(select(count(users.id)).from(users))) {
            return static_cast<int>(row.count);
        }
        return 0;
    }

    // 残高更新
    bool updateUserBalance(int64_t userId, double amount) {
        auto conn = pool_->get();
        
        auto affected = conn(
            update(users)
            .set(
                users.balance = users.balance + amount,
                users.updatedAt = std::chrono::system_clock::now()
            )
            .where(users.id == userId)
        );
        
        return affected > 0;
    }
};

// 使用例
void demonstrateBasicOperations() {
    auto pool = DatabaseConfig::createPool();
    UserService userService(pool);
    
    try {
        // ユーザー作成
        auto userId1 = userService.createUser("Alice Johnson", "[email protected]", 28, 1000.50);
        auto userId2 = userService.createUser("Bob Smith", "[email protected]", 32, 2500.75);
        auto userId3 = userService.createUser("Charlie Brown", "[email protected]", 25, 750.25);
        
        // 全ユーザー取得
        std::cout << "\n=== All Users ===" << std::endl;
        userService.getAllUsers();
        
        // 特定ユーザー取得
        std::cout << "\n=== Get User by ID ===" << std::endl;
        userService.getUserById(userId1);
        
        // ユーザー更新
        std::cout << "\n=== Update User ===" << std::endl;
        userService.updateUser(userId1, "Alice Johnson Smith", "[email protected]", 29);
        
        // 検索機能
        userService.searchUsersByName("Alice");
        userService.getUsersByAgeRange(25, 30);
        
        // 統計情報
        std::cout << "\n=== Statistics ===" << std::endl;
        std::cout << "Total users: " << userService.getTotalUserCount() << std::endl;
        std::cout << "Active users: " << userService.getActiveUserCount() << std::endl;
        
        // 残高更新
        std::cout << "\n=== Balance Update ===" << std::endl;
        userService.updateUserBalance(userId1, 250.0);
        userService.getUserById(userId1);
        
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
}

投稿機能とJOIN操作

// generated/Posts.h の内容（省略 - Usersと同様の構造）

// 投稿サービス
class PostService {
private:
    std::shared_ptr<sqlpp::postgresql::connection_pool> pool_;

public:
    PostService(std::shared_ptr<sqlpp::postgresql::connection_pool> pool) 
        : pool_(pool) {}

    // 投稿作成
    int64_t createPost(int64_t userId, const std::string& title, const std::string& content) {
        auto conn = pool_->get();
        
        auto id = conn(
            insert_into(posts)
            .set(
                posts.userId = userId,
                posts.title = title,
                posts.content = content,
                posts.published = false,
                posts.viewCount = 0,
                posts.createdAt = std::chrono::system_clock::now(),
                posts.updatedAt = std::chrono::system_clock::now()
            )
        );
        
        std::cout << "Created post with ID: " << id << std::endl;
        return id;
    }

    // 投稿取得
    void getPostById(int64_t postId) {
        auto conn = pool_->get();
        
        for (const auto& row : conn(select(all_of(posts)).from(posts).where(posts.id == postId))) {
            std::cout << "Post: " << row.title 
                      << " (Views: " << row.viewCount 
                      << ", Published: " << (row.published ? "Yes" : "No") << ")" << std::endl;
            return;
        }
        std::cout << "Post not found: " << postId << std::endl;
    }

    // ユーザーの投稿一覧
    void getPostsByUser(int64_t userId) {
        auto conn = pool_->get();
        
        std::cout << "=== Posts by User " << userId << " ===" << std::endl;
        
        for (const auto& row : conn(
            select(all_of(posts))
            .from(posts)
            .where(posts.userId == userId)
            .order_by(posts.createdAt.desc())
        )) {
            std::cout << "Post: " << row.title 
                      << " (Views: " << row.viewCount << ")" << std::endl;
        }
    }

    // 公開済み投稿取得
    void getPublishedPosts(int limit = 10) {
        auto conn = pool_->get();
        
        std::cout << "=== Published Posts ===" << std::endl;
        
        for (const auto& row : conn(
            select(all_of(posts))
            .from(posts)
            .where(posts.published == true)
            .order_by(posts.publishedAt.desc())
            .limit(static_cast<std::size_t>(limit))
        )) {
            std::cout << "Post: " << row.title 
                      << " (Views: " << row.viewCount << ")" << std::endl;
        }
    }

    // 投稿とユーザー情報結合取得
    void getPostsWithAuthors(int limit = 10) {
        auto conn = pool_->get();
        
        std::cout << "=== Posts with Authors ===" << std::endl;
        
        for (const auto& row : conn(
            select(posts.id, posts.title, posts.viewCount, users.name.as(sqlpp::alias::a))
            .from(posts.join(users).on(posts.userId == users.id))
            .order_by(posts.createdAt.desc())
            .limit(static_cast<std::size_t>(limit))
        )) {
            std::cout << "Post: " << row.title 
                      << " by " << row.a 
                      << " (Views: " << row.viewCount << ")" << std::endl;
        }
    }

    // 投稿公開
    bool publishPost(int64_t postId) {
        auto conn = pool_->get();
        
        auto affected = conn(
            update(posts)
            .set(
                posts.published = true,
                posts.publishedAt = std::chrono::system_clock::now(),
                posts.updatedAt = std::chrono::system_clock::now()
            )
            .where(posts.id == postId)
        );
        
        bool success = affected > 0;
        if (success) {
            std::cout << "Published post " << postId << std::endl;
        }
        return success;
    }

    // ビューカウント増加
    bool incrementViewCount(int64_t postId) {
        auto conn = pool_->get();
        
        auto affected = conn(
            update(posts)
            .set(
                posts.viewCount = posts.viewCount + 1,
                posts.updatedAt = std::chrono::system_clock::now()
            )
            .where(posts.id == postId)
        );
        
        return affected > 0;
    }

    // 人気投稿取得（ビューカウント順）
    void getPopularPosts(int limit = 5) {
        auto conn = pool_->get();
        
        std::cout << "=== Popular Posts ===" << std::endl;
        
        for (const auto& row : conn(
            select(all_of(posts))
            .from(posts)
            .where(posts.published == true)
            .order_by(posts.viewCount.desc())
            .limit(static_cast<std::size_t>(limit))
        )) {
            std::cout << "Popular: " << row.title 
                      << " (" << row.viewCount << " views)" << std::endl;
        }
    }

    // 統計情報取得
    void getPostStatistics() {
        auto conn = pool_->get();
        
        std::cout << "=== Post Statistics ===" << std::endl;
        
        // 総投稿数
        for (const auto& row : conn(select(count(posts.id)).from(posts))) {
            std::cout << "Total posts: " << row.count << std::endl;
        }
        
        // 公開済み投稿数
        for (const auto& row : conn(select(count(posts.id)).from(posts).where(posts.published == true))) {
            std::cout << "Published posts: " << row.count << std::endl;
        }
        
        // 平均ビューカウント
        for (const auto& row : conn(select(avg(posts.viewCount)).from(posts))) {
            std::cout << "Average view count: " << row.avg.value_or(0.0) << std::endl;
        }
    }

    // ユーザー別投稿数
    void getUserPostCounts() {
        auto conn = pool_->get();
        
        std::cout << "=== User Post Counts ===" << std::endl;
        
        for (const auto& row : conn(
            select(users.name, count(posts.id).as(sqlpp::alias::b))
            .from(users.left_outer_join(posts).on(users.id == posts.userId))
            .group_by(users.id, users.name)
            .order_by(sqlpp::alias::b.desc())
        )) {
            std::cout << row.name << ": " << row.b << " posts" << std::endl;
        }
    }
};

// 使用例
void demonstratePostOperations() {
    auto pool = DatabaseConfig::createPool();
    UserService userService(pool);
    PostService postService(pool);
    
    try {
        // ユーザー作成
        auto userId = userService.createUser("John Doe", "[email protected]", 30, 1500.0);
        
        // 投稿作成
        auto postId1 = postService.createPost(userId, "My First Post", "This is the content of my first post.");
        auto postId2 = postService.createPost(userId, "Another Post", "This is another interesting post.");
        auto postId3 = postService.createPost(userId, "Technical Article", "This is a technical article about C++.");
        
        // 投稿公開
        postService.publishPost(postId1);
        postService.publishPost(postId3);
        
        // ビューカウント増加
        postService.incrementViewCount(postId1);
        postService.incrementViewCount(postId1);
        postService.incrementViewCount(postId3);
        
        // 各種取得
        postService.getPostsByUser(userId);
        postService.getPublishedPosts();
        postService.getPostsWithAuthors();
        postService.getPopularPosts();
        
        // 統計情報
        postService.getPostStatistics();
        postService.getUserPostCounts();
        
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
}

トランザクションと高度な機能

// トランザクションサービス
class TransactionService {
private:
    std::shared_ptr<sqlpp::postgresql::connection_pool> pool_;

public:
    TransactionService(std::shared_ptr<sqlpp::postgresql::connection_pool> pool) 
        : pool_(pool) {}

    // トランザクション：ユーザーと初期投稿を同時作成
    std::pair<int64_t, std::vector<int64_t>> createUserWithPosts(
        const std::string& userName, const std::string& userEmail, int userAge,
        const std::vector<std::pair<std::string, std::string>>& initialPosts) {
        
        auto conn = pool_->get();
        auto tx = start_transaction(*conn);
        
        try {
            // ユーザー作成
            auto userId = (*conn)(
                insert_into(users)
                .set(
                    users.name = userName,
                    users.email = userEmail,
                    users.age = userAge,
                    users.active = true,
                    users.balance = 0.0,
                    users.createdAt = std::chrono::system_clock::now(),
                    users.updatedAt = std::chrono::system_clock::now()
                )
            );
            
            std::vector<int64_t> postIds;
            
            // 初期投稿作成
            for (const auto& [title, content] : initialPosts) {
                auto postId = (*conn)(
                    insert_into(posts)
                    .set(
                        posts.userId = userId,
                        posts.title = title,
                        posts.content = content,
                        posts.published = false,
                        posts.viewCount = 0,
                        posts.createdAt = std::chrono::system_clock::now(),
                        posts.updatedAt = std::chrono::system_clock::now()
                    )
                );
                postIds.push_back(postId);
            }
            
            tx.commit();
            
            std::cout << "Created user " << userId << " with " << postIds.size() << " posts" << std::endl;
            return {userId, postIds};
            
        } catch (const std::exception& e) {
            tx.rollback();
            std::cerr << "Transaction failed: " << e.what() << std::endl;
            throw;
        }
    }

    // トランザクション：ユーザー間で投稿を移譲
    int transferPosts(int64_t fromUserId, int64_t toUserId) {
        auto conn = pool_->get();
        auto tx = start_transaction(*conn);
        
        try {
            // 両方のユーザーの存在確認
            auto fromUserCount = (*conn)(select(count(users.id)).from(users).where(users.id == fromUserId));
            auto toUserCount = (*conn)(select(count(users.id)).from(users).where(users.id == toUserId));
            
            bool fromUserExists = false, toUserExists = false;
            for (const auto& row : fromUserCount) {
                fromUserExists = row.count > 0;
            }
            for (const auto& row : toUserCount) {
                toUserExists = row.count > 0;
            }
            
            if (!fromUserExists || !toUserExists) {
                throw std::runtime_error("One or both users not found");
            }
            
            // 投稿を移譲
            auto affected = (*conn)(
                update(posts)
                .set(
                    posts.userId = toUserId,
                    posts.updatedAt = std::chrono::system_clock::now()
                )
                .where(posts.userId == fromUserId)
            );
            
            tx.commit();
            
            std::cout << "Transferred " << affected << " posts from user " 
                      << fromUserId << " to user " << toUserId << std::endl;
            return static_cast<int>(affected);
            
        } catch (const std::exception& e) {
            tx.rollback();
            std::cerr << "Transfer failed: " << e.what() << std::endl;
            throw;
        }
    }

    // バッチ処理：複数ユーザーの年齢を一括更新
    int batchUpdateUserAges(const std::vector<std::pair<int64_t, int>>& updates) {
        auto conn = pool_->get();
        auto tx = start_transaction(*conn);
        
        try {
            int totalUpdated = 0;
            
            for (const auto& [userId, newAge] : updates) {
                auto affected = (*conn)(
                    update(users)
                    .set(
                        users.age = newAge,
                        users.updatedAt = std::chrono::system_clock::now()
                    )
                    .where(users.id == userId)
                );
                totalUpdated += static_cast<int>(affected);
            }
            
            tx.commit();
            
            std::cout << "Updated ages for " << totalUpdated << " users" << std::endl;
            return totalUpdated;
            
        } catch (const std::exception& e) {
            tx.rollback();
            std::cerr << "Batch update failed: " << e.what() << std::endl;
            throw;
        }
    }

    // 条件付きクリーンアップ：非アクティブユーザーとその投稿を削除
    std::pair<int, int> cleanupInactiveUsers() {
        auto conn = pool_->get();
        auto tx = start_transaction(*conn);
        
        try {
            // 非アクティブユーザーの投稿を削除
            auto deletedPosts = (*conn)(
                remove_from(posts)
                .using_(users)
                .where(posts.userId == users.id and users.active == false)
            );
            
            // 非アクティブユーザーを削除
            auto deletedUsers = (*conn)(
                remove_from(users)
                .where(users.active == false)
            );
            
            tx.commit();
            
            std::cout << "Cleanup: deleted " << deletedUsers << " users and " 
                      << deletedPosts << " posts" << std::endl;
            return {static_cast<int>(deletedUsers), static_cast<int>(deletedPosts)};
            
        } catch (const std::exception& e) {
            tx.rollback();
            std::cerr << "Cleanup failed: " << e.what() << std::endl;
            throw;
        }
    }

    // 複雑な更新：残高に基づく投稿公開
    int publishPostsByUserBalance(double minBalance) {
        auto conn = pool_->get();
        auto tx = start_transaction(*conn);
        
        try {
            // 指定残高以上のユーザーの未公開投稿を公開
            auto affected = (*conn)(
                update(posts)
                .set(
                    posts.published = true,
                    posts.publishedAt = std::chrono::system_clock::now(),
                    posts.updatedAt = std::chrono::system_clock::now()
                )
                .from(users)
                .where(
                    posts.userId == users.id and
                    users.balance >= minBalance and
                    posts.published == false
                )
            );
            
            tx.commit();
            
            std::cout << "Published " << affected << " posts from users with balance >= " 
                      << minBalance << std::endl;
            return static_cast<int>(affected);
            
        } catch (const std::exception& e) {
            tx.rollback();
            std::cerr << "Conditional publish failed: " << e.what() << std::endl;
            throw;
        }
    }
};

// 高度なクエリサービス
class AdvancedQueryService {
private:
    std::shared_ptr<sqlpp::postgresql::connection_pool> pool_;

public:
    AdvancedQueryService(std::shared_ptr<sqlpp::postgresql::connection_pool> pool) 
        : pool_(pool) {}

    // 条件付き集計クエリ
    void getConditionalStatistics() {
        auto conn = pool_->get();
        
        std::cout << "=== Conditional Statistics ===" << std::endl;
        
        // 年齢グループ別統計
        for (const auto& row : conn(
            select(
                case_when(users.age < 25).then("Young")
                    .when(users.age < 35).then("Adult")
                    .else_("Senior").as(sqlpp::alias::c),
                count(users.id).as(sqlpp::alias::d),
                avg(users.balance).as(sqlpp::alias::e)
            )
            .from(users)
            .where(users.active == true)
            .group_by(sqlpp::alias::c)
            .order_by(sqlpp::alias::d.desc())
        )) {
            std::cout << "Age group: " << row.c 
                      << ", Count: " << row.d 
                      << ", Avg Balance: " << row.e.value_or(0.0) << std::endl;
        }
    }

    // ウィンドウ関数の使用例
    void getUserRankings() {
        auto conn = pool_->get();
        
        std::cout << "=== User Rankings ===" << std::endl;
        
        // PostgreSQL固有のウィンドウ関数（注意：sqlpp11ではサポートが限定的）
        // ROW_NUMBER, RANK, DENSE_RANK等は生SQLまたは拡張が必要
        auto query = R"(
            SELECT 
                name,
                balance,
                ROW_NUMBER() OVER (ORDER BY balance DESC) as rank,
                PERCENT_RANK() OVER (ORDER BY balance) as percentile
            FROM users 
            WHERE active = true
            ORDER BY balance DESC
            LIMIT 10
        )";
        
        auto prepared = conn->prepare(query);
        auto result = conn->execute(prepared);
        
        // 結果の処理（低レベルAPI使用）
        for (const auto& row : result) {
            std::cout << "User: " << row.at("name").as<std::string>()
                      << ", Balance: " << row.at("balance").as<double>()
                      << ", Rank: " << row.at("rank").as<int>() << std::endl;
        }
    }

    // 複雑なJOINとサブクエリ
    void getComplexJoinResults() {
        auto conn = pool_->get();
        
        std::cout << "=== Complex Join Results ===" << std::endl;
        
        // ユーザー、投稿数、平均ビューカウントを結合
        for (const auto& row : conn(
            select(
                users.name,
                users.balance,
                count(posts.id).as(sqlpp::alias::f),
                avg(posts.viewCount).as(sqlpp::alias::g),
                max(posts.createdAt).as(sqlpp::alias::h)
            )
            .from(
                users.left_outer_join(posts).on(users.id == posts.userId)
            )
            .where(users.active == true)
            .group_by(users.id, users.name, users.balance)
            .having(count(posts.id) > 0)
            .order_by(sqlpp::alias::f.desc())
        )) {
            std::cout << "User: " << row.name 
                      << ", Posts: " << row.f 
                      << ", Avg Views: " << row.g.value_or(0.0)
                      << ", Balance: " << row.balance.value_or(0.0) << std::endl;
        }
    }
};

// 使用例
void demonstrateAdvancedFeatures() {
    auto pool = DatabaseConfig::createPool();
    TransactionService txService(pool);
    AdvancedQueryService advancedService(pool);
    
    try {
        // ユーザーと初期投稿を同時作成
        std::vector<std::pair<std::string, std::string>> initialPosts = {
            {"Welcome Post", "Welcome to my blog!"},
            {"About Me", "Let me introduce myself..."},
            {"Technical Article", "This is a technical article."}
        };
        
        auto [userId, postIds] = txService.createUserWithPosts(
            "Content Creator", "[email protected]", 25, initialPosts
        );
        
        // 別のユーザー作成
        UserService userService(pool);
        auto targetUserId = userService.createUser("Another User", "[email protected]", 30, 3000.0);
        
        // 投稿移譲
        int transferredCount = txService.transferPosts(userId, targetUserId);
        
        // バッチ年齢更新
        std::vector<std::pair<int64_t, int>> ageUpdates = {
            {userId, 26},
            {targetUserId, 31}
        };
        txService.batchUpdateUserAges(ageUpdates);
        
        // 残高に基づく投稿公開
        txService.publishPostsByUserBalance(2000.0);
        
        // 高度なクエリ実行
        advancedService.getConditionalStatistics();
        advancedService.getUserRankings();
        advancedService.getComplexJoinResults();
        
        // ユーザーを非アクティブに設定してクリーンアップ
        userService.updateUser(userId, "Content Creator", "[email protected]", 26);
        // 非アクティブ設定の実装は省略
        
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
}

エラーハンドリングとユーティリティ

// カスタム例外クラス
class DatabaseException : public std::runtime_error {
public:
    explicit DatabaseException(const std::string& message) 
        : std::runtime_error("Database error: " + message) {}
};

class ValidationException : public std::runtime_error {
public:
    explicit ValidationException(const std::string& message) 
        : std::runtime_error("Validation error: " + message) {}
};

// 安全なユーザーサービス
class SafeUserService {
private:
    std::shared_ptr<sqlpp::postgresql::connection_pool> pool_;

    // バリデーション関数
    void validateUserData(const std::string& name, const std::string& email, int age) {
        if (name.empty()) {
            throw ValidationException("Name cannot be empty");
        }
        if (email.empty() || email.find('@') == std::string::npos) {
            throw ValidationException("Invalid email format");
        }
        if (age < 0 || age > 150) {
            throw ValidationException("Age must be between 0 and 150");
        }
    }

    // メール重複チェック
    bool isEmailExists(const std::string& email, int64_t excludeUserId = -1) {
        auto conn = pool_->get();
        
        auto query = select(count(users.id)).from(users).where(users.email == email);
        if (excludeUserId != -1) {
            query = query.where(users.id != excludeUserId);
        }
        
        for (const auto& row : conn(query)) {
            return row.count > 0;
        }
        return false;
    }

public:
    SafeUserService(std::shared_ptr<sqlpp::postgresql::connection_pool> pool) 
        : pool_(pool) {}

    // 安全なユーザー作成
    int64_t createUserSafely(const std::string& name, const std::string& email, int age, double balance = 0.0) {
        try {
            // バリデーション
            validateUserData(name, email, age);
            
            // メール重複チェック
            if (isEmailExists(email)) {
                throw ValidationException("Email already exists: " + email);
            }
            
            // ユーザー作成
            auto conn = pool_->get();
            auto id = conn(
                insert_into(users)
                .set(
                    users.name = name,
                    users.email = email,
                    users.age = age,
                    users.balance = balance,
                    users.active = true,
                    users.createdAt = std::chrono::system_clock::now(),
                    users.updatedAt = std::chrono::system_clock::now()
                )
            );
            
            std::cout << "✓ Successfully created user: " << name << " (ID: " << id << ")" << std::endl;
            return id;
            
        } catch (const ValidationException& e) {
            std::cerr << "✗ " << e.what() << std::endl;
            throw;
        } catch (const sqlpp::exception& e) {
            std::cerr << "✗ Database error: " << e.what() << std::endl;
            throw DatabaseException(e.what());
        } catch (const std::exception& e) {
            std::cerr << "✗ Unexpected error: " << e.what() << std::endl;
            throw;
        }
    }

    // 安全なユーザー取得
    std::optional<std::tuple<int64_t, std::string, std::string, int, bool, double>> getUserSafely(int64_t userId) {
        try {
            auto conn = pool_->get();
            
            for (const auto& row : conn(select(all_of(users)).from(users).where(users.id == userId))) {
                return std::make_tuple(
                    row.id, row.name, row.email, row.age, 
                    row.active, row.balance.value_or(0.0)
                );
            }
            
            std::cout << "✗ User not found: ID " << userId << std::endl;
            return std::nullopt;
            
        } catch (const sqlpp::exception& e) {
            std::cerr << "✗ Database error: " << e.what() << std::endl;
            throw DatabaseException(e.what());
        }
    }

    // 安全なユーザー更新
    bool updateUserSafely(int64_t userId, const std::string& name, const std::string& email, int age) {
        try {
            // バリデーション
            validateUserData(name, email, age);
            
            // ユーザー存在確認
            if (!getUserSafely(userId)) {
                return false;
            }
            
            // メール重複チェック（自分以外）
            if (isEmailExists(email, userId)) {
                throw ValidationException("Email already exists: " + email);
            }
            
            // 更新実行
            auto conn = pool_->get();
            auto affected = conn(
                update(users)
                .set(
                    users.name = name,
                    users.email = email,
                    users.age = age,
                    users.updatedAt = std::chrono::system_clock::now()
                )
                .where(users.id == userId)
            );
            
            bool success = affected > 0;
            if (success) {
                std::cout << "✓ Successfully updated user: " << userId << std::endl;
            }
            return success;
            
        } catch (const ValidationException& e) {
            std::cerr << "✗ " << e.what() << std::endl;
            return false;
        } catch (const sqlpp::exception& e) {
            std::cerr << "✗ Database error: " << e.what() << std::endl;
            throw DatabaseException(e.what());
        }
    }

    // 接続ヘルスチェック
    bool healthCheck() {
        try {
            auto conn = pool_->get();
            
            // 簡単なクエリを実行
            for (const auto& row : conn(select(count(users.id)).from(users))) {
                std::cout << "✓ Database health check passed (Total users: " << row.count << ")" << std::endl;
                return true;
            }
            
        } catch (const std::exception& e) {
            std::cerr << "✗ Health check failed: " << e.what() << std::endl;
            return false;
        }
        return false;
    }
};

// パフォーマンス測定ユーティリティ
class PerformanceTimer {
private:
    std::chrono::high_resolution_clock::time_point start_;
    std::string operation_;

public:
    explicit PerformanceTimer(const std::string& operation) 
        : start_(std::chrono::high_resolution_clock::now()), operation_(operation) {}
    
    ~PerformanceTimer() {
        auto end = std::chrono::high_resolution_clock::now();
        auto duration = std::chrono::duration_cast<std::chrono::milliseconds>(end - start_);
        std::cout << "⏱️ " << operation_ << " took " << duration.count() << "ms" << std::endl;
    }
};

// 使用例とエラーハンドリング
void demonstrateErrorHandling() {
    auto pool = DatabaseConfig::createPool();
    SafeUserService safeService(pool);
    
    std::cout << "=== Error Handling Demo ===" << std::endl;
    
    // ヘルスチェック
    if (!safeService.healthCheck()) {
        return;
    }
    
    try {
        // 正常なユーザー作成
        {
            PerformanceTimer timer("User creation");
            auto userId = safeService.createUserSafely("Jane Doe", "[email protected]", 28, 1200.0);
            
            // 正常な更新
            safeService.updateUserSafely(userId, "Jane Smith", "[email protected]", 29);
        }
        
        // バリデーションエラーのテスト
        std::cout << "\n--- Testing validation errors ---" << std::endl;
        try {
            safeService.createUserSafely("", "invalid-email", -5);
        } catch (const ValidationException&) {
            std::cout << "✓ Correctly caught validation error" << std::endl;
        }
        
        // 重複メールエラーのテスト
        std::cout << "\n--- Testing duplicate email ---" << std::endl;
        try {
            safeService.createUserSafely("Another Jane", "[email protected]", 30);
        } catch (const ValidationException&) {
            std::cout << "✓ Correctly caught duplicate email error" << std::endl;
        }
        
        // 存在しないユーザーの取得テスト
        std::cout << "\n--- Testing non-existent user ---" << std::endl;
        auto nonExistentUser = safeService.getUserSafely(999999);
        if (!nonExistentUser) {
            std::cout << "✓ Correctly handled non-existent user" << std::endl;
        }
        
    } catch (const DatabaseException& e) {
        std::cerr << "Database exception: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Unexpected exception: " << e.what() << std::endl;
    }
}

// メイン関数
int main() {
    std::cout << "=== sqlpp11 Demo ===" << std::endl << std::endl;
    
    try {
        demonstrateBasicOperations();
        std::cout << std::endl << std::string(50, '=') << std::endl << std::endl;
        
        demonstratePostOperations();
        std::cout << std::endl << std::string(50, '=') << std::endl << std::endl;
        
        demonstrateAdvancedFeatures();
        std::cout << std::endl << std::string(50, '=') << std::endl << std::endl;
        
        demonstrateErrorHandling();
        
    } catch (const std::exception& e) {
        std::cerr << "Fatal error: " << e.what() << std::endl;
        return 1;
    }
    
    return 0;
}