Database Integration (SQLx/Diesel)
Integrate your Rust web service with databases using popular ORMs or query builders like SQLx or Diesel.
Database Integration (SQLx/Diesel) is a free Learn Rust Coding lesson on CoddyKit — lesson 2 of 3. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Learn Rust Coding learning path, one of 3 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Why Databases for Web?
Web services often need to store and retrieve data persistently. Think about user profiles, product catalogs, or blog posts – this data needs to live somewhere beyond your program's memory.
Databases provide a structured way to manage this data, ensuring it's safe, consistent, and accessible. Integrating your Rust web service with a database is a core task for most real-world applications.
SQLx: Your Async DB Friend
In Rust, we have excellent tools for database interaction. Two popular choices are SQLx and Diesel.
- SQLx: An asynchronous, pure Rust SQL crate with compile-time checked queries. It's often preferred for async web services built with frameworks like Tokio.
- Diesel: A powerful ORM (Object-Relational Mapper) that provides a more abstract way to interact with your database, mapping Rust structs directly to database tables. It's typically synchronous.
For this lesson, we'll focus on SQLx due to its strong async support, which aligns well with modern Rust web service development.
SQLx Setup: Dependencies
Before writing code, you need to add sqlx and tokio to your project's Cargo.toml. tokio provides the async runtime needed by sqlx.
For example, to use SQLite (an easy file-based database for local development) with SQLx, your Cargo.toml might look like this:
[dependencies]
sqlx = { version = "0.7", features = ["runtime-tokio-rustls", "sqlite"] }
tokio = { version = "1", features = ["full"] }You'll also typically use a DATABASE_URL environment variable to configure your database connection string.
Connect to Your First DB
Let's establish a connection to an in-memory SQLite database. SQLx uses connection pools, which manage multiple database connections efficiently to handle concurrent requests in your web service.
Try running this example:
use sqlx::SqlitePool;
use tokio;
#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
// Use an in-memory SQLite database for simplicity
let database_url = "sqlite::memory:";
let pool = SqlitePool::connect(&database_url).await?;
println!("Successfully connected to the database!");
// In a real web app, 'pool' would be passed around or stored in app state.
Ok(())
}Structuring Data with Tables
Before we can store data, we need to define our database schema using DDL (Data Definition Language). Let's create a simple tasks table.
The sqlx::query! macro allows you to embed SQL directly into your Rust code. It even performs compile-time checks on your SQL!
use sqlx::{SqlitePool, query};
use tokio;
#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
let database_url = "sqlite::memory:";
let pool = SqlitePool::connect(&database_url).await?;
// Create a 'tasks' table if it doesn't exist
query!(
"CREATE TABLE IF NOT EXISTS tasks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
description TEXT NOT NULL,
completed BOOLEAN NOT NULL DEFAULT FALSE
)"
).execute(&pool).await?;
println!("Table 'tasks' created successfully!");
Ok(())
}Adding New Records
Now that we have a table, let's add some data! We'll use the INSERT statement with parameter binding. This protects against SQL injection and makes your queries safer.
Notice how ? placeholders are used for parameters, and values are passed to .bind().
use sqlx::{SqlitePool, query};
use tokio;
#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
let database_url = "sqlite::memory:";
let pool = SqlitePool::connect(&database_url).await?;
query!(
"CREATE TABLE IF NOT EXISTS tasks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
description TEXT NOT NULL,
completed BOOLEAN NOT NULL DEFAULT FALSE
)"
).execute(&pool).await?;
// Insert a new task
let description = "Learn SQLx";
let completed = false;
query!(
"INSERT INTO tasks (description, completed) VALUES (?, ?)",
description,
completed
).execute(&pool).await?;
println!("Task '{}' inserted successfully!", description);
Ok(())
}Retrieving Data with SELECT
To fetch data from our tasks table, we use the SELECT statement. SQLx provides methods like .fetch_all() or .fetch_one() to get results.
You can iterate over the returned rows and access columns by name or index.
use sqlx::{SqlitePool, query};
use tokio;
#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
let database_url = "sqlite::memory:";
let pool = SqlitePool::connect(&database_url).await?;
query!(
"CREATE TABLE IF NOT EXISTS tasks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
description TEXT NOT NULL,
completed BOOLEAN NOT NULL DEFAULT FALSE
)"
).execute(&pool).await?;
query!(
"INSERT INTO tasks (description, completed) VALUES (?, ?)",
"Learn SQLx", false
).execute(&pool).await?;
query!(
"INSERT INTO tasks (description, completed) VALUES (?, ?)",
"Build a web app", false
).execute(&pool).await?;
// Fetch and print all tasks
println!("\n--- All Tasks ---");
let rows = query!("SELECT id, description, completed FROM tasks")
.fetch_all(&pool).await?;
for row in rows {
println!("ID: {}, Desc: {}, Done: {}",
row.id, row.description, row.completed);
}
Ok(())
}Type-Safe Data with Structs
Manually extracting data from rows can be error-prone. SQLx's query_as! macro, combined with #[derive(FromRow)], lets you map query results directly into Rust structs!
This provides strong type safety and makes your code much cleaner and easier to work with.
use sqlx::{FromRow, SqlitePool, query, query_as};
use tokio;
#[derive(Debug, FromRow)]
struct Task {
id: i64,
description: String,
completed: bool,
}
#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
let database_url = "sqlite::memory:";
let pool = SqlitePool::connect(&database_url).await?;
query!(
"CREATE TABLE IF NOT EXISTS tasks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
description TEXT NOT NULL,
completed BOOLEAN NOT NULL DEFAULT FALSE
)"
).execute(&pool).await?;
query!(
"INSERT INTO tasks (description, completed) VALUES (?, ?)",
"Learn SQLx", false
).execute(&pool).await?;
// Fetch tasks directly into our Task struct
println!("\n--- Tasks as Structs ---");
let tasks = query_as::<_, Task>("SELECT id, description, completed FROM tasks")
.fetch_all(&pool).await?;
for task in tasks {
println!("{:?}", task);
}
Ok(())
}Modifying & Deleting Records
Updating and deleting data follows a similar pattern to inserting. You use UPDATE and DELETE SQL statements with parameter binding.
- UPDATE: Changes existing rows based on a
WHEREclause. - DELETE: Removes rows based on a
WHEREclause.
Always be careful with WHERE clauses in UPDATE and DELETE to avoid unintended data loss!
use sqlx::{SqlitePool, query};
use tokio;
#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
let database_url = "sqlite::memory:";
let pool = SqlitePool::connect(&database_url).await?;
query!(
"CREATE TABLE IF NOT EXISTS tasks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
description TEXT NOT NULL,
completed BOOLEAN NOT NULL DEFAULT FALSE
)"
).execute(&pool).await?;
query!(
"INSERT INTO tasks (description, completed) VALUES (?, ?)",
"Finish lesson", false
).execute(&pool).await?;
let task_id_to_update = 1;
// Update a task
query!(
"UPDATE tasks SET completed = ? WHERE id = ?",
true,
task_id_to_update
).execute(&pool).await?;
println!("Task {} updated.", task_id_to_update);
// Delete a task (uncomment to run, careful with IDs after update)
// let task_id_to_delete = 1;
// query!(
// "DELETE FROM tasks WHERE id = ?",
// task_id_to_delete
// ).execute(&pool).await?;
// println!("Task {} deleted.", task_id_to_delete);
Ok(())
}Test Your DB Skills
Which of the following statements are TRUE regarding SQLx in Rust web services?
DB Integration: Key Takeaways
You've taken a significant step in building robust Rust web services by learning database integration!
- Persistence: Databases are vital for storing application data.
- SQLx: An async, compile-time checked SQL crate, great for web services.
- Connection Pools: Essential for efficient and concurrent database access.
- CRUD: We covered creating (
INSERT), retrieving (SELECT), updating (UPDATE), and deleting (DELETE) data. - Type Safety:
query_as!and#[derive(FromRow)]enable safe and ergonomic data mapping.
Next, you can explore integrating these database operations into actual web routes!
Frequently asked questions
Is the “Database Integration (SQLx/Diesel)” lesson free?
Yes — the full text of “Database Integration (SQLx/Diesel)” is free to read here on the web, and the Learn Rust Coding course includes 3 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Learn Rust Coding course, upgrade to CoddyKit PRO.
What will I learn in “Database Integration (SQLx/Diesel)”?
Integrate your Rust web service with databases using popular ORMs or query builders like SQLx or Diesel. You practise Learn Rust Coding with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start Learn Rust Coding?
No prior experience is required. Learn Rust Coding on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 3, so you can start here or from the beginning and move at your own pace.
How long does the “Database Integration (SQLx/Diesel)” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this Learn Rust Coding lesson?
Yes. Every Learn Rust Coding lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- REST APIs with Actix-web/Rocket
- Database Integration (SQLx/Diesel)
- Authentication and Authorization