Hey there! Welcome back to our journey through Rust and SeaORM. Today, we're going to explore how to perform database operations like inserting, selecting, updating, and deleting rows in PostgreSQL using Rust's SeaORM. If you followed my previous post on creating PostgreSQL tables with Rust's SeaORM, you're in the perfect spot to continue!

Setting Up Your Project

First off, let's set up a new entity crate in our seaorm-by-example Cargo workspace. Run the following command:

$ cargo new entity --lib
Created library `entity` package

Your project structure should now include an entity crate and look something like this:

.
├── Cargo.lock
├── Cargo.toml
├── README.md
├── entity
│   ├── Cargo.toml
│   └── src
│       └── lib.rs
└── migration
    ├── Cargo.toml
    ├── README.md
    └── src
        ├── lib.rs
        ├── m20240106_061448_create_user_table.rs
        └── main.rs

In the Cargo.toml file, make sure to include both entity and migration:

[workspace]
members = [
    "entity",
    "migration",
]

Configuring the Database Connection

Now, let's set up the database connection. Create a .env file in your project root with the following content:

DATABASE_SCHEMA=public
DATABASE_URL=protocol://username:password@localhost/database

Generating Entity Files

Ensure sea-orm-cli is installed, then run the following command inside your project directory to generate the entity:

$ sea generate entity -o entity/src --lib
Connecting to Postgres ...
Discovering schema ...
... discovered.
Generating users.rs
    > Column `id`: i32, auto_increment, not_null
    > Column `name`: String, not_null
    > Column `age`: i32, not_null
Writing entity/src/users.rs
Writing entity/src/mod.rs
Writing entity/src/prelude.rs
... Done.

Creating the Application

Let's create a new binary crate called app:

cargo new app

Your project structure should now look like this:

.
├── Cargo.lock
├── Cargo.toml
├── README.md
├── app
│   ├── Cargo.toml
│   └── src
│       └── main.rs
├── entity
│   ├── Cargo.toml
│   └── src
│       ├── lib.rs
│       ├── prelude.rs
│       └── users.rs
└── migration
    ├── Cargo.toml
    ├── README.md
    └── src
        ├── lib.rs
        ├── m20240106_061448_create_user_table.rs
        └── main.rs

In app/Cargo.toml, add necessary dependencies:

[dependencies]
entity = { path = "../entity" }
migration = { path = "../migration" }
dotenvy = "0.15.7"
tokio = { version = "1.35.1", features = ["full"] }
sea-orm = { version = "0.12.0", features = ["debug-print", "runtime-tokio-native-tls", "sqlx-postgres"] }

Connecting to PostgreSQL

In app/src/main.rs, set up the database connection:

use sea_orm::*;
use std::env;

use ::entity::{users, users::Entity as User};

#[tokio::main]
async fn main() {
    dotenvy::dotenv().ok();
    let db_url = env::var("DATABASE_URL").expect("DATABASE_URL is not set in .env file");
    let db = Database::connect(db_url).await.expect("Database connection failed");

    // [Insert, Select, Update, Delete operations here]
}

Performing Database Operations

Now, let's dive into the CRUD operations:

Insert a new user:

// Insert new row
let alice = users::ActiveModel {
    name: Set("Alice".to_owned()),
    age: Set(20),
    ..Default::default()
};
let alice = alice.insert(&db).await.unwrap();
println!("insert {:?}", alice);

Select a user:

// Select alice by primary key
let alice: Option<users::Model> = User::find_by_id(alice.id).one(&db).await.unwrap();
println!("select {:?}", alice);

Update a user's age:

// Update alice's age
let mut alice: users::ActiveModel = alice.unwrap().into();
alice.age = Set(29);
let alice: users::Model = alice.update(&db).await.unwrap();
println!("update {:?}", alice);

Delete a user:

// Delete alice
let res: DeleteResult = alice.delete(&db).await.unwrap();
println!("delete {:?}", res);

Complete app/main.rs should looks like the following:

use sea_orm::*;
use std::env;

use ::entity::{users, users::Entity as User};

#[tokio::main]
async fn main() {
    // Connect to database
    dotenvy::dotenv().ok();
    let db_url = env::var("DATABASE_URL").expect("DATABASE_URL is not set in .env file");
    let db = Database::connect(db_url)
        .await
        .expect("Database connection failed");

    // Insert new row
    let alice = users::ActiveModel {
        name: Set("Alice".to_owned()),
        age: Set(20),
        ..Default::default()
    };
    let alice = alice.insert(&db).await.unwrap();
    println!("insert {:?}", alice);

    // Select alice by primary key
    let alice: Option<users::Model> = User::find_by_id(alice.id).one(&db).await.unwrap();
    println!("select {:?}", alice);

    // Update alice's age
    let mut alice: users::ActiveModel = alice.unwrap().into();
    alice.age = Set(29);
    let alice: users::Model = alice.update(&db).await.unwrap();
    println!("update {:?}", alice);

    // Delete alice
    let res: DeleteResult = alice.delete(&db).await.unwrap();
    println!("delete {:?}", res);
}

Running the Application

If you run the app, you should see outputs for each operation showing the insert, select, update, and delete actions being performed on the database.

insert Model { id: 5, name: "Alice", age: 20 }
select Some(Model { id: 5, name: "Alice", age: 20 })
update Model { id: 5, name: "Alice", age: 29 }
delete DeleteResult { rows_affected: 1 }

And that's it! You've now mastered the basics of inserting, selecting, updating, and deleting rows in a PostgreSQL database using SeaORM in Rust. Keep experimenting and happy coding!