Statiq - MSSQL Service Library

High-performance, zero-overhead Rust database access layer for SQL Server

Statiq provides compile-time SQL generation, async CRUD, stored procedure execution, and connection pooling for MSSQL databases.

🚀 Installation

Add to your Cargo.toml:

[dependencies]
statiq = "0.1"
tokio = { version = "1", features = ["full"] }
serde = { version = "1", features = ["derive"] }
chrono = "0.4"
rust_decimal = { version = "1", features = ["serde-with-str"] }
uuid = { version = "1", features = ["v4", "serde"] }

# Optional integrations
statiq = { version = "0.1", features = ["axum"] }  # for web APIs

📚 Quick Start

1. Configuration (config.json)

{
  "mssql": {
    "connection_string": "Driver={ODBC Driver 18 for SQL Server};Server=localhost,1433;Database=MyDb;UID=sa;PWD=password;Encrypt=yes;TrustServerCertificate=yes",
    "pool": {
      "min_size": 2,
      "max_size": 20,
      "checkout_timeout_ms": 5000,
      "idle_timeout_ms": 300000,
      "max_lifetime_ms": 1800000,
      "validation_interval_ms": 60000
    }
  },
  "redis": {
    "url": "redis://127.0.0.1:6379",
    "default_ttl_seconds": 300
  },
  "logging": {
    "level": "info",
    "json": false
  }
}

2. Define Your Entity

use statiq::SqlEntity;

#[derive(SqlEntity, serde::Serialize, serde::Deserialize, Clone)]
#[sql_table("Users", schema = "dbo")]
pub struct User {
    #[sql_primary_key(identity)]
    pub id: i32,

    pub email: String,
    pub name: String,
    
    #[sql_default]
    pub created_at: chrono::DateTime<chrono::Utc>,

    #[sql_ignore]
    pub is_admin: bool,
}

3. Use in Your Handler

use statiq::SqlServiceFactory;
use tokio_util::sync::CancellationToken;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let factory = SqlServiceFactory::new().await?;
    let service = factory.create_service();
    
    let ct = CancellationToken::new();
    
    // Get all users
    let users: Vec<User> = service.list(&ct).await?;
    
    // Get by ID
    let user = service.get_by_id(1, &ct).await?;
    
    // Create
    let new_user = User {
        id: 0,
        email: "user@example.com".to_string(),
        name: "John Doe".to_string(),
        created_at: chrono::Utc::now(),
        is_admin: false,
    };
    let created = service.create(&new_user, &ct).await?;
    
    // Update
    let mut user = service.get_by_id(1, &ct).await?;
    user.name = "Jane Doe".to_string();
    service.update(&user, &ct).await?;
    
    // Delete
    service.delete_by_id(1, &ct).await?;
    
    Ok(())
}

📖 Core Features

Entity Macro Attributes

Attribute	Location	Purpose
`#[sql_table("Name", schema = "dbo")]`	struct	Table mapping
`#[sql_primary_key]`	field	Regular primary key
`#[sql_primary_key(identity)]`	field	IDENTITY column
`#[sql_column("ColumnName")]`	field	Override SQL column name
`#[sql_default]`	field	Server default (excluded from INSERT)
`#[sql_computed]`	field	Computed column (SELECT only)
`#[sql_ignore]`	field	Not mapped to SQL

CRUD Operations

// All operations are async with CancellationToken support

// Insert
let result = service.create(&entity, &ct).await?;

// Read (single)
let entity = service.get_by_id(primary_key_value, &ct).await?;

// Read (all)
let entities: Vec<_> = service.list(&ct).await?;

// Update
service.update(&entity, &ct).await?;

// Delete
service.delete_by_id(primary_key_value, &ct).await?;

// Batch operations
service.create_many(&entities, &ct).await?;
service.delete_many(&ids, &ct).await?;

Stored Procedures

use statiq::SprocParams;

let params = SprocParams::new()
    .add("@Name", "John")
    .add("@Active", true)
    .add_nullable("@OptionalField", None::<String>);

// Single result set
let results: Vec<User> = sproc
    .query("dbo.sp_GetUsers", params, &ct)
    .await?;

// Multiple result sets (2)
let (count, users) = sproc
    .query2::<Scalar<i64>, Vec<User>>(
        "dbo.sp_GetUsersWithCount",
        params,
        &ct,
    )
    .await?;

// Multiple result sets (3+)
let mut reader = sproc
    .query_multiple("dbo.sp_ComplexReport", params, &ct)
    .await?;

let first_set = reader.read_list::<User>()?;
let second_set = reader.read_list::<Product>()?;

Transactions

use statiq::Transaction;

let mut tx = service.begin_transaction(&ct).await?;

// All operations within transaction
tx.create(&user, &ct).await?;
tx.update(&product, &ct).await?;

// Auto-rollback on error, commit on success
tx.commit(&ct).await?;

Redis Caching

#[derive(SqlEntity, serde::Serialize, serde::Deserialize, Clone)]
#[sql_table("Dealers", schema = "dbo")]
#[sql_cache(ttl = 3600)]  // 1 hour
pub struct Dealer {
    #[sql_primary_key(identity)]
    pub id: i32,
    pub name: String,
}

// Cache layer automatically:
// - Caches on read
// - Invalidates on write/delete
// - Handles cache misses transparently

🔧 Connection Pooling

Statiq uses a lock-free connection pool with:

✅ Automatic connection validation
✅ sp_reset_connection on every checkout
✅ Idle timeout & max lifetime management
✅ Built-in metrics (pool usage, query times)

Configuration in config.json:

{
  "pool": {
    "min_size": 2,
    "max_size": 20,
    "checkout_timeout_ms": 5000,
    "idle_timeout_ms": 300000
  }
}

🌐 Framework Integration

Axum

use axum::{Json, Router, routing::get, extract::State};
use statiq::SqlServiceFactory;

#[derive(Clone)]
pub struct AppState {
    pub db: Arc<SqlService>,
}

async fn get_user(
    State(state): State<AppState>,
    axum::extract::Path(id): axum::extract::Path<i32>,
) -> Result<Json<User>, SqlError> {
    let ct = CancellationToken::new();
    let user = state.db.get_by_id(id, &ct).await?;
    Ok(Json(user))
}

#[tokio::main]
async fn main() -> Result<()> {
    let factory = SqlServiceFactory::new().await?;
    let db = Arc::new(factory.create_service());

    let app = Router::new()
        .route("/users/:id", get(get_user))
        .with_state(AppState { db });

    let listener = tokio::net::TcpListener::bind("0.0.0.0:3000").await?;
    axum::serve(listener, app).await?;
    Ok(())
}

Tauri

// Backend service
#[tauri::command]
async fn get_users(state: tauri::State<'_, AppState>) -> Result<Vec<User>, String> {
    let ct = CancellationToken::new();
    state.db.list(&ct)
        .await
        .map_err(|e| e.to_string())
}

// Automatically serializes/deserializes via serde

⚠️ Error Handling

use statiq::SqlError;

match service.get_by_id(1, &ct).await {
    Ok(user) => println!("Found: {}", user.name),
    Err(SqlError::NotFound) => println!("User not found"),
    Err(SqlError::DeadlockRetry) => println!("Retried after deadlock"),
    Err(e) => eprintln!("Database error: {}", e),
}

🧪 Testing

Use MockRepository for unit tests:

#[cfg(test)]
mod tests {
    use statiq::MockRepository;

    #[tokio::test]
    async fn test_create_user() {
        let mock = MockRepository::new();
        let user = User { /* ... */ };
        
        let result = mock.create(&user, &CancellationToken::new()).await;
        assert!(result.is_ok());
    }
}

📝 Features

Statiq is a single crate with optional integrations:

statiq = { version = "0.1", features = ["axum"] }   # Axum IntoResponse
statiq = { version = "0.1", features = ["tauri"] }  # Tauri serialization
statiq = { version = "0.1", features = ["testing"] }# MockRepository

📄 License

Licensed under MIT OR Apache-2.0. See LICENSE and LICENSE-APACHE.

🔗 Resources

Docs: docs.rs/statiq
GitHub: github.com/yasinakmaz/Statiq
Crates.io: crates.io/crates/statiq

Configuration
Entity Definition
Sproc Execution (SprocService)
Axum — From Zero to Advanced
Actix-Web — From Zero to Advanced
Tauri — From Zero to Advanced
Entity CRUD (SqlService)
Transactions
Caching
Entity Macro Reference
Testing with MockRepository

Configuration

config.json (root of your project):

{
  "mssql": {
    "connection_string": "Driver={ODBC Driver 18 for SQL Server};Server=localhost,1433;Database=MyDb;UID=sa;PWD=yourStrong(!)Password;Encrypt=yes;TrustServerCertificate=yes",
    "pool": {
      "min_size": 2,
      "max_size": 20,
      "checkout_timeout_ms": 5000,
      "idle_timeout_ms": 300000,
      "max_lifetime_ms": 1800000,
      "validation_interval_ms": 60000
    },
    "query": {
      "slow_query_threshold_ms": 500,
      "max_text_bytes": 65536
    }
  },
  "redis": {
    "url": "redis://127.0.0.1:6379",
    "default_ttl_seconds": 300,
    "count_ttl_seconds": 60
  },
  "logging": {
    "level": "info",
    "json": false
  }
}

Entity Definition

use sqlservice::SqlEntity;
use sqlservice_macros::SqlEntity;

#[derive(SqlEntity, serde::Serialize, serde::Deserialize, Clone)]
#[sql_table("Dealers", schema = "dbo")]
pub struct Dealer {
    #[sql_primary_key(identity)]
    pub id: i32,

    pub name: String,
    pub code: String,

    #[sql_column("TaxId")]          // override SQL column name
    pub tax_id: Option<String>,

    pub active: bool,

    #[sql_default]                  // server default — excluded from INSERT, included in UPDATE
    pub created_at: chrono::DateTime<chrono::Utc>,

    #[sql_computed]                 // DB computed — SELECT only, never written
    pub full_name: Option<String>,

    #[sql_ignore]                   // excluded from all SQL
    pub _cache_key: String,
}

Macro Attributes:

Attribute	Location	Meaning
`#[sql_table("Name", schema = "dbo")]`	struct	Table name + schema
`#[sql_primary_key]`	field	PK, not identity
`#[sql_primary_key(identity)]`	field	PK, IDENTITY column
`#[sql_column("ColName")]`	field	Override SQL column name
`#[sql_ignore]`	field	Excluded from all SQL
`#[sql_computed]`	field	SELECT only (DB computed column)
`#[sql_default]`	field	Excluded from INSERT, included in UPDATE

Sproc Execution (SprocService)

SprocService is the Rust equivalent of ISprocService — zero reflection, compile-time dispatch.

Core Pattern: `FromResultSet`

The FromResultSet trait maps one ODBC result set to a Rust type at compile time:

Rust Type	Meaning	Example
`Vec<T: SqlEntity>`	All rows	`Vec<VwDealers>`
`Single<T>`	First row, `Option<T>`	`Single<VwDealers>`
`Required<T>`	First row, error if empty	`Required<VwDealers>`
`Scalar<S>`	First column of first row	`Scalar<i64>`

SprocParams Builder

use sqlservice::{SprocParams, SprocService};

let params = SprocParams::new()
    .add("@Name",           "Acme Corp")        // &str, String, i32, bool, Uuid, ...
    .add("@Active",         true)
    .add("@Amount",         rust_decimal::Decimal::new(9999, 2))
    .add_nullable("@TaxId", tax_id.as_deref())  // Option<T> → NULL if None
    .add_nullable("@Notes", None::<&str>);       // explicit NULL

Parameter names may include or omit the leading @ — both .add("@Name", ...) and .add("Name", ...) work.

Query Methods

// ── Single result set ────────────────────────────────────────────────────────

// List (QueryListAsync equivalent)
let dealers: Vec<VwDealers> =
    sproc.query("Dealer.sp_DealerList", params, &ct).await?;

// Single nullable (QuerySingleAsync equivalent)
let Single(dealer) =
    sproc.query::<Single<VwDealers>>("Dealer.sp_GetById", params, &ct).await?;

// Required — errors if result set is empty (QuerySingleRequiredAsync equivalent)
let Required(dealer) =
    sproc.query::<Required<VwDealers>>("Dealer.sp_GetById", params, &ct).await?;

// Scalar (QueryScalarAsync equivalent)
let Scalar(count) =
    sproc.query::<Scalar<i64>>("dbo.sp_Count", SprocParams::new(), &ct).await?;

// ── Two result sets ──────────────────────────────────────────────────────────

// Scalar + list (paging pattern — QueryScalarAndListAsync equivalent)
let (Scalar(total), dealers) =
    sproc.query2::<Scalar<i64>, Vec<VwDealers>>(
        "Dealer.sp_DealerList", params, &ct,
    ).await?;

// Two lists
let (Single(dealer), Vec<VwPoolAllocations>) =
    sproc.query2::<Single<VwDealers>, Vec<VwPoolAllocations>>(
        "Dealer.sp_GetWithAllocations", params, &ct,
    ).await?;

// ── Three result sets ────────────────────────────────────────────────────────

let (Single(dealer), allocs, sub_dealers) =
    sproc.query3::<Single<VwDealers>, Vec<VwPoolAllocations>, Vec<VwDealers>>(
        "Dealer.sp_DealerGetById", params, &ct,
    ).await?;

// ── Four result sets ─────────────────────────────────────────────────────────

let (Scalar(total), dealers, allocs, users) =
    sproc.query4::<Scalar<i64>, Vec<VwDealers>, Vec<VwAllocs>, Vec<VwUsers>>(
        "Dealer.sp_Dashboard", params, &ct,
    ).await?;

// ── Manual reader (5+ result sets) ───────────────────────────────────────────

let mut reader = sproc.query_multiple("Dealer.sp_FullReport", params, &ct).await?;
let result_row  = reader.read_single::<SprocResultRow>()?;   // row 1: success/error
let dealer      = reader.read_single::<VwDealers>()?;         // row 2
let allocs      = reader.read_list::<VwPoolAllocations>()?;   // row 3
let licenses    = reader.read_list::<VwUnassignedLicenses>()?;// row 4
let sub_dealers = reader.read_list::<VwDealers>()?;           // row 5
let count       = reader.read_scalar::<i64>()?;               // row 6 (scalar)

// ── Non-query ────────────────────────────────────────────────────────────────

let affected = sproc.execute("Dealer.sp_DealerDelete", params, &ct).await?;

SprocResult Pattern

// Define a struct for the common status row returned by your sprocs
#[derive(SqlEntity, serde::Serialize, serde::Deserialize, Clone)]
#[sql_table("SprocResultRow", schema = "dbo")]
pub struct SprocResultRow {
    #[sql_primary_key]
    pub success: i32,
    pub error_code: Option<String>,
    pub error_message: Option<String>,
}

// Use SprocResult<T> as the return envelope
use sqlservice::SprocResult;

pub async fn create_dealer(
    sproc: &SprocService,
    req: DealerCreate,
    ct: CancellationToken,
) -> Result<SprocResult<VwDealers>, SqlError> {
    let mut reader = sproc.query_multiple(
        "Dealer.sp_DealerCreate",
        SprocParams::new()
            .add("@Name",           req.name.as_str())
            .add("@Code",           req.code.as_str())
            .add_nullable("@TaxId", req.tax_id.as_deref())
            .add("@CreatedBy",      req.created_by),
        &ct,
    ).await?;

    let status = reader.read_single::<SprocResultRow>()?;
    match status {
        Some(r) if r.success == 1 => {
            let dealer = reader.read_single::<VwDealers>()?;
            Ok(SprocResult::ok(dealer))
        }
        r => Ok(SprocResult::fail(
            r.as_ref().and_then(|x| x.error_code.clone()),
            r.as_ref().and_then(|x| x.error_message.clone()),
        )),
    }
}

Axum — From Zero to Advanced

1. Project Setup

# Cargo.toml
[package]
name = "my-axum-api"
version = "0.1.0"
edition = "2021"

[dependencies]
sqlservice  = { path = "../sqlservice", features = ["axum"] }
axum        = "0.7"
tokio       = { version = "1", features = ["full"] }
tokio-util  = { version = "0.7", features = ["full"] }
serde       = { version = "1", features = ["derive"] }
serde_json  = "1"
tower-http  = { version = "0.5", features = ["trace"] }
tracing     = "0.1"
tracing-subscriber = { version = "0.3", features = ["env-filter"] }

2. App State

// src/state.rs
use std::sync::Arc;
use sqlservice::SprocService;

#[derive(Clone)]
pub struct AppState {
    pub sproc: Arc<SprocService>,
}

3. main.rs

// src/main.rs
use std::sync::Arc;
use axum::{Router, routing::get, routing::post};
use sqlservice::SqlServiceFactory;
use tokio_util::sync::CancellationToken;
use tracing_subscriber::{layer::SubscriberExt, util::SubscriberInitExt};

mod state;
mod handlers;
mod error;

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    tracing_subscriber::registry()
        .with(tracing_subscriber::EnvFilter::new("info"))
        .with(tracing_subscriber::fmt::layer())
        .init();

    let shutdown = CancellationToken::new();

    let sproc = SqlServiceFactory::new()
        .config_path("config.json")
        .shutdown(shutdown.clone())
        .build_sproc()
        .await?;

    let state = state::AppState {
        sproc: Arc::new(sproc),
    };

    let app = Router::new()
        .route("/dealers",         get(handlers::dealer::list))
        .route("/dealers",         post(handlers::dealer::create))
        .route("/dealers/:id",     get(handlers::dealer::get_by_id))
        .route("/dealers/:id",     axum::routing::put(handlers::dealer::update))
        .route("/dealers/:id",     axum::routing::delete(handlers::dealer::delete))
        .route("/metrics/pool",    get(handlers::metrics::pool_metrics))
        .with_state(state);

    let listener = tokio::net::TcpListener::bind("0.0.0.0:8080").await?;
    tracing::info!("Listening on :8080");

    axum::serve(listener, app)
        .with_graceful_shutdown(async move { shutdown.cancelled().await })
        .await?;

    Ok(())
}

4. Error Handling

// src/error.rs
use axum::{response::{IntoResponse, Response}, http::StatusCode, Json};
use serde_json::json;
use sqlservice::SqlError;

pub struct ApiError(pub SqlError);

impl IntoResponse for ApiError {
    fn into_response(self) -> Response {
        let status = match &self.0 {
            SqlError::NotFound { .. }        => StatusCode::NOT_FOUND,
            SqlError::Cancelled              => StatusCode::SERVICE_UNAVAILABLE,
            SqlError::PoolExhausted { .. }   => StatusCode::SERVICE_UNAVAILABLE,
            SqlError::QueryTimeout { .. }    => StatusCode::GATEWAY_TIMEOUT,
            _                               => StatusCode::INTERNAL_SERVER_ERROR,
        };
        let body = Json(json!({
            "error": self.0.error_code(),
            "message": self.0.safe_message(),
        }));
        (status, body).into_response()
    }
}

impl From<SqlError> for ApiError {
    fn from(e: SqlError) -> Self { ApiError(e) }
}

pub type ApiResult<T> = Result<T, ApiError>;

5. Handlers — Basic

// src/handlers/dealer.rs
use axum::{extract::{State, Path}, Json};
use serde::{Deserialize, Serialize};
use tokio_util::sync::CancellationToken;
use sqlservice::{SprocParams, Single, Scalar};

use crate::{state::AppState, error::ApiResult};
use crate::models::{VwDealers, SprocResultRow};

#[derive(Deserialize)]
pub struct ListQuery {
    page: Option<i32>,
    page_size: Option<i32>,
    search: Option<String>,
}

#[derive(Serialize)]
pub struct PagedResponse<T> {
    pub items: Vec<T>,
    pub total_count: Option<i64>,
}

pub async fn list(
    State(state): State<AppState>,
    axum::extract::Query(q): axum::extract::Query<ListQuery>,
) -> ApiResult<Json<PagedResponse<VwDealers>>> {
    let ct = CancellationToken::new();

    let (Scalar(total), dealers) = state.sproc
        .query2::<Scalar<i64>, Vec<VwDealers>>(
            "Dealer.sp_DealerList",
            SprocParams::new()
                .add("@PageNumber", q.page.unwrap_or(1))
                .add("@PageSize",   q.page_size.unwrap_or(20))
                .add_nullable("@Search", q.search.as_deref()),
            &ct,
        )
        .await?;

    Ok(Json(PagedResponse { items: dealers, total_count: total }))
}

pub async fn get_by_id(
    State(state): State<AppState>,
    Path(id): Path<i32>,
) -> ApiResult<Json<VwDealers>> {
    let ct = CancellationToken::new();

    let Single(dealer) = state.sproc
        .query::<Single<VwDealers>>(
            "Dealer.sp_DealerGetById",
            SprocParams::new().add("@Id", id),
            &ct,
        )
        .await?;

    match dealer {
        Some(d) => Ok(Json(d)),
        None => Err(sqlservice::SqlError::NotFound {
            table: "Dealer",
            pk: id.to_string(),
        }.into()),
    }
}

#[derive(Deserialize)]
pub struct CreateDealerRequest {
    pub name: String,
    pub code: String,
    pub tax_id: Option<String>,
    pub created_by: i32,
}

pub async fn create(
    State(state): State<AppState>,
    Json(req): Json<CreateDealerRequest>,
) -> ApiResult<Json<VwDealers>> {
    let ct = CancellationToken::new();

    let mut reader = state.sproc
        .query_multiple(
            "Dealer.sp_DealerCreate",
            SprocParams::new()
                .add("@Name",           req.name.as_str())
                .add("@Code",           req.code.as_str())
                .add_nullable("@TaxId", req.tax_id.as_deref())
                .add("@CreatedBy",      req.created_by),
            &ct,
        )
        .await?;

    let status = reader.read_single::<SprocResultRow>()?;
    if status.as_ref().map_or(true, |r| r.success != 1) {
        let msg = status
            .and_then(|r| r.error_message)
            .unwrap_or_else(|| "Create failed".to_string());
        return Err(sqlservice::SqlError::config(msg).into());
    }

    let dealer = reader
        .read_required::<VwDealers>()
        .map_err(ApiError::from)?;

    Ok(Json(dealer))
}

pub async fn update(
    State(state): State<AppState>,
    Path(id): Path<i32>,
    Json(req): Json<UpdateDealerRequest>,
) -> ApiResult<Json<VwDealers>> {
    let ct = CancellationToken::new();
    // ... similar to create
    todo!()
}

pub async fn delete(
    State(state): State<AppState>,
    Path(id): Path<i32>,
) -> ApiResult<axum::http::StatusCode> {
    let ct = CancellationToken::new();

    state.sproc
        .execute(
            "Dealer.sp_DealerDelete",
            SprocParams::new()
                .add("@Id",        id)
                .add("@DeletedBy", 1i32),
            &ct,
        )
        .await?;

    Ok(axum::http::StatusCode::NO_CONTENT)
}

6. Pool Metrics Endpoint

// src/handlers/metrics.rs
use axum::{extract::State, Json};
use serde::Serialize;
use crate::state::AppState;

#[derive(Serialize)]
pub struct PoolMetrics {
    pub idle: u64,
    pub active: u64,
    pub total_checkouts: u64,
    pub total_timeouts: u64,
    pub total_deadlocks: u64,
}

pub async fn pool_metrics(State(state): State<AppState>) -> Json<PoolMetrics> {
    let m = state.sproc.pool_metrics();
    Json(PoolMetrics {
        idle: m.idle,
        active: m.active,
        total_checkouts: m.total_checkouts,
        total_timeouts: m.total_timeouts,
        total_deadlocks: m.total_deadlocks,
    })
}

7. Advanced — Entity CRUD + Cache via SqlService

// For table-entity CRUD with Redis cache, use SqlService instead
use sqlservice::{SqlServiceFactory, SqlRepository};

let dealer_svc = SqlServiceFactory::new()
    .config_path("config.json")
    .shutdown(shutdown.clone())
    .build_with_cache::<VwDealers>()   // SqlService<VwDealers, RedisCache>
    .await?;

// Cached by primary key
let dealer = dealer_svc.get_by_id(42, &ct).await?;

// Paged
let page = dealer_svc.get_paged(1, 20, &ct).await?;

// Filtered
let active = dealer_svc
    .get_where("active = 1", &[], &ct)
    .await?;

// Write (auto-invalidates cache)
let id = dealer_svc.insert(&new_dealer, &ct).await?;
dealer_svc.update(&dealer, &ct).await?;
dealer_svc.delete(42, &ct).await?;

8. Advanced — Cancellation & Timeouts

use tokio_util::sync::CancellationToken;
use tokio::time::Duration;

pub async fn list_with_timeout(
    State(state): State<AppState>,
) -> ApiResult<Json<Vec<VwDealers>>> {
    // Request-scoped token with timeout
    let ct = CancellationToken::new();
    let _guard = ct.clone().drop_guard();

    tokio::spawn({
        let ct = ct.clone();
        async move {
            tokio::time::sleep(Duration::from_secs(10)).await;
            ct.cancel();
        }
    });

    let dealers: Vec<VwDealers> = state.sproc
        .query("Dealer.sp_DealerList", SprocParams::new(), &ct)
        .await?;

    Ok(Json(dealers))
}

Actix-Web — From Zero to Advanced

1. Project Setup

# Cargo.toml
[dependencies]
sqlservice  = { path = "../sqlservice" }
actix-web   = "4"
tokio       = { version = "1", features = ["full"] }
tokio-util  = { version = "0.7", features = ["full"] }
serde       = { version = "1", features = ["derive"] }
serde_json  = "1"

2. App State + main.rs

// src/main.rs
use actix_web::{web, App, HttpServer, middleware};
use sqlservice::SqlServiceFactory;
use std::sync::Arc;
use tokio_util::sync::CancellationToken;

mod handlers;
mod error;

#[actix_web::main]
async fn main() -> std::io::Result<()> {
    let shutdown = CancellationToken::new();

    let sproc = SqlServiceFactory::new()
        .config_path("config.json")
        .with_logging(true)      // opt-in: init tracing subscriber
        .shutdown(shutdown.clone())
        .build_sproc()
        .await
        .expect("Failed to build SprocService");

    let sproc = Arc::new(sproc);

    HttpServer::new(move || {
        App::new()
            .app_data(web::Data::new(sproc.clone()))
            .service(
                web::scope("/api")
                    .route("/dealers",      web::get().to(handlers::dealer::list))
                    .route("/dealers",      web::post().to(handlers::dealer::create))
                    .route("/dealers/{id}", web::get().to(handlers::dealer::get_by_id))
                    .route("/dealers/{id}", web::delete().to(handlers::dealer::delete))
            )
    })
    .bind("0.0.0.0:8080")?
    .run()
    .await
}

3. Error Handling

// src/error.rs
use actix_web::{HttpResponse, ResponseError};
use serde_json::json;
use sqlservice::SqlError;

#[derive(Debug)]
pub struct ApiError(pub SqlError);

impl std::fmt::Display for ApiError {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        write!(f, "{}", self.0)
    }
}

impl ResponseError for ApiError {
    fn error_response(&self) -> HttpResponse {
        let status = match &self.0 {
            SqlError::NotFound { .. }      => actix_web::http::StatusCode::NOT_FOUND,
            SqlError::PoolExhausted { .. } => actix_web::http::StatusCode::SERVICE_UNAVAILABLE,
            SqlError::Cancelled            => actix_web::http::StatusCode::SERVICE_UNAVAILABLE,
            _                             => actix_web::http::StatusCode::INTERNAL_SERVER_ERROR,
        };
        HttpResponse::build(status).json(json!({
            "error":   self.0.error_code(),
            "message": self.0.safe_message(),
        }))
    }
}

impl From<SqlError> for ApiError {
    fn from(e: SqlError) -> Self { ApiError(e) }
}

pub type ApiResult<T> = Result<T, ApiError>;

4. Handlers

// src/handlers/dealer.rs
use actix_web::{web, HttpResponse};
use serde::{Deserialize, Serialize};
use tokio_util::sync::CancellationToken;
use sqlservice::{SprocService, SprocParams, Single, Scalar};
use std::sync::Arc;

use crate::error::ApiResult;
use crate::models::VwDealers;

#[derive(Deserialize)]
pub struct ListQuery {
    page:      Option<i32>,
    page_size: Option<i32>,
    search:    Option<String>,
}

#[derive(Serialize)]
pub struct PagedResponse<T> {
    items:       Vec<T>,
    total_count: Option<i64>,
}

pub async fn list(
    sproc: web::Data<Arc<SprocService>>,
    query: web::Query<ListQuery>,
) -> ApiResult<HttpResponse> {
    let ct = CancellationToken::new();

    let (Scalar(total), dealers) = sproc
        .query2::<Scalar<i64>, Vec<VwDealers>>(
            "Dealer.sp_DealerList",
            SprocParams::new()
                .add("@PageNumber", query.page.unwrap_or(1))
                .add("@PageSize",   query.page_size.unwrap_or(20))
                .add_nullable("@Search", query.search.as_deref()),
            &ct,
        )
        .await?;

    Ok(HttpResponse::Ok().json(PagedResponse {
        items: dealers,
        total_count: total,
    }))
}

pub async fn get_by_id(
    sproc: web::Data<Arc<SprocService>>,
    path: web::Path<i32>,
) -> ApiResult<HttpResponse> {
    let id = path.into_inner();
    let ct = CancellationToken::new();

    let Single(dealer) = sproc
        .query::<Single<VwDealers>>(
            "Dealer.sp_DealerGetById",
            SprocParams::new().add("@Id", id),
            &ct,
        )
        .await?;

    match dealer {
        Some(d) => Ok(HttpResponse::Ok().json(d)),
        None    => Ok(HttpResponse::NotFound().finish()),
    }
}

#[derive(Deserialize)]
pub struct CreateRequest {
    pub name:       String,
    pub code:       String,
    pub tax_id:     Option<String>,
    pub created_by: i32,
}

pub async fn create(
    sproc: web::Data<Arc<SprocService>>,
    body: web::Json<CreateRequest>,
) -> ApiResult<HttpResponse> {
    let ct = CancellationToken::new();

    let mut reader = sproc
        .query_multiple(
            "Dealer.sp_DealerCreate",
            SprocParams::new()
                .add("@Name",           body.name.as_str())
                .add("@Code",           body.code.as_str())
                .add_nullable("@TaxId", body.tax_id.as_deref())
                .add("@CreatedBy",      body.created_by),
            &ct,
        )
        .await?;

    let status = reader.read_single::<SprocResultRow>()?;
    if status.as_ref().map_or(true, |r| r.success != 1) {
        return Ok(HttpResponse::BadRequest().json(serde_json::json!({
            "error": status.and_then(|r| r.error_code),
        })));
    }

    let dealer = reader.read_required::<VwDealers>()?;
    Ok(HttpResponse::Created().json(dealer))
}

pub async fn delete(
    sproc: web::Data<Arc<SprocService>>,
    path: web::Path<i32>,
) -> ApiResult<HttpResponse> {
    let id = path.into_inner();
    let ct = CancellationToken::new();

    sproc.execute(
        "Dealer.sp_DealerDelete",
        SprocParams::new()
            .add("@Id", id)
            .add("@DeletedBy", 1i32),
        &ct,
    ).await?;

    Ok(HttpResponse::NoContent().finish())
}

5. Advanced — Background Validation + Graceful Shutdown

// In main.rs, pass a shared shutdown token to both the pool and the server
let shutdown = CancellationToken::new();

let sproc = SqlServiceFactory::new()
    .config_path("config.json")
    .shutdown(shutdown.clone())   // pool validator stops when token is cancelled
    .build_sproc()
    .await?;

// Catch Ctrl+C
let shutdown_for_signal = shutdown.clone();
tokio::spawn(async move {
    tokio::signal::ctrl_c().await.ok();
    shutdown_for_signal.cancel();
});

// ... start HttpServer ...

Tauri — From Zero to Advanced

1. Project Setup

# src-tauri/Cargo.toml
[dependencies]
sqlservice = { path = "../../sqlservice", features = ["tauri"] }
tauri      = { version = "2", features = [] }
tokio      = { version = "1", features = ["full"] }
tokio-util = { version = "0.7", features = ["full"] }
serde      = { version = "1", features = ["derive"] }
serde_json = "1"

2. State Setup in main.rs / lib.rs

// src-tauri/src/lib.rs
use sqlservice::SqlServiceFactory;
use std::sync::Arc;
use tokio_util::sync::CancellationToken;

pub struct DbState {
    pub sproc: Arc<sqlservice::SprocService>,
}

#[cfg_attr(mobile, tauri::mobile_entry_point)]
pub fn run() {
    let rt = tokio::runtime::Runtime::new().expect("Tokio runtime failed");

    let shutdown = CancellationToken::new();

    let sproc = rt.block_on(async {
        SqlServiceFactory::new()
            .config_path("config.json")
            .with_logging(true)
            .shutdown(shutdown.clone())
            .build_sproc()
            .await
            .expect("Failed to connect to database")
    });

    tauri::Builder::default()
        .manage(DbState { sproc: Arc::new(sproc) })
        .invoke_handler(tauri::generate_handler![
            cmd_dealer_list,
            cmd_dealer_get_by_id,
            cmd_dealer_create,
            cmd_dealer_delete,
        ])
        .run(tauri::generate_context!())
        .expect("error while running tauri application");
}

3. IPC Commands

// src-tauri/src/commands/dealer.rs
use tauri::State;
use serde::{Deserialize, Serialize};
use tokio_util::sync::CancellationToken;
use sqlservice::{SprocParams, Single, Scalar, SqlError};

use crate::lib::DbState;
use crate::models::{VwDealers, SprocResultRow};

// ── Error type for Tauri IPC ──────────────────────────────────────────────────

#[derive(Serialize)]
pub struct IpcError {
    pub code:    String,
    pub message: String,
}

impl From<SqlError> for IpcError {
    fn from(e: SqlError) -> Self {
        IpcError {
            code:    e.error_code().to_string(),
            message: e.safe_message(),
        }
    }
}

// ── Commands ──────────────────────────────────────────────────────────────────

#[derive(Serialize)]
pub struct PagedResult<T: Serialize> {
    pub items:       Vec<T>,
    pub total_count: Option<i64>,
}

#[tauri::command]
pub async fn cmd_dealer_list(
    state: State<'_, DbState>,
    page:      Option<i32>,
    page_size: Option<i32>,
    search:    Option<String>,
) -> Result<PagedResult<VwDealers>, IpcError> {
    let ct = CancellationToken::new();

    let (Scalar(total), dealers) = state.sproc
        .query2::<Scalar<i64>, Vec<VwDealers>>(
            "Dealer.sp_DealerList",
            SprocParams::new()
                .add("@PageNumber", page.unwrap_or(1))
                .add("@PageSize",   page_size.unwrap_or(20))
                .add_nullable("@Search", search.as_deref()),
            &ct,
        )
        .await
        .map_err(IpcError::from)?;

    Ok(PagedResult { items: dealers, total_count: total })
}

#[tauri::command]
pub async fn cmd_dealer_get_by_id(
    state: State<'_, DbState>,
    id: i32,
) -> Result<Option<VwDealers>, IpcError> {
    let ct = CancellationToken::new();

    let Single(dealer) = state.sproc
        .query::<Single<VwDealers>>(
            "Dealer.sp_DealerGetById",
            SprocParams::new().add("@Id", id),
            &ct,
        )
        .await
        .map_err(IpcError::from)?;

    Ok(dealer)
}

#[derive(Deserialize)]
pub struct CreateDealerArgs {
    pub name:       String,
    pub code:       String,
    pub tax_id:     Option<String>,
    pub created_by: i32,
}

#[derive(Serialize)]
pub struct SprocResultIpc<T: Serialize> {
    pub success:       bool,
    pub error_code:    Option<String>,
    pub error_message: Option<String>,
    pub data:          Option<T>,
}

#[tauri::command]
pub async fn cmd_dealer_create(
    state: State<'_, DbState>,
    args: CreateDealerArgs,
) -> Result<SprocResultIpc<VwDealers>, IpcError> {
    let ct = CancellationToken::new();

    let mut reader = state.sproc
        .query_multiple(
            "Dealer.sp_DealerCreate",
            SprocParams::new()
                .add("@Name",           args.name.as_str())
                .add("@Code",           args.code.as_str())
                .add_nullable("@TaxId", args.tax_id.as_deref())
                .add("@CreatedBy",      args.created_by),
            &ct,
        )
        .await
        .map_err(IpcError::from)?;

    let status = reader.read_single::<SprocResultRow>().map_err(IpcError::from)?;

    if status.as_ref().map_or(true, |r| r.success != 1) {
        return Ok(SprocResultIpc {
            success:       false,
            error_code:    status.as_ref().and_then(|r| r.error_code.clone()),
            error_message: status.as_ref().and_then(|r| r.error_message.clone()),
            data:          None,
        });
    }

    let dealer = reader.read_single::<VwDealers>().map_err(IpcError::from)?;

    Ok(SprocResultIpc {
        success:       true,
        error_code:    None,
        error_message: None,
        data:          dealer,
    })
}

#[tauri::command]
pub async fn cmd_dealer_delete(
    state: State<'_, DbState>,
    id: i32,
    deleted_by: i32,
) -> Result<(), IpcError> {
    let ct = CancellationToken::new();

    state.sproc
        .execute(
            "Dealer.sp_DealerDelete",
            SprocParams::new()
                .add("@Id",        id)
                .add("@DeletedBy", deleted_by),
            &ct,
        )
        .await
        .map_err(IpcError::from)?;

    Ok(())
}

4. Advanced — Entity CRUD in Tauri

// For table CRUD, SqlService is available alongside SprocService
use sqlservice::{SqlServiceFactory, SqlRepository};

// In your AppState alongside sproc:
pub struct DbState {
    pub sproc:       Arc<sqlservice::SprocService>,
    pub dealer_svc:  Arc<sqlservice::SqlService<VwDealers>>,
}

// Build both in run()
let dealer_svc = SqlServiceFactory::new()
    .config_path("config.json")
    .shutdown(shutdown.clone())
    .build::<VwDealers>()
    .await?;

// In command:
#[tauri::command]
pub async fn cmd_get_dealer(
    state: State<'_, DbState>,
    id: i32,
) -> Result<Option<VwDealers>, IpcError> {
    let ct = CancellationToken::new();
    state.dealer_svc
        .get_by_id(id, &ct)
        .await
        .map_err(IpcError::from)
}

5. Advanced — Async Tauri Command with Timeout

#[tauri::command]
pub async fn cmd_complex_report(
    state: State<'_, DbState>,
    dealer_id: i32,
) -> Result<DealerReport, IpcError> {
    // 15-second timeout for heavy reports
    let ct = CancellationToken::new();
    let ct_clone = ct.clone();
    tokio::spawn(async move {
        tokio::time::sleep(std::time::Duration::from_secs(15)).await;
        ct_clone.cancel();
    });

    let (Single(dealer), allocs, sub_dealers) = state.sproc
        .query3::<Single<VwDealers>, Vec<VwPoolAllocations>, Vec<VwDealers>>(
            "Dealer.sp_DealerGetById",
            SprocParams::new().add("@Id", dealer_id),
            &ct,
        )
        .await
        .map_err(IpcError::from)?;

    let dealer = dealer.ok_or_else(|| IpcError {
        code:    "not_found".to_string(),
        message: format!("Dealer {dealer_id} not found"),
    })?;

    Ok(DealerReport { dealer, allocs, sub_dealers })
}

Entity CRUD (SqlService)

SqlService<T, C> implements SqlRepository<T> — full compile-time CRUD.

use sqlservice::{SqlServiceFactory, SqlRepository, params};

let svc = SqlServiceFactory::new()
    .config_path("config.json")
    .build::<Dealer>()
    .await?;

let ct = CancellationToken::new();

// ── Read ─────────────────────────────────────────────────────────────────────
let dealer  = svc.get_by_id(1, &ct).await?;           // Option<Dealer>
let all     = svc.get_all(&ct).await?;                 // Vec<Dealer>
let count   = svc.count(&ct).await?;                   // i64
let exists  = svc.exists(1, &ct).await?;               // bool
let page    = svc.get_paged(1, 20, &ct).await?;        // Vec<Dealer>
let active  = svc.get_where("active = 1", &[], &ct).await?;

// With params!{} macro:
let filtered = svc
    .get_where("name LIKE @name", params!{ name: "%Acme%" }, &ct)
    .await?;

// ── Write ─────────────────────────────────────────────────────────────────────
let id = svc.insert(&new_dealer, &ct).await?;    // i64 (identity or pk)
svc.update(&dealer, &ct).await?;
svc.delete(id, &ct).await?;
svc.upsert(&dealer, &ct).await?;                 // MERGE

// ── Batch ────────────────────────────────────────────────────────────────────
let ids = svc.batch_insert(&dealers, &ct).await?;  // wraps in transaction
svc.batch_update(&dealers, &ct).await?;
svc.batch_delete(&[pk1, pk2, pk3], &ct).await?;

// ── Raw ──────────────────────────────────────────────────────────────────────
let rows    = svc.query_raw("SELECT TOP 5 * FROM dbo.Dealers", &[], &ct).await?;
let affected = svc.execute_raw("UPDATE dbo.Dealers SET active = 0 WHERE id = @id",
    params!{ id: 99i32 }, &ct).await?;
let total: i64 = svc.scalar("SELECT COUNT_BIG(*) FROM dbo.Dealers", &[], &ct).await?;

Transactions

// Transaction with manual begin/commit
let mut tx = svc.begin_transaction(&ct).await?;

let id1 = tx.insert::<Dealer>(&dealer_a, &ct).await?;
let id2 = tx.insert::<Dealer>(&dealer_b, &ct).await?;
tx.update::<Dealer>(&dealer_c, &ct).await?;
tx.delete::<Dealer>(old_id, &ct).await?;
tx.execute_raw("EXEC dbo.sp_DoSomething", &ct).await?;

tx.commit().await?;
// If you forget commit(), drop() auto-rolls back.

// ── Deadlock retry ───────────────────────────────────────────────────────────
use sqlservice::transaction::with_retry;

with_retry(3, || async {
    let mut tx = svc.begin_transaction(&ct).await?;
    tx.update::<Dealer>(&dealer, &ct).await?;
    tx.commit().await
}).await?;

Caching

// Build with Redis cache
let svc = SqlServiceFactory::new()
    .config_path("config.json")
    .build_with_cache::<Dealer>()   // SqlService<Dealer, RedisCache>
    .await?;

// get_by_id, get_all, count → cached automatically
// insert, update, delete, upsert → auto-invalidate cache

Cache keys follow the pattern {CACHE_PREFIX}::GetById::{pk} (generated by the macro from table + schema).

Entity Macro Reference

#[derive(SqlEntity, Serialize, Deserialize, Clone)]
#[sql_table("TableName", schema = "dbo")]
pub struct MyEntity {
    // ── Primary key ─────────────────────────────────────────────────────────
    #[sql_primary_key(identity)]   // IDENTITY — excluded from INSERT, returned via OUTPUT INSERTED
    pub id: i64,

    // ── Or non-identity PK ───────────────────────────────────────────────────
    // #[sql_primary_key]
    // pub id: uuid::Uuid,

    // ── Regular column ───────────────────────────────────────────────────────
    pub name: String,

    // ── Override SQL column name ─────────────────────────────────────────────
    #[sql_column("TaxIdentifier")]
    pub tax_id: Option<String>,

    // ── Server default (GETDATE(), NEWID(), …) ───────────────────────────────
    // Excluded from INSERT (server fills it). Included in UPDATE.
    #[sql_default]
    pub created_at: chrono::DateTime<chrono::Utc>,

    // ── DB computed column ───────────────────────────────────────────────────
    // SELECT only — never in INSERT / UPDATE / MERGE / to_params
    #[sql_computed]
    pub display_name: Option<String>,

    // ── Ignored field ────────────────────────────────────────────────────────
    // Completely excluded from all SQL
    #[sql_ignore]
    pub runtime_flag: bool,
}

Generated compile-time constants on the trait:

Constant	Value
`TABLE_NAME`	`"TableName"`
`SCHEMA`	`"dbo"`
`SELECT_SQL`	`SELECT [id],[name],[TaxIdentifier],… FROM [dbo].[TableName]`
`INSERT_SQL`	`INSERT INTO … OUTPUT INSERTED.id VALUES (…)`
`UPDATE_SQL`	`UPDATE … SET name=@name,… WHERE id=@id`
`DELETE_SQL`	`DELETE FROM … WHERE id=@id`
`UPSERT_SQL`	`MERGE … WHEN MATCHED UPDATE … WHEN NOT MATCHED INSERT …`
`COUNT_SQL`	`SELECT COUNT_BIG(*) FROM …`
`PK_IS_IDENTITY`	`true` / `false`

Testing with MockRepository

// Cargo.toml
[dev-dependencies]
sqlservice = { path = "../sqlservice", features = ["testing"] }

// In tests:
use sqlservice::testing::MockRepository;

let mock = MockRepository::<Dealer>::new()
    .with_data(vec![
        Dealer { id: 1, name: "Acme".to_string(), active: true, .. },
        Dealer { id: 2, name: "Beta".to_string(), active: false, .. },
    ]);

let ct = CancellationToken::new();

// All SqlRepository methods work in-memory
let dealer = mock.get_by_id(1, &ct).await?.unwrap();
assert_eq!(dealer.name, "Acme");

let count = mock.count(&ct).await?;
assert_eq!(count, 2);

mock.insert(&new_dealer, &ct).await?;
assert_eq!(mock.insert_call_count(), 1);

mock.update(&dealer, &ct).await?;
assert_eq!(mock.update_call_count(), 1);

mock.delete(1, &ct).await?;
assert_eq!(mock.delete_call_count(), 1);

// Inspect state
let all = mock.all_items();
assert_eq!(all.len(), 1);  // id=2 remains

// Reseed
mock.clear();
mock.seed(Dealer { id: 99, .. });
assert_eq!(mock.len(), 1);

Supported SQL Server Types

SQL Server Type	Rust Type	`ParamValue` Variant
`bit`	`bool`	`Bool`
`tinyint`	`u8`	`U8`
`smallint`	`i16`	`I16`
`int`	`i32`	`I32`
`bigint`	`i64`	`I64`
`real`	`f32`	`F32`
`float`	`f64`	`F64`
`decimal`, `numeric`, `money`	`rust_decimal::Decimal`	`Decimal`
`char`, `varchar`, `nvarchar`, `text`	`String`	`Str`
`binary`, `varbinary`, `image`	`Vec<u8>`	`Bytes`
`date`	`chrono::NaiveDate`	`NaiveDate`
`time`	`chrono::NaiveTime`	`NaiveTime`
`datetime`, `datetime2`, `smalldatetime`	`chrono::DateTime<Utc>`	`DateTime`
`datetimeoffset`	`chrono::DateTime<FixedOffset>`	`DateTimeOffset`
`uniqueidentifier`	`uuid::Uuid`	`Guid`
`NULL`	`Option<T>`	`Null`

statiq 0.2.3

Statiq - MSSQL Service Library

🚀 Installation

📚 Quick Start

1. Configuration (config.json)

2. Define Your Entity

3. Use in Your Handler

📖 Core Features

Entity Macro Attributes

CRUD Operations

Stored Procedures

Transactions

Redis Caching

🔧 Connection Pooling

🌐 Framework Integration

Axum

Tauri

⚠️ Error Handling

🧪 Testing

📝 Features

📄 License

🔗 Resources

Table of Contents

Configuration

Entity Definition

Sproc Execution (SprocService)

Core Pattern: FromResultSet

SprocParams Builder

Query Methods

SprocResult Pattern

Axum — From Zero to Advanced

1. Project Setup

2. App State

3. main.rs

4. Error Handling

5. Handlers — Basic

6. Pool Metrics Endpoint

7. Advanced — Entity CRUD + Cache via SqlService

8. Advanced — Cancellation & Timeouts

Actix-Web — From Zero to Advanced

1. Project Setup

2. App State + main.rs

3. Error Handling

4. Handlers

5. Advanced — Background Validation + Graceful Shutdown

Tauri — From Zero to Advanced

1. Project Setup

2. State Setup in main.rs / lib.rs

3. IPC Commands

4. Advanced — Entity CRUD in Tauri

5. Advanced — Async Tauri Command with Timeout

Entity CRUD (SqlService)

Transactions

Caching

Entity Macro Reference

Testing with MockRepository

Supported SQL Server Types

Core Pattern: `FromResultSet`