Skip to content

Database with SQLx

Prisma/TypeORM vs SQLx

In Node.js you likely use Prisma or TypeORM — ORMs that generate queries from a schema or model decorators. SQLx takes a different approach: you write raw SQL but the compiler verifies your queries against a live database at compile time using the sqlx::query! macro family. No ORM, no query builder — just type-checked SQL.

Feature	Prisma/TypeORM	SQLx
Query style	ORM API / query builder	Raw SQL
Type safety	Generated types	Compile-time macro verification
Migrations	Built-in CLI	`sqlx migrate` CLI
Connection pool	Managed internally	`PgPool` (explicit)
Async runtime	Node event loop	Tokio

Setting up the pool

[dependencies]
sqlx = { version = "0.7", features = ["postgres", "runtime-tokio-rustls", "uuid", "chrono"] }

// Node — Prisma client setup
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();
export default prisma;

// Rust — SQLx PgPool setup
use sqlx::PgPool;
use std::env;

pub async fn create_pool() -> PgPool {
    let url = env::var("DATABASE_URL")
        .expect("DATABASE_URL must be set");
    PgPool::connect(&url)
        .await
        .expect("Failed to connect to database")
}

Queries with `query_as!`

sqlx::query_as! maps a SQL result row directly to a Rust struct. Column names must match field names. If the schema changes and a column disappears, the code fails to compile.

// Prisma
const users = await prisma.user.findMany({
  where: { active: true },
  orderBy: { createdAt: 'desc' },
});

const user = await prisma.user.findUnique({
  where: { id },
});

use sqlx::PgPool;
use uuid::Uuid;

#[derive(sqlx::FromRow, serde::Serialize)]
struct User {
    id: Uuid,
    name: String,
    email: String,
    active: bool,
}

// List query
async fn list_users(pool: &PgPool) -> sqlx::Result<Vec<User>> {
    sqlx::query_as!(
        User,
        "SELECT id, name, email, active FROM users WHERE active = true ORDER BY id"
    )
    .fetch_all(pool)
    .await
}

// Get one
async fn find_user(pool: &PgPool, id: Uuid) -> sqlx::Result<Option<User>> {
    sqlx::query_as!(
        User,
        "SELECT id, name, email, active FROM users WHERE id = $1",
        id
    )
    .fetch_optional(pool)
    .await
}

Insert and delete

use sqlx::PgPool;
use uuid::Uuid;

async fn create_user(
    pool: &PgPool,
    name: &str,
    email: &str,
) -> sqlx::Result<User> {
    sqlx::query_as!(
        User,
        "INSERT INTO users (id, name, email, active)
         VALUES ($1, $2, $3, true)
         RETURNING id, name, email, active",
        Uuid::new_v4(),
        name,
        email
    )
    .fetch_one(pool)
    .await
}

async fn delete_user(pool: &PgPool, id: Uuid) -> sqlx::Result<bool> {
    let result = sqlx::query!(
        "DELETE FROM users WHERE id = $1",
        id
    )
    .execute(pool)
    .await?;

    Ok(result.rows_affected() > 0)
}

Wiring the pool into Axum State

use axum::{extract::State, routing::get, Json, Router};
use sqlx::PgPool;
use std::sync::Arc;

#[derive(Clone)]
struct AppState { db: PgPool }

async fn list_users_handler(
    State(state): State<Arc<AppState>>,
) -> Result<Json<Vec<User>>, AppError> {
    let users = list_users(&state.db).await.map_err(AppError::Database)?;
    Ok(Json(users))
}

#[tokio::main]
async fn main() {
    let pool = create_pool().await;
    let state = Arc::new(AppState { db: pool });

    let app = Router::new()
        .route("/users", get(list_users_handler))
        .with_state(state);

    let listener = tokio::net::TcpListener::bind("0.0.0.0:3000").await.unwrap();
    axum::serve(listener, app).await.unwrap();
}

Run locally — SQLx requires a live PostgreSQL database and the DATABASE_URL environment variable. Run sqlx migrate run to apply migrations, then cargo run.