Create Operation

SeaORM abstracts database opertaions though the sea_orm::Database::connect() method which yields a DatabaseConnection. We will use this DatabaseConnection to execute database operations. Let's create the fruits table using the database connection.

Our goal is to do the SQL CREATE TABLE operation:

# Create a fruits table
CREATE TABLE fruits(
    fruit_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    datetime_utc TIMESTAMP NOT NULL,
    unit_price INT NOT NULL,
    sku VARCHAR(255) NOT NULL,
    PRIMARY KEY (fruit_id)
);

The fruits table is a record of all the fruits available, their id (fruit_id), name (name), the timestamp when the row was entered (datetime_utc) in the UTC timezone, the price per kilogram of fruit (unit_price) and the stock tracking alphanumeric code (sku) commonly known as Stock Keeping Unit.

Add a module fruits_table inside the src folder and add mod.rs, prelude.rs and fruit.rs files as its children.

FILE: SimpleCRUD/src/create_fruits_table.rs

|-- SimpleCRUD/
    |-- Cargo.toml
    |-- Cargo.lock
    |-- src/
        |-- main.rs
+    	|-- fruits_table/ #Code to create table fruits goes here
+			|-- fruits.rs
+			|-- mod.rs
+			|-- prelude.rs

Then, import this module

FILE: SimpleCRUD/src/main.rs

+ mod fruits_table;
+ use fruits_table::prelude::Fruits;

 // Import the needed modules for table creation
+ use sea_orm::{ConnectionTrait, Database, Schema};
// Handle errors using the `https://crates.io/crates/anyhow` crate
+ use anyhow::Result;

// Convert this main function into async function
+ #[async_std::main]
+ async fn main() -> Result<()>{
- fn main {
+
+    
+    Ok(())
+ }

The #[async_std::main] attribute is used to convert our main function fn main() {} into an async function async fn main() {} in order to use await inside main using async-std as the library.

Inside the fruit.rs add:

FILE: SimpleCRUD/src/fruits_table/fruit.rs

use sea_orm::entity::prelude::*;
    
#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
#[sea_orm(table_name = "fruits")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub fruit_id: i32,
    #[sea_orm(unique)]
    pub name: String,
    pub datetime_utc: DateTime,
    pub unit_price: i32,
    pub sku: String,
}

#[derive(Copy, Clone, Debug, EnumIter)]
pub enum Relation {}

impl RelationTrait for Relation {
    fn def(&self) -> RelationDef {
        panic!("No RelationDef")
    }
}

impl ActiveModelBehavior for ActiveModel {}

The #[derive(... , DeriveEntityModel)] proc macro is used to automatically derive the code for Entity, Model and ActiveModel . For this to work, the struct MUST be called Model.

The enum Relation MUST also be created, currently, it has empty fields but if the table had a relationship with another table, this is where it would be illustrated. The #[derive(... , EnumIter)] is required on a Relation to ensure the type implements and exposes a Rust Iterator.

A Relation MUST implement a the Relation trait and the method def() of the trait. Currently, there is no relation so the def() method returns a panic!("No RelationDef") if we try to do operations like joins with other tables.

Lastly, we implement ActiveModelBehavior for the ActiveModel. The ActiveModel is autogenerated by SeaORM codegen when we derived #[derive(... , DeriveEntityModel)] from Model struct.

The #[sea_orm(primary_key)] is used to set the primary key and can be called using the ..Default::default() when instantiating a model.

#[sea_orm(unique)] derive macro is used on the name field to ensure that two rows are not entered with the same name. This corresponds to SQL UNIQUE constraint.

To set the field of a Model to a default of NULL , ensure the field is set to an Option<T> , for example, to set sku field of Model to SQL default of NULL:

#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
#[sea_orm(table_name = "fruits")]
pub struct Model {
    // -- shippet --
+   pub sku: Option<String>,
-   pub sku: String,
}

Inside prelude.rs add:

FILE: SimpleCRUD/src/fruits_table/prelude.rs

pub use super::fruits::{
    ActiveModel as FruitsActiveModel, Column as FruitsColumn, Entity as Fruits,
    Model as FruitsModel, PrimaryKey as FruitsPrimaryKey, Relation as FruitsRelation,
};

This code reads the Entity from the generated code and renames it to Fruits to avoid name collisions with other existing Entities. The same goes for the Model, Relation, ActiveModel, Column, etc..

Inside mod.rs , export the modules using:

FILE: SimpleCRUD/src/fruits_table/mod.rs

pub mod prelude;

pub mod fruits;

Add code to perform execution

// Code snippet

#[async_std::main]
async fn main() -> Result<()>{
     // Read the database environment from the `.env` file
+ 	 let env_database_url = include_str!("../.env").trim();
     // Split the env url
+    let split_url: Vec<&str> = env_database_url.split("=").collect();
     // Get item with the format `database_backend://username:password@localhost/database`
+    let database_url = split_url[1];
+
+    let db = Database::connect(database_url).await?;
+
+    let builder = db.get_database_backend();
+    let schema = Schema::new(builder);
+
+	 let create_table_op =  db.execute(builder.build(&schema.create_table_from_entity(Fruits))).await;
+    println!("`CREATE TABLE fruits` {:?}", 
+        match create_table_op {
+    		Ok(_) => "Operation Successful".to_owned(),
+    		Err(e) => format!("Unsuccessful - Error {:?}", e),
+    	 }
+    );
    Ok(())
}

This operation requires reading the database environment, so we read the .env file using include_str!(".env") and store that result as the database_url variable.

The Database::connect(database_url) creates a DatabaseConnection that we will use to connect to the database abd perform operations. Using this connection, the get_database_backend() method retrieves the database backend in use and then build a schema using Schema::new(builder) which in turn is used by the database backed value stored in the builder variable to build the SQL statement using the .build() method.

Finally, we run the SQL query using the .execute() method on DatabaseConnection stored as the db variable. Running the program using cargo run should print:

$ `CREATE TABLE fruits` "Operation Successful"

Running the operation again should print the error:

$ `CREATE TABLE fruits` "Unsuccessful - Error Exec(\"error returned from database: 1050 (42S01): Table 'fruits' already exists\")"

Automatically deriving the code to perform CRUD operations

If the database we want to use already exists, we can automatically generate an Entity, Model and ActiveModel using sea-orm-cli which we installed in the Installation part of the Introduction.

sea-orm-cli will load the database configuration by reading the .env file we created earlier in order to login to the database using the username and password in this file, then it will load the schema which is the database we specified, create the Entities from all the tables in the selected database and automatically generate the relevant code and of the process is successful, create all the code in the folder we will specify.

In the current working directory of the project, execute:

$ sea-orm-cli generate entity -o src/fruits_table

The structure of the current working directory after sea-orm-cli has done its "magical" code generation:

|-- SimpleCRUD/
    |-- Cargo.toml
    |-- Cargo.lock
    |-- src/
        |-- main.rs
+    	|-- fruits_table/ #Model, ActiveModel and Entity code generated by `sea-orm-cli`
+            |-- fruit.rs 
+            |-- mod.rs
+            |-- prelude.rs

Next, import the fruits_table module for use with the project

File: ./SimpleCRUD/src/main.rs

+ mod fruits_table;

#[async_std::main]
async fn main() -> Result<()>{
	...
    Ok(())
}

That's it, we have automatically loaded and created all the tables in our database as Entities using sea-orm-cli.

Next, we perform Insert operations and print results to the console.

Sea-ORM Simple CRUD Operations

Create Operation

Automatically deriving the code to perform CRUD operations