# Rust Persistence SQL

> Rust persistence, SQLx, SeaQuery, and SQL-library guidance. Use when adding, changing, reviewing, or testing SQLx queries/macros, SeaQuery builders, SeaORM, Diesel, raw SQL choices, Rust database adapters, pools, transactions, offline query metadata, migrations invoked from Rust, SQLite support, database-backed Rust tests, dynamic SQL construction, or persistence boundaries. Use sql-engineering, postgresql-sql-engineering, mysql-mariadb-sql-engineering, or sqlite-sql-engineering for database-native design.

- Skill: `nledford/rust-persistence-sql` (Agent Skill)
- Install (CLI): `npx skillmds@latest add nledford/rust-persistence-sql`
- Raw SKILL.md: https://api.skillmd.com/api/skills/nledford/rust-persistence-sql/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: nledford (https://skillmd.com/u/nledford)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/nledford/rust-persistence-sql

---


# Rust Persistence, SQLx, And SeaQuery

Use this skill for database-backed Rust work. Keep persistence adapters explicit,
keep core domain rules independent of database mechanics where practical, and
validate both Rust types and database behavior. For database-neutral SQL design,
use [`sql-engineering`](../sql-engineering/SKILL.md). For language-independent
PostgreSQL schema, SQL, query-plan, security, and migration design, use
[`postgresql-sql-engineering`](../postgresql-sql-engineering/SKILL.md). For
MySQL- or MariaDB-native schema, query-plan, and migration design, use
[`mysql-mariadb-sql-engineering`](../mysql-mariadb-sql-engineering/SKILL.md).
For SQLite-native schema, transaction, and test-database behavior, use
[`sqlite-sql-engineering`](../sqlite-sql-engineering/SKILL.md). Use
[`hexagonal-architecture`](../hexagonal-architecture/SKILL.md) when deciding
whether repositories, database gateways, transactions, or query services should
be outbound ports and adapters rather than core domain code.

For a requested Rust review, use [`code-review`](../code-review/SKILL.md) with
[`rust-code-review`](../rust-code-review/SKILL.md) and
[`review-verification-protocol`](../review-verification-protocol/SKILL.md) as the
review workflow; add this skill for persistence-specific evidence.

## Workflow

1. Inspect the persistence setup: `Cargo.toml` SQLx and SeaQuery dependencies,
   feature flags, migrations, `DATABASE_URL` conventions, `.sqlx/` offline
   metadata, pool types, transaction helpers, repository recipes, seed/fixture
   strategy, and CI database services.
2. Model the boundary. Use DDD language for tables, aggregates, invariants, and
   repository/service responsibilities. Do not let query row shapes become the
   core domain model by accident.
3. When storage behavior changes, design SQL and migrations with the database
   skill first, then implement the Rust adapter.
4. Implement SQLx or SeaQuery access with typed parameters, explicit result
   mapping, narrow transactions, and safe error conversion.
5. Test at the right layer: pure domain tests, SQLx integration tests against
   the target database, and end-to-end tests only for user-visible workflows.
6. Update SQLx offline query metadata for every supported configuration that
   compiles query macros, or document why offline metadata is not used.

## Security Review Prompts

Load [`security-review`](../security-review/SKILL.md) when Rust persistence work
touches SQL injection risk, SQLx/SeaQuery dynamic SQL, database credentials or
`DATABASE_URL`, migration/backfill exposure, RLS/privilege assumptions, tenant
isolation, audit logs, startup migrations, database error leakage, or
production-data access. Use
[`threat-modeling`](../threat-modeling/SKILL.md) before or during new tenant,
privilege, migration/backfill, external-database, audit, or sensitive data-flow
boundaries. Use
[`dependency-supply-chain-review`](../dependency-supply-chain-review/SKILL.md)
when persistence crates, migration tools, generated database clients, CI database
bootstrap, or database/client binaries raise provenance or advisory questions.
Pair security-sensitive reviews with
[`security-review-evidence`](../security-review-evidence/SKILL.md) when evidence
includes sanitized SQL, bind values, query plans, `.sqlx` metadata, migration
output, logs, dumps, or other persistence artifacts.

## SQLx Checklist

- Prefer compile-checked SQLx macros such as `query!` or `query_as!` when the
  query shape is static and the repository supports online or offline checking.
- Use dynamic SQL only when the shape is genuinely dynamic; bind values instead
  of formatting user input into SQL.
- Map nullable columns, database enums, time types, UUIDs, JSON, and numeric
  precision deliberately.
- Keep pool acquisition, transaction scope, and timeout behavior explicit.
- Convert database errors at the adapter edge. Preserve enough context for logs
  while returning safe domain or API errors.
- For PostgreSQL, MySQL/MariaDB, and SQLite support in one codebase, test each
  target engine independently. SQL dialects, type affinity, locking,
  migrations, and constraint behavior differ, and MySQL and MariaDB diverge
  from each other too even though both use the `mysql` SQLx/SeaQuery driver.

### Offline Metadata

Query macros behind `cfg(test)`, target-specific modules, or features are not
necessarily compiled by a default build. Inspect the repository-supported Cargo
target and feature matrix, then forward that matrix to Cargo after `--` when
preparing or checking metadata. `--workspace` on `cargo sqlx prepare` writes one
workspace-root `.sqlx` directory; pass Cargo's workspace scope after `--` too
when the supported command needs it.

For a project where all targets and all features are compatible, for example:

```sh
cargo sqlx prepare -- --all-targets --all-features
```

For a workspace or a narrower supported feature set, adapt the forwarded Cargo
arguments rather than assuming that all features can coexist:

```sh
cargo sqlx prepare --workspace -- --workspace --all-targets --features <supported-feature-set>
cargo sqlx prepare --check --workspace -- --workspace --all-targets --features <supported-feature-set>
SQLX_OFFLINE=true cargo check --workspace --all-targets --features <supported-feature-set>
```

Require `SQLX_OFFLINE=true` compile checks for each supported configuration that
uses query macros. Run the matching prepare command against the repository's
approved database/schema first, then use `prepare --check` and the offline Cargo
check to prove committed metadata covers that configuration.

Prefer `sqlx` when:

- Queries are mostly static and readable as SQL.
- Compile-time checked queries or offline query checking are valuable.
- Query shape is known ahead of time.
- Migrations, pools, transactions, and row mapping are the main concern.
- A query builder would obscure simpler explicit SQL.

## SeaQuery Checklist

Use SeaQuery for dynamic SQL construction in Rust when it is already a project
convention or when runtime query shape would otherwise require unsafe or
unreviewable string assembly.

- Inspect the installed version and features before editing: `sea-query`,
  `sea-query-sqlx`, backend features such as `backend-postgres`,
  `backend-sqlite`, or `backend-mysql`, and existing `Iden`/identifier enums.
- Prefer SeaQuery when many filters, sorts, joins, projections, predicates, or
  dialect targets are optional and composition improves clarity.
- Avoid SeaQuery when a plain `sqlx::query!`, `query_as!`, or `query_file!` is
  clearer, compile-time checking matters more, or dialect-specific SQL is easier
  to review directly.
- Keep query builders small and close to the persistence adapter or query
  service. Do not leak SeaQuery types into core domain models unless the crate
  is explicitly a database adapter.
- Preserve parameter binding. Prefer APIs that build SQL plus values for the
  driver; reserve value-injected SQL strings for tests, sanitized logs, and
  debugging.
- Keep generated SQL reviewable. Name reusable predicates, avoid deeply nested
  builder chains, and snapshot only stable SQL shapes where that helps review.
- Make dialect choices explicit. PostgreSQL, SQLite, and MySQL/MariaDB differ
  in placeholders, quoting, upsert syntax, return clauses, JSON, arrays,
  date/time, locking, and DDL — and MySQL and MariaDB diverge from each other
  on some of these (for example, native `JSON` storage and `RETURNING`
  support) even under the same `backend-mysql` feature.

Prefer SeaQuery when:

- Query shape is genuinely dynamic.
- Many optional filters, sorts, joins, projections, or predicates must compose.
- The same query-building logic targets multiple SQL dialects.
- Repeated string concatenation would become unsafe or hard to review.
- The project already uses SeaQuery consistently.
- An AST-style builder improves maintainability.

Use SeaQuery with `sqlx` when SeaQuery builds the SQL and bind values while
SQLx remains responsible for execution, pooling, transactions, migrations, and
row mapping. In projects using `sea-query-sqlx`, inspect the installed version;
current docs expose a `SqlxBinder` integration that builds SQLx-compatible SQL
and values for `sqlx::query_with`.

Common commands:

```sh
sqlx migrate info
sqlx migrate run
sqlx migrate revert
cargo sqlx prepare --workspace -- --workspace --all-targets --features <supported-feature-set>
cargo sqlx prepare --check --workspace -- --workspace --all-targets --features <supported-feature-set>
SQLX_OFFLINE=true cargo check --workspace --all-targets --features <supported-feature-set>
```

Adapt command names to the repository's SQLx CLI version and recipes. Some
projects use `cargo sqlx ...`; others install a standalone `sqlx` binary.

## Schema And Migration Review

- Database invariants live in the database skill; the Rust review checks that
  Rust types, query mappings, migrations, and domain errors line up with them.
- SQLx migrations are invoked through the repository's chosen workflow. Do not
  assume `sqlx migrate add/run/revert` is authoritative when the repo has a
  custom migration generator or schema source of truth.
- Rust migration code, embedded migrations, and startup migration hooks have a
  rollback and deployment story. Avoid surprise DDL on application boot unless
  that is explicit repository policy.
- Seed data and fixtures are deterministic, isolated, and safe for repeated test
  runs.

## SQL Library Choice

- Prefer raw SQL with SQLx macros when queries are static, readability matters,
  compile-time checking is available, and SQL expresses the behavior directly.
- Prefer SeaQuery when query shape is genuinely dynamic, many optional filters or
  dialects must compose, or string assembly would be unsafe or hard to review.
- Consider SeaORM only when the project already uses it or needs its entity,
  relation, migration, and active-model conventions across a broad persistence
  layer. Do not add it for one query or to avoid understanding SQL.
- Consider Diesel when the repository already uses Diesel or needs its typed query
  DSL, compile-time schema integration, and migration workflow. Do not mix it
  into a SQLx-centered project without a clear boundary.
- Prefer explicit raw SQL for migrations, performance-sensitive queries,
  database-native features, hand-tuned plans, or cases where an ORM/query builder
  hides important semantics.

## SQLite Guidance

- Do not assume SQLite is a drop-in PostgreSQL substitute. Use
  [`sqlite-sql-engineering`](../sqlite-sql-engineering/SKILL.md) for
  SQLite-specific schema, transaction, PRAGMA, locking, and test-database
  behavior.
- Type affinity, foreign key enforcement, locking, datetime handling, DDL
  support, and concurrency behavior differ from PostgreSQL.
- Ensure foreign keys are enabled when tests or application logic rely on them.
- Keep migrations compatible with SQLite's DDL limits, or isolate SQLite-specific
  migrations when the repository already supports multiple database backends.
- Use SQLite for local or embedded workflows when that is the product choice,
  not as proof that PostgreSQL-specific SQL is correct.

## MySQL/MariaDB Guidance

- Do not assume MySQL and MariaDB are interchangeable even though SQLx and
  SeaQuery share a single `mysql`/`backend-mysql` feature for both. Use
  [`mysql-mariadb-sql-engineering`](../mysql-mariadb-sql-engineering/SKILL.md)
  for engine-specific schema, storage-engine, replication, and query-plan
  behavior, and confirm which engine and version the code actually targets.
- SQLx's `MySqlConnection` connects to both engines, but engine-specific SQL
  (native `JSON` type behavior, window functions/CTEs on older MySQL, MariaDB
  `RETURNING`, `INSERT ... ON DUPLICATE KEY UPDATE` semantics) is not portable
  between them without verification.
- InnoDB is the default and correct storage engine for transactional tables on
  both; do not assume a schema imported from or generated against a
  non-InnoDB table (MyISAM, Aria, MEMORY) preserves transactional or
  foreign-key guarantees.
- Test against both engines in CI when the repository claims to support both;
  a passing MySQL integration test does not prove MariaDB compatibility, or
  the reverse.

## SQL Review Checklist

- Queries select explicit columns and return only the data the caller needs.
- Predicates are sargable where possible and match available indexes.
- Pagination is stable under inserts/deletes; keyset pagination is considered
  for large ordered result sets.
- Joins preserve intended cardinality and do not accidentally multiply rows.
- `NULL` handling, collation, case sensitivity, timezone, and numeric precision
  are explicit where they affect behavior.
- Transactions cover exactly the consistency boundary and no more.
- User-controlled values are bound parameters, never interpolated SQL.
- Observability exists for slow queries, migration failures, and pool exhaustion
  when the repository has a logging/tracing pattern.
- For PostgreSQL-specific index, constraint, RLS, privilege, and query-plan
  review, load `postgresql-sql-engineering`.
- For MySQL/MariaDB-specific storage-engine, index, replication, and
  query-plan review, load `mysql-mariadb-sql-engineering`.
- For SQLite-specific PRAGMA, type-affinity, locking, migration, and temp
  database behavior, load `sqlite-sql-engineering`.

## Testing And Acceptance Criteria

- Write a failing test or migration check before fixing a query bug when
  practical.
- Use BDD-style examples for user-visible persistence behavior, such as
  "Given an existing account, when a duplicate email is saved, then the caller
  receives a conflict and no partial write remains."
- Use integration tests against the database engine whose behavior matters.
- Verify migrations from an empty database and, when compatibility matters, from
  a representative previous schema.
- Run SQLx prepare/check commands after changing compile-checked queries,
  schema, or migrations.
- For SeaQuery builders, add focused tests for generated SQL shape and bind
  values when logic is dynamic. Cover no filters, one filter, multiple filters,
  sort order, pagination, empty inputs, `NULL`, and dialect-specific behavior.

## Anti-Patterns

- Treating application validation as a substitute for database constraints on
  critical invariants.
- Returning raw SQLx or driver errors through domain or HTTP APIs.
- Building SQL by string concatenation with user input.
- Introducing SeaQuery for simple static SQL that would be clearer as `sqlx`
  macros or explicit SQL.
- Calling `to_string` or debug-rendered SQL with user-controlled values as an
  execution path instead of using bound values.
- Adding indexes speculatively without a query and write-cost rationale.
- Hiding long transactions behind generic repository helpers.
- Claiming PostgreSQL behavior from SQLite-only tests, or the reverse.
- Claiming MariaDB behavior from MySQL-only tests, or the reverse.