> ## Documentation Index
> Fetch the complete documentation index at: https://rydantic.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Queries and transactions

> Filter, paginate and persist validated rows with SQLx.

```rust theme={"system"}
use rydantic::sql::{Compare, Order};

let users = User::select()
    .filter("age", Compare::GreaterOrEqual, &18u8)?
    .order_by("name", Order::Ascending)?
    .limit(20)
    .offset(0)
    .fetch_all(&pool)
    .await?;

let mut transaction = pool.begin().await?;
let stored = user.insert(&mut transaction).await?;
assert!(User::get(&mut transaction, &stored.id.unwrap()).await?.is_some());
transaction.rollback().await?;
```

`where_eq`, comparison filters, ordering, limits, and offsets use checked column names. NULL equality/inequality becomes `IS NULL`/`IS NOT NULL`. Operations accept a pool, mutable connection, or SQLx transaction. Insert/update operations use a transaction or nested savepoint; failed constraints or returned-row validation roll back the write. Missing rows return `None` from `get`, `false` from `delete`, or `SqlError::NotFound` from `update`.

Writes await synchronous and asynchronous model validation before acquiring a connection, then recheck returned rows synchronously before committing. Reads validate stored typed values and release acquired pooled connections before awaiting asynchronous callbacks. Models with asynchronous rules require a pool: insert, update, get and select return `sql_async_connection` immediately for borrowed connections and transactions, which can otherwise hold the only pool slot needed by a callback. Synchronous models support all three connection forms. Normalization runs during JSON/model parsing; persistence checks preserve stored typed values. Skipped fields use the same default preparation, `before` callbacks, and normalization as a missing JSON field. Synchronous SQLx `query_as` uses the generated `FromRow` validator; models with asynchronous rules use the awaiting `SqlModel` entry points.

Portable fields use these native storage declarations:

| Rust field | SQLite | PostgreSQL | MySQL |
| - | - | - | - |
| Integer types through 64 bits | INTEGER | BIGINT | BIGINT |
| `bool` | INTEGER | BOOLEAN | BOOLEAN |
| `f32`, `f64` | REAL | DOUBLE PRECISION | DOUBLE |
| `String` | TEXT | TEXT | LONGTEXT or bounded VARCHAR |
| `Vec<u8>` | BLOB | BYTEA | LONGBLOB or bounded VARBINARY |
| Dates, Decimal, `i128`, `u128` | TEXT | TEXT | LONGTEXT or bounded VARCHAR |

Unsigned 64-bit values use the common signed 64-bit SQL range; conversions reject overflow. Decimal and 128-bit text storage preserves precision. Comparisons and ordering for these text fields follow the database's text semantics. `Option<T>` uses correctly typed SQL NULL parameters, and `Box<T>` uses its inner representation. Enable `chrono`/`decimal` for the corresponding field types. Identity sequences follow each database's native behavior, including gaps after rolled-back writes.

Custom SQL field representations implement the required `SqlValue::to_parameter` and `from_parameter` conversions through `SqlParameter`. The same codec handles all enabled drivers and filters. `SqlModel` owns table metadata and CRUD; `SqlRow<DB>` owns typed row decoding and column binding. The derive generates both traits without driver-specific compatibility hooks.

`create_table` creates missing tables with their declared indexes. `migrate` also upgrades existing tables from the current model definition. Foreign keys describe database references; application queries load related models. The table layer covers single-column keys and the portable scalar mappings above.

## SQL architecture

The SQL API uses one portable codec and explicit driver features. Select `sqlite`, `postgres` or `mysql` in Cargo.toml. The shared `sqlx` feature provides metadata and generic APIs without a database driver. DDL previews require a database type parameter: `create_table_sql::<DB>()` and `create_index_sql::<DB>()`. Custom codecs implement both portable conversions; row decoding and binding use `SqlRow<DB>`.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.