Connect to OceanBase Database by using Diesel

This topic describes how to connect to OceanBase Database by using the Diesel ORM framework in Rust and perform basic Create, Read, Update, and Delete (CRUD) operations.

Environment preparation

Before you start, make sure that the following tools are installed:

• Rust 1.78 or later
• Cargo (Rust package manager)
• Diesel CLI tool
• OceanBase Database V4.2.4 or later (MySQL mode) is installed and running
• MySQL 5.7 client development library

Install MySQL 5.7 client development library

rpm -ivh https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
yum install -y mysql-community-devel

Install Diesel CLI

# Install Diesel CLI with MySQL support
cargo install diesel_cli --no-default-features --features mysql

Create a new project

cargo new diesel-oceanbase-demo
cd diesel-oceanbase-demo

Add dependencies

Edit the Cargo.toml file and add the following dependencies:

[package]
name = "diesel-oceanbase-demo"
version = "0.1.0"
edition = "2024"

[lib]
name = "diesel_oceanbase_demo"  # Note: Use underscores instead of hyphens
path = "src/lib.rs"

[dependencies]
diesel = { version = "2.1.0", features = ["mysql", "r2d2", "chrono", "serde_json"] }
dotenv = "0.15"
log = "0.4"
pretty_env_logger = "0.5"
serde = { version = "1.0", features = ["derive"] }
serde_json = "1.0"

Prepare the database

1. Set the environment variable:

   # Create a .env file in the project root directory
   echo 'DATABASE_URL=mysql://username:password@localhost:2881/diesel_demo' > .env
   

2. Initialize the database:

   # Create the database and set the table structure
   diesel setup
   

3. Create and write the table file:

   diesel migration generate create_users
   

   Expected output:

   Creating migrations/2025-05-30-042428_create_users/up.sql
   Creating migrations/2025-05-30-042428_create_users/down.sql
   

4. Edit the generated migration file migrations/YYYY-MM-DD-HHMMSS_create_users/up.sql:

   CREATE TABLE IF NOT EXISTS users (
       id INT AUTO_INCREMENT PRIMARY KEY,
       username VARCHAR(50) NOT NULL,
       email VARCHAR(100) NOT NULL UNIQUE,
       active BOOLEAN NOT NULL DEFAULT TRUE
   );
   

5. Edit migrations/YYYY-MM-DD-HHMMSS_create_users/down.sql:

   DROP TABLE users;
   

6. Initialize the users table:

   diesel migration run
   

   Create the src/schema.rs file with the following content:

   diesel::table! {
       users (id) {
           id -> Integer,
           username -> Varchar,
           email -> Varchar,
           active -> Bool,
       }
   }
   

Define models

Create the src/models.rs file:

use serde::{Serialize, Deserialize};
use diesel::prelude::*;

#[derive(Queryable, Selectable, Insertable, AsChangeset, Debug, Serialize, Deserialize)]
#[diesel(table_name = crate::schema::users)]
pub struct User {
    pub id: i32,
    pub username: String,
    pub email: String,
    pub active: bool,
}

#[derive(Insertable, Debug, Serialize, Deserialize)]
#[diesel(table_name = crate::schema::users)]
pub struct NewUser<'a> {
    pub username: &'a str,
    pub email: &'a str,
    pub active: bool,
}

#[derive(AsChangeset, Debug, Serialize, Deserialize)]
#[diesel(table_name = crate::schema::users)]
pub struct UpdateUser<'a> {
    pub username: Option<&'a str>,
    pub email: Option<&'a str>,
    pub active: Option<bool>,
}

Database connection pool

Create the src/db.rs file:

use diesel::mysql::MysqlConnection;
use diesel::r2d2::{self, ConnectionManager};
use std::env;

pub type Pool = r2d2::Pool<ConnectionManager<MysqlConnection>>;

pub fn init_pool() -> Pool {
    let database_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    let manager = ConnectionManager::<MysqlConnection>::new(database_url);
    r2d2::Pool::builder()
        .build(manager)
        .expect("Failed to create pool")
}

Export the database module in src/lib.rs:

pub mod models;
pub mod schema;
pub mod db;

// Re-export commonly used types
pub use db::{init_pool, Pool};
pub use models::{User, NewUser, UpdateUser};

Implement the data access layer

Create the src/repositories/user_repository.rs file:

use diesel::prelude::*;
use diesel::result::Error;
use crate::models::{User, NewUser, UpdateUser};
use crate::schema::users;

pub struct UserRepository;

impl UserRepository {
    pub fn create(conn: &mut MysqlConnection, new_user: NewUser) -> Result<User, Error> {
        diesel::insert_into(users::table)
            .values(&new_user)
            .execute(conn)?;
        
        users::table
            .order(users::id.desc())
            .first(conn)
    }

    pub fn find(conn: &mut MysqlConnection, user_id: i32) -> Result<User, Error> {
        users::table.find(user_id).first(conn)
    }

    pub fn update(conn: &mut MysqlConnection, user_id: i32, update_user: UpdateUser) -> Result<User, Error> {
        diesel::update(users::table.find(user_id))
            .set(&update_user)
            .execute(conn)?;
            
        Self::find(conn, user_id)
    }

    pub fn delete(conn: &mut MysqlConnection, user_id: i32) -> Result<usize, Error> {
        diesel::delete(users::table.find(user_id))
            .execute(conn)
    }

    pub fn find_all(conn: &mut MysqlConnection) -> Result<Vec<User>, Error> {
        users::table.load(conn)
    }
}

Create a module file

1. Create the src/repositories/mod.rs file:

   pub mod user_repository;
   

2. Update the src/main.rs file:

   mod models;
   mod schema;
   mod db;
   mod repositories;
   
   use dotenv::dotenv;
   use log::info;
   use crate::models::{NewUser, UpdateUser};
   use crate::repositories::user_repository::UserRepository;
   use crate::db::{init_pool};
   
   fn main() -> Result<(), Box<dyn std::error::Error>> {
       dotenv().ok();
       pretty_env_logger::init();
   
       let pool = init_pool();
       let mut conn = pool.get()?;
   
       // Create a user
       let new_user = NewUser {
           username: "testuser",
           email: "test@example.com",
           active: true,
       };
   
       let created_user = UserRepository::create(&mut conn, new_user)?;
       info!("Created user: {:?}", created_user);
   
       // Query a user
       let user = UserRepository::find(&mut conn, created_user.id)?;
       info!("Found user: {:?}", user);
   
       // Update a user
       let update_data = UpdateUser {
           username: Some("updated_user"),
           email: Some("updated@example.com"),
           active: Some(false),
       };
       let updated_user = UserRepository::update(&mut conn, user.id, update_data)?;
       info!("Updated user: {:?}", updated_user);
   
       // Query all users
       let all_users = UserRepository::find_all(&mut conn)?;
       info!("All users: {:?}", all_users);
   
       // Delete a user
       let count = UserRepository::delete(&mut conn, updated_user.id)?;
       info!("Deleted {} user(s)", count);
   
       Ok(())
   }
   

Run the program

1. Make sure that OceanBase Database is running.

2. Make sure that the .env file is correctly configured with the database connection information.

3. Run the program:

   RUST_LOG=info cargo run
   

Code explanation

1. Project structure

   • db.rs: Database connection pool configuration
   • models/: Data model definitions
   • repositories/: Data access layer
   • migrations/: Database migration files
   • schema.rs: Automatically generated database schema

2. Main components

   • Use r2d2 to manage the database connection pool
   • Use diesel to provide type-safe query builders
   • Use dotenv to manage environment variables
   • Use log and pretty_env_logger for logging

3. Error handling

   • Use Rust's Result type for error handling
   • Provide detailed error messages and logs
   • Use the ? operator to simplify error propagation

4. Best practices

   • Separate data access logic from business logic
   • Use transactions to ensure data consistency
   • Implement appropriate error handling and logging
   • Use environment variables to manage sensitive information