Query builders and ORMs

Recipe	Crates	Categories
sqlx
diesel
SeaORM
toasty

sqlx

sqlx⮳ is a low-level, asynchronous SQL library for Rust. It supports various databases like PostgreSQL, MySQL, SQLite, and MSSQL, and both tokio and async-std async runtimes. It features compile-time checked queries without a DSL. SQLx is not an ORM.

use sqlx::SqlitePool;

// In Cargo.toml, add the following dependencies:
// sqlx = { version = "0.8", features = [ "runtime-tokio", "sqlite" ]

#[derive(sqlx::FromRow, Debug, PartialEq, Eq)]
struct User {
    id: i32,
    name: String,
    email: String,
}

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    // Create a connection pool
    let pool = SqlitePool::connect("sqlite::memory:").await?;

    // In SQL, queries can be separated into prepared (parameterized) or
    // unprepared (simple). Prepared queries have their query plan cached,
    // use a binary mode of communication (lower bandwidth and faster decoding),
    // and utilize parameters to avoid SQL injection. Unprepared queries are
    // simple and intended only for use where a prepared statement will not
    // work, such as various database commands (e.g., PRAGMA or SET or
    // BEGIN). In SQLx, a `&str` is treated as an unprepared query,
    // and a `Query` or `QueryAs` struct is treated as a prepared query.

    // Create the users table using a prepared, cached query
    sqlx::query(
        "
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT NOT NULL UNIQUE
        )",
    )
    .execute(&pool)
    .await?;

    // Insert a new user
    sqlx::query("INSERT INTO users (name, email) VALUES (?, ?)")
        .bind("John Doe")
        .bind("john@example.com")
        .execute(&pool)
        .await?;

    // Retrieve all users
    let users: Vec<User> =
        sqlx::query_as::<_, User>("SELECT id, name, email FROM users")
            .fetch_all(&pool)
            .await?;

    // Print the retrieved users
    for user in users {
        println!(
            "ID: {}, Name: {}, Email: {}",
            user.id, user.name, user.email
        );
    }

    Ok(())
}

SeaORM

Seaography GraphQL server⮳

Built on top of sqlx (see above). There is also a related sea-query crate that provides a query builder without full ORM functionality.

diesel

The diesel crate is a powerful ORM (object-relational mapper) and query builder for Rust. It allows you to interact with databases in a type-safe and efficient manner. ORMs help object-oriented programmers abstract the details of relational databases, and do not require writing raw SQL queries.

diesel supports PostgreSQL, MySQL, and SQLite. diesel has excellent performance and takes an approach of strict compile time guarantees. The main crate is synchronous only, but diesel-async provides an async connection implementation.

To create a new diesel project targeting sqlite, follow these steps:

• Add the necessary dependencies to your Cargo.toml file (update the versions as needed):

diesel = { version = "2.2.6", features = ["sqlite"] }
diesel_migrations = { version = "2.2.0", features = ["sqlite"] }
dotenvy = "0.15.7"
serde = { version = "1.0.217", features = ["derive"] }
serde_json = "1.0.134"

• Run the following commands in your terminal to create a .env file with the database URL: set up your schema.rs file and migrations.

echo DATABASE_URL=sqlite::memory: > .env
cargo install diesel_cli --no-default-features --features sqlite
diesel setup
diesel migration generate create_users

DATABASE_URL can be set to a file path, or sqlite::memory: for an in-memory database. For other databases, the URL format is protocol://user:password@host/database. For MySQL, the URL would be as follows:

echo DATABASE_URL=mysql://<username>:<password>@localhost/<database>  > .env

• Add the following SQL to the generated up.sql file:

-- Your SQL goes here
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

• Leave the down.sql file empty for simplicity.

-- This file should undo anything in `up.sql`

• Create a schema.rs file by running:

diesel print-schema > src/schema.rs

• Write the Rust code to interact with the database:

toasty

Toasty is an ORM for the Rust programming language that prioritizes ease-of-use. It supports both SQL datases as well as some NoSQL databases, including DynamoDB and Cassandra. Note that Toasty does not hide the database capabilities. Instead, Toasty exposes features based on the target database.

It is currently in active development and not yet published to crates.io. You can try using it directly from Github.

Using the example in the Toasty announcement blog, projects that use Toasty start by creating a schema file to define the application's data model.

model User {
  #[key]
  #[auto]
  id: Id,

  name: String,

  #[unique]
  email: String,

  todos: [Todo],

  moto: Option<String>,
}

model Todo {
  #[key]
  #[auto]
  id: Id,

  #[index]
  user_id: Id<User>,

  #[relation(key = user_id, references = id)]
  user: User,

  title: String,
}

Use the Toasty CLI tool to generate all necessary Rust code for working with this data model.

// Create a new user and give them some todos.
User::create()
  .name("John Doe")
  .email("john@example.com")
  .todo(Todo::create().title("Make pizza"))
  .todo(Todo::create().title("Finish Toasty"))
  .todo(Todo::create().title("Sleep"))
  .exec(&db)
  .await?;

// Load the user from the database
let user = User::find_by_email("john@example.com").get(&db).await?

// Load and iterate the user's todos
let mut todos = user.todos().all(&db).await.unwrap();

while let Some(todo) = todos.next().await {
  let todo = todo.unwrap();
  println!("{:#?}", todo);
}