2 AM, service crash due to “too many connections”
I still remember that night vividly — 2 AM, Slack blowing up, MySQL returning ERROR 1040: Too many connections. A Rust service running in production, every request opening a new MySQL connection and never closing it. 800 Tokio tasks, each holding an open connection. The database collapsed entirely.
After that incident, I refactored the entire database layer to use sqlx with a proper connection pool. This post documents that setup — enough for you to avoid making the same mistake.
Quick Start: Up and Running in 5 Minutes
1. Add dependencies to Cargo.toml
[dependencies]
sqlx = { version = "0.7", features = ["runtime-tokio-rustls", "mysql", "macros", "migrate"] }
tokio = { version = "1", features = ["full"] }
dotenvy = "0.15"
macros enables the query! macro — which validates SQL at compile time, not runtime. And migrate is needed for sqlx-cli to run migrations later.
2. Create a pool and run your first query
use sqlx::mysql::MySqlPoolOptions;
use sqlx::MySqlPool;
#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
dotenvy::dotenv().ok();
let database_url = std::env::var("DATABASE_URL")
.expect("DATABASE_URL must be set");
let pool: MySqlPool = MySqlPoolOptions::new()
.max_connections(20)
.connect(&database_url)
.await?;
let row: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM users")
.fetch_one(&pool)
.await?;
println!("Total users: {}", row.0);
Ok(())
}
The .env file:
DATABASE_URL=mysql://user:password@localhost:3306/mydb
cargo run
If it prints the user count, the connection is working. Next up is the more important part: pool sizing and type-safe queries.
Deep Dive: Connection Pooling and Async Queries
Why does pooling matter?
MySQL defaults to max_connections = 151. Each connection consumes RAM (~8MB) and requires a TLS handshake (~10–50ms). If every request opens a new connection, you’ll hit exactly the same incident I had at 2 AM.
sqlx’s pool works like this: pre-create N connections, reuse them. A request arrives → borrows a connection → returns it when done. No constant open/close overhead.
Proper pool configuration for production
use std::time::Duration;
use sqlx::mysql::MySqlPoolOptions;
async fn create_pool(database_url: &str) -> Result<MySqlPool, sqlx::Error> {
MySqlPoolOptions::new()
.max_connections(20) // Maximum total connections
.min_connections(5) // Keep 5 idle connections ready
.acquire_timeout(Duration::from_secs(3)) // Timeout waiting for a free connection
.idle_timeout(Duration::from_secs(600)) // Close connections idle for more than 10 minutes
.max_lifetime(Duration::from_secs(1800)) // Recycle connections after 30 minutes
.connect(database_url)
.await
}
From real-world experience: max_connections ≈ (number of CPU cores × 2) + number of disks. A 4-core VPS should set 10–15 — setting it higher doesn’t help because MySQL will still bottleneck on disk I/O.
Type-safe queries with the query! macro
This is what truly sets sqlx apart from other libraries. The query! macro connects directly to the database at compile time to verify the schema:
#[derive(Debug, sqlx::FromRow)]
struct User {
id: i64,
username: String,
email: String,
created_at: chrono::NaiveDateTime,
}
async fn get_user_by_id(
pool: &MySqlPool,
user_id: i64,
) -> Result<Option<User>, sqlx::Error> {
sqlx::query_as!(
User,
"SELECT id, username, email, created_at FROM users WHERE id = ?",
user_id
)
.fetch_optional(pool)
.await
}
Misspell a column name or use the wrong data type → the compiler catches it immediately, not at runtime. This is real type-safety, not the fake kind you get from an ORM.
You need to set DATABASE_URL at build time so sqlx can connect to the DB to verify the schema:
export DATABASE_URL=mysql://user:password@localhost:3306/mydb
cargo build
Common fetch methods
// Fetch exactly 1 row — errors if none or more than one exists
let user = sqlx::query_as!(User, "SELECT ...").fetch_one(pool).await?;
// Fetch 1 row if it exists, None otherwise
let user = sqlx::query_as!(User, "SELECT ...").fetch_optional(pool).await?;
// Fetch all rows
let users = sqlx::query_as!(User, "SELECT ...").fetch_all(pool).await?;
// Stream — process rows one at a time, saves RAM for large datasets
use futures::TryStreamExt;
let mut stream = sqlx::query_as!(User, "SELECT ...").fetch(pool);
while let Some(user) = stream.try_next().await? {
process_user(user).await;
}
Advanced: Safe Migrations with sqlx-cli
Install sqlx-cli
cargo install sqlx-cli --no-default-features --features rustls,mysql
Create and write migrations
sqlx migrate add create_users_table
# Creates: migrations/20240101000000_create_users_table.sql
-- migrations/20240101000000_create_users_table.sql
CREATE TABLE users (
id BIGINT NOT NULL AUTO_INCREMENT,
username VARCHAR(100) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_email (email)
);
# Run all pending migrations
sqlx migrate run
# Check migration status
sqlx migrate info
Auto-migrate on service startup
My approach for microservices — auto-migrate on start, no separate CI/CD step required:
#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
let pool = create_pool(&database_url).await?;
sqlx::migrate!("./migrations")
.run(&pool)
.await
.expect("Failed to run database migrations");
start_http_server(pool).await?;
Ok(())
}
sqlx records which migrations have run in a _sqlx_migrations table. Deploying multiple instances simultaneously isn’t a problem — there’s a distributed lock that handles it. Fully idempotent.
Proper transactions
async fn transfer_credits(
pool: &MySqlPool,
from_id: i64,
to_id: i64,
amount: i64,
) -> Result<(), sqlx::Error> {
let mut tx = pool.begin().await?;
sqlx::query!(
"UPDATE wallets SET balance = balance - ? WHERE user_id = ?",
amount, from_id
)
.execute(&mut *tx)
.await?;
sqlx::query!(
"UPDATE wallets SET balance = balance + ? WHERE user_id = ?",
amount, to_id
)
.execute(&mut *tx)
.await?;
tx.commit().await?
// If the function returns Err or tx is dropped before commit → automatic rollback
}
Production Tips from the Trenches
SQLX_OFFLINE for CI/CD without a database
# On your local machine — generate the cache
cargo sqlx prepare
# Commit it to git
git add .sqlx/
git commit -m "chore: update sqlx offline cache"
# In the CI pipeline
SQLX_OFFLINE=true cargo build
Monitor pool health
tokio::spawn({
let pool = pool.clone();
async move {
loop {
tokio::time::sleep(Duration::from_secs(30)).await;
log::info!(
"DB Pool — size: {}, idle: {}",
pool.size(),
pool.num_idle()
);
}
}
});
If idle = 0 consistently — the pool is saturated. Either increase max_connections or optimize your queries.
Handle pool errors gracefully
use sqlx::Error as SqlxError;
match get_user_by_id(&pool, user_id).await {
Ok(Some(user)) => handle_user(user),
Ok(None) => return Err(AppError::NotFound),
Err(SqlxError::PoolTimedOut) => {
log::error!("DB pool exhausted — increase max_connections or optimize queries");
return Err(AppError::ServiceUnavailable);
}
Err(e) => {
log::error!("DB error: {:?}", e);
return Err(AppError::Internal);
}
}
Always back up before migrating — non-negotiable
I once dealt with database corruption at 3 AM. It took nearly 4 hours to restore from the oldest backup I could find. Since then, backing up before every migration is a non-negotiable rule — even if you’re just adding a single column:
#!/bin/bash
# pre-migrate.sh
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
mysqldump -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" > "backup_${TIMESTAMP}.sql"
echo "Backup saved: backup_${TIMESTAMP}.sql"
sqlx migrate run
Efficient bulk inserts with QueryBuilder
async fn bulk_insert_users(
pool: &MySqlPool,
users: &[NewUser],
) -> Result<(), sqlx::Error> {
let mut builder = sqlx::QueryBuilder::new(
"INSERT INTO users (username, email) "
);
builder.push_values(users.iter(), |mut b, u| {
b.push_bind(&u.username).push_bind(&u.email);
});
builder.build().execute(pool).await?;
Ok(())
}
For 1,000 records, this approach is 50–100x faster than inserting one row at a time in a loop.
When to use sqlx vs. an ORM like Diesel or SeaORM?
- sqlx: Real SQL, compile-time type-safety, full control over queries, async-native. Ideal for high-performance microservices.
- Diesel: Full-featured ORM, sync (limited async support), compile-time schema checks in a different style. Great for complex applications that need a high level of abstraction.
- SeaORM: Async ORM, ActiveRecord-style API, less boilerplate than sqlx. A good fit when development speed matters more than fine-grained control.
I use sqlx for every service that needs to handle heavy load — queries are hand-optimized, with no hidden magic query generation causing surprises in production.
