Skip to content

Database Integration

Ultimo provides first-class support for database integration with both SQLx and Diesel ORMs.

Overview

Choose the ORM that fits your development style:

  • SQLx - Async-first, raw SQL with compile-time verification
  • Diesel - Type-safe query builder with powerful DSL

Both integrations include:

  • ✅ Connection pooling for optimal performance
  • ✅ Context integration (ctx.sqlx() or ctx.diesel())
  • ✅ Multiple database support (PostgreSQL, MySQL, SQLite)
  • ✅ Transaction support
  • ✅ Health checks

Quick Comparison

FeatureSQLxDiesel
Execution ModelFully AsyncSync (with async wrapper)
Query StyleRaw SQLType-safe DSL
Compile-time checks✅ Yes (optional)✅ Yes (always)
MigrationsBuilt-in CLIdiesel_cli
PerformanceHigh (async I/O)High (efficient queries)
Learning CurveLowerModerate
Best ForAPI servers, microservicesComplex queries, type safety

Which to Choose?

Choose SQLx if you:

  • ✅ Prefer async/await throughout your application
  • ✅ Are comfortable writing raw SQL
  • ✅ Want simpler setup and configuration
  • ✅ Need quick prototyping
  • ✅ Value flexibility in query construction

Choose Diesel if you:

  • ✅ Want strong compile-time guarantees for all queries
  • ✅ Prefer a type-safe query builder DSL
  • ✅ Need complex joins and aggregations
  • ✅ Want auto-generated schema from migrations
  • ✅ Value explicit type safety over flexibility

Quick Start

SQLx Example

use ultimo::prelude::*;
use ultimo::database::sqlx::SqlxPool;
use serde::Serialize;
use sqlx::FromRow;
 
#[derive(Serialize, FromRow)]
struct User {
    id: i32,
    name: String,
    email: String,
}
 
#[tokio::main]
async fn main() -> ultimo::Result<()> {
    let mut app = Ultimo::new();
 
    let pool = SqlxPool::connect("postgres://localhost/mydb").await?;
    app.with_sqlx(pool);
 
    app.get("/users", |ctx: Context| async move {
        let db = ctx.sqlx::<sqlx::Postgres>()?;
        let users = sqlx::query_as::<_, User>("SELECT id, name, email FROM users")
            .fetch_all(db)
            .await?;
        ctx.json(users).await
    });
 
    app.listen("127.0.0.1:3000").await
}

Learn more about SQLx →

Diesel Example

use ultimo::prelude::*;
use ultimo::database::diesel::DieselPool;
use diesel::prelude::*;
use serde::Serialize;
 
// Define schema
mod schema {
    diesel::table! {
        users (id) {
            id -> Int4,
            name -> Varchar,
            email -> Varchar,
        }
    }
}
 
// Define model
#[derive(Serialize, Queryable, Selectable)]
#[diesel(table_name = schema::users)]
struct User {
    id: i32,
    name: String,
    email: String,
}
 
#[tokio::main]
async fn main() -> ultimo::Result<()> {
    let mut app = Ultimo::new();
 
    let pool = DieselPool::<PgConnection>::new("postgres://localhost/mydb")?;
    app.with_diesel(pool);
 
    app.get("/users", |ctx: Context| async move {
        let mut conn = ctx.diesel::<PgConnection>()?;
        let users = schema::users::table
            .select(User::as_select())
            .load(&mut *conn)?;
        ctx.json(users).await
    });
 
    app.listen("127.0.0.1:3000").await
}

Learn more about Diesel →

Common Patterns

Connection Pooling

Both ORMs include connection pooling for optimal performance:

// SQLx - Configure pool
let pool = SqlxPool::connect_with_options(
    PgPoolOptions::new()
        .max_connections(10)
        .min_connections(2),
    &database_url
).await?;
 
// Diesel - Configure pool
let pool = r2d2::Pool::builder()
    .max_size(10)
    .min_idle(Some(2))
    .build(manager)?;

Transactions

Execute multiple operations atomically:

// SQLx
let mut tx = db.begin().await?;
sqlx::query("UPDATE accounts SET balance = balance - $1 WHERE id = $2")
    .bind(amount).bind(from).execute(&mut *tx).await?;
sqlx::query("UPDATE accounts SET balance = balance + $1 WHERE id = $2")
    .bind(amount).bind(to).execute(&mut *tx).await?;
tx.commit().await?;
 
// Diesel
conn.transaction::<_, Error, _>(|conn| {
    diesel::update(accounts.find(from))
        .set(balance.eq(balance - amount)).execute(conn)?;
    diesel::update(accounts.find(to))
        .set(balance.eq(balance + amount)).execute(conn)?;
    Ok(())
})?;

Error Handling

Convert database errors to user-friendly responses:

// Handle unique constraint violations
.map_err(|e| {
    if e.to_string().contains("unique") {
        UltimoError::BadRequest("Email already exists".to_string())
    } else {
        UltimoError::Internal(format!("Database error: {}", e))
    }
})

Health Checks

Add database health check endpoints:

app.get("/health", |ctx: Context| async move {
    let db = ctx.sqlx::<sqlx::Postgres>()?;
    sqlx::query("SELECT 1").execute(db).await?;
    ctx.json(json!({"status": "healthy", "database": "connected"})).await
});

Migration Management

SQLx Migrations

# Create migration
sqlx migrate add create_users
 
# Run migrations
sqlx migrate run

Diesel Migrations

# Create migration
diesel migration generate create_users
 
# Run migrations
diesel migration run

Testing

Both ORMs support database testing:

#[tokio::test]
#[ignore] // Requires test database
async fn test_create_user() {
    let pool = setup_test_db().await;
    // Test database operations
}

Run tests with:

DATABASE_URL=postgres://localhost/test_db cargo test -- --ignored

Examples

Explore working examples in the repository:

  • examples/database-sqlx/ - Complete SQLx integration
  • examples/database-diesel/ - Complete Diesel integration
  • examples/database-api-styles/ - Comparing REST vs RPC with databases
  • examples/database-with-openapi/ - Database + OpenAPI integration

Next Steps

Getting Help