# Technical Documentation: diesel-rs/diesel > ℹ️ **Provenance:** Multi-Tier Ingestion · [CodeWiki Reference](https://codewiki.google/github.com/diesel-rs/diesel) ### Overview (`README.md`) [](https://diesel.rs) # A safe, extensible ORM and Query Builder for Rust [](https://github.com/diesel-rs/diesel/actions?query=workflow%3A%22CI+Tests%22+branch%3Amain) [](https://crates.io/crates/diesel) API Documentation: [latest release](https://docs.rs/diesel) – [main branch](https://docs.diesel.rs/main/diesel/index.html) [Homepage](https://diesel.rs) Diesel gets rid of the boilerplate for database interaction and eliminates runtime errors without sacrificing performance. It takes full advantage of Rust's type system to create a low overhead query builder that "feels like Rust." Supported databases: 1. [PostgreSQL](https://docs.diesel.rs/main/diesel/pg/index.html) 2. [MySQL](https://docs.diesel.rs/main/diesel/mysql/index.html) 3. [SQLite](https://docs.diesel.rs/main/diesel/sqlite/index.html) 4. [MariaDB](https://docs.diesel.rs/main/diesel/mariadb/index.html) You can configure the database backend in `Cargo.toml`: ```toml [dependencies] diesel = { version = "", features = [""] } ``` ## Getting Started Find our extensive Getting Started tutorial at [https://diesel.rs/guides/getting-started](https://diesel.rs/guides/getting-started). Guides on more specific features are coming soon. ## Getting help If you run into problems, you can come ask for help at in our [GitHub Discussions](https://github.com/diesel-rs/diesel/discussions) forum. This is also the right place to propose new features or show your applications. ## Usage ### Simple queries Simple queries are a complete breeze. Loading all users from a database: ```rust users::table.load(&mut connection) ``` Executed SQL: ```sql SELECT * FROM users; ``` Loading all the posts for a user: ``` rust Post::belonging_to(user).load(&mut connection) ``` Executed SQL: ```sql SELECT * FROM posts WHERE user_id = 1; ``` ### Complex queries Diesel's powerful query builder helps you construct queries as simple or complex as you need, at zero cost. ```rust let versions = Version::belonging_to(krate) .select(id) .order(num.desc()) .limit(5); let downloads = version_downloads .filter(date.gt(now - 90.days())) .filter(version_id.eq_any(versions)) .order(date) .load::(&mut conn)?; ``` Executed SQL: ```sql SELECT version_downloads.* FROM version_downloads WHERE date > (NOW() - '90 days') AND version_id = ANY( SELECT id FROM versions WHERE crate_id = 1 ORDER BY num DESC LIMIT 5 ) ORDER BY date ``` ### Less boilerplate Diesel codegen generates boilerplate for you. It lets you focus on your business logic, not mapping to and from SQL rows. That means you can write this: ```rust #[derive(Queryable, Selectable)] #[diesel(table_name = downloads)] pub struct Download { id: i32, version_id: i32, downloads: i32, counted: i32, date: SystemTime, } ``` Instead of this without Diesel: ```rust pub struct Download { id: i32, version_id: i32, downloads: i32, counted: i32, date: SystemTime, } impl Download { fn from_row(row: &Row) -> Download { Download { id: row.get("id"), version_id: row.get("version_id"), downloads: row.get("downloads"), counted: row.get("counted"), date: row.get("date"), } } } ``` ### Inserting data It's not just about reading data. Diesel makes it easy to use structs for new records. ```rust #[derive(Insertable)] #[diesel(table_name = users)] struct NewUser<'a> { name: &'a str, hair_color: Option<&'a str>, } let new_users = vec![ NewUser { name: "Sean", hair_color: Some("Black") }, NewUser { name: "Gordon", hair_color: None }, ]; insert_into(users) .values(&new_users) .execute(&mut connection); ``` Executed SQL: ```sql INSERT INTO users (name, hair_color) VALUES ('Sean', 'Black'), ('Gordon', DEFAULT) ``` If you need data from the rows you inserted, just change `execute` to `get_result` or `get_results`. Diesel will take care of the rest. ```rust let new_users = vec![ NewUser { name: "Sean", hair_color: Some("Black") }, NewUser { name: "Gordon", hair_color: None }, ]; let inserted_users = insert_into(users) .values(&new_users) .get_results::(&mut connection); ``` Executed SQL: ```sql INSERT INTO users (name, hair_color) VALUES ('Sean', 'Black'), ('Gordon', DEFAULT) RETURNING * ``` ### Updating data Diesel's codegen can generate several ways to update a row, letting you encapsulate your logic in the way that makes sense for your app. Modifying a struct: ```rust post.published = true; post.save_changes(&mut connection); ``` One-off batch changes: ```rust update(users.filter(email.like("%@spammer.com"))) .set(banned.eq(true)) .execute(&mut connection) ``` Using a struct for encapsulation: ```rust update(Settings::belonging_to(current_user)) .set(&settings_form) .execute(&mut connection) ``` ### Raw SQL There will always be certain queries that are just easier to write as raw SQL, or can't be expressed with the query builder. Even in these cases, Diesel provides an easy to use API for writing raw SQL. ```rust #[derive(QueryableByName)] #[diesel(table_name = users)] struct User { id: i32, name: String, organization_id: i32, } // Using `include_str!` allows us to keep the SQL in a // separate file, where our editor can give us SQL specific // syntax highlighting. sql_query(include_str!("complex_users_by_organization.sql")) .bind::(organization_id) .bind::(offset) .bind::(limit) .load::(&mut conn)?; ``` ## Code of conduct Anyone who interacts with Diesel in any space, including but not limited to this GitHub repository, must follow our [code of conduct](https://github.com/diesel-rs/diesel/blob/main/code_of_conduct.md). ## License Licensed under either of these: * Apache License, Version 2.0, ([LICENSE-APACHE](LICENSE-APACHE) or https://www.apache.org/licenses/LICENSE-2.0) * MIT license ([LICENSE-MIT](LICENSE-MIT) or https://opensource.org/licenses/MIT) ### Contributing Before contributing, please read the [contributors guide](https://github.com/diesel-rs/diesel/blob/main/CONTRIBUTING.md) for useful information about setting up Diesel locally, coding style and common abbreviations. Unless you explicitly state otherwise, any contribution you intentionally submit for inclusion in the work, as defined in the Apache-2.0 license, shall be dual-licensed as above, without any additional terms or conditions. ### Notable Sponsors and Supporters We would like to thank all of the sponsors supporting the work on Diesel. Notable large sponsors are:

NLNet Foundation
Prototype Fund
GitHub Secure Open Source Fund

NGI Zero Core
Federal Ministry of Research, Technology and Space (Germany)
GiGa Infosystems GmbH

Rust Foundation
Additionally we would like to thank all persons sponsoring the project on [GitHub](https://github.com/sponsors/weiznich#sponsors). Without them developing Diesel wouldn't be possible. --- ### Overview (`diesel_bench/Readme.md`) # A benchmark suite for relational database connection crates in rust This directory contains a basic benchmark suite that allows to compare different crates that can be used to connect to relational database systems. Those benchmarks are created with the following goals: a) To track diesels performance and find potential regressions b) To evaluate potential alternatives for the currently used C-dependencies c) To compare diesel with the competing crates It currently supports the following database systems: * PostgreSQL * MySQL/MariaDB * SQLite and the following crates: * Diesel * [SQLx](https://github.com/launchbadge/sqlx) * [Rustorm](https://github.com/ivanceras/rustorm) * [Quaint](https://github.com/prisma/quaint) * [Postgres](https://github.com/sfackler/rust-postgres) * [Rusqlite](https://github.com/rusqlite/rusqlite) * [Mysql](https://github.com/blackbeam/rust-mysql-simple) * [diesel-async](https://github.com/weiznich/diesel_async) * [wtx](https://github.com/c410-f3r/wtx) By default only diesels own benchmarks are executed. To run the benchmark do the following: ```sh $ DATABASE_URL=your://database/url diesel migration run --migration-dir ../migrations/$backend $ DATABASE_URL=your://database/url cargo bench --features "$backend" ``` To enable other crates add the following features: * `SQLx: ` `sqlx-bench sqlx/$backend $backend` * `Rustorm`: `rustorm rustorm/with-$backend rustorm_dao $backend` * `SeaORM`: `sea-orm sea-orm/sqlx-$backend sqlx tokio criterion/tokio futures $backend` * `Quaint`: `quaint quaint/$backend tokio quaint/serde-support serde $backend` * `Postgres`: `rust_postgres $backend` * `Rusqlite`: `rusqlite $backend` * `Mysql`: `rust-mysql $backend` * `diesel-async`: `diesel-async diesel-async/$backend $backend tokio` * `wtx`: `$backend tokio/rt-multi-thread wtx` ## Benchmarks ### Common data structures #### Table definitions The following schema definition was used. (For Mysql/Sqlite postgres specific types where replaced by their equivalent type). ```sql CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL, hair_color VARCHAR ); CREATE TABLE posts ( id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, title VARCHAR NOT NULL, body TEXT ); CREATE TABLE comments ( id SERIAL PRIMARY KEY, post_id INTEGER NOT NULL, text TEXT NOT NULL ); ``` #### Struct definitions ```rust pub struct User { pub id: i32, pub name: String, pub hair_color: Option, } pub struct Post { pub id: i32, pub user_id: i32, pub title: String, pub body: Option, } pub struct Comment { id: i32, post_id: i32, text: String, } ``` Field types are allowed to differ, to whatever type is expected compatible by the corresponding crate. ### `bench_trivial_query` This benchmark tests how entities from a single table are loaded. Before starting the benchmark 1, 10, 100, 1000 or 10000 entries are inserted into the `users` table. For this the `id` of the user is provided by the autoincrementing id, the `name` is set to `User {id}` and the `hair_color` is set to `Null`. An implementation of this benchmark is expected to return a list of the type `User. ### `bench_medium_complex_query` This benchmark tests how entities from more than one table are loaded. Before starting the benchmark 1, 10, 1000, 10000 entries are inserted into the `users` table. For this the `id` of the user is provided by the autoincrementing id, the `name` is set to `User {id}` and the `hair_color` is set to `"black"` for even id's, to `"brown"` otherwise. An implementation of this benchmark is expected to return a list of the type `(User, Option)` filtered by `hair_color = "black"` so that matching pairs of `User` and `Option` are returned. Though the `posts` table is empty the corresponding implementation needs to query both tables. ### `bench_insert` This benchmark tests how fast entities are inserted into the database. For this each implementation gets a size how many entries are scheduled to be inserted into the database. An implementation of this benchmark is expected to insert as many entries into the user table as the number provided by the benchmark framework. It is not required to clean up the already inserted entries at any time. Newly inserted users are generated using the following rules: `id` of the user is provided by the autoincrementing id, the `name` is set to `User {batch_id}` and the `hair_color` is set to `"hair_color"`. ### `bench_loading_associations_sequentially` This benchmark tests how fast a complex set of entities is received from the database. Before starting the benchmark a large amount of data needs to be inserted into the database. The `users` table is required to contain 100 entries (or for sqlite 9 entries) based on the following rules: `id` is determined by the autoincrementing column, `name` is set to `User {batch_id}` and `hair_color` is set to `"black"` for even id's, otherwise to `"brown"`. For each entry in the `users` table, 10 entries in the `posts` table need to exist. Each entry in the `posts` table is based on the following rules: `id` is autogenerated by autoincrementing column, `title` is set to `Post {post_batch_id} for user {user_id}` where `post_batch_id` referees to number of the current post in relation to the user (so between 0 and 9), `user_id` is set to the corresponding user's id, and `body` is set to `NULL`. For each entry in the `posts` table 10 entries in the `comments` table are generated based on the following rules: `id` is set to the autoincrementing default value, `text` is set to `Comment {comment_batch_id} on post {post_id}` where `comment_batch_id` referees to the number of the current comment in relation to the post (so between 0 and 9), `post_id` is set tho the corresponding posts's id. An implementation of the benchmark is expected to return a list of the type `(User, Vec<(Post, Vec)>)`, that contains all users wit all corresponding comments and posts grouped by their corresponding associations. --- ### Overview (`diesel_cli/README.md`) Diesel CLI ========== Diesel CLI is a tool that aids in managing your database schema. Migrations are bi-directional changes to your database that get applied sequentially. Installation --------------- The diesel cli, by default, requires [`openssl`][openssl], [`libpq`][postgres], [`sqlite`][sqlite], and [`mysql`][mysql]. Once these dependencies are installed, you can run `cargo install diesel_cli`. > Note: Make sure that both the `bin` and `lib` directories for > postgres are added to your PATH To install the cli without these dependencies, omit the unneeded dependencies from the following command: ```sh cargo install diesel_cli --no-default-features --features "postgres sqlite mysql" ``` [openssl]: https://www.openssl.org/source [postgres]: https://www.postgresql.org/download/ [sqlite]: http://www.sqlitetutorial.net/download-install-sqlite/ [mysql]: https://dev.mysql.com/doc/refman/5.7/en/installing.html If you are using a system without an easy way to install sqlite (for example Windows), you can use a bundled version instead: ```shell cargo install diesel_cli --no-default-features --features "sqlite-bundled" ``` Getting Started --------------- ```sh cargo install diesel_cli diesel setup --database-url='postgres://localhost/my_db' diesel migration generate create_users_table ``` You'll see that a `migrations/` directory was generated for you (by the setup command), and two sql files were generated, `migrations/{current_timestamp}_create_users_table/up.sql` and `migrations/{current_timestamp}_create_users_table/down.sql`. You should edit these files to show how to update your schema, and how to undo that change. ```sql -- up.sql CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL, favorite_color VARCHAR ); ``` ```sql -- down.sql DROP TABLE USERS; ``` You can then run your new migration by running `diesel migration run`. Your DATABASE_URL must be set in order to run this command, and there are several ways that you can set it: * Set it as an environment variable manually * Set it as an environment variable using [dotenv](https://github.com/dotenv-rs/dotenv#examples) * Pass it directly by adding the `--database-url` flag As an alternative to running migrations with the CLI, you can call [`diesel::migrations::run_pending_migrations`][pending-migrations] from `build.rs`. Diesel will automatically keep track of which migrations have already been run, ensuring that they're never run twice. Commands -------- ## `diesel setup` Searches for a `migrations/` directory, and if it can't find one, creates one in the same directory as the first `Cargo.toml` it finds. It then tries to connect to the provided DATABASE_URL, and will create the given database if it cannot connect to it. Finally it will create diesel's internal table for tracking which migrations have been run, and run any existing migrations if the internal table did not previously exist. ## `diesel database` #### `database setup` Tries to connect to the provided DATABASE_URL, and will create the given database if it cannot connect to it. It then creates diesel's internal migrations tracking table if it needs to be created, and runs any pending migrations if it created the internal table. #### `database reset` Drops the database specified in your DATABASE_URL if it can, and then runs `diesel database setup`. ## `diesel migration` #### `migration generate` Takes the name of your migration as an argument, and will create a migration directory with `migrations/` in the format of `migrations/{current_timestamp}_{migration_name}`. It will also generate `up.sql` and `down.sql` files, for running your migration up and down respectively. #### `migration run` Runs all pending migrations, as determined by diesel's internal schema table. #### `migration revert` Runs the `down.sql` for the most recent migration. #### `migration redo` Runs the `down.sql` and then the `up.sql` for the most recent migration. ## `diesel print-schema` Prints table definitions for database schema. [pending-migrations]: https://docs.rs/diesel_migrations/*/diesel_migrations/fn.run_pending_migrations.html [rust-dotenv]: https://github.com/dotenv-rs/dotenv#examples Bash completion --------------- Diesel can generate a bash completion script for itself: #### linux ```sh $ diesel completions bash > /etc/bash_completion.d/diesel ``` #### os x (homebrew) ```sh $ brew install bash-completion # you may already have this installed $ diesel completions bash > $(brew --prefix)/etc/bash_completion.d/diesel ``` --- ### Overview (`diesel_dynamic_schema/README.md`) [](https://diesel.rs) Query schemas not known at compile time with Diesel =================================================== [](https://travis-ci.org/diesel-rs/diesel-dynamic-schema) API Documentation: [latest release](https://docs.rs/diesel-dynamic-schema) Diesel is built to provide strong compile time guarantees that your queries are valid. To do this, it needs to represent your schema at compile time. However, there are some times where you don't actually know the schema you're interacting with until runtime. This crate provides tools to work with those cases, while still being able to use Diesel's query builder. Keep in mind that many compile time guarantees are lost. We cannot verify that the tables/columns you ask for actually exist, or that the types you state are correct. Getting Started --------------- The main function used by this crate is `table`. Note that you must always provide an explicit select clause when using this crate. ```rust use diesel_dynamic_schema::table; let users = table("users"); let id = users.column::("id"); let name = users.column::("name"); users.select((id, name)) .filter(name.eq("Sean")) .first(&conn) ``` ## Code of conduct Anyone who interacts with Diesel in any space, including but not limited to this GitHub repository, must follow our [code of conduct](https://github.com/diesel-rs/diesel/blob/master/code_of_conduct.md). ## License Licensed under either of these: * Apache License, Version 2.0, ([LICENSE-APACHE](LICENSE-APACHE) or https://www.apache.org/licenses/LICENSE-2.0) * MIT license ([LICENSE-MIT](LICENSE-MIT) or https://opensource.org/licenses/MIT) ### Contributing Unless you explicitly state otherwise, any contribution you intentionally submit for inclusion in the work, as defined in the Apache-2.0 license, shall be dual-licensed as above, without any additional terms or conditions. --- ### Overview (`fuzz/README.md`) # diesel-fuzz Fuzz harnesses for diesel's database input and codec boundaries, reaching diesel only through its public API. ``` cargo +nightly fuzz run --fuzz-dir fuzz # from the repository root ``` | Target | Property | |---|---| | `pg_from_sql` | 48 postgres decoders never panic | | `pg_array` | array encoding matches a model of the binary format, and decoding recovers the elements in up to six dimensions | | `mysql_from_sql` | 29 mysql decoders never panic, under every wire type | | `sqlite_from_sql` | 26 sqlite decoders never panic, in every storage class | | `sqlite_jsonb_decode` | decoding a blob never panics | | `sqlite_jsonb_roundtrip` | diesel reads back what it wrote as jsonb and json text, and sqlite calls both valid | | `sqlite_blob_state` | incremental blob reads, seeks, and close or drop agree with a byte-slice model | - Only `sqlite_jsonb_decode` has a checked-in corpus; the other targets take `Arbitrary` input. - `sqlite_jsonb_roundtrip` builds its value from entropy, not parsed `JSON`, so a writer bug cannot hide behind matched `serde_json` rounding. - `-max_len=4096` avoids the drop-time stack overflow in `serde_json::Value`. - Minimize a crash with `cargo fuzz tmin`, then pin it as a test in the owning diesel module. --- ### Overview (`guide_drafts/README.md`) Guide Drafts ============ This directory contains guides that will eventually be added to the diesel.rs website, but are still being worked on. Once the guide content is deemed complete, and it has been styled and marked up for the website, they are removed from this directory. --- ### Overview (`examples/postgres/composite_types/README.md`) # diesel-postgres-composite-type This repository contains a series of examples to demonstrate how [composite types](https://www.postgresql.org/docs/current/rowtypes.html) in PostgreSQL can be used in the diesel query builder. This manual assumes you're familiar with [PostgreSQL](https://www.postgresql.org/docs/) and [Rust](https://www.rust-lang.org/learn). As I struggled to understand how you can use [Diesel](https://diesel.rs) with PostgreSQL's composite types, I have written a series of examples which stepwise introduces the required methods and traits to deal with this. What will be discussed? * Importing data from postgreSQL to Rust [anonymously](README.md#from-pgsql-to-rust-anonymously-coordinates). * Importing data from postgreSQL to Rust [using named fields](README.md#from-pgsql-to-rust-with-type-binding-colors). * Exporting data from Rust to PostgreSQL (TODO) # From pgSQL to Rust anonymously: coordinates. Let's start with a simple table containing an unique identifier and two columns with integers. After downloading the repository and running `diesel migration run` you should be able to see the following, using e.g. the terminal `psql`: ```sql composite_type_db=# SELECT * FROM coordinates; coord_id | xcoord | imaginairy_part ----------+-----------+----------------- 1 | 1 | 0 2 | 0 | 1 3 | 1 | 1 4 | 3 | 4 ``` ### Get the used types from the column definition The typical working flow when using Diesel is to automatically generate [schema.rs](./src/schema.rs) which provides us with the type information of the columns which are present in our coordinates table. Also, an SQL function, [distance_from_origin()](./migrations/2023-10-23-111951_composite2rust_coordinates/up.sql), is defined. We need to explain this to the Rust compiler using the [define_sql_function!](https://docs.rs/diesel/latest/diesel/expression/functions/macro.define_sql_function.html) macro like this: ```rust define_sql_function!(fn distance_from_origin(re: Integer,im: Integer) -> Float); ``` Keep in mind that we specify [only postgreSQL types](https://docs.rs/diesel/latest/diesel/sql_types/index.html) as the input parameters and return value(s) of this function. If the columns names are also *in scope* then we can write in Rust: ```rust let results: Vec<(i32, f32)> = coordinates .select((coord_id, distance_from_origin(xcoord, ycoord))) .load(connection)?; ``` So we expect a vector of a 2-tuple, or ordered pair of the Rust types ```i32``` and ```f32```. Mind that the float type is not present in the table but is specified in by the SQL function in the database and also in the macro `define_sql_function!` definition above. Of course we can expand this to very long tuples, but that will become error prone as we have to specify the sequence of type correctly every function call. Try out the [first example](./examples/composite2rust_coordinates) with: ```sh cargo run --example composite2rust_coordinates ``` ### Define an alias type To avoid errors we could define an [alias type](https://doc.rust-lang.org/stable/std/keyword.type.html) once and use this in the various calls of our function. ```rust type Distance = (i32, f32); ``` The re-written function call will then look like: ```rust let results: Vec = coordinates .select((coord_id, distance_from_origin(xcoord, ycoord))) .load(connection)?; ``` ### Reducing the output to a single value instead of an array The default output of a query to the database is a table with results. However, frequently we may only expect a single answer, especially if a function has defined several **OUT**-*put* parameters instead of returning a table, like the created SQL function ```shortest_distance()```. To avoid the destructering of the vector, when a vector is not needed, we can use the ```get_result()``` instead of the ```load()``` function call with our specified type: ```rust let result: Distance = select(shortest_distance()) .get_result(connection)?; ``` ### Creating a type in PostgreSQL world Using a tuple only enforces the correct number of return values and their basic type. If multiple values of the same type are returned, they can easily be mixed-up without any warning. Therefore, to improve readability of the database SQL functions it makes sense to introduce new types, like for example this one: ```sql CREATE TYPE my_comp_type AS (coord_id INTEGER, distance FLOAT4); ``` If we specified a database function ```longest_distance()``` we can simply use that now on the Rust side with: ```rust let result: Distance = select(longest_distance()) .get_result(connection)?; ``` So, although we specified a new type in the database, we **don't need** to specify it on the Rust side too. If we never make errors, that would be a possible solution. However, like unsafe Rust, this is not recommended. Why build in possible pitfalls if we can avoid them? # From pgSQL to Rust with type binding: colors. In the [second example](./examples/composite2rust_colors.rs) we want to convert any RGB value, consisting of three integer values, to a new type which expresses the reflected light and suggests a name for this reflection: ```sql CREATE TYPE gray_type AS (intensity FLOAT4, suggestion TEXT); ``` This new type will be used in two exactly the same SQL functions `color2grey()` and `color2gray()` of which input and return value are specified like: ```sql CREATE FUNCTION color2grey( red INTEGER, green INTEGER, blue INTEGER ) RETURNS gray_type AS $$ ... ``` You can run the example with the following command: ```sh cargo run --example composite2rust_colors ``` On the Rust side, we define the interpretation of both functions differently, the first one using a tuple similar to the coordinates example, the second one using a _locally_ defined Rust type for interpreting a tuple: notice the **Pg**-prefix of `PgGrayType`. ```rust define_sql_function!(fn color2grey(r: Integer, g: Integer,b: Integer) -> Record<(Float,Text)>); define_sql_function!(fn color2gray(r: Integer, g: Integer,b: Integer) -> PgGrayType); ``` As this only creates a type with anonymous fields, which can be addressed by their field number **object.0**, **object.1** etc., it would be more convenient to attach names to the fields. Therefore we need to define a type with our intended field names, which we can use _globally_ (or at least outside the database related code space): ```rust #[derive(Debug, FromSqlRow)] pub struct GrayType { pub intensity: f32, pub suggestion: String, } ``` The derived [FromSqlRow](https://docs.rs/diesel/latest/diesel/deserialize/trait.FromSqlRow.html) trait explains Diesel it is allowed to convert a tuple to this new type. We only need a implementation on _how_ to do that for a PostgreSQL backend: ```rust impl FromSql for GrayType { fn from_sql(bytes: PgValue) -> deserialize::Result { let (intensity, suggestion) = FromSql::::from_sql(bytes)?; Ok(GrayType { intensity, suggestion, }) } } ``` Although this seems trivial for this example, it also allows the possibility to add some more checks or modifications on the imported data: we could for example limit the values of intensity between 0 and 100%. Did you read the [License](./LICENSE)? # Miscellaneous, Set-up etc. Switch to user postgres with the following terminal command: ```bash su - postgres psql ``` In this psql terminal do: ```sql CREATE DATABASE composite_type_db ENCODING 'UTF8' LC_COLLATE='C' LC_CTYPE='C' template=template0 OWNER postgres; ``` this should reply with: ``` CREATE DATABASE ``` You can verify the list of present databases with typing `\l` and then exit with `\q` echo DATABASE_URL=postgres://username:password@localhost/diesel_demo > .env Create it with the diesel command (will create database if it didn't exist, but with your locale settings.): diesel setup composite_type_db --- ### Overview (`examples/postgres/custom_arrays/README.md`) # Custom Array and Data Types in Postgres Table of Content: 1. [Concepts](#concepts) 2. [The project](#the-project) 3. [Getting started](#getting-started) 4. [Postgres schema](#postgres-schema) 5. [Postgres Enum](#postgres-enum) 6. [Postgres custom type](#postgres-custom-type) 7. [Postgres SERIAL vs INTEGER primary key](#postgres-serial-vs-integer-primary-key) 8. [Postgres array with custom type](#postgres-array-with-custom-type) 9. [Rust Types](#rust-types) 10. [Additional methods](#additional-methods) 11. [Applied Best Practices](#applied-best-practices) 12. [Testing](#testing) In this guide, you learn more about the concepts listed below and illustrate the actual usage in Diesel with a sample project. ## Concepts: * Custom Enum * Custom type that uses a custom Enum * Array of custom types * Serialization / Deserialization of custom type array * Implementation of DB operations on a Rust Type ## The project: Your company decided to create a database of all microservices in its system; the high-level requirements are: * CRUD: Create, read, update, and delete a service in the database * List all offline services and all services that are online * Retrieve all API endpoints for a service Upon closer inspection, you realize a few missing details in the requirements: * There are many different endpoint types, some are http, some are gRPC, and some are even UDP (i.e. a message bus), but the number of endpoint types is limited to just three that haven’t changed in years. * While each service may have one or more endpoints, no two services can share the same API endpoints, which means normalizing API endpoints in a separate table makes little sense in this case. * Unmentioned in the requirements, a service depends on other services, and it would be great to test, before deploying a new service, if all of its dependencies are online. * Since this part of the database contains only meta-data, its best to store it in a separate schema to separate the metadata from all other data. Thinking further, you realize a few things: 1) Endpoint type can be expressed as an Enum in Postgres and in Rust 2) Endpoint can be a custom type in Postgres that matches a Rust type 3) Service contains an array of custom type Enum 4) When each service gets a unique ID that also serves as primary key, then service dependencies are basically just a collection of those unique service IDs. Thus, you just add an integer array in Postgres. 5) Checking all dependencies of a service is then as simple as loading the array of ID’s, iterating over it, check each dependency if its online, and you are basically done with the core requirement. As the old saying goes, if you can solve a problem with data structures, by all means, just do it. ## Getting started Let’s crate a new crate, called custom_arrays: ` cargo new custom_arrays –lib ` Next, run the Diesel setup: ```bash diesel setup ``` And then generated a new Diesel migration called services: ```bash diesel migration generate services ``` This creates a new folder within the migration folder containing an empty up.sql and down.sql file. ## Postgres schema Since the service management operates independently from the rest of the system, it is sensible to store all data in a dedicated schema. A schema in Postgres is like a namespace as it ensures unique table and type names within the schema. More importantly, you can create a new user that specifically has access to only a particular schema and that is always a good security practice. To create a new schema with Diesel, you follow three steps: 1) Declare the schema name in the diesel.toml file 2) Declare the schema in the migration up/down.sql files 3) Add the schema to the SQL type as annotations Postgres uses internally a schema as something like a search path for a table and, by default, searches in the public schema for a table. If that fails, Postgres returns an error that the table does not exists (in the default schema). Because of the search path mechanism, you only have to declare the schema name and consistently prefix the tables in the schema with the schema name and that’s it. In your diesel.toml file, add the following entry: ```toml [print_schema] file = "src/schema.rs" custom_type_derives = ["diesel::query_builder::QueryId", "Clone"] schema = "smdb" ``` Specifically, in the created migration folder, add the following to your up.sql file ```sql -- Your SQL goes here CREATE SCHEMA IF NOT EXISTS smdb; ``` Also, add the corresponding drop operation in the down.sql file. ```sql DROP SCHEMA IF EXISTS smdb; ``` ## Postgres Enum The company only uses three types of API endpoints, gRPC, http, or UDP. However, because data entry or transmission errors happen, an UnknownProtocol has been added to catch everything else that may go wrong to ensure that a potential serialization or deserialization bug does not crash the system. Instead, every once a while a Cron job runs over the database and searches for those UnknownProtocol entries, reports it to the admin who may fixes the incorrect entries. Therefore the Postgres ENUM in your up.sql looks like this: ```sql -- Your SQL goes here CREATE SCHEMA IF NOT EXISTS smdb; CREATE TYPE smdb.protocol_type AS ENUM ( 'UnknownProtocol', 'GRPC', 'HTTP', 'UDP' ); ``` Notice the schema prefix before the Enum name. Add the corresponding drop type operation to your down.sql file: ```sql DROP TYPE IF EXISTS smdb.protocol_type CASCADE; DROP schema IF EXISTS smdb; ``` ## Postgres custom type The service endpoint in this case is modelled as a custom type. There are few cases when you want to choose a custom type over a table: 1) The data have no relation to data in other tables. 2) You want to group a set of fields i.e. to store a complex configuration file with nested fields. 3) You specifically don’t want relations because of specific requirements. The service endpoint is an example of the first two cases. For once, the endpoints have no relation to any other data therefore a separate table makes little sense. Secondly, the entire service metadata database really is more of a configuration store and, in a way, the service table really is just one complex configuration with nested fields. Also, worth mentioning, if you were to store endpoints in a separate table, then you would have to deal with resolving relations during query time and that is probably a bit too much for just loading a configuration. Rather, when using the custom type, you just access a field that is basically a tuple. With that out of the way, your endpoint type looks like this in your up.sql: ```sql -- Your SQL goes here CREATE TYPE smdb.service_endpoint AS ( "name" Text, "version" INTEGER, "base_uri" Text, "port" INTEGER, "protocol" smdb.protocol_type ); ``` Again, all custom types are prefixed with the custom schema. And the matching drop operation in the down.sql file: ```sql DROP TYPE IF EXISTS smdb.service_endpoint CASCADE; DROP TYPE IF EXISTS smdb.protocol_type CASCADE; DROP schema IF EXISTS smdb; ``` ## Postgres SERIAL vs INTEGER primary key There is an important detail you must decide upfront: Internal or External Primary key? External primary key refers to the idea that you designate primary keys outside of Postgres. Let’s think this through: ### Internal primary key: SERIAL type When you only need a primary key as an index, you just use the SERIAL and Postgres gives your data an automatically incrementing integer primary key. You usually can return the primary key value after inserting so if you need to know the key, you do get it after insert. ### External primary key: INTEGER type In case you need to know the specific value of the primary key and you need to know it before inserting the data, you have to assign unique primary keys before inserting data and you have to use the INTEGER type (or any of the many integer variations) to convey to Postgres that you set the primary key yourself. Notice, you still have to set the NOT NULL and PRIMARY KEY attribute to ensure data consistency. In case of the microservice database, I am afraid, you have to use external primary keys because, remember, a service depends on other services. In order to insert a service that depends on any other service, regardless of whether it has already been inserted or not, you have to know the service ID upfront. With that out of the way, let’s define the service table. ## Postgres array with custom type To define the service table, the add the following to your up.sql file ```sql CREATE TABLE smdb.service( "service_id" INTEGER NOT NULL PRIMARY KEY, "name" Text NOT NULL, "version" INTEGER NOT NULL, "online" BOOLEAN NOT NULL, "description" Text NOT NULL, "health_check_uri" Text NOT NULL, "base_uri" Text NOT NULL, "dependencies" INTEGER[] NOT NULL, "endpoints" smdb.service_endpoint[] NOT NULL ); ``` Add the matching drop statement to your down.sql file: ```sql -- This file should undo anything in `up.sql` DROP TABLE IF EXISTS smdb.service; DROP TYPE IF EXISTS smdb.service_endpoint CASCADE; DROP TYPE IF EXISTS smdb.protocol_type CASCADE; DROP SCHEMA IF EXISTS smdb; ``` A few notes on the service table: * All types and tables are prefixed with the custom schema to give Postgres a hint where to find these types and tables. * The array type follows the usual convention schema.type[]. * The NOT NULL attribute means that the array itself must be set; Values inside the array still might be null. The Diesel team decided that array values are nullable by default because there so many things that may go wrong when dealing with array values thus making them nullable by default prevents a few potential crashes. Conversely, it also means you have to unwrap option types whenever you access data deserialized from an array. In total, the up.sql looks as below: ```sql -- Your SQL goes here CREATE SCHEMA IF NOT EXISTS smdb; CREATE TYPE smdb.protocol_type AS ENUM ( 'UnknownProtocol', 'GRPC', 'HTTP', 'UDP' ); CREATE TYPE smdb.service_endpoint AS ( "name" Text, "version" INTEGER, "base_uri" Text, "port" INTEGER, "protocol" smdb.protocol_type ); CREATE TABLE smdb.service( "service_id" INTEGER NOT NULL PRIMARY KEY, "name" Text NOT NULL, "version" INTEGER NOT NULL, "online" BOOLEAN NOT NULL, "description" Text NOT NULL, "health_check_uri" Text NOT NULL, "base_uri" Text NOT NULL, "dependencies" INTEGER[] NOT NULL, "endpoints" smdb.service_endpoint[] NOT NULL ); ``` Now, it’s time to run the Diesel migration: ```bash diesel migration run ``` You may use a database console to double check if all types and tables have been created inside the custom schema. If a type somehow appears in the public default schema, double check if the type definition in up.sql has been prefixed with the schema name. The Diesel migration generates the following schema.rs file in the src root: ```rust // @generated automatically by Diesel CLI. pub mod smdb { pub mod sql_types { #[derive(diesel::query_builder::QueryId, Clone, diesel::sql_types::SqlType)] #[diesel(postgres_type(name = "service_endpoint", schema = "smdb"))] pub struct ServiceEndpoint; } diesel::table! { use diesel::sql_types::*; use super::sql_types::ServiceEndpoint; smdb.service (service_id) { service_id -> Int4, name -> Text, version -> Int4, online -> Bool, description -> Text, health_check_uri -> Text, base_uri -> Text, dependencies -> Array>, endpoints -> Array>, } } } ``` Notice, the Postgres types are stored in Postgres therefore you are not seeing them in the generated schema. Only tables will show up in the generates schema. Furthermore, you will need a wrapper struct to map from Rust to the Postgres type. For the ServiceEndpoint, Diesel already generated a matching zero sized SQL type struct with the correct annotations. The service table then uses that SQL type `ServiceEndpoint` instead of the native Postgres type to reference the custom type in the endpoints array. You want to attach the generated schema.rs to your lib file to access it from within your crate and stop your IDE from showing related warnings Next, you want to create a folder model with a mod file in it attached to your lib file. This is your place to store all Rust type implementations matching all the generated Postgres types and tables. Lastly, you also want to add a type alias for a postgres database connection in the lib file because that gives you the freedom to swap between a normal single connection or a connection pool without updating your type implementation or tests. The connection pool type alias has been uncommented, but the type signature of the pooled connection makes it obvious why a type alias is a good idea. At this point, your lib file looks as shown below: ```rust mod schema; pub mod model; // Alias for a pooled connection. // pub type Connection = diesel::r2d2::PooledConnection>; // Alias for a normal, single, connection. pub type Connection = PgConnection; ``` ## Rust Types In total, you need implement three Rust types: * Enum: ProtocolType * Struct: Endpoint * Struct: Service ### Rust Enum: ProtocolType Diesel needs a zero sized SQL type struct to represent a custom Enum in Postgres, so let’s add one: ```rust #[derive(SqlType)] #[diesel(postgres_type(name = "protocol_type", schema = "smdb"))] pub struct PgProtocolType; ``` It is important to add the database schema ("smdb") and type name ("protocol_type") otherwise Postgres fails to find the type and aborts an operation on that type. Next, let’s define the actual Enum. Bear in mind to use the SQL type struct as sql_type on the Enum: ```rust #[derive(Debug, Clone, FromSqlRow, AsExpression, PartialEq, Eq)] #[diesel(sql_type = PgProtocolType)] pub enum ProtocolType { UnknownProtocol, GRPC, HTTP, UDP, } ``` It's important to have a [`#[derive(FromSqlRow)]`](https://docs.diesel.rs/master/diesel/deserialize/derive.FromSqlRow.html) for loading values of this enum from the database and it's important to have a [`#[derive(AsExpression)]`](https://docs.diesel.rs/master/diesel/expression/derive.AsExpression.html) to send values of this enum to the database. It’s worth pointing out that you are dealing with three types in total: 1) protocol_type: The Postgres Enum type, defined in your SQL migration 2) PgProtocolType: The SQL type struct that refers to the Postgres Enum type 3) ProtocolType: The Rust Enum that maps to the SQL type struct PgProtocolType Mixing any of those three types will result in a complicated Diesel trait error. However, these error messages are just a convoluted way to say that the database type mismatches the Rust type. When you encounter a consulted trait error message, make sure to check: 1) Do I have a SQL type struct? 2) Does my a SQL type struct derive SqlType? 3) Does my SQL type has a `#[diesel(postgres_type(_)]` attribute declared? 4) Is the `#[diesel(sql_type = _)]` attribute referring to the correct Postgres type? If all those checks pass and you still see errors, it’s most likely a serialization error. To serialize and deserialize a custom Enum, you write a custom `ToSql` and `FromSql` implementation. [`ToSql`](https://docs.diesel.rs/master/diesel/serialize/trait.ToSql.html) describes how to serialize a given rust type (Self) as sql side type (first generic argument) for a specific database backend (second generic argument). It needs to translate the type into the relevant wire protocol in that case. For postgres/mysql enums that's just the enum value as ascii string, but in general it's depended on the database + type. Also, it is important to end the implementation with Ok(IsNull::No). ```rust impl ToSql for ProtocolType { fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result { match *self { ProtocolType::UnknownProtocol => out.write_all(b"UnknownProtocol")?, ProtocolType::GRPC => out.write_all(b"GRPC")?, ProtocolType::HTTP => out.write_all(b"HTTP")?, ProtocolType::UDP => out.write_all(b"UDP")?, } Ok(IsNull::No) } } ``` [`FromSql`](https://docs.diesel.rs/master/diesel/deserialize/trait.FromSql.html) describes how to deserialize a given rust type (Self) as sql side type (first generic argument) for a specific database backend (second generic argument). It need to translate from the relevant wire protocol to the rust type. For postgres/mysql enums that just means matching on the as ascii string, but in general it's depended on the database + type. ```rust impl FromSql for ProtocolType { fn from_sql(bytes: PgValue<'_>) -> deserialize::Result { match bytes.as_bytes() { b"UnknownProtocol" => Ok(ProtocolType::UnknownProtocol), b"GRPC" => Ok(ProtocolType::GRPC), b"HTTP" => Ok(ProtocolType::HTTP), b"UDP" => Ok(ProtocolType::UDP), _ => Err(format!( "Unrecognized enum variant: {:?}", String::from_utf8_lossy(bytes.as_bytes()) ) .into()), } } } ``` In the `FromSql`, it is important to add error handling for unknown Enum variants to catch errors that may result from incorrect database updates. ### Rust Struct Endpoint Conceptually Diesel supports custom record types the same way as custom enums (and any other custom type mapping). You need to handle the same three types: * The `service_endpoint` type in your SQL migration * The `ServiceEndpoint` type in your schema.rs file, representing the SQL side type * The `Endpoint` type holding the rust side values. The details of the implementation differ slightly due to differences how these types are represented by postgresql. ```rust #[derive(Debug, Clone, FromSqlRow, AsExpression, PartialEq, Eq)] #[diesel(sql_type = crate::schema::smdb::sql_types::ServiceEndpoint)] pub struct Endpoint { pub name: String, pub version: i32, pub base_uri: String, pub port: i32, pub protocol: ProtocolType, } ``` It is worth mentioning is that the `#[diesel(sql_type = _)]` refers to the sql side type generated by Diesel and stored in the schema.rs file. Keep this in mind if you ever refactor the schema.rs file into a different folder because the macro annotation checks the path during compilation and throws an error if it cannot find the type in the provided path. The `FromSql` and `ToSql` implementation of a custom record type is not as obvious as the Enum because, internally, Postgres represent a custom type as an anonymous typed tuple. Therefore, you have to map a struct to a typed tuple and back. Let’s start with the ToSql implementation to store the Rust Endpoint struct as typed tuple. Luckily, Diesel provides a [helper util](https://docs.diesel.rs/master/diesel/serialize/trait.WriteTuple.html) to do just that. ```rust impl ToSql for Endpoint { fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result { serialize::WriteTuple::<(Text, Integer, Text, Integer, PgProtocolType)>::write_tuple( &( &self.name, &self.version, &self.base_uri, &self.port, &self.protocol, ), &mut out.reborrow(), ) } } ``` I cannot stress enough that it is paramount that the tuple type signature must match exactly the Postgres type signature defined in your up.sql. Ideally, you want to use the split view function in your IDE to have the up.sql in one pane and the the ToSql implementation in another pane, both side by side, to double check that the number and types match. If the type or number of types mismatch, you will get a compiler error telling you that somehow either the number of fields don’t match or that the type of the fields don’t match. The `FromSql` reverses the process by converting a Postgres typed tuple back into a Rust struct. Again, Diesel provides a convenient helper to do so: ```rust impl FromSql for Endpoint { fn from_sql(bytes: PgValue<'_>) -> deserialize::Result { let (name, version, base_uri, port, protocol) = FromSql::, Pg>::from_sql(bytes)?; Ok(Endpoint { name, version, base_uri, port, protocol, }) } } ``` Similar to the serialization process, it is paramount that the type annotation match exactly the one used in ToSql and the type definition in up.sql. Debugging serialization issues in Diesel is relatively straight forward since the compiler usually points out the location of the issue and, often, the issue is a type mismatch that is relatively easy to fix. ### Rust Struct Service The service struct gets its serialization and deserialization implementation generated by Diesel so that saves some typing. On the other hand, it is a good practice to implement database operations on the actual type itself. The wisdom here is twofold. For once, you cannot separate the database operation from the type, therefore it makes sense to implement the operations on the type itself. This also hides implementation details in encapsulation, as [discussed in the book](https://doc.rust-lang.org/book/ch17-01-what-is-oo.html#encapsulation-that-hides-implementation-details). Second, you gain a single point of maintenance because, in case your table definition changes, you have to update the Rust type anyways and because Diesel is statically checked, the compiler will immediately point out where your operations need to be corrected to match the new type definition. The requirements stated we want CRUD operations, and type-based programming suggest creating a different type per operation. Because we use external primary keys, we don’t need an additional type for the delete operation, therefore, in total, we crate 3 service types: 1) Service: For Read 2) CreateService: For create operations 3) UpdateService: For update operations The Service and CreateService have identical fields. Also, both types require a primary key annotation in addition to the table name. For more details about inserts, refer to the official [all about inserts guide](https://diesel.rs/guides/all-about-inserts.html). The UpdateService, however, has each field wrapped into an option type. When the option type is Some, Diesel knows that this field needs updating. If the field is set to None, Diesel ignores it. For more details about updates, refer to the official [all about updates guide](https://diesel.rs/guides/all-about-updates.html) The relevant type declarations are: ```rust #[derive(Debug, Clone, Queryable, Selectable)] #[diesel(table_name = crate::schema::smdb::service, primary_key(service_id))] pub struct Service { pub service_id: i32, pub name: String, pub version: i32, pub online: bool, pub description: String, pub health_check_uri: String, pub base_uri: String, pub dependencies: Vec>, pub endpoints: Vec>, } #[derive(Debug, Clone, Queryable, Insertable)] #[diesel(table_name = crate::schema::smdb::service, primary_key(service_id))] pub struct CreateService { pub service_id: i32, // ... skipped pub endpoints: Vec>, } #[derive(Debug, Clone, Queryable, Insertable, AsChangeset)] #[diesel(table_name = crate::schema::smdb::service)] pub struct UpdateService { pub name: Option, pub version: Option, pub online: Option, pub description: Option, pub health_check_uri: Option, pub base_uri: Option, pub dependencies: Option>>, pub endpoints: Option>>, } ``` Next, let’s implement the CRUD operations on the service type. Remember the handy connection type alias defined earlier? This service implementation is the place to use it. ### **Create** ```rust impl Service { pub fn create(db: &mut Connection, item: &CreateService) -> QueryResult { insert_into(crate::schema::smdb::service::table) .values(item) .get_result::(db) } } ``` The insert into function needs the table as target and the value to insert. It is a common convention to return the inserted value so that the callsite can verify that the insert completed correctly. ### **Read** ```rust pub fn read(db: &mut Connection, param_service_id: i32) -> QueryResult { service .filter(service_id.eq(param_service_id)) .first::(db) } ``` Each service has one unique ID and therefore querying for a service ID returns either exactly one result or an error. That means, the read operation completes with capturing and returning the first result. In case you have data that may return multiple entries for a search key, you must change the return type of the read method to QueryResult> and use load instead of first, just as it shown in the read all method. ### **Read All** ```rust pub fn read_all(db: &mut Connection) -> QueryResult> { service.load::(db) } ``` Here, we just load everything from the service table and return the entire result as a vector. Note, it is expected that the service table only has relatively few entries. For tables that hold a large number of data, you can implement pagination, add limit clause, or some kind of filtering to reduce the number of returned values. ### **Update** ```rust pub fn update( db: &mut Connection, param_service_id: i32, item: &UpdateService, ) -> QueryResult { diesel::update(service.filter(service_id.eq(param_service_id))) .set(item) .returning(Service::as_returning()) .get_result(db) } ``` The update method, similar to insert, requires the target table and update values as argument and returns the updated service as result. Notice, the parameter is of type UpdateService to ensure compiler verification. ### **Delete** ```rust pub fn delete(db: &mut Connection, param_service_id: i32) -> QueryResult { diesel::delete(service.filter(service_id.eq(param_service_id))).execute(db) } ``` Delete is a standard function provided by Diesel. Notice, the filter searched over the primary key to ensure only one service with the matching ID gets deleted. However, if you were to filter over a non-unique attribute, you may end up deleting more data than you though you would. In that case, always run the corresponding SQL query in a SQL console to double check that your filter criterion returns exactly what you want it to return. With the CRUD methods implemented, it’s time to look at the more interesting part of the requirements. ## Additional methods In the requirement, it was stated that: “... a service depends on other services, and it would be great to test before deploying a new service, if all of its dependencies are online.” To do so we have to implement a method that sets a service online and another one to set it offline. By experience, a few non-trivial errors are caused by incorrectly set Boolean flags and whenever you can, hide Boolean flags in your public API and use a dedicated method instead to the set them. Next, we need a method to return true if a service is online. At this point, we also want to add a method that checks if a service actually is in the database. Imagine, you want to test if you can deploy a new service with, say 5 dependencies, and somehow the database returns no, you can’t. The obvious question is why? There might be some services that are offline, fair enough, just start them, but it might also be possible that somehow a service isn’t currently in the database and that is where you need another check method. Then, we want another method that takes a collection of service ID’s, checks all of them and returns either true if all services are online, or returns an error message that states which service is offline. Finally, let’s add another method that returns all services that are online and another one that returns all services that are currently offline. The latter is handy to quickly identify a deployment blocker. ### Set Service Online / Offline Let’s start with a private helper method that sets the Boolean online flag on a service in the database. ```rust fn set_svc_online( db: &mut Connection, param_service_id: i32, param_online: bool, ) -> QueryResult<()> { diesel::update(service.filter(service_id.eq(param_service_id))) .set(online.eq(param_online)) .execute(db)?; Ok(()) } ``` Here the generated DSL really shines as you can easily select a field and set it to a new value. Notice, the return type is void in case of success. Realize, if you were to return the updated Boolean flag, you would cause a non-trivial ambiguity on the call-site. For setting the Boolean flag true, the result would be true, which is correct, but there is also a risk of the return value being interpreted as the operation having succeeded. And that matters, because if you set the flag to false, the same method return false, which is also correct, but could be misunderstood as the operation didn’t completed because false was returned. A good API is mostly free of ambiguity and therefore, therefore you just return Ok(()) in case of success because there is no way to misunderstand a returned ok. Now, let’s add two public wrapper methods that set the flag to either true or false. ```rust pub fn set_service_online(db: &mut Connection, param_service_id: i32) -> QueryResult<()> { Self::set_svc_online(db, param_service_id, true) } pub fn set_service_offline(db: &mut Connection, param_service_id: i32) -> QueryResult<()> { Self::set_svc_online(db, param_service_id, false) } ``` ### Check Service Exists Next up, we add a method to check if service is in the database and the easiest way to implement this is to test if find returns a result or error. ```rust pub fn check_if_service_id_exists( db: &mut Connection, param_service_id: i32, ) -> QueryResult { Ok(service.find(param_service_id).first::(db).optional()?.is_some()) } ``` ### Check Service Online To check if a service is online, we again lean on the generated DSL: ```rust pub fn check_if_service_id_exists( db: &mut Connection, param_service_id: i32, ) -> QueryResult { service .find(param_service_id) .first::(db) .optional() .map(|arg0: Option| Option::is_some(&arg0)) } ``` ### Get Service Dependencies Notice, if we select dependencies instead of online, we return all the dependencies of a service. Let’s add another method to do just that: ```rust pub fn get_all_service_dependencies( db: &mut Connection, param_service_id: i32, ) -> QueryResult>> { service .filter(service_id.eq(param_service_id)) .select(dependencies) .first::>>(db) } ``` ### Get Service Endpoints And likewise, if we select endpoints, we get all API endpoints of a service: ```rust pub fn get_all_service_endpoints( db: &mut Connection, param_service_id: i32, ) -> QueryResult>> { service .filter(service_id.eq(param_service_id)) .select(endpoints) .first::>>(db) } ``` As an observation, when we replace the filter criterion with a test if a service is online, we get all services with online set to true and that is very easy to implement: ### Get All Online Services ```rust pub fn get_all_online_services(db: &mut Connection) -> QueryResult> { service .filter(online.eq(true)) .select(Service::as_returning()) .load::(db) } ``` ### Get All Offline Services Likewise, we use the same filter to find and return all services with online set to false. ```rust pub fn get_all_offline_services(db: &mut Connection) -> QueryResult> { service .filter(online.eq(false)) .select(Service::as_returning()) .load::(db) } ``` ### Check All Services Online Finally, we can implement the last method that takes a collection of service ID’s, checks all of them, and returns either true, or the service ID that is offline. ```rust pub fn check_all_services_online(db: &mut Connection, services: &[i32]) -> QueryResult<(bool, Option)> { for id in services { if !Service::check_if_service_id_online(db, *id)? { return Ok((false, Some(format!("Service {} is offline", id)))); } } Ok((true, None)) } ``` Here, it is a matter of taste to return a Boolean flag with an optional string or to encode the case of an offline service as a custom error. The rational for the Boolean flag is that any service might be offline for any reason so that doesn’t count as a database error whereas if the query would fail, that would amount to a database error. That said, the decision depends largely on the design requirements and if you already have custom errors defined, then adding a `ServiceDependencyOfflineError` variant should be straight forward. At this point, the Service type implementation is complete. ## Applied Best Practices Implementing the database operations on the Service type is simple, straight forward, and follows three best practices: 1) Take the database connection as a parameter 2) Prefer the generated DSL over custom queries whenever possible 3) Add type annotations on database return types ### Database connection as a parameter The first one refers to the idea that no type should hold application state, and a database connection definitely counts as application state. Instead, you would write a database connection manager that manages a Postgres connection pool, and then calls into the database methods implemented in the service type giving it a pooled connection as parameter. ### Prefer the generated DSL over custom queries The second one refers to the idea that non-complex queries are usually easier and more composable expressed in the generated DSL than in custom SQL queries and therefore the DSL is preferred in that case. However, if you have to write complex transactions, there is a chance the DSL isn’t cutting it, so in that scenario you may end up writing custom SQL. That said, the DSL gets you a long way before you are writing custom SQL. ### Add type annotations The last one, type annotation on database return types, isn’t that obvious because Rust can infer the database return type from the method return type, so why bother? Why would you write get_result::(db) instead of just get_result(db) you may wonder? Here is the big secret, type inference in Rust needs occasionally a hint. When you annotated the database return type and then wants to daisy chain another operation via `map` or `flat_map`, without the type annotation you get a compile error. If you have the type annotation in place, you can add more operations whenever you want and things compile right away. To illustrate the last point, the count operator in Postgres only returns an i64 integer. Suppose your application expects an u64 from the persistence API. Wouldn’t it be nice to make the conversion on the fly? Let’s take a closer look. Conventionally, you write the count operation like so: ```rust pub fn count(db: &mut Connection) -> QueryResult { service.count().get_result::(db) } ``` Notice, the get result has a type annotation for i64. If you remove it, the code still compiles. But leave it there for a moment. Let’s add a map operation that takes an i64 returned from the count query and converts it into an u64. Notice, we have to update the return type of the method as well. ```rust pub fn count_u64(db: &mut Connection) -> QueryResult { service.count() .get_result::(db) .map(|c| c as u64) } ``` Run the compile and see that it works. Now, if you were to remove the type annotation from get_result, you get a compile error saying that trait `FromSql` is not implemented. The type annotation is required because there can be more than one `FromSql` impl for the same rust type and there can be more than one FromSql type for the same sql type. That gives a lot flexibility how you actually map data between your database and your rust code, but it requires explicit type annotation. If you write a database layer for an existing system, this technique comes in handy as you can seamlessly convert between Diesel DB types and your target types while leaving your target types as it. And because you never know when you have to do this, it’s generally recommended to add type annotations to DB return types. ## Testing Diesel enables you to test your database schema and migration early on in the development process. To do so, you need only meed: * A util that creates a DB connection * A util that runs the DB migration * And your DB integration tests ### DB Connection Types Broadly speaking, there are two ways to handle database connection. One way is to create one connection per application and use it until the application shuts down. Another way is to create a pool of connection and, whenever a database connection is needed for an DB operation, a connection is taken from the pool and after the DB operation has been completed, the connection is returned to the pool. The first approach is quite common in small application whereas the second one is commonly used in server application that expected consistent database usage. **Important** Using database connection pool in Diesel requires you to enable the `r2d2` feature in your Cargo.toml file. In Diesel, you can handle connections either way, but the only noticeable difference is the actual connection type used as type parameter. For that reason, a type alias for the DB connection was declared in the lib.rs file because that would allow you to switch between a single connection and a pooled connection without refactoring. ### DB Connection Test Util Let's start with a small util that returns a simple DB connection. First you need to get a database URI from somewhere, then construct a connection, and lastly return the connection. Remember, this is just a test util so there is no need to add anything more than necessary. ```rust use dotenvy::dotenv; fn postgres_connection() -> PgConnection { dotenv().ok(); let database_url = env::var("PG_DATABASE_URL") .or_else(|_| env::var("DATABASE_URL")) .expect("PG_DATABASE_URL must be set"); PgConnection::establish(&database_url) .unwrap_or_else(|e| panic!("Failed to connect, error: {}", e)) } ``` Here we use the dotenvy crate to test if there is an environment variable of either DATABASE_URL or PG_DATABASE_URL and if so, parse the string and use it to establish a connection. Therefore, make sure the PG_DATABASE_URL is set correctly. ### DB Migration Util Diesel can run a database migration in one of two ways. First, you can use the Diesel CLI in your terminal to generate, run, or revert migrations manually. This is ideal for development when you frequently change the database schema. The second way is programmatically via the embedded migration macro, which is ideal to build a single binary with all migrations compiled into it so that you don't have to install and run the Diesel CLI on the target machine. This simplifies deployment of your application and streamlines continuous integration testing. **Important** You must add the crate `diesel_migrations` to your cargo.toml and set the target database as feature flag to enable the embedded migration macro. To serve both purposes, deployment and CI testing, let's add a new function `run_db_migration` to the lib.rs file of the crate that takes a connection as parameter, checks if the DB connection is valid, checks if there are any pending migrations, and if so, runs all pending migrations. The implementation is straight forward, as you can see below: ```rust pub type Connection = PgConnection; pub const MIGRATIONS: EmbeddedMigrations = embed_migrations!("migrations"); pub fn run_db_migration( conn: &mut Connection, ) -> Result<(), Box> { // Check DB connection! match conn.ping() { Ok(_) => {} Err(e) => { eprint!("[run_db_migration]: Error connecting to database: {}", e); return Err(Box::new(e)); } } // Run all pending migrations. match conn.run_pending_migrations(MIGRATIONS) { Ok(_) => Ok(()), Err(e) => { eprint!("[run_db_migration]: Error migrating database: {}", e); Err(e) } } } ``` A few key points worth highlighting here: * The connection check is done to ensure that the DB connection is valid. * The `run_pending_migrations` methods checks if there are any pending migrations, and if so, runs them. Likewise, we add a function `revert_db_migration` to revert all Diesel migrations as defined in the `down.sql `file. If you don't have a `down.sql` file, you can skip this step. However, adding a proper `down.sql` for the revert migration function is particularly useful for testing purposes, as discussed in the next section. ```rust pub fn revert_db_migration( conn: &mut Connection, ) -> Result<(), Box> { // Check DB connection! if conn.ping().is_ok() { } else if let Err(e) = conn.ping() { eprint!("[pg_cmdb]: Error connecting to database: {}", e); return Err(Box::new(e)); } // Revert all pending migrations match conn.revert_all_migrations(MIGRATIONS) { Ok(_) => Ok(()), Err(e) => { eprint!("[pg_cmdb]: Error reverting database migrations: {}", e); Err(e) } } } ``` ### DB Integration Tests Conventionally, Database integration tests become flaky when executed in parallel usually because of conflicting read / write operations. While modern database systems can handle concurrent data access, test tools with assertions not so much. That means, test assertions start to fail seemingly randomly when executed concurrently. There are only very few viable options to deal with this reality: * Don't use parallel test execution * Form isolated test groups that run in parallel, but tests in each group run sequentially. In practice, the second option is often used by grouping tables into isolated test groups. Suppose your database has 25 tables you are tasked to test. Some of them are clearly unrelated, others only require read access to some tables, and then you have those where you have to test for multi-table inserts and updates. Say, you can form 7 groups of tables that are clearly independent of each other, then you can run those 7 test groups in parallel, but for all practical purpose within each group, all tests are run in sequence unless you are testing specifically for concurrent read write access. You want to put those test into a dedicated test group anyways as they capture errors that are more complex than errors from your basic integration tests. And it makes sense to stage integration tests into simple functional tests, complex workflow tests, and chaos tests that triggers read / write conflicts randomly to test for blind spots. In any case, the test folder contains an example of this testing style in the single_service_test file. ```rust #[test] fn test_service() { let mut connection = postgres_connection(); let conn = &mut connection; println!("Run DB migration"); run_db_migration(conn); println!("Test create!"); test_create_service(conn); println!("Test count!"); test_count_service(conn); //... println!("Revert DB migration"); revert_db_migration(conn); } ``` The idea here is simple yet powerful: There is just one Rust test so regardless of whether you test with Cargo, Nextest or any other test util, this test will run everything within it in sequence. The print statements are usually only shown if a test fails. However, there is one important details worth mentioning, the assert macro often obfuscates the error message and if you have ever seen a complex stack trace full of fnOnce invocations, you know that already. To get a more meaningful error message, just uncomment the dbg! statement that unwraps the result before the assertion and you will see a helpful error message in most cases. Diesel, however, offers an easier way to do to parallel integration tests by leaning on a test transaction. Conventionally, a transaction follows a the [two phase commit protocol](https://en.wikipedia.org/wiki/Two-phase_commit_protocol). In stage one, a commit request is send to the database to take the necessary steps for either committing or aborting the transaction and test if a commit would succeed. In stage two, the transaction is then committed or aborted depending on whether any conflicts were detected in stage one. A test transaction initiates the first stage, prepares everything ready to commit, and then just aborts the transaction at the end of a test. Diesel uses this technique extensively for it's own test-suite. What that means is that you perform the following steps: * Open a new connection per test * Directly start a new transaction (Diesel provides the Connection::begin_test_transaction function for this) * Perform any test code (including schema setup for postgres) * Rollback the transaction With that you end up with each test does not see the changes from any other tests. The test folder contains an example of this testing style in the parallel_service_test file. ```rust #[test] fn test_create_service() { test_setup(); let mut connection = postgres_connection(); let conn = &mut connection; conn.begin_test_transaction() .expect("Failed to begin test transaction"); let service = get_crate_service(); let endpoints = get_endpoints(); let dependencies = get_dependencies(); let result = service::Service::create(conn, &service); // dbg!(&result); assert!(result.is_ok()); // ... } ``` Here, the test setup runs first to test if the database connection works and all migrations are in place. Also, noticed, each test gets its own database connection. After the connection has been established, the test transaction is started next. From there, the test code is executed. Because each test runs in a separate transaction, you have to apply first principle to each test. For example, if you want to test to update a service, you have to insert a service first, then update it, and then test if the update was successful. Because each test transaction aborts after each test, there is no left over data to worry about after each test. Furthermore, since PostgreSQL executes transactions in isolation, regardless of whether these are committed or not, no two tests can interfere with each other. Lastly, testing this way is very fast when executed in parallel. --- ### Overview (`examples/sqlite/embedded/README.md`) # Running Diesel + Sqlite on an embedded target This example project assumes that you use an [`esp32-c6`](https://www.espressif.com/en/products/socs/esp32-c6). For other embedded targets you need to make adjustments to fit the project to your target platform. Please checkout the documentation of the corresponding hal crate to get information about the setup for your microcontroller. Diesel supports running in a no-std mode. For this you need to add diesel like this to your project: ```toml diesel = { version = "2.3.0", path = "../../../diesel/", default-features = false, features = ["sqlite", "hashbrown"] } ``` The important point here are: * Disabling default features to disable the `std` feature * Only the `sqlite` backend is supported for embedded targets * Enabling the `hashbrown` feature, otherwise diesel cannot be compiled You also need to add `libsqlite3-sys` to your cargo toml to enable the bundled build feature: ``` toml libsqlite3-sys = { version = "0.35.0", features = ["bundled"] } ``` Finally you need to depend on `tinyrlibc` (or a similar crate) to provide some libc functions required by libsqlite3: ```toml tinyrlibc = "0.5" ``` To compile diesel + the sqlite library in no-std mode you need to perform the following steps: * Get a cross compiler for your target, for the `esp32-c6` you can get it from [here](https://github.com/riscv-collab/riscv-gnu-toolchain) * Set `TARGET_CC` to point to the cross compiler. For the `esp32-c6` this needs to be set to `riscv32-unknown-elf-gcc` (or the fully qualified path to that binary) * Set `LIBSQLITE3_FLAGS` to configure building libsqlite for an embedded target. This need to be set to `-DSQLITE_SMALL_STACK=1 -DSQLITE_THREADSAFE=0 -DSQLITE_OS_OTHER=1 -DSQLITE_OMIT_LOCALTIME=1 -USQLITE_ENABLE_FTS3 -UQLITE_ENABLE_DBSTAT_VTAB -UDSQLITE_ENABLE_JSON1 -USQLITE_ENABLE_FTS5 -USQLITE_ENABLE_STAT4 -DSQLITE_ENABLE_MEMSYS3=1` or similar. You application needs to use the `alloc` crate and provide an allocator implementation as Diesel requires support for allocations. Finally your application needs to provide a `sqlite3_os_init()` function to configure SQLite for the embedded target. This function needs to register at least one VFS. For demonstration purposes you can use `rsqlite-vfs = "0.2"` with the `EmbeddedVfs` callbacks defined in `src/main.rs`: ``` rust #[unsafe(no_mangle)] extern "C" fn sqlite3_os_init() -> core::ffi::c_int { unsafe { rsqlite_vfs::memvfs::install(EmbeddedVfs, true).unwrap(); } libsqlite3_sys::SQLITE_OK } ``` --- ### Overview (`examples/sqlite/getting_started_step_1/README.md`) # `rs-diesel-sqlite` Diesel's `Getting Started` guide using SQLite instead of Postgresql ## Usage ``` $ echo "DATABASE_URL=file:test.db" > .env $ diesel migration run $ cargo run --bin show_posts ``` --- ### Overview (`examples/sqlite/getting_started_step_2/README.md`) # `rs-diesel-sqlite` Diesel's `Getting Started` guide using SQLite instead of Postgresql ## Usage ``` $ echo "DATABASE_URL=file:test.db" > .env $ diesel migration run $ cargo run --bin show_posts $ cargo run --bin write_post # write your post ``` --- ### Overview (`examples/sqlite/getting_started_step_3/README.md`) # `rs-diesel-sqlite` Diesel's `Getting Started` guide using SQLite instead of Postgresql ## Usage ``` $ echo "DATABASE_URL=file:test.db" > .env $ diesel migration run $ cargo run --bin show_posts $ cargo run --bin write_post # write your post $ cargo run --bin publish_post 1 $ cargo run --bin show_posts # your post will be printed here # Delete post with given title $ cargo run --bin delete_post "title of post to delete" $ cargo run --bin show_posts # observe that no posts are shown ``` --- ### Overview (`examples/sqlite/wasm/README.md`) # `rs-diesel-sqlite` Diesel's `Getting Started` guide using SQLite instead of Postgresql ## Usage Compile wasm and start the web server: ``` rustup target add wasm32-unknown-unknown # Add wasm32-unknown-unknown toolchain cargo install wasm-bindgen-cli --locked # Install the wasm-bindgen cli cargo build --target wasm32-unknown-unknown # Build wasm wasm-bindgen ../../../target/wasm32-unknown-unknown/debug/sqlite_wasm_example.wasm --out-dir pkg --web # bindgen python3 server.py # Start server ``` Next, try it on the web page: [on the web page](http://localhost:8000) --- --- METRICS --- - Files Extracted: 13 - Estimated Token Budget: ~20944 tokens - Canonical Reference: https://codewiki.google/github.com/diesel-rs/diesel