Query Builder & CRUD

Relevant source files

The following files were used as context for generating this wiki page:

• include/HXLibs/db/sql/Dml.hpp
• include/HXLibs/db/sql/Expression.hpp
• include/HXLibs/db/sql/DataBase.hpp
• include/HXLibs/db/sqlite3/MakeCreateDbSql.hpp
• tests/db/MakeSql.cpp

The Query Builder and CRUD system provides a type-safe, fluent C++ interface for interacting with databases, specifically supporting SQLite3. It allows developers to construct SQL queries using C++ expressions, manages database connections through a DataBase interface, and automates table creation and schema enforcement using reflection.

Sources: include/HXLibs/db/sql/DataBase.hpp:31, tests/db/MakeSql.cpp:89-115

Database Management

The DataBase class serves as the entry point for database operations. It uses a CRTP (Curiously Recurring Template Pattern) interface to provide consistent behavior across different database backends.

Key Capabilities

• SQL Templating: The sqlTemplate() function registers a unique compile-time ID for a query. This ensures the internal SQL string is built only once and reused from a cache for subsequent calls.
• Schema Generation: The createDatabase<Table>() function automatically generates and executes CREATE TABLE and CREATE INDEX statements based on C++ struct definitions.
• Execution: Provides exec() for direct SQL execution and execNoThrow() to handle exceptions via a container::Try wrapper.

Sources: include/HXLibs/db/sql/DataBase.hpp:34-60, include/HXLibs/db/sql/DataBase.hpp:80-112

The diagram shows the SQL template lifecycle where queries are cached by a compile-time ID to avoid repeated string construction. Sources: include/HXLibs/db/sql/DataBase.hpp:40-52

CRUD Operations

The library implements CRUD (Create, Read, Update, Delete) operations using a fluent API. Each operation returns a builder object that chains SQL clauses.

Create (Insert)

The system supports inserting entire objects or specific fields. By default, it excludes primary keys to allow for auto-incrementing IDs unless specified otherwise.

• insert<Table>(const Table& t): Inserts a complete object.
• insert<Ptrs...>(const Ts&... ts): Inserts values into specific member pointers.
• returning<Ptrs...>(): Appends a RETURNING clause to retrieve values (e.g., generated IDs) after insertion.

Sources: include/HXLibs/db/sql/Dml.hpp:109-170

Read (Select)

The select() method initiates a query builder. It allows selecting specific columns or aggregate functions.

• Selection: Uses Col{&Table::Member} or sum<Col{&Table::Member}>.
• Joins: Supports join<Table>(), leftJoin<Table>(), and rightJoin<Table>().
• Filtering: Provides where<Expression>() for complex logic.
• Organization: Includes groupBy(), having(), and orderBy().

Sources: include/HXLibs/db/sql/Dml.hpp:89-107, include/HXLibs/db/sql/Dml.hpp:643-705

Update and Delete

Mutation operations use a similar builder pattern to Select, requiring a where() clause to target specific records.

• update<Table>(const Table& t): Updates record values based on an object's state.
• deleteForm<Table>(): Constructs a DELETE FROM statement.
• execGetChanges(): Executes the mutation and returns the count of affected rows.

Sources: include/HXLibs/db/sql/Dml.hpp:178-232, include/HXLibs/db/sql/Dml.hpp:460-485

Expression System

The expression system translates C++ operators into SQL syntax. It uses template metaprogramming to validate column types and binding parameters at compile time.

Supported Operators

Category	Operators / Functions	SQL Translation
Comparison	==, !=, <, >, <=, >=	=, <>, <, >, <=, >=
Logic	&&, `
Set	in<...>, notIn<...>	IN (...), NOT IN (...)
Pattern	like<"pattern">	LIKE 'pattern'
Arithmetic	+, -, *, /	+, -, *, /

Sources: include/HXLibs/db/sql/Expression.hpp:50-150, include/HXLibs/db/sql/Dml.hpp:278-316

Parameter Binding

Placeholders are defined using db::param<T>. You can bind these placeholders by name using .bind<"name">(). During execution, values are passed to the builder methods (e.g., where<...>(val)).

Sources: include/HXLibs/db/sql/Param.hpp, tests/db/MakeSql.cpp:106-121

The sequence shows how the builder prepares a statement, binds parameters, and executes the query to return results. Sources: include/HXLibs/db/sql/Dml.hpp:356-385

Table Constraints and Attributes

The library uses a Constraint<T, Attributes...> wrapper to define database schemas within C++ structs.

Column Attributes

• attr::PrimaryKey<...>: Defines single or composite primary keys.
• attr::NotNull: Enforces NOT NULL constraints.
• attr::Unique: Enforces UNIQUE constraints.
• attr::AutoIncrement: Enables SQLite AUTOINCREMENT.
• attr::DefaultVal<V>: Sets a default value for the column.
• attr::Index<...>: Defines single or multi-column indexes.

Sources: include/HXLibs/db/sqlite3/MakeCreateDbSql.hpp:85-135, tests/db/MakeSql.cpp:46-75

Schema Definition Example

struct User {
    Constraint<int, attr::PrimaryKey<>, attr::AutoIncrement> id;
    Constraint<std::string, attr::NotNull, attr::DefaultVal<"Guest"_fs>> name;
    
    struct UnionAttr {
        attr::Index<attr::IndexOrder<&User::name>> idx_name;
    };
};

Sources: tests/db/MakeSql.cpp:46-60

Summary

The Query Builder & CRUD module provides a high-level abstraction for SQL operations. By leveraging C++20 features and reflection, it ensures that database schemas and queries are synchronized with C++ types at compile time. This reduces runtime SQL errors and simplifies database interaction through a fluent, object-oriented API.

Sources: include/HXLibs/db/sql/DataBase.hpp, include/HXLibs/db/sql/Dml.hpp