This skill provides patterns for database operations in Rust using SQLx (async, compile-time checked) or Diesel (sync, type-safe ORM). Use this when working with databases in Rust projects.
Quick Reference
Pattern
When to Use
Example
Query
Fetch data
sqlx::query_as!
Transaction
Multiple operations
pool.begin()
Migration
Schema changes
sqlx migrate run
Repository
Data access layer
UserRepository
SQLx vs Diesel
Feature
SQLx
Diesel
Async
Yes
関連 Skill
No (use tokio_diesel)
Compile-time checks
Yes (with macros)
Yes
ORM features
Minimal
Full ORM
Query style
SQL strings
DSL
Learning curve
Lower
Higher
Core Patterns (SQLx)
Pattern 1: Connection Pool
// ✅ CORRECT: Connection pool setup
use sqlx::postgres::PgPoolOptions;
use sqlx::PgPool;
use std::time::Duration;
pub async fn create_pool(database_url: &str) -> Result<PgPool, sqlx::Error> {
PgPoolOptions::new()
.max_connections(10)
.min_connections(2)
.acquire_timeout(Duration::from_secs(3))
.idle_timeout(Duration::from_secs(600))
.connect(database_url)
.await
}
// In main.rs
#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
dotenvy::dotenv().ok();
let database_url = std::env::var("DATABASE_URL")?;
let pool = create_pool(&database_url).await?;
// Run migrations
sqlx::migrate!("./migrations").run(&pool).await?;
// Start server with pool
Ok(())
}
// ✅ CORRECT: Repository with SQLx
use sqlx::PgPool;
pub struct UserRepository {
pool: PgPool,
}
impl UserRepository {
pub fn new(pool: PgPool) -> Self {
Self { pool }
}
pub async fn find_by_id(&self, id: i64) -> Result<Option<User>, sqlx::Error> {
sqlx::query_as!(
User,
r#"
SELECT id, email, name, password_hash, is_active, created_at, updated_at
FROM users
WHERE id = $1
"#,
id
)
.fetch_optional(&self.pool)
.await
}
pub async fn find_by_email(&self, email: &str) -> Result<Option<User>, sqlx::Error> {
sqlx::query_as!(
User,
r#"SELECT * FROM users WHERE email = $1"#,
email
)
.fetch_optional(&self.pool)
.await
}
pub async fn create(
&self,
email: &str,
name: &str,
password_hash: &str,
) -> Result<User, sqlx::Error> {
sqlx::query_as!(
User,
r#"
INSERT INTO users (email, name, password_hash)
VALUES ($1, $2, $3)
RETURNING *
"#,
email,
name,
password_hash
)
.fetch_one(&self.pool)
.await
}
pub async fn update(&self, id: i64, name: &str) -> Result<Option<User>, sqlx::Error> {
sqlx::query_as!(
User,
r#"
UPDATE users
SET name = $2, updated_at = NOW()
WHERE id = $1
RETURNING *
"#,
id,
name
)
.fetch_optional(&self.pool)
.await
}
pub async fn delete(&self, id: i64) -> Result<bool, sqlx::Error> {
let result = sqlx::query!(r#"DELETE FROM users WHERE id = $1"#, id)
.execute(&self.pool)
.await?;
Ok(result.rows_affected() > 0)
}
pub async fn exists_by_email(&self, email: &str) -> Result<bool, sqlx::Error> {
let result = sqlx::query!(
r#"SELECT EXISTS(SELECT 1 FROM users WHERE email = $1) as "exists!""#,
email
)
.fetch_one(&self.pool)
.await?;
Ok(result.exists)
}
}
Pattern 4: Transactions
// ✅ CORRECT: Transaction handling
use sqlx::{PgPool, Postgres, Transaction};
pub async fn transfer_funds(
pool: &PgPool,
from_id: i64,
to_id: i64,
amount: i64,
) -> Result<(), AppError> {
let mut tx: Transaction<'_, Postgres> = pool.begin().await?;
// Debit from source
let from_balance = sqlx::query_scalar!(
r#"
UPDATE accounts
SET balance = balance - $2
WHERE id = $1 AND balance >= $2
RETURNING balance
"#,
from_id,
amount
)
.fetch_optional(&mut *tx)
.await?
.ok_or(AppError::InsufficientFunds)?;
// Credit to destination
sqlx::query!(
r#"UPDATE accounts SET balance = balance + $2 WHERE id = $1"#,
to_id,
amount
)
.execute(&mut *tx)
.await?;
// Record transaction
sqlx::query!(
r#"
INSERT INTO transactions (from_id, to_id, amount)
VALUES ($1, $2, $3)
"#,
from_id,
to_id,
amount
)
.execute(&mut *tx)
.await?;
tx.commit().await?;
Ok(())
}
Pattern 5: Complex Queries
// ✅ CORRECT: Joins and aggregations
pub async fn get_posts_with_authors(
pool: &PgPool,
limit: i64,
) -> Result<Vec<PostWithAuthor>, sqlx::Error> {
sqlx::query_as!(
PostWithAuthor,
r#"
SELECT
p.id,
p.title,
p.content,
p.created_at,
u.id as author_id,
u.name as author_name
FROM posts p
JOIN users u ON p.author_id = u.id
WHERE p.published = true
ORDER BY p.created_at DESC
LIMIT $1
"#,
limit
)
.fetch_all(pool)
.await
}
pub async fn get_user_stats(pool: &PgPool, user_id: i64) -> Result<UserStats, sqlx::Error> {
sqlx::query_as!(
UserStats,
r#"
SELECT
COUNT(p.id) as "post_count!",
COALESCE(SUM(p.views), 0) as "total_views!",
MAX(p.created_at) as last_post_at
FROM posts p
WHERE p.author_id = $1
"#,
user_id
)
.fetch_one(pool)
.await
}
Pattern 6: Migrations
-- migrations/001_create_users.sql
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT true,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_users_email ON users(email);
-- migrations/002_create_posts.sql
CREATE TABLE posts (
id BIGSERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
author_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
published BOOLEAN NOT NULL DEFAULT false,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_posts_author ON posts(author_id);
// ❌ BAD: N+1 query problem
let users = sqlx::query_as!(User, "SELECT * FROM users")
.fetch_all(&pool).await?;
for user in &users {
let posts = sqlx::query_as!(Post, "SELECT * FROM posts WHERE author_id = $1", user.id)
.fetch_all(&pool).await?; // Query per user!
}
// ✅ GOOD: Single query with join
let users_with_posts = sqlx::query_as!(
UserWithPosts,
r#"
SELECT u.*, array_agg(p.*) as posts
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
GROUP BY u.id
"#
)
.fetch_all(&pool).await?;
Don't: String Interpolation
// ❌ BAD: SQL injection risk
let query = format!("SELECT * FROM users WHERE name = '{}'", name);
// ✅ GOOD: Parameterized queries