SQLxSQLx is an asynchronous, pure Rust SQL toolkit that provides compile-time checked queries and migrations. It aims to be an ORM-less solution, focusing on direct SQL interaction while providing strong type safety and reducing the likelihood of runtime SQL errors. SQLx supports a variety of databases, including PostgreSQL, MySQL, SQLite, and Microsoft SQL Server.
Key features and concepts of SQLx:
1. Compile-Time Query Checking: This is SQLx's most powerful feature. Using `sqlx::query!` and `sqlx::query_as!` macros, SQLx connects to a database at compile time (or uses cached metadata) to validate your SQL queries against the actual database schema. This means typos, incorrect column names, or mismatched parameter types are caught during compilation, not at runtime, significantly improving reliability.
2. Asynchronous by Design: Built on Rust's `async`/`await` primitives, SQLx integrates seamlessly with async runtimes like Tokio, allowing for high-performance, non-blocking database operations.
3. ORM-Less Philosophy: Unlike many database libraries that introduce an Object-Relational Mapper, SQLx encourages writing raw SQL. It provides powerful tools for mapping query results directly into Rust structs using the `#[derive(sqlx::FromRow)]` macro, giving developers full control over their queries without an abstraction layer.
4. Type Safety: SQLx automatically infers and checks the types of query parameters and result columns, ensuring that data is correctly handled between Rust and the database. It handles type conversions and provides traits for custom type implementations.
5. Connection Pooling: SQLx includes a robust connection pool (e.g., `SqlitePool`, `PgPool`) to efficiently manage database connections, reducing overhead and improving application performance.
6. Migrations: It offers built-in support for database migrations, allowing you to manage schema changes version by version. This helps in maintaining consistent database schemas across different environments.
7. Macros for Query Construction: Macros like `sqlx::query!`, `sqlx::query_as!`, `sqlx::query_scalar!`, and `sqlx::query_file!` simplify query construction and enable the compile-time checks.
SQLx is an excellent choice for Rust applications that require direct SQL control, high performance, and robust type safety with the added benefit of catching common SQL errors at compile time.
Example Code
```rust
use sqlx::sqlite::{SqlitePool, SqlitePoolOptions};
use sqlx::FromRow;
use std::time::Duration;
// Derive FromRow to easily map query results to this struct
#[derive(Debug, FromRow, PartialEq, Eq)]
struct User {
id: i64,
name: String,
email: String,
}
#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
// Create a connection pool to an in-memory SQLite database.
// In a real application, you'd use a file path or an actual database URL.
let pool = SqlitePoolOptions::new()
.max_connections(5)
.acquire_timeout(Duration::from_secs(3))
.connect("sqlite::memory:").await?;
println!("Connected to SQLite in-memory database.");
// 1. Create a table
sqlx::query!(
r#"
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
)
"#
)
.execute(&pool)
.await?;
println!("Table 'users' created or already exists.");
// 2. Insert data
let user_name = "Alice";
let user_email = "alice@example.com";
let result = sqlx::query!(
"INSERT INTO users (name, email) VALUES (?, ?)",
user_name,
user_email
)
.execute(&pool)
.await?;
println!("Inserted {} row(s). Last ID: {}", result.rows_affected(), result.last_insert_rowid());
// Try to insert another user
let user_name2 = "Bob";
let user_email2 = "bob@example.com";
sqlx::query!(
"INSERT INTO users (name, email) VALUES (?, ?)",
user_name2,
user_email2
)
.execute(&pool)
.await?;
println!("Inserted another user: {}, {}.", user_name2, user_email2);
// 3. Query data and map to a struct
println!("\nFetching all users:");
let users = sqlx::query_as::<_, User>("SELECT id, name, email FROM users")
.fetch_all(&pool)
.await?;
for user in users {
println!("User ID: {}, Name: {}, Email: {}", user.id, user.name, user.email);
}
// 4. Query a single user by ID
println!("\nFetching user with ID 1:");
let fetched_user = sqlx::query_as::<_, User>("SELECT id, name, email FROM users WHERE id = ?")
.bind(1) // Bind the parameter
.fetch_one(&pool)
.await?;
println!("Fetched user: {:#?}", fetched_user);
assert_eq!(fetched_user, User { id: 1, name: "Alice".to_string(), email: "alice@example.com".to_string() });
// 5. Update data
println!("\nUpdating user Alice's email...");
let new_email = "alice.updated@example.com";
let update_result = sqlx::query!(
"UPDATE users SET email = ? WHERE name = ?",
new_email,
"Alice"
)
.execute(&pool)
.await?;
println!("Updated {} row(s).", update_result.rows_affected());
// Verify update
let updated_user = sqlx::query_as::<_, User>("SELECT id, name, email FROM users WHERE name = ?")
.bind("Alice")
.fetch_one(&pool)
.await?;
println!("User after update: {:#?}", updated_user);
assert_eq!(updated_user.email, new_email.to_string());
// 6. Delete data
println!("\nDeleting user Bob...");
let delete_result = sqlx::query!("DELETE FROM users WHERE name = ?", "Bob")
.execute(&pool)
.await?;
println!("Deleted {} row(s).", delete_result.rows_affected());
let remaining_users = sqlx::query_as::<_, User>("SELECT id, name, email FROM users")
.fetch_all(&pool)
.await?;
println!("\nUsers after deletion: {:#?}", remaining_users);
Ok(())
}
// To run this example, add the following to your Cargo.toml:
/*
[dependencies]
tokio = { version = "1", features = ["full"] }
sqlx = {
version = "0.7",
features = ["runtime-tokio-rustls", "sqlite", "macros", "time", "chrono"]
}
*/
```