SOCI

Language: CPP

Database

SOCI (The C++ Database Access Library) was started in 2004 by Maciej Sobczak to provide a modern alternative to traditional C database APIs like ODBC and libpq. It focuses on safety, readability, and minimal boilerplate while avoiding the complexity of full ORM frameworks. It supports multiple backends including PostgreSQL, MySQL, SQLite, Oracle, and ODBC.

SOCI is a C++ database access library that provides a clean, type-safe, and object-oriented abstraction over SQL databases. It acts like a lightweight ORM but retains full SQL control, offering both simplicity and flexibility.

Installation

linux: sudo apt install libsoci-dev

mac: brew install soci

windows: vcpkg install soci or build from source with CMake

Usage

SOCI allows developers to write queries in natural SQL while providing type-safe bindings to C++ variables. It eliminates manual binding and fetching of data, making database interaction safer and cleaner.

Connecting to a database

#include <soci/soci.h>
#include <iostream>

int main() {
    try {
        soci::session sql(soci::postgresql, "dbname=mydb user=myuser password=mypass");
        std::cout << "Connected successfully!" << std::endl;
    } catch (std::exception const &e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
    return 0;
}

Opens a connection to a PostgreSQL database using SOCI.

Executing queries

int count;
sql << "SELECT COUNT(*) FROM users", soci::into(count);
std::cout << "User count: " << count << std::endl;

Executes a query and stores the result directly into a C++ variable.

Fetching multiple rows

soci::rowset<soci::row> rs = (sql.prepare << "SELECT id, name FROM users");
for (auto const &r : rs) {
    int id = r.get<int>(0);
    std::string name = r.get<std::string>(1);
    std::cout << id << ": " << name << std::endl;
}

Iterates over query results using SOCI’s rowset abstraction.

Binding parameters

std::string name = "John";
int age;
sql << "SELECT age FROM users WHERE name = :name", soci::into(age), soci::use(name);

Binds C++ variables safely to SQL query parameters.

Transactions

{
    soci::transaction tr(sql);
    sql << "INSERT INTO logs(message) VALUES('test')";
    tr.commit();
}

Executes queries inside a transaction with explicit commit or rollback.

Error Handling

soci_error: Cannot establish connection: Check database credentials, network connectivity, and that the backend driver is installed.

Row not found exception: Use `sql.get_single_result()` cautiously or check for empty results before accessing.

Type conversion error: Ensure correct C++ types are mapped to SQL columns (e.g., `int` to INTEGER, `std::string` to TEXT).

Best Practices

Use parameter binding (`soci::use`) to avoid SQL injection.

Wrap multiple statements in transactions for safety and efficiency.

Catch exceptions from SOCI queries to handle runtime errors gracefully.

Use `rowset` for large result sets to stream data instead of loading all at once.

Leverage backend-specific options (e.g., PostgreSQL vs MySQL) for performance tuning.

Official Docs Github