SQLx vs Diesel vs SeaORM: Choosing a Database Layer

25 min read

By now you have a database in Node and three or four ways to talk to it: a raw driver (pg), a query builder (Knex), and an ORM or two (Prisma, TypeORM, Sequelize). Rust’s database story has the same shape, but the three leading choices stake out genuinely different positions on a single axis: how much does the compiler check, and how much does it write for you? This page compares SQLx, Diesel, and SeaORM side by side so you can pick deliberately instead of by reflex.

Quick Overview

The three crates answer “what is the right abstraction for SQL?” differently:

SQLx — you write raw SQL strings, and (with its macros) the compiler checks them against your real database schema at build time. Async. Not an ORM.
Diesel — a synchronous ORM and typed query builder. You build queries through a strongly typed DSL that the compiler validates; there is no async by default.
SeaORM — an async, ActiveRecord-style ORM built on top of SQLx. It gives you entities, an ActiveModel write pattern, relations, and a dynamic query builder, with async/await throughout.

For a TypeScript developer the rough map is: SQLx is “Knex, but the compiler checks your SQL”, Diesel is “TypeORM, but synchronous and statically typed end-to-end”, and SeaORM is “TypeORM/Prisma ergonomics that stay async.” This page builds a shared example across all three and gives you a decision framework.

Note: Every Rust snippet here was compiled and run with cargo 1.96.0 (current stable; 2024 edition). Versions used: SQLx 0.8, Diesel 2.3, SeaORM 1.1 (current at the time of writing). All examples use SQLite (in-memory or bundled) so they reproduce with no database server installed. The deep dives for each crate live in the sibling pages linked throughout; this page is purely the comparison.

TypeScript/JavaScript Example

In Node you already make this same choice. Here are the three positions you are choosing between, in TypeScript:

1
// 1) Raw-ish SQL with a query builder (Knex) — flexible, unchecked.
2
import knex from "knex";
3
const db = knex({ client: "pg", connection: process.env.DATABASE_URL });
4

5
const activeKnex = await db.raw(
6
  "SELECT id, name, email, active FROM users WHERE active = ? ORDER BY id",
7
  [true],
8
); // rows: any[] — the shape is a promise to yourself
9

10
// 2) An ORM with decorated entities (TypeORM) — ergonomic, runtime-checked.
11
import { DataSource } from "typeorm";
12
const ds = new DataSource({ type: "postgres", url: process.env.DATABASE_URL, entities: [User] });
13
await ds.initialize();
14
const activeTypeOrm = await ds.getRepository(User).findBy({ active: true });
15

16
// 3) A schema-first ORM (Prisma) — generated client, partial type-safety.
17
import { PrismaClient } from "@prisma/client";
18
const prisma = new PrismaClient();
19
const activePrisma = await prisma.user.findMany({
20
  where: { active: true },
21
  orderBy: { id: "asc" },
22
});

All three are async (everything returns a Promise), and the safety they give you ranges from none (the Knex raw cast) to partial (Prisma generates types from schema.prisma, but a hand-written $queryRaw is unchecked again). None of them validates your SQL against the actual database before the program runs. That last property is exactly what Rust’s options compete on.

Rust Equivalent

The same query — find active users, ordered by id — written three ways. Each is a complete, compile-verified program against an in-memory SQLite database.

SQLx: raw SQL, typed result

1
[dependencies]
2
sqlx = { version = "0.8", features = ["runtime-tokio-rustls", "sqlite"] }
3
tokio = { version = "1", features = ["full"] }

1
use sqlx::sqlite::SqlitePoolOptions;
2
use sqlx::FromRow;
3

4
#[derive(Debug, FromRow)]
5
struct User {
6
    id: i64,
7
    name: String,
8
    email: String,
9
    active: bool,
10
}
11

12
#[tokio::main]
13
async fn main() -> Result<(), sqlx::Error> {
14
    let pool = SqlitePoolOptions::new().connect("sqlite::memory:").await?;
15
    sqlx::query(
16
        "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, \
17
         email TEXT NOT NULL, active BOOLEAN NOT NULL)",
18
    )
19
    .execute(&pool)
20
    .await?;
21
    for (n, e, a) in [("Ada", "ada@x.com", true), ("Alan", "alan@x.com", false)] {
22
        sqlx::query("INSERT INTO users (name, email, active) VALUES (?, ?, ?)")
23
            .bind(n)
24
            .bind(e)
25
            .bind(a)
26
            .execute(&pool)
27
            .await?;
28
    }
29

30
    // The query is a SQL STRING. With the query!/query_as! macros it is also
31
    // checked against the live schema at compile time (see sqlx-queries.md).
32
    let active: Vec<User> = sqlx::query_as::<_, User>(
33
        "SELECT id, name, email, active FROM users WHERE active = ? ORDER BY id",
34
    )
35
    .bind(true)
36
    .fetch_all(&pool)
37
    .await?;
38

39
    println!(
40
        "{} active: {:?}",
41
        active.len(),
42
        active.iter().map(|u| &u.name).collect::<Vec<_>>()
43
    );
44
    Ok(())
45
}

Real output:

1
1 active: ["Ada"]

Diesel: synchronous typed DSL

1
[dependencies]
2
diesel = { version = "2.3", features = ["sqlite", "returning_clauses_for_sqlite_3_35"] }
3
libsqlite3-sys = { version = "0.37", features = ["bundled"] }

1
use diesel::prelude::*;
2
use diesel::sqlite::SqliteConnection;
3

4
// Normally generated by `diesel print-schema` into src/schema.rs.
5
diesel::table! {
6
    users (id) { id -> Integer, name -> Text, email -> Text, active -> Bool, }
7
}
8

9
#[derive(Queryable, Selectable, Debug)]
10
#[diesel(table_name = users)]
11
#[diesel(check_for_backend(diesel::sqlite::Sqlite))]
12
struct User {
13
    id: i32,
14
    name: String,
15
    email: String,
16
    active: bool,
17
}
18

19
fn main() -> Result<(), Box<dyn std::error::Error>> {
20
    let mut conn = SqliteConnection::establish(":memory:")?;
21
    diesel::sql_query(
22
        "CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, \
23
         email TEXT NOT NULL, active BOOLEAN NOT NULL)",
24
    )
25
    .execute(&mut conn)?;
26
    diesel::sql_query(
27
        "INSERT INTO users (name, email, active) \
28
         VALUES ('Ada','ada@x.com',1),('Alan','alan@x.com',0)",
29
    )
30
    .execute(&mut conn)?;
31

32
    // No SQL string: the query is built from typed column objects. A typo
33
    // like `users::activ` simply does not compile.
34
    let active: Vec<User> = users::table
35
        .filter(users::active.eq(true))
36
        .order(users::id.asc())
37
        .select(User::as_select())
38
        .load(&mut conn)?; // blocking — no .await
39

40
    println!(
41
        "{} active: {:?}",
42
        active.len(),
43
        active.iter().map(|u| &u.name).collect::<Vec<_>>()
44
    );
45
    Ok(())
46
}

Real output:

1
1 active: ["Ada"]

SeaORM: async ActiveRecord-style entities

1
[dependencies]
2
sea-orm = { version = "1.1", features = ["sqlx-sqlite", "runtime-tokio-rustls", "macros"] }
3
tokio = { version = "1", features = ["full"] }

1
use sea_orm::entity::prelude::*;
2
use sea_orm::{ActiveModelTrait, ConnectionTrait, Database, QueryOrder, Schema, Set};
3

4
// An ENTITY — the SeaORM analogue of a TypeORM @Entity class.
5
// Normally generated by `sea-orm-cli generate entity` from your live schema.
6
#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
7
#[sea_orm(table_name = "users")]
8
pub struct Model {
9
    #[sea_orm(primary_key)]
10
    pub id: i32,
11
    pub name: String,
12
    #[sea_orm(unique)]
13
    pub email: String,
14
    pub active: bool,
15
}
16

17
#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
18
pub enum Relation {}
19

20
impl ActiveModelBehavior for ActiveModel {}
21

22
#[tokio::main]
23
async fn main() -> Result<(), DbErr> {
24
    let db = Database::connect("sqlite::memory:").await?;
25

26
    // Create the schema from the entity (dev convenience, like synchronize: true).
27
    let builder = db.get_database_backend();
28
    let schema = Schema::new(builder);
29
    db.execute(builder.build(&schema.create_table_from_entity(Entity)))
30
        .await?;
31

32
    // INSERT via an ActiveModel: set the fields you want, leave the PK NotSet.
33
    for (n, e, a) in [("Ada", "ada@x.com", true), ("Alan", "alan@x.com", false)] {
34
        ActiveModel {
35
            name: Set(n.into()),
36
            email: Set(e.into()),
37
            active: Set(a),
38
            ..Default::default()
39
        }
40
        .insert(&db)
41
        .await?;
42
    }
43

44
    // A dynamic query builder, like TypeORM's QueryBuilder — but async.
45
    let active: Vec<Model> = Entity::find()
46
        .filter(Column::Active.eq(true))
47
        .order_by_asc(Column::Id)
48
        .all(&db)
49
        .await?;
50

51
    println!(
52
        "{} active: {:?}",
53
        active.len(),
54
        active.iter().map(|u| &u.name).collect::<Vec<_>>()
55
    );
56
    Ok(())
57
}

Real output:

1
1 active: ["Ada"]

Three crates, identical result. The differences are not in what they can do — all three run any query you need — but in where the checking happens, whether you write SQL or a DSL, and whether the API is async.

Detailed Explanation

The core axis: SQL vs DSL, and where validation happens

Read the three programs again with one question in mind: where is your query, and who checks it?

In SQLx, the query is a SQL string. The plain sqlx::query(...) form is unchecked until it runs (like Knex raw). The sqlx::query!/query_as! macros — covered in sqlx-queries.md — connect to your real database during cargo build and validate the SQL, its parameters, and the result types against the actual schema. So a typo is a compile error, but the SQL itself is still SQL you wrote.
In Diesel, there is no SQL string in your code at all. users::active is a distinct Rust type carrying the column’s SQL type, and .filter(...), .order(...), .select(...) build a query from those types. The compiler validates the whole thing structurally: comparing a Text column to an integer, or loading a column into the wrong field type, has no valid trait implementation and the program does not build. The schema lives in a generated schema.rs (see diesel-intro.md).
In SeaORM, you also avoid raw SQL via a DSL (Entity::find().filter(Column::Active.eq(true))), but the building blocks are runtime values (a Column enum, dynamic Conditions) rather than Diesel’s compile-time type machinery. SeaORM checks that you reference a real Column variant — a typo is a compile error — but it does not validate the full query against a live schema the way SQLx’s macros do.

A compact way to hold it: SQLx checks your SQL against the database; Diesel checks a typed query against the schema’s types; SeaORM checks that you used real columns and gives you ORM ergonomics on top.

Async vs synchronous

This is the second decisive difference, and it follows the broader Rust async story from Section 11:

SQLx and SeaORM are async. Every call returns a Future you .await, and you need a runtime (Tokio) to drive it. This is the natural fit for an async web server like Axum (see Section 16), where a handler is already async.
Diesel is synchronous. establish, load, and get_result are blocking calls that return a plain Result — no .await, no runtime needed for a script. Inside an async server, a blocking Diesel call would stall a runtime worker thread, so you run it on a blocking pool (tokio::task::spawn_blocking + an r2d2 pool) or reach for the separate diesel-async crate. Details in connection-pooling.md.

Tip: “Async” is not automatically “faster.” Diesel’s synchronous model is often the simplest and fastest choice for a CLI, a batch job, or a worker that is not already inside an async runtime. The cost of async is real (a runtime, .await everywhere); pay it when you are already async, not as a reflex.

The write side: `INSERT`/`UPDATE` ergonomics

The three crates differ most visibly when writing data:

SQLx: you write the INSERT/UPDATE SQL yourself and bind parameters. Maximum control, zero magic.
Diesel: you build an Insertable struct (a separate write model, no id) and call insert_into(table).values(&new). Updates use AsChangeset. Covered in diesel-queries.md.
SeaORM: the ActiveModel pattern. Every entity gets a generated ActiveModel whose fields are ActiveValue<T> — each field is Set(value), NotSet, or Unchanged. You build one, set the fields you want, and call .insert() or .update(). SeaORM tracks which fields changed, so an update only writes the columns you touched — the closest analogue to TypeORM’s repo.save(partialEntity).

Here is the SeaORM ActiveModel write pattern in action — load a row, mutate it, and save only the changed column:

1
use sea_orm::entity::prelude::*;
2
use sea_orm::{ActiveModelTrait, ConnectionTrait, Database, PaginatorTrait, Schema, Set};
3

4
#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
5
#[sea_orm(table_name = "users")]
6
pub struct Model {
7
    #[sea_orm(primary_key)]
8
    pub id: i32,
9
    pub name: String,
10
    #[sea_orm(unique)]
11
    pub email: String,
12
    pub active: bool,
13
}
14
#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
15
pub enum Relation {}
16
impl ActiveModelBehavior for ActiveModel {}
17

18
#[tokio::main]
19
async fn main() -> Result<(), DbErr> {
20
    let db = Database::connect("sqlite::memory:").await?;
21
    let builder = db.get_database_backend();
22
    let schema = Schema::new(builder);
23
    db.execute(builder.build(&schema.create_table_from_entity(Entity)))
24
        .await?;
25

26
    for (n, e, a) in [("Ada", "ada@x.com", true), ("Alan", "alan@x.com", false)] {
27
        ActiveModel {
28
            name: Set(n.into()),
29
            email: Set(e.into()),
30
            active: Set(a),
31
            ..Default::default()
32
        }
33
        .insert(&db)
34
        .await?;
35
    }
36

37
    // UPDATE: load the Model, turn it into an ActiveModel, change one field, save.
38
    let alan = Entity::find_by_id(2).one(&db).await?.unwrap();
39
    let mut alan: ActiveModel = alan.into();
40
    alan.active = Set(true); // only this column will be written
41
    let alan: Model = alan.update(&db).await?;
42
    println!("updated: {} active={}", alan.name, alan.active);
43

44
    let n = Entity::find().filter(Column::Active.eq(true)).count(&db).await?;
45
    println!("active count = {n}");
46

47
    let res = Entity::delete_by_id(1).exec(&db).await?;
48
    println!("deleted {} row(s)", res.rows_affected);
49
    Ok(())
50
}

Real output:

1
updated: Alan active=true
2
active count = 2
3
deleted 1 row(s)

Code generation: who writes the boilerplate?

A practical concern: how much do you type by hand?

SQLx generates nothing persistent. (cargo sqlx prepare caches query metadata for offline builds — see migrations.md — but you write all the structs and SQL.)
Diesel generates schema.rs (the table! macros) from your migrations via the diesel CLI. You hand-write the model structs.
SeaORM can generate entire entity files (the Model, Column, Relation, and ActiveModel) from a live database with sea-orm-cli generate entity. This is the most “Prisma-like” experience: point it at a database and get typed code out.

Escape hatches: every option lets you drop to raw SQL

None of these locks you out of raw SQL when the DSL cannot express something (recursive CTEs, database-specific functions, hand-tuned queries). SQLx is already raw SQL. Diesel has diesel::sql_query. SeaORM has Statement::from_sql_and_values plus FromQueryResult to map the result into a custom struct:

1
use sea_orm::entity::prelude::*;
2
use sea_orm::{ActiveModelTrait, ConnectionTrait, Database, DbBackend, FromQueryResult,
3
              Schema, Set, Statement};
4

5
#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
6
#[sea_orm(table_name = "users")]
7
pub struct Model {
8
    #[sea_orm(primary_key)]
9
    pub id: i32,
10
    pub name: String,
11
    pub active: bool,
12
}
13
#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
14
pub enum Relation {}
15
impl ActiveModelBehavior for ActiveModel {}
16

17
// A custom projection for the raw-SQL escape hatch.
18
#[derive(Debug, FromQueryResult)]
19
struct NameOnly {
20
    name: String,
21
}
22

23
#[tokio::main]
24
async fn main() -> Result<(), DbErr> {
25
    let db = Database::connect("sqlite::memory:").await?;
26
    let builder = db.get_database_backend();
27
    let schema = Schema::new(builder);
28
    db.execute(builder.build(&schema.create_table_from_entity(Entity)))
29
        .await?;
30
    for (n, a) in [("Ada", true), ("Alan", false), ("Grace", true)] {
31
        ActiveModel { name: Set(n.into()), active: Set(a), ..Default::default() }
32
            .insert(&db)
33
            .await?;
34
    }
35

36
    // Drop to raw SQL when the DSL is not enough; bound params still prevent injection.
37
    let names: Vec<NameOnly> = NameOnly::find_by_statement(Statement::from_sql_and_values(
38
        DbBackend::Sqlite,
39
        "SELECT name FROM users WHERE active = ? ORDER BY name DESC",
40
        [true.into()],
41
    ))
42
    .all(&db)
43
    .await?;
44

45
    println!("raw: {:?}", names.iter().map(|n| &n.name).collect::<Vec<_>>());
46
    Ok(())
47
}

Real output:

1
raw: ["Grace", "Ada"]

The lesson: choosing an ORM does not trap you. You can use SeaORM or Diesel for 95% of queries and drop to raw SQL for the rest, the same way you mix Prisma’s typed client with $queryRaw in Node.

Key Differences

Aspect	SQLx	Diesel	SeaORM
Category	SQL toolkit (not an ORM)	ORM + typed query builder	Async ActiveRecord ORM
You write	raw SQL strings	a typed DSL (no SQL)	a typed DSL + ActiveModels
Async?	Yes (Tokio)	No (synchronous)	Yes (Tokio; built on SQLx)
Query validation	SQL checked vs live DB at compile time (macros)	compile-time structural type-checking	column names checked at compile time; query not validated vs live schema
Needs DB to compile?	Yes, for the `query!` macros (or offline cache)	No	No
Write pattern	hand-written SQL	`Insertable`/`AsChangeset` structs	`ActiveModel` (`Set`/`NotSet`)
Relations / eager loading	none (write the `JOIN`)	`belongs_to`/`has_many` (diesel-relations.md)	`related`/`find_also_related`
Code generation	none (query cache only)	`schema.rs` from migrations	full entity files from a live DB
Closest TS analogue	Knex `raw`, but compiler-checked	TypeORM/Prisma, but sync + fully typed	TypeORM/Prisma, staying async
Maturity	very mature, widely used	the oldest, most battle-tested	newer, rapidly growing

Why these designs differ

SQLx’s bet is that SQL is already the right abstraction and the compiler should check it rather than replace it; you keep full SQL expressiveness and lose nothing to a DSL. Diesel’s bet is that the type system can model SQL itself, so an entire class of query bugs becomes unrepresentable — at the cost of a steeper learning curve and a DSL that occasionally fights you on complex queries. SeaORM’s bet is that async ORM ergonomics (entities, ActiveModels, relations, generated code) matter most to teams coming from Prisma/TypeORM, so it builds those on top of SQLx’s solid async foundation, trading Diesel’s deepest compile-time guarantees for a gentler, more familiar API.

Common Pitfalls

Pitfall 1: Expecting SeaORM/SQLx to be synchronous like a quick script, or Diesel to be async

Diesel calls block and return a Result directly; writing .await on one is a compile error because the value is not a Future. Conversely, SQLx and SeaORM calls return Futures that do nothing until .awaited and driven by a runtime (Rust futures are lazy, the opposite of eager JS Promises — see promises-vs-futures.md). Mixing the models up is the single most common early mistake. Decide up front whether your program is async.

Pitfall 2: Misreading what “compile-time checked” means for each crate

All three give some compile-time safety, but it is not the same safety:

Diesel and SeaORM catch a misspelled column at compile time because columns are Rust items. In SeaORM, Column::Activ for an active field is a real rustc error:

1
error[E0599]: no variant or associated item named `Activ` found for enum `Column` in the current scope
2
  --> src/main.rs:20:43
3
   |
4
 4 | #[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
5
   |                                   ----------------- variant or associated item `Activ` not found for this enum
6
...
7
20 |     let _ = Entity::find().filter(Column::Activ.eq(true)).all(&db).await?;
8
   |                                           ^^^^^ variant or associated item not found in `Column`
9
   |
10
help: there is a variant with a similar name
11
   |
12
20 |     let _ = Entity::find().filter(Column::Active.eq(true)).all(&db).await?;
13
   |                                                +

But only SQLx’s query! macros check that the SQL itself matches the live database — that a column exists in the table, that a JOIN is valid, that a returned column is non-null. SeaORM does not phone the database at compile time. So “SeaORM is compile-time checked” is true for the DSL surface but does not give you SQLx’s schema-level guarantee.

The plain sqlx::query("...") form (without !) is not checked at all until it runs — it is exactly as unsafe as Knex raw. The compile-time guarantee is the macro, not the crate.

Pitfall 3: The `query!` macro needs a database at build time — surprising in CI/Docker

Because SQLx’s macros validate against a live database during cargo build, a build with no reachable database fails unless you committed an offline query cache (cargo sqlx prepare → .sqlx/). This bites CI pipelines and docker build constantly. Diesel and SeaORM do not have this requirement — they compile without any database. If “must reach the DB to compile” is unacceptable for your build environment and you do not want to manage the offline cache, that argues for Diesel/SeaORM (or for SQLx’s non-macro API).

Pitfall 4: Reaching for an ORM out of habit when SQLx would be simpler

TypeScript developers often default to “I need an ORM.” In Rust, if your access patterns are mostly straightforward queries, SQLx with the query! macros frequently beats a full ORM: you get compile-time-checked SQL, the smallest dependency footprint, and no DSL to learn — just SQL you already know. Reach for Diesel or SeaORM when you genuinely want entity mapping, relations/associations, change-tracking on updates, or generated code — not reflexively.

Pitfall 5: Assuming relations work the same as in TypeORM/Prisma

Prisma’s include and TypeORM’s relations make eager loading feel free. In Rust, SQLx has no relation concept at all (you write the JOIN and map it yourself), Diesel models associations explicitly with belongs_to/has_many and a belonging_to load step (diesel-relations.md), and SeaORM has related()/find_also_related(). If rich relation loading is central to your app, weigh SeaORM or Diesel over SQLx, and read their relation pages before committing.

Best Practices

Pick async vs sync first. Already inside an async server (Axum, etc.)? Choose SQLx or SeaORM. Writing a CLI, a migration tool, or a batch job? Diesel’s synchronous model is often the simplest. This decision narrows the field before any other.
Default to SQLx + query! for query-shaped workloads. Compile-time-checked SQL, minimal dependencies, no DSL to learn. It is the most common production choice and the easiest for a SQL-fluent team.
Choose Diesel for the strongest static guarantees and a mature ecosystem, especially in synchronous services and when you want column/type mismatches to be flatly impossible to compile. Accept the DSL learning curve and the blocking model.
Choose SeaORM when you want Prisma/TypeORM-style ergonomics while staying async — entities, ActiveModel change-tracking, relations, and sea-orm-cli generate entity to scaffold from an existing database.
Do not over-pick. Many teams use SQLx everywhere and reach for raw SQL when needed; that is a perfectly good endpoint. Mixing crates in one binary is possible but rarely worth the cognitive cost — standardize on one.
Whatever you choose, share one connection pool, cloned everywhere. All three expose a cheap clonable handle (SQLx Pool, SeaORM DatabaseConnection, Diesel via r2d2). Build it once at startup. See connection-pooling.md.
Keep the escape hatch in mind. No choice locks you out of raw SQL; use the DSL for the common path and drop down for the hard 5%.

Real-World Example

A UserRepo in SeaORM — the kind of repository layer you would put behind a web handler — showing entities with a typed enum column (a SeaORM strength that maps a Rust enum to a database value), the ActiveModel write path, and typed queries. It is fully self-contained on SQLite.

1
[dependencies]
2
sea-orm = { version = "1.1", features = ["sqlx-sqlite", "runtime-tokio-rustls", "macros"] }
3
tokio = { version = "1", features = ["full"] }

1
use sea_orm::entity::prelude::*;
2
use sea_orm::{ActiveModelTrait, ConnectionTrait, Database, DatabaseConnection, DbErr,
3
              QueryOrder, Schema, Set};
4

5
// A typed enum column: SeaORM maps this Rust enum to a SQL string value.
6
#[derive(Debug, Clone, PartialEq, Eq, EnumIter, DeriveActiveEnum)]
7
#[sea_orm(rs_type = "String", db_type = "String(StringLen::None)")]
8
pub enum Role {
9
    #[sea_orm(string_value = "admin")]
10
    Admin,
11
    #[sea_orm(string_value = "member")]
12
    Member,
13
}
14

15
#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
16
#[sea_orm(table_name = "users")]
17
pub struct Model {
18
    #[sea_orm(primary_key)]
19
    pub id: i32,
20
    pub name: String,
21
    #[sea_orm(unique)]
22
    pub email: String,
23
    pub role: Role,
24
}
25

26
#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
27
pub enum Relation {}
28

29
impl ActiveModelBehavior for ActiveModel {}
30

31
// A repository over a clonable connection handle (DatabaseConnection is an Arc inside).
32
#[derive(Clone)]
33
struct UserRepo {
34
    db: DatabaseConnection,
35
}
36

37
impl UserRepo {
38
    async fn create(&self, name: &str, email: &str, role: Role) -> Result<Model, DbErr> {
39
        ActiveModel {
40
            name: Set(name.to_owned()),
41
            email: Set(email.to_owned()),
42
            role: Set(role),
43
            ..Default::default()
44
        }
45
        .insert(&self.db)
46
        .await
47
    }
48

49
    // Returns Option<Model>: None (not an error) when nothing matches.
50
    async fn find_by_email(&self, email: &str) -> Result<Option<Model>, DbErr> {
51
        Entity::find().filter(Column::Email.eq(email)).one(&self.db).await
52
    }
53

54
    async fn admins(&self) -> Result<Vec<Model>, DbErr> {
55
        Entity::find()
56
            .filter(Column::Role.eq(Role::Admin)) // compares against the typed enum
57
            .order_by_asc(Column::Id)
58
            .all(&self.db)
59
            .await
60
    }
61
}
62

63
#[tokio::main]
64
async fn main() -> Result<(), DbErr> {
65
    let db = Database::connect("sqlite::memory:").await?;
66
    let builder = db.get_database_backend();
67
    let schema = Schema::new(builder);
68
    db.execute(builder.build(&schema.create_table_from_entity(Entity)))
69
        .await?;
70

71
    let repo = UserRepo { db };
72
    repo.create("Ada", "ada@x.com", Role::Admin).await?;
73
    repo.create("Alan", "alan@x.com", Role::Member).await?;
74

75
    if let Some(u) = repo.find_by_email("ada@x.com").await? {
76
        println!("found {} with role {:?}", u.name, u.role);
77
    }
78
    let admins = repo.admins().await?;
79
    println!(
80
        "admins: {:?}",
81
        admins.iter().map(|u| &u.name).collect::<Vec<_>>()
82
    );
83
    Ok(())
84
}

Real output:

1
found Ada with role Admin
2
admins: ["Ada"]

The repository is Clone because DatabaseConnection is a cheap Arc handle, exactly like an SQLx Pool. You would build the connection once in main, store the UserRepo in your application state, and clone it into every handler — the same shared-state pattern used across the web frameworks in Section 16. The typed Role enum is the kind of ergonomic that pulls teams toward SeaORM: in SQLx you would map that column to a String and convert by hand, or write a custom FromRow.

Exercises

Exercise 1: Name the right tool

Difficulty: Beginner

Objective: Internalize the decision axis by matching scenarios to crates.

Instructions: For each scenario, name the crate (SQLx, Diesel, or SeaORM) that fits best and state the deciding factor in one sentence:

An Axum web service whose team is fluent in SQL and wants its queries verified against the schema at build time.
A synchronous command-line tool that imports a CSV into a database, with no async runtime anywhere.
A team migrating from a Prisma/TypeORM codebase that wants generated entities and ActiveRecord-style updates while staying async.

Solution

SQLx. It is async (fits Axum) and its query! macros check the SQL against the live database at compile time — ideal for a SQL-fluent team.
Diesel. Its synchronous, blocking model needs no runtime, which is the simplest fit for a CLI with no async anywhere. (SQLx without async would force you to add a runtime just for the database.)
SeaORM. It offers Prisma/TypeORM-like ergonomics — generated entities (sea-orm-cli generate entity) and the ActiveModel write pattern — while remaining fully async on top of SQLx.

The deciding factors are, in order: async vs sync, then how much you value compile-time-checked raw SQL versus ORM ergonomics and generated code.

Exercise 2: Translate one query across two crates

Difficulty: Intermediate

Objective: Feel the difference between raw-SQL SQLx and the SeaORM DSL by writing the same read both ways.

Instructions: Given a products table (id, name, price), write a program that loads the two most expensive products (ordered by price descending, limit 2) and prints their names. Do it once with SQLx (query_as and a LIMIT in the SQL string) and once with SeaORM (Entity::find().order_by_desc(...).limit(2)). Both should print the same names. Use in-memory SQLite.

Solution

SQLx version:

1
[dependencies]
2
sqlx = { version = "0.8", features = ["runtime-tokio-rustls", "sqlite"] }
3
tokio = { version = "1", features = ["full"] }

1
use sqlx::sqlite::SqlitePoolOptions;
2
use sqlx::FromRow;
3

4
#[derive(Debug, FromRow)]
5
struct Product {
6
    name: String,
7
    price: f64,
8
}
9

10
#[tokio::main]
11
async fn main() -> Result<(), sqlx::Error> {
12
    let pool = SqlitePoolOptions::new().connect("sqlite::memory:").await?;
13
    sqlx::query("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL NOT NULL)")
14
        .execute(&pool)
15
        .await?;
16
    for (n, p) in [("Keyboard", 49.99), ("Mouse", 19.5), ("Monitor", 199.0), ("Cable", 5.0)] {
17
        sqlx::query("INSERT INTO products (name, price) VALUES (?, ?)")
18
            .bind(n)
19
            .bind(p)
20
            .execute(&pool)
21
            .await?;
22
    }
23

24
    let top: Vec<Product> =
25
        sqlx::query_as::<_, Product>("SELECT name, price FROM products ORDER BY price DESC LIMIT 2")
26
            .fetch_all(&pool)
27
            .await?;
28
    println!("{:?}", top.iter().map(|p| &p.name).collect::<Vec<_>>());
29
    Ok(())
30
}

SeaORM version:

1
[dependencies]
2
sea-orm = { version = "1.1", features = ["sqlx-sqlite", "runtime-tokio-rustls", "macros"] }
3
tokio = { version = "1", features = ["full"] }

1
use sea_orm::entity::prelude::*;
2
use sea_orm::{ActiveModelTrait, ConnectionTrait, Database, QueryOrder, QuerySelect, Schema, Set};
3

4
#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
5
#[sea_orm(table_name = "products")]
6
pub struct Model {
7
    #[sea_orm(primary_key)]
8
    pub id: i32,
9
    pub name: String,
10
    #[sea_orm(column_type = "Double")]
11
    pub price: f64,
12
}
13
#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
14
pub enum Relation {}
15
impl ActiveModelBehavior for ActiveModel {}
16

17
#[tokio::main]
18
async fn main() -> Result<(), DbErr> {
19
    let db = Database::connect("sqlite::memory:").await?;
20
    let builder = db.get_database_backend();
21
    let schema = Schema::new(builder);
22
    db.execute(builder.build(&schema.create_table_from_entity(Entity)))
23
        .await?;
24
    for (n, p) in [("Keyboard", 49.99), ("Mouse", 19.5), ("Monitor", 199.0), ("Cable", 5.0)] {
25
        ActiveModel { name: Set(n.into()), price: Set(p), ..Default::default() }
26
            .insert(&db)
27
            .await?;
28
    }
29

30
    let top: Vec<Model> = Entity::find()
31
        .order_by_desc(Column::Price)
32
        .limit(2)
33
        .all(&db)
34
        .await?;
35
    println!("{:?}", top.iter().map(|p| &p.name).collect::<Vec<_>>());
36
    Ok(())
37
}

Both print:

1
["Monitor", "Keyboard"]

The SQLx version carries the ordering and limit in the SQL string; the SeaORM version expresses them through order_by_desc and limit on the typed query (note limit comes from the QuerySelect trait). Same result, two philosophies.

Exercise 3: Use SeaORM’s pagination

Difficulty: Advanced

Objective: Exercise a feature an ORM gives you for free that you would hand-roll in SQLx — paginating a result set.

Instructions: Seed a products table with four rows. Using SeaORM’s Paginator (the paginate(&db, page_size) method from the PaginatorTrait), page through all products two per page, ordered by price descending. Print the total number of pages, then print each page’s (name, price) pairs.

Solution

1
[dependencies]
2
sea-orm = { version = "1.1", features = ["sqlx-sqlite", "runtime-tokio-rustls", "macros"] }
3
tokio = { version = "1", features = ["full"] }

1
use sea_orm::entity::prelude::*;
2
use sea_orm::{ActiveModelTrait, ConnectionTrait, Database, PaginatorTrait, QueryOrder, Schema, Set};
3

4
#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
5
#[sea_orm(table_name = "products")]
6
pub struct Model {
7
    #[sea_orm(primary_key)]
8
    pub id: i32,
9
    pub name: String,
10
    #[sea_orm(column_type = "Double")]
11
    pub price: f64,
12
}
13
#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
14
pub enum Relation {}
15
impl ActiveModelBehavior for ActiveModel {}
16

17
#[tokio::main]
18
async fn main() -> Result<(), DbErr> {
19
    let db = Database::connect("sqlite::memory:").await?;
20
    let builder = db.get_database_backend();
21
    let schema = Schema::new(builder);
22
    db.execute(builder.build(&schema.create_table_from_entity(Entity)))
23
        .await?;
24
    for (n, p) in [("Keyboard", 49.99), ("Mouse", 19.50), ("Monitor", 199.0), ("Cable", 5.0)] {
25
        ActiveModel { name: Set(n.into()), price: Set(p), ..Default::default() }
26
            .insert(&db)
27
            .await?;
28
    }
29

30
    // num_pages reports the total; fetch_and_next walks the pages.
31
    let counter = Entity::find().order_by_desc(Column::Price).paginate(&db, 2);
32
    println!("total pages = {}", counter.num_pages().await?);
33

34
    let mut paginator = Entity::find().order_by_desc(Column::Price).paginate(&db, 2);
35
    let mut page_no = 0;
36
    while let Some(rows) = paginator.fetch_and_next().await? {
37
        println!(
38
            "page {page_no}: {:?}",
39
            rows.iter().map(|m| (&m.name, m.price)).collect::<Vec<_>>()
40
        );
41
        page_no += 1;
42
    }
43
    Ok(())
44
}

Real output:

1
total pages = 2
2
page 0: [("Monitor", 199.0), ("Keyboard", 49.99)]
3
page 1: [("Mouse", 19.5), ("Cable", 5.0)]

In SQLx you would compute LIMIT/OFFSET and a separate COUNT(*) yourself; SeaORM’s Paginator bundles both into one API. This is a concrete example of the ergonomics an ORM buys you over a raw-SQL toolkit.

SQLx vs Diesel vs SeaORM: Choosing a Database Layer

Quick Overview

TypeScript/JavaScript Example

Rust Equivalent

SQLx: raw SQL, typed result

Diesel: synchronous typed DSL

SeaORM: async ActiveRecord-style entities

Detailed Explanation

The core axis: SQL vs DSL, and where validation happens

Async vs synchronous

The write side: INSERT/UPDATE ergonomics

Code generation: who writes the boilerplate?

Escape hatches: every option lets you drop to raw SQL

Key Differences

Why these designs differ

Common Pitfalls

Pitfall 1: Expecting SeaORM/SQLx to be synchronous like a quick script, or Diesel to be async

Pitfall 2: Misreading what “compile-time checked” means for each crate

Pitfall 3: The query! macro needs a database at build time — surprising in CI/Docker

Pitfall 4: Reaching for an ORM out of habit when SQLx would be simpler

Pitfall 5: Assuming relations work the same as in TypeORM/Prisma

Best Practices

Real-World Example

Further Reading

Exercises

Exercise 1: Name the right tool

Exercise 2: Translate one query across two crates

Exercise 3: Use SeaORM’s pagination

The write side: `INSERT`/`UPDATE` ergonomics

Pitfall 3: The `query!` macro needs a database at build time — surprising in CI/Docker