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

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)
MySql

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 DataBase container

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

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

The file contains information about a PostgreSQL, SQLServer and MySql 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 = 'PostgresDS', auth = { postgresql = { basic = { username = "postgres", password = "postgres" } } }, properties.host = 'localhost', properties.db_name = 'triforce', properties.migrations = 'enabled' },
    { name = 'SqlServerDS', auth = { sqlserver = { basic = { username = "sa", password = "SqlServer-10" } } }, properties.host = '192.168.0.250.1', properties.port = 3340, properties.db_name = 'triforce2', properties.migrations = 'disabled' },
    { name = 'MysqlDS', auth = { mysql = { basic = { username = "root", password = "root" } } }, properties.host = '192.168.0.250.1', properties.port = 3340, properties.db_name = 'triforce2', properties.migrations = 'disabled' }
]

Note: In SqlServer the data will not be loaded automatically, user interaction is required. 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.

The Canyon's Book