typed_sqlx_client

A type-safe, extensible Rust library for managing multiple SQL databases and tables with sqlx. Build robust database applications with automatic CRUD operations, flexible queries, and compile-time safety.

✨ Key Features

🚀 Automatic CRUD: #[derive(CrudOpsRef)] generates complete CRUD operations
🛡️ Type Safety: Compile-time prevention of database/table mix-ups
🗄️ Multi-Database: MySQL, PostgreSQL, and SQLite support with unified API
🎛️ Per-Table Traits: Each table can implement custom traits independently (not monolithic SQL client)
🔍 Flexible Queries: Both type-safe and JSON-based SELECT operations
🌐 Framework Ready: Perfect for actix-web, warp, and other async frameworks
⚡ Zero Runtime Cost: All type safety is compile-time only

🚀 Quick Start

Add to your Cargo.toml:

[dependencies]
typed_sqlx_client = "0.2.0"
sqlx = { version = "0.8", features = ["postgres", "runtime-tokio", "uuid"] }

Define your entity and get instant CRUD:

use typed_sqlx_client::{CrudOpsRef, SqlPool, SelectOnlyQuery};
use sqlx::{PgPool, FromRow};
use uuid::Uuid;

#[derive(FromRow, CrudOpsRef, Debug)]
#[crud(table = "users", db = "postgres")]
struct User {
    #[crud(primary_key)]
    id: Option<Uuid>,
    #[crud(rename = "user_name")]  // Maps to 'user_name' column
    name: String,
    email: String,
}

struct MainDB;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    // Setup typed pool
    let pool = PgPool::connect("postgres://...").await?;
    let sql_pool = SqlPool::from_pool::<MainDB>(pool);
    let user_table = sql_pool.get_table::<User>();
    
    // CRUD operations work immediately!
    let user = User { 
        id: Some(Uuid::new_v4()), 
        name: "Alice".to_string(), 
        email: "alice@example.com".to_string() 
    };
    
    user_table.insert(&user).await?;
    let found = user_table.get_by_id(&user.id.unwrap()).await?;
    
    // Type-safe queries
    let users: Vec<User> = user_table
        .execute_select_as_only::<User>("SELECT * FROM users WHERE active = true")
        .await?;
    
    // Dynamic JSON queries  
    let json_data = user_table
        .execute_select_only("SELECT name, email FROM users")
        .await?;
        
    Ok(())
}

📋 What's New in v0.2.0

🎉 Major Improvements

Replaced CrudOps trait with powerful #[derive(CrudOpsRef)] macro
Added execute_select_as_only<T>() for type-safe SELECT queries
Enhanced field mapping with #[crud(rename = "...")]
Direct trait implementation on SqlTable (no more macros!)
Better error messages and IDE support

🔄 Migration from v0.1.x

// OLD (v0.1.x) - Remove this
impl CrudOps<i64, User> for SqlTable<Postgres, MainDB, User> { /* manual impl */ }

// NEW (v0.2.0) - Just add this!  
#[derive(FromRow, CrudOpsRef)]
#[crud(table = "users", db = "postgres")]
struct User { /* fields */ }

🗄️ Database Support

Database	Status	CRUD Support	Query Support	Example
PostgreSQL	✅ Stable	✅ Full	✅ Both modes	`db = "postgres"`
MySQL	✅ Stable	✅ Full	✅ Both modes	`db = "mysql"`
SQLite	✅ Stable	✅ Full	✅ Both modes	`db = "sqlite"`

📚 Advanced Examples

Multi-Database Setup

struct MainDatabase;
struct AnalyticsDatabase;
struct CacheDatabase;

// Type safety prevents mixing databases!
let main_pool = SqlPool::from_pool::<MainDatabase>(pg_pool);
let analytics_pool = SqlPool::from_pool::<AnalyticsDatabase>(mysql_pool);
let cache_pool = SqlPool::from_pool::<CacheDatabase>(sqlite_pool);

let users = main_pool.get_table::<User>();          // ✅ 
let events = analytics_pool.get_table::<Event>();   // ✅
// let wrong = main_pool.get_table::<Event>();      // ❌ Compile error!

Custom Field Mapping

#[derive(FromRow, CrudOpsRef)]
#[crud(table = "user_profiles", db = "postgres")]
struct UserProfile {
    #[crud(primary_key)]
    id: Option<Uuid>,
    
    #[crud(rename = "full_name")]
    name: String,                    // Rust: name ↔ DB: full_name
    
    #[crud(rename = "email_address")]  
    email: String,                   // Rust: email ↔ DB: email_address
    
    #[crud(rename = "birth_date")]
    birthday: Option<NaiveDate>,     // Rust: birthday ↔ DB: birth_date
    
    is_active: bool,                 // Same name in both
}

Advanced Queries

// Aggregations with type safety
let stats: Vec<(String, i64, f64)> = user_table
    .execute_select_as_only::<(String, i64, f64)>(
        "SELECT department, COUNT(*), AVG(salary) FROM users GROUP BY department"
    ).await?;

// Custom projections  
let user_emails: Vec<(String,)> = user_table
    .execute_select_as_only::<(String,)>("SELECT email FROM users WHERE active = true")
    .await?;

// Dynamic queries for unknown schemas
let dynamic_data = user_table
    .execute_select_only("SELECT * FROM user_settings WHERE user_id = 123")
    .await?;

for row in dynamic_data {
    if let Some(setting_name) = row.get("setting_name").and_then(|v| v.as_str()) {
        println!("Setting: {}", setting_name);
    }
}

Framework Integration (actix-web)

use actix_web::{web, App, HttpServer, HttpResponse, Result};

async fn get_user(
    user_table: web::Data<SqlTable<sqlx::Postgres, MainDB, User>>,
    path: web::Path<Uuid>
) -> Result<HttpResponse> {
    let user_id = path.into_inner();
    match user_table.get_by_id(&user_id).await {
        Ok(Some(user)) => Ok(HttpResponse::Ok().json(user)),
        Ok(None) => Ok(HttpResponse::NotFound().finish()),
        Err(_) => Ok(HttpResponse::InternalServerError().finish()),
    }
}

#[actix_web::main]
async fn main() -> std::io::Result<()> {
    let pool = SqlPool::from_pool::<MainDB>(create_pool().await);
    let user_table = pool.get_table::<User>();
    
    HttpServer::new(move || {
        App::new()
            .app_data(web::Data::new(user_table.clone()))
            .route("/users/{id}", web::get().to(get_user))
    })
    .bind("127.0.0.1:8080")?
    .run()
    .await
}

🏗️ Multi-Table Custom Traits Example

Perfect for scenarios where different tables need different business logic:

use typed_sqlx_client::{CrudOpsRef, SqlPool, SelectOnlyQuery};
use sqlx::FromRow;

// User table with analytics capabilities
#[derive(FromRow, CrudOpsRef)]
#[crud(table = "users", db = "postgres")]
struct User {
    #[crud(primary_key)]
    id: Option<i64>,
    name: String,
    email: String,
}

// Custom trait for user analytics
trait UserAnalytics {
    async fn get_active_users(&self) -> Result<Vec<User>, sqlx::Error>;
    async fn get_user_engagement_stats(&self) -> Result<EngagementStats, sqlx::Error>;
}

impl UserAnalytics for SqlTable<sqlx::Postgres, MainDB, User> {
    async fn get_active_users(&self) -> Result<Vec<User>, sqlx::Error> {
        self.execute_select_as_only::<User>(
            "SELECT * FROM users WHERE last_login > NOW() - INTERVAL '30 days'"
        ).await
    }
    
    async fn get_user_engagement_stats(&self) -> Result<EngagementStats, sqlx::Error> {
        let stats: Vec<(i64, f64)> = self.execute_select_as_only::<(i64, f64)>(
            "SELECT COUNT(*), AVG(session_duration) FROM user_sessions WHERE created_at > NOW() - INTERVAL '7 days'"
        ).await?;
        // ... process stats
        todo!()
    }
}

// Post table with content moderation
#[derive(FromRow, CrudOpsRef)]
#[crud(table = "posts", db = "postgres")]
struct Post {
    #[crud(primary_key)]
    id: Option<i64>,
    title: String,
    content: String,
    status: String,
}

// Custom trait for content moderation
trait ContentModeration {
    async fn moderate_post(&self, post_id: i64) -> Result<ModerationResult, sqlx::Error>;
    async fn get_flagged_posts(&self) -> Result<Vec<Post>, sqlx::Error>;
}

impl ContentModeration for SqlTable<sqlx::Postgres, MainDB, Post> {
    async fn moderate_post(&self, post_id: i64) -> Result<ModerationResult, sqlx::Error> {
        // Custom moderation logic specific to posts
        todo!()
    }
    
    async fn get_flagged_posts(&self) -> Result<Vec<Post>, sqlx::Error> {
        self.execute_select_as_only::<Post>(
            "SELECT * FROM posts WHERE status = 'flagged' ORDER BY created_at DESC"
        ).await
    }
}

// Usage: Each table has both CRUD + custom capabilities
struct MainDB;

# async fn example() -> Result<(), sqlx::Error> {
let pool = SqlPool::from_pool::<MainDB>(pg_pool);

let user_table = pool.get_table::<User>();
let post_table = pool.get_table::<Post>();

// Standard CRUD (from derive macro)
user_table.insert(&user).await?;
post_table.insert(&post).await?;

// Table-specific operations (custom traits)
let active_users = user_table.get_active_users().await?;
let flagged_posts = post_table.get_flagged_posts().await?;
let user_stats = user_table.get_user_engagement_stats().await?;
let moderation_result = post_table.moderate_post(123).await?;
# Ok(())
# }

🔧 Attribute Reference

Struct-level Attributes

#[crud(table = "table_name")]          // Custom table name
#[crud(db = "postgres|mysql|sqlite")]  // Database type  
#[crud(table = "users", db = "postgres")]  // Combined

Field-level Attributes

#[crud(primary_key)]                   // Mark as primary key
#[crud(rename = "column_name")]         // Map to different column name

📖 Documentation

🤝 Contributing

Contributions are welcome! Please feel free to submit a Pull Request. For major changes, please open an issue first to discuss what you would like to change.

📄 License

This project is licensed under either of

Apache License, Version 2.0, (LICENSE-APACHE or http://www.apache.org/licenses/LICENSE-2.0)
MIT License (LICENSE-MIT or http://opensource.org/licenses/MIT)

at your option.

⭐ Acknowledgments

Built on top of the excellent sqlx crate
Inspired by the need for type-safe multi-database applications
Thanks to all contributors and users!

typed_sqlx_client 0.2.1