statiq 0.1.0

Zero-overhead, compile-time MSSQL service for Rust — stored procedures, async CRUD, connection pooling, static dispatch
Documentation

SqlService

Zero-overhead, compile-time MSSQL service library for Rust.

  • Compile-time SQL — all entity SQL constants generated by proc-macro (&'static str)
  • Static dispatch — FromResultSet, SqlEntity, CacheLayer all monomorphised at compile time, no vtables
  • Async-first — tokio + CancellationToken throughout
  • Stored procedure support — SprocService with typed tuple returns up to 4 result sets, or unlimited via MultiReader
  • Connection pooling — lock-free idle queue, sp_reset_connection, validation, metrics
  • Transaction + deadlock retry — RAII auto-rollback, exponential backoff
  • Redis cache — opt-in per entity, invalidation on write
  • Framework integrations — axum feature (IntoResponse), tauri feature (IPC Serialize)

Table of Contents

  1. Configuration
  2. Entity Definition
  3. Sproc Execution (SprocService)
  4. Axum — From Zero to Advanced
  5. Actix-Web — From Zero to Advanced
  6. Tauri — From Zero to Advanced
  7. Entity CRUD (SqlService)
  8. Transactions
  9. Caching
  10. Entity Macro Reference
  11. 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