Database Access in C++

#include <sqlite3.h>

sqlite3* db;
sqlite3_open("app.db", &db);

// Create table
sqlite3_exec(db,
    "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)",
    nullptr, nullptr, nullptr);

// Prepared statement — parameterized query
sqlite3_stmt* stmt;
sqlite3_prepare_v2(db,
    "INSERT INTO users (name) VALUES (?)", -1, &stmt, nullptr);

sqlite3_bind_text(stmt, 1, "Alice", -1, SQLITE_STATIC);
sqlite3_step(stmt);
sqlite3_finalize(stmt);

// Query
sqlite3_prepare_v2(db, "SELECT id, name FROM users", -1, &stmt, nullptr);
while (sqlite3_step(stmt) == SQLITE_ROW) {
    int id = sqlite3_column_int(stmt, 0);
    const char* name = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
    std::println("{}: {}", id, name);
}
sqlite3_finalize(stmt);
sqlite3_close(db);

#include <SQLiteCpp/SQLiteCpp.h>

// RAII — no manual open/close
SQLite::Database db{"app.db", SQLite::OPEN_READWRITE | SQLite::OPEN_CREATE};

db.exec("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)");

// Prepared statement
SQLite::Statement insert{db, "INSERT INTO users (name) VALUES (?)"};
insert.bind(1, "Alice");
insert.exec();

// Query with structured binding
SQLite::Statement query{db, "SELECT id, name FROM users WHERE id > ?"};
query.bind(1, 0);
while (query.executeStep()) {
    int id         = query.getColumn(0).getInt();
    std::string name = query.getColumn(1).getString();
    std::println("{}: {}", id, name);
}

// Transaction
SQLite::Transaction txn{db};
for (const auto& name : names) {
    SQLite::Statement ins{db, "INSERT INTO users (name) VALUES (?)"};
    ins.bind(1, name);
    ins.exec();
}
txn.commit();

#include <pqxx/pqxx>

pqxx::connection conn{
    "host=localhost dbname=mydb user=postgres password=secret"
};

// Non-transactional query (read-only, auto-commit)
pqxx::nontransaction ntx{conn};
pqxx::result r = ntx.exec("SELECT version()");
std::println("{}", r[0][0].c_str());

// Transaction
pqxx::work txn{conn};

// Prepared statement — register once, call many times
conn.prepare("insert_user",
    "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id");

auto result = txn.exec_prepared("insert_user", "Alice", "alice@example.com");
int new_id = result[0][0].as<int>();

// Parameterized query (one-shot, no need to prepare)
auto rows = txn.exec_params(
    "SELECT id, name FROM users WHERE age > $1 AND active = $2",
    18, true);

for (const auto& row : rows) {
    auto id   = row["id"].as<int>();
    auto name = row["name"].as<std::string>();
    std::println("{}: {}", id, name);
}

txn.commit();

#include <pqxx/pqxx>
#include <queue>
#include <mutex>

class ConnectionPool {
    std::queue<std::unique_ptr<pqxx::connection>> pool_;
    std::mutex mtx_;
    std::string conn_str_;
    size_t max_size_;

public:
    ConnectionPool(std::string cs, size_t size)
        : conn_str_{std::move(cs)}, max_size_{size}
    {
        for (size_t i = 0; i < size; ++i)
            pool_.push(std::make_unique<pqxx::connection>(conn_str_));
    }

    // RAII connection handle
    class Handle {
        ConnectionPool* pool_;
        std::unique_ptr<pqxx::connection> conn_;
    public:
        Handle(ConnectionPool* p, std::unique_ptr<pqxx::connection> c)
            : pool_{p}, conn_{std::move(c)} {}
        ~Handle() { pool_->release(std::move(conn_)); }
        pqxx::connection& get() { return *conn_; }
        pqxx::connection* operator->() { return conn_.get(); }
    };

    Handle acquire() {
        std::lock_guard lock{mtx_};
        if (pool_.empty())
            return {this, std::make_unique<pqxx::connection>(conn_str_)};
        auto conn = std::move(pool_.front());
        pool_.pop();
        return {this, std::move(conn)};
    }

    void release(std::unique_ptr<pqxx::connection> conn) {
        std::lock_guard lock{mtx_};
        if (pool_.size() < max_size_)
            pool_.push(std::move(conn));
    }
};

// Usage
ConnectionPool pool{"dbname=mydb", 10};
{
    auto conn = pool.acquire();
    pqxx::work txn{conn.get()};
    txn.exec("UPDATE counters SET val = val + 1");
    txn.commit();
}  // connection returned to pool

struct User {
    int id = 0;
    std::string name;
    std::string email;
    bool active = true;
};

class UserRepository {
    pqxx::connection& conn_;

public:
    explicit UserRepository(pqxx::connection& c) : conn_{c} {}

    std::optional<User> find_by_id(int id) {
        pqxx::nontransaction ntx{conn_};
        auto r = ntx.exec_params(
            "SELECT id, name, email, active FROM users WHERE id = $1", id);
        if (r.empty()) return std::nullopt;
        return User{r[0]["id"].as<int>(),
                    r[0]["name"].as<std::string>(),
                    r[0]["email"].as<std::string>(),
                    r[0]["active"].as<bool>()};
    }

    User save(User user) {
        pqxx::work txn{conn_};
        if (user.id == 0) {
            auto r = txn.exec_params(
                "INSERT INTO users (name, email, active) "
                "VALUES ($1, $2, $3) RETURNING id",
                user.name, user.email, user.active);
            user.id = r[0][0].as<int>();
        } else {
            txn.exec_params(
                "UPDATE users SET name=$1, email=$2, active=$3 WHERE id=$4",
                user.name, user.email, user.active, user.id);
        }
        txn.commit();
        return user;
    }

    std::vector<User> find_all_active() {
        pqxx::nontransaction ntx{conn_};
        auto r = ntx.exec("SELECT id, name, email, active FROM users WHERE active");
        std::vector<User> users;
        for (const auto& row : r)
            users.push_back({row[0].as<int>(), row[1].as<std::string>(),
                             row[2].as<std::string>(), row[3].as<bool>()});
        return users;
    }
};

try {
    pqxx::work txn{conn};
    txn.exec("INSERT INTO users VALUES (1, 'Alice')");
    txn.commit();
} catch (const pqxx::unique_violation& e) {
    std::println("duplicate key: {}", e.what());
} catch (const pqxx::foreign_key_violation& e) {
    std::println("FK violation: {}", e.what());
} catch (const pqxx::sql_error& e) {
    std::println("SQL error: {}, query: {}", e.what(), e.query());
} catch (const pqxx::broken_connection& e) {
    std::println("connection lost: {}", e.what());
    // reconnect and retry
}

# SQLite
find_package(SQLite3 REQUIRED)
target_link_libraries(myapp SQLite::SQLite3)

# SQLiteCpp (via FetchContent)
FetchContent_Declare(SQLiteCpp
    GIT_REPOSITORY https://github.com/SRombauts/SQLiteCpp.git
    GIT_TAG 3.3.1)
FetchContent_MakeAvailable(SQLiteCpp)
target_link_libraries(myapp SQLiteCpp)

# pqxx (PostgreSQL)
find_package(PostgreSQL REQUIRED)
find_package(pqxx REQUIRED)
target_link_libraries(myapp pqxx::pqxx)