sqlorm

An ergonomic, lightweight SQL ORM for Rust with type-safe query building and powerful entity relationships.

Sqlorm is a modern mini-ORM built on top of sqlx that provides compile-time safety, powerful macro-generated APIs, and an intuitive query builder. It's designed for developers who want the performance of sqlx with the convenience of an ORM.

Key Features

• Type-Safe: All queries are checked at compile-time
• Zero-Cost Abstraction: Minimal overhead over raw sqlx
• Macro-Powered: Rich APIs generated from simple struct definitions
• Relationships: Support for belongs_to, has_many and has_many relations with eager/lazy loading
• Multi-Database: PostgreSQL and SQLite support
• Powerful Querying: Fluent query builder with comprehensive filtering

Quick Start

Installation

Add to your Cargo.toml:

[dependencies]
sqlorm = { version = "0.8", features = ["postgres", "uuid" ] }
# important to use the same version of sqlx, to avoid compile time increase
sqlx={version = "=0.8.*"}

tokio = { version = "1.0", features = ["full"] }
chrono = { version = "0.4", features = ["serde"] }
uuid = { version = "1.0", features = ["v4", "serde"] }
serde = { version = "1.0", features = ["derive"] }

Database Support

Choose one feature:

• postgres - PostgreSQL support
• sqlite - SQLite support

Optional features:

• uuid - UUID support
• extra-traits - Additional query methods for better DX

Your First Entity

use sqlorm::prelude::*;
use chrono::{DateTime, Utc};

#[table(name = "users")]
#[derive(Debug, Clone, Default)]
pub struct User {
    #[sql(pk)]
    pub id: i64,

    #[sql(unique)]
    pub email: String,

    pub username: String,
    pub first_name: String,
    pub last_name: String,
    pub bio: Option<String>,

    #[sql(timestamp(created_at, chrono::Utc::now()))]
    pub created_at: DateTime<Utc>,

    #[sql(timestamp(updated_at, chrono::Utc::now()))]
    pub updated_at: DateTime<Utc>,
}

Usage Examples

Basic CRUD Operations

use sqlorm::prelude::*;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let pool = Pool::connect("postgres://user:pass@localhost/db").await?;

    // CREATE - Insert a new user
    let user = User {
        email: "alice@example.com".to_string(),
        username: "alice".to_string(),
        first_name: "Alice".to_string(),
        last_name: "Smith".to_string(),
        bio: Some("Rust developer".to_string()),
        ..Default::default()
    }
    .save(&pool)  // Returns the inserted user with generated ID and timestamp fields
    .await?;

    println!("Created user with ID: {}", user.id);

    // READ - Find by primary key
    let found_user = User::query().filter(User::ID.eq(user.id)).fetch_one(&pool)
        .await?
        .expect("User should exist");

    // READ - Find by unique field. Note, this requires `extra-traits` feature
    let found_by_email = User::find_by_email(&pool,"alice@example.com".to_string());
        .await?
        .expect("User should exist");

    // UPDATE - Modify and save
    let mut updated_user = found_user;
    updated_user.bio = Some("Senior Rust developer".to_string());
    let updated_user = updated_user.update().columns((User::BIO)).execute(&pool).await?;  // updated_at auto-updated

    user.delete().execute(&pool).await.unwrap()

    Ok(())
}

Advanced Querying

Sqlorm provides a powerful, type-safe query builder:

// Simple filtering
let active_users = User::query()
    .filter(User::BIO.is_not_null())
    .fetch_all(&pool)
    .await?;

// Comparison operators
let recent_users = User::query()
    .filter(User::CREATED_AT.gt(chrono::Utc::now() - chrono::Duration::days(30)))
    .filter(User::ID.ge(100))
    .fetch_all(&pool)
    .await?;

// Pattern matching
let rust_developers = User::query()
    .filter(User::BIO.like("%Rust%".to_string()))
    .fetch_all(&pool)
    .await?;

// Multiple conditions
let specific_users = User::query()
    .filter(User::ID.in_(vec![1, 2, 3, 4, 5]))
    .filter(User::EMAIL.ne("admin@example.com".to_string()))
    .fetch_all(&pool)
    .await?;

// Range queries
let mid_range_users = User::query()
    .filter(User::ID.between(10, 50))
    .fetch_one(&pool)  // Get first result
    .await?;

// NULL checks
let users_without_bio = User::query()
    .filter(User::BIO.is_null())
    .fetch_all(&pool)
    .await?;

Selective Field Queries

Optimize your queries by selecting only needed fields:

// Select specific fields as tuple
let (id, email) = User::query()
    .filter(User::USERNAME.eq("alice".to_string()))
    .select((User::ID, User::EMAIL))
    .fetch_one_as(&pool)
    .await?;

// Select multiple fields
let user_summaries: Vec<(String, String, Option<String>)> = User::query()
    .select((
        User::USERNAME,
        User::EMAIL,
        User::BIO,
    ))
    .fetch_all_as(&pool)
    .await?;

Relationships

Define and work with entity relationships:

#[table(name = "posts")]
#[derive(Debug, Clone, Default)]
pub struct Post {
    #[sql(pk)]
    pub id: i64,

    pub title: String,
    pub content: String,

    // Foreign key relationship
    #[sql(relation(belongs_to -> User, relation = "author", on = id))]
    pub user_id: i64,

    #[sql(timestamp(created_at, chrono::Utc::now()))]
    pub created_at: DateTime<Utc>,
}

#[table(name = "users")]
#[derive(Debug, Clone, Default)]
pub struct User {
    #[sql(pk)]
    // Define reverse relationship
    #[sql(relation(has_many -> Post, relation = "posts", on = user_id))]
    pub id: i64,

    // ... other fields
}

// Lazy loading - fetch related data when needed
let user = User::find_by_id(&pool, 1).await?.expect("User exists");
let user_posts = user.posts(&pool).await?;  // Separate query

let post = Post::find_by_id(&pool, 1).await?.expect("Post exists");
let author = post.author(&pool).await?.expect("Author exists");

let user_with_posts = User::query()
    .filter(User::ID.eq(1))
    .with_posts()
    .fetch_one(&pool)
    .await?;

let posts = user_with_posts.posts.expect("Posts loaded");

let post_with_author = Post::query()
    .filter(Post::ID.eq(1))
    .with_author()
    .fetch_one(&pool)
    .await?;

let author = post_with_author.author.expect("Author loaded");

Automatic Timestamps

SQLOrm automatically handles timestamp fields:

#[table]
#[derive(Debug, Clone, Default)]
pub struct Article {
    #[sql(pk)]
    pub id: i64,

    pub title: String,

    // Automatically set on insert
    #[sql(timestamp(created_at, chrono::Utc::now()))]
    pub created_at: DateTime<Utc>,

    // Automatically updated on save
    #[sql(timestamp(updated_at, chrono::Utc::now()))]
    pub updated_at: DateTime<Utc>,

    // Optional soft delete timestamp
    #[sql(timestamp(deleted_at, chrono::Utc::now()))]
    pub deleted_at: Option<DateTime<Utc>>,
}

// Custom timestamp functions
#[sql(timestamp(created_at, get_custom_timestamp()))]
pub created_at: i64,  // Unix timestamp

fn get_custom_timestamp() -> i64 {
    SystemTime::now()
        .duration_since(UNIX_EPOCH)
        .unwrap()
        .as_secs() as i64
}

Multiple Primary Key Types

SQLOrm supports various primary key types:

use uuid::Uuid;

// Auto-incrementing integer
#[table]
#[derive(Debug, Clone, Default)]
pub struct User {
    #[sql(pk)]
    pub id: i64,  // BIGSERIAL in PostgreSQL
    // ...
}

// UUID primary key
#[table]
#[derive(Debug, Clone, Default)]
pub struct Session {
    #[sql(pk)]
    pub id: Uuid,  // Auto-generated UUID (PostgreSQL only. Sqlite does not like UUIDs)
    // ...
}

// Custom primary key
#[table]
#[derive(Debug, Clone, Default)]
pub struct Setting {
    #[sql(pk)]
    pub key: String,  // String primary key
    pub value: String,
    // ...
}

Working with Options and Different Types

#[table]
#[derive(Debug, Clone, Default)]
pub struct Product {
    #[sql(pk)]
    pub id: i64,

    pub name: String,
    pub description: Option<String>,    // Nullable text
    pub price: f64,                     // Numeric
    pub is_active: bool,                // Boolean
    pub tags: Option<Vec<String>>,      // JSON array (PostgreSQL)
    pub metadata: Option<serde_json::Value>,  // JSON

    #[sql(timestamp(created_at, chrono::Utc::now()))]
    pub created_at: DateTime<Utc>,
}

🔧 Generated API Reference

The #[table] macro generates extensive APIs for each entity:

Core Methods

• save() - Insert or update (smart detection)
• insert() - Force insert
• update() - Force update

With extra-traits feature:

• find_by_id() - Find by primary key
• find_by_<unique_field>() - Find by unique fields

Query Builder

• query() - Start query builder
• filter() - Add WHERE conditions
• select() - Specify columns to fetch
• fetch_one() - Get single result
• fetch_all() - Get all results
• fetch_one_as() - Get result as tuple/custom type
• fetch_all_as() - Get results as Vec of tuples/custom type

Filter Operators

• eq() / ne() - Equality / Not equal
• gt() / ge() - Greater than / Greater equal
• lt() / le() - Less than / Less equal
• like() - Pattern matching
• in_() / not_in() - List membership
• between() / not_between() - Range queries
• is_null() / is_not_null() - NULL checks

Relationships (when defined)

• <relation_name>() - Lazy load related entities
• with_<relation_name>() - Eager load in query builder

Attribute Reference

Table Attributes

#[table]                           // Use struct name as table name
#[table(name = "custom_name")]     // Custom table name

Field Attributes

#[sql(pk)]                                    // Primary key
#[sql(unique)]                                // Unique constraint
#[sql(timestamp(created_at, chrono::Utc::now()))]  // Auto timestamp
#[sql(relation(belongs_to -> Parent, relation = "parent", on = id))]
#[sql(relation(has_one -> Sister, relation = "sister", on = id))]
#[sql(relation(has_many -> Child, relation = "children", on = parent_id))]

Testing

Using just runner:

# Test with all drivers
just test

# Test with specific driver

just test postgres # or sqlite

# Run examples
just examples

Using cargo :

# Test with PostgreSQL
cargo test --features postgres

# Test with SQLite
cargo test --features sqlite

# Run examples
cargo run --example basic --features "postgres uuid chrono"
cargo run --example crud --features "postgres uuid chrono"
cargo run --example relations --features "postgres uuid chrono"

More Examples

Check the examples/ directory for complete working examples:

• basic: Simple CRUD operations
• crud: Comprehensive CRUD with multiple entities
• relations: Working with entity relationships

Contributing

Contributions are welcome! Please feel free to submit a Pull Request. Make sure to:

1. Run tests with both database features
2. Follow the existing code style
3. Add tests for new features
4. Update documentation as needed

License

This project is licensed under the Apache License 2.0 - see the LICENSE file for details.

Acknowledgments

Built on the excellent sqlx crate. Inspired by Rails Active Record and Laravel Eloquent.