### Setup WASM Target and Toolchain

Source: https://github.com/diesel-rs/diesel/blob/main/examples/sqlite/wasm/README.md

Installs the necessary target for WebAssembly compilation and the wasm-bindgen CLI.

```bash
rustup target add wasm32-unknown-unknown
# Add wasm32-unknown-unknown toolchain


```

```bash
cargo install wasm-bindgen-cli --locked
# Install the wasm-bindgen cli


```

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

### Install PostgreSQL client library on macOS

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/backend_installation.md

Installs PostgreSQL using Homebrew on macOS.

```bash
brew install postgresql
```

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

### Install PostgreSQL client library on CentOS/Fedora

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/backend_installation.md

Installs the PostgreSQL development library on CentOS/Fedora systems.

```bash
sudo yum install postgresql-devel
```

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

### Install MySQL client library on macOS

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/backend_installation.md

Installs MySQL using Homebrew on macOS.

```bash
brew install mysql
```

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

### Install SQLite client library on Arch

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/backend_installation.md

Installs the SQLite package on Arch Linux.

```bash
sudo pacman -Su sqlite
```

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

### Install PostgreSQL client library on Debian/Ubuntu

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/backend_installation.md

Installs the PostgreSQL development library on Debian-based systems.

```bash
sudo apt-get install libpq-dev
```

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

### Start Development Server

Source: https://github.com/diesel-rs/diesel/blob/main/examples/sqlite/wasm/README.md

Launches a simple Python HTTP server to serve the web application.

```bash
python3 server.py
# Start server
```

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

### Run Diesel Setup Command

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/composite_types/README.md

Executes the `diesel setup` command to create the database (if it doesn't exist) and set up the necessary schema based on the Diesel configuration. This command is essential for initializing the project's database.

```bash
diesel setup
```

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

### SQLite VFS Initialization for Embedded Targets

Source: https://github.com/diesel-rs/diesel/blob/main/examples/sqlite/embedded/README.md

Implement the `sqlite3_os_init` function to configure SQLite for embedded use. This example demonstrates installing the `sqlite_memvfs` crate to register a VFS.

```rust
#[unsafe(no_mangle)]
extern "C" fn sqlite3_os_init() -> core::ffi::c_int {
    sqlite_memvfs::install();
    libsqlite3_sys::SQLITE_OK
}

```

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

### Install SQLite client library on CentOS/Fedora

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/backend_installation.md

Installs the SQLite development library on CentOS/Fedora systems.

```bash
sudo yum install sqlite-devel
```

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

### Install MySQL client library on Arch

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/backend_installation.md

Installs the MySQL package on Arch Linux.

```bash
sudo pacman -Su mysql
```

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

### Install MySQL client library on Debian/Ubuntu

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/backend_installation.md

Installs the MySQL development client library on Debian-based systems.

```bash
sudo apt-get install libmysqlclient-dev
```

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

### Install PostgreSQL client library on Arch

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/backend_installation.md

Installs the PostgreSQL package on Arch Linux.

```bash
sudo pacman -Su postgresql
```

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

### Install SQLite client library on Debian/Ubuntu

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/backend_installation.md

Installs the SQLite development library on Debian-based systems.

```bash
sudo apt-get install libsqlite3-dev
```

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

### Basic Diesel CLI Setup and Migration Generation

Source: https://github.com/diesel-rs/diesel/blob/main/diesel_cli/README.md

Demonstrates the initial steps for using the Diesel CLI: setting up the database connection and generating a new migration file. Ensure the DATABASE_URL is configured.

```sh
cargo install diesel_cli
diesel setup --database-url='postgres://localhost/my_db'
diesel migration generate create_users_table
```

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

### Start Docker Compose Services

Source: https://github.com/diesel-rs/diesel/blob/main/CONTRIBUTING.md

Command to start services defined in a docker-compose.yml file.

```bash
$ docker-compose up

```

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

### Install Diesel CLI with Bundled SQLite

Source: https://github.com/diesel-rs/diesel/blob/main/diesel_cli/README.md

Install the Diesel CLI with a bundled version of SQLite, which is helpful for systems like Windows where installing SQLite directly might be difficult.

```shell
cargo install diesel_cli --no-default-features --features "sqlite-bundled"
```

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

### Run Example Cargo Command

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/composite_types/README.md

Compiles and runs the `composite2rust_colors` example using Cargo. This command executes the Rust code that demonstrates the integration with PostgreSQL composite types.

```bash
cargo run --example composite2rust_colors
```

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

### Install MySQL client library on CentOS/Fedora

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/backend_installation.md

Installs the MySQL development client library on CentOS/Fedora systems.

```bash
sudo yum install mysql-devel
```

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

### Install Rustfmt and Clippy

Source: https://github.com/diesel-rs/diesel/blob/main/CONTRIBUTING.md

Installs the rustfmt and clippy components for Rust development. These tools help enforce code style and catch potential errors.

```bash
rustup component add rustfmt
rustup component add clippy

```

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

### Install Diesel CLI with Specific Features

Source: https://github.com/diesel-rs/diesel/blob/main/diesel_cli/README.md

Install the Diesel CLI, specifying which database drivers to include using feature flags. This is useful for systems with specific dependency requirements or for omitting unused drivers.

```sh
cargo install diesel_cli --no-default-features --features "postgres sqlite mysql"
```

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

### Diesel CRUD Operations Example

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/trait_derives.md

Demonstrates inserting, loading, and updating user records using Diesel. Shows different update strategies for handling optional fields.

```rust
// File: src/main.rs
#[macro_use] extern crate diesel;

use diesel::prelude::*;

fn main() {
    let new_user = NewUser {
        first_name: "Gordon",
        last_name: "Freeman",
        email: "gordon.freeman@blackmesa.co",
    };

    diesel::insert_into(users::table)
        .values(&new_user)
        .execute(&db_connection)
        .expect("Error inserting row into database");

    let all_users = users.load::<User>(&db_connection)
        .expect("Error loading users");

    println!("User count: {}", all_users.len());
    //=> User count: 1

    // Querying User
    let hero = users.first::<User>(&db_connection)
        .expect("Error loading first user");

    // Update scenario 1
    let ignore_fields_update = IgnoreNoneFieldsUpdateUser {
        first_name: "Issac",
        last_name: "Kleiner",
        email: None, // Field to be ignored when updating
    }

    diesel::update(&hero).set(&ignore_fields_update)
        .execute(&db_connection);

    let updated_hero = users.first(&db_connection)::<Users>
        .expect("Error loading first user");

    println!("Name: {} {} Email: {}", updated_hero.first_name,
        updated_hero.last_name,
        updated_hero.email.unwrap(),
    );

    // Output
    //=> Name: Issac Kleiner Email: gordon.freeman@blackmesa.ca

    // Update scenario 2
    let null_a_field_update = IgnoreNoneFieldsUpdateUser {
        first_name: "Issac",
        last_name: "Kleiner",
        email: Some(None), // Nulls the column in the DB
    }

    diesel::update(&hero).set(&null_a_field_update)
        .execute(&db_connection);

    let updated_hero = users.first::<User>(&db_connection)
        .expect("Error loading first user");

    println!("Name: {} {} Email: {}", updated_hero.first_name,
        updated_hero.last_name,
        updated_hero.email.unwrap_or("This field is now Nulled".to_string()),
    );

    // Output
    //=> Name: Issac Kleiner Email: This field is now Nulled

    // Update scenario 3
    let null_fields_update = NullNoneFieldsUpdateUser {
        first_name: "Eli",
        last_name: "Vance",
        email: None, // with option treat_none_as_null=true
    }

    diesel::update(&hero).set(&null_fields_update)
        .execute(&db_connection);

    let updated_hero = users.first::<User>(&db_connection)
        .expect("Error loading first user");

    println!("Name: {} {} Email: {:?}", updated_hero.first_name,
        updated_hero.last_name,
        updated_hero.email.unwrap_or("This is a Null value".to_string()),
    );

    // Output
    //=> Name: Eli Vance Email: This is a Null value
}
```

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

### Lib.rs Setup for Diesel Models and Connections

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/custom_arrays/README.md

Configures the lib file to include generated schema and model modules, and defines a type alias for database connections, supporting both single and pooled connections.

```rust
mod schema;
pub mod model;

// Alias for a pooled connection.
// pub type Connection = diesel::r2d2::PooledConnection<diesel::r2d2::ConnectionManager<diesel::pg::PgConnection>>;

// Alias for a normal, single, connection.
pub type Connection = PgConnection;
```

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

### Install Diesel CLI with SQLite only

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/backend_installation.md

Use this command to install the Diesel CLI with only SQLite support, disabling default features.

```bash
cargo install diesel_cli --no-default-features --features sqlite
```

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

### Define and Query Dynamic Table

Source: https://github.com/diesel-rs/diesel/blob/main/diesel_dynamic_schema/README.md

Example of defining a dynamic table and performing a query using its columns. Requires explicit select clause.

```rust
use diesel_dynamic_schema::table;

let users = table("users");
let id = users.column::<Integer, _>("id");
let name = users.column::<Text, _>("name");

users.select((id, name))
    .filter(name.eq("Sean"))
    .first(&conn)
```

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

### Get All Online Services

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/custom_arrays/README.md

Fetches all services that are marked as online. Returns a vector of Service objects.

```rust
    pub fn get_all_online_services(db: &mut Connection) -> QueryResult<Vec<Self>> {
        service
            .filter(online.eq(true))
            .select(Service::as_returning())
            .load::<Service>(db)
    }
```

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

### Insert New Users and Get Results

Source: https://github.com/diesel-rs/diesel/blob/main/diesel/README.md

Inserts multiple new user records and retrieves the inserted rows from the database. Uses `get_results` to return the newly inserted data as `User` structs.

```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 },
];

let inserted_users = insert_into(users)
    .values(&new_users)
    .get_results::<User>(&mut connection);
```

```sql
INSERT INTO users (name, hair_color) VALUES
  ('Sean', 'Black'),
  ('Gordon', DEFAULT)
  RETURNING *
```

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

### Inserting a Struct into the Database

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/trait_derives.md

Use the `.insert_into()` and `.values()` methods to insert a struct that implements `Insertable` into the database. This example simulates deserializing JSON into a `NewUser` struct and then inserting it.

```rust
// File: src/main.rs
#[macro_use] extern crate diesel;

use diesel::prelude::*;

fn main() {
    // Here we simulate a webform by deserializing a json string
    // into a NewUser struct.
    let new_user: NewUser = serde_json::from_str(
        r#"{
            "first_name": "Gordon",
            "last_name": "Freeman",
            "electronic_mail": "gordon.freeman@blackmesa.co"
        }"#).unwrap();

    diesel::insert_into(users::table)
        .values(&new_user)
        .execute(&db_connection)
        .expect("Error inserting row into database");

    let all_users = users.load::<User>(&db_connection)
        .expect("Error loading users");

    println!("User count: {}", all_users.len());
    //=> User count: 1
}
```

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

### Get All Service Dependencies

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/custom_arrays/README.md

Retrieves all dependencies for a given service ID. Returns a vector of optional integers representing dependency IDs.

```rust
    pub fn get_all_service_dependencies(
        db: &mut Connection,
        param_service_id: i32,
    ) -> QueryResult<Vec<Option<i32>>> {
        service
            .filter(service_id.eq(param_service_id))
            .select(dependencies)
            .first::<Vec<Option<i32>>>(db)
    }
```

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

### Generate Bash Completion Script for OS X (Homebrew)

Source: https://github.com/diesel-rs/diesel/blob/main/diesel_cli/README.md

Generates a bash completion script for the Diesel CLI on macOS using Homebrew. Ensure bash-completion is installed.

```sh
$ brew install bash-completion  # you may already have this installed
$ diesel completions bash > $(brew --prefix)/etc/bash_completion.d/diesel
```

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

### Create PostgreSQL Database

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/composite_types/README.md

Creates a new PostgreSQL database named `composite_type_db` with UTF8 encoding, owned by the `postgres` user. This is a prerequisite for running Diesel migrations and examples.

```sql
CREATE DATABASE composite_type_db ENCODING 'UTF8'  LC_COLLATE='C'  LC_CTYPE='C'  template=template0 OWNER postgres;
```

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

### Manual FromSql Implementation for Enum with Pg Backend

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/custom_types.md

Implement the `FromSql` trait to deserialize SQL data into a custom enum. This example uses the `diesel::pg::Pg` backend to convert byte slices to a `Language` enum.

```rust
use diesel::deserialize::{self, FromSql};
use diesel::pg::Pg;

impl FromSql<LanguageType, Pg> for Language {
    fn from_sql(bytes: Option<&<Pg as Backend>::RawValue>) -> deserialize::Result<Self> {
        match not_none!(bytes) {
            b"en" => Ok(Language::En),
            b"ru" => Ok(Language::Ru),
            b"de" => Ok(Language::De),
            _ => Err("Unrecognized enum variant".into()),
        }
    }
}
```

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

### Set Up .env and Run Migrations

Source: https://github.com/diesel-rs/diesel/blob/main/examples/sqlite/getting_started_step_2/README.md

Configure the database connection URL in the .env file and then execute database migrations.

```bash
$ echo "DATABASE_URL=file:test.db" > .env
$ diesel migration run
```

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

### Insert, Query, and SQL Query Execution

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/trait_derives.md

Demonstrates inserting data into `users` and `posts` tables, querying a single `User`, executing a raw SQL query for `UserEmail`, and a complex SQL query joining `users` and `posts` to load into `PostsWithUserName`.

```rust
#[macro_use] extern crate diesel;

use diesel::prelude::*;
use diesel::sql_query;

fn main() {
    diesel::insert_into(users::table)
        .values(
          (
            users::first_name.eq("Gordon"),
            users::last_name.eq("Freeman"),
            users::email.eq("gordon.freeman@blackmesa.co"),
          )
        )
        .execute(&db_connection)
        .expect("Error inserting row into database");

    let first_user = users
        .first::<User>(&db_connection)
        .expect("Error querying first user");

    diesel::insert_into(posts::table)
        .values(
          (
            posts::user_id.eq(first_user.id),
            posts::title.eq("Thoughts on Tomorrow's Experiment"),
            posts::body.eq("What could possibly go wrong?"),
          )
        )
        .execute(&db_connection)
        .expect("Error inserting row into database");

    let users_emails = sql_query("SELECT users.id, users.email FROM users ORDER BY id")
      .load::<UserEmail>(&connection);

    println!("{:?}", users_emails);
    //=> User { id: 1, email: "gordon.freeman@blackmesa.co" }

    let joined = sql_query("
      SELECT users.first_name,
             users.last_name,
             CONCAT(users.first_name, users.last_name) as full_name,
             posts.body,
             posts.title
      FROM users
      INNER JOIN posts ON users.id = posts.user_id
    ")
      .load::<PostsWithUserName>(&connection);
    println!("{:?}", joined);
    /* Output =>
        [
            PostsWithUserName {
                user_name: UserName { first_name: "Gordon", last_name: "Freeman", full_name: "GordonFreeman" },
                title: "Thoughts on Tomorrow's Experiment",
                body: "What could possibly go wrong?
            }
        ]
    */
}

```

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

### Run Application to Show Posts

Source: https://github.com/diesel-rs/diesel/blob/main/examples/sqlite/getting_started_step_2/README.md

Execute the application to display posts from the database.

```bash
$ cargo run --bin show_posts
```

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

### Run Postgres and MySQL with Docker

Source: https://github.com/diesel-rs/diesel/blob/main/CONTRIBUTING.md

This script sets up PostgreSQL and MySQL Docker containers for testing. It waits for the databases to be ready before proceeding.

```bash
#!/usr/bin/env sh
set -e
docker run -d --name diesel.mysql -p 3306:3306 -e MYSQL_ALLOW_EMPTY_PASSWORD=true mysql
while
  sleep 1;
  ! echo 'CREATE DATABASE diesel_test; CREATE DATABASE diesel_unit_test;' | docker exec -i diesel.mysql mysql
do sleep 1; done

docker run -d --name diesel.postgres -p 5432:5432 -e POSTGRES_PASSWORD=postgres postgres
while
  sleep 1;
  ! echo 'CREATE DATABASE diesel_test;' | docker exec -i diesel.postgres psql -U postgres
do :; done

```

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

### Get All Offline Services

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/custom_arrays/README.md

Fetches all services that are marked as offline. Returns a vector of Service objects.

```rust
    pub fn get_all_offline_services(db: &mut Connection) -> QueryResult<Vec<Self>> {
        service
            .filter(online.eq(false))
            .select(Service::as_returning())
            .load::<Service>(db)
    }
```

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

### Run Application to Write a Post

Source: https://github.com/diesel-rs/diesel/blob/main/examples/sqlite/getting_started_step_2/README.md

Execute the application to write a new post to the database, with a prompt for input.

```bash
$ cargo run --bin write_post
# write your post
```

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

### Diesel Schema Definition

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/trait_derives.md

Defines the users table with a nullable email field. This schema is used in subsequent examples for updating records.

```rust
table! {
    users (id) {
        id -> Int4,
        first_name -> Varchar,
        last_name -> Varchar,
        email -> Nullable<Varchar>,
    }
}

```

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

### Get All Service Endpoints

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/custom_arrays/README.md

Retrieves all API endpoints associated with a given service ID. Returns a vector of optional Endpoint objects.

```rust
    pub fn get_all_service_endpoints(
        db: &mut Connection,
        param_service_id: i32,
    ) -> QueryResult<Vec<Option<Endpoint>>> {
        service
            .filter(service_id.eq(param_service_id))
            .select(endpoints)
            .first::<Vec<Option<Endpoint>>>(db)
    }
```

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

### Insert, Load, and Update User Records

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/trait_derives.md

Demonstrates inserting a new user, loading all users, retrieving the first user by ID, and updating a user's name using Diesel.

```rust
// File: src/main.rs
#[macro_use] extern crate diesel;

use diesel::prelude::*;

fn main() {
    let new_user = NewUser {
        first_name: "Gordon",
        last_name: "Freeman",
        electronic_mail: "gordon.freeman@blackmesa.co",
    };

    diesel::insert_into(users::table)
        .values(&new_user)
        .execute(&db_connection)
        .expect("Error inserting row into database");

    let all_users = users.load::<User>(&db_connection)
        .expect("Error loading users");

    println!("User count: {}", all_users.len());
    //=> User count: 1

    let hero = users.first::<Users>(&db_connection)
        .expect("Error loading first user");

    println!("Our Hero's ID: {}", hero.id());
    //=> Our Hero's ID: 1

    diesel::update(&hero).set((
        first_name.eq("Alyx"),
        last_name.eq("Vance"),
    )).execute(&db_connection);

    let updated_hero = users.first::<Users>(&db_connection)
        .expect("Error loading first user");

    println!("Our Hero's updated name: {} {}", updated_hero.first_name, updated_hero.last_name);
    //=> Our Hero's updated name: Alyx Vance
}
```

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

### Complete up.sql for Custom Schema and Types

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/custom_arrays/README.md

Includes schema creation, custom ENUM and composite types, and the 'service' table definition with array types. Ensure schema prefixing.

```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
);
```

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

### Build WASM Application

Source: https://github.com/diesel-rs/diesel/blob/main/examples/sqlite/wasm/README.md

Compiles the Rust project to WebAssembly.

```bash
cargo build --target wasm32-unknown-unknown
# Build wasm


```

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

### Verify Post Display After Operations

Source: https://github.com/diesel-rs/diesel/blob/main/examples/sqlite/getting_started_step_3/README.md

Run the application again to show posts, verifying that the previously written and published post is now displayed.

```bash
$ cargo run --bin show_posts
# your post will be printed here
```

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

### SQL Schema Migration - Up

Source: https://github.com/diesel-rs/diesel/blob/main/diesel_cli/README.md

Defines the SQL statements to create a new 'users' table with id, name, and favorite_color columns. This is the 'up' script for a migration.

```sql
-- up.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL,
    favorite_color VARCHAR
);
```

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

### Loading Query Results into Structs

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/trait_derives.md

Demonstrates how to execute a database query using Diesel's query builder and load the results into `User` and `EmailUser` structs. This requires the structs to implement `Queryable` and match the query's selected columns.

```rust
// File: src/main.rs
#[macro_use] extern crate diesel;

use diesel::prelude::*;

fn main() {
    // The following will return all users as a `QueryResult<Vec<User>>`
    let users_result: QueryResult<Vec<User>> = users.load(&db_connection);

    // Here we are getting the value (or error) out of the `QueryResult`
    // A successful value will be of type `Vec<User>`
    let users = users_result.expect("Error loading users");

    // Here, a successful value will be type `Vec<EmailUser>`
    let email_users = users.select((users::id, users::email))
        .load::<EmailUser>(&db_connection)
        .expect("Error loading the email only query");
}
```

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

### Add MySQL APT repository on Debian/Ubuntu

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/backend_installation.md

Adds the MySQL APT repository to your system's sources list.

```bash
wget https://dev.mysql.com/get/mysql-apt-config_0.8.15-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.15-1_all.deb
```

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

### Configure Database Backend in Cargo.toml

Source: https://github.com/diesel-rs/diesel/blob/main/README.md

Specify the database backend and features for Diesel in your Cargo.toml file. Replace '<version>' with the desired Diesel version and '<postgres|mysql|sqlite>' with your chosen database.

```toml
[dependencies]
diesel = { version = "<version>", features = ["<postgres|mysql|sqlite>"] }
```

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

### Rust CreateService Struct Definition

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/custom_arrays/README.md

Defines the 'CreateService' struct for insert operations. It shares many fields with 'Service' but is derived with Diesel's Insertable trait. Note that some fields are omitted in the example for brevity.

```rust
#[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<Option<Endpoint>>,
}
```

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

### Manual Struct Mapping (Without Diesel)

Source: https://github.com/diesel-rs/diesel/blob/main/diesel/README.md

Illustrates the manual implementation required to map database rows to a Rust struct without using Diesel's derive macros. This is shown for comparison to highlight Diesel's boilerplate reduction.

```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"),
        }
    }
}
```

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

### Manual ToSql Implementation for Enum

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/custom_types.md

Implement the `ToSql` trait to serialize a custom enum to its SQL representation. This example shows converting a `Language` enum to its byte string equivalent for a generic `Db` backend.

```rust
impl<Db: Backend> ToSql<LanguageType, Db> for Language {
    fn to_sql<W: Write>(&self, out: &mut Output<W, Db>) -> serialize::Result {
        match *self {
            Language::En => out.write_all(b"en")?,
            Language::Ru => out.write_all(b"ru")?,
            Language::De => out.write_all(b"de")?,
        }
        Ok(IsNull::No)
    }
}
```

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

### Get Single Result from SQL Function

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/composite_types/README.md

Retrieves a single result from an SQL function `shortest_distance` using `get_result()`. This is suitable when the SQL function is designed to return a single value or a composite type that maps to a single Rust type.

```rust
let result: Distance = select(shortest_distance())
    .get_result(connection)?;
```

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

### Deriving Insertable for a Struct

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/trait_derives.md

Derive the `Insertable` trait on your struct to enable easy insertion into a Diesel table. This example also shows how to map struct fields to different column names using `#[diesel(column_name = ...)]` and specifies the target table with `#[diesel(table_name = ...)]`.

```rust
// File: src/models.rs

// Add serde, serde_derive, and serde_json to simulate
// deserializing a web form.
extern crate serde;
extern crate serde_derive;
extern crate serde_json;


use schema::users;

#[derive(Queryable)]
pub struct User {
    pub id: i32,
    pub first_name: String,
    pub last_name: String,
    pub email: String,
}

#[derive(Deserialize, Insertable)]
#[diesel(table_name = users)]
pub struct NewUser<'a> {
    pub first_name: &'a str,
    pub last_name: &'a str,
    #[diesel(column_name = email)]
    pub electronic_mail: &'a str,
 }
```

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

### Publish a Post by ID

Source: https://github.com/diesel-rs/diesel/blob/main/examples/sqlite/getting_started_step_3/README.md

Run the application to publish a specific post, identified by its ID. This action makes the post visible or active.

```bash
$ cargo run --bin publish_post 1
```

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

### Inserting and Querying Associated Data

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/trait_derives.md

Demonstrates inserting a new user and associated posts, then querying for all posts belonging to a specific user using `Post::belonging_to`.

```rust
// File: src/main.rs
#[macro_use] extern crate diesel;

use diesel::prelude::*;

fn main() {
    let new_user = NewUser {
        first_name: "Issac",
        last_name: "Kleiner",
        email: Some("issac.kleiner@blackmesa.co"),
    };

    let issac_kleiner = diesel::insert_into(users::table)
        .values(&new_user)
        .get_result::<User>(&db_connection)
        .expect("Error inserting row into database");

    // Setting up our posts vec and pointing each post to the
    // most recently inserted user
    let post_list = vec![
        NewPost {
            user_id: issac_kleiner.id().to_owned(),
            title: "Top Secret #001",
            content: "I'm feeling optimistic about our new reactor code written in Rust!",
        },
        NewPost {
            user_id: issac_kleiner.id().to_owned(),
            title: "Top Secret #002",
            content: "Finished making special pet for Gordon. I hope he likes it!",
        },
    ];

    // Insert the new posts vector
    diesel::insert_into(posts::table)
        .values(&post_list)
        .execute(&db_connection)
        .expect("Error inserting post");

    // Get the first user
    let issac = users.first::<User>(&db_connection)
        .expect("Couldn't find first user");

    // Get all the posts belonging to the first user
    let issacs_posts = Post::belonging_to(&issac)
        .get_results::<Post>(&db_connection)
        .expect("Couldn't find associated posts");

    for post in issacs_posts {
        println!("-----\nTitle: {}\nContent: \n", post.title, post.body);
    }

  // Outputs
  /*
------
Title: Top Secret #001
Content: "I'm feeling optimistic about our new reactor code written in Rust!"

------
Title: Top Secret #002
Content: "Finished making special pet for Gordon. I hope he likes it!"

  */
}
```

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

### Run Diesel Migrations

Source: https://github.com/diesel-rs/diesel/blob/main/examples/sqlite/getting_started_step_1/README.md

Execute Diesel database migrations using the 'diesel migration run' command. Ensure the .env file is present in the project root.

```bash
$ diesel migration run
```

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

### PostgreSQL Database Connection Utility

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/custom_arrays/README.md

A utility function to establish a PostgreSQL connection for testing purposes. It uses `dotenvy` to load database credentials from environment variables.

```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))
}
```

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

### Run Diesel Migration

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/custom_arrays/README.md

Command to execute pending Diesel database migrations. Verify custom types and tables in the database console.

```bash
diesel migration run
```

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

### Load All Users

Source: https://github.com/diesel-rs/diesel/blob/main/diesel/README.md

Loads all users from the database. Requires a database connection.

```rust
users::table.load(&mut connection)
```

```sql
SELECT * FROM users;
```

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

### Execute Raw SQL Query with Bindings

Source: https://github.com/diesel-rs/diesel/blob/main/diesel/README.md

Execute raw SQL queries, useful for complex queries or when the query builder is insufficient. Requires a struct with `QueryableByName` derive.

```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::<Integer, _>(organization_id)
    .bind::<BigInt, _>(offset)
    .bind::<BigInt, _>(limit)
    .load::<User>(&mut conn)?;
```

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

### Configure Diesel project dependencies

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/backend_installation.md

Specify required database backends for your Diesel project in Cargo.toml.

```toml
[dependencies]
diesel = { version = "X.X.X", features = ["sqlite"] }
```

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

### Initialize and Handle SQLite WASM Worker

Source: https://github.com/diesel-rs/diesel/blob/main/examples/sqlite/wasm/index.html

Sets up a Web Worker for SQLite operations and handles messages from the worker to update the UI. This includes initializing the worker, defining message handlers for different commands, and setting up event listeners for UI interactions.

```javascript
// Tab functionality
document.querySelectorAll('.tab').forEach(tab => {
    tab.addEventListener('click', () => {
        // Remove active class from all tabs and contents
        document.querySelectorAll('.tab, .tab-content').forEach(el => {
            el.classList.remove('active');
        });
        // Add active class to clicked tab and corresponding content
        tab.classList.add('active');
        const tabId = tab.getAttribute('data-tab');
        document.getElementById(`${tabId}-tab`).classList.add('active');
    });
});

// DOM Elements
const elements = {
    vfs: document.getElementById('vfs'),
    showPosts: document.getElementById('show-posts'),
    showResult: document.getElementById('show-result'),
    createTitle: document.getElementById('create-title'),
    createBody: document.getElementById('create-body'),
    createPost: document.getElementById('create-post'),
    createResult: document.getElementById('create-result'),
    getPostId: document.getElementById('get-post-id'),
    getPost: document.getElementById('get-post'),
    getResult: document.getElementById('get-result'),
    publishPostId: document.getElementById('publish-post-id'),
    publishPost: document.getElementById('publish-post'),
    publishResult: document.getElementById('publish-result'),
    deletePostTitle: document.getElementById('delete-post-title'),
    deletePost: document.getElementById('delete-post'),
    deleteResult: document.getElementById('delete-result')
};

// Initialize Web Worker
const worker = new Worker('worker.js', { type: 'module' });

// Message handler
worker.onmessage = function(event) {
    const { cmd, ...data } = event.data;
    switch (cmd) {
        case 'show_posts':
            elements.showResult.innerHTML = `<pre>${JSON.stringify(data.posts, null, 2)}</pre>`;
            break;
        case 'create_post':
            elements.createResult.innerHTML = `<pre>${JSON.stringify(data.post, null, 2)}</pre>`;
            break;
        case 'get_post':
            elements.getResult.innerHTML = `<pre>${JSON.stringify(data.post, null, 2)}</pre>`;
            break;
        case 'publish_post':
            elements.publishResult.textContent = 'Post published successfully';
            break;
        case 'delete_post':
            elements.deleteResult.textContent = 'Post deleted successfully';
            break;
        case 'error':
            // Show error in all result areas for visibility
            document.querySelectorAll('.result').forEach(el => {
                el.innerHTML = `<pre style="color: red">Error: ${data.error}</pre>`;
            });
            break;
    }
};

// Event listeners
elements.vfs.addEventListener('change', () => {
    const id = parseInt(elements.vfs.value);
    worker.postMessage({ cmd: "switch_vfs", id });
});

elements.showPosts.addEventListener('click', () => {
    worker.postMessage({ cmd: "show_posts" });
});

elements.createPost.addEventListener('click', () => {
    const title = elements.createTitle.value.trim();
    const body = elements.createBody.value.trim();
    if (title && body) {
        worker.postMessage({ cmd: "create_post", title, body });
    } else {
        elements.createResult.innerHTML = '<pre style="color: red">Please enter both title and content</pre>';
    }
});

elements.getPost.addEventListener('click', () => {
    const postId = parseInt(elements.getPostId.value);
    if (!isNaN(postId)) {
        worker.postMessage({ cmd: "get_post", post_id: postId });
    } else {
        elements.getResult.innerHTML = '<pre style="color: red">Please enter a valid post ID</pre>';
    }
});

elements.publishPost.addEventListener('click', () => {
    const postId = parseInt(elements.publishPostId.value);
    if (!isNaN(postId)) {
        worker.postMessage({ cmd: "publish_post", post_id: postId });
    } else {
        elements.publishResult.innerHTML = '<pre style="color: red">Please enter a valid post ID</pre>';
    }
});

elements.deletePost.addEventListener('click', () => {
    const title = elements.deletePostTitle.value.trim();
    if (title) {
        worker.postMessage({ cmd: "delete_post", title });
    } else {
        elements.deleteResult.innerHTML = '<pre style="color: red">Please enter a post title</pre>';
    }
});

// Load initial posts
elements.showPosts.click();
```

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

### Insert New Users (Execute)

Source: https://github.com/diesel-rs/diesel/blob/main/diesel/README.md

Inserts multiple new user records into the 'users' table using a vector of `NewUser` structs. Uses `execute` to perform the insertion without returning data.

```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);
```

```sql
INSERT INTO users (name, hair_color) VALUES
  ('Sean', 'Black'),
  ('Gordon', DEFAULT)
```

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

### Sequential DB Integration Test

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/custom_arrays/README.md

This Rust code snippet demonstrates a single, sequential integration test for a service. It includes running DB migrations, testing service creation and counting, and reverting migrations. Use this for simple test cases where parallel execution is not a concern.

```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);
}
```

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

### libsqlite3-sys Dependency for Bundled Build

Source: https://github.com/diesel-rs/diesel/blob/main/examples/sqlite/embedded/README.md

Add the libsqlite3-sys crate with the 'bundled' feature enabled to your Cargo.toml to facilitate the bundled build process for SQLite.

```toml
libsqlite3-sys = { version = "0.35.0", features = ["bundled"] }
```

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

### Verify Post Deletion

Source: https://github.com/diesel-rs/diesel/blob/main/examples/sqlite/getting_started_step_3/README.md

Run the application to display posts and confirm that the post intended for deletion is no longer present in the database.

```bash
$ cargo run --bin show_posts
# observe that no posts are shown
```

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

### Set Database URL Environment Variable

Source: https://github.com/diesel-rs/diesel/blob/main/examples/sqlite/getting_started_step_1/README.md

Configure the DATABASE_URL environment variable to point to your SQLite database file. This is typically done in a .env file.

```bash
$ echo "DATABASE_URL=file:test.db" > .env
```

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

### Struct for Database Rows (Diesel Codegen)

Source: https://github.com/diesel-rs/diesel/blob/main/diesel/README.md

Defines a struct `Download` using Diesel's `Queryable` and `Selectable` derive macros to map database rows to a Rust struct. This reduces boilerplate compared to manual mapping.

```rust
#[derive(Queryable, Selectable)]
#[diesel(table_name = downloads)]
pub struct Download {
    id: i32,
    version_id: i32,
    downloads: i32,
    counted: i32,
    date: SystemTime,
}
```

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

### Set Database URL Environment Variable

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/composite_types/README.md

Sets the `DATABASE_URL` environment variable in a `.env` file to configure the connection string for the PostgreSQL database. Replace `username:password` with actual credentials.

```bash
echo DATABASE_URL=postgres://username:password@localhost/diesel_demo > .env
```

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

### Enabling Benchmarks for Other Crates

Source: https://github.com/diesel-rs/diesel/blob/main/diesel_bench/Readme.md

Add specific features to Cargo.toml to enable benchmarks for various database crates.

```sh
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`
```

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

### Construct Complex Query with Subquery and Filtering

Source: https://github.com/diesel-rs/diesel/blob/main/README.md

Build a complex query involving a subquery to select recent versions, then filter and load associated downloads. This demonstrates Diesel's expressive query builder.

```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::<Download>(&mut conn)?;
```

```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
```

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

### Complex Query Construction

Source: https://github.com/diesel-rs/diesel/blob/main/diesel/README.md

Constructs a complex query to fetch download data within the last 90 days, filtered by specific version IDs. Requires a database connection.

```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::<Download>(&mut conn)?;
```

```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
```

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

### Create Postgres Schema

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/custom_arrays/README.md

Define the SQL command to create a new schema in PostgreSQL. This is used in the `up.sql` migration file to set up the schema when applying migrations.

```sql
CREATE SCHEMA IF NOT EXISTS smdb;
```

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

### Run Pending DB Migrations

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/custom_arrays/README.md

Use this function to programmatically run all pending database migrations. It first checks the database connection and then executes migrations defined in `MIGRATIONS`. Ensure `diesel_migrations` is added to Cargo.toml and the target database is set as a feature flag.

```rust
pub type Connection = PgConnection;

pub const MIGRATIONS: EmbeddedMigrations = embed_migrations!("migrations");

pub fn run_db_migration(
    conn: &mut Connection,
) -> Result<(), Box<dyn Error + Send + Sync + 'static>> {
    // 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)
        }
    }
}
```

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

### Rust Service Read All Operation

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/custom_arrays/README.md

Implements the 'read_all' method to fetch all Service records from the database. It loads all entries from the service table into a vector. This is suitable for tables with a small number of entries.

```rust
    pub fn read_all(db: &mut Connection) -> QueryResult<Vec<Self>> {
        service.load::<Service>(db)
    }
```

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

### Insert New Users and Retrieve Inserted Data

Source: https://github.com/diesel-rs/diesel/blob/main/README.md

Insert new records into the 'users' table and retrieve the newly inserted rows. This is useful when you need to return generated IDs or other fields from the inserted data.

```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::<User>(&mut connection);
```

```sql
INSERT INTO users (name, hair_color) VALUES
  ('Sean', 'Black'),
  ('Gordon', DEFAULT)
  RETURNING *
```

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

### Set Service Online Public Method

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/custom_arrays/README.md

Public wrapper method to set a service's 'online' flag to true.

```rust
    pub fn set_service_online(db: &mut Connection, param_service_id: i32) -> QueryResult<()> {
        Self::set_svc_online(db, param_service_id, true)
    }
```

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

### Rust Service Create Operation

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/custom_arrays/README.md

Implements the 'create' method for the Service struct to insert a new record into the database. It uses Diesel's insert_into function and returns the created Service object.

```rust
impl Service {
    pub fn create(db: &mut Connection, item: &CreateService) -> QueryResult<Self> {
        insert_into(crate::schema::smdb::service::table)
            .values(item)
            .get_result::<Service>(db)
    }

}
```

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

### Load Posts for a User

Source: https://github.com/diesel-rs/diesel/blob/main/diesel/README.md

Loads all posts belonging to a specific user. Requires a user object and a database connection.

```rust
Post::belonging_to(user).load(&mut connection)
```

```sql
SELECT * FROM posts WHERE user_id = 1;
```

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

### Postgres Count Query with u64 Conversion

Source: https://github.com/diesel-rs/diesel/blob/main/examples/postgres/custom_arrays/README.md

Demonstrates chaining a map operation to convert an i64 count to a u64. The explicit `<i64>` type annotation on `get_result` is necessary for this conversion to compile.

```rust
pub fn count_u64(db: &mut Connection) -> QueryResult<u64> {
        service.count()
        .get_result::<i64>(db)
        .map(|c| c as u64)
    }
```

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

### Running Diesel Benchmarks

Source: https://github.com/diesel-rs/diesel/blob/main/diesel_bench/Readme.md

Execute Diesel's own benchmarks or enable benchmarks for other crates by specifying features.

```sh
$ DATABASE_URL=your://database/url diesel migration run --migration-dir ../migrations/$backend
$ DATABASE_URL=your://database/url cargo bench --features "$backend"
```

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

### tinyrlibc Dependency for libc Functions

Source: https://github.com/diesel-rs/diesel/blob/main/examples/sqlite/embedded/README.md

Include the tinyrlibc crate (or a similar alternative) in your Cargo.toml to provide essential libc functions required by libsqlite3 on embedded targets.

```toml
tinyrlibc = "0.5"
```

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

### Update package lists on Debian/Ubuntu

Source: https://github.com/diesel-rs/diesel/blob/main/guide_drafts/backend_installation.md

Refreshes the list of available packages after adding a new repository.

```bash
sudo apt-get update
```