SQLx is an asynchronous library for working with real SQL from Rust without turning everything into an ORM.
The idea is quite appealing: we write normal SQL, but the query macros can check it at compile time against a real database or prepared metadata. If we make a mistake with a column, we can find out before deploying.
Adding SQLx to the project
For a simple example, we’ll use SQLite because we don’t need to spin up a separate server.
cargo add sqlx --features sqlite,runtime-tokio
cargo add tokio --features full
cargo add dotenvyWe also install the SQLx CLI to create the database and manage migrations:
cargo install sqlx-cli --no-default-features --features sqliteConfiguring DATABASE_URL
SQLx uses the DATABASE_URL environment variable to know which database to connect to.
We create a .env file:
DATABASE_URL=sqlite://mi_base.dbAnd then create the database:
sqlx database createMigrations
A migration is an SQL file that describes a change to the database.
sqlx migrate add crear_usuariosInside the generated file, we can put:
CREATE TABLE usuarios (
id INTEGER PRIMARY KEY AUTOINCREMENT,
nombre TEXT NOT NULL,
activo BOOLEAN NOT NULL DEFAULT 1
);We apply the migration:
sqlx migrate runCreating a connection pool
In web applications, we don’t open a new connection for each query. We use a pool.
use dotenvy::dotenv;
use sqlx::sqlite::SqlitePool;
use std::env;
#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
dotenv().ok();
let database_url = env::var("DATABASE_URL")
.expect("Missing DATABASE_URL");
let pool = SqlitePool::connect(&database_url).await?;
println!("Connected to the database");
Ok(())
}The pool maintains several available connections and reuses them when needed.
Inserting data
With query! we write literal SQL:
let nombre = "Ferris";
let resultado = sqlx::query!(
"INSERT INTO usuarios (nombre, activo) VALUES (?, ?)",
nombre,
true
)
.execute(&pool)
.await?;
println!("Rows affected: {}", resultado.rows_affected());The advantage of query! is that SQLx can check the query at compile time if it has access to the database or the prepared metadata.
Reading data
To map rows to a struct, we use query_as!.
struct Usuario {
id: i64,
nombre: String,
activo: bool,
}
let usuarios = sqlx::query_as!(
Usuario,
"SELECT id, nombre, activo FROM usuarios WHERE activo = ?",
true
)
.fetch_all(&pool)
.await?;
for usuario in usuarios {
println!("{}: {}", usuario.id, usuario.nombre);
}Here we’re still writing plain SQL, but Rust receives typed data.
Offline mode
There is an important detail. To check queries at compile time, SQLx needs to know the database schema.
Locally, it can connect using DATABASE_URL. In CI or deployment, that’s not always convenient.
For those cases, there is offline mode:
cargo sqlx prepareThis generates a .sqlx folder containing query metadata. We must include it in version control so the project can compile without having the database running, as long as those metadata files are up to date and DATABASE_URL does not force an online check.
In CI, it’s usually a good idea to run cargo sqlx prepare --check to verify that the .sqlx folder hasn’t become outdated.
Using it with Axum
The typical pattern is to store the pool in the application state:
use axum::{extract::State, http::StatusCode, response::IntoResponse, Json};
use serde::Deserialize;
use sqlx::sqlite::SqlitePool;
use std::sync::Arc;
struct AppState {
db: SqlitePool,
}
#[derive(Deserialize)]
struct CrearUsuario {
nombre: String,
}
async fn crear_usuario(
State(state): State<Arc<AppState>>,
Json(datos): Json<CrearUsuario>,
) -> impl IntoResponse {
let resultado = sqlx::query!(
"INSERT INTO usuarios (nombre) VALUES (?)",
datos.nombre
)
.execute(&state.db)
.await;
match resultado {
Ok(_) => StatusCode::CREATED,
Err(_) => StatusCode::INTERNAL_SERVER_ERROR,
}
}