# cratestack-sqlx
SQLx-backed Postgres runtime and delegate primitives for CrateStack models.
## Overview
`cratestack-sqlx` is the server-side database layer. `include_server_schema!` generates one delegate per model, plus migration helpers, audit DDL, idempotency-store DDL, optimistic-locking support, and transaction-isolation helpers — all backed by SQLx + PostgreSQL.
## Installation
```toml
[dependencies]
cratestack-sqlx = "0.2.2"
sqlx = { version = "0.8", default-features = false, features = [
"runtime-tokio-rustls", "postgres", "chrono", "uuid", "json", "macros",
] }
```
Most users depend on the umbrella `cratestack` crate instead, which re-exports the entire surface.
## Delegate Usage
Generated by `include_server_schema!`. The unscoped delegate takes `&ctx` on `.run`; the scoped variant (`cool.bind_context(ctx).user()`) captures the context once and drops the trailing argument.
```rust
use cratestack::{CoolContext, include_server_schema};
use cratestack_schema::{Cratestack, post};
include_server_schema!("schema.cstack");
let pool = sqlx::PgPool::connect(&database_url).await?;
let cool = Cratestack::builder(pool).build();
let ctx = CoolContext::anonymous();
// find_unique → Option<M>
let user = cool.user().find_unique(user_id.clone()).run(&ctx).await?;
// find_many with filters and ordering
let posts = cool
.post()
.find_many()
.where_expr(
post::published().is_true()
.and(post::author().email().eq("owner@example.com"))
)
.order_by(post::createdAt().desc())
.limit(20)
.run(&ctx)
.await?;
// Create
let created = cool.user().create(CreateUserInput { /* ... */ }).run(&ctx).await?;
// Update (with optimistic locking via `if_match`)
let updated = cool
.user()
.update(user_id.clone())
.set(UpdateUserInput { /* ... */ })
.if_match(expected_version)
.run(&ctx)
.await?;
// Delete
cool.user().delete(user_id).run(&ctx).await?;
```
## Transactions Under an Isolation Level
The crate exposes `run_in_isolated_tx` and `run_in_isolated_tx_with_retries` for procedures that need explicit isolation. Both transparently retry on PostgreSQL SQLSTATE `40001` (serialization_failure) and `40P01` (deadlock_detected), including failures detected at COMMIT time.
```rust
use cratestack::{TransactionIsolation, run_in_isolated_tx};
let result = run_in_isolated_tx(
cool.pool(),
TransactionIsolation::Serializable,
|mut tx| async move {
// Run your SQL against `&mut *tx` so all writes share the transaction.
let value = perform_transfer(&mut tx).await?;
Ok((value, tx))
},
).await?;
```
Schemas declare the requested isolation through `@isolation("serializable")` on a procedure; the macro records the level on the procedure's metadata constant so dispatch code can pass it to these helpers.
## Audit Log
Models with `@@audit` write before/after snapshots into a `cratestack_audit` table inside the same transaction as the mutation. `AUDIT_TABLE_DDL` is exported for migration tooling. `@pii` and `@sensitive` columns are redacted in the persisted snapshots.
## Idempotency
`SqlxIdempotencyStore::new(pool)` implements the `IdempotencyStore` trait from `cratestack-axum::idempotency`. Use it with `IdempotencyLayer`. The `expiry_from(created_at, ttl)` helper computes the deadline a record should be evicted at.
## Migrations
The crate exports `Migration`, `MigrationState`, `MigrationStatus`, `MIGRATIONS_TABLE_DDL`, `apply_pending`, `ensure_migrations_table`, and `status` for working with a `cratestack_migrations` table.
## Decimal Backend
`cratestack-sqlx` follows the workspace `decimal-rust-decimal` / `decimal-bigdecimal` feature flags; generated columns of type `Decimal` use the selected backend.
## See Also
- [Transaction Isolation guide](https://cratestack.dev/guides/transaction-isolation)
- [Audit Log guide](https://cratestack.dev/guides/audit-log)
- [Optimistic Locking guide](https://cratestack.dev/guides/optimistic-locking)
- [Migrations guide](https://cratestack.dev/guides/migrations)
- `cratestack-sql` — shared SQL primitives
- `cratestack-rusqlite` — SQLite backend (sync, on-device)
## License
MIT