Introduction

Welcome to The Canyon's SQL book! This is a work-in-progress user guide for the Canyon-SQL ORM and querybuilder.

Join our discord

For the latest announcements, frequently asked questions, and discussions about Canyon in general, join our Discord.

Phylosophy of design

The idea behind Canyon-SQL is a really simple one: Make the developer's life easier.

This is the core concept of the full development. We wanted to create an out-of-the-box solution to quickly develop any application that interacts with a database. With simplicity in mind. Hiding the ugly implementation details of handling database connections, connectors or repetitive code for every entity or model.

Write code that writes code

Most of the powerful concepts in Canyon.SQL are achieved through the Rust incredible macro system. This is the perfect definition for metaprogramming: they write code that writes code.

In concrete, Canyon's most beautiful solutions are created through procedural and derive macros. By writing attributes attached to a few structs or fields, the user achieve all functionalities provided by the framework.

The advantages

With this in mind, Canyon enables the development of code that requires a persistence solution. Allowing the focus to be placed on application design rather than infrastructure or technical requirements. This facilitates the scalability of the project to meet business requirements.

The Rust programming language

If you're new to the Rust programming language, it is highly recommended that you first familiarize yourself with all of the concepts outlined in the officially maintained Rust Book.

Contributing to Canyon's source code or to the book documentation

We welcome the contributions of new developers in advancing this project. However, in order to maintain order and consistency during development, we request that all contributors adhere to the following guidelines when adding new functionality, modifying or improving the source code, or fixing a bug:

  • Check with the community on Discord to keep updated with the latest announcements, issues and goals;
  • Open an issue here explaining what you wish to change or upgrade on the source code;
  • Clone the repo locally and work in the source code;
  • Open a PR to a new branch, and try to documentate as much as possible the changes made in the commits to your working branch.

So, if you wish to contribute to the source code:

  • Canyon-SQL it's a fully open-source project. You can find it here

If you wish to update the documentation with any information that was previously omitted or with changes that have been accepted in a pull request, please proceed as follows:

  • The source code for this book is this one.

License

Canyon-SQL, and this user guide, are both licensed under the MIT license.

Getting started

Before getting started with the code. Some small steps need to be taken care of. In section 1, the following topics will be discussed:

  • Making sure that all dependencies are installed;
  • Understanding Canyon's configuration file;
  • Setting up databases to use with Canyon;

If everything is already set up and tested correctly. Feel free to skip to section 2 where entities are explained.

Initial Setup


Minimum Requirements

Canyon is a versatile tool that combines a database manager, ORM, and query builder to create your persistence layer with ease. However, before using the framework, a minimum setup is required.


Supported database engines

At present, Canyon supports the following database engines:

  • PostgreSQL
  • MSSQL (SqlServer)

The development team is planning to add support for additional database engines in the future. For the purposes of this tutorial, we will be using PostgreSQL.


Downloading the PostgreSQL engine

To use Canyon with PostgreSQL, an active installation of PostgreSQL is required. If you are an experienced user and have already installed PostgreSQL, you can skip this section.

Installation and instructions can be found in the official website. Choose your operation system. If using Linux, more instructions can be found for each specific distro by searching in the web.


Add Canyon-SQL as a dependency

To create a new Rust project from scratch. Run:

# Replace project-name with the project name
cargo new project-name

In the existing project, open Cargo.toml to include the new dependency.

# Cargo.toml

# More dependencies and configurations

canyon_sql = 0.1.0^

Compiling will now download canyon_sql to the project. The ^ symbol tells the compiler to use version "0.1.0 or higher".

cargo build

Fetching from Github

Cargo can fetch the latest updates directly from the Canyon-SQL GitHub repository. These will include the latest changes that aren't included in a specific release.

Include the following lines to Cargo.toml:

# Cargo.toml

[dependencies]
canyon_sql = { git = "https://github.com/zerodaycode/Canyon-SQL" }

To fetch from a specific branch, such as the development branch, specify as follows:

# Cargo.toml

[dependencies]
canyon_sql = { git = "https://github.com/zerodaycode/Canyon-SQL", branch = "development" }

Building the project

After including Canyon-SQL as a dependency in Cargo.toml. The crate can be built with:

# When in project root
cargo build

If everything is set up correctly, the build process will complete successfully. However, if the canyon.toml file is not found, the following error will appear:


#![allow(unused)]
fn main() {
error: custom attribute panicked
  --> src\main.rs:17:1
   |
17 | #[canyon]
   | ^^^^^^^^^
   |
   = help: message:

           No file --> canyon.toml <-- founded on the root of this project.
           Please, ensure that you created one .toml file with the necesary properties needed in order to connect to the database.

           : Os { code: 2, kind: NotFound, message: "The system can't found the specified file." }

    ...
}

A file named canyon.toml is required to run Canyon-SQL. It should be located in the root directory of the project. canyon.toml contains the necessary properties required to connect to the database. Next chapter will explain how to configure it.

The secrets.toml file

In order for Canyon to work properly, it requires a canyon.toml file which stores the configuration details for each datasource declared. Essentially, this file contains the user configuration information for every datasource, each of which represents a connection to a database.

The canyon.toml file is a regular .toml file and should be kept secure, as it contains sensitive information such as passwords and credentials needed to access the databases. Therefore, it should not be stored in a public repository or be accessible to unauthorized users.

The format

A Canyon-SQL canyon.toml configuration file should be structured like this:

[canyon_sql]
datasources = [
    {name = 'postgres_docker', properties.db_type = 'postgresql', properties.username = 'postgres', properties.password = 'postgres', properties.host = 'localhost', properties.port = 5438, properties.db_name = 'postgres'},
    {name = 'sqlserver_docker', properties.db_type = 'sqlserver', properties.username = 'sa', properties.password = 'SqlServer-10', properties.host = 'localhost', properties.port = 1434, properties.db_name = 'master'}
]

Depending on your preferences you may also use "TOML array of tables":

[[canyon_sql.datasources]]
name = 'postgres_docker'

[canyon_sql.datasources.properties]
db_type = 'postgresql'
username = 'postgres'
password = 'postgres'
host = 'localhost'
port = 5438
db_name = 'postgres'

[[canyon_sql.datasources]]
name = 'sqlserver_docker'

[canyon_sql.datasources.properties]
db_type = 'sqlserver'
username = 'sa'
password = 'SqlServer-10'
host = 'localhost'
port = 1434
db_name = 'master'

In this configuration file, you should declare the following properties for each datasource:

  • name: A unique identifier for the datasource.
  • db_type: A string value that represents the type of database engine being used. Possible values are 'postgres' or 'postgresql' for PostgreSQL engines, and 'sqlserver' or 'mssql' for Microsoft SQL Server engines.
  • username: The username used to access the database.
  • password: The password associated with the above username.
  • host: (Optional) The address where the database lives. Defaults to localhost.
  • port: (Optional) A number representing the address port value. Defaults to the default value of the engine.
  • db_name: The name of the database that you want to connect with.
  • migrations: (Optional) Allows you to disable migrations for a whole datasource. Can be set to 'Enabled' or 'enabled' to explicitly enable migrations for this datasource (not necessary, since this is the default value), or 'Disabled' or 'disabled' to avoid processing migrations for this datasource.

It is worth noting that migrations are a powerful tool that will be discussed in later chapters, but for now, you may choose to skip them.

Note: It is recommended to use the TOML syntax for Canyon-SQL configuration file as presented above, with one table called [canyon_sql], a property named datasources, which is a collection of datasources, and json-like objects inside the collection representing every datasource that should be configured.

Note: It should be noted that inlined TOML tables, which are the objects inside the datasources collection, should be in a unique line without any jumps. This is an imposed restriction by the TOML standard itself. You can read more about this at the following link: https://toml.io/en/v1.0.0#inline-table. You may choose to use the classic table notation if you prefer. However, note that the example above may appear with new line jumps due to the TOML formatting.

Upon completing these steps, your program should now compile successfully. By running cargo build again, the compiler should be equipped with the necessary information for us to start writing code.

Setting up a Working Environment

To test the functionality of Canyon-SQL, it is necessary to have a working database with some data stored in it.

One quick way to set up such an environment is by using Docker. Docker is an open-source project that automates the deployment of applications as portable, self-sufficient containers that can run almost anywhere.

For those who are not yet familiar with Docker, official documentation is available here, along with installers for every supported platform.

Creating a PostgreSQL container

Assuming that your Docker environment is ready, the next step is to create a container with a PostgreSQL installation.

To accomplish this, we provide an example of a docker-compose file located in the scripts folder at the root of this repository. You may use this file to create a new container and populate it with data.

The file contains information about a PostgreSQL and a SQLServer containers to start.

In the same folder, you will also find an sql folder containing some SQL scripts. These scripts will be automatically detected by the docker-compose file and used to fill the tables of the examples.

Please note that we assume you have already installed docker-compose and copied the scripts folder into your project. Adjust the paths according to your preferences.

Build and start containers by running:

docker-compose -f ./scripts/docker-compose.yml up

Lastly, you will need to create a canyon.toml file as mentioned in the previous chapter. You can use the following snippet on it:

[canyon_sql]
datasources = [
    {name = 'postgres_docker', properties.db_type = 'postgresql', properties.username = 'postgres', properties.password = 'postgres', properties.host = 'localhost', properties.port = 5438, properties.db_name = 'postgres'}
]

Note: Please ensure that the inlined tables are correctly formatted without any line breaks.

Next time the code is compiled, Canyon will:

  • Connect to the databases;
  • Create the tables if needed;
  • Populate each table if needed;

Now it is time to start writing the code...

Canyon Entity

Welcome to the section dedicated to one of the most iconic features of Canyon - The Entities.

Definition of Canyon Entity

Throughout the previous chapters, we have already introduced the concept of entity and demonstrated its usage in various contexts. In Canyon, however, an entity holds an even more powerful meaning than what was presented before.

Put simply, a Canyon Entity is a user-defined type, which is represented as a struct, and is annotated with a special procedural macro:


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

#[canyon_entity]
pub struct League {
    #[primary_key]
    pub id: i32,
    pub ext_id: i64,
    pub slug: String,
    pub name: String,
    pub region: String,
    pub image_url: String
}
}

This macro unlocks a set of new features for the type. These features include:

  • Inform Canyon that it should handle everything in the program that relates to this type on the database;
  • Track the type along with all the necessary metadata on a special register;
  • Allow the user to configure an alternative table name and specify the schema in which the entity being pointed to resides;

These features provide a wealth of functionalities that will be explored further in upcoming chapters.

The table_name and the schema attribute parameters

The canyon_entity macro can optionally receive two parameters: table_name and schema.

Example:


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

The naming convention for Rust types is CamelCase. On the other hand, the naming convention for databases tables and schemas is often snake_case. These attributes allow us to fulfill both standards.

Typically, Canyon adheres to the convention of converting the name of a user-defined struct to a snake-case identifier for the purpose of generating database operations. This approach eliminates the need for modifying anything other than the defaults. Enabling users to conform to established ORM conventions in conjunction with the most common method of defining a table across various database engines.

However, it is not always necessary to follow this convention. In some cases, tables may already exist in the database, requiring different mapping. There may also be situations where the user prefer to use a different type name that cannot be converted to snake_case as described above. To accomodate these scenarios, the user has the ability to specify the name of the database entity accordingly.

Additionally, Canyon assumes assumes that the entities will be placed in the default schema, such as public in PostgreSQL or dbo in MSSQL. If there is need to use an alternative schema, the user may configure this on a per-entity basis using the schema parameter.

The #[primary_key] field attribute

Let's discuss one of the fundamental concepts in relational databases: the primary_key. Each table in a database typically has its own primary key, and the primary purpose of designating one is to identify each unique record in a particular table.

In Canyon, this is a significant concept. Almost every entity must be annotated with a primary_key to unlock the full potential of the CRUD operations generated by the CanyonCrud derive macro. It also manages other things, such as:

  • Identifying each unique record in a table;
  • Facilitating data serialization;
  • Auto-incrementing the primary_key of new inserted rows;

The primary_key attribute has one optional parameter: autoincremental. This is enabled by default. And means that each new row will have the key of the last row incremented by 1.

Sometimes, the user may not wish an incremental primary key (usually on rare cases where a unique String is used as primary key). Although this is not common, it is sometimes used. In that case, the user may disable autoincremental by including:


#![allow(unused)]
fn main() {
#[primary_key(autoincremental = false)]
}

Note: autoincremental is enabled by default. Not specifying it means it is true.

CRUD and Mapping Operations

Introduction

(back to top)

The primary purpose of Canyon is to facilitate database querying and retrieval of data. Writing raw SQL statements every time data is needed can be tedious, error-prone, and challenging to debug. Therefore, Canyon provides a solution to make the developer's life easier by automatically generating the desired queries.

As described in previous chapter, the macros #[canyon_entity] and #[primary_key] describe an entity and the primary key, respectively. In Canyon's common language, an entity represents a Rust type that models a real world object. It is a term used to refer to a type that maps against a database table. Canyon recognizes this and generate an implementation block with methods and associated functions to allow the type to communicate with the database.

Consider the following Rust type declaration:


#![allow(unused)]
fn main() {
#[canyon_entity]
pub struct League {
    #[primary_key]
    pub id: i32,
    pub ext_id: i64,
    pub slug: String,
    pub name: String,
    pub region: String,
    pub image_url: String
}
}

This struct represents the data of a professional competitive league from the League of Legends MOBA obtained from the LolEsports API. Suppose there is a web application that requests this information. The first thing that can be done is store it in the database and then serve it to the main application. Which could be, for instance, a mobile front-end application.

Methods will have to be implemented for saving, retrieving, and updating data...

To accomplish these tasks, Canyon will support the development by reducing the extensive amount of Rust code required to perform such database operations.

The CanyonCrud and CanyonMapper derive macros

(back to top)

Canyon provides two powerful macros to simplify the developer's work:


#![allow(unused)]
fn main() {
#[derive(CanyonCrud, CanyonMapper)]
}

These two derive macros work together and are essential for communicating with the database. They will implement the following traits on the type:

  • CanyonCrud is responsible for generating the CRUD methods for interacting with the database;
  • CanyonMapper is responsible for serializing/deserializing the data as it sends/receives messages with the database;

#![allow(unused)]
fn main() {
#[derive(CanyonCrud, CanyonMapper)]
#[canyon_entity]
pub struct League {
    #[primary_key]
    pub id: i32,
    pub ext_id: i64,
    pub slug: String,
    pub name: String,
    pub region: String,
    pub image_url: String
}
}

The asynchronous behavior of Canyon

(back to top)

It should be noted that every Canyon action requires the usage of a .await expression. This is because every operation in Canyon is designed to be asynchronous, which means that the result must be awaited in order to be obtained.

It is also important to remember that from this point forward, every action with Canyon that leads to the execution of a database query must be followed by the await instruction. This is because futures are lazy by default, which means that they will not be consumed unless they are awaited.

If you are not familiar with the concept of asynchronous programming, we recommend taking a look at The Rust async book. This resource can provide valuable insight about asynchronous programming.

The datasource concept

(back to top)

Before we proceed, let's discuss the concept of datasource in Canyon.

A datasource in Canyon refers to the channel that connects the framework with a specific database. A user may have multiple datasources, even if they use different database engines. With Canyon, you can query one database in a statement and then query a completely different one in the next statement.

Specifying Datasources

(back to top)

It is worth noting that all CRUD operations, whether they are a method from your type or an associated function, will have a duplicate ending with _datasource. For instance, the ::find_all() operation will have a replica called ::find_all_datasource(datasource_name: &str). In this duplicate, datasource_name matches one of the datasource names defined in the configuration file.

Default Datasources

(back to top)

Canyon takes the first declared datasource as the default one. Therefore, you may find code that uses ::find_all() without the need to specify a datasource, as Canyon will use the default directly in the queries.

To avoid duplication in the next chapters, it is essential to remember that there will be a _datasource alternative for every CRUD operation. This can be used to specify a datasource when the default is not the target.

Next steps

(back to top)

In this chapter, we have created a new type that is capable of querying the database and properly serializing/deserializing messages as needed.

However it is important to explore what Canyon is really capable of doing with these annotations. The following chapters will discuss more in-depth about each of the CRUD operations...

The Canyon Runtime

Before getting started with Canyon, there is an essential requirement to consider.

Canyon-SQL requires an asynchronous runtime to function, which means that the signature of functions or methods should be modified with the async modifier.


#![allow(unused)]
fn main() {
// from
fn a_synchronous_function(){
    // ...
}

// to
async fn an_asynchronous_function() {
    // ...
}
}

To use the asynchronous runtime, Canyon re-exports the tokio crate. Which can be enabled by modifying the main function as follows:

#[tokio::main]
async fn main() { 
    /* code in main */ 
}

Canyon also comes with a prebuilt solution to this that reduces the presented above to only this:

#[canyon]
fn main() { 
    /* code in main */ 
}

Either solution is acceptable. The second option is simpler and enables additional features that will be discussed later in the migrations section

SELECT operations

The most fundamental operation when querying a database is the read operation, which involves requesting a particular set of data stored in one or more tables.

When any of the CRUD operations are processed, the data received as response from the database will be of a generic type referred to as Row. Row is unique for each client library, but each of them refer to a single "row" retrieved from the database.

Canyon's job is to then parse this retrieved response into T or Vec<T>, where T is your defined type. As long as the types are properly annotated, the users don't need to worry about parsing the data themselves.

Here is the type that was described in the previous chapter:


#![allow(unused)]
fn main() {
#[derive(CanyonCrud, CanyonMapper)]
#[canyon_entity]
pub struct League {
    #[primary_key]
    pub id: i32,
    pub ext_id: i64,
    pub slug: String,
    pub name: String,
    pub region: String,
    pub image_url: String
}
}

Let's review what SELECT operations are automatically available now. With focus on those that are well known in the CRUD world.

find_all

One of the most commonly used queries when working with databases. In summary, it is the same as saying:

"Please, database, give me all the data that you have for this table, including all of the columns!"

In Canyon, this method is available as an associated function for your defined type T. The type::find_all() method can be translated to a SQL query SELECT * FROM {table_name}, which will return a collection of Row instances.

The retrieved Row collection will then be automatically mapped into a Vec<T>, where T is the same type of the object where type::find_all was called.


#![allow(unused)]
fn main() {
#[derive(CanyonCrud, CanyonMapper)]
#[canyon_entity]
pub struct League {
    #[primary_key]
    pub id: i32,
    pub ext_id: i64,
    pub slug: String,
    pub name: String,
    pub region: String,
    pub image_url: String
}
}

We can retrieve all rows of the league table with the following line:


#![allow(unused)]
fn main() {
let leagues: Result<Vec<League>, _> = League::find_all().await;
}

Unchecked alternatives

There are two more operations associated with the find_all() function that provide unchecked alternatives.

In Canyon, every database operation returns a Result<T, E>. However, during software development, debugging, or prototyping, it may be useful to access query results without additional complications. For this purpose, Canyon provides:

  • T::find_all_unchecked()
  • T::find_all_unchecked_datasource()

Both functions return a Vec<T> directly, bypassing the Result type. However, if there is any error during the database connection, the program will panic. Therefore, these functions are only recommended for quickly profiling or experimentation.

Find by PK

Another common pattern for reading data is to find a record by its primary key (PK).

Looking at the previous example League again:


#![allow(unused)]
fn main() {
#[derive(CanyonCrud, CanyonMapper)]
#[canyon_entity]
pub struct League {
    #[primary_key]
    pub id: i32,
    pub ext_id: i64,
    pub slug: String,
    pub name: String,
    pub region: String,
    pub image_url: String
}
}

The primary key in this case is id. Therefore, only one League row exists for every unique id value. The auto-incrementing parameter isn't set. So it is enabled. Check previous chapter for more information.

To find a record by its primary key, the method type::find_by_id can be used:


#![allow(unused)]
fn main() {
// Searching for a row that has id=1
let league: Result<Option<League>, _> = League::find_by_id(&1).await;
}

Note the reference on the function argument. The find_by_id associated function doesn't take ownership of the parameter.

INSERT operations

Now it is time to write data into the database.

Canyon provides a convenient way to insert data directly from an instantiated object.

Remember this?


#![allow(unused)]
fn main() {
#[derive(CanyonCrud, CanyonMapper)]
#[canyon_entity]
pub struct League {
    #[primary_key]
    pub id: i32,
    pub ext_id: i64,
    pub slug: String,
    pub name: String,
    pub region: String,
    pub image_url: String
}
}

Let's create a League instance:


#![allow(unused)]
fn main() {
let mut lec: League = League {
    id: Default::default(),
    ext_id: 134524353253,
    slug: "LEC".to_string(),
    name: "League Europe Champions".to_string(),
    region: "EU West".to_string(),
    image_url: "https://lec.eu".to_string(),
};
}

Notice how the id is Default::default(). For the type i32, this just means 0.

Now, with that value, we will call the following instruction:


#![allow(unused)]
fn main() {
lec.insert().await;
}

And that record is now inserted into the database!

But note one thing: the lec instance was declared as mutable. Why?

It is important to remember that the insert method from Canyon will automatically update the self.id field. The previous id was replaced by the newly generated ID after the insert.

Thanks to it, the insert method is a really convenient method for writing data into the database. For example, a common scenario is when you make a call to an external service and parse the data into a new instance of your Rust type. With this you can insert it directly in a single expression!

The new associated function pattern

A common alternative to writing better and more maintainable code is to use the convention of "constructors" in Rust.

Although there is no exact concept of constructors in the language, a convention has been established for creating new instances of a given type T.

This convention is to write an associated function ::new(params) in the impl block for initializing objects of the given type. Through this approach, an initializer may be implemented as follows:


#![allow(unused)]
fn main() {
impl League {
    // Notice how id is not included in the parameters
    pub fn new(
        ext_id: i64,
        slug: String,
        name: String,
        region: String,
        image_url: String
    ) -> Self {
        Self {
            id: Default::default(),
            ext_id: ext_id,
            slug: slug,
            name: name,
            region: region,
            image_url: image_url
        }
    }
}
}

With this in mind, the previous example can be written and inserted into the database as follows:


#![allow(unused)]
fn main() {
// Create new instance
let lec: League = League::new(
    134524353253,
    "LEC".to_string(),
    "League Europe Champions".to_string(),
    "EU West".to_string(),
    image_url: "https://lec.eu".to_string()
);

// Insert new instance into the database
lec.insert().await;
}

The insert instruction returns a Result<(), Err> type. For simplicity, we are omiting handling results. For more information on the topic, see in The Official Book.

Performing Multiple Inserts

In addition to allowing the insertion of individual entities, Canyon also enables users to insert multiple entities with a single operation. The multi-insert feature is implemented as an associated function rather than a method of the instantiated object.

You can pass instances of your type as a reference to a raw Rust array, and then await the result. The values generated for fields declared as primary keys will then be assigned to the corresponding field of each instance.

The syntax for the multi-insert feature is shown below:


#![allow(unused)]
fn main() {
/// Demonstration on how to perform an insert of multiple items on a table
async fn _multi_insert_example() {
    // Create an instance
    let new_league = League {
        id: Default::default(),
        ext_id: 392489032,
        slug: "DKC".to_owned(),
        name: "Denmark Competitive".to_owned(),
        region: "Denmark".to_owned(),
        image_url: "https://www.an_url.com".to_owned()
    };
    // Create a second instance
    let new_league2 = League {
        id: Default::default(),
        ext_id: 392489032,
        slug: "IreL".to_owned(),
        name: "Ireland ERL".to_owned(),
        region: "Ireland".to_owned(),
        image_url: "https://www.yet_another_url.com".to_owned()
    };
    // Create a third instance
    let new_league3 = League {
        id: Default::default(),
        ext_id: 9687392489032,
        slug: "LEC".to_owned(),
        name: "League Europe Champions".to_owned(),
        region: "EU".to_owned(),
        image_url: "https://www.lag.com".to_owned()
    };

    // Unused Result<T, E>
    // Insert all three instances in a single transaction
    League::insert_into(
        &[new_league, new_league2, new_league3]
    ).await;
}
}

The insert_into instruction returns a result with an array of the updated values that were inserted.

Notes on the #[primary_key] annotation

It is important to note that if the database has a primary key, the #[primary_key] annotation is mandatory. By default, Canyon assumes that the column holding the primary key has a sequence or similar database concept and will omit the PK value on the insert operation. This will result in the database generating a unique and auto-incremental value.

However, you can still insert into tables that do not have any columns marked as primary key. In such cases, you should not declare a #[primary_key] in your entity.

Therefore, it is important to include the #[primary_key] annotation when performing insert operations. If a table's column has a primary key and Canyon does not find the #[primary_key] annotation, it will serialize the value to insert it, which is an illegal or incomplete operation.

It is also worth noting that _datasource(datasource_name: &str) alternatives are available, and if you do not provide a #[primary_key] annotation, the insert methods will not be generated for that type. However, the associated function T::multi_insert() will still be available.

UPDATE operations

Update operations involve altering the values in the database with new ones. Canyon offer developers an easy way to perform operations on a specific instance of a given T type that was properly set up.

Considering the following League instance:


#![allow(unused)]
fn main() {
let mut lec: League = League::new(
    134524353253, 
    "LEC".to_string(),
    "League Europe Champions".to_string(),
    "EU West".to_string(),
    image_url: "https://lec.eu".to_string()
);
}

Suppose that the image_url field has to be modified. It can be done as follows:


#![allow(unused)]
fn main() {
// modify the field first
lec.image_url = "https://new_lec_url.eu".to_string();
// synchronize the changes with the database
lec.update().await; // unused result
}

image_url is a public field, so it can be modified at will. Some objects require validation before making changes. In such cases, usually the fields will be private. Although getters and setters should be also available for interacting with the fields.

When running the update instructions, an "update row request" with all the new fields will be made with the database. Synchronizing the new state with the database.

There is no need to call update more than once. Make all the changes that are required, call update once, then all the changes will be reflected to the database.

Note: Don't forget about using _datasources methods when not using the default datasource.

Note: If a #[primary_key] does not exist on the type declaration, the update methods for it will not be generated.

DELETE operations

Delete operations consists in removing rows of data from the database.

With Canyon, developers can delete a single record at a time using an instance method of the given T type that was properly set up as an entity.

Once again, using the League entity:


#![allow(unused)]
fn main() {
let mut lec: League = League::new(
    134524353253, 
    "LEC".to_string(),
    "League Europe Champions".to_string(),
    "EU West".to_string(),
    image_url: "https://lec.eu".to_string()
);
}

The existing entry can be deleted from the database by running:


#![allow(unused)]
fn main() {
lec.delete();
}

The delete method will run a query similar to DELETE FROM table_name WHERE table_name.<pk_name> = value, where pk_name and value comes from the #[primary_key] set on the type declaration for League. Canyon will delete the row from the database where the id matches the instance id.

In summary, to delete a record from the database using Canyon, the record must exist and be mapped to an instance. If the record for that row doesn't exist, the query will still be executed on the database.

Note: Don't forget about using _datasources methods when not using the default datasource.

Note: If a #[primary_key] does not exist on the type declaration, the delete methods for it will not be generated.

Relations: Foreign Keys

Canyon provides an easy-to-implement solution for enforcing rules over relations or associations in a database. This chapter will explore how to add SQL CONSTRAINTS to the classic relation in the SQL standard, the FOREIGN KEY.

Index

Foreign Key

(back to top)

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. A FOREIGN KEY is a field or collection of fields in one table that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the PRIMARY KEY is called the referenced or parent table.

To demonstrate how Canyon implements this relation, two entities will be used:


#![allow(unused)]
fn main() {
#[derive(CanyonCrud, CanyonMapper, ForeignKeyable)]
#[canyon_macros::canyon_entity]
pub struct League {
    #[primary_key]
    pub id: i32,
    pub ext_id: i64,
    pub slug: String,
    pub name: String,
    pub region: String,
    pub image_url: String
}
}

league.rs


#![allow(unused)]
fn main() {
#[derive(CanyonCrud, CanyonMapper)]
#[canyon_entity]
pub struct Tournament {
    #[primary_key]
    pub id: i32,
    pub ext_id: i64,
    pub slug: String,
    pub start_date: NaiveDate,
    pub end_date: NaiveDate,
    #[foreign_key(table = "league", column = "id")]
    pub league: i32
}
}

tournament.rs

The foreign_key annotation and the ForeignKeyable derive macro

(back to top)

As you may have noticed, a new annotation has been introduced in the Tournament entity:


#![allow(unused)]
fn main() {
#[foreign_key(table = "league", column = "id")]
}

This annotation generates a new parent-child relation between League and Tournament, where League is the parent or the referenced entity, where League is the parent or the referenced entity, and Tournament is the child entity. This is specified through the table argument. Which indicates the parent table, and the column argument, which indicates the referred field.

foreign_key annotations describe a parent-child relation between two tables. It requires two arguments:

  • table : The parent table. In the above example, it is "league";
  • column : The column or field that is the id of the parent table;

The current table is Tournament. Therefore Tournament is the child and League is the parent. Tournament can not exist without a League to reference. But League can exist without any Tournament existing.

Note: The table and column arguments currently accept an &str to create the data necessary for generating the SQL for each operation. However, this behavior will change in upcoming releases, where the table argument will accept a valid Rust identifier, and the column argument will accept a FieldIdentifier discussed in previous chapters.

For this relation to be implemented successfully, a new derive macro must be included along with the ones presented in past chapters. Entities that behave like parents must have the ForeignKeyable derive macro.

Whenever there is a foreign key relation in the code, you must tell Canyon that this entity T at some point will be the parent for some other entity T. There is no need to specify the child because Canyon will be able to resolve that question through the first annotation.

However, do not forget to annotate your parents with the ForeignKeyable macro, or Canyon will issue a warning later when trying to query the database given this relation.

Foreign Key Queries

(back to top)

In Canyon, a SQL CONSTRAINT operation offers two advantages:

Firstly, it ensures data integrity by preventing invalid data insertion and removing data that depends on other records. This feature is particularly useful when working with relational databases.

Secondly, it enables querying based on the relation. For instance, given the League and Tournament types defined at the beginning of this chapter, a user may query:

  • Retrieve data for a League referenced by a Tournament.
  • Find out how many Tournaments are associated with a specific League.

Retrieve data for a League referenced by a Tournament

(back to top)

To retrieve data for a League referenced by a Tournament. Manually, a query similar to this could be made:

SELECT * FROM league WHERE league.id = tournament.league

In the above example tournament.league is the id of the league stored on the tournament instance.

With Canyon, a method for this will be generated called search_parent_table_league. It returns an option that will be Some if it finds the League instance.


#![allow(unused)]
fn main() {
// You can search the 'League' that is the parent of a concrete instance of 'Tournament'
let parent_league: Option<League> = tournament_itce.search_league().await;

println!(
    "The related League queried through a method of tournament: {:?}", 
    &parent_league
);
}

In a more complete example, suppose you retrieve some Tournament from the database and then ask Canyon to find its League parent:


#![allow(unused)]
fn main() {
let tournament: Option<Tournament> = Tournament::find_by_id(1).await;
println!("Tournament: {:?}", &tournament);

if let Some(trnmt) = tournament {
    let result: Option<League> = trnmt.search_league().await;
    println!("The related League as method if tournament is some: {:?}", &result);
} else { println!("`tournament` variable contains a None value") }
}

Note that the identifier for the autogenerated method is called 'search_league'

Find out how many Tournaments are associated with a specific League

(back to top)

On the type declarations for Tournament and League. It can be noticed that Tournament stores the id of a foreign League. It is possible to have several tournaments that reference the same League. This query is about retrieving these entries.

This is usually known as "the reverse side of a Foreign Key". Canyon offers the possibility to find the children of a specific type or, in other words, the Tournaments associated with a particular League:


#![allow(unused)]
fn main() {
    /* Find all the tournaments that are pointing to the same `League` record. This is usually known as the reverse side of a foreign key. It is a many-to-one relation on this side, not a one-to-one */

    // Find league with id 1
    let some_league: League = League::find_by_pk(&1)
        .await
        .expect("Result variant of the query is err")
        .expect("No result found for the given parameter");

    // Retrieve all tournaments that reference to some_league
    let child_tournaments: Vec<Tournament> = Tournament::search_league_childrens(&some_league)
        .await
        .expect("Result variant of the query is err");

    assert!(!child_tournaments.is_empty());
    child_tournaments
        .iter()
        .for_each(|t| assert_eq!(t.league, some_league.id));    
}

Finds the records that are directly pointing to an existing League instance.

It is important to note that the associated function follows a naming convention of search_ + parent_type + childrens. Furthermore, it receives a reference to an instance of League(&lec), necessitating the existence of a valid record of League prior to executing the function.

The QueryBuilder

So far, queries have been executed by mapping fields and their corresponding data for each type or instance of that type. Generating the necessary SQL sentences to perform the CRUD operations discussed earlier.

However, the SQL language offers a wealth of additional power to analyze and manipulate data stored in tables. Specifically, we are referring to the SQL clauses that function as filters.

Index

Introduction

(back to top)

Canyon provides support for making queries over your entities more flexible. These queries are also defined in the CanyonCrud macro and belong to a family called QueryBuilder, which include three types representing the SELECT, UPDATE and DELETE operations. These types implement the QueryBuilder trait.

  • The SelectQueryBuilder, is invoked after calling T::select_query()
  • The UpdateQueryBuilder, is invoked after calling T::update_query()
  • The DeleteQueryBuilder, is invoked after calling T::delete_query()

How does it work?

(back to top)

The macro implementations provide various associated functions or methods that return an implementor of the QueryBuilder type. These can be used as a builder pattern to construct the query that Canyon will use to retrieve data from the database. These functions also contain a query() consumer method, which consumes the builder and executes the query against the database, returning the data.

The QueryBuilder works by generating the base SQL, similar to the CRUD operations presented in the previous chapters. However, instead of executing the query, it allows the user to chain methods over a QueryBuilder instance. With each method call, the query builder generates more SQL content, enabling an efficient and elegant solution to build more complex queries.

Available methods to work with the builder

(back to top)

"Hablar de la raĆ­z de la familia"

Method calls can be chained over a QueryBuilder implementor. This allows building a more complex query when needed. By returning &mut Self, the user may access the same instance of the QueryBuilder and modify its internal state until consumed.

To understand the capabilities of the QueryBuilder, let's review the available declarations, which are the available methods in all the implementors.

To see more about the capabilities of the QueryBuilder, below are the available methods of its implementors:


#![allow(unused)]
fn main() {
pub trait QueryBuilder<'a, T>
    where T: CrudOperations<T> + Transaction<T> + RowMapper<T> 
{
    /// Returns a read-only reference to the underlying SQL sentence with the same lifetime as self.
    fn read_sql(&'a self) -> &'a str;

    /// Allows the user to append the content of a string slice to the
    /// end of the underlying SQL sentence.
    ///
    /// # Arguments
    ///
    /// * `sql` - The [`&str`] to be appended to the SQL query.
    fn push_sql(&mut self, sql: &str);

    /// Generates a `WHERE` SQL clause for constraining the query based on a column name and a binary comparison operator.
    ///
    /// # Arguments
    ///
    /// * `column` - A `FieldValueIdentifier` that will provide the target column name and the value for the filter.
    /// * `op` - Any type that implements the `Operator` trait.
    fn r#where<Z: FieldValueIdentifier<'a, T>>(&mut self, column: Z, op: impl Operator) -> &mut Self
        where T: Debug + CrudOperations<T> + Transaction<T> + RowMapper<T>;

    /// Generates an `AND` SQL clause for constraining the query based on a column name and a binary comparison operator.
    ///
    /// # Arguments
    ///
    /// * `column` - A `FieldValueIdentifier` that will provide the target column name and the value for the  filter.
    /// * `op` - A type that implements `Operator` for the comparison.
    fn and<Z: FieldValueIdentifier<'a, T>>(&mut self, column: Z, op: impl Operator) -> &mut Self;

    /// Generates an `OR` SQL clause for constraining the query based on a column name and a set of values that are contained within the column.
    ///
    /// # Arguments
    ///
    /// * `column` - A `FieldIdentifier` for the column name.
    /// * `values` - An array of `QueryParameter` with the values that are contained within the column.
    fn and_values_in<Z, Q>(&mut self, column: Z, values: &'a [Q]) -> &mut Self
        where 
            Z: FieldIdentifier<T>,
            Q: QueryParameters<'a>;

    /// Generates an `OR` SQL clause for constraining the query based on a column name and a set of values that are contained within the column.
    ///
    /// # Arguments
    ///
    /// * `column` - A `FieldIdentifier` that will provide the target column name for the filter.
    /// * `values` - An array of `QueryParameter` with the values to filter.
    fn or_values_in<Z, Q>(&mut self, r#or: Z, values: &'a [Q]) -> &mut Self
        where Z: FieldIdentifier<T>, Q: QueryParameters<'a>;

    /// Generates an `OR` SQL clause for constraining the query based on a column name and a binary comparison operator.
    ///
    /// # Arguments
    ///
    /// * `column` - A `FieldValueIdentifier` that will provide the target column name and the value for the filter.
    /// * `op` - Any type that implements `Operator` for the comparison
    fn or<Z: FieldValueIdentifier<'a, T>>(&mut self, column: Z, op: impl Operator) -> &mut Self;

    /// Generates an `ORDER BY` SQL clause for ordering the results of the query.
    ///
    /// # Arguments
    ///
    /// * `order_by`: A `FieldIdentifier` for the column name.
    /// * `desc`: A boolean indicating whether ordering should be ascending or descending.
    fn order_by<Z: FieldIdentifier<T>>(&mut self, order_by: Z, desc: bool) -> &mut Self;
}
}

Note: The 'Like' clause will be included in a later release.

This will provide you with an idea of the functions performed by the chained methods on the instance. These methods essentially append additional SQL code to the base code to filter the results or execute a particular operation based on your requirements.

The QueryBuilder implementors

(back to top)

Each QueryBuilder implementor provides a public interface that contains the methods discussed above, as well as its logical operations. For instance, calling T::select_query() returns a SelectQueryBuilder, which offers an interface to add JOIN clauses to the SQL statement. Currently, LEFT, RIGHT, INNER and FULL joins are available. the inner_join() method signature is:


#![allow(unused)]
fn main() {
pub fn inner_join(&mut self, join_table: &str, col1: &str, col2: &str) -> &mut Self;
}

Where join_table is the entity to perform the join, and col1 and col2 are the columns to declare in the ON clause. The other join methods have the same parameters.

The T::update_query() builds an UPDATE statement, providing all the methods defined in the QueryBuilder trait declaration. Additionally, it includes a set() method:


#![allow(unused)]
fn main() {
pub fn set<Z, Q>(&mut self, columns: &'a [(Z, Q)]) -> &mut Self
    where
        Z: FieldIdentifier<T> + Clone,
        Q: QueryParameter<'a>
}

You may use it as follows:


#![allow(unused)]
fn main() {
let mut q: UpdateQueryBuilder<Player> = Player::update_query_datasource(SQL_SERVER_DS);
    q.set(&[
        (PlayerField::summoner_name, "Random updated player name"),
        (PlayerField::first_name, "I am an updated first name"),
    ])
    .r#where(PlayerFieldValue::id(&1), Comp::Gt)
    .and(PlayerFieldValue::id(&8), Comp::Lt)
    .query()
    .await
    .expect("Failed to update records with the querybuilder");
}

Notice the array of tuples. The first element is the column to target, and the second is the value to update in the database column.

Finally, T::delete_query() constructs a DELETE statement.


#![allow(unused)]
fn main() {
Tournament::delete_query()
    .r#where(TournamentFieldValue::id(&14), Comp::Gt)
    .and(TournamentFieldValue::id(&16), Comp::Lt)
    .query()
    .await
    .expect("Error connecting to the database during the delete operation");
}

The Comp enum type

(back to top)

The QueryBuilder require a comparison operator in some of its methods. This is created from the Comp enum type, which is passed as a parameter to the methods that require it. Allowing you to generate comparison operators in a procedural manner.

The available operators are:


#![allow(unused)]
fn main() {
pub enum Comp {
    /// Operator "=" equal
    Eq,
    /// Operator "!=" not equal
    Neq,
    /// Operator ">" greater than <value>
    Gt,
    /// Operator ">=" greater than or equal to <value>
    GtEq,
    /// Operator "<" less than <value>
    Lt,
    /// Operator "=<" less than or equal to <value>
    LtEq,
}
}

Better by example

(back to top)

Using the QueryBuilder might be confusing at first. Specially for developers that are not accustomed to the builder pattern. However, with practice, developers will see that it is a very practical way of querying.

Let's consider an example where we are working once again with the League type: we want to retrieve all records in the league table with id less than 20 and slug equal to LCK.


#![allow(unused)]
fn main() {
let leagues: Result<Vec<League>, _> = League::select_query()
    .r#where(
        LeagueFieldValue::id(20),    // This will create a filter -> `WHERE league.id < 20`
        Comp::Lt                     // where the `<` symbol is generated by Canyon when it sees this variant
    ).and(
        LeagueFieldValue::slug("LCK".to_string()),
        Comp::Eq
    ).query()
    .await
    .expect("Query failed");

println!("League elements: {:?}", &leagues);
}

The remaining methods that can be chained are self-explanatory. You can chain as many methods as needed. Make sure that the methods are chained properly to avoid errors.

In addition, if needing more examples, you can visit our repository's /tests/crud folder and refer to the querybuilder_operations.rs tests. There you will find code examples for all available operations within the Canyon query builders.

Now, you may be wondering where LeagueField, LeagueFieldValue(T), FieldIdentifier, and FieldValueIdentifier types and bounds come from.

The #[derive(Fields)] macro

(back to top)

When working with SQL, it is often necessary to specify the column name or type of a column in some operations. To avoid using raw or literal values, we created the #[derive(Fields)] derive macro. This macro generates special enumerated types that allows the user to refer to a database column name, type or value in a procedural way. It replaces the string that is usually used with a piece of valid Rust code.

Rest assured that Canyon translates these procedures into the corresponding identifiers whenever they are needed. Let's take a closer look at these two types.

The Field enum

(back to top)

The field enum is an autogenerated enum that serves to relate each field of a type with a database column name in a procedural way, making it easier to reflect them through the code.

The identifier of the Field enum is generated as the concatenation of the type's identifier with "Field". It is important to note that there is a naming convention in Canyon, where the variants of an enumeration in Rust are typically written in PascalCase, while in Canyon they are written in snake_case to match the way that the field is written.

For example:


#![allow(unused)]
fn main() {
#[derive(Clone, Debug)]
#[allow(non_camel_case_types)]
pub enum LeagueField {
    id,
    ext_id,
    slug, 
    name, 
    region, 
    image_url
}

impl FieldIdentifier<LeagueField> for LeagueField {}
}

When directly interacting with a database, it is common to refer to a column using a plain string. That is a problem, because the compiler wouldn't be able to detect any mistake until it attempts to make the query. We, Rust developers, didn't choose this language in order to have runtime errors.

Therefore, whenever a concrete column of the league table must be specified, such as the slug column, you could write LeagueField::slug. While this may seem like more code, it removes the potential errors that can come with using literals in the code. IDEs can also offer autocompletion, making it easier to write and detect wrong variants.

The impl FieldIdentifier<LeagueField> for LeagueField {} trait implementation is part of Canyon's method of identifying when it must accept a FieldIdentifier type as an argument for functions that generate filters for queries, or when constraining the concrete arguments of some operations of the QueryBuilder to the same type. This helps to avoid potential errors of specifying variants or different types, and is almost always used when you generate a filter for your SQL query.

The FieldValue enum

(back to top)

In addition to the Field enum, another enum is generated by the canyon_entity proc-macro annotation, known as the FieldValue enum. This enum serves as a procedural way to determine which field is being referred to, but with the added capability of accepting a value as an argument in every variant. This value can be any supported literal or type for which the QueryParameter<'a> is implemented. The main purpose of this enum is to create filters for an SQL query where the filter needs a value to perform the filtering.

The FieldValue enum looks like this:


#![allow(unused)]
fn main() {
#[derive(Clone, Debug)]
#[allow(non_camel_case_types)]
pub enum LeagueFieldValue {
    id(&dyn QueryParameter<'_>),
    ext_id(&dyn QueryParameter<'_>),
    slug(&dyn QueryParameter<'_>), 
    name(&dyn QueryParameter<'_>), 
    region(&dyn QueryParameter<'_>), 
    image_url(&dyn QueryParameter<'_>)
}

impl FieldIdentifierValue<LeagueFieldValue> for LeagueFieldValue {}
}

Using the FieldValue enum provides a powerful way of writing filters for SQL queries. For instance, LeagueFieldValue::id(1) can be used to generate a WHERE clause filter: WHERE league.id = 1, among other operations.

Next steps

(back to top)

Now that Querybuilder has been discussed, the next chapter is about Migrations. Where everything that have been discussed up until now will be wrapped into the full package that Canyon provides. See you there!

Canyon Migrations: The Final Boss

Note: Please be advised that this feature is still unstable. For further information, please refer to the disclaimer section a the end of this document.

Index

Introduction

As a modern ORM framework, Canyon aims to provide a comprehensive solution for managing all relations with the database in a manner that is agnostic to the developer. This concept is commonly referred to as "migrations". Migrations are a way for the framework to take full responsibility for managing every aspect of the database, including creating and dropping tables, altering their names, columns, column names, column types, generating GRANT permissions, etc.

However, unlike other ORM frameworks, Canyon proposes a new way of handling migrations. Other frameworks come with a built-in command tool or require a separate command tool to be downloaded, installed, and managed. Canyon, on the other hand, manages everything at compile time and executes the migrations at the beginning of the client's code.

It should be noted that this feature must be enabled, as it is not active by default.

Only one entity per .rs file

(back to top)

To enable the framework to manage the entity types, they must be annotated with the #[canyon_entity] macro, which was introduced in the Entities chapter.


#![allow(unused)]
fn main() {
#[canyon_entity]  // ready to be tracked and managed by Canyon
pub struct League { /* fields... */ }
}

It is important to remember that only one annotated entity with #[canyon_entity] is allowed per file due to the way migrations work. Attempting to implement more than one entity in the same file will result in a compiler error.

The #[canyon] annotation

(back to top)

As is typical with components of a modern ORM framework. Canyon utilizes annotations to drive its behavior. This means that when needing something, Canyon most likely has an annotation to solve it.

In order to enable migrations, include the following annotation for the main() function.

#[canyon]
fn main() { 
    /* code in main */ 
}

Canyon's #[canyon] annotation. It unlocks the 'full mode'

The 'full mode' concept

(back to top)

When discussing about the framework, the term 'full mode' is used to indicate that Canyon has activated all of its features to assume complete control over everything related to the database in your program.

The migrations

(back to top)

When the full mode is enabled, Canyon takes care of managing the migrations for the complete lifecycle of the program. Migrations are designed to manage every declared entity during compile time. Thanks to the complex macro system, it will generate all the necessary queries to be applied at the start of the application.

Note: Currently, we are making efforts to ensure that the migration process is only executed when cargo build or cargo run are invoked. The execution of migrations by code static analyzers, which make use of the cargo check functionality, can lead to obscure feedback.

Canyon searches for structs annotated with canyon_entity, then checks the database information provided in the canyon.toml file. It generates everything necessary for the entities in the database.

During compilation, Canyon searches for new entities, entities that have been removed from your code (which will then be deleted from the database), entities that have been renamed, entity fields that have been renamed or changed type, and any new or removed field annotation for an entity.

When it is still compiling, Canyon generates the necessary queries for the database. Even though this is the most complicated feature of Canyon, there is not much more explanation about how it affects development. As mentioned earlier, the idea behind Canyon is simple:

Make developer's life easier

Disclaimer

(back to top)

As stated at the beginning of this document, Canyon migrations are still an unstable feature under development.

There is still some work to be done to ensure they function as intended, and some of the features mentioned are not yet available in the 0.1.0 release.

Many functionalities are already implemented, such as changing table names, creating, dropping, altering tables, modifying sequences or identities, changing column data types. However, renaming columns is not yet possible.

Furthermore, while some of these functionalities are ready for use with PostgreSQL databases, they may not be available for MSSQL databases, if Canyon encounters an action that requires processing from Microsoft's database engine, it will raise a todo!() panic. To avoid such situations, it is possible to disable migrations for a specific datasource in the configuration file.

!todo()