Arctic SQL

SQL mapping library for C++ offering a simple, light, fast, and type-safe layer between your POCOs and your database.

The library suggests an interface one step above micro-ORMs thus keeping a thin abstraction layer between your code and the database. Your queries are checked by the C++ compiler and allow for easy refactoring. However, it’s not as heavy as Hibernate or Entity Framework. I would say that the most close analogue is Linkq2db in C#.

Dependencies

The foundation for Arctic SQL is a wonderfull cppparser library. This allowes us to keep consice POCO classes and translate them transparently so that they can be used in complex queries.

Boost is also used to generate uuids and work with files.

SQLite3 and Postgres packages are required as well. Currently no other database engines are supported.

Example

Connecting to database

SQLite

#include <arctic/sql/sql.hpp>
#include <arctic/sql/sqlite/sqlite.hpp>
auto sqlite_connptr = sqlite::SQLiteConnector::Connect(":memory:", SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE);
auto connptr = std::dynamic_pointer_cast<IConnector>(sqlite_connptr);

:memory: - can be replaced with file path.

Postgres

#include <arctic/sql/sql.hpp>
#include <arctic/sql/postgres/postgres.hpp>
auto postgres_connptr = postgres::PostgresConnector::Connect("postgresql://postgres:<password>@localhost/<database>");
auto connptr = std::dynamic_pointer_cast<IConnector>(postgres_connptr);

Define POCO class

class [[Table]] Department {
public:
    [[PK]] [[NotNull]]
    Int CompanyID;

    [[PK]] [[AutoIncrement]] [[NotNull]]
    Int DepartmentID;

    Text Name;
};

struct [[Table]] Person {
    [[PK]] [[NotNull]]
    Int CompanyID;

    [[PK]] [[AutoIncrement]] [[NotNull]] 
    Int PersonID;

    [[DBName("DepartmentID")]]
    Int DeptID;

    Text LastName;
    Text FirstName;
    Text Phone;
    Text Email;

    Long ExternalID;
    Double Balance;
    Bool Fired;
    Date Birthdate;
    DateTime LastLoginTime;
    TimeDuration WorkDayDuration;
};

Selecting data

Department dept;
Person person;
auto query = 
    Select()
    .From(person)
        .LeftJoin(dept).On(dept->DepartmentID == person->DeptID)
    .Where(!person->Fired)
    .Offset(20).FetchFirst(10);
auto data = connptr->Exec(query);
while (auto rec = data->FetchRecord()) {
    std::cout << "#" << person.PersonID
        << " " << person.FirstName
        << " " << person.LastName
        << " <" << person.Email << ">"
        << std::endl;
}

Certain columns can be selected using expressions, aggregations and even subqueries.

auto query = 
    Select(person->PersonID
        , Case()
            .When(person->LastName == "Иванов").Then(Literal("Иван"))
            .When(person->LastName == "Petrov").Then(person->FirstName)
            .Else(person->Email)
            .As("CaseExpr")
        , person->Balance
        , (person->Balance == Select(person->Balance.Max()).From(person))->As("MaxBalance"))
    .From(person);

While there is still no proper documentation please find the examples in tests: sql-expr-test.cpp, postgres-test.cpp and others.

Changing data

Insert

Person p1;
p1.CompanyID = 1;
p1.LastName = "Petrov";
p1.FirstName = "Petr";
p1.Phone = "+7 (916) 222-22-22";
p1.Email = "petr.petrov@example.com";

connptr->Exec(Insert(p1));

Update

Person person;
auto selectIvanov = Select().From(person).Where(person->PersonID == 2);
auto data = connptr->Exec(selectIvanov);
auto rec = data->FetchRecord();
Person ivanov = rec[person];

ivanov.Phone = "+7 (985) 111-11-11";
auto updateExpr = Update(ivanov);
connptr->Exec(updateExpr);

Delete

Person person;
auto query = Select().From(person).Where(person->PersonID == 2);
auto data = connptr->Exec(query);
auto rec = data->FetchRecord();

connptr->Exec(Delete(person));

Building

Build: Windows (MSVC)

Make and install cppparser

cd ~/thirdparty
git clone https://github.com/satya-das/common.git
git clone https://github.com/satya-das/cppparser.git
cd cppparser
cmake -B build -S .
cmake --build build --config Debug
cmake --build build --config Debug --target install

Install vcpkg to fetch boost

cd <arctic-sql project dir>
cmake -B build -S . -DCMAKE_TOOLCHAIN_FILE="[path to vcpkg]/scripts/buildsystems/vcpkg.cmake"
cmake --build build --config Debug
.\build\bin\Debug\sql-test.exe
cmake --build build --config Debug --target install

Build: Ubuntu (22.04)

sudo apt update
sudo apt install git cmake g++ libboost-all-dev flex libsqlite3-dev zlib1g-dev
#sudo pip install conan

cd ~/thirdparty
git clone https://github.com/satya-das/common.git
git clone https://github.com/satya-das/cppparser.git
cd cppparser
mkdir build && cd build
cmake ..
make
sudo make install

cd <arctic-sql project dir>
mkdir build && cd build
cmake ..
make
./test/sql-test
./test/pg-test
sudo make install