Library

SOCI

Overview

SOCI is a "Database Access Library for C++" that provides a lightweight and portable database access layer for C++ applications. It offers a unified API for multiple database engines (PostgreSQL, MySQL, SQLite, Oracle, DB2, Firebird, etc.) and achieves efficient database operations leveraging C++'s type safety and RAII (Resource Acquisition Is Initialization) pattern. It comprehensively supports features necessary for modern C++ development including natural integration with STL containers, prepared statements, and connection pooling.

Details

SOCI 2025 edition supports the C++17 standard and provides a database programming experience that maximizes modern C++ features (smart pointers, move semantics, range-based for loops, etc.). Built on foundations of type-safe query execution, automatic resource management, and exception safety, it achieves the reliability and performance required for enterprise application development. Multi-database portability and backend transparency provide flexibility in database technology choices. Easy integration with modern C++ development environments through CMake support and package manager compatibility (vcpkg, Conan, etc.).

Key Features

• Multi-Database: Unified API for major RDBMS systems
• Type Safety: Complete integration with C++ type system
• RAII Design: Automatic resource management and exception safety
• STL Integration: Natural collaboration with STL containers
• Portable: Cross-platform compatibility
• Prepared Statements: SQL injection protection and performance optimization

Pros and Cons

Pros

• High portability between multiple databases with unified API
• Type-safe and intuitive programming model that feels natural to C++
• Reliable resource management and exception safety through RAII design
• C++ developer-friendly design with natural STL integration
• Lightweight implementation with minimal overhead
• Rich database backend support

Cons

• No ORM functionality, requires raw SQL-based programming
• C++-specific learning curve and memory management complexity
• Additional work required for integration with modern web frameworks
• Limited documentation and Japanese language resources
• Lack of high-level database abstraction layers
• Not suitable for complex object mapping

Reference Pages

• SOCI Official Site
• SOCI GitHub Repository
• SOCI Documentation

Code Examples

Setup

// CMakeLists.txt
cmake_minimum_required(VERSION 3.16)
project(soci_example)

set(CMAKE_CXX_STANDARD 17)
set(CMAKE_CXX_STANDARD_REQUIRED ON)

# Find SOCI
find_package(SOCI REQUIRED)

# Find database backend (PostgreSQL example)
find_package(PostgreSQL REQUIRED)

add_executable(soci_example main.cpp)

# Link SOCI
target_link_libraries(soci_example 
    SOCI::soci_core 
    SOCI::soci_postgresql
    ${PostgreSQL_LIBRARIES}
)

// vcpkg installation
/*
vcpkg install soci[postgresql,mysql,sqlite3]:x64-linux
vcpkg install soci[postgresql,mysql,sqlite3]:x64-windows
*/

// Conan installation
/*
[requires]
soci/4.0.3

[generators]
cmake_find_package
cmake_paths

[options]
soci:shared=True
soci:with_postgresql=True
soci:with_mysql=True
soci:with_sqlite3=True
*/

Basic Usage

#include <soci/soci.h>
#include <soci/postgresql/soci-postgresql.h>
#include <iostream>
#include <string>
#include <vector>
#include <memory>

using namespace soci;

// User structure
struct User {
    int id;
    std::string name;
    std::string email;
    int age;
    std::string created_at;
    
    User() = default;
    User(int id, const std::string& name, const std::string& email, int age)
        : id(id), name(name), email(email), age(age) {}
};

// Type conversion configuration for User table
namespace soci {
    template<>
    struct type_conversion<User> {
        typedef values base_type;
        
        static void from_base(const values& v, indicator& ind, User& user) {
            user.id = v.get<int>("id");
            user.name = v.get<std::string>("name");
            user.email = v.get<std::string>("email");
            user.age = v.get<int>("age");
            user.created_at = v.get<std::string>("created_at");
            ind = i_ok;
        }
        
        static void to_base(const User& user, values& v, indicator& ind) {
            v.set("id", user.id);
            v.set("name", user.name);
            v.set("email", user.email);
            v.set("age", user.age);
            v.set("created_at", user.created_at);
            ind = i_ok;
        }
    };
}

class UserDatabase {
private:
    std::unique_ptr<session> sql_;
    
public:
    // Database connection
    explicit UserDatabase(const std::string& connection_string) {
        try {
            sql_ = std::make_unique<session>(postgresql, connection_string);
            
            // Create tables
            createTables();
            
            std::cout << "Database connected successfully" << std::endl;
        } catch (const soci_error& e) {
            std::cerr << "Database connection failed: " << e.what() << std::endl;
            throw;
        }
    }
    
    ~UserDatabase() = default;
    
    // Create tables
    void createTables() {
        try {
            *sql_ << R"(
                CREATE TABLE IF NOT EXISTS users (
                    id SERIAL PRIMARY KEY,
                    name VARCHAR(255) NOT NULL,
                    email VARCHAR(255) UNIQUE NOT NULL,
                    age INTEGER NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            )";
            
            *sql_ << R"(
                CREATE TABLE IF NOT EXISTS posts (
                    id SERIAL PRIMARY KEY,
                    title VARCHAR(255) NOT NULL,
                    content TEXT,
                    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
                    published BOOLEAN DEFAULT FALSE,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            )";
            
            std::cout << "Tables created successfully" << std::endl;
        } catch (const soci_error& e) {
            std::cerr << "Table creation failed: " << e.what() << std::endl;
            throw;
        }
    }
    
    // Add user
    int addUser(const std::string& name, const std::string& email, int age) {
        try {
            int user_id;
            *sql_ << "INSERT INTO users (name, email, age) VALUES (:name, :email, :age) RETURNING id",
                use(name), use(email), use(age), into(user_id);
                
            std::cout << "User added with ID: " << user_id << std::endl;
            return user_id;
        } catch (const soci_error& e) {
            std::cerr << "Failed to add user: " << e.what() << std::endl;
            throw;
        }
    }
    
    // Get all users
    std::vector<User> getAllUsers() {
        try {
            std::vector<User> users;
            rowset<User> rs = (sql_->prepare << "SELECT id, name, email, age, created_at FROM users ORDER BY id");
            
            for (const auto& user : rs) {
                users.push_back(user);
            }
            
            return users;
        } catch (const soci_error& e) {
            std::cerr << "Failed to get users: " << e.what() << std::endl;
            throw;
        }
    }
    
    // Get user by ID
    std::unique_ptr<User> getUserById(int id) {
        try {
            auto user = std::make_unique<User>();
            indicator ind;
            
            *sql_ << "SELECT id, name, email, age, created_at FROM users WHERE id = :id",
                into(*user, ind), use(id);
                
            if (ind == i_ok) {
                return user;
            } else {
                return nullptr;
            }
        } catch (const soci_error& e) {
            std::cerr << "Failed to get user: " << e.what() << std::endl;
            return nullptr;
        }
    }
    
    // Update user
    bool updateUser(int id, const std::string& name, const std::string& email, int age) {
        try {
            statement st = (sql_->prepare <<
                "UPDATE users SET name = :name, email = :email, age = :age WHERE id = :id",
                use(name), use(email), use(age), use(id));
            
            st.execute();
            return st.get_affected_rows() > 0;
        } catch (const soci_error& e) {
            std::cerr << "Failed to update user: " << e.what() << std::endl;
            return false;
        }
    }
    
    // Delete user
    bool deleteUser(int id) {
        try {
            statement st = (sql_->prepare << "DELETE FROM users WHERE id = :id", use(id));
            st.execute();
            return st.get_affected_rows() > 0;
        } catch (const soci_error& e) {
            std::cerr << "Failed to delete user: " << e.what() << std::endl;
            return false;
        }
    }
    
    // Search functionality
    std::vector<User> searchUsers(const std::string& searchTerm) {
        try {
            std::vector<User> users;
            std::string pattern = "%" + searchTerm + "%";
            
            rowset<User> rs = (sql_->prepare <<
                "SELECT id, name, email, age, created_at FROM users "
                "WHERE name ILIKE :pattern OR email ILIKE :pattern "
                "ORDER BY name",
                use(pattern));
            
            for (const auto& user : rs) {
                users.push_back(user);
            }
            
            return users;
        } catch (const soci_error& e) {
            std::cerr << "Failed to search users: " << e.what() << std::endl;
            throw;
        }
    }
    
    // Filter by age range
    std::vector<User> getUsersByAgeRange(int minAge, int maxAge) {
        try {
            std::vector<User> users;
            
            rowset<User> rs = (sql_->prepare <<
                "SELECT id, name, email, age, created_at FROM users "
                "WHERE age BETWEEN :min_age AND :max_age "
                "ORDER BY age, name",
                use(minAge), use(maxAge));
            
            for (const auto& user : rs) {
                users.push_back(user);
            }
            
            return users;
        } catch (const soci_error& e) {
            std::cerr << "Failed to get users by age range: " << e.what() << std::endl;
            throw;
        }
    }
    
    // Get statistics
    struct UserStats {
        int total_users;
        double average_age;
        int min_age;
        int max_age;
    };
    
    UserStats getUserStatistics() {
        try {
            UserStats stats{};
            
            *sql_ << "SELECT COUNT(*), AVG(age), MIN(age), MAX(age) FROM users",
                into(stats.total_users), into(stats.average_age), 
                into(stats.min_age), into(stats.max_age);
                
            return stats;
        } catch (const soci_error& e) {
            std::cerr << "Failed to get user statistics: " << e.what() << std::endl;
            throw;
        }
    }
};

// Usage example
void demonstrateBasicOperations() {
    try {
        UserDatabase db("postgresql://user:password@localhost/testdb");
        
        // Add users
        int userId1 = db.addUser("Alice Smith", "[email protected]", 25);
        int userId2 = db.addUser("Bob Johnson", "[email protected]", 30);
        int userId3 = db.addUser("Charlie Brown", "[email protected]", 35);
        
        // Get all users
        auto users = db.getAllUsers();
        std::cout << "\n=== All Users ===" << std::endl;
        for (const auto& user : users) {
            std::cout << "ID: " << user.id << ", Name: " << user.name 
                      << ", Email: " << user.email << ", Age: " << user.age << std::endl;
        }
        
        // Get specific user
        auto user = db.getUserById(userId1);
        if (user) {
            std::cout << "\n=== User by ID ===" << std::endl;
            std::cout << "Found user: " << user->name << std::endl;
        }
        
        // Update user
        if (db.updateUser(userId1, "Alice Johnson", "[email protected]", 26)) {
            std::cout << "\n=== User Updated ===" << std::endl;
            std::cout << "User updated successfully" << std::endl;
        }
        
        // Search
        auto searchResults = db.searchUsers("Alice");
        std::cout << "\n=== Search Results ===" << std::endl;
        for (const auto& user : searchResults) {
            std::cout << "Found: " << user.name << " (" << user.email << ")" << std::endl;
        }
        
        // Statistics
        auto stats = db.getUserStatistics();
        std::cout << "\n=== Statistics ===" << std::endl;
        std::cout << "Total users: " << stats.total_users << std::endl;
        std::cout << "Average age: " << stats.average_age << std::endl;
        std::cout << "Age range: " << stats.min_age << " - " << stats.max_age << std::endl;
        
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
}

Transaction Processing

#include <soci/soci.h>
#include <soci/postgresql/soci-postgresql.h>

class TransactionManager {
private:
    std::unique_ptr<session> sql_;
    
public:
    explicit TransactionManager(const std::string& connection_string) 
        : sql_(std::make_unique<session>(postgresql, connection_string)) {}
    
    // User migration with transaction
    bool transferUserPosts(int fromUserId, int toUserId) {
        try {
            transaction tr(*sql_);
            
            // User existence check
            int fromUserCount, toUserCount;
            *sql_ << "SELECT COUNT(*) FROM users WHERE id = :id", 
                use(fromUserId), into(fromUserCount);
            *sql_ << "SELECT COUNT(*) FROM users WHERE id = :id", 
                use(toUserId), into(toUserCount);
                
            if (fromUserCount == 0 || toUserCount == 0) {
                std::cerr << "One or both users not found" << std::endl;
                return false;
            }
            
            // Transfer posts
            statement st = (sql_->prepare << 
                "UPDATE posts SET user_id = :to_user WHERE user_id = :from_user",
                use(toUserId), use(fromUserId));
            st.execute();
            
            int affectedRows = st.get_affected_rows();
            std::cout << "Transferred " << affectedRows << " posts" << std::endl;
            
            // Update statistics (example)
            *sql_ << "UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE id IN (:id1, :id2)",
                use(fromUserId), use(toUserId);
            
            tr.commit();
            return true;
            
        } catch (const soci_error& e) {
            std::cerr << "Transaction failed: " << e.what() << std::endl;
            // Transaction automatically rolls back
            return false;
        }
    }
    
    // Batch user creation
    std::vector<int> batchCreateUsers(const std::vector<std::tuple<std::string, std::string, int>>& usersData) {
        std::vector<int> userIds;
        
        try {
            transaction tr(*sql_);
            
            for (const auto& [name, email, age] : usersData) {
                int userId;
                *sql_ << "INSERT INTO users (name, email, age) VALUES (:name, :email, :age) RETURNING id",
                    use(name), use(email), use(age), into(userId);
                userIds.push_back(userId);
            }
            
            tr.commit();
            std::cout << "Batch created " << userIds.size() << " users" << std::endl;
            
        } catch (const soci_error& e) {
            std::cerr << "Batch creation failed: " << e.what() << std::endl;
            userIds.clear();
        }
        
        return userIds;
    }
    
    // Conditional deletion
    int deleteInactiveUsers(int daysSinceLastActivity) {
        try {
            transaction tr(*sql_);
            
            // Identify inactive users
            statement st = (sql_->prepare <<
                "DELETE FROM users WHERE id IN ("
                "SELECT u.id FROM users u "
                "LEFT JOIN posts p ON u.id = p.user_id "
                "WHERE u.created_at < CURRENT_DATE - INTERVAL ':days days' "
                "AND (p.created_at IS NULL OR p.created_at < CURRENT_DATE - INTERVAL ':days days')"
                ")", use(daysSinceLastActivity));
            
            st.execute();
            int deletedCount = st.get_affected_rows();
            
            tr.commit();
            std::cout << "Deleted " << deletedCount << " inactive users" << std::endl;
            return deletedCount;
            
        } catch (const soci_error& e) {
            std::cerr << "Failed to delete inactive users: " << e.what() << std::endl;
            return 0;
        }
    }
    
    // Complex statistics query
    struct UserEngagementStats {
        std::string user_name;
        int post_count;
        std::string last_post_date;
        double avg_posts_per_month;
    };
    
    std::vector<UserEngagementStats> getUserEngagementStats() {
        try {
            std::vector<UserEngagementStats> stats;
            
            rowset<row> rs = (sql_->prepare <<
                "SELECT "
                "    u.name, "
                "    COUNT(p.id) as post_count, "
                "    MAX(p.created_at)::date as last_post_date, "
                "    COUNT(p.id) / GREATEST(EXTRACT(epoch FROM (CURRENT_DATE - u.created_at::date)) / (30 * 24 * 3600), 1) as avg_posts_per_month "
                "FROM users u "
                "LEFT JOIN posts p ON u.id = p.user_id "
                "GROUP BY u.id, u.name, u.created_at "
                "HAVING COUNT(p.id) > 0 "
                "ORDER BY post_count DESC");
            
            for (const auto& row : rs) {
                UserEngagementStats stat;
                stat.user_name = row.get<std::string>(0);
                stat.post_count = row.get<int>(1);
                
                // NULL check
                if (row.get_indicator(2) != i_null) {
                    stat.last_post_date = row.get<std::string>(2);
                } else {
                    stat.last_post_date = "Never";
                }
                
                stat.avg_posts_per_month = row.get<double>(3);
                stats.push_back(stat);
            }
            
            return stats;
            
        } catch (const soci_error& e) {
            std::cerr << "Failed to get engagement stats: " << e.what() << std::endl;
            throw;
        }
    }
};

// Usage example
void demonstrateTransactions() {
    try {
        TransactionManager tm("postgresql://user:password@localhost/testdb");
        
        // Batch user creation
        std::vector<std::tuple<std::string, std::string, int>> newUsers = {
            {"David Wilson", "[email protected]", 28},
            {"Emma Davis", "[email protected]", 24},
            {"Frank Miller", "[email protected]", 32}
        };
        
        auto userIds = tm.batchCreateUsers(newUsers);
        
        if (!userIds.empty()) {
            std::cout << "Created users with IDs: ";
            for (int id : userIds) {
                std::cout << id << " ";
            }
            std::cout << std::endl;
        }
        
        // Engagement statistics
        auto engagementStats = tm.getUserEngagementStats();
        std::cout << "\n=== User Engagement Stats ===" << std::endl;
        for (const auto& stat : engagementStats) {
            std::cout << "User: " << stat.user_name 
                      << ", Posts: " << stat.post_count
                      << ", Last Post: " << stat.last_post_date
                      << ", Avg/Month: " << stat.avg_posts_per_month << std::endl;
        }
        
    } catch (const std::exception& e) {
        std::cerr << "Transaction demo error: " << e.what() << std::endl;
    }
}

Prepared Statements and Batch Processing

#include <soci/soci.h>
#include <chrono>
#include <random>

class PerformanceManager {
private:
    std::unique_ptr<session> sql_;
    
public:
    explicit PerformanceManager(const std::string& connection_string) 
        : sql_(std::make_unique<session>(postgresql, connection_string)) {}
    
    // Fast bulk insert using prepared statements
    void fastBulkInsert(int userCount) {
        auto start = std::chrono::high_resolution_clock::now();
        
        try {
            transaction tr(*sql_);
            
            // Create prepared statement
            statement st = (sql_->prepare <<
                "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)");
            
            std::string name, email;
            int age;
            
            // Bind variables
            st.exchange(use(name, "name"));
            st.exchange(use(email, "email"));
            st.exchange(use(age, "age"));
            
            // Random data generator
            std::random_device rd;
            std::mt19937 gen(rd());
            std::uniform_int_distribution<> ageDist(18, 80);
            
            // Batch insertion
            for (int i = 0; i < userCount; ++i) {
                name = "User" + std::to_string(i);
                email = "user" + std::to_string(i) + "@example.com";
                age = ageDist(gen);
                
                st.execute();
            }
            
            tr.commit();
            
            auto end = std::chrono::high_resolution_clock::now();
            auto duration = std::chrono::duration_cast<std::chrono::milliseconds>(end - start);
            
            std::cout << "Inserted " << userCount << " users in " 
                      << duration.count() << "ms" << std::endl;
                      
        } catch (const soci_error& e) {
            std::cerr << "Bulk insert failed: " << e.what() << std::endl;
        }
    }
    
    // Batch update
    void batchUpdateUsers(const std::vector<std::tuple<int, std::string, int>>& updates) {
        try {
            transaction tr(*sql_);
            
            statement st = (sql_->prepare <<
                "UPDATE users SET name = :name, age = :age WHERE id = :id");
            
            int id, age;
            std::string name;
            
            st.exchange(use(id, "id"));
            st.exchange(use(name, "name"));
            st.exchange(use(age, "age"));
            
            for (const auto& [userId, userName, userAge] : updates) {
                id = userId;
                name = userName;
                age = userAge;
                
                st.execute();
            }
            
            tr.commit();
            std::cout << "Batch updated " << updates.size() << " users" << std::endl;
            
        } catch (const soci_error& e) {
            std::cerr << "Batch update failed: " << e.what() << std::endl;
        }
    }
    
    // Efficient large dataset processing
    template<typename Callback>
    void processLargeDataset(const std::string& query, Callback callback) {
        try {
            rowset<row> rs = (sql_->prepare << query);
            
            int processedCount = 0;
            const int batchSize = 1000;
            
            for (const auto& row : rs) {
                callback(row);
                processedCount++;
                
                // Progress indication
                if (processedCount % batchSize == 0) {
                    std::cout << "Processed " << processedCount << " rows..." << std::endl;
                }
            }
            
            std::cout << "Total processed: " << processedCount << " rows" << std::endl;
            
        } catch (const soci_error& e) {
            std::cerr << "Large dataset processing failed: " << e.what() << std::endl;
        }
    }
    
    // Index analysis
    void analyzeQueryPerformance(const std::string& query) {
        try {
            auto start = std::chrono::high_resolution_clock::now();
            
            // Execute EXPLAIN ANALYZE
            rowset<row> rs = (sql_->prepare << ("EXPLAIN ANALYZE " + query));
            
            auto end = std::chrono::high_resolution_clock::now();
            auto duration = std::chrono::duration_cast<std::chrono::microseconds>(end - start);
            
            std::cout << "\n=== Query Performance Analysis ===" << std::endl;
            std::cout << "Query: " << query << std::endl;
            std::cout << "Execution time: " << duration.count() << " microseconds" << std::endl;
            std::cout << "\nExecution plan:" << std::endl;
            
            for (const auto& row : rs) {
                std::cout << row.get<std::string>(0) << std::endl;
            }
            
        } catch (const soci_error& e) {
            std::cerr << "Query analysis failed: " << e.what() << std::endl;
        }
    }
    
    // Connection pool usage example (pseudo implementation)
    class ConnectionPool {
    private:
        std::vector<std::unique_ptr<session>> connections_;
        std::string connection_string_;
        size_t pool_size_;
        size_t current_index_;
        
    public:
        ConnectionPool(const std::string& conn_str, size_t size) 
            : connection_string_(conn_str), pool_size_(size), current_index_(0) {
            
            for (size_t i = 0; i < pool_size_; ++i) {
                connections_.push_back(
                    std::make_unique<session>(postgresql, connection_string_)
                );
            }
        }
        
        session* getConnection() {
            auto* conn = connections_[current_index_].get();
            current_index_ = (current_index_ + 1) % pool_size_;
            return conn;
        }
        
        void executeQuery(const std::string& query) {
            try {
                auto* sql = getConnection();
                *sql << query;
                std::cout << "Executed query using pooled connection" << std::endl;
            } catch (const soci_error& e) {
                std::cerr << "Pooled query failed: " << e.what() << std::endl;
            }
        }
    };
};

// Usage example
void demonstratePerformance() {
    try {
        PerformanceManager pm("postgresql://user:password@localhost/testdb");
        
        // Fast bulk insert
        std::cout << "=== Fast Bulk Insert Test ===" << std::endl;
        pm.fastBulkInsert(1000);
        
        // Batch update
        std::vector<std::tuple<int, std::string, int>> updates = {
            {1, "Updated User 1", 30},
            {2, "Updated User 2", 35},
            {3, "Updated User 3", 40}
        };
        pm.batchUpdateUsers(updates);
        
        // Large dataset processing
        std::cout << "\n=== Large Dataset Processing ===" << std::endl;
        pm.processLargeDataset(
            "SELECT id, name, email FROM users ORDER BY id",
            [](const row& r) {
                // Processing for each row
                if (r.get<int>(0) % 100 == 0) {
                    std::cout << "Processing user ID: " << r.get<int>(0) << std::endl;
                }
            }
        );
        
        // Query performance analysis
        pm.analyzeQueryPerformance("SELECT * FROM users WHERE age > 25 ORDER BY name");
        
    } catch (const std::exception& e) {
        std::cerr << "Performance demo error: " << e.what() << std::endl;
    }
}

Error Handling

#include <soci/soci.h>
#include <stdexcept>
#include <optional>

// Custom exception classes
class DatabaseException : public std::runtime_error {
public:
    explicit DatabaseException(const std::string& message) 
        : std::runtime_error("Database Error: " + message) {}
};

class UserNotFoundException : public DatabaseException {
public:
    explicit UserNotFoundException(int userId) 
        : DatabaseException("User not found with ID: " + std::to_string(userId)) {}
};

class DuplicateEmailException : public DatabaseException {
public:
    explicit DuplicateEmailException(const std::string& email) 
        : DatabaseException("Email already exists: " + email) {}
};

class SafeUserDatabase {
private:
    std::unique_ptr<session> sql_;
    
    // Connection validation
    bool isConnectionValid() {
        try {
            int result;
            *sql_ << "SELECT 1", into(result);
            return result == 1;
        } catch (...) {
            return false;
        }
    }
    
    // Reconnection attempt
    void reconnect(const std::string& connection_string) {
        try {
            sql_ = std::make_unique<session>(postgresql, connection_string);
            std::cout << "Database reconnected successfully" << std::endl;
        } catch (const soci_error& e) {
            throw DatabaseException("Reconnection failed: " + std::string(e.what()));
        }
    }
    
public:
    explicit SafeUserDatabase(const std::string& connection_string) {
        try {
            sql_ = std::make_unique<session>(postgresql, connection_string);
        } catch (const soci_error& e) {
            throw DatabaseException("Initial connection failed: " + std::string(e.what()));
        }
    }
    
    // Safe user creation
    std::optional<int> createUserSafely(const std::string& name, const std::string& email, int age) {
        // Validation
        if (name.empty()) {
            throw std::invalid_argument("Name cannot be empty");
        }
        if (email.find('@') == std::string::npos) {
            throw std::invalid_argument("Invalid email format");
        }
        if (age < 0 || age > 150) {
            throw std::invalid_argument("Invalid age range");
        }
        
        try {
            // Duplicate check
            int existingCount;
            *sql_ << "SELECT COUNT(*) FROM users WHERE email = :email", 
                use(email), into(existingCount);
                
            if (existingCount > 0) {
                throw DuplicateEmailException(email);
            }
            
            // Create user
            int userId;
            *sql_ << "INSERT INTO users (name, email, age) VALUES (:name, :email, :age) RETURNING id",
                use(name), use(email), use(age), into(userId);
                
            return userId;
            
        } catch (const soci_error& e) {
            throw DatabaseException("User creation failed: " + std::string(e.what()));
        }
    }
    
    // Safe user retrieval
    std::optional<User> getUserSafely(int userId) {
        try {
            User user;
            indicator ind;
            
            *sql_ << "SELECT id, name, email, age, created_at FROM users WHERE id = :id",
                into(user, ind), use(userId);
                
            if (ind == i_ok) {
                return user;
            } else {
                return std::nullopt;
            }
            
        } catch (const soci_error& e) {
            throw DatabaseException("User retrieval failed: " + std::string(e.what()));
        }
    }
    
    // Safe user update
    bool updateUserSafely(int userId, const std::string& name, const std::string& email, int age) {
        // Validation
        if (name.empty()) {
            throw std::invalid_argument("Name cannot be empty");
        }
        if (email.find('@') == std::string::npos) {
            throw std::invalid_argument("Invalid email format");
        }
        
        try {
            transaction tr(*sql_);
            
            // User existence check
            int userExists;
            *sql_ << "SELECT COUNT(*) FROM users WHERE id = :id", 
                use(userId), into(userExists);
                
            if (userExists == 0) {
                throw UserNotFoundException(userId);
            }
            
            // Email duplicate check (excluding self)
            int duplicateCount;
            *sql_ << "SELECT COUNT(*) FROM users WHERE email = :email AND id != :id", 
                use(email), use(userId), into(duplicateCount);
                
            if (duplicateCount > 0) {
                throw DuplicateEmailException(email);
            }
            
            // Execute update
            statement st = (sql_->prepare <<
                "UPDATE users SET name = :name, email = :email, age = :age WHERE id = :id",
                use(name), use(email), use(age), use(userId));
            
            st.execute();
            bool success = st.get_affected_rows() > 0;
            
            if (success) {
                tr.commit();
            }
            
            return success;
            
        } catch (const soci_error& e) {
            throw DatabaseException("User update failed: " + std::string(e.what()));
        }
    }
    
    // Safe user deletion
    bool deleteUserSafely(int userId) {
        try {
            transaction tr(*sql_);
            
            // User existence check
            int userExists;
            *sql_ << "SELECT COUNT(*) FROM users WHERE id = :id", 
                use(userId), into(userExists);
                
            if (userExists == 0) {
                throw UserNotFoundException(userId);
            }
            
            // Related data check
            int postCount;
            *sql_ << "SELECT COUNT(*) FROM posts WHERE user_id = :id", 
                use(userId), into(postCount);
                
            if (postCount > 0) {
                std::cout << "User has " << postCount << " posts. Deleting related data..." << std::endl;
                *sql_ << "DELETE FROM posts WHERE user_id = :id", use(userId);
            }
            
            // Delete user
            statement st = (sql_->prepare << "DELETE FROM users WHERE id = :id", use(userId));
            st.execute();
            
            bool success = st.get_affected_rows() > 0;
            
            if (success) {
                tr.commit();
            }
            
            return success;
            
        } catch (const soci_error& e) {
            throw DatabaseException("User deletion failed: " + std::string(e.what()));
        }
    }
    
    // Query execution with retry functionality
    template<typename Func>
    auto executeWithRetry(Func&& func, const std::string& connection_string, int maxRetries = 3) -> decltype(func()) {
        int attempts = 0;
        
        while (attempts < maxRetries) {
            try {
                if (!isConnectionValid() && attempts > 0) {
                    reconnect(connection_string);
                }
                
                return func();
                
            } catch (const soci_error& e) {
                attempts++;
                std::cerr << "Attempt " << attempts << " failed: " << e.what() << std::endl;
                
                if (attempts >= maxRetries) {
                    throw DatabaseException("Max retry attempts reached: " + std::string(e.what()));
                }
                
                // Wait before retry
                std::this_thread::sleep_for(std::chrono::milliseconds(100 * attempts));
            }
        }
        
        throw DatabaseException("Unexpected error in executeWithRetry");
    }
    
    // Health check
    struct HealthCheckResult {
        bool connection_ok;
        bool tables_exist;
        int user_count;
        std::string last_error;
    };
    
    HealthCheckResult performHealthCheck() {
        HealthCheckResult result{};
        
        try {
            // Connection check
            result.connection_ok = isConnectionValid();
            
            if (result.connection_ok) {
                // Table existence check
                int tableCount;
                *sql_ << "SELECT COUNT(*) FROM information_schema.tables WHERE table_name IN ('users', 'posts')",
                    into(tableCount);
                result.tables_exist = (tableCount == 2);
                
                // Get user count
                *sql_ << "SELECT COUNT(*) FROM users", into(result.user_count);
            }
            
        } catch (const soci_error& e) {
            result.last_error = e.what();
            result.connection_ok = false;
        }
        
        return result;
    }
};

// Error handling usage example
void demonstrateErrorHandling() {
    try {
        SafeUserDatabase db("postgresql://user:password@localhost/testdb");
        
        // Health check
        auto health = db.performHealthCheck();
        std::cout << "=== Health Check ===" << std::endl;
        std::cout << "Connection OK: " << (health.connection_ok ? "Yes" : "No") << std::endl;
        std::cout << "Tables exist: " << (health.tables_exist ? "Yes" : "No") << std::endl;
        std::cout << "User count: " << health.user_count << std::endl;
        
        if (!health.last_error.empty()) {
            std::cout << "Last error: " << health.last_error << std::endl;
        }
        
        // Safe user operations
        try {
            auto userId = db.createUserSafely("Test User", "[email protected]", 25);
            if (userId) {
                std::cout << "Created user with ID: " << *userId << std::endl;
                
                // Update test
                if (db.updateUserSafely(*userId, "Updated User", "[email protected]", 26)) {
                    std::cout << "User updated successfully" << std::endl;
                }
                
                // Retrieval test
                auto user = db.getUserSafely(*userId);
                if (user) {
                    std::cout << "Retrieved user: " << user->name << std::endl;
                }
            }
            
        } catch (const DuplicateEmailException& e) {
            std::cerr << "Duplicate email error: " << e.what() << std::endl;
        } catch (const UserNotFoundException& e) {
            std::cerr << "User not found error: " << e.what() << std::endl;
        }
        
        // Test retrieving non-existent user
        auto nonExistentUser = db.getUserSafely(99999);
        if (!nonExistentUser) {
            std::cout << "Non-existent user correctly returned nullopt" << std::endl;
        }
        
    } catch (const DatabaseException& e) {
        std::cerr << "Database error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "General error: " << e.what() << std::endl;
    }
}

int main() {
    std::cout << "=== SOCI Database Access Demo ===" << std::endl;
    
    demonstrateBasicOperations();
    demonstrateTransactions();
    demonstratePerformance();
    demonstrateErrorHandling();
    
    return 0;
}