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/migrations.md
View on GitHub# Migrations and Pool Management
## Connection Pool Configuration
### Creating the Pool
```rust
use sqlx::postgres::PgPoolOptions;
let pool = PgPoolOptions::new()
.max_connections(20)
.min_connections(5)
.acquire_timeout(Duration::from_secs(5))
.idle_timeout(Duration::from_secs(600))
.max_lifetime(Duration::from_secs(1800))
.connect(&database_url)
.await?;
```
### Edition 2024: Static Pool with `LazyLock`
For applications that initialize a global pool once, use `std::sync::LazyLock` instead of `once_cell::sync::Lazy` or `lazy_static!`. `LazyLock` is in std since Rust 1.80.
```rust
// BAD — third-party crate, unnecessary dependency
use once_cell::sync::Lazy;
static POOL: Lazy<PgPool> = Lazy::new(|| {
tokio::runtime::Handle::current().block_on(async {
PgPoolOptions::new()
.max_connections(20)
.connect(&std::env::var("DATABASE_URL").unwrap())
.await
.unwrap()
})
});
// GOOD — std library, no extra dependency
use std::sync::LazyLock;
static POOL: LazyLock<PgPool> = LazyLock::new(|| {
tokio::runtime::Handle::current().block_on(async {
PgPoolOptions::new()
.max_connections(20)
.connect(&std::env::var("DATABASE_URL").unwrap())
.await
.unwrap()
})
});
```
Note: Framework-managed state (e.g., axum `State<PgPool>`) is still preferred over global statics. Use `LazyLock` only when a static singleton is genuinely needed.
### Pool Sizing Guidelines
- **Web servers:** 2-4× the number of async worker threads
- **Background workers:** Match to the number of concurrent jobs
- **Default (5):** Too low for most production workloads
- **Maximum:** Don't exceed the database's `max_connections` minus connections reserved for admin/monitoring
### Common Mistake: Pool Per Request
```rust
// BAD - creates a new pool (and connections) per request
async fn handle(req: Request) -> Response {
let pool = PgPool::connect(&url).await.unwrap();
let user = query_as!(User, "...", id).fetch_one(&pool).await?;
// pool dropped, connections closed
}
// GOOD - share pool via application state
async fn handle(State(pool): State<PgPool>, req: Request) -> Response {
let user = query_as!(User, "...", id).fetch_one(&pool).await?;
}
```
## Transactions
### Basic Pattern
```rust
let mut tx = pool.begin().await?;
sqlx::query!("INSERT INTO orders (user_id, total) VALUES ($1, $2)", user_id, total)
.execute(&mut *tx)
.await?;
sqlx::query!("UPDATE inventory SET count = count - $1 WHERE item_id = $2", qty, item_id)
.execute(&mut *tx)
.await?;
tx.commit().await?;
```
If `tx` is dropped without calling `.commit()`, the transaction rolls back automatically. This is a safety net — explicit commit is clearer.
### Error Handling in Transactions
```rust
async fn create_order(pool: &PgPool, order: NewOrder) -> Result<Order, Error> {
let mut tx = pool.begin().await?;
let order = sqlx::query_as!(Order, "INSERT INTO orders ... RETURNING *", ...)
.fetch_one(&mut *tx)
.await
.map_err(|e| {
// tx will rollback on drop
Error::OrderCreate { source: e }
})?;
for item in &order.items {
sqlx::query!("INSERT INTO order_items ...", ...)
.execute(&mut *tx)
.await?;
}
tx.commit().await?;
Ok(order)
}
```
### Savepoints (Nested Transactions)
```rust
let mut tx = pool.begin().await?;
// Outer operation
sqlx::query!("INSERT INTO audit_log ...").execute(&mut *tx).await?;
// Inner operation that might fail independently
let savepoint = tx.begin().await?; // creates a savepoint
match try_optional_operation(&mut *savepoint).await {
Ok(_) => savepoint.commit().await?,
Err(_) => {
// savepoint rolls back, outer transaction continues
tracing::warn!("optional operation failed, continuing");
}
}
tx.commit().await?;
```
## Migrations
### File Structure
```
migrations/
├── 20240115100000_create_users.sql
├── 20240115100001_create_orders.sql
└── 20240220150000_add_user_email.sql
```
### Running Migrations
```rust
// At application startup
sqlx::migrate!("./migrations")
.run(&pool)
.await?;
```
### Migration Safety Rules
1. **Never modify an applied migration** — Create a new one instead
2. **Separate schema and data migrations** — Mixing DDL and DML in one migration increases lock contention and makes rollbacks operationally risky
3. **Make migrations idempotent where possible** — `IF NOT EXISTS`, `IF EXISTS`
4. **Destructive migrations need a plan** — `DROP COLUMN` and `DROP TABLE` should be preceded by a data migration that archives/moves data
### Reversible Migration Pattern
```sql
-- 20240220150000_add_user_email.sql
ALTER TABLE users ADD COLUMN IF NOT EXISTS email TEXT;
-- To reverse (keep as documentation or in a separate down file):
-- ALTER TABLE users DROP COLUMN IF EXISTS email;
```
## Review Questions
1. Is the pool shared across the application (not created per-request)?
2. Is pool size configured appropriately for the deployment?
3. Are transactions used for multi-statement writes?
4. Are transactions committed explicitly?
5. Are migrations safe (no data loss, idempotent, separate DDL/DML)?
6. Is `sqlx::migrate!()` called at startup?
7. (Edition 2024) Does static pool initialization use `std::sync::LazyLock` instead of `once_cell` or `lazy_static!`?
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: 119k | 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: 119k | 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 |