sqlgen

sqlgen is a modern, type-safe ORM and SQL query generator for C++20. It is inspired by SQLAlchemy (Python) and Diesel (Rust), providing a fluent, composable interface for database operations.

Key features include:

• Type Safety: Compile-time validation of table schemas and queries.
• SQL Injection Protection: Built-in input validation and parameterized queries.
• Composable Queries: A fluent interface for building complex queries.
• High Performance: Efficient batch operations and prepared statements.
• Reflection Integration: Tightly integrated with reflect-cpp for seamless serialization and deserialization.

It is designed to enable reliable and efficient ETL pipelines by combining C++20 features with high-performance database backends.

sqlgen is a C++ library that allows you to define database schemas using C++ structs and transpile C++ operations into dialect-specific SQL.

Key core concepts include:

• Defining Tables: Using C++ structs to represent database tables.
• Column Representation: Using sqlgen::col to represent columns in queries.
• Literals: Using sqlgen::literals for column and table aliases.
• Field Inheritance: Using sqlgen::Flatten to "inherit" fields from other structs.
• Primary Keys: Defining primary keys via sqlgen::PrimaryKey.
• Error Handling: Managing results and errors with sqlgen::Result.
• SQL Transpilation: Converting C++ operations to SQL using sqlgen::to_sql.

The sqlgen::duckdb module provides a type-safe interface for DuckDB with the following features:

• Database Types: Supports in-memory and file-based databases.
• Transactions: Full support for begin, commit, and rollback.
• Querying: Supports complex queries including WHERE clauses, ORDER BY, LIMIT, OFFSET, JOINs, LIKE pattern matching, and mathematical/string functions.
• Data Types: Supports VARCHAR, TIMESTAMP, DATE, and JSON.
• Schema & Integrity: Supports Foreign keys, referential integrity, Unique constraints, Views, Materialized views, and Indexes.
• Performance: Uses prepared statements and an iterator interface for efficient batch processing.

The sqlgen::sqlite module provides a type-safe and efficient interface for SQLite. Key features include:

• Database Support: Supports both in-memory and file-based databases.
• Type Safety: Provides a type-safe interface for SQLite operations and SQL generation.
• Performance: Uses prepared statements for efficient execution and an iterator interface that supports batch processing.
• Error Handling: All operations return sqlgen::Result<T> for explicit error management.
• Resource Management: Uses sqlgen::Ref<T> for safe resource handling.
• SQL Dialect: Automatically adapts SQL generation to the SQLite dialect.
• Transactions: Supports database transactions.

When using sqlgen::JSON<T>, the library automatically selects the appropriate JSON-compatible column type based on the database dialect you are using:

• PostgreSQL: JSONB
• MySQL / MariaDB: JSON
• SQLite: JSONB

The sqlgen library uses the sqlgen::Timestamp<format> template to represent temporal data. It provides two common type aliases for convenience:

• Date: An alias for Timestamp<"%Y-%m-%d"> (date only).
• DateTime: An alias for Timestamp<"%Y-%m-%d %H:%M:%S"> (date and time).

All timestamp operations are type-safe and propagate nullability.

When building where clauses for delete_from or other query builders, use the "..."_c literal to refer to column names. This literal is defined in the sqlgen::literals namespace.

Using this literal ensures that the column name is checked against the fields of the model struct at compile time. If the string literal does not match a field in the struct, the compiler will generate an error.

If you prefer not to use using namespace sqlgen::literals, you can use the explicit sqlgen::col<"column_name"> syntax.

// Using literals (recommended)
using namespace sqlgen::literals;
const auto query = delete_from<Person> | where("first_name"_c == "Hugo");

// Using explicit col template
const auto query = sqlgen::delete_from<Person> | 
                   sqlgen::where(sqlgen::col<"first_name"> == "Hugo");

Foreign keys in sqlgen enforce data integrity at the database level:

• Insert Validation: The database will reject insertions of foreign key values that do not exist in the referenced table.
• Delete Protection: You cannot delete a record that is currently being referenced by another table's foreign key (unless specific database rules are configured).
• Update Consistency: Updates to primary keys are managed according to the underlying database's foreign key rules.

When constructing queries involving grouping and aggregation, keep the following constraints in mind:

• Clause Order: The group_by clause must be placed before order_by, limit, or offset clauses.
• Uniqueness: You cannot use group_by multiple times in a single query.
• Mapping: The result type must match the structure of your select_from statement. Because fields are matched by name, ensure your struct field names are identical to your .as<"alias">() strings.
• Error Handling: The Result<...> type returned by queries provides error handling. Use .value() to extract the result (which throws on error) or handle the error explicitly.

The sqlgen::Varchar<Size> class provides a type-safe way to handle SQL VARCHAR fields in C++. It uses a template parameter Size to specify the maximum length of the field and enforces length checks at runtime to ensure data integrity. It supports direct string assignment, conversion between different VARCHAR sizes, and provides reflection for SQL operations.

// Create a VARCHAR(50) field
sqlgen::Varchar<50> name;

// Initialize with a string
sqlgen::Varchar<50> name("John Doe");

// Initialize with a std::string
std::string str = "Jane Doe";
sqlgen::Varchar<50> name(str);