### Full Query Composition Example (C++)

Source: https://github.com/getml/sqlgen/blob/main/docs/create_table.md

Illustrates a complete example of composing a SQL CREATE TABLE IF NOT EXISTS query using sqlgen. It demonstrates how to execute the query and provides a basic structure for error handling.

```cpp
using namespace sqlgen;

const auto query = create_table<Person> | if_not_exists;

const auto result = query(conn);

if (!result) {
    // Error handling
}
```

```cpp
const auto query = sqlgen::create_table<Person> | sqlgen::if_not_exists;

const auto result = query(conn);
```

--------------------------------

### Full Query Composition Example in C++

Source: https://github.com/getml/sqlgen/blob/main/docs/create_as.md

Demonstrates a comprehensive example of composing a 'CREATE TABLE IF NOT EXISTS AS' query. It includes defining source and target data structures, creating the SELECT query, applying the 'if_not_exists' clause, and executing the query against a database connection.

```cpp
// Define the source data structure
struct Person {
  sqlgen::PrimaryKey<uint32_t> id;
  std::string first_name;
  std::string last_name;
  int age;
};

// Define the target structure for the new table/view
struct Name {
  std::string first_name;
  std::string last_name;
};

// Create a SELECT query
const auto names_query = select_from<Person>("first_name"_c, "last_name"_c);

// Create table as query
const auto create_table_query = create_as<Name>(names_query) | if_not_exists;

// Execute the query
const auto result = create_table_query(conn);

if (!result) {
    // Error handling
}
```

--------------------------------

### Hello World Example with sqlgen

Source: https://github.com/getml/sqlgen/blob/main/README.md

A basic 'Hello World' example demonstrating how to use sqlgen to connect to an SQLite database, create a user table, insert a user, and then read all users from the database. It shows the complete workflow from connection to data retrieval and printing.

```cpp
#include <sqlgen/sqlite.hpp>
#include <iostream>

struct User {
    std::string name;
    int age;
};

int main() {
    // Connect to SQLite database
    const auto conn = sqlgen::sqlite::connect("test.db");
    
    // Create and insert a user
    const auto user = User{.name = "John", .age = 30};
    sqlgen::write(conn, user);
    
    // Read all users
    const auto users = sqlgen::read<std::vector<User>>(conn).value();
    
    for (const auto& u : users) {
        std::cout << u.name << " is " << u.age << " years old\n";
    }
}
```

--------------------------------

### Full Query Composition and Error Handling Example

Source: https://github.com/getml/sqlgen/blob/main/docs/create_index.md

This snippet provides a comprehensive example of composing an SQL index creation query, including the `if_not_exists` clause, and executing it. It highlights the use of `sqlgen::Result` for handling potential errors during connection or query execution, and demonstrates checking the result for success or failure.

```cpp
using namespace sqlgen;
using namespace sqlgen::literals;

const auto query = create_index<"person_ix", Person>("first_name"_c, "last_name"_c) | if_not_exists;

const auto result = query(conn);

if (!result) {
    // Error handling
}
```

```cpp
const auto query = sqlgen::create_index<"person_ix", Person>("first_name"_c, "last_name"_c) | 
                   sqlgen::if_not_exists;

const auto result = query(conn);
```

--------------------------------

### Working with Related Data and Joins in C++

Source: https://github.com/getml/sqlgen/blob/main/docs/foreign_key.md

Demonstrates how to insert related data into two tables ('Person' and 'Relationship') using foreign keys and then write both tables to the database. This example highlights the setup for performing joins.

```cpp
struct Person {
    sqlgen::PrimaryKey<uint32_t> id;
    std::string first_name;
    std::string last_name;
};

struct Relationship {
    sqlgen::ForeignKey<uint32_t, Person, "id"> parent_id;
    uint32_t child_id;
};

// Insert parent records
auto people = std::vector<Person>({
    Person{.id = 1, .first_name = "Homer", .last_name = "Simpson"},
    Person{.id = 2, .first_name = "Marge", .last_name = "Simpson"}
});

// Insert relationship records that reference the parents
auto relationships = std::vector<Relationship>({
    Relationship{.parent_id = 1, .child_id = 3},  // Homer -> child 3
    Relationship{.parent_id = 1, .child_id = 4},  // Homer -> child 4
    Relationship{.parent_id = 2, .child_id = 3},  // Marge -> child 3
    Relationship{.parent_id = 2, .child_id = 4}   // Marge -> child 4
});

// Write both tables to the database
conn.and_then(create_table<Person> | if_not_exists)
    .and_then(create_table<Relationship> | if_not_exists)
    .and_then(insert(std::ref(people)))
    .and_then(insert(std::ref(relationships)));
```

--------------------------------

### Session Management in C++ with SQLGen

Source: https://github.com/getml/sqlgen/blob/main/docs/connection_pool.md

Demonstrates proper session management in C++ using SQLGen. The 'Good' example shows releasing the session immediately after use, while the 'Bad' example illustrates holding the session longer than necessary, which can lead to resource wastage.

```cpp
// Good: Session is released immediately after use
session(pool).and_then(execute_query).value();

// Bad: Session is held longer than necessary
const auto sess = session(pool).value();
// ... other operations ...
sess.execute_query();
```

--------------------------------

### Compile-Time Validation Example in C++

Source: https://github.com/getml/sqlgen/blob/main/docs/foreign_key.md

An example showcasing the compile-time validation provided by sqlgen::ForeignKey. It demonstrates a valid foreign key definition and comments out an invalid one to highlight type compatibility checks.

```cpp
struct Person {
    sqlgen::PrimaryKey<uint32_t> id;
    std::string name;
};

struct Order {
    sqlgen::PrimaryKey<uint32_t> id;
    // This will compile successfully - "id" exists in Person, is a primary key, and types match
    sqlgen::ForeignKey<uint32_t, Person, "id"> person_id;
        
    // This would cause a compile error - type mismatch
    // sqlgen::ForeignKey<std::string, Person, "id"> person_id;
};
```

--------------------------------

### Install SQLGen Target and Headers

Source: https://github.com/getml/sqlgen/blob/main/CMakeLists.txt

Installs the 'sqlgen' target and its associated header files. The headers are installed to a destination defined by 'INCLUDE_INSTALL_DIR'. This ensures the library and its headers are available after installation.

```cmake
install(
    TARGETS sqlgen
    EXPORT sqlgen-exports
    FILE_SET sqlgen_headers DESTINATION ${INCLUDE_INSTALL_DIR}
)
```

--------------------------------

### Connecting to Databases with sqlgen

Source: https://github.com/getml/sqlgen/blob/main/README.md

Demonstrates how to establish connections to different types of databases using sqlgen. It includes examples for PostgreSQL and SQLite, showing the necessary credentials and connection functions.

```cpp
#include <sqlgen/postgres.hpp>

// PostgreSQL connection
const auto credentials = sqlgen::postgres::Credentials{
    .user = "username",
    .password = "password",
    .host = "localhost",
    .dbname = "mydb",
    .port = 5432
};

const auto conn = sqlgen::postgres::connect(credentials);

// SQLite connection
const auto sqlite_conn = sqlgen::sqlite::connect("database.db");
```

--------------------------------

### Full SQL Query Composition Example

Source: https://github.com/getml/sqlgen/blob/main/docs/reading.md

Demonstrates a comprehensive query composition including filtering ('where'), sorting by multiple columns with descending order ('order_by'), and limiting results ('limit'). It shows both the idiomatic usage with namespaces and the explicit qualification.

```cpp
using namespace sqlgen;
using namespace sqlgen::literals;

const auto query = sqlgen::read<std::vector<Person>> |
                   where("age"_c >= 18) |
                   order_by("last_name"_c, "first_name"_c.desc()) |
                   limit(10);

const auto adults = query(conn).value();
```

```cpp
const auto query = sqlgen::read<std::vector<Person>> |
                   sqlgen::where(sqlgen::col<"age"> >= 18) |
                   sqlgen::order_by(sqlgen::col<"last_name">, sqlgen::col<"first_name">.desc()) |
                   sqlgen::limit(10);

const auto adults = query(conn).value();
```

--------------------------------

### Full Query Composition Example (C++)

Source: https://github.com/getml/sqlgen/blob/main/docs/update.md

Presents a comprehensive example of composing an UPDATE query, setting multiple columns ('first_name' to 'last_name' and 'age' to 100) with a 'where' clause. It also shows an alternative syntax using explicit `sqlgen::col` if namespaces are not used.

```cpp
using namespace sqlgen;
using namespace sqlgen::literals;

const auto query = update<Person>(
                    "first_name"_c.set("last_name"_c),
                    "age"_c.set(100))
                   |
                   where("age"_c > 0);

const auto result = query(conn);

if (!result) {
    // Error handling
}
```

```cpp
const auto query = sqlgen::update<Person>(
                    sqlgen::col<"first_name">.set(sqlgen::col<"last_name">),
                    sqlgen::col<"age">.set(100))
                   |
                   sqlgen::where(sqlgen::col<"age"> > 0);

const auto result = query(conn);
```

--------------------------------

### Insert or Replace Rows using sqlgen::insert_or_replace

Source: https://github.com/getml/sqlgen/blob/main/docs/insert.md

Demonstrates replacing existing rows in a SQL table if they conflict based on the primary key, using sqlgen::insert_or_replace. This example also shows table creation with `if_not_exists` and chaining operations within a `sqlgen::Result` context. It provides examples of generated SQL for SQLite and MySQL backends.

```cpp
const auto people1 = std::vector<Person>({
    Person{.id = 0, .first_name = "Homer", .last_name = "Simpson", .age = 45},
    Person{.id = 1, .first_name = "Bart", .last_name = "Simpson", .age = 10}
});

const auto people2 = std::vector<Person>({
    Person{.id = 1, .first_name = "Bartholomew", .last_name = "Simpson", .age = 10}
});

using namespace sqlgen;

const auto result = sqlite::connect()
    .and_then(create_table<Person> | if_not_exists)
    .and_then(insert(std::ref(people1)))
    .and_then(insert_or_replace(std::ref(people2)))
    .value();
```

```sql
CREATE TABLE IF NOT EXISTS "Person" (
    "id" INTEGER PRIMARY KEY,
    "first_name" TEXT NOT NULL,
    "last_name" TEXT NOT NULL,
    "age" INTEGER NOT NULL
);
INSERT INTO "Person" ("id", "first_name", "last_name", "age") VALUES (?, ?, ?, ?);
INSERT INTO "Person" ("id", "first_name", "last_name", "age") VALUES (?, ?, ?, ?)
ON CONFLICT (id) DO UPDATE SET
  id=excluded.id,
  first_name=excluded.first_name,
  last_name=excluded.last_name,
  age=excluded.age;
```

```sql
INSERT INTO `Person` (`id`, `first_name`, `last_name`, `age`) VALUES (?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
  id=VALUES(id),
  first_name=VALUES(first_name),
  last_name=VALUES(last_name),
  age=VALUES(age);
```

--------------------------------

### Full Timestamp Operations Example in sqlgen

Source: https://github.com/getml/sqlgen/blob/main/docs/timestamp_operations.md

Demonstrates a comprehensive query using various sqlgen timestamp functions, including arithmetic, date difference, epoch conversion, and extraction, aliasing each result. The example selects data from the 'Person' table and projects it into a 'Birthday' struct, generating SQL for SQLite and Postgres.

```cpp
struct Birthday {
    Date birthday;
    Date birthday_recreated;
    time_t birthday_unixepoch;
    double age_in_days;
    int hour;
    int minute;
    int second;
    int weekday;
};

const auto get_birthdays = select_from<Person>(
    ("birthday"_c + std::chrono::days(10)) | as<"birthday">
    ,
    (cast<Date>(concat(cast<std::string>(year("birthday"_c)), "-",
                        cast<std::string>(month("birthday"_c)), "-",
                        cast<std::string>(day("birthday"_c))))) | as<"birthday_recreated">
    ,
    days_between("birthday"_c, Date("2011-01-01")) | as<"age_in_days">
    ,
    unixepoch("birthday"_c + std::chrono::days(10)) | as<"birthday_unixepoch">
    ,
    hour("birthday"_c) | as<"hour">
    ,
    minute("birthday"_c) | as<"minute">
    ,
    second("birthday"_c) | as<"second">
    ,
    weekday("birthday"_c) | as<"weekday">
) | order_by("id"_c) | to<std::vector<Birthday>>;
```

--------------------------------

### C++ Query Composition with Joins and Grouping

Source: https://github.com/getml/sqlgen/blob/main/docs/select_from.md

Shows how to build a query using `sqlgen::select_from` that includes multiple `JOIN` operations, `GROUP BY`, and `ORDER BY` clauses. This example highlights the library's capability in constructing complex relational queries.

```cpp
const auto query = select_from<Person, "t1">
(
    "last_name"_t1 | as<"last_name">
    ,
    "first_name"_t3 | as<"first_name_child">
    ,
    avg("age"_t1 - "age"_t3) | as<"avg_parent_age_at_birth">
)
| inner_join<Relationship, "t2">("id"_t1 == "parent_id"_t2)
| inner_join<Person, "t3">("id"_t3 == "child_id"_t2)
| group_by("last_name"_t1, "first_name"_t3)
| order_by("last_name"_t1, "first_name"_t3)
| to<std::vector<ParentAndChild>>;
```

--------------------------------

### Format Specification Example

Source: https://github.com/getml/sqlgen/blob/main/docs/timestamp.md

Provides an example of defining a timestamp format using a template parameter with strftime specifiers. It explains the significance of this format for database reading, writing, and validation, highlighting runtime error prevention for mismatched formats.

```cpp
struct Person {
    sqlgen::Timestamp<"%Y-%m-%d %H:%M:%S"> birthdate;
};

/*
This definition implies:
- Reading from the database requires the format "YYYY-MM-DD HH:MM:SS".
- Writing to the database will format the timestamp as "YYYY-MM-DD HH:MM:SS".
- Assigning a string in a different format will result in a runtime error.

Supported strftime specifiers include:
- %Y: Year with century (e.g., 2024)
- %m: Month (01-12)
- %d: Day of month (01-31)
- %H: Hour in 24h format (00-23)
- %M: Minute (00-59)
- %S: Second (00-59)
- %z: Timezone offset (e.g., +0000, -0500)

Common format patterns:
- "%Y-%m-%d %H:%M:%S" for regular timestamps
- "%Y-%m-%d %H:%M:%S%z" for timestamps with timezone
*/
```

--------------------------------

### Chained Database Operations with sqlgen::exec

Source: https://github.com/getml/sqlgen/blob/main/docs/exec.md

Illustrates a complete example of using sqlgen::exec within a chain of database operations, including setting time zone, dropping tables, writing data, and reading data. This showcases the power of chaining multiple operations.

```cpp
using namespace sqlgen;

const auto result = postgres::connect(credentials)
    .and_then(exec("SET TIME ZONE 'UTC';"))
    .and_then(drop<Person> | if_exists)
    .and_then(write(people))
    .and_then(read<std::vector<Person>>)
    .value();
```

--------------------------------

### C++ Struct Mapping with SQL Aliases

Source: https://github.com/getml/sqlgen/blob/main/docs/group_by_and_aggregations.md

An example demonstrating how C++ struct members must precisely match the aliases defined using the .as operator in a SQL query. Field matching is by name, not order.

```cpp
struct Result {
    std::string field;           // Matches "field1"_c.as<"field">()
    double avg_field2;           // Matches avg("field2"_c).as<"avg_field2">()
    std::optional<std::string> nullable_field;  // Matches "nullable"_c | as<"nullable_field">
    int one;                     // Matches as<"one">(1)
    std::string hello;           // Matches "hello" | as<"hello">
};

const auto query = select_from<TestTable>(
    "field1"_c.as<"field">(),
    avg("field2"_c).as<"avg_field2">(),
    "nullable"_c | as<"nullable_field">
    as<"one">(1),
    "hello" | as<"hello">
) | to<Result>;
```

--------------------------------

### Complex Nested Query Example (C++)

Source: https://github.com/getml/sqlgen/blob/main/docs/select_from.md

Provides an advanced example of constructing a complex nested query. It first defines a subquery to establish parent-child relationships and then uses this subquery within a larger query involving joins and aggregations to retrieve detailed information. Ensure that result struct field names precisely match the query aliases for successful data mapping.

```cpp
struct ParentAndChild {
    std::string last_name;
    std::string first_name_parent;
    std::string first_name_child;
    double parent_age_at_birth;
};

// Step 1: Create a subquery to get parent-child relationships
const auto get_children = select_from<Relationship, "t1">
(
    "parent_id"_t1 | as<"id">
,
    "first_name"_t2 | as<"first_name">
,
    "age"_t2 | as<"age">
)
| inner_join<Person, "t2">("id"_t2 == "child_id"_t1);

// Step 2: Use the subquery in a larger query
const auto get_people = select_from<Person, "t1">
(
    "last_name"_t1 | as<"last_name">
,
    "first_name"_t1 | as<"first_name_parent">
,
    "first_name"_t2 | as<"first_name_child">
,
    ("age"_t1 - "age"_t2) | as<"parent_age_at_birth">
)
| inner_join<"t2">(get_children, "id"_t1 == "id"_t2)
| order_by("id"_t1, "id"_t2)
| to<std::vector<ParentAndChild>>;
```

--------------------------------

### Transaction Management in C++ with SQLGen

Source: https://github.com/getml/sqlgen/blob/main/docs/connection_pool.md

Illustrates atomic transaction management in C++ using SQLGen. This example chains operations including beginning a transaction, updating a record, and committing the transaction, ensuring atomicity.

```cpp
session(pool)
    .and_then(begin_transaction)
    .and_then(update<Person>("age"_c.set(46)))
    .and_then(commit)
    .value();
```

--------------------------------

### Build sqlgen with Conan

Source: https://github.com/getml/sqlgen/blob/main/README.md

Instructions for building the sqlgen library from source using Conan. This includes installing Conan, detecting the build profile, and the command to build the library. Options for shared libraries and MySQL/MariaDB support are also mentioned.

```bash
pipx install conan
conan profile detect
```

```bash
conan build . --build=missing -s compiler.cppstd=gnu20
```

```cmake
find_package(sqlgen REQUIRED)
target_link_libraries(your_target PRIVATE sqlgen::sqlgen)
```

--------------------------------

### Create SQL Index Conditionally (IF NOT EXISTS)

Source: https://github.com/getml/sqlgen/blob/main/docs/create_index.md

This snippet shows how to create an SQL index only if it does not already exist, using the `if_not_exists` clause. It demonstrates composing the query with the `|` operator and executing it, including an example with monadic error handling.

```cpp
using namespace sqlgen;
using namespace sqlgen::literals;

const auto query = create_index<"person_ix", Person>("first_name"_c, "last_name"_c) | if_not_exists;

query(conn).value();
```

```cpp
using namespace sqlgen;
using namespace sqlgen::literals;

const auto query = create_index<"person_ix", Person>("first_name"_c, "last_name"_c) | if_not_exists;

// sqlgen::Result<Ref<Connection>>
const auto result = sqlite::connect("database.db").and_then(query);
```

--------------------------------

### SQL Statements Executed in Sequence

Source: https://github.com/getml/sqlgen/blob/main/docs/exec.md

Shows the sequence of raw SQL statements that would be executed by the preceding C++ code example using sqlgen::exec and other sqlgen operations. This provides a clear view of the underlying database commands.

```sql
SET TIME ZONE 'UTC';
DROP TABLE IF EXISTS "Person";
-- Table creation and data insertion via write
-- Data retrieval via read
```

--------------------------------

### Grouping and Aggregating Data with sqlgen

Source: https://github.com/getml/sqlgen/blob/main/README.md

Provides an example of how to perform grouping and aggregation operations on database data using sqlgen. It shows how to define aggregate functions like COUNT, MAX, MIN, and SUM, and how to group results by a specific column.

```cpp
using namespace sqlgen;
using namespace sqlgen::literals;

struct Children {
    std::string last_name;
    int num_children;
    int max_age;
    int min_age;
    int sum_age;
};

const auto get_children = select_from<Person>(
    "last_name"_c,
    count().as<"num_children">(),
    max("age"_c).as<"max_age">(),
    min("age"_c).as<"min_age">(),
    sum("age"_c).as<"sum_age">
) | where("age"_c < 18) | group_by("last_name"_c) | to<std::vector<Children>>;

const std::vector<Children> children = get_children(conn).value();
```

```sql
SELECT 
    "last_name",
    COUNT(*) as "num_children",
    MAX("age") as "max_age",
    MIN("age") as "min_age",
    SUM("age") as "sum_age"
FROM "Person"
WHERE "age" < 18
GROUP BY "last_name";
```

--------------------------------

### Configure Connection Pool with Retry Behavior

Source: https://github.com/getml/sqlgen/blob/main/docs/connection_pool.md

Example of configuring the connection pool with specific retry parameters for acquiring connections. This includes setting the number of attempts and the wait time between attempts, ensuring resilience against temporary connection issues.

```cpp
using namespace sqlgen;

// Configure pool with retry behavior
ConnectionPoolConfig config{
    .size = 4,
    .num_attempts = 5,
    .wait_time_in_seconds = 2
};

const auto pool = make_connection_pool<postgres::Connection>(config, credentials);

// Acquire a session - will retry if no connections are available
const auto session_result = session(pool);

```

--------------------------------

### Generate Full Name and Trimmed Last Name Reports

Source: https://github.com/getml/sqlgen/blob/main/docs/string_operations.md

This example demonstrates creating a report of people, formatting their full names in uppercase with a comma and space, and trimming whitespace from their last names. It utilizes `select_from`, `concat`, `upper`, and `trim` functions.

```cpp
struct PersonReport {
  std::string full_name;
  std::string last_name_trimmed;
};

const auto get_reports = 
    select_from<Person>(
        concat(upper("last_name"_c), ", ", "first_name"_c).as<"full_name">(),
        trim("last_name"_c).as<"last_name_trimmed">()
    )
    | to<std::vector<PersonReport>>;
```

--------------------------------

### Full Transaction Usage with sqlgen::insert

Source: https://github.com/getml/sqlgen/blob/main/docs/insert.md

Illustrates a complete transaction flow using sqlgen, starting with connecting to the database, beginning a transaction, creating a table if it doesn't exist, inserting data, and finally committing the transaction. This ensures atomicity for related database operations.

```cpp
using namespace sqlgen;

const auto result = sqlite::connect()
    .and_then(begin_transaction)
    .and_then(create_table<Person> | if_not_exists)
    .and_then(insert(std::ref(people)))
    .and_then(commit)
    .value();
```

```sql
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "Person" (
    "id" INTEGER PRIMARY KEY,
    "first_name" TEXT NOT NULL,
    "last_name" TEXT NOT NULL,
    "age" INTEGER NOT NULL
);
INSERT INTO "Person" ("id", "first_name", "last_name", "age") VALUES (?, ?, ?, ?);
COMMIT;
```

--------------------------------

### Nested Joins with Subquery C++

Source: https://github.com/getml/sqlgen/blob/main/docs/joins.md

Shows how to perform nested joins with subqueries in C++ using the sqlgen library. This example first defines a subquery to get children and then joins it with the Person table. Requires C++ and sqlgen.

```cpp
const auto get_children = 
    select_from<Relationship, "t1">
    (
        "parent_id"_t1 | as<"id">
      , "first_name"_t2 | as<"first_name">
      , "age"_t2 | as<"age">
    ) |
    left_join<Person, "t2">("id"_t2 == "child_id"_t1);

const auto get_people = 
    select_from<Person, "t1">
    (
        "last_name"_t1 | as<"last_name">
      , "first_name"_t2 | as<"first_name_child">
      , avg("age"_t1 - "age"_t2) | as<"avg_parent_age_at_birth">
    ) |
    inner_join<"t2">(get_children, "id"_t1 == "id"_t2) |
    group_by("last_name"_t1, "first_name"_t2) |
    order_by("first_name"_t2) |
    to<std::vector<ParentAndChild>>;
```

--------------------------------

### Get String Length

Source: https://github.com/getml/sqlgen/blob/main/docs/string_operations.md

Demonstrates how to get the length of a string using the `length` function, optionally after trimming whitespace. The result is aliased for clarity in the query output.

```cpp
length(trim("first_name"_c)) | as<"length_first_name">
```

--------------------------------

### Create a Connection Pool with Default Configuration

Source: https://github.com/getml/sqlgen/blob/main/docs/connection_pool.md

Illustrates the process of creating a connection pool using a predefined configuration and necessary credentials. It includes error handling for pool creation failures, outputting the error message if the pool cannot be initialized.

```cpp
using namespace sqlgen;

const auto pool = make_connection_pool<postgres::Connection>(
    config,  // ConnectionPoolConfig
    credentials // Variables necessary to create the connections
);

if (!pool) {
    // Handle error
    std::cerr << "Failed to create pool: " << pool.error() << std::endl;
    return;
}
```

--------------------------------

### Install Package Configuration File

Source: https://github.com/getml/sqlgen/blob/main/CMakeLists.txt

Installs the generated 'sqlgen-config.cmake' file to the appropriate CMake package directory. This makes the configuration file available for external projects to use. The destination is set based on CMAKE_INSTALL_LIBDIR.

```cmake
install(
    FILES "${CMAKE_CURRENT_BINARY_DIR}/sqlgen-config.cmake"
    DESTINATION "${CMAKE_INSTALL_LIBDIR}/cmake/sqlgen"
)
```

--------------------------------

### Referential Integrity Enforcement Example

Source: https://github.com/getml/sqlgen/blob/main/docs/foreign_key.md

Illustrates the enforcement of referential integrity by foreign keys. It shows an example of an invalid relationship insertion that would fail because the referenced 'Person' does not exist, highlighting the database-level checks.

```cpp
// This would fail if Person with id = 999 doesn't exist
auto invalid_relationship = Relationship{
    .parent_id = 999,  // This Person doesn't exist
    .child_id = 1
};
```

--------------------------------

### MySQL CREATE TABLE AS Syntax

Source: https://github.com/getml/sqlgen/blob/main/docs/create_as.md

Shows the equivalent SQL syntax for creating a table in MySQL using the 'CREATE TABLE AS' statement. It includes the 'IF NOT EXISTS' clause for safe table creation.

```sql
-- Table creation
CREATE TABLE IF NOT EXISTS `Name` AS SELECT `first_name`, `last_name` FROM `Person`;
```

--------------------------------

### Basic Caching and Execution with sqlgen::cache (C++)

Source: https://github.com/getml/sqlgen/blob/main/docs/cache.md

Demonstrates how to wrap a sqlgen query with the cache, execute it, and retrieve results from the cache. It also shows an alternative way to apply caching using `conn.and_then`. The example highlights that identical queries served from the cache do not increment the cache size.

```cpp
#include <sqlgen.hpp>

// Define a table
struct User {
  std::string name;
  int age;
};

// Create a query
const auto query = sqlgen::read<User> | where("name"_c == "John");

// Wrap the query with the cache
const auto cached_query = sqlgen::cache<100>(query);

// Execute the query
const auto user1 = cached_query(conn).value();
const auto user2 = cached_query(conn).value();

// Also OK 
const auto user3 = conn.and_then(
    cache<100>(sqlgen::read<User> | where("name"_c == "John"))).value();

// The cache size will be 1, because the second and third query were served from the cache.
// auto cache_size = cached_query.cache(conn).size(); // cache_size is 1
```

--------------------------------

### Install Export Set for SQLGen

Source: https://github.com/getml/sqlgen/blob/main/CMakeLists.txt

Installs the CMake export set for the 'sqlgen' target. This allows other projects to find and link against SQLGen using CMake's find_package mechanism. It defines a namespace 'sqlgen::' for exported targets.

```cmake
install(
    EXPORT sqlgen-exports
    DESTINATION ${CMAKE_INSTALL_LIBDIR}/cmake/sqlgen
    NAMESPACE sqlgen::
```

--------------------------------

### PostgreSQL CREATE TABLE AS Syntax

Source: https://github.com/getml/sqlgen/blob/main/docs/create_as.md

Presents the standard SQL syntax for creating a table in PostgreSQL using 'CREATE TABLE AS'. The 'IF NOT EXISTS' clause is included to prevent errors if the table already exists.

```sql
-- Table creation
CREATE TABLE IF NOT EXISTS "Name" AS SELECT "first_name", "last_name" FROM "Person";
```

--------------------------------

### MySQL CREATE OR REPLACE VIEW AS Syntax

Source: https://github.com/getml/sqlgen/blob/main/docs/create_as.md

Demonstrates the SQL syntax for creating or replacing a view in MySQL using 'CREATE OR REPLACE VIEW AS'. This is used to define or update a view based on a SELECT query.

```sql
-- View creation  
CREATE OR REPLACE VIEW `NAMES` AS SELECT `first_name`, `last_name` FROM `PEOPLE`;
```

--------------------------------

### Create Table with IF NOT EXISTS (C++)

Source: https://github.com/getml/sqlgen/blob/main/docs/create_table.md

Shows how to create a SQL table only if it does not already exist using the `if_not_exists` clause with sqlgen::create_table. This approach supports fluent composition and monadic error handling.

```cpp
using namespace sqlgen;

const auto query = create_table<Person> | if_not_exists;

query(conn).value();
```

```cpp
using namespace sqlgen;

const auto query = create_table<Person> | if_not_exists;

// sqlgen::Result<Ref<Connection>>
const auto result = sqlite::connect("database.db").and_then(query);
```

--------------------------------

### C++ sqlgen::Varchar Value Access and Size Information

Source: https://github.com/getml/sqlgen/blob/main/docs/varchar.md

Details how to retrieve the string value stored within a sqlgen::Varchar object using different accessor methods (`()`, `get()`, `value()`). It also shows how to get the compile-time maximum size of the VARCHAR field.

```cpp
struct Person{
    sqlgen::Varchar<50> name;
};

const auto person = Person{.name = "John Doe"};

// Get the value
const std::string& value1 = person.name();  
const std::string& value2 = person.name.get();
const std::string& value3 = person.name.value();  

// Get the maximum size
constexpr size_t max_size = name.size();
```

--------------------------------

### Create Basic SQL Index

Source: https://github.com/getml/sqlgen/blob/main/docs/create_index.md

This snippet demonstrates how to create a basic SQL index on one or more columns of a table using the `sqlgen::create_index` interface. It shows both direct execution and monadic error handling with `sqlgen::Result`. The interface ensures type safety and compile-time checks for column existence.

```cpp
const auto conn = sqlgen::sqlite::connect("database.db");

sqlgen::create_index<"person_ix", Person>("first_name"_c, "last_name"_c)(conn).value();
```

```cpp
// sqlgen::Result<Ref<Connection>>
const auto result = sqlgen::sqlite::connect("database.db").and_then(
                        sqlgen::create_index<"person_ix", Person>("first_name"_c, "last_name"_c));
```

--------------------------------

### PostgreSQL CREATE OR REPLACE VIEW AS Syntax

Source: https://github.com/getml/sqlgen/blob/main/docs/create_as.md

Shows the SQL syntax for creating or replacing a view in PostgreSQL. This statement defines or updates a view based on the results of a SELECT query.

```sql
-- View creation
CREATE OR REPLACE VIEW "NAMES" AS SELECT "first_name", "last_name" FROM "PEOPLE";
```

--------------------------------

### C++ Query Composition: Filter, Order, Limit, Offset

Source: https://github.com/getml/sqlgen/blob/main/docs/select_from.md

Demonstrates composing a `sqlgen::select_from` query with common SQL clauses like `WHERE`, `ORDER BY`, `LIMIT`, and `OFFSET`. This showcases the chainable nature of the `sqlgen` library for building complex queries step-by-step.

```cpp
const auto query = select_from<Person>(
    "first_name"_c,
    "last_name"_c,
    "age"_c
)
| where("age"_c >= 18)              // Filter results
| order_by("last_name"_c, "first_name"_c)  // Order results
| limit(10)                         // Limit number of results
| offset(5)                         // Offset the result set
| to<std::vector<Person>>;
```

--------------------------------

### Configure and Create a PostgreSQL Connection Pool

Source: https://github.com/getml/sqlgen/blob/main/docs/connection_pool.md

Demonstrates how to configure a connection pool with specific settings like size, retry attempts, and wait time. It then creates a pool for PostgreSQL connections using provided credentials. This setup ensures efficient management and thread-safe access to database connections.

```cpp
using namespace sqlgen;

ConnectionPoolConfig config{
    .size = 4,                    // Number of connections in the pool
    .num_attempts = 10,          // Number of retry attempts when acquiring a connection
    .wait_time_in_seconds = 1    // Wait time between retry attempts
};

// Create a pool with the specified configuration
const auto pool = make_connection_pool<postgres::Connection>(
    config,
    postgres::Credentials{
        .user = "postgres",
        .password = "password",
        .host = "localhost",
        .dbname = "postgres"
    }
);
```

--------------------------------

### Concatenate Strings

Source: https://github.com/getml/sqlgen/blob/main/docs/string_operations.md

Provides examples of the `concat` function for joining multiple strings or expressions together. This includes simple concatenation and concatenation with case conversion.

```cpp
concat("first_name"_c, " ", "last_name"_c) | as<"full_name">
concat(upper("last_name"_c), ", ", "first_name"_c) | as<"full_name">
```

--------------------------------

### Drop Views in C++ using SQLGen

Source: https://github.com/getml/sqlgen/blob/main/docs/views.md

Illustrates how to drop views using SQLGen's `drop` interface. It shows the basic syntax for dropping a view and how to use the `if_exists` option to prevent errors if the view does not exist. Requires the `sqlgen` namespace to be included.

```cpp
using namespace sqlgen;

const auto result = drop<EmployeeView>(conn);
// or with if_exists
const auto result = (drop<EmployeeView> | if_exists)(conn);
```

--------------------------------

### Create a Filtered View in C++

Source: https://github.com/getml/sqlgen/blob/main/docs/views.md

Defines and creates a view that includes only specific rows based on a `WHERE` clause. This example filters employees with a salary greater than 50000.

```cpp
struct HighSalaryEmployees {
  static constexpr bool is_view = true;
  
  std::string first_name;
  std::string last_name;
  int salary;
};

const auto high_salary_query = select_from<Employee>("first_name"_c, "last_name"_c, "salary"_c) |
                               where("salary"_c > 50000);

const auto create_high_salary_view = create_or_replace_view_as<HighSalaryEmployees>(high_salary_query);

```

--------------------------------

### Create a Joined View in C++

Source: https://github.com/getml/sqlgen/blob/main/docs/views.md

Defines and creates a view that combines data from multiple tables using an `INNER JOIN`. This example joins the `Employee` and `Department` tables.

```cpp
struct EmployeeDepartment {
  static constexpr bool is_view = true;
  
  std::string first_name;
  std::string last_name;
  std::string department_name;
  std::string location;
};

const auto joined_query = select_from<Employee, "t1">
(
    "first_name"_t1 | as<"first_name">
    "last_name"_t1 | as<"last_name">
    "department_name"_t2 | as<"department_name">
    "location"_t2 | as<"location">
)
| inner_join<Department, "t2">("department_id"_t1 == "id"_t2);

const auto create_joined_view = create_or_replace_view_as<EmployeeDepartment>(joined_query);

```

--------------------------------

### Update Data in DuckDB Table

Source: https://github.com/getml/sqlgen/blob/main/docs/duckdb.md

Provides an example of updating data in a DuckDB table, allowing for modifications to multiple columns based on specified conditions using a WHERE clause.

```cpp
using namespace sqlgen;
using namespace sqlgen::literals;

// Update multiple columns
const auto query = update<Person>("first_name"_c.set("last_name"_c), "age"_c.set(100)) |
                  where("first_name"_c == "Hugo");

query(conn).value();
```

--------------------------------

### Aggregation with Grouping Example (C++)

Source: https://github.com/getml/sqlgen/blob/main/docs/select_from.md

Demonstrates how to perform aggregations like count, average, minimum, and maximum on grouped data using `sqlgen`. The query groups individuals by last name and calculates summary statistics for each group. The results are then ordered and converted into a `std::vector` of `FamilySummary`. Ensure the `FamilySummary` struct is defined and its fields match the query aliases.

```cpp
const auto summary = select_from<Person>(
    "last_name"_c,
    count() | as<"family_size">
,
    avg("age"_c) | as<"avg_age">
,
    min("age"_c) | as<"youngest">
,
    max("age"_c) | as<"oldest">
)
| group_by("last_name"_c)
| order_by("last_name"_c)
| to<std::vector<FamilySummary>>;
```

--------------------------------

### Include sqlgen in CMake Project

Source: https://github.com/getml/sqlgen/blob/main/README.md

Demonstrates how to integrate the sqlgen library into a CMake project. It involves finding the installed sqlgen package and linking it to your target.

```cmake
find_package(sqlgen REQUIRED)
target_link_libraries(your_target PRIVATE sqlgen::sqlgen)
```

--------------------------------

### Create Table Basic (C++)

Source: https://github.com/getml/sqlgen/blob/main/docs/create_table.md

Demonstrates the basic usage of sqlgen::create_table to generate a SQL CREATE TABLE statement. It uses a C++ struct to define the table schema. Error handling can be integrated using sqlgen::Result for monadic operations.

```cpp
const auto conn = sqlgen::sqlite::connect("database.db");

sqlgen::create_table<Person>(conn).value();
```

```cpp
// sqlgen::Result<Ref<Connection>>
const auto result = sqlgen::sqlite::connect("database.db").and_then(
                        sqlgen::create_table<Person>);
```

--------------------------------

### Create an Aggregated View in C++

Source: https://github.com/getml/sqlgen/blob/main/docs/views.md

Defines and creates a view that includes aggregated data from a table. This example calculates the employee count, average salary, and total salary per department.

```cpp
struct DepartmentStats {
  static constexpr bool is_view = true;
  
  std::string department;
  int employee_count;
  double avg_salary;
  int total_salary;
};

const auto stats_query = select_from<Employee>(
                            "department"_c,
                            count("id"_c) | as<"employee_count">
                            avg("salary"_c) | as<"avg_salary">
                            sum("salary"_c) | as<"total_salary">
                        ) | group_by("department"_c);

const auto create_stats_view = create_or_replace_view_as<DepartmentStats>(stats_query);

```

--------------------------------

### Time Zone Support in Timestamps

Source: https://github.com/getml/sqlgen/blob/main/docs/timestamp.md

Demonstrates the usage of sqlgen::Timestamp for both regular timestamps (without timezone information) and timestamps with timezone support. Includes examples of assignment for both types.

```cpp
// Regular timestamp (without timezone)
sqlgen::Timestamp<"%Y-%m-%d %H:%M:%S"> local_time;

// Timestamp with timezone
sqlgen::Timestamp<"%Y-%m-%d %H:%M:%S%z"> utc_time;

// Examples
local_time = "2024-03-20 15:30:00";  // Local time
utc_time = "2024-03-20 15:30:00+0000";  // UTC time with offset
```

--------------------------------

### Trim Whitespace from Strings

Source: https://github.com/getml/sqlgen/blob/main/docs/string_operations.md

Shows how to remove leading, trailing, or both leading and trailing whitespace from strings using `ltrim`, `rtrim`, and `trim`. It also includes an example of trimming specific characters.

```cpp
ltrim("first_name"_c) | as<"ltrimmed_name">
rtrim("last_name"_c) | as<"rtrimmed_name">
trim("nickname"_c) | as<"trimmed_nickname">
// With custom characters:
ltrim("field"_c, "_ ") | as<"ltrimmed_field">
```

--------------------------------

### coalesce Nullability Semantics Examples

Source: https://github.com/getml/sqlgen/blob/main/docs/null_handling_operations.md

Demonstrates the nullability of the `coalesce` function's return type based on the nullability of its arguments. The function enforces that all arguments must have the same underlying type.

```cpp
// All arguments nullable: result is nullable
coalesce(std::optional<int>{}, std::optional<int>{}); // -> std::optional<int>

// At least one argument non-nullable: result is non-nullable
coalesce(std::optional<int>{}, 42); // -> int
coalesce(42, std::optional<int>{}); // -> int

// All arguments non-nullable: result is non-nullable
coalesce(1, 2); // -> int

// Mixed string example
coalesce(std::optional<std::string>{}, "default"); // -> std::string

// Compile-time error: mismatched types
// coalesce(std::optional<int>{}, std::optional<double>{}); // Error
```

--------------------------------

### SQLite CREATE VIEW IF NOT EXISTS Syntax

Source: https://github.com/getml/sqlgen/blob/main/docs/create_as.md

Demonstrates the SQL syntax for creating a view in SQLite using 'CREATE VIEW IF NOT EXISTS'. SQLite does not support 'CREATE OR REPLACE VIEW', so this is the alternative for creating or conditionally creating views.

```sql
-- View creation - SQLite does NOT support CREATE OR REPLACE VIEW
-- Instead, it uses CREATE VIEW IF NOT EXISTS
CREATE VIEW IF NOT EXISTS "NAMES" AS SELECT "first_name", "last_name" FROM "PEOPLE";
```