### Development Setup for Clorinde

Source: https://github.com/halcyonnouveau/clorinde/blob/main/CONTRIBUTING.md

Steps to set up the development environment, including cloning the repository, running codegen, and verifying tests.

```bash
git clone https://github.com/halcyonnouveau/clorinde.git
```

```bash
cargo run --package test_integration -- --apply-codegen
```

```bash
cargo test --all
```

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

### Static File Examples

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/configuration.md

Illustrates various static file configurations, including simple copying, renaming files during copy, and organizing files into subdirectories within the generated crate.

```toml
static = [
    # Copy LICENSE to root as-is
    "LICENSE",

    # Rename during copy
    { path = "template.env", destination = ".env.example" },

    # Organize into subdirectories
    { path = "docs/api.md", destination = "documentation/api.md" },
    { path = "scripts/build.sh", destination = "tools/build.sh" }
]
```

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

### Run Benchmarks with Cargo

Source: https://github.com/halcyonnouveau/clorinde/blob/main/benches/README.md

Execute the benchmarking suite using the Cargo build tool. Ensure you have the necessary database development libraries installed.

```bash
cargo bench
```

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

### Install Clorinde CLI

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/introduction/installation.md

Use this command to install the latest released version of the Clorinde CLI. Ensure you have Rust and Cargo installed.

```bash
cargo install clorinde
```

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

### Clorinde TOML for Publishing

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/introduction/migration_from_cornucopia.md

This `clorinde.toml` example demonstrates how to specify package metadata for the generated Clorinde crate, enabling separate publishing.

```toml
[manifest.package]
name = "my-clorinde-queries"
version = "0.1.0"
license = "MIT"
homepage = "https://github.com/furina/my-repo"
repository = "https://github.com/furina/my-repo"
publish = true
```

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

### Cornucopia Full Dependencies Example

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/introduction/migration_from_cornucopia.md

This TOML snippet shows the full dependencies required by Cornucopia for async operations, serialization, and extra types.

```toml
[dependencies]
# Required
postgres-types = { version = "*", features = ["derive"] }

# Async
cornucopia_async = { version = "*", features = ["with-serde_json-1"] }
tokio = { version = "*", features = ["full"] }
tokio-postgres = { version = "*", features = [
    "with-serde_json-1",
    "with-time-0_3",
    "with-uuid-1",
    "with-eui48-1",
] }utures = "*"
# Async connection pooling
deadpool-postgres = { version = "*" }

# Row serialization
serde = { version = "*", features = ["derive"] }

# Extra types
serde_json = "*"
time = "*"
uuid = "*"
eui48 = "*"
rust_decimal = { version = "*", features = ["db-postgres"] }
```

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

### Example Error Message: Unknown Field

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_clorinde/error_reporting.md

This example shows a typical error message received when a query attempts to use a field that does not exist. The message includes the error location, the problematic code, and suggestions for valid field names.

```text
× unknown field
   ╭─[queries/test.sql:1:1]
 1 │ --! author: (age?)
   ·              ─┬─
   ·               ╰── no field with this name was found
 2 │ SELECT * FROM author;
   ╰────
  help: use one of those names: id, name
```

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

### Derive Column Names at Runtime

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/configuration.md

This example shows how to map field metadata to user-facing headers or diagnostics by iterating over the `FieldMetadata` and extracting the `name` field. This avoids hardcoding column labels.

```rust
let headers: Vec<&str> = clorinde::queries::lock_info::LockInfo::field_metadata()
    .iter()
    .map(|m| m.name)
    .collect();
```

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

### Generated Rust Structs with Custom Attributes

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/writing_queries/type_annotations.md

Example of generated Rust structs, showing both the owned `Author` and borrowed `AuthorBorrowed` variants with applied derive traits and custom attributes.

```rust
#[derive(Default, serde::Deserialize, PartialEq)]
#[doc = "Represents an author in the system"]
#[derive(Clone)]
pub struct Author {
    pub name: String,
    pub age: Option<i32>,
    pub bio: String,
}

#[derive(Debug)]
#[cfg_attr(feature = "graphql", derive(async_graphql::SimpleObject))]
pub struct AuthorBorrowed<'a> {
    pub name: &'a str,
    pub age: Option<i32>,
    pub bio: &'a str,
}
```

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

### Example Custom PostgreSQL Type Generation

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/configuration.md

This Rust code demonstrates how derive traits and attributes configured in TOML are applied to a custom PostgreSQL enum. It includes `Default`, `serde::Deserialize`, and `#[repr(u8)]`.

```rust
#[repr(u8)]
#[derive(Debug, Clone, Copy, PartialEq, Eq, Default, serde::Deserialize)]
pub enum FontaineRegion {
    // ...
}
```

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

### Row Mapping with `map()`

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_queries/using_queries.md

Transform query results without intermediate allocation using the `map()` method. This example demonstrates mapping author data into a custom `CustomAuthor` struct with a transformed country field.

```rust
enum Country {
    Greece,
    TheRest
}

impl<'a> From<&'a str> for Country {
    fn from(s: &'a str) -> Self {
        if s == "Greece" {
            Self::Greece
        } else {
            Self::TheRest
        }
    }
}

struct CustomAuthor {
    full_name: String,
    country: Country,
    age: usize,
}

authors()
    .bind(&client)
    .map(|author| {
        let full_name = format!(
            "{}}, {}",
            author.last_name.to_uppercase(),
            author.first_name
        );
        let country = Country::from(author.country);
        CustomAuthor {
            full_name,
            country,
            age: author.age,
        }
    });
```

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

### Connect to a Live Database

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_clorinde/cli.md

Use the `live` command to connect to an arbitrary live PostgreSQL database. Provide the full connection URL.

```bash
clorinde live postgresql://user:pass@localhost/mydb
```

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

### Custom Directories with `clorinde live`

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Specify custom directories for queries and generated code output when connecting to a live PostgreSQL database.

```bash
clorinde live postgresql://user:pass@localhost/mydb \
  --queries db/queries \
  --destination db/generated
```

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

### Create Temporary Database with Schema

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_clorinde/cli.md

Use the `fresh` command to create a temporary database on an existing PostgreSQL server, load schema files, generate queries, and then drop the database. Requires a connection URL.

```bash
clorinde fresh schema.sql --url postgresql://user:pass@localhost
```

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

### Simple Static File Copy Configuration

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/configuration.md

Configure Clorinde to copy specified files to the root of the generated crate directory using a simple string array in the `static` TOML field.

```toml
static = ["LICENSE.txt", "build.rs"]
```

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

### Add Derive Traits to Generated Structs

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/writing_queries/type_annotations.md

Append derive traits after the type annotation using a colon to add them to the generated Rust struct. For example, `Default` and `serde::Deserialize`.

```sql
--: Author(age?) : Default, serde::Deserialize

--! authors : Author
SELECT name, age FROM authors;
```

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

### Using Ergonomic Parameters with String Bindings

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_queries/ergonomic_parameters.md

Demonstrates how to use ergonomic parameters with string types. Both `&str` and `String` can be passed to the `bind` method for the same query.

```rust
authors_by_first_name.bind(&client, &"John").all(); // This works
authors_by_first_name.bind(&client, &String::from("John")).all(); // This also works
```

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

### Custom DB Name and Search Path with `clorinde fresh`

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Specify a custom temporary database name and search path for `clorinde fresh`.

```bash
clorinde fresh schema.sql \
  --url postgresql://user:pass@localhost \
  --db-name my_temp_db \
  --search-path public,custom_schema
```

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

### Generate Schema with Container Management

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_clorinde/cli.md

Use the `schema` command to generate queries against schema files, with Clorinde managing a temporary database container. Requires Docker or Podman.

```bash
clorinde schema schema.sql
```

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

### Execute SQL Queries with Database Client

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Import generated query functions and use `.bind()` with a database client. Methods like `all()`, `one()`, `opt()`, and `iter()` retrieve rows. Void-return queries execute immediately on `.bind()`.

```rust
// Cargo.toml: clorinde = { path = "./clorinde" }
use clorinde::queries::
    module_1::insert_book,
    module_2::{author_name_by_id, authors, books};
use clorinde::deadpool_postgres::{Config, Pool, Runtime};
use clorinde::tokio_postgres::NoTls;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    // Set up a deadpool-postgres connection pool
    let mut cfg = Config::new();
    cfg.user = Some("postgres".into());
    cfg.password = Some("postgres".into());
    cfg.host = Some("127.0.0.1".into());
    cfg.port = Some(5432);
    cfg.dbname = Some("mydb".into());
    let pool = cfg.create_pool(Some(Runtime::Tokio1), NoTls)?;
    let client = pool.get().await?;

    // .all() collects all rows into a Vec
    let all_authors = authors().bind(&client).all().await?;
    dbg!(&all_authors);

    // .opt() returns Option<Row> (error if more than one row)
    let maybe_author = author_name_by_id().bind(&client, &42_i32).opt().await?;
    dbg!(maybe_author);

    // .one() returns exactly one row (error if zero or more than one)
    let one_author = author_name_by_id().bind(&client, &1_i32).one().await?;
    dbg!(one_author);

    // Void-return queries (INSERT/UPDATE/DELETE) execute immediately on .bind()
    insert_book().bind(&client, &"Crime and Punishment").await?;

    // Transactions — pass the transaction just like a regular client
    let tx = client.transaction().await?;
    insert_book().bind(&tx, &"The Brothers Karamazov").await?;
    let uppercase = books()
        .bind(&tx)
        .map(|title| title.to_uppercase())
        .all()
        .await?;
    tx.commit().await?;
    dbg!(uppercase);

    Ok(())
}
```

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

### Fresh Command with Custom Database Name and Search Path

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_clorinde/cli.md

Customize the temporary database name and search path when using the `fresh` command. This allows for more specific temporary database configurations.

```bash
clorinde fresh schema.sql --url postgresql://user:pass@localhost \
  --db-name my_temp_db \
  --search-path public,custom_schema
```

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

### Use Podman with `clorinde schema`

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Specify Podman as the container runtime instead of Docker for `clorinde schema`.

```bash
clorinde schema schema.sql --podman
```

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

### Custom Queries and Destination with `clorinde schema`

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Specify custom directories for queries and generated code output using `clorinde schema`.

```bash
clorinde schema schema.sql --queries db/queries --destination db/generated
```

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

### Executing Queries with Default `bind()` Behavior

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_queries/using_queries.md

Execute a query using the `bind()` method followed by `all().await?`. This approach intelligently handles statement preparation, caching when supported by the client (e.g., `deadpool-postgres`), or executing without preparation for one-off queries.

```rust
authors().bind(&client, Some("Greece")).all().await?;
```

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

### Generate Sync Code with `clorinde live`

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Generate synchronous Rust code by connecting to an existing PostgreSQL instance.

```bash
clorinde live postgresql://user:pass@localhost/mydb --sync
```

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

### Programmatic Configuration with `ConfigBuilder`

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Configure code generation options programmatically using `Config::builder()`. This allows fine-grained control over generated crate names, output directories, SQL query locations, and type mappings.

```rust
use clorinde::config::{Config, StaticFile, TypeMapping, Types};

// Programmatic builder
let config = Config::builder()
    .name("my_project_queries")         // generated crate name
    .destination("generated/queries")   // output directory
    .queries("db/queries")              // SQL queries directory
    .r#async(true)                      // generate async code (default)
    .sync(false)                        // also generate sync code
    .generate_field_metadata(true)      // enable FieldMetadata on row structs
    .params_only(false)                 // if true, hide bind(), force params()
    .add_derive_trait("serde::Serialize")
    .add_type_mapping(
        "pg_catalog.date",
        TypeMapping::Simple("my_crate::Date".to_string()),
    )
    .add_static_file(StaticFile::Simple("LICENSE".into()))
    .build();
```

```rust
// Load from file
let config = Config::from_file("clorinde.toml").unwrap();
```

```rust
// Load from file and further modify
let config = Config::builder_from_file("clorinde.toml")
    .unwrap()
    .destination("other/output")
    .build();
```

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

### Advanced Static File Configuration

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/configuration.md

Utilize a detailed configuration format for static files, allowing renaming, placing files in subdirectories, or creating hard links instead of copying.

```toml
static = [
    # Simple copy (copies to root with original filename)
    "README.md",

    # Rename file during copy
    { path = "config.template.toml", destination = "config.toml" },

    # Place file in subdirectory
    { path = "assets/logo.png", destination = "static/images/logo.png" },

    # Hard link instead of copy (saves disk space for large files)
    { path = "large_asset.bin", hard-link = true },

    # Combine renaming with hard linking
    { path = "data.json", destination = "resources/app_data.json", hard-link = true }
]
```

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

### TOML Configuration for Clorinde

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Configure Clorinde using a `clorinde.toml` file for options like SQL query locations, output directories, and type mappings. This provides a declarative way to manage generation settings.

```toml
# clorinde.toml

queries = "db/queries"
destination = "db/generated"
generate-field-metadata = true
params-only = false
ignore-underscore-files = true
container-image = "docker.io/library/postgres:16"
container-wait = 500

# Use workspace deps from the root Cargo.toml
use-workspace-deps = true

[style]
enum-variant-camel-case = true

[types]
derive-traits = ["serde::Serialize", "serde::Deserialize"]

# Per-type derive traits and attributes for custom PostgreSQL types
[types.custom.spongebob_character]
derive-traits = ["Hash"]
attributes = ["repr(u8)"]

# Custom type mappings (override or extend default mappings)
[types.mapping]
"pg_catalog.date" = "my_crate::Date"

# Detailed type mapping with borrowed type and attributes
[types.mapping."pg_catalog.varchar"]
rust-type = "my_crate::CustomString"
borrowed-type = "my_crate::CustomStringRef<'a>"
is-copy = false
attributes = ['serde(skip_serializing_if = "Option::is_none")']

# Generated crate's Cargo.toml manifest
[manifest.package]
name = "my_project_queries"
version = "1.0.0"
edition = "2021"

[manifest.dependencies]
serde = { version = "1.0", features = ["derive"] }
my_types = { path = "../my_types" }

# Static files to copy into the generated crate directory
static = [
    "LICENSE",
    { path = "template.env", destination = ".env.example" },
    { path = "large_data.bin", hard-link = true },
]
```

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

### Run Tests and Ensure Code Quality

Source: https://github.com/halcyonnouveau/clorinde/blob/main/CONTRIBUTING.md

Before submitting changes, ensure all tests pass, code is formatted, and clippy warnings are addressed.

```bash
cargo test --all
```

```bash
cargo fmt
```

```bash
cargo clippy
```

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

### Importing Generated Queries

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_queries/using_queries.md

Import the necessary query items from your generated Clorinde crate. This is the first step before using any generated queries.

```rust
use clorinde::queries::authors;
```

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

### Use Multiple Schema Files with `clorinde schema`

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Generate code from multiple SQL schema files using the `clorinde schema` command.

```bash
clorinde schema schema1.sql schema2.sql
```

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

### Explicitly Preparing Statements with `prepare()`

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_queries/using_queries.md

For queries executed multiple times with different parameters, explicitly prepare the statement using `prepare().await?`. This ensures the statement is prepared once and reused, offering better performance in specific scenarios like loops or when statement caching is not handled by the connection pool.

```rust
let prepared = authors().prepare(&client).await?;

// Reuse the prepared statement multiple times
for country in ["Greece", "Italy", "Spain"] {
    let results = prepared.bind(&client, Some(country)).all().await?;
    // Process results...
}
```

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

### Define PostgreSQL Queries

Source: https://github.com/halcyonnouveau/clorinde/blob/main/README.md

Write PostgreSQL queries with annotations and named parameters in a .sql file. Use `--! <query_name>` to define query identifiers.

```sql
-- queries/authors.sql

--! insert_author
INSERT INTO authors
    (first_name, last_name, country)
VALUES
    (:first_name, :last_name, :country);

--! authors
SELECT first_name, last_name, country FROM authors;

```

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

### Enable Query Field Metadata

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/configuration.md

Enable the opt-in feature for generating lightweight metadata about each result-row struct by setting `generate-field-metadata` to `true` in your `clorinde.toml`.

```toml
generate-field-metadata = true
```

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

### Programmatic API: Generating Code from a Live Database

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Use Clorinde as a library in a `build.rs` script to generate code from a live database connection. This allows for dynamic schema introspection during the build process.

```rust
// build.rs
use clorinde::{Error, config::Config, conn, gen_live};

fn main() -> Result<(), Error> {
    let config = Config::builder()
        .name("my_queries")
        .destination("src/generated")
        .queries("db/queries")
        .r#async(true)
        .sync(false)
        .build();

    let client = conn::from_url("postgresql://user:pass@localhost/mydb")?;
    gen_live(&client, config)?;
    Ok(())
}
```

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

### Configure Package Manifest

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/configuration.md

Customize the `Cargo.toml` for the generated crate, including package metadata and dependencies. Clorinde merges these with auto-generated PostgreSQL dependencies.

```toml
[manifest.package]
name = "furinapp-queries"
version = "1.0.0"
description = "Today I wanted to eat a *quaso*."
license = "MIT"
edition = "2021"

[manifest.dependencies]
serde = { version = "1.0", features = ["derive"] }
my_custom_types = { path = "../types" }
```

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

### Use Podman as Container Manager

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_clorinde/cli.md

Specify Podman as the container manager by using the `-p` or `--podman` flag with commands that manage containers, such as `schema`.

```bash
# Example using schema command with --podman flag
clorinde schema schema.sql --podman
```

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

### Fetching All Rows with `all()`

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_queries/using_queries.md

Retrieve all rows from a query result and collect them into a `Vec` using `all().await?`.

```rust
authors().bind(&client).all().await?;
```

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

### Building Query Objects with `bind()`

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_queries/using_queries.md

Construct a query object by calling the query function and binding parameters directly. This is useful for queries with a few obvious parameters.

```rust
authors().bind(&client, Some("Greece"));
```

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

### Generate Sync Code with `clorinde schema`

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Generate synchronous Rust code from schema.sql using a managed PostgreSQL container. Requires Docker or Podman.

```bash
clorinde schema schema.sql --sync
```

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

### Use Generated Rust Code

Source: https://github.com/halcyonnouveau/clorinde/blob/main/README.md

Import and utilize the type-safe query interfaces generated by Clorinde in your Rust project. Use the `bind` method for parameter binding and `all` to fetch results.

```rust
use clorinde::queries::authors::{authors, insert_author};

insert_author.bind(&client, "Agatha", "Christie", "England");

let all_authors = authors().bind(&client).all();

for author in all_authors {
  println!("[{}] {},
    {}",
    author.country,
    author.last_name.to_uppercase(),
    author.first_name
  )
}

```

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

### Explicit Statement Preparation for Hot Paths

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

For frequently executed queries, explicitly prepare the statement once using `prepare()` and then reuse it multiple times with `bind()` to avoid re-preparing.

```rust
// Prepare once
let stmt = author_name_by_id().prepare(&client).await?;

// Execute many times without re-preparing
for id in 1_i32..=100 {
    if let Some(name) = stmt.bind(&client, &id).opt().await? {
        println!(ירת{id}: {name:?});
    }
}
```

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

### Query Documentation Comments

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/writing_queries/query_annotations.md

Adds documentation comments to generated Rust code using '---' after the query annotation. These comments describe the query's purpose and parameters.

```sql
--! authors_from_country
--- Finds all authors from a specific country.
--- Parameters:
---   country: The nationality to filter by
SELECT id, name, age
FROM authors
WHERE authors.nationality = :country;
```

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

### Nullable Parameters and Columns

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/writing_queries/query_annotations.md

Specifies nullable parameters and columns using the '?' syntax. '(country?)' indicates a nullable parameter, and '(age?)' indicates a nullable return column.

```sql
--! authors_from_country (country?) : (age?)
SELECT id, name, age
FROM authors
WHERE authors.nationality = :country;
```

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

### Integrate with Workspace Dependencies

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/configuration.md

Enable integration with workspace dependency management. This option can point to the current directory's `Cargo.toml` or a specific path, ensuring consistent dependency versions.

```toml
# Use workspace dependencies from the current directory's Cargo.toml
use-workspace-deps = true

# Use workspace dependencies from a specific Cargo.toml
use-workspace-deps = "../../Cargo.toml"
```

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

### Keep Temporary DB with `clorinde fresh`

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Keep the temporary database after code generation for debugging purposes using `clorinde fresh`.

```bash
clorinde fresh schema.sql --url postgresql://user:pass@localhost --keep-db
```

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

### Generate Code with `gen_fresh()`

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Use `gen_fresh()` to create a temporary PostgreSQL database for code generation. The database is automatically dropped after use. Ensure the server URL does not include a database name.

```rust
use clorinde::{Error, config::Config, gen_fresh};

fn main() -> Result<(), Error> {
    let config = Config::builder()
        .name("my_queries")
        .destination("my_queries")
        .build();

    gen_fresh(
        "postgresql://user:pass@localhost",  // server URL (no dbname)
        "clorinde_temp_build",              // temporary database name
        &["schema.sql"],                    // schema files
        Some("public"),                     // optional search path
        false,                              // keep_db: drop after generation
        config,
    )?;
    Ok(())
}
```

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

### Synchronous Database Usage with Clorinde

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Generate synchronous code using the `--sync` CLI flag or `sync(true)` API option. This allows the use of `postgres::Client` instead of `tokio_postgres::Client` and avoids the need for `.await`.

```bash
clorinde live postgresql://user:pass@localhost/mydb --sync
```

```rust
use clorinde::postgres::{Client, Config, NoTls};
use clorinde::queries::module_2::{authors, books};

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let mut client = Config::new()
        .user("postgres")
        .password("postgres")
        .host("127.0.0.1")
        .port(5432)
        .dbname("mydb")
        .connect(NoTls)?;

    // Sync: no .await needed
    let all_authors = authors().bind(&mut client).all()?;
    dbg!(all_authors);

    let mut tx = client.transaction()?;
    let upper_books = books()
        .bind(&mut tx)
        .map(|t| t.to_uppercase())
        .all()?;
    tx.commit()?;
    dbg!(upper_books);

    Ok(())
}
```

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

### Fetching Single Row with `opt()`

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_queries/using_queries.md

Retrieve zero or one row from a query result using `opt().await?`. An error is returned if more than one row is found.

```rust
author_by_id().bind(&client, &0).opt().await?;
```

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

### Map PostgreSQL Types to Rust Types

Source: https://github.com/halcyonnouveau/clorinde/blob/main/examples/custom_types/README.md

Configure the mapping between PostgreSQL schema types and their corresponding Rust types in Clorinde. Supports simple mappings and detailed configurations with options like `is-copy` and `attributes`.

```toml
[types.mapping]
# Simple mapping
"public.element" = "db_types::element::Element"

# Detailed mapping with options
[types.mapping."pg_catalog.date"]
rust-type = "db_types::date::Date"
is-copy = false
attributes = ['serde(skip_serializing_if = "Option::is_none")']
```

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

### Generate Synchronous Code

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_clorinde/cli.md

Use the `--sync` flag with any Clorinde command to generate synchronous code instead of the default asynchronous code.

```bash
# Example using schema command with --sync flag
clorinde schema schema.sql --sync
```

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

### Building Query Objects with `params()`

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_queries/using_queries.md

Build a query object using the generated parameter struct for more explicit parameter binding. Requires importing the `Params` trait.

```rust
use clorinde::{
    client::Params,
    queries::{authors, AuthorsParams}
};

authors().params(
    &client,
    AuthorsParams {
        country: Some("Greece")
    }
);
```

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

### Custom Type Mappings for `time` Crate

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/introduction/migration_from_cornucopia.md

This TOML configuration shows how to map PostgreSQL types to the `time` crate's types in Clorinde, if you wish to avoid using `chrono`.

```toml
[types.mapping]
"pg_catalog.timestamp" = "time::PrimitiveDateTime"
"pg_catalog.timestamptz" = "time::OffsetDateTime"
"pg_catalog.time" = "time::Time"
"pg_catalog.date" = "time::Date"

[manifest.dependencies]
time = { version = "0.3", features = ["serde"] }
# enable the time feature of postgres and tokio-postgres
postgres = { version = "0.19", features = [
    "with-time_0_3",
    "with-serde_json-1",
] }
tokio-postgres = { version = "0.7", features = [
    "with-time_0_3",
    "with-serde_json-1",
] }
```

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

### Map Custom PostgreSQL Types to Rust

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/configuration.md

Define custom mappings between PostgreSQL types and Rust types. Ensure necessary dependencies for custom types are listed in `[manifest.dependencies]`.

```toml
[manifest.dependencies]
# Dependencies required for custom type mappings
ctypes = { path = "../ctypes" }
postgres_range = { version = "0.11.1", features = ["with-chrono-0_4"] }

[types.mapping]
# Simple mapping: just specify the Rust type
"pg_catalog.date" = "ctypes::date::Date"
"pg_catalog.tstzrange" = "postgres_range::Range<chrono::DateTime<chrono::FixedOffset>>"
```

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

### Programmatic API: Generating Code with Managed Database

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Generate code using a Clorinde-managed Docker/Podman container, suitable for CI pipelines where a persistent database is not available. This method ensures a consistent build environment.

```rust
// build.rs
use clorinde::{Error, config::Config, gen_managed};

fn main() -> Result<(), Error> {
    println!("cargo:rerun-if-changed=db/queries");
    println!("cargo:rerun-if-changed=schema.sql");

    let config = Config::builder()
        .name("my_queries")
        .destination("my_queries")
        .queries("db/queries")
        .build();

    gen_managed(&["schema.sql"], config)?;
    Ok(())
}
```

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

### SQL Query with Inline Parameter Struct

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

This SQL query defines an inline parameter struct `AuthorNameStartingWithParams` which is used to pass parameters to the query.

```sql
--! author_name_starting_with AuthorNameStartingWithParams()
SELECT author_id, name, book_id, title
FROM book_authors
INNER JOIN authors ON authors.id = book_authors.author_id
INNER JOIN books   ON books.id   = book_authors.book_id
WHERE authors.name LIKE CONCAT(:start_str::text, '%');
```

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

### Basic Query Annotation

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/writing_queries/query_annotations.md

Defines a query named 'authors_from_country' with a single parameter 'country'.

```sql
--! authors_from_country
SELECT id, name, age
FROM authors
WHERE authors.nationality = :country;
```

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

### Add Clorinde API to Cargo.toml

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/introduction/installation.md

Include this line in your project's Cargo.toml file to add Clorinde as a dependency. Choose the desired version for your project.

```toml
clorinde = "..." # choose the desired version
```

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

### Add Clorinde as a Library Dependency

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Add Clorinde as a library dependency in your Cargo.toml for programmatic use in build scripts.

```toml
# clorinde = "1.4.1"
```

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

### Clorinde CLI Help Command

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_clorinde/cli.md

Access the help message for complete information on Clorinde CLI commands and options.

```bash
clorinde --help
```

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

### Detailed Custom Type Mapping Syntax

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/configuration.md

Configure advanced options for type mappings, including the Rust type, copy behavior, and attributes for owned and borrowed structs. The `rust-type` is required.

```toml
[types.mapping."pg_catalog.date"]
rust-type = "ctypes::date::Date"
is-copy = false
attributes = ['serde(skip_serializing_if = "Option::is_none")']
attributes-borrowed = []
```

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

### Fetching Rows with `iter()`

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_queries/using_queries.md

Retrieve rows from a query result as an iterator using `iter().await?` and then collect them into a `Vec`. This is functionally equivalent to using `all()`.

```rust
authors().bind(&client).iter().await?.collect::<Vec<_>>();
```

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

### Add Generated Crate to Cargo.toml

Source: https://github.com/halcyonnouveau/clorinde/blob/main/README.md

Include the generated Clorinde crate in your project's Cargo.toml file by specifying its local path.

```toml
clorinde = { path = "./clorinde" }
```

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

### Use Named Parameters Instead of Indexed

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/writing_queries/writing_queries.md

Queries MUST use named parameters (like :name) instead of indexed ones like $3.

```sql
```admonish warning
Queries **MUST** use named parameters (like `:name`) instead of indexed ones like `$3`.
```
```

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

### Minimal Query Annotation

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Annotate a SQL query with a name. Clorinde infers parameter and column types.

```sql
-- queries/authors.sql

-- Minimal annotation: just a name
--! authors
SELECT id, name, country FROM authors;
```

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

### Define SQL Types with Annotations

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Use `--:` to define named row structs that can be shared across multiple queries. Only nullable columns need explicit declaration; others are inferred as non-null.

```sql
-- queries/authors.sql

-- Define a named type, reuse it for multiple queries
--: Author(age?)

--! authors : Author
SELECT name, age FROM authors;

--! authors_from_country (country?) : Author
SELECT name, age FROM authors WHERE nationality = :country;

-- Type with derive traits
--: Author(age?) : Default, serde::Deserialize

-- Inline type (no separate --: declaration needed, use parens)
--! authors_inline : Author()
SELECT name, age FROM authors;

-- Type with custom attributes for owned and borrowed structs
--: Author(age?, bio) : serde::Deserialize
--# doc = "Represents an author in the system"
--& cfg_attr(feature = "graphql", derive(async_graphql::SimpleObject))

--! authors_detailed : Author
SELECT name, age, bio FROM authors;
```

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

### Use Named Parameter Structs for Queries

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

For queries with multiple parameters, use the generated parameter struct with the `Params` trait for more explicit and less error-prone code. The `params()` method accepts a reference to the generated parameter struct.

```rust
use clorinde::
    client::Params,
    queries::module_2::{AuthorNameStartingWithParams, author_name_starting_with};

// params() accepts a reference to the generated parameter struct
let results = author_name_starting_with()
    .params(&client, &AuthorNameStartingWithParams { start_str: "Jo" })
    .all()
    .await?;
// => Vec of rows for authors whose name starts with "Jo"
dbg!(results);
```

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

### Query Doc Comment Annotation

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Add documentation comments to a SQL query using `---`. These comments are converted to Rust doc comments in the generated code.

```sql
-- Query doc comment (becomes Rust doc comment)
--! author_by_id
--- Returns a single author by their primary key.
--- Returns NULL if no author with the given id exists.
SELECT id, name FROM authors WHERE id = :id;
```

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

### Nullable Parameter and Return Column Annotation

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Annotate a SQL query with a name, specifying nullable parameters and return columns. Uses `?` for nullable parameters and `:param_name` for named parameters.

```sql
-- With nullable parameter and nullable return column
--! authors_from_country (country?) : (age?)
SELECT id, name, age
FROM authors
WHERE authors.nationality = :country;
```

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

### Configure Custom PostgreSQL Types

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/configuration.md

Use the `types.custom` section to apply specific derive traits and attributes to custom PostgreSQL types like enums and composites. This allows for granular control over type generation.

```toml
[types]
# Applied to all generated structs and postgres types
derive-traits = ["Default"]

# Configuration for specific custom postgres types
[types.custom.fontaine_region]
derive-traits = ["serde::Deserialize"]
attributes = ["repr(u8)"]
```

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

### Specify Custom Type Dependencies in Clorinde

Source: https://github.com/halcyonnouveau/clorinde/blob/main/examples/custom_types/README.md

Define local or remote crates that provide custom types for Clorinde. Supports standard Cargo dependency specifications like path, version, features, and optional dependencies.

```toml
[manifest.dependencies]
# Local crate with a relative path
ctypes = { path = "../ctypes" }

# Crate from crates.io with a specific version
custom_types = "1.0.0"

# Crate with additional configuration
types_with_features = { version = "2.0", features = ["date", "time"] }

# Complete example with all options
full_example = {
    version = "1.2.3",
    path = "../local_types",
    features = ["custom_types"],
    default-features = false,
    optional = true
}
```

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

### Fetching Exactly One Row with `one()`

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_queries/using_queries.md

Retrieve exactly one row from a query result using `one().await?`. An error is returned if zero or more than one row is found.

```rust
author_by_id().bind(&client, &0).one().await?;
```

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

### Add Clorinde to Cargo.toml

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/using_clorinde/using_clorinde.md

Include the Clorinde crate in your project's dependencies by specifying its path. This allows you to import generated query items.

```toml
[dependencies]
clorinde = { path = "clorinde" }
```

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

### Accessing Field Metadata

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/configuration.md

Demonstrates how to access the generated `FieldMetadata` for a row struct. This metadata includes the field's name, Rust type, and PostgreSQL type.

```rust
let meta: &'static [clorinde::types::FieldMetadata] = 
    clorinde::queries::lock_info::LockInfo::field_metadata();
```

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

### Configure Borrowed Type Mappings

Source: https://github.com/halcyonnouveau/clorinde/blob/main/examples/custom_types/README.md

Specify separate owned and borrowed Rust types for a PostgreSQL type, useful for types like `String` and `&str`. Ensures correct type usage in owned and borrowed structs.

```toml
[types.mapping."pg_catalog.varchar"]
rust-type = "db_types::string::CustomString"
borrowed-type = "db_types::string::CustomStringRef<'a>"
is-copy = false
```

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

### Map Query Results to Custom Structs and Simple Types

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Transform query results inline using `.map()` without creating intermediate allocations. This method returns another query object, allowing subsequent retrieval methods to be applied.

```rust
use clorinde::queries::module_2::{authors, books};

// Map rows to a custom struct
#[derive(Debug)]
struct AuthorDisplay {
    label: String,
}

let labels = authors()
    .bind(&client)
    .map(|row| AuthorDisplay {
        label: format!( "{}, {} ({})", row.name, row.id, row.country),
    })
    .all()
    .await?;

// Map to a simple type
let titles_upper: Vec<String> = books()
    .bind(&client)
    .map(|title| title.to_uppercase())
    .all()
    .await?;
```

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

### Nullable Composite and Array Fields Annotation

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Annotate a query to handle nullable composite types and arrays. Uses `?.` for nullable fields within composites and `?[]` for nullable arrays.

```sql
-- Nullable composite and array fields
--! example_query : (compos?.some_field?, arr?[?])
SELECT compos, arr FROM example;
```

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

### Custom Attributes for Query Functions

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/writing_queries/query_annotations.md

Applies custom Rust attributes like 'deprecated' or 'allow' to generated query functions using '--#' syntax. These attributes must follow the query annotation.

```sql
--! authors_from_country
--# deprecated = "Use authors_from_country_v2 instead"
--# allow(dead_code)
SELECT id, name, age
FROM authors
WHERE authors.nationality = :country;
```

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

### Insert Author Query Annotation

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Annotate an INSERT statement using named parameters for values.

```sql
-- Named parameters use the `:param_name` syntax
--! insert_author
INSERT INTO authors (first_name, last_name, country)
VALUES (:first_name, :last_name, :country);
```

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

### Ergonomic Parameter Types for Database Queries

Source: https://context7.com/halcyonnouveau/clorinde/llms.txt

Clorinde generates umbrella traits that allow string-like, bytes, JSON, and array parameters to accept multiple concrete types without explicit conversion.

```rust
use clorinde::queries::module_2::author_name_starting_with;

// All of the following are valid for a TEXT parameter:
author_name_by_first_name().bind(&client, &"Alice").all().await?;
author_name_by_first_name().bind(&client, &String::from("Alice")).all().await?;
author_name_by_first_name().bind(&client, &std::borrow::Cow::Borrowed("Alice")).all().await?;

// For BYTEA parameters: &[u8] or Vec<u8>
// For JSON/JSONB parameters: serde_json::Value or postgres_types::Json<T>
// For array parameters: &[T], Vec<T>, or IterSql wrapper
use clorinde::types::IterSql;
let ids = vec![1_i32, 2, 3];
select_by_ids().bind(&client, &ids.as_slice()).all().await?;
```

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

### Apply Custom Attributes to Generated Structs

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/writing_queries/type_annotations.md

Use `--#` for owned struct attributes and `--&` for borrowed struct attributes. These are applied after the type annotation and are useful for documentation or conditional compilation.

```sql
--: Author(age?, bio) : serde::Deserialize
--# doc = "Represents an author in the system"
--& cfg_attr(feature = "graphql", derive(async_graphql::SimpleObject))

--! authors : Author
SELECT name, age, bio FROM authors;
```

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

### Specify Borrowed Type Mappings

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/configuration.md

Define separate owned and borrowed Rust types for a PostgreSQL type. The borrowed type must implement `Into<OwnedType>` for conversion.

```toml
[types.mapping."pg_catalog.varchar"]
rust-type = "my_crate::CustomString"
borrowed-type = "my_crate::CustomStringRef<'a>"
is-copy = false
```

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

### Nullable Composites and Arrays

Source: https://github.com/halcyonnouveau/clorinde/blob/main/docs/src/writing_queries/query_annotations.md

Granularly controls nullity for fields within composite types and elements within arrays. '(compos?.some_field?)' marks both the field and its parent as nullable, while 'arr?[?]' marks array elements as nullable.

```sql
--! example_query : (compos?.some_field?, arr?[?])
SELECT compos, arr
FROM example
```