Working with Databases in Rust

# CHAPTER 25

Working with Databases in Rust #

1. Chapter Introduction #

2. Learning Objectives #

• Understand the SQLx ecosystem.

• Connect a Rust application to an SQLite database.

• Execute basic queries (Create tables, Insert data).

• Fetch data and map it directly to Rust Structs.

• Handle database connection pooling asynchronously.

3. Setting Up SQLx and SQLite #

1. Update Cargo.toml: We need the async runtime (Tokio) and SQLx.

[dependencies]
tokio = { version = "1", features = ["full"] }
sqlx = { version = "0.7", features = ["runtime-tokio", "sqlite"] }

4. Establishing a Connection Pool #

use sqlx::sqlite::SqlitePoolOptions;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    // Connect to a local SQLite file named "sqlite.db"
    // (Ensure you create this file or run `sqlite3 sqlite.db ""` in your terminal)
    let pool = SqlitePoolOptions::new()
        .max_connections(5)
        .connect("sqlite://sqlite.db").await?;

    println!("Connected to the database successfully!");
    
    Ok(())
}

5. Creating Tables and Inserting Data (Executing) #

// Inside main...

// 1. Create a Table
sqlx::query(
    "CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER NOT NULL
    )"
)
.execute(&pool)
.await?;

// 2. Insert Data
let new_name = "Alice";
let new_age = 28;

// We use '?' placeholders to prevent SQL Injection attacks!
// .bind() attaches variables safely to the placeholders.
sqlx::query("INSERT INTO users (name, age) VALUES (?, ?)")
    .bind(new_name)
    .bind(new_age)
    .execute(&pool)
    .await?;

println!("User inserted!");

6. Querying Data and Mapping to Structs #

// Derive FromRow allows SQLx to map the database columns to this struct automatically
#[derive(sqlx::FromRow, Debug)]
struct User {
    id: i64,
    name: String,
    age: i64,
}

// Inside main...
// Fetch all users
let users = sqlx::query_as::<_, User>("SELECT id, name, age FROM users")
    .fetch_all(&pool)
    .await?;

for user in users {
    println!("Found User: ID: {}, Name: {}, Age: {}", user.id, user.name, user.age);
}

7. Compile-Time SQL Verification (The SQLx Superpower) #

8. Mini Project: Student Database CLI #

9. Common Mistakes #

• String Interpolation for SQL: Never do this: sqlx::query(format!("SELECT * FROM users WHERE name = {}", name)). This allows SQL Injection attacks. Always use ? (or $1 for Postgres) and .bind().

• Forgetting .await: SQLx is fully asynchronous. Every query returns a Future. If you forget .await, the query will never hit the database.

10. Best Practices #

• Use Postgres for Production: While SQLite is great for learning, use PostgreSQL (sqlx = { features = ["postgres"] }) for massive production web servers.

• Connection Pools in Structs: When building a web server, store the pool inside your application state so every route can access the database safely.

11. Exercises #

1. 1. Set up a Cargo project with Tokio and SQLx.

1. 2. Write a script that connects to an SQLite database, creates a products table (id, name, price).

1. 3. Insert 3 products and fetch them, printing the results.

12. MCQs with Answers #

13. Interview Questions #

• Q: Explain how compile-time SQL verification works in SQLx and why it is a game-changer for database development.

• Q: What is SQL Injection, and how does .bind() prevent it?

14. Summary #

15. Next Chapter Recommendation #