CRUD using Diesel (postgres)

diesel_cli

$ cargo install diesel_cli --no-default-features --features postgres
$ sudo apt install libpq        //for the PostgreSQL backend
        

diesel_cli is a tool which is used to interact with your database schema.

Usage of diesel CLI

1. Running Database Migrations scripts
2. Generating Rust Code for Your Database Schema

Diesel Migration Scripts

Migrations are essentially scripts that make changes to your database schema.

Database, Table creation using Diesel

1. Create .env file containing DATABASE_URL to connect to postgres

Add DATABASE_URL string to .env

// username = username of postgres database
// password = password of postgres database
// localhost = machine on which database need to setup
// database_name = name of database to create
$ echo DATABASE_URL=postgres://username:password@localhost/database_name > .env
        

2. Create database using diesel

Database & migrations directory Creation using command diesel setup

$ cd /root/code/rust/personalized_report_card/backend
backend# diesel setup
Creating migrations directory at: /root/code/rust/personalized_report_card/backend/migrations
Creating database: teachers
root@Amit:~/code/rust/personalized_report_card/backend# ls
Cargo.lock  Cargo.toml  Docs  diesel.toml  migrations  src  target
        

diesel setup will also creates diesel.toml

When we ran diesel setup, a file called diesel.toml was created which tells Diesel to maintain a file at src/schema.rs for us

# For documentation on how to configure this file,
# see https://diesel.rs/guides/configuring-diesel-cli

[print_schema]
file = "src/schema.rs"          // Schema will be dumped into this file
custom_type_derives = ["diesel::query_builder::QueryId", "Clone"]

[migrations_directory]
dir = "/root/code/rust/personalized_report_card/backend/migrations"
        

Connect to DB to check database(teachers) created?

root@Amit:~/code/rust/personalized_report_card/backend# sudo -u postgres psql
psql (16.6 (Ubuntu 16.6-0ubuntu0.24.04.1))
Type "help" for help.

postgres=# \l
                                                   List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | 
 teachers  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | 
 template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
postgres=# \q
            

What Files created by migration script?

root@Amit:~/code/rust/personalized_report_card/backend# ls -ltr migrations/00000000000000_diesel_initial_setup/
total 8
-rw-r--r-- 1 root root 1145 Dec  7 10:14 up.sql
-rw-r--r-- 1 root root  328 Dec  7 10:14 down.sql
        

3. Create Table(teachers_login) schema

Create table's migration files(up.sql, down.sql) using command $ diesel migration generate teachers_login

root@Amit:~/code/rust/personalized_report_card/backend# diesel migration generate teachers_login
Creating migrations/2024-12-07-102706_teachers_login/up.sql
Creating migrations/2024-12-07-102706_teachers_login/down.sql
        

Create table's(teachers_login) Schema. ie manually open the files and add schema

root@Amit:~/code/rust/personalized_report_card/backend# cat migrations/2024-12-07-102706_teachers_login/up.sql
CREATE TABLE teachers_login (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash TEXT NOT NULL
)
root@Amit:~/code/rust/personalized_report_card/backend# cat migrations/2024-12-07-102706_teachers_login/down.sql
DROP TABLE teachers_login
        

4. Create table(teachers_login)

Create the table by running $diesel migration run command

root@Amit:~/code/rust/personalized_report_card/backend# diesel migration run
Running migration 2024-12-07-102706_teachers_login
        

Connect to DB to check table(teachers_login) created?

# sudo -u postgres psql
psql (16.6 (Ubuntu 16.6-0ubuntu0.24.04.1))
Type "help" for help.
postgres=# \l
                                                   List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | 
 teachers  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | 
 template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres

postgres=# \c teachers
You are now connected to database "teachers" as user "postgres".
teachers=# \dt
                   List of relations
 Schema |            Name            | Type  |  Owner   
--------+----------------------------+-------+----------
 public | __diesel_schema_migrations | table | postgres
 public | teachers_login             | table | postgres
(2 rows)
teachers=# select * from teachers_login;
 id | email | password_hash 
----+-------+---------------
(0 rows)

teachers=# \q
        

Diesel creates schema.rs(ie rust file) for you

# cat src/schema.rs 
// @generated automatically by Diesel CLI.

diesel::table! {
    teachers_login (id) {
        id -> Int4,
        #[max_length = 255]
        email -> Varchar,
        password_hash -> Text,
    }
}
        

Using Migrations in Production

When preparing your app for use in production, you may want to run your migrations during the application’s initialization phase. You may also want to include the migration scripts as a part of your code, to avoid having to copy them to your deployment location/image etc.

The diesel_migrations crate provides the embed_migrations! macro, allowing you to embed migration scripts in the final binary. Once your code uses it, you can simply include connection.run_pending_migrations(MIGRATIONS) at the start of your main function to run migrations every time the application starts.