KitX - Lightweight SQL Builder for Rust

A minimalistic SQL builder library based on sqlx, supporting SQLite, MySQL/MariaDB, and PostgreSQL. It offers efficient database operations with soft delete capabilities and global filters, enabling developers to interact with databases more effectively.

Features

Core Functionality

• Efficient CRUD Operations
  insert_one, insert_many, update_by_key, update_one, upsert_by_key,
  upsert_many, delete_by_key, delete_by_cond, delete_many
  with transaction support

• Advanced Queries
  get_list, get_by_key, get_one, get_list_paginated, get_list_by_cursor,
  exists, count

• Soft Delete Management
  restore_one, restore_many
  with global configuration

• Flexible Query Building
  Supports JOINs, CASE WHEN, WITH CTE, and aggregations. Supports ON CONFLICT/DUPLICATE KEY (upsert) and RETURNING for conflict resolution and data retrieval.

• Code Characteristics

  • No Macros: Public interfaces avoid macros, ensuring transparency and maintainability.
  • No .unwrap() or .expect(): Prevents runtime panics by promoting robust error handling.

Key Advantages

• 🚀 No ORM Overhead - Direct SQL interaction with builder pattern
• 🔧 Field Access API - Utilizes field_access for field operations
• 🌍 Global Filters - Apply tenant ID or soft delete filters across all queries
• 📦 Extensible - Easily add custom operations and query modifiers

Quick Start

1. Add Dependency

# Default SQL Builder, completely decoupled from any external libraries.
kitx = "0.0.11"

# For SQLite only
kitx = { version = "0.0.11", features = ["sqlite"] }

# For MySQL/MariaDB only
kitx = { version = "0.0.11", features = ["mysql"] }

# For PostgreSQL only
kitx = { version = "0.0.11", features = ["postgres"] }

2. Basic Usage

use kitx::sqlite::{sql::Select, sql::Insert, sql::col, operations::Operations};

// SQL Builder Example
// AND and OR conditions can be applied either within filter clauses or directly in the builder.
let query = Select::columns(&["id", "name"])
    .from("users")
    .where_(col("age").eq(23))
    .and(col("salary").gt(4500))
    .or(col("status").in_(vec!["active", "pending"]))
    .order_by("created_at", false)
    .build().0;

let query2 = Insert::into("users")
    .columns(&["id", "name"])
    .values(&[22, "John Doe"])
    .build().0;
  
// CRUD Operations
// KitX does not support composite primary keys. For such cases, please use constraints instead.
let op = Operations::new("articles", ("article_id", true));
let article = Article {
    id: 22,
    title: "Rust Best Practices".into(),
    content: "...".into(),
};

// Insert with transaction
op.insert_one(article).await?;

3. Pagination Example

let results = op.get_list_paginated(10, 2, empty_query()).await?;

let results = op.get_list_by_cursor(10, |&mut builder|{
    builder.where_mut(col("created_at").gt(DateTime::now()));
}).await?;

4. Optional: Global Configuration

// Soft delete configuration
set_global_soft_delete_field("deleted_at", vec!["audit_logs"]);

// Global_filter is applied on a per-thread basis.
// Multi-tenant filtering
set_global_filter(col("tenant_id").eq(123)), vec!["system_metrics"]);

5. More Usage Examples

For more detailed usage examples and advanced scenarios, please refer to the test cases provided in the repository.

License

MIT License