rust-database

Rust Database Layer Delivery

Deliver a migration-compatible, rollback-capable, and observable data access layer based on the actual dialects, schemas, and transaction semantics of databases. Do not treat HTTP handlers as repositories; do not use mocks to validate real database behavior.

Confirming Database Contracts

Before modifications:

• Collect information about the database product, exact version, extensions, deployment topology, read/write nodes;
• Gather current schema constraints, indexes, triggers, views, and migration history;
• Identify locked versions of SQLx, Diesel, SeaORM (or other access stacks) along with their features;
• Determine Rust toolchain/MSRV, synchronous or asynchronous execution models;
• Establish connection limits, instance counts, timeouts, transaction isolation levels, and consistency requirements;
• Define data volume profiles, hot query patterns, pagination strategies, retention policies, and archival needs;
• Document credential sources, TLS configurations, tenant boundaries, row-level permissions, and audit requirements;
• Clarify whether deployments allow downtime, double writes, backfills, or destructive DDL operations.

When real database access is unavailable, distinguish between "code/offline metadata validation" and "real dialect validation." Do not conflate these two approaches in reporting.

Workflow

1. Establish Current Baseline

rustc --version --verbose
cargo metadata --format-version 1
cargo tree -e features
cargo test --all-targets

Locate connection initialization, pool configuration, repository implementations, queries, migrations, test fixtures, and CI database connections. Execute existing migration status/check commands; do not run operations that modify shared databases without explicit authorization.

2. Preserve Existing Access Stacks

Avoid rewriting solely based on preference among SQLx, Diesel, or SeaORM. Select the appropriate stack according to current codebase and task requirements:

• SQLx: Requires direct SQL access, asynchronous querying, and optional compile-time query validation;
• Diesel: Utilizes typed DSL queries, synchronous connections, or existing Diesel schemas;
• SeaORM: Leverages async ORM/entity models with corresponding migration systems;
• Native database drivers: Reserved for cases where protocol or functionality does not support a generic layer, though expanding maintenance and security review scope.

Specific selection criteria and type boundaries are documented in Access Stack and Types. All APIs must align with the versioned documentation from Cargo.lock, avoiding direct application of latest examples to legacy projects.

3. Let Schema Constraints Enforce Facts

• Define primary keys, uniqueness constraints, foreign keys, non-null checks, and check constraints as immutable invariants guaranteed by the database;
• Rust types express domain semantics but cannot replace cross-process concurrency guarantees provided by databases;
• Explicitly specify integer widths, decimal precision, timezones, UUIDs, JSON formats, nullability mappings;
• Bind parameters to all queries. Use dynamic identifiers or sort fields only via allowlists and type mapping—not as regular bind values pretending to be safe;
• Restrict column selection to necessary columns to avoid reliance on unstable SELECT * layouts;
• Prioritize stable sorting strategies and keyset-based pagination over unbounded offset operations under high data volumes.

4. Design Migrations Before Code Changes

For each schema change, document:

1. Forward DDL statements;
2. Compatibility windows between old and new versions for applications;
3. Data backfill methods, batch sizes, and rollback points;
4. Index construction risks and lock contention implications;
5. Rollback or forward recovery strategies;
6. Validation queries, monitoring metrics, and termination conditions.

Adopt the sequence: expand → backfill → switch → contract. Destructive operations—column deletion, type changes, large table reconstruction, data clearing, irreversible backfills—require explicit target parsing and authorization details found in Migrations and Schema Evolution.

5. Place Transactions Within Complete Use Cases

• Initiate transactions via application services or repository unit-of-work units; do not commit transactionally across HTTP handlers indiscriminately;
• Ensure atomic success of writes within a single shared transaction and connection set;
• Avoid unbounded user interactions outside the transaction scope, including unnecessary external HTTP waits inside transactions;
• Select isolation levels per database documentation to identify risks such as lost updates, write skew, phantom reads;
• Retry only transient errors explicitly identified by the database. Set retry counts, backoff strategies, and idempotency boundaries;
• Commit failures are possible; do not treat "last SQL statement success" as transactional completion;
• Drop/rollback serves as a safety net but does not replace explicit control flow design or testing practices.

Connection pooling, isolation levels, and retry mechanisms are detailed in Transactions and Pooling.

6. Design Stable Data Access Interfaces

application use case
  -> repository / unit of work port
  -> SQL/ORM adapter
  -> database

• Repository methods should accept domain parameters and results without leaking Web DTOs;
• Classify errors into categories: not found, uniqueness conflicts, foreign key violations, serialization failures, timeouts, unavailability;
• Preserve full error chains for logging and diagnostics while preventing sensitive SQL parameter exposure in user responses;
• Batch interfaces must define ordering semantics, partial failure handling, idempotency guarantees, and return row counts;
• Avoid N+1 queries. Validate performance under realistic data scales when pre-fetching joins or batching is required.

7. Configure Limited Connection Budgets

Validate total connection budgets using the formula: (per-instance pool limit × maximum instance count) + operational connections. Set acquire, connect, statement, and transaction timeouts; define idle/max lifetimes, health checks, and graceful shutdown procedures. Do not instantiate a new pool per request nor let indefinite waits mask resource exhaustion issues.

8. Validate with Real Engines

Conduct at least the following validations:

1. Execute all migrations from an empty database;
2. Upgrade schema versions supported by existing databases;
3. Verify repository success, missing records, constraint conflicts, and type boundaries;
4. Test transaction commits, rollbacks, commit failures, and retryable concurrency issues;
5. Ensure invariants hold under concurrent writes;
6. Confirm query plans, index hits, and representative data volumes;
7. Assess pool exhaustion scenarios, database restarts/disconnections, and graceful shutdown behaviors;
8. Verify logs, metrics, and traces do not leak bound values or credentials.

Test execution and operation lists are provided in Testing and Operations.

Common Gates (Gateways)

cargo fmt --all -- --check
cargo check --all-targets
cargo test --all-targets
cargo clippy --all-targets -- -D warnings

Run repository-specific migration validation, SQLx offline metadata checks, Diesel schema audits, or real database integration tests. Commands and features may vary by version; consult project configuration first.

Completion Criteria

• Database versions, schemas, access stacks, and consistency assumptions are clearly defined;
• All queries use parameterization with dynamic identifiers filtered via allowlists;
• Migration compatibility windows, backfill strategies, recovery procedures, and destructive boundaries are documented;
• Transaction coverage spans complete use cases with justified connection budgets and timeouts;
• Real database testing validates migrations, constraints, rollbacks, and concurrency risks;
• fmt, check, test, Clippy pass; unvalidated real-database tests must be explicitly marked.

Handoff Boundaries

Primary Responsibility	Assigned To
HTTP routing, handlers, status codes, middleware	rust-web
Credentials, tenant authorization, audit logs, sensitive field exposure	rust-web-security
Async cancellation, locks, tasks, blocking isolation	rust-concurrency
Test layering, coverage metrics, performance baselines	rust-testing
Features, build scripts, offline metadata, release pipelines	rust-cargo-build
Types, ownership semantics, error traits, standard library behavior	rust-stable

On-Demand Resources

• Access Stack and Types: Consult when selecting or adapting SQLx, Diesel, SeaORM, or type mappings.
• Migrations and Schema Evolution: Review DDL design, compatibility backfills, and recovery strategies.
• Transactions and Pooling: Refer for atomicity guarantees, isolation levels, retry logic, and capacity management.
• Testing and Operations: Access real-database test setups, query plans, observability practices.
• Scenario Examples: Use this template when end-to-end task decomposition is required.
• examples/golden-transaction/: Offline compilation and validation of transaction boundary examples available here.

References

• std::error (Rust Standard Library)
• SQLx documentation
• Diesel guides
• SeaORM documentation