Introduction

Writing one SQL query is rarely the hard part. The work piles up around it: opening connections, passing parameters, turning rows into Rust values, and repeating the same column names across the application.

Canyon-SQL uses a Rust model to generate much of that routine work. You still own the database schema and can still write SQL when it is the clearest tool.

There are three ways to work with data in Canyon:

• Generated operations handle common reads and writes on a mapped Rust struct.
• The query builder adds filters, joins, ordering, and projections.
• A connection lets you send SQL directly when neither of those fits.

Canyon is asynchronous and supports PostgreSQL, MySQL, and SQL Server. You can enable more than one backend and configure several named datasources. The first active datasource is the default; an explicit connection or datasource name lets an operation use another one.

How to read this book

We'll connect to PostgreSQL, define a Team, and use it to read and write rows. Once that works, we'll add relationships and build queries that go beyond the generated methods. Later chapters cover errors, direct SQL, and repository adapters. Examples target Canyon-SQL 0.5.1.

Before you start: Create the tables with your usual schema tool. Canyon's migrations feature is experimental and incomplete; none of the examples depend on it.

New to async Rust? The Async Book is useful background. You need not know how Canyon's procedural macros are implemented, but their generated code is still Rust: traits must be in scope, types must match, and a database error is not an absent row.

Canyon-SQL is MIT licensed. If an example no longer matches the code, open an issue or a pull request in the book repository; the Canyon-SQL repository holds the implementation.

Getting started

We'll use PostgreSQL to get the first query running. MySQL and SQL Server follow the same path: enable the matching Cargo feature and configure a datasource for that backend.

To get a query running, you need:

1. A project with a SQL backend enabled.
2. A datasource in canyon.toml.
3. A table that matches the Rust model.

Once those three pieces are in place, call Canyon::init().await? to open the connection pools. Then Team::find_all().await? can read your table.

Already have a database? Use its connection details and an existing table. You do not need Canyon's experimental migrations feature.

Install Canyon

Add Canyon to your application's Cargo.toml. Choose at least one SQL backend; we'll use PostgreSQL:

[dependencies]
canyon_sql = { version = "0.5.1", features = ["postgres"] }
tokio = { version = "1", features = ["macros", "rt-multi-thread"] }

The other choices are mysql and mssql. If one application talks to several kinds of database, enable more than one:

canyon_sql = { version = "0.5.1", features = ["postgres", "mysql", "mssql"] }

At least one backend is required. Canyon has no default SQL backend; omitting all three features produces a compile-time error.

Start the runtime and Canyon

The explicit startup form gives your application a chance to report a configuration or connection error:

use canyon_sql::{core::Canyon, CanyonResult};

#[tokio::main]
async fn main() -> CanyonResult<()> {
    Canyon::init().await?;
    // Queries can run here.
    Ok(())
}

Canyon::init() reads the configuration and opens the pools. Calling it again does not replace an initialized instance. Run it before a generated query method.

A shorter main: #[canyon_sql::main] creates the Tokio runtime and initializes Canyon before the function body. Use it only on a function named main. Unlike the explicit form above, it panics if initialization fails.

Next, configure a datasource and prepare a table for the first query.

Configure datasources

A datasource gives a name to a database connection. Put its settings in a TOML file whose name starts with canyon and ends with .toml.

Canyon searches the current working directory and its immediate descendants, up to depth two. Keep one matching file in that area so you know which configuration it will find.

Your first datasource

For the PostgreSQL example, put this in canyon.toml where you run the application:

[canyon_sql]

[[canyon_sql.datasources]]
name = "main"

[canyon_sql.datasources.auth]
postgresql = { basic = { username = "postgres", password = "postgres" } }

[canyon_sql.datasources.properties]
host = "localhost"
port = 5432
db_name = "app"

The first active datasource becomes the default for calls such as Team::find_all(). Give each additional datasource a different name; an operation's _with form selects one by name.

Backend features matter here too: A datasource for a backend that was not enabled at compile time is ignored. If none remain, there is no usable default connection.

All three backends currently use basic username/password authentication. The keys and standard ports are:

host and db_name are required; port is optional.

Add another database

A second datasource follows the same shape. For example, a MySQL connection alongside main could be declared as:

[[canyon_sql.datasources]]
name = "analytics"

[canyon_sql.datasources.auth]
mysql = { basic = { username = "reporter", password = "replace-me" } }

[canyon_sql.datasources.properties]
host = "localhost"
port = 3306
db_name = "analytics"

For SQL Server, use sqlserver as the auth key, supply its credentials and database name, and choose a TLS policy. Both examples require the matching Cargo feature.

After initialization, you can see which datasources are active:

#![allow(unused)]
fn main() {
use canyon_sql::core::Canyon;

let canyon = Canyon::instance()?;
for datasource in canyon.datasources() {
    println!("{}: {:?}", datasource.name, datasource.get_db_type());
}
}

Use get_connection("analytics") to select by name or get_default_connection() for the first active datasource. An unknown name returns an error.

Connection pools

You can tune each connection pool separately:

[canyon_sql.datasources.properties.pool]
min_size = 2
max_size = 10

The example shows the defaults. max_size must be positive and at least min_size; invalid bounds produce a configuration error.

SQL Server TLS

Set mssql_tls under [canyon_sql.datasources.properties]:

• "required" (the default) encrypts and validates the server certificate.
• "trust_server_certificate" encrypts without validating the certificate.
• "disabled" turns encryption off.

Only for local tests: The repository's Docker fixture uses "disabled". For a server outside a controlled test environment, use a valid certificate and "required".

Credentials are application secrets

Keep credentials private: The values above are examples. A real canyon.toml contains credentials; do not publish it or commit production passwords. Arrange file permissions and deployment accordingly.

If startup fails, the error tells you where to look:

• Configuration means Canyon could not find, read, or parse the file, or rejected one of its settings.
• Connection means it read the configuration but could not open a datasource.

We'll handle those errors in Handle errors.

Prepare a database

The first model in this book will read from a teams table in the PostgreSQL database named app. Create it before running the Rust example:

CREATE TABLE teams (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

The next chapter maps these columns to Team.id: i64 and Team.name: String. The table is plural, while Canyon's default name for Team is team, so the model will use #[canyon_entity(table_name = "teams")].

Use your usual schema tool or SQL client to create the table. Canyon's normal CRUD path reads and writes existing tables; it does not create them. If you use MySQL or SQL Server instead, adapt the identity-column syntax and configure that backend's datasource.

No migration setup is required. Canyon's migrations feature is experimental; leave it disabled for this example. Contributors who want Canyon's Docker test databases can follow Test and contribute.

Entities and mapping

A Canyon entity starts as an ordinary Rust struct. Its fields tell the mapper what to read from a row. The database table still has to exist, and its columns must match the model.

For the teams table from the previous chapter:

#![allow(unused)]
fn main() {
use canyon_sql::macros::{canyon_entity, CanyonMapper, Crud, Fields};

#[derive(Debug, Fields, Crud, CanyonMapper)]
#[canyon_entity(table_name = "teams")]
pub struct Team {
    #[primary_key]
    pub id: i64,
    pub name: String,
}
}

There are four Canyon pieces in this example:

• #[canyon_entity] supplies the table name and processes field markers such as #[primary_key]. It also registers this struct for the experimental migrations machinery.
• CanyonMapper converts driver rows to Team.
• Crud generates read, insert, update, and delete operations.
• Fields generates names and typed values for the query builder. Ordinary find_all() or insert() calls do not need it.

When do you need #[canyon_entity]?

The derives can infer team from Team without an entity attribute. If a model has no Canyon field markers and its table follows that naming convention, you can omit #[canyon_entity].

Use #[canyon_entity] when:

• The physical table or schema differs from the inferred name: pass table_name = "..." or schema = "...".
• You annotate fields with Canyon markers such as #[primary_key] or #[foreign_key(...)]. The current attribute macro processes these markers; keep it on models that use them.

Registration for experimental migrations is a side effect, not a reason to enable that feature for normal CRUD. Query execution uses the metadata generated by the derives; it does not look up a runtime registry of entities.

Table names and schemas

Without an explicit name, Canyon derives a snake_case table name from the Rust type: TournamentDetails maps to tournament_details. Existing schemas do not always follow that rule. Use explicit metadata when they do not:

#![allow(unused)]
fn main() {
#[canyon_entity(table_name = "tournament_entries", schema = "public")]
}

Generated CRUD and relationship operations on the model use that name. Repository adapters have a current limitation with custom table names.

Primary keys

#[primary_key] identifies the field used by key-based reads, updates, and deletes. For a numeric key, it is treated as database-generated and auto-incrementing unless you say otherwise. After a successful insert(&mut self), Canyon writes the generated key back to the Rust instance.

#![allow(unused)]
fn main() {
#[primary_key(autoincremental = false)]
pub external_id: i64,
}

Use the non-incrementing form when your application supplies the key. You can map a table without annotating a primary key, but find_by_pk, update, and delete then have no key to use and return a typed error.

What Fields generates

For Team, the derive exposes three types:

• TeamTable represents table metadata.
• TeamField names a column, for example TeamField::name. Joins and ordering need this form.
• TeamFieldValue pairs a column with a value of its Rust type, for example TeamFieldValue::name("Blue".to_owned()). Predicates need both pieces.

Fields is the supported API in 0.5.1, and several models may derive it in one Rust module. A future major version may use typed column descriptors instead. Nothing is being removed in this release.

The query-builder chapter shows how the enums work now.

Mapping failures are real errors

A row may contain extra columns; the derived mapper ignores those it does not need. It cannot ignore a problem with a field the struct does require:

• A required column is missing.
• A non-optional field receives SQL NULL.
• The database value cannot be converted to the Rust field type.

Each case returns CanyonError::Mapping, not an empty result.

Nullable column? Use Option<T> on the model field when the SQL column can contain NULL.

The CRUD chapters reuse this Team model when they omit its definition.

For an unusual projection, you can implement canyon_sql::core::RowMapper yourself. Its backend-specific deserialization methods return CanyonResult. You then take responsibility for column names, nullability, and conversions on every backend you enable; start with CanyonMapper when a normal row struct will do.

Working with data

We have a Team that Canyon can map from a row. Deriving Crud adds the common read and write operations to that model:

#![allow(unused)]
fn main() {
#[derive(Debug, Fields, Crud, CanyonMapper)]
#[canyon_entity(table_name = "teams")]
pub struct Team {
    #[primary_key]
    pub id: i64,
    pub name: String,
}
}

You need not expose all four. The smaller derives are:

• Read for lookups.
• Insert for new rows.
• Update for changes.
• Delete for removals.

When you call a generated method, import its matching trait from canyon_sql::crud. The derive creates the implementation; the trait makes the method available at the call site.

All database calls are asynchronous and return CanyonResult<T>. A query can succeed without finding a row: find_all() returns an empty vector, and find_by_pk() returns Ok(None). A connection or mapping failure returns Err instead.

We'll start with reads, then move through writes and relationships. Once those methods feel familiar, the query builder will make more sense.

Choosing a datasource

Methods without _with use the first active datasource in canyon.toml. To choose another database, pass its name—or a compatible connection—to the _with form:

#![allow(unused)]
fn main() {
use canyon_sql::crud::Read;

let default_teams: Vec<Team> = Team::find_all().await?;
let analytics_teams: Vec<Team> = Team::find_all_with("analytics").await?;
}

find_by_pk, count, insert, update, and delete have the same choice. A misspelled name produces ConnectionError::DatasourceNotFound; it does not silently use the default.

Two meanings of _with: Team::find_all_with("analytics") chooses a connection. Team::select_query_with(DatabaseType::MySQL) chooses a SQL dialect, not a connection. The same is true of update_query_with and delete_query_with.

Build a query for the intended dialect, then run it with launch_with("mysql_datasource") or send its SQL and parameters through a matching connection. select_query() followed by launch_default() uses the default datasource for both steps.

Passing an existing connection to _with keeps that operation on the connection you chose. It does not make two different datasources part of one transaction.

Read

Import Read, then call its methods on Team:

#![allow(unused)]
fn main() {
use canyon_sql::crud::Read;

let teams: Vec<Team> = Team::find_all().await?;
let team: Option<Team> = Team::find_by_pk(&42_i64).await?;
let total: i64 = Team::count().await?;
}

Notice the three result shapes:

• find_all() reads every mapped row; no matches gives you an empty Vec<Team>.
• count() returns the row count as i64.
• find_by_pk() uses the field marked #[primary_key] and returns Ok(None) if no row has that key.

No row versus failed query: A missing table, failed connection, or failed row conversion is an Err, not an empty collection or None.

Each function has a _with counterpart for a named datasource or compatible connection:

#![allow(unused)]
fn main() {
let team = Team::find_by_pk_with(&42_i64, "reporting").await?;
}

Need a filter or an ordering? select_query() and select_query_with(...) start a builder instead of executing immediately. We'll use one in Build a query.

Without #[primary_key], find_all and count can still work. find_by_pk cannot guess which field is the key; it returns a typed error.

Insert

Give the new Team a name and leave its auto-generated key at the default value:

#![allow(unused)]
fn main() {
use canyon_sql::crud::Insert;

let mut team = Team {
    id: 0,
    name: "Blue".to_owned(),
};

team.insert().await?;
println!("new key: {}", team.id);
}

The result is CanyonResult<()>. On success, Canyon writes the generated key into team.id. If you mark the key #[primary_key(autoincremental = false)], provide its value yourself; Canyon inserts it instead of fetching one.

Use team.insert_with("reporting").await? to target a named datasource, or pass a compatible connection. Database constraints still apply: a duplicate key or missing required column will fail the insert.

Older examples: Crud does not provide the former insert_into API. For multiple rows, insert them individually or write SQL through DbConnection. If the rows must succeed or fail together, manage the transaction explicitly.

Update

An update uses the entity's primary key to identify the row. Change the Rust value, then persist it:

#![allow(unused)]
fn main() {
use canyon_sql::crud::{Read, Update};

let mut team = Team::find_by_pk(&42_i64)
    .await?
    .expect("the team must exist");

team.name = "Blue Tigers".to_owned();
let affected: u64 = team.update().await?;
}

affected is the database's affected-row count. It can be zero even though the statement succeeded—for example, if another operation removed that team after you read it. If your application expects exactly one row, check the count.

update_with("reporting") chooses a named datasource or compatible connection. If there is no #[primary_key], Canyon cannot safely construct the key predicate and returns a typed error.

To update rows by some condition other than the model's primary key, start with Team::update_query()?. The query-builder chapter shows how to set values and add the predicate. Always check that predicate before running a multi-row update.

Delete

Delete uses the annotated primary key, just as update does:

#![allow(unused)]
fn main() {
use canyon_sql::crud::Delete;

team.delete().await?;
}

The generated method returns CanyonResult<()>, not an affected-row count. If you need to confirm that a row is gone, read it again with find_by_pk. Without #[primary_key], Canyon returns an error rather than deleting without a key predicate.

delete_with("reporting") targets a named datasource or compatible connection. Database foreign-key constraints still apply: Canyon's relationship annotation generates lookup methods; it does not bypass or create those constraints.

For a filtered delete, start with Team::delete_query()? and add a predicate. Check the SQL before you execute a statement that could remove many rows.

Relationships

A player belongs to a team: the players table holds team_id, which points at a team. Canyon can generate lookups in both directions from that relationship. The database still needs its own foreign-key constraint if you want it enforced.

Suppose Player.team_id refers to Team.id:

#![allow(unused)]
fn main() {
use canyon_sql::macros::{canyon_entity, CanyonMapper, Crud, Fields};

#[derive(Debug, Fields, Crud, CanyonMapper)]
#[canyon_entity(table_name = "teams")]
pub struct Team {
    #[primary_key]
    pub id: i64,
    pub name: String,
}

#[derive(Debug, Fields, Crud, CanyonMapper)]
#[canyon_entity(table_name = "players")]
pub struct Player {
    #[primary_key]
    pub id: i64,
    #[foreign_key(references = Team::id)]
    pub team_id: i64,
    pub name: String,
}
}

The references path names a Rust type and field, not a SQL table string. Player needs Read (included in Crud), and Team needs mapping metadata. Canyon derives the name team from team_id and gives Player four methods:

#![allow(unused)]
fn main() {
let parent: Option<Team> = player.find_team().await?;
let children: Vec<Player> = Player::find_all_by_team(&team).await?;

let parent_on_other_db = player.find_team_with("reporting").await?;
let children_on_other_db =
    Player::find_all_by_team_with(&team, "reporting").await?;
}

The return type follows the direction of the lookup:

• player.find_team() looks for one parent: Ok(None) means the lookup found none.
• Player::find_all_by_team(&team) looks for children: Ok(vec![]) means there are none.

A query or mapping failure is an error in either direction. The _with variants also accept a compatible connection.

The referenced field can be something other than the parent's primary key. In that case, make sure your schema identifies a parent unambiguously—usually with a unique constraint. A fully qualified Rust path also works:

#![allow(unused)]
fn main() {
#[foreign_key(references = crate::models::Team::external_id)]
pub team_external_id: i64,
}

Canyon reads the parent's physical table name and schema from its entity metadata. TournamentDetails maps to tournament_details by default, but an explicit table_name works too. Keep that metadata, the Rust relationship, and the database constraint in agreement. The relationship tests exercise default and custom names on all three backends.

A complete example

Let's put the pieces together. We have teams, each with zero or more players, and two tables that already exist:

CREATE TABLE teams (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE players (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    team_id BIGINT NOT NULL REFERENCES teams(id),
    name TEXT NOT NULL
);

This is PostgreSQL SQL; another backend needs its own identity and foreign-key syntax. Configure a postgresql datasource as in Configure datasources, then write the models and the read:

use canyon_sql::{
    core::Canyon,
    crud::Read,
    macros::{canyon_entity, CanyonMapper, Crud, Fields},
    CanyonResult,
};

#[derive(Debug, Fields, Crud, CanyonMapper)]
#[canyon_entity(table_name = "teams")]
struct Team {
    #[primary_key]
    id: i64,
    name: String,
}

#[derive(Debug, Fields, Crud, CanyonMapper)]
#[canyon_entity(table_name = "players")]
struct Player {
    #[primary_key]
    id: i64,
    #[foreign_key(references = Team::id)]
    team_id: i64,
    name: String,
}

#[tokio::main]
async fn main() -> CanyonResult<()> {
    Canyon::init().await?;

    let teams = Team::find_all().await?;
    for team in teams {
        let players = Player::find_all_by_team(&team).await?;
        println!("{} has {} players", team.name, players.len());
    }

    Ok(())
}

Follow the loop in main: Canyon reads every team, then reads that team's players. Two declarations make this possible:

• #[foreign_key(references = Team::id)] generates the Rust lookup methods.
• REFERENCES teams(id) makes PostgreSQL enforce the relationship.

A team with no players prints 0. If either query or row mapping fails, ? returns that error from main; the loop does not quietly skip the team.

You can grow this example in several directions:

• Filter teams with Team::select_query()? and TeamFieldValue.
• Add writes with the Insert, Update, or Delete traits.
• Put writes behind a repository adapter. Read that chapter's table-name limitation first: this example uses teams, not the default team.

The schema, permissions, transaction boundaries, and meaning of a missing row remain application decisions. Canyon handles the repeated database plumbing around them.

Build a query

find_all() is useful until you want only the blue teams, or teams ordered by name. Then you need to describe the query before running it. That is the query builder's job.

The steps are:

1. Start with select_query(), update_query(), or delete_query(). These choose the default datasource's SQL dialect, but execute nothing.
2. Add predicates, joins, ordering, or values.
3. Call build()? to get a Query containing SQL and bound parameters. Then launch it or pass it to a connection.

A filtered read

Given the Team model from Entities and mapping:

#![allow(unused)]
fn main() {
use canyon_sql::{
    crud::Read,
    query::{
        operators::Operator,
        querybuilder::QueryBuilderExt,
    },
};

let name = TeamFieldValue::name("Blue".to_owned());
let teams: Vec<Team> = Team::select_query()?
    .where_value(&name, Operator::Eq)
    .build()?
    .launch_default()
    .await?;
}

where_value takes the field and value together. It collects "Blue" as a bound parameter; no string interpolation is needed. Keep name alive through the async call.

For more conditions, use and or or. The and_values_in and or_values_in methods take a Field and a non-empty slice of values; an empty slice produces QueryBuilderError::EmptyInClause.

Mind the parameters: The lower-level r#where(column, operator) adds a placeholder but does not collect a value. Use where_value unless you intend to supply the matching parameter yourself at execution time.

The Operator enum includes equality, inequality, greater/less-than comparisons, IN, and LIKE / NOT LIKE. For a contains-style pattern, use Operator::Like(LikeKind::Full); Left and Right select the other wildcard placements. Canyon renders backend-specific placeholders and quoting when it builds the query.

What Fields contributes

For Team, Fields generates three public enums:

• TeamTable: table metadata such as TeamTable::DbName.
• TeamField: a column name, such as TeamField::name, for ordering or a join.
• TeamFieldValue: the same column with a value of its Rust field type, such as TeamFieldValue::name("Blue".to_owned()), for a predicate.

These enums are the supported API in 0.5.1. A future major version may use typed column descriptors instead, but Fields is not deprecated. Multiple models can derive it in the same Rust module.

Joins and projection

SelectQueryBuilderExt adds inner_join, left_join, right_join, full_join, with_columns, with_distinct, count, and order_by. A join identifies its table and the two columns of the equality:

#![allow(unused)]
fn main() {
use canyon_sql::query::querybuilder::{QueryBuilderExt, SelectQueryBuilderExt};

let query = Team::select_query()?
    .inner_join(PlayerTable::DbName, TeamField::id, PlayerField::team_id)
    .order_by(TeamField::id, false)
    .build()?;

println!("{}", query.sql());
}

This uses the Player model from Relationships. The query may return more columns than Team declares; the mapper ignores extras. It still requires every field in Team.

If you need values from both tables, define a result type for that projection. Select overlapping column names explicitly so the mapper can tell them apart.

For a smaller projection, or to remove duplicate rows, use the select-specific methods:

#![allow(unused)]
fn main() {
let query = Team::select_query()?
    .with_columns(vec![TeamField::name])
    .with_distinct()
    .order_by(TeamField::name, false)
    .build()?;
}

This query selects only name, so it cannot produce a Team: the mapper also needs id. Use a result type with the projected shape, or inspect the rows with query_rows(...).

For a count without filters, Team::count().await? is simpler and returns i64 on every backend.

Updates and deletes

A conditional update must say what changes. Prefer set_values, which records each target column and its bound value together:

#![allow(unused)]
fn main() {
use canyon_sql::{
    connection::DbConnection,
    core::Canyon,
    crud::Update,
    query::{operators::Operator, querybuilder::{QueryBuilderExt, UpdateQueryBuilderExt}},
};

let changes = [(TeamField::name, "Blue Tigers")];
let target = TeamFieldValue::id(42_i64);
let query = Team::update_query()?
    .set_values(&changes)?
    .where_value(&target, Operator::Eq)
    .build()?;

let connection = Canyon::instance()?.get_default_connection()?;
let affected = connection.execute(query.sql(), query.params()).await?;
}

The lower-level set lists columns but does not collect their values. Use it only when you will supply parameters yourself. Canyon rejects an empty SET and a second SET on the same builder.

Check the scope of a write: Add a predicate to delete_query()? unless you mean to delete every row. The same care applies to a multi-row update. execute returns the affected-row count; update() and delete() are simpler when you have one model and its key.

For an insert that does not fit insert(), construct InsertQueryBuilder with a table and DatabaseType. Add columns with InsertQueryBuilderExt::with_columns, values with with_values, and optionally returning before build(). The number of columns and bound values must match. Crud does not generate an insert_query() method.

Dialect and connection are separate choices

Team::select_query_with(DatabaseType::MySQL)? chooses MySQL SQL syntax. It does not connect to MySQL. After build(), use launch_with("mysql_datasource") on a matching datasource.

The Query exposes sql() and params() for inspection or direct execution. Do not send SQL generated for one dialect to another backend.

Choose the launch method by the result you expect:

The scalar type must match the backend's value. A raw SQL Server COUNT(*), for example, is read as i32; generated Team::count() converts that count to i64 for you.

If the builder gets in the way, use a connection directly. Builder validation catches certain malformed shapes, not a table or column that is missing from your live database.

Run SQL directly

You do not have to fit every query into a derive or a builder. A report or database-specific operation may be clearer as SQL. Get a configured connection through Canyon::instance() and use DbConnection. This example uses PostgreSQL's $1 placeholder:

#![allow(unused)]
fn main() {
use canyon_sql::{connection::DbConnection, core::Canyon};

let connection = Canyon::instance()?.get_connection("main")?;
let id = 42_i64;
let team: Option<Team> = connection
    .query_one::<Team>("SELECT id, name FROM teams WHERE id = $1", &[&id])
    .await?;
}

There are four common ways to execute a statement:

All four return CanyonResult. With no matching rows, query returns an empty vector and query_one returns None. A query failure returns Err.

Binding values

The parameter slice holds references to values implementing QueryParameter. Supported values include:

• Numeric types such as i16, i32, i64, u32, f32, and f64.
• bool, strings, and several chrono date and time types.
• Nullable forms of selected types.

There is no blanket implementation for every Option<T>. An unsupported type fails to compile.

Keep parameter values alive through the async call. Bind values as shown above; do not interpolate them into the SQL string. On MySQL, Canyon normalizes DateTime<Utc> and DateTime<FixedOffset> values to UTC before binding them.

Rows without a model

If a query does not yet have a model, query_rows returns CanyonRows. Start with len(), is_empty(), or get_row_at(). When the first row does match a model, first::<Team>() maps it and returns CanyonResult<Option<Team>>:

• Ok(Some(team)): a row was mapped.
• Ok(None): there was no first row.
• Err(...): reading or mapping failed.

To work with driver rows directly, use get_postgres_rows(), get_tiberius_rows(), or get_mysql_rows(), behind their respective Cargo features. Asking for the wrong backend returns a mapping error.

If you depend on canyon_core directly, canyon_core::row::RowExt provides individual-row access. The root canyon_sql crate does not re-export it.

• get_postgres, get_mysql, and get_mssql read required values.
• Their _opt counterparts read nullable values.
• columns() returns each column's name and backend-specific ColumnType, useful when inspecting an unfamiliar result shape.

These getters return CanyonResult. Missing columns and incompatible types are errors. Required getters also reject NULL; the _opt getters represent it as None. Use a CanyonMapper model for an ordinary row.

Handwritten SQL is your SQL: You choose backend syntax and quote identifiers correctly. The query builder handles those details only for statements it generates.

Transaction is a low-level proxy over connection operations. It does not begin or commit a database transaction by itself. For atomic multi-statement work, use a compatible backend connection and manage its transaction explicitly.

Handle errors

Every fallible public operation returns CanyonResult<T>, short for Result<T, CanyonError>. Start with the outer error variant to find the failing part of the operation:

The error enums are #[non_exhaustive]; include a fallback arm when you match them. Canyon preserves the underlying driver error through std::error::Error::source(), so you can inspect the original cause.

Here is one way to handle a lookup at the call site:

#![allow(unused)]
fn main() {
use canyon_sql::{CanyonError, crud::Read};

match Team::find_by_pk(&42_i64).await {
    Ok(Some(team)) => println!("Found {}", team.name),
    Ok(None) => println!("No team has that key"),
    Err(CanyonError::Connection(error)) => eprintln!("Connection: {error}"),
    Err(error) => eprintln!("Query failed: {error}"),
}
}

What does “not found” return?

Read the return type:

• A list lookup — find_all() or Player::find_all_by_team(&team) — succeeds with an empty Vec when there are no matching rows. Looping over it simply does nothing.
• A single-row lookup — find_by_pk() or player.find_team() — succeeds with Ok(None) when no row matches. Decide at the call site whether that is acceptable or should become an application-level “not found” error.
• A scalar lookup such as query_one_for() expects a value. No row is reported as an error; there is no Option in its return type.

A broken row is not an absent row. A missing required column, unexpected NULL, or incompatible type produces Err(CanyonError::Mapping(...)).

Some mistakes fail before Canyon sends SQL. An empty IN list or a duplicated SET returns a query-builder error during build(). A nonexistent column or missing database permission is different: only the database can report it.

If your application has its own error type, convert CanyonError where you have enough context—for example, in an HTTP handler or service. Keep the source error for logs instead of replacing it with a generic string.

Repository adapters

Sometimes a Team should hold team data and nothing else. You can put database operations on a separate type without copying the fields into it. Canyon calls this a repository adapter.

Let's use a table named team, Canyon's default name for the Rust type Team. Only the row model is a Canyon entity:

#![allow(unused)]
fn main() {
use canyon_sql::macros::{canyon_entity, CanyonMapper};

#[derive(Debug, CanyonMapper)]
#[canyon_entity]
pub struct Team {
    #[primary_key]
    pub id: i64,
    pub name: String,
}
}

CanyonMapper reads rows into Team. The #[primary_key] marker tells generated operations which field identifies a row; #[canyon_entity] processes that marker on the model.

Now define a type for writes. It has no database fields of its own:

#![allow(unused)]
fn main() {
use canyon_sql::macros::{EntityDelete, EntityInsert, EntityUpdate};

#[derive(EntityInsert, EntityUpdate, EntityDelete)]
#[canyon_crud(maps_to = Team)]
pub struct TeamWriter {
    marker: (),
}
}

maps_to = Team says which type the operations receive. It does not turn TeamWriter into another entity. With the operation traits in scope, you can write:

#![allow(unused)]
fn main() {
use canyon_sql::crud::{EntityDelete as _, EntityInsert as _, EntityUpdate as _};

TeamWriter::insert_entity(&mut team).await?;
let affected: u64 = TeamWriter::update_entity(&team).await?;
TeamWriter::delete_entity(&team).await?;
}

There is no need to construct a TeamWriter here. The generated methods take a Team as an argument. You can derive just one or two operations if that is all the adapter should expose. Deriving all three also gives it the composite EntityCrud trait.

As with model methods, each operation has a _with form for a named datasource or compatible connection. The adapter does not start a transaction for you.

Before using an adapter with another model

The example above works with the conventional table name team. Check these two cases before copying the pattern:

1. Your table has a custom name. Earlier in this book, Team maps to teams. Today's adapter derives still generate SQL for team; they do not pick up Team's table_name. Write those repository operations explicitly for now.
2. You want to derive Read on a fieldless adapter. Its generated find_all selects the adapter's fields, not Team's. A marker: () field would become a selected column. Keep Read on the model or implement the repository read yourself.

Don't annotate the adapter as an entity. Adding #[canyon_entity(table_name = "teams")] to TeamWriter happens to point its SQL at the right table, but it also registers the adapter as an entity. That is a macro limitation to fix, not a pattern to copy.

Migrations: experimental

Canyon has a migrations Cargo feature. It is incomplete and experimental, so this book does not give you a migration workflow to use in an application.

Create and change tables with an established schema tool or SQL scripts. Canyon's mapping, CRUD methods, and query builder work without its migrations feature.

If you experiment with migrations, keep two things in mind:

• You still need a backend feature: postgres, mysql, or mssql.
• Older examples of compile-time table creation and data loading describe an earlier design; do not use them as a guide for the current release.

The feature needs a redesign, including integration with the typed CanyonError API, before it can have a supported workflow. Until then, migration code in the repository is implementation in progress, not an application contract.

Test and contribute

There are two useful levels of tests when working on Canyon itself:

• Unit and compile tests check Rust behavior and generated APIs without starting a database.
• Integration tests send queries to PostgreSQL, MySQL, and SQL Server. They need the repository's database fixtures.

From a checkout of the Canyon-SQL source repository, run the first level with:

cargo fmt --all -- --check
cargo test --workspace --lib --all-features
cargo test -p tests --test compile_tests --features postgres

At least one backend feature is required. A bare cargo test --workspace produces Canyon's intentional compile-time diagnostic about the missing backend.

Integration tests often put #[canyon_sql::macros::canyon_tokio_test] on a synchronous fn. The macro creates a test, starts Canyon's Tokio runtime, initializes datasources, and runs the body asynchronously. An initialization or body error fails the test; the databases still need to be running.

Start the Docker fixtures before running the integration suite. PostgreSQL and MySQL load their test data at startup. SQL Server needs its ignored initializer:

docker compose -f docker/docker-compose.yml up -d --wait
cargo test -p tests --test canyon_integration_tests --all-features \
  initialize_sql_server_docker_instance -- --ignored --test-threads=1
cargo test --workspace --all-features --no-fail-fast -- --test-threads=1

The integration suite changes database state, so --test-threads=1 helps keep runs predictable. The relationship tests prepare an idempotent fixture of their own, even when a Docker volume already exists. To check that the integration binary compiles without running it:

cargo test -p tests --test canyon_integration_tests --all-features --no-run

When fixing a bug, test it where it failed. Put generated-Rust regressions in tests/ui; put SQL behavior in the backend integration suite. CONTRIBUTING.md covers the repository workflow.

API map

When an example names a type but not its import, use this map. It lists the main exports of the root canyon_sql crate. A feature-gated item exists only when you enable its backend or experimental feature.

The derive macro Crud and the trait Crud have the same name but different jobs: import the derive from macros, and import the trait from crud to call its methods. The same applies to EntityInsert, EntityUpdate, and EntityDelete.

CanyonMapper maps rows; Fields supplies query identifiers. A model needs #[canyon_entity] for explicit table/schema metadata or Canyon field markers, not merely because it has a derive. Entities and mapping works through that choice.

On a repository adapter, #[canyon_crud(maps_to = Team)] names the mapped entity. It does not make the adapter an entity or copy Team's custom table name; see Repository adapters.

For exact signatures, use the public Rust API in the Canyon-SQL source or the published crate documentation.