Jump to content

Rust and SQL: A Match Made in Backend Heaven

From JOHNWICK

So you’re building a backend API. You need a database.

You want it fast, safe, and not a nightmare to maintain. Let me tell you about something that just works: Rust with SQL. The Problem with Traditional Approaches

Ever written code like this in JavaScript? const user = await db.query("SELECT * FROM users WHERE id = " + userId);

Looks innocent. But if userId comes from user input, you just opened the door to SQL injection. Oops.

Or maybe you’re using an ORM that hides SQL completely. It’s convenient until you need to optimize a query, and you have no idea what’s actually hitting your database.

What Rust Brings to the Table

Rust has libraries like SQLx and Diesel that give you something rare: type safety that works with your actual database schema. Not fake types that you hope match. Real compile-time checks against your real database.

Here’s SQLx in action: use sqlx::PgPool;

async fn get_user(pool: &PgPool, user_id: i32) -> Result<User, sqlx::Error> { 

   let user = sqlx::query_as!(
       User,
       "SELECT id, name, email FROM users WHERE id = $1",
       user_id
   )
   .fetch_one(pool)
   .await?;
   
   Ok(user)

}

struct User { 

   id: i32,
   name: String,
   email: String,

}

That $1 is a parameter placeholder. SQLx automatically prevents SQL injection. But here's the cool part: if you mistype a column name or get the type wrong, your code won't even compile.

SQLx checks your SQL against your actual database at compile time. Diesel Takes It Further

Diesel is different. It gives you a query builder that feels like writing SQL, but it’s all type-checked Rust: use diesel::prelude::*;

fn find_active_users(conn: &mut PgConnection) -> Vec<User> { 

   users::table
       .filter(users::active.eq(true))
       .filter(users::created_at.gt(one_week_ago))
       .order(users::name.asc())
       .load::<User>(conn)
       .expect("Error loading users")

}

No string queries. No magic. If the active column doesn't exist, the compiler tells you.

If you try to order by a column that isn't there, compilation fails. How It Actually Works Traditional Stack: ┌──────────────┐ │ Application │──→ String Query └──────────────┘ 

      ↓

┌──────────────┐ │ Database │──→ Runtime Error (maybe) └──────────────┘

Rust + SQLx/Diesel: ┌──────────────┐ │ Application │──→ Type-Checked Query └──────────────┘ 

      ↓

┌──────────────┐ │ Compiler │──→ Validates Against Schema └──────────────┘ 

      ↓

┌──────────────┐ │ Database │──→ Safe Execution └──────────────┘

Real World Example Let’s build something actual. A simple user service: use sqlx::{PgPool, FromRow};

  1. [derive(FromRow)]

struct User { 

   id: i32,
   email: String,
   username: String,

}

async fn create_user( 

   pool: &PgPool,
   email: &str,
   username: &str

) -> Result<User, sqlx::Error> { 

   sqlx::query_as!(
       User,
       "INSERT INTO users (email, username) VALUES ($1, $2) RETURNING id, email, username",
       email,
       username
   )
   .fetch_one(pool)
   .await

}

async fn get_user_by_email( 

   pool: &PgPool,
   email: &str

) -> Result<Option<User>, sqlx::Error> { 

   sqlx::query_as!(
       User,
       "SELECT id, email, username FROM users WHERE email = $1",
       email
   )
   .fetch_optional(pool)
   .await

}

This code is boring. That’s good. It does exactly what it says. No surprises. No hidden behavior. The Connection Pool Story

Database connections are expensive. You don’t want to create a new one for every request. Rust’s ownership system makes connection pools natural: use sqlx::postgres::PgPoolOptions;

async fn setup_database() -> PgPool { 

   PgPoolOptions::new()
       .max_connections(5)
       .connect("postgresql://user:pass@localhost/mydb")
       .await
       .expect("Failed to create pool")

}

The pool manages connections safely. When you borrow a connection, Rust ensures you can’t accidentally use it after it’s returned to the pool. No leaked connections. No race conditions.


What About Transactions?

Transactions in Rust are actually safer than most languages: async fn transfer_money( 

   pool: &PgPool,
   from_id: i32,
   to_id: i32,
   amount: i32

) -> Result<(), sqlx::Error> { 

   let mut tx = pool.begin().await?;
   
   sqlx::query!(
       "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
       amount,
       from_id
   )
   .execute(&mut *tx)
   .await?;
   
   sqlx::query!(
       "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
       amount,
       to_id
   )
   .execute(&mut *tx)
   .await?;
   
   tx.commit().await?;
   Ok(())

}

If anything fails, the transaction rolls back automatically.

The ? operator handles errors, and if you forget to commit, Rust's drop checker rolls back the transaction when it goes out of scope.

The Performance Angle

Rust doesn’t have garbage collection pauses. When you’re handling database connections and streaming results, this matters. Your response times stay consistent.

SQLx can stream large result sets without loading everything into memory: use futures::TryStreamExt;

async fn process_all_users(pool: &PgPool) -> Result<(), sqlx::Error> { 

   let mut stream = sqlx::query_as!(
       User,
       "SELECT id, email, username FROM users"
   )
   .fetch(pool);
   
   while let Some(user) = stream.try_next().await? {
       println!("Processing user: {}", user.username);
   }
   
   Ok(())

}

This processes millions of rows without eating all your RAM.

Why This Matters Building backends is hard enough. You don’t need mysterious crashes from null pointers. You don’t need SQL injection vulnerabilities. You don’t need queries that break in production because someone renamed a column. Rust with SQLx or Diesel gives you boring, reliable database code. The compiler catches mistakes before they ship.

The type system prevents entire categories of bugs. And it’s fast enough that you won’t need to rewrite it later. Is it more work upfront? Yes. Learning Rust takes time. But the alternative is debugging production issues at 3am because your ORM generated a bad query or your string concatenation created a security hole. The backend developers who switched to Rust aren’t going back. There’s a reason for that.

Retrieved from "https://johnwick.cc/index.php?title=Rust_and_SQL:_A_Match_Made_in_Backend_Heaven&oldid=66"