# 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](#configuration)
2. [Entity Definition](#entity-definition)
3. [Sproc Execution (SprocService)](#sproc-execution-sprocservice)
4. [Axum — From Zero to Advanced](#axum--from-zero-to-advanced)
5. [Actix-Web — From Zero to Advanced](#actix-web--from-zero-to-advanced)
6. [Tauri — From Zero to Advanced](#tauri--from-zero-to-advanced)
7. [Entity CRUD (SqlService)](#entity-crud-sqlservice)
8. [Transactions](#transactions)
9. [Caching](#caching)
10. [Entity Macro Reference](#entity-macro-reference)
11. [Testing with MockRepository](#testing-with-mockrepository)
---
## Configuration
**`config.json`** (root of your project):
```json
{
"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
```rust
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:**
| `#[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:
| `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
```rust
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
```rust
// ── 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
```rust
// 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
```toml
# 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
```rust
// src/state.rs
use std::sync::Arc;
use sqlservice::SprocService;
#[derive(Clone)]
pub struct AppState {
pub sproc: Arc<SprocService>,
}
```
### 3. main.rs
```rust
// 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
```rust
// 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
```rust
// 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
```rust
// 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
```rust
// 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
```rust
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
```toml
# 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
```rust
// 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
```rust
// 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
```rust
// 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
```rust
// 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
```toml
# 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
```rust
// 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
```rust
// 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
```rust
// 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
```rust
#[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.
```rust
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
```rust
// 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
```rust
// 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
```rust
#[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:**
| `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
```rust
// 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
| `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` |