SQLx Standards
Connection Pool
use sqlx::postgres::PgPoolOptions;
let pool = PgPoolOptions::new()
.max_connections(5)
.connect("postgres://user:pass@localhost/db")
.await?;
// Or from environment
let pool = PgPool::connect(&std::env::var("DATABASE_URL")?).await?;
Compile-Time Checked Queries
// Requires DATABASE_URL at compile time
let user = sqlx::query_as!(
User,
"SELECT id, name, email FROM users WHERE id = $1",
user_id
)
.fetch_one(&pool)
.await?;
// Query with type override
let count = sqlx::query_scalar!(
r#"SELECT COUNT(*) as "count!" FROM users"#
)
.fetch_one(&pool)
.await?;
Runtime Queries
use sqlx::{query, query_as, FromRow};
#[derive(FromRow)]
struct User {
id: i64,
name: String,
email: String,
}
// Named struct mapping
let users: Vec<User> = query_as("SELECT * FROM users WHERE active = $1")
.bind(true)
.fetch_all(&pool)
.await?;
// Dynamic query
let user = query("SELECT * FROM users WHERE id = $1")
.bind(user_id)
.fetch_optional(&pool)
.await?;
Fetch Methods
| Method |
Returns |
Use Case |
fetch_one |
T |
Exactly one row expected |
fetch_optional |
Option<T> |
Zero or one row |
fetch_all |
Vec<T> |
All rows in memory |
fetch |
Stream<T> |
Large result sets |
Transactions
let mut tx = pool.begin().await?;
sqlx::query("INSERT INTO users (name) VALUES ($1)")
.bind(&user.name)
.execute(&mut *tx)
.await?;
sqlx::query("INSERT INTO audit_log (action) VALUES ($1)")
.bind("user_created")
.execute(&mut *tx)
.await?;
tx.commit().await?;
// Or automatic rollback on drop
Migrations
# Create migration
sqlx migrate add create_users_table
# Run migrations
sqlx migrate run
# Revert last migration
sqlx migrate revert
// Run embedded migrations at startup
sqlx::migrate!("./migrations")
.run(&pool)
.await?;
Type Mappings
| PostgreSQL |
Rust |
Notes |
BIGINT |
i64 |
|
INTEGER |
i32 |
|
TEXT/VARCHAR |
String |
|
BOOLEAN |
bool |
|
TIMESTAMP |
chrono::NaiveDateTime |
Requires chrono feature |
TIMESTAMPTZ |
chrono::DateTime<Utc> |
|
UUID |
uuid::Uuid |
Requires uuid feature |
JSONB |
serde_json::Value |
Requires json feature |
Best Practices
- Compile-time checks: Use
query! macros when possible
- Connection limits: Match pool size to Postgres
max_connections
- Prepared statements: sqlx caches automatically per connection
- Offline mode: Generate
sqlx-data.json for CI without database
- Nullable columns: Use
Option<T> for nullable, or override with "column!"
Converted and distributed by TomeVault — claim your Tome and manage your conversions.
tomevault-io