Connect to OceanBase Database by using Diesel

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

Prerequisites

Before you begin, make sure you have the following installed:

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

Install the 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 the 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"  # Notice: 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 environment variables:

   # 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 up the table structure
   diesel setup
   

3. Create and write the table files:

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

In src/lib.rs, export the database module:

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 the module file

1. Create a file named src/repositories/mod.rs:

   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 the user
       let user = UserRepository::find(&mut conn, created_user.id)?;
       info!("Found user: {:?}", user);
   
       // Update the 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 the 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 has been correctly configured with database connection information.

3. Run the program:

   RUST_LOG=info cargo run
   

Code overview

1. Project structure

   • db.rs: Configures the database connection pool
   • models/: Defines data models
   • repositories/: Implements the data access layer
   • migrations/: Contains migration files
   • schema.rs: Auto-generated database schema

2. Key components

   • Use r2d2 to manage the database connection pool
   • Use diesel for 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
   • Uses 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