SOCI — SQL ORM

SOCI 是 C++ 的数据库访问库，提供统一 API 支持 PostgreSQL/MySQL/SQLite/Oracle，语法简洁，支持 ORM 风格映射。

安装

# vcpkg
vcpkg install soci[sqlite3,postgresql,mysql]

# CMake
find_package(SOCI REQUIRED)
target_link_libraries(myapp PRIVATE SOCI::soci_core SOCI::soci_sqlite3)

基本用法

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

// SQLite 连接
soci::session sql(soci::sqlite3, "mydb.db");

// PostgreSQL 连接
soci::session sql2(soci::postgresql,
    "host=localhost dbname=mydb user=postgres password=secret");

// DDL
sql << "CREATE TABLE IF NOT EXISTS users ("
       "id INTEGER PRIMARY KEY AUTOINCREMENT,"
       "name TEXT NOT NULL,"
       "age INTEGER)";

// 插入（流式语法）
std::string name = "Alice";
int age = 30;
sql << "INSERT INTO users (name, age) VALUES (:name, :age)",
       soci::use(name), soci::use(age);

// 获取最后插入 ID
long long id;
sql << "SELECT last_insert_rowid()", soci::into(id);

查询与绑定

// 单行查询
std::string name;
int age;
sql << "SELECT name, age FROM users WHERE id = :id",
       soci::into(name), soci::into(age), soci::use(1);
std::cout << name << " " << age << "\n";

// 多行查询（rowset）
soci::rowset<soci::row> rs = (sql.prepare <<
    "SELECT id, name, age FROM users WHERE age > :min_age",
    soci::use(18));

for (const auto& row : rs) {
    int id = row.get<int>("id");
    std::string name = row.get<std::string>("name");
    int age = row.get<int>("age");
    std::cout << id << " " << name << " " << age << "\n";
}

自定义类型映射

struct User {
    int id;
    std::string name;
    int age;
    std::string email;
};

// 特化 type_conversion
namespace soci {
template<>
struct type_conversion<User> {
    using base_type = values;

    static void from_base(const values& v, indicator ind, User& u) {
        u.id    = v.get<int>("id");
        u.name  = v.get<std::string>("name");
        u.age   = v.get<int>("age");
        u.email = v.get<std::string>("email", "");  // 默认值
    }

    static void to_base(const User& u, values& v, indicator& ind) {
        v.set("name",  u.name);
        v.set("age",   u.age);
        v.set("email", u.email);
        ind = i_ok;
    }
};
}

// 使用
User user;
sql << "SELECT id, name, age, email FROM users WHERE id = :id",
       soci::into(user), soci::use(1);
std::cout << user.name << "\n";

// 插入
User new_user{0, "Bob", 25, "bob@example.com"};
sql << "INSERT INTO users (name, age, email) VALUES (:name, :age, :email)",
       soci::use(new_user);

事务

{
    soci::transaction txn(sql);
    sql << "UPDATE accounts SET balance = balance - 100 WHERE id = 1";
    sql << "UPDATE accounts SET balance = balance + 100 WHERE id = 2";
    txn.commit();
}
// 析构时自动回滚（若未 commit）

关键认知

SOCI 的最大优势是统一 API 支持多种数据库，切换数据库只需改连接字符串。type_conversion 特化实现 ORM 风格的对象映射。语法简洁，适合中小型项目。大型项目考虑专用 ORM 或直接用数据库原生驱动。