The problem with hand-rolling database drivers
You have a Rust service humming along, handling requests and crunching numbers. Now you need to persist data. There is a MySQL database waiting, full of tables and rows. You could write a TCP client to speak the MySQL protocol by hand, parsing binary packets and managing handshakes. Or you could use a crate that already did the hard work and gives you type safety on top.
Writing a database driver from scratch is a rabbit hole. The MySQL protocol involves complex authentication sequences, capability negotiation, and binary encoding for every data type. Even if you get it working, you still need to handle connection pooling, retries, and error mapping. The ecosystem has solved this. The modern standard for MySQL in Rust is sqlx.
sqlx treats your database schema as part of your type system. It checks your SQL queries at compile time. If you typo a column name, change a column type in the database, or pass the wrong argument, the compiler rejects your code. You catch bugs before you run the binary. This is a massive shift from other languages where database errors often hide until runtime.
Why sqlx wins for MySQL
Rust has several database libraries. diesel offers an ORM with a query builder. sqlx offers raw SQL with compile-time verification. For MySQL, sqlx is the preferred choice for most teams because it balances safety with flexibility. You write SQL, which is the universal language of databases, but you get Rust's type guarantees.
sqlx uses macros to inspect your queries. The macro parses the SQL, extracts the column names and types, and generates code that matches them to Rust types. This means your code knows exactly what the database returns. You don't guess that a column is a string. The compiler enforces it.
This approach requires a small setup step. You need to run a command to cache the schema information. This is a one-time cost that pays off every time you compile. The community calls this "offline mode" preparation. It ensures your builds are fast and reproducible, even without a database connection.
Setting up the crate
Add sqlx to your Cargo.toml. You must enable the mysql feature. sqlx is modular; it does not bundle every database driver by default. You also need to pick an async runtime. The convention is runtime-tokio-rustls, which uses Tokio for async and Rustls for TLS. This keeps your dependencies pure Rust and avoids OpenSSL linking issues.
[dependencies]
sqlx = { version = "0.7", features = ["mysql", "runtime-tokio-rustls"] }
tokio = { version = "1", features = ["full"] }
Enable the feature or the crate won't compile. The compiler won't guess you want MySQL.
Convention aside: never hardcode credentials. Use an environment variable named DATABASE_URL. sqlx reads this variable automatically. Set it in your shell or a .env file. This keeps secrets out of your source code and makes it easy to switch between development and production databases.
The connection pool pattern
Database connections are expensive. Opening a new TCP connection for every query kills performance. You need a connection pool. A pool maintains a set of open connections and reuses them. When your code needs to run a query, it borrows a connection from the pool, runs the query, and returns the connection.
sqlx provides MySqlPool. You create the pool once, usually at startup, and share it across your application. The pool handles connection limits, idle timeouts, and health checks. You don't manage connections manually.
use sqlx::MySql;
/// Connects to the database and returns a pool.
/// Reads the connection string from DATABASE_URL.
async fn create_pool() -> Result<sqlx::MySqlPool, sqlx::Error> {
// std::env::var returns a Result.
// ? propagates the error to the caller.
let database_url = std::env::var("DATABASE_URL")
.expect("DATABASE_URL must be set");
// MySqlPool::connect creates a pool with default settings.
// It opens a few connections immediately.
let pool = sqlx::MySqlPool::connect(&database_url).await?;
Ok(pool)
}
Let the pool manage connections. You focus on the data.
You can tune the pool using MySqlPoolOptions. This is useful for high-traffic services. You can set the maximum number of connections, the minimum idle connections, and the timeout for acquiring a connection.
use sqlx::MySqlPool;
/// Creates a tuned pool for production workloads.
async fn create_production_pool(url: &str) -> Result<MySqlPool, sqlx::Error> {
let pool = MySqlPool::connect_with(url)
.max_connections(10)
.min_connections(2)
.acquire_timeout(std::time::Duration::from_secs(5))
.connect()
.await?;
Ok(pool)
}
Adjust the limits based on your database capacity. Too many connections overwhelm the server. Too few bottleneck your application.
Mapping rows to structs
Real applications map database rows to Rust structs. sqlx provides the FromRow derive macro. You annotate your struct with #[derive(FromRow)] and define fields that match the column names. The macro generates the code to extract values from a row.
use sqlx::{MySql, FromRow};
/// Represents a user record from the database.
#[derive(Debug, FromRow)]
struct User {
id: i64,
name: String,
email: String,
// Fields must match column names exactly.
// sqlx is case-sensitive for column names.
}
Use query_as! to map results to your struct. The macro checks that the SQL query returns columns matching the struct fields. If the schema changes, the compiler catches the mismatch.
use sqlx::{MySqlPool, query_as};
/// Fetches active users from the database.
async fn get_active_users(pool: &MySqlPool) -> Result<Vec<User>, sqlx::Error> {
// query_as! takes the struct type and the SQL.
// It verifies types at compile time.
let users = query_as!(User, "SELECT id, name, email FROM users WHERE active = true")
.fetch_all(pool)
.await?;
Ok(users)
}
Run cargo sqlx prepare before building. This command connects to the database, inspects the schema, and caches the query metadata. It allows sqlx to verify queries offline. If you skip this, sqlx falls back to runtime checking, which defeats the purpose of compile-time safety.
Convention aside: add cargo sqlx prepare to your CI pipeline. This ensures that schema drift is caught before code reaches production. It also speeds up builds by avoiding database lookups during compilation.
Streaming results
fetch_all loads all rows into memory at once. This works for small result sets. If you query a table with millions of rows, your application runs out of memory. Use fetch to get a stream of rows. Process rows one by one as they arrive.
use sqlx::{MySqlPool, query_as};
/// Streams users and prints them without loading all into memory.
async fn stream_users(pool: &MySqlPool) -> Result<(), sqlx::Error> {
// fetch returns a Stream.
// You iterate over it with a for loop.
let mut stream = query_as!(User, "SELECT id, name, email FROM users")
.fetch(pool);
while let Some(user) = stream.next().await {
let user = user?;
println!("User: {} ({})", user.name, user.email);
}
Ok(())
}
Stream large result sets. Memory usage stays constant regardless of row count.
Pitfalls and compiler checks
sqlx catches many errors at compile time, but runtime pitfalls remain. The most common issue is using fetch_one when the query might return no rows. fetch_one expects exactly one row. If the table is empty, it returns an error. Use fetch_optional when the row might not exist.
use sqlx::{MySqlPool, query_as};
/// Fetches a user by ID, handling the case where the user doesn't exist.
async fn get_user_by_id(pool: &MySqlPool, id: i64) -> Result<Option<User>, sqlx::Error> {
// fetch_optional returns None if no rows match.
// It returns Some(row) if exactly one row matches.
// It errors if multiple rows match.
let user = query_as!(User, "SELECT id, name, email FROM users WHERE id = $1", id)
.fetch_optional(pool)
.await?;
Ok(user)
}
Check your connection string format. MySQL URLs follow the pattern mysql://user:pass@host/dbname. A typo in the host or port causes a connection error. The compiler cannot verify network addresses. You get a runtime error if the database is unreachable.
Type mismatches trigger compiler errors. If you map a BIGINT column to an i32 field, sqlx rejects the code. MySQL BIGINT maps to i64 in Rust. The compiler rejects this with a type mismatch error if the SQL result doesn't align with your struct. Fix the field type to match the database.
Convention aside: use sqlx::types::chrono::NaiveDateTime for timestamp columns. sqlx supports chrono types out of the box. This avoids manual parsing of date strings. Add the chrono feature to sqlx in Cargo.toml.
Decision: sqlx vs alternatives
Use sqlx when you write SQL by hand and want the compiler to verify your queries against the schema. Use diesel when you prefer building queries in Rust code with a type-safe DSL and want an ORM-like experience with migrations built in. Use a raw driver like mysql_async only when you need to bypass higher-level abstractions for protocol-level tuning, which is almost never necessary.
Trust the borrow checker. It usually has a point.