existential-birds/beagle / plugins/beagle-rust/skills/sqlx-code-review
sqlx-code-review
VerifiedReviews sqlx database code for compile-time query checking, connection pool management, migration patterns, and PostgreSQL-specific usage. Use when reviewing Rust code that uses sqlx, database queries, connection pools, or migrations. Covers offline mode, type mapping, and transaction patterns.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 7.4 KB |
| references/migrations.md | markdown | 5.4 KB |
| references/queries.md | markdown | 6.9 KB |
references/queries.md
View on GitHub# Queries
## Compile-Time Checked Queries
sqlx verifies queries against the database schema at compile time. This catches column name typos, type mismatches, and invalid SQL before runtime.
### query! Macro
Returns an anonymous struct with fields matching the query columns.
```rust
// Compile-time checked — column names and types verified
let row = sqlx::query!(
"SELECT id, name, email FROM users WHERE id = $1",
user_id
)
.fetch_one(&pool)
.await?;
let name: String = row.name;
let email: Option<String> = row.email; // nullable column → Option
```
### query_as! Macro
Maps results directly to a named struct. Preferred for reusable result types.
```rust
#[derive(Debug)]
struct User {
id: Uuid,
name: String,
email: Option<String>,
created_at: DateTime<Utc>,
}
let user = sqlx::query_as!(
User,
"SELECT id, name, email, created_at FROM users WHERE id = $1",
user_id
)
.fetch_optional(&pool)
.await?;
```
### Offline Mode
For CI/CD where the database isn't available, sqlx caches query metadata. In sqlx 0.8+, configuration lives in `sqlx.toml` and cached metadata is stored in the `.sqlx/` directory. Older versions used `sqlx-data.json`.
```bash
# Generate cache from live database
cargo sqlx prepare
# Build uses cached metadata when DATABASE_URL is absent
cargo build
```
In sqlx 0.8+, you can configure offline mode and other settings via `sqlx.toml`:
```toml
# sqlx.toml
[common]
offline = true
column-override = {}
```
## Fetch Methods
| Method | Returns | Use When |
|--------|---------|----------|
| `.fetch_one()` | `T` (error if 0 or 2+ rows) | Exactly one row expected (by PK) |
| `.fetch_optional()` | `Option<T>` | Zero or one row expected |
| `.fetch_all()` | `Vec<T>` | Small, bounded result set |
| `.fetch()` | `Stream<Item = Result<T>>` | Large or unbounded results |
### Common Mistake: fetch_one for Lookups
```rust
// BAD - returns Err(RowNotFound) on "not found" which is an expected case
let user = sqlx::query_as!(User, "SELECT ... WHERE id = $1", id)
.fetch_one(&pool)
.await?; // RowNotFound error for missing users
// GOOD - "not found" is a normal case, not an error
let user = sqlx::query_as!(User, "SELECT ... WHERE id = $1", id)
.fetch_optional(&pool)
.await?;
match user {
Some(user) => Ok(user),
None => Err(Error::NotFound(id)),
}
```
### Streaming Large Results
```rust
use futures::TryStreamExt;
let mut stream = sqlx::query_as!(Event, "SELECT * FROM events WHERE workflow_id = $1", wf_id)
.fetch(&pool);
while let Some(event) = stream.try_next().await? {
process(event).await;
}
```
### Edition 2024: RPIT Lifetime Capture in Query Helpers
In edition 2024, `-> impl Trait` captures all in-scope lifetimes by default. This affects functions that return streams or futures from sqlx queries.
```rust
// Edition 2021 — worked because `-> impl Stream` didn't capture 'a
fn get_events<'a>(pool: &'a PgPool, wf_id: Uuid) -> impl Stream<Item = Result<Event, sqlx::Error>> {
sqlx::query_as!(Event, "SELECT * FROM events WHERE workflow_id = $1", wf_id)
.fetch(pool)
}
// Edition 2024 — captures 'a by default, which is usually correct here.
// If you need to NOT capture a lifetime, use precise capture syntax:
fn get_events<'a>(pool: &'a PgPool, wf_id: Uuid) -> impl Stream<Item = Result<Event, sqlx::Error>> + use<'a> {
sqlx::query_as!(Event, "SELECT * FROM events WHERE workflow_id = $1", wf_id)
.fetch(pool)
}
```
Most sqlx query helpers that borrow the pool *should* capture the pool lifetime, so the edition 2024 default is usually correct. Flag cases where the return type is stored in a struct that outlives the borrow.
## Bind Parameters
Always use bind parameters (`$1`, `$2` for Postgres; `?` for MySQL/SQLite). Never interpolate values into query strings.
```rust
// BAD - SQL injection vulnerability
let query = format!("SELECT * FROM users WHERE name = '{}'", name);
sqlx::query(&query).fetch_one(&pool).await?;
// GOOD - parameterized query
sqlx::query("SELECT * FROM users WHERE name = $1")
.bind(&name)
.fetch_one(&pool)
.await?;
// BEST - compile-time checked
sqlx::query!("SELECT * FROM users WHERE name = $1", name)
.fetch_one(&pool)
.await?;
```
## Type Mapping
### Rust ↔ PostgreSQL
| Rust Type | PostgreSQL Type |
|-----------|-----------------|
| `i32` | `INT4` / `INTEGER` |
| `i64` | `INT8` / `BIGINT` |
| `f64` | `FLOAT8` / `DOUBLE PRECISION` |
| `Decimal` | `NUMERIC` / `DECIMAL` |
| `String` | `TEXT` / `VARCHAR` |
| `bool` | `BOOL` |
| `Uuid` | `UUID` |
| `DateTime<Utc>` | `TIMESTAMPTZ` |
| `NaiveDateTime` | `TIMESTAMP` |
| `serde_json::Value` | `JSONB` / `JSON` |
| `Vec<u8>` | `BYTEA` |
| `Option<T>` | Nullable column |
### Custom Enum Types
```rust
#[derive(Debug, Clone, sqlx::Type, Serialize, Deserialize)]
#[sqlx(type_name = "varchar", rename_all = "snake_case")]
#[serde(rename_all = "snake_case")]
pub enum Status {
Pending,
InProgress,
Complete,
Failed,
}
```
Ensure `rename_all` matches between `sqlx::Type` and `serde` — mismatches cause silent bugs where data written by one system can't be read by the other.
### Edition 2024: Reserved `gen` Keyword
In edition 2024, `gen` is a reserved keyword. Any sqlx enum variant or struct field named `gen` will fail to compile. Use `r#gen` as the Rust identifier and `#[sqlx(rename)]` to preserve the database column name.
```rust
// BAD — fails to compile on edition 2024
#[derive(sqlx::Type)]
#[sqlx(type_name = "varchar", rename_all = "snake_case")]
pub enum GenerationType {
Manual,
Gen, // compile error: `gen` is a reserved keyword
}
// GOOD — compiles on edition 2024, database value unchanged
#[derive(sqlx::Type)]
#[sqlx(type_name = "varchar", rename_all = "snake_case")]
pub enum GenerationType {
Manual,
#[sqlx(rename = "gen")]
r#Gen,
}
```
### Edition 2024: `#[expect]` for Lint Suppression
Prefer `#[expect(unused)]` over `#[allow(unused)]` for struct fields that exist only for sqlx mapping but aren't read directly. The `#[expect]` attribute warns when the suppression becomes unnecessary, keeping lint overrides self-cleaning.
```rust
// BAD — silent if the field starts being used elsewhere
#[allow(dead_code)]
struct AuditRow {
id: i64,
raw_payload: serde_json::Value,
}
// GOOD — warns when suppression is no longer needed
#[expect(dead_code)]
struct AuditRow {
id: i64,
raw_payload: serde_json::Value,
}
```
## Review Questions
1. Are queries compile-time checked where possible?
2. Is `.fetch_optional()` used for lookups that may return no rows?
3. Are bind parameters used (no string interpolation)?
4. Is `.fetch()` streaming used for large result sets?
5. Do Rust types match PostgreSQL column types?
6. Are enum representations consistent between sqlx and serde?
7. (Edition 2024) Do any enum variants or fields use `gen` as an identifier without `r#gen`?
8. (Edition 2024) Do functions returning `-> impl Stream`/`-> impl Future` account for RPIT lifetime capture changes?
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |