Database ORM

Oxidite provides a powerful Object-Relational Mapping (ORM) system that allows you to work with databases using Rust structs. This chapter covers how to define models, perform database operations, and use relationships.

Overview

The Oxidite ORM provides:

• Type-safe database operations
• Model definitions with derive macros
• Relationship management
• Migrations and schema management
• Query building capabilities
• Validation and hooks

Model Definition

Define your database models using the Model derive macro:

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

#[derive(Model, sqlx::FromRow, Serialize, Deserialize)]
#[model(table = "users")]
pub struct User {
    pub id: i64,
    pub email: String,
    pub name: String,
    pub created_at: i64,
    pub updated_at: i64,
    pub active: bool,
}

// Timestamps (created_at, updated_at) are automatically set by the Model derive macro.

Basic CRUD Operations

Creating Records

use oxidite::prelude::*;

async fn create_user() -> Result<()> {
    let user = User {
        id: 0, // Will be auto-generated
        email: "john@example.com".to_string(),
        name: "John Doe".to_string(),
        created_at: 0, // Auto-set by Model derive
        updated_at: 0, // Auto-set by Model derive
        active: true,
    };
    
    user.save(&db).await?;
    println!("Created user");
    
    Ok(())
}

// Alternative: Using create method directly
async fn create_user_alternative(db: &DbPool) -> oxidite::db::Result<()> {
    let mut user = User {
        id: 0,
        email: "jane@example.com".to_string(),
        name: "Jane Smith".to_string(),
        created_at: 0, // Auto-set by Model derive
        updated_at: 0, // Auto-set by Model derive
        active: true,
    };
    user.create(db).await?;
    
    println!("Created user: {}", user.name);
    Ok(())
}

Reading Records

async fn find_users() -> Result<()> {
    // Find all users
    let all_users = User::all(&db).await?;
    println!("Found {} users", all_users.len());
    
    // Find user by ID
    if let Some(user) = User::find(&db, 1).await? {
        println!("Found user: {}", user.name);
    } else {
        println!("User not found");
    }
    
    // Find users with conditions (simplified example)
    let active_users = User::query().filter_eq("active = true").await?;
    println!("Found {} active users", active_users.len());
    
    Ok(())
}

Updating Records

async fn update_user() -> Result<()> {
    if let Some(mut user) = User::find(&db, 1).await? {
        user.name = "John Updated".to_string();
        user.updated_at = now();
        
        let updated_user = user.save(&db).await?;
        println!("Updated user: {}", updated_user.name);
    }
    
    Ok(())
}

// Bulk update
async fn bulk_update() -> Result<()> {
    let updated_count = // Use ModelQuery or raw SQL for bulk operations
    // 
        "active = false",
        &[("updated_at", &now())]
    ).await?;
    
    println!("Updated {} users", updated_count);
    Ok(())
}

Deleting Records

async fn delete_user() -> Result<()> {
    if let Some(user) = User::find(&db, 1).await? {
        user.delete(&db).await?;
        println!("Deleted user: {}", user.name);
    }
    
    Ok(())
}

// Bulk delete
async fn bulk_delete() -> Result<()> {
    let deleted_count = // Use ModelQuery or raw SQL for bulk operations
    // "created_at < '2023-01-01'").await?;
    println!("Deleted {} old users", deleted_count);
    Ok(())
}

Relationships

Define relationships between models:

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

#[derive(Model, sqlx::FromRow, Serialize, Deserialize)]
#[model(table = "posts")]
pub struct Post {
    pub id: i64,
    pub title: String,
    pub content: String,
    pub user_id: i64,  // Foreign key
    pub created_at: i64,
}

#[derive(Model, sqlx::FromRow, Serialize, Deserialize)]
#[model(table = "comments")]
pub struct Comment {
    pub id: i64,
    pub content: String,
    pub user_id: i64,   // Foreign key
    pub post_id: i64,  // Foreign key
    pub created_at: i64,
}

// Update User model to include relationships
#[derive(Model, sqlx::FromRow, Serialize, Deserialize)]
#[model(table = "users")]
pub struct User {
    pub id: i64,
    pub email: String,
    pub name: String,
    pub created_at: i64,
    pub updated_at: i64,
    pub active: bool,
}

// Access related records
async fn work_with_relationships() -> Result<()> {
    // Find a user
    if let Some(user) = User::find(&db, 1).await? {
        // Find user's posts
        let posts = Post::query().filter_eq("user_id", user.id).fetch_all(&db).await?;
        println!("User {} has {} posts", user.name, posts.len());
        
        // Find user's comments
        let comments = Comment::query().filter_eq("user_id", user.id).fetch_all(&db).await?;
        println!("User {} has {} comments", user.name, comments.len());
    }
    
    Ok(())
}

Query Building

Use the query builder for complex queries:

use oxidite::prelude::*;

async fn complex_queries() -> Result<()> {
    // Find users with custom conditions
    let users = User::query().filter_eq("name LIKE '%John%' AND active = true").await?;
    println!("Found {} users matching criteria", users.len());
    
    // Find with ordering
    let recent_users = User::query().filter_eq("active = true")
        .order_by("created_at DESC")
        .limit(10)
        .await?;
    
    // Find with joins (conceptual - exact syntax may vary)
    let users_with_posts = execute_raw_query("
        SELECT u.*, COUNT(p.id) as post_count 
        FROM users u 
        LEFT JOIN posts p ON u.id = p.user_id 
        WHERE u.active = true 
        GROUP BY u.id 
        ORDER BY post_count DESC
    ").await?;
    
    Ok(())
}

async fn execute_raw_query<T>(_sql: &str) -> Result<Vec<T>> {
    // Implementation would depend on the specific database connector
    Ok(vec![])
}

Migrations

Database migrations allow you to manage schema changes:

// Migrations are SQL files managed by the CLI.
// Generate one with: oxidite generate migration create_users_table
// The file uses -- migrate:up and -- migrate:down sections.
//
// Example generated migration:
    fn version(&self) -> i64 {
        20231201000001  // YYYYMMDDHHMMSS
    }
    
    fn name(&self) -> &'static str {
        "create_users_table"
    }
    
    fn up(&self) -> &'static str {
        r#"
        CREATE TABLE users (
            id SERIAL PRIMARY KEY,
            email VARCHAR(255) UNIQUE NOT NULL,
            name VARCHAR(255) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            active BOOLEAN DEFAULT TRUE
        )
        "#
    }
    
    fn down(&self) -> &'static str {
        "DROP TABLE users"
    }
}

pub struct CreatePostsTable;

impl Migration for CreatePostsTable {
    fn version(&self) -> i64 {
        20231201000002
    }
    
    fn name(&self) -> &'static str {
        "create_posts_table"
    }
    
    fn up(&self) -> &'static str {
        r#"
        CREATE TABLE posts (
            id SERIAL PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            content TEXT NOT NULL,
            user_id INTEGER REFERENCES users(id),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        "#
    }
    
    fn down(&self) -> &'static str {
        "DROP TABLE posts"
    }
}

Validation

Add validation to your models:

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

#[derive(Model, sqlx::FromRow, Serialize, Deserialize)]
#[model(table = "users")]
pub struct ValidatedUser {
    pub id: i64,
    
    // String length validation
    #[validate(length(min = 3, max = 50))]
    pub name: String,
    
    // Email format and database uniqueness validation
    #[validate(email, unique(table = "users", column = "email"))]
    pub email: String,
    
    // Numeric range validation
    #[validate(range(min = 18, max = 120))]
    pub age: i64,
    
    // URL format validation
    #[validate(url)]
    pub website: String,
    
    // Regex and custom function validation
    #[validate(regex("^[a-zA-Z0-9]+$"), custom("check_username_blacklist"))]
    pub username: String,

    pub created_at: i64,
    pub updated_at: i64,
}

impl ValidatedUser {
    // Custom validation logic
    async fn check_username_blacklist(&self, _db: &impl Database) -> std::result::Result<(), String> {
        if self.username == "admin" || self.username == "root" {
            Err("This username is reserved".to_string())
        } else {
            Ok(())
        }
    }
}

// To run these validations, use the save_checked method instead of save:
//   user.save_checked(&db).await?;

Relationships

Oxidite provides a fluent ActiveRecord-style syntax for defining relationships between models. Using the has_many, has_one, and belongs_to attributes, the framework will automatically generate asynchronous lazy-loading accessor methods for your models.

use oxidite::prelude::*;

#[derive(Model, Clone, Serialize, Deserialize)]
#[has_many(model = "Post", foreign_key = "user_id", name = "posts")]
#[has_one(model = "Profile", foreign_key = "user_id", name = "profile")]
pub struct User {
    pub id: i64,
    pub name: String,
}

#[derive(Model, Clone, Serialize, Deserialize)]
#[belongs_to(model = "User", foreign_key = "user_id", name = "user")]
pub struct Post {
    pub id: i64,
    pub user_id: i64,
    pub title: String,
}

Accessing Relationships

Once defined, you can fetch related models asynchronously by passing a database reference:

async fn fetch_user_data(db: &impl Database) -> Result<(), Box<dyn std::error::Error>> {
    let user = User::find(db, 1).await?.unwrap();
    
    // Fetch all posts belonging to the user (Has Many)
    let posts: Vec<Post> = user.posts(db).await?;
    println!("User has {} posts", posts.len());
    
    // Fetch the user's profile (Has One)
    if let Some(profile) = user.profile(db).await? {
        println!("Profile Bio: {}", profile.bio);
    }
    
    // Fetch the post's author (Belongs To)
    if let Some(post) = posts.first() {
        let author = post.user(db).await?.unwrap();
        println!("Author name: {}", author.name);
    }
    
    Ok(())
}

Transactions

Perform operations within transactions:

use oxidite::db::DbPool;

async fn transaction_example(db: &DbPool) -> oxidite::db::Result<()> {
    // with_transaction auto-commits on Ok, auto-rollbacks on Err
    db.with_transaction(|tx| async move {
    
    match async {
        // Create user
        let user = User {
            id: 0,
            email: "transaction@example.com".to_string(),
            name: "Transaction User".to_string(),
            created_at: 0, // Auto-set by Model derive
            updated_at: 0, // Auto-set by Model derive
            active: true,
        };
        let saved_user = user.save(&db).await?;
        
        // Create a post for the user
        let post = Post {
            id: 0,
            title: "First Post".to_string(),
            content: "Hello, world!".to_string(),
            user_id: saved_user.id,
            created_at: 0, // Auto-set by Model derive
        };
        post.save(&db).await?;
        
        Ok::<_, Error>(saved_user.id)
    }.await {
        Ok(user_id) => {
            // Commit the transaction
            tx.commit().await?;
            println!("Successfully created user {} and associated post", user_id);
        }
        Err(e) => {
            // Rollback the transaction
            tx.rollback().await?;
            println!("Transaction failed: {:?}", e);
            return Err(e);
        }
    }
    
    Ok(())
}

async fn begin_transaction() -> Result<Transaction> {
    // Implementation would depend on the database connector
    Ok(Transaction {})
}

pub struct Transaction;

impl Transaction {
    pub async fn commit(self) -> Result<()> {
        Ok(())
    }
    
    pub async fn rollback(self) -> Result<()> {
        Ok(())
    }
}

Soft Deletes

Models can support soft deletes:

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

#[derive(Model, sqlx::FromRow, Serialize, Deserialize)]
// Soft-delete is auto-detected: just include a `deleted_at: Option<i64>` field.
#[model(table = "soft_delete_users")]
pub struct SoftDeleteUser {
    pub id: i64,
    pub email: String,
    pub name: String,
    pub deleted_at: Option<i64>,
    pub created_at: i64,
    pub updated_at: i64,
}

async fn soft_delete_example(db: &DbPool) -> Result<()> {
    // Find active users (deleted_at IS NULL is added automatically)
    let active_users = SoftDeleteUser::all(db).await?;
    
    // Include soft-deleted rows with .with_deleted()
    let all_users = SoftDeleteUser::query().with_deleted().fetch_all(db).await?;
    
    // Soft delete a user (sets deleted_at to now)
    if let Some(user) = SoftDeleteUser::find(db, 1).await? {
        user.delete(db).await?;
        println!("User soft-deleted");
    }
    
    // Force-delete permanently removes the row
    if let Some(user) = SoftDeleteUser::find(db, 2).await? {
        user.force_delete(db).await?;
        println!("User permanently deleted");
    }
    
    Ok(())
}

Connection Management

Configure database connections:

use oxidite::db::{DbPool, PoolOptions};

async fn configure_database() -> oxidite::db::Result<DbPool> {
    let url = std::env::var("DATABASE_URL")
        .unwrap_or_else(|_| "sqlite://data.db".to_string());
    
    // Connect with default pool settings (max 10 connections)
    let db = DbPool::connect(&url).await?;
    
    Ok(db)
}

async fn configure_database_advanced() -> oxidite::db::Result<DbPool> {
    let url = std::env::var("DATABASE_URL").unwrap();
    
    let options = PoolOptions {
        max_connections: 20,
        min_connections: 2,
        connect_timeout: std::time::Duration::from_secs(10),
        idle_timeout: Some(std::time::Duration::from_secs(300)),
    };
    
    let db = DbPool::connect_with_options(&url, options).await?;
    
    Ok(db)
}

Error Handling

Handle database errors appropriately:

use oxidite::prelude::*;

async fn error_handling_example() -> Result<()> {
    match User::find(&db, 999999).await {
        Ok(Some(user)) => {
            println!("Found user: {}", user.name);
        }
        Ok(None) => {
            println!("User not found");
        }
        Err(Error::InternalServerError(msg)) => {
            eprintln!("Database error: {}", msg);
            return Err(Error::InternalServerError(msg));
        }
        Err(e) => {
            eprintln!("Unexpected error: {:?}", e);
            return Err(e);
        }
    }
    
    Ok(())
}

Performance Considerations

1. Use Indexes: Add database indexes for frequently queried fields
2. Batch Operations: Use batch operations when possible
3. Connection Pooling: Use connection pooling for better performance
4. N+1 Queries: Be aware of N+1 query problems with relationships
5. Caching: Consider caching frequently accessed data

Security Considerations

1. SQL Injection: The ORM protects against SQL injection by using parameterized queries
2. Input Validation: Always validate input before saving to the database
3. Access Control: Implement proper access control for database operations
4. Data Encryption: Consider encrypting sensitive data at rest

Summary

The Oxidite ORM provides a comprehensive solution for database operations:

• Define models with the Model derive macro
• Perform CRUD operations with type safety
• Define and work with relationships
• Handle migrations for schema management
• Add validation and hooks to models
• Use transactions for data consistency
• Support for soft deletes
• Proper error handling and security considerations

The ORM abstracts away the complexity of raw SQL while providing the flexibility to execute custom queries when needed.