Zach's Oobject Relational Mapping library - A C++20 ORM for SQLite

zxorm is an attempt to create an ORM for C++ that achieves the following:

• Compile time type safety
• No code generation
• Low amount of boilerplate code
• No requirement to inherit from anything or conform to a prescribed interface
• SQL like syntax for writing more complicated queries without writing raw SQL
• Make it impossible to construct an invalid query

1. Write your objects

struct Object {
    int id = 0;
    std::string some_text;
};

zxorm doesn't provide a base class or anything like that. The types for the SQL table are instead inferred from types used in the C++ code.

2. Create a Table for your object

using ObjectTable = zxorm::Table<"objects", Object,
    zxorm::Column<"id", &Object::id, zxorm::PrimaryKey<>>,
    zxorm::Column<"some_text", &Object::some_text>
>;

This is the mapping that will tell zxorm how to deal with Object and what table in the database it is referring to.

3. Create a connection

auto connection = zxorm::Connection<ObjectTable>("./path/to/my/data.db");

The connection template accepts a list of tables, and should contain all the tables that your application is going to work with.

4. Start running queries

connection.create_tables();
connection.insert_many_records(std::vector<Object> {
    { .some_text = "This is my data" },
    { .some_text = "Wow it is so important" },
    { .some_text = "Better make sure it is saved" },
    { .some_text = "!" },
});
std::optional<Object> record = connection.find_record<Object>(4);
assert(result.has_value());
assert(result.value().some_text == "!");

Check out the example for more

The library is header only, so all you need to do is include the includes directory, and link sqlite

g++ example.cpp -Izxorm/include -o example.bin `pkg-config --libs sqlite3` -std=c++20

There is also a CMakeLists.txt that can be used for integrating easily into a CMake project. So you can do one of the following:

add_subdirectory("./zxorm")

or

FetchContent_Declare(
  zxorm
  GIT_REPOSITORY https://github.com/crabmandable/zxorm.git
  GIT_TAG <whatever the current version is>
)
FetchContent_MakeAvailable(zxorm)

or

# build & install zxorm
cmake -B build && cmake --build build && sudo cmake --install build

# require zxorm in your CMakeLists.txt
requires(zxorm)

Currently only gcc 12 and clang 15 are tested and working on linux

I don't have a Windows machine, and won't be adding support any time soon.

For more complicated queries, the connection class has the select_query.

This function returns a query builder that can be prepared, and then executed to obtain results.

e.g.

auto prepared_query = connection.select_query<Object>().many();
// or
auto prepared_query = connection.select_query<Object>().one();

// and then
auto results = prepared_query.exec();

many means that the exec function will return a zxorm::RecordIterator<Object>, which allows the results of the query to be streamed.

for (const Object& row: results) {
    std::cout << row.some_text << std::endl;
}

The result of the query can also be loaded into memory all at once by using the to_vector function:

std::vector<Object> rows = results.to_vector();

one will apply a LIMIT 1 clause (if no limit is already specified), and makes that the exec function return an optional.

std::optional<Object> maybe_object = prepared_query.exec();

A WHERE clause can be added by using where_many or where_one:

auto prepared_query = connection.select_query<Object>()
    .where_many(ObjectTable::field_t<"some_text">().like("hello %"));
// or
auto prepared_query = connection.select_query<Object>()
    .where_one(ObjectTable::field_t<"some_text">().like("hello %"));

In order to reference specific fields on the table, the Table template must be used (here ObjectTable is the alias from the example at the top of the README).

Similarly, order and limits can be applied

auto results = connection.select_query<Object>()
    .order_by<ObjectTable::field_t<"some_text">>(zxorm::order_t::DESC)
    .limit(10)
    .many().exec();

The same field template can be used to select specific columns too:

auto results = connection.select_query<ObjectTable::field_t<"some_text">>().many().exec();

Multiple items can be selected using the Select template. The results will be returned as tuples

std::tuple<int, Object> row = connection.select_query<
    Select<ObjectTable::field_t<"id">, Object>
>().one().exec().value();

The template arguments for the select_query function use an SQL-like syntax and can include which columns or tables to return, as well as other tables that should be joined like so:

auto results = connection.select_query<
    Select<User, UserData>,
    From<User>,
    Join<UserData>
>().many().exec();

// In case you are interested, the type of `results` is:
// zxorm::RecordIterator<std::tuple<User, UserData>>

If the From clause is omitted, then it will default to the first thing selected

Of course, many joins can be used. The only limitation is on the order that the clauses are used. Each join should refer to a table that was already "joined" in a previous clause:

auto results = connection.select_query<
    Select<User, Group>,
    From<User>,
    Join<UserGroup>, // we can imagine this is a join table, joining users & groups
    Join<Group>
>().many().exec();

The Join template will only work if zxorm knows about a foreign key that can be used to relate the two tables.

If there is no foreign key, the JoinOn template can be used instead, which takes two zxorm::Fields instead e.g.

auto results = connection.select_query<
    Select<User, Group>,
    From<User>,
    JoinOn<UserGroupTable::field_t<"user_id">, UserTable::field_t<"id">>,
    JoinOn<GroupTable::field_t<"id">, UserGroupTable::field_t<"group_id">>
>().many().exec();

The order of the two fields doesn't matter

The Count template can be used instead of a table or column to select a count

// you can specify the column to count:
auto result = connection->select_query<Count<ObjectTable::field_t<"id">>().one().exec();

// if unspecified, the primary key will be used
auto result = connection->select_query<Count<ObjectTable>>().one().exec();

CountAll can be used to select COUNT(*) also

unsigned long result = connection->select_query<CountAll, From<Object>>().one().exec().value();

And of course the results can be grouped too, allowing occurrences to be counted:

auto results = connection->select_query<
    Select<CountAll, ObjectTable::field_t<"some_text">>,
    From<Object>
>().group_by<ObjectTable::field_t<"some_text">>().many().exec();

The connection has a delete_query function that can be used to deletions. It behaves similarly to the select_query interface.

auto query = connection->delete_query<Object>()
    .where(ObjectTable::field_t<"some_text">().like("hello %"));

query.exec();

Using joins in a delete query is not supported, since it is not part of the SQL standard, and not supported by SQLite.

The basic queries such as find_record, insert_record and delete_record will use statement caching, meaning that the query string does not need to be regenerated, and the statement doesn't need to be recompiled by the underlying SQL engine.

This is possible since the shape of these queries, and the number of binds never changes. For more open ended queries, caching and reuse is possible, but it is up to you to implement.

Once the query has been prepared, exec can be called on it to execute it with the same bindings that were used previously

auto query = connection->select_query<Object>()
                .order_by<ObjectTable::field_t<"some_text">()
                .where_many(ObjectTable::field_t<"some_text">().like("x%"));

auto results = query.exec();
// some time later...
auto more_results = query.exec();

Or the rebind function can be used to change the bound arguments for the WHERE clause

query.rebind("y%");
auto results_for_y = query.exec();

It is important to note that when reusing queries, it is not possible to change the text of a query that was already prepared, it can only be bound with different parameters.

There are five types of exceptions that are intentionally thrown from within the zxorm library:

1. zxorm::Error - This is the base class for all zxorm exceptions.

2. zxorm::SQLConstraintError - This will be thrown if a statement is unable to be executed, due to constraints

3. zxorm::SQLExecutionError - This will be thrown if a statement is unable to be initialized or executed

4. zxorm::ConnectionError - This will be thrown if there is a problem opening or closing a connection to the database.

5. zxorm::InternalError - This likely indicates a bug in zxorm, and will hopefully never been seen outside of development.

Where relevant, the sqlite_errcode() function can be used to query the sqlite extended result code that caused the exception.

The connection constructor can take additional options, flags and z_vfs, these arguments are forwarded directly to SQLite.

The default flags are SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE

You can read about them here

It is also possible to pass a function to the connection when it is created where logs can be sent. This is useful for debugging, but probably shouldn't be used in production.

using connection_t = Connection<table1, table2, table3, //etc...

auto connection = connection_t("mydata.db", 0, nullptr, [](auto level, const auto& msg) {
    if (zxorm::log_level::Error == level)
        std::cerr << "Ooops: " << msg;
    else
        std::cout << msg;
});

Logs will be sent at two levels, Error & Debug

Error logs will include most errors that cause an exception.

Debug will include information about statement preparation, and raw queries that are actually sent to the database.

Here is the logger definition

This is currently not well tested but in theory it should work fine as long as you follow the golden rule:

⚠️ 1 connection per thread

I created this library after looking at what was available in C++ and seeing that the options for a true ORM library are quite limited.

Many C++ programmers will tell you that an ORM is simply bloat that will slow your performance, and they're not entirely wrong. However, there is a reason why they proliferate in higher level languages: they are incredibly valuable for the maintainability of large projects. zxorm is an attempt to have our cake and eat it.

Almost all C++ ORMs are built using traditional object oriented paradigms, without making much use of modern C++ metaprogramming techniques. C++20 introduced many useful metaprogramming features into the language, which zxorm utilizes to generate queries with compile-time type checking.

Much influence was taken from the excellent sqlpp11 library, however it is not a true ORM, and requires code generation, or manually writing a lot of boilerplate.

I wanted to write something that is simple to integrate, easy to start using, and totally agnostic to how the Object in the ORM is written.