SeaORM 1.0 debuted on 2024-08-04. Over the past year, we've shipped 16 minor releases - staying true to our promise of delivering new features without compromising stability.

While building new features in 1.0, we often found ourselves bending over backwards to avoid breaking changes, which meant leaving in a few bits that aren't exactly elegant, intuitive, or frankly, "footgun".

To make SeaORM friendlier and more intuitive for newcomers (and a little kinder to seasoned users too), we've decided it's time for a 2.0 release - one that embraces necessary breaking changes to clean things up and set a stronger foundation for the future.

1.0 New Features​

If you haven't been following every update, here's a quick tour of some quality-of-life improvements you can start using right now. Otherwise, you can skip to the 2.0 section.

Nested Select​

This is the most requested feature by far, and we've implemented nested select in SeaORM. We've added nested alias and ActiveEnum support too.

use sea_orm::DerivePartialModel;

#[derive(DerivePartialModel)]
#[sea_orm(entity = "cake::Entity", from_query_result)]
struct CakeWithFruit {
    id: i32,
    name: String,
    #[sea_orm(nested)]
    fruit: Option<Fruit>,
}

#[derive(DerivePartialModel)]
#[sea_orm(entity = "fruit::Entity", from_query_result)]
struct Fruit {
    id: i32,
    name: String,
}

let cakes: Vec<CakeWithFruit> = cake::Entity::find()
    .left_join(fruit::Entity)
    .into_partial_model()
    .all(db)
    .await?;

PartialModel -> ActiveModel​

DerivePartialModel got another extension to derive IntoActiveModel as well. Absent attributes will be filled with NotSet. This allows you to use partial models to perform insert / updates as well.

#[derive(DerivePartialModel)]
#[sea_orm(entity = "cake::Entity", into_active_model)]
struct PartialCake {
    id: i32,
    name: String,
}

let partial_cake = PartialCake {
    id: 12,
    name: "Lemon Drizzle".to_owned(),
};

// this is now possible:
assert_eq!(
    cake::ActiveModel {
        ..partial_cake.into_active_model()
    },
    cake::ActiveModel {
        id: Set(12),
        name: Set("Lemon Drizzle".to_owned()),
        ..Default::default()
    }
);

Insert active models with non-uniform column sets​

Insert many now allows active models to have different column sets. Previously, it'd panic when encountering this. Missing columns will be filled with NULL. This makes seeding data a seamless operation.

let apple = cake_filling::ActiveModel {
    cake_id: ActiveValue::set(2),
    filling_id: ActiveValue::NotSet,
};
let orange = cake_filling::ActiveModel {
    cake_id: ActiveValue::NotSet,
    filling_id: ActiveValue::set(3),
};
assert_eq!(
    Insert::<cake_filling::ActiveModel>::new()
        .add_many([apple, orange])
        .build(DbBackend::Postgres)
        .to_string(),
    r#"INSERT INTO "cake_filling" ("cake_id", "filling_id") VALUES (2, NULL), (NULL, 3)"#,
);

Support Postgres PgVector & IpNetwork​

Under feature flag postgres-vector / with-ipnetwork.

#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
#[sea_orm(table_name = "demo_table")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i32,
    pub embedding: PgVector,
    pub ipaddress: IpNetwork,
}

2.0 New Features​

These are small touch-ups, but added-up can make big differences.

Nested Select on any Model​

#2642 Wait... we've seen this before? No, there is a small detail here: now every Model can be used in nested select! This requires a small breaking change to basically derive PartialModelTrait on regular Models. And also notice the removed from_query_result.

use sea_orm::DerivePartialModel;

#[derive(DerivePartialModel)]
#[sea_orm(entity = "cake::Entity")] // <- from_query_result not needed
struct CakeWithFruit {
    id: i32,
    name: String,
    #[sea_orm(nested)]
    fruit: Option<fruit::Model>, // <- this is just a regular Model
}

let cakes: Vec<CakeWithFruit> = cake::Entity::find()
    .left_join(fruit::Entity)
    .into_partial_model()
    .all(db)
    .await?;

Wrapper type as primary key​

#2643 Wrapper type derived with DeriveValueType can now be used as primary key. Now you can embrace Rust's type system to make your code more robust!

#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
#[sea_orm(table_name = "my_value_type")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: MyInteger,
}

#[derive(Clone, Debug, PartialEq, Eq, DeriveValueType)]
pub struct MyInteger(pub i32);
// only for i8 | i16 | i32 | i64 | u8 | u16 | u32 | u64

Multi-part unique keys​

#2651 You can now define unique keys that span multiple columns in Entity.

#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
#[sea_orm(table_name = "lineitem")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i32,
    #[sea_orm(unique_key = "item")]
    pub order_id: i32,
    #[sea_orm(unique_key = "item")]
    pub cake_id: i32,
}

let stmts = Schema::new(backend).create_index_from_entity(lineitem::Entity);

assert_eq!(
    stmts[0],
    Index::create()
        .name("idx-lineitem-item")
        .table(lineitem::Entity)
        .col(lineitem::Column::OrderId)
        .col(lineitem::Column::CakeId)
        .unique()
        .take()
);

assert_eq!(
    backend.build(stmts[0]),
    r#"CREATE UNIQUE INDEX "idx-lineitem-item" ON "lineitem" ("order_id", "cake_id")"#
);

Allow missing fields when using ActiveModel::from_json​

#2599 Improved utility of ActiveModel::from_json when dealing with inputs coming from REST APIs.

Consider the following Entity:

#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel, Serialize, Deserialize)]
#[sea_orm(table_name = "cake")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i32,      // <- not nullable
    pub name: String,
}

Previously, the following would result in error missing field "id". The usual solution is to add #[serde(skip_deserializing)] to the Model.

assert!(
    cake::ActiveModel::from_json(json!({
        "name": "Apple Pie",
    })).is_err();
);

Now, the above will just work. The ActiveModel will be partially filled:

assert_eq!(
    cake::ActiveModel::from_json(json!({
        "name": "Apple Pie",
    }))
    .unwrap(),
    cake::ActiveModel {
        id: NotSet,
        name: Set("Apple Pie".to_owned()),
    }
);

How does it work under the hood? It's actually quite interesting. This requires a small breaking to the trait bound of the method.

2.0 Exciting New Features​

We've planned some exciting new features for SeaORM too.

Raw SQL macro​

While already explained in detail in a previous blog post, we've integrated the raw_sql! macro nicely into SeaORM.

It's not a ground-breaking new feature, but it does unlock exciting new ways to use SeaORM. After all, SeaORM isn't just an ORM; it's a flexible SQL toolkit you can tailour to your own programming style. Use it as a backend-agnostic SQLx wrapper, SeaQuery with built-in connection management, or a lightweight ORM with enchanted raw SQL. The choice is yours!

Find Model by raw SQL​

let id = 1;

let cake: Option<cake::Model> = cake::Entity::find()
    .from_raw_sql(raw_sql!(
        Postgres,
        r#"SELECT "cake"."id", "cake"."name" FROM "cake" WHERE "id" = {id}"#
    ))
    .one(&db)
    .await?;

Find custom Model by raw SQL​

#[derive(FromQueryResult)]
struct Cake {
    name: String,
    #[sea_orm(nested)]
    bakery: Option<Bakery>,
}

#[derive(FromQueryResult)]
struct Bakery {
    #[sea_orm(alias = "bakery_name")]
    name: String,
}

let cake_ids = [2, 3, 4]; // expanded by the `..` operator

let cake: Option<Cake> = Cake::find_by_statement(raw_sql!(
    Sqlite,
    r#"SELECT "cake"."name", "bakery"."name" AS "bakery_name"
       FROM "cake"
       LEFT JOIN "bakery" ON "cake"."bakery_id" = "bakery"."id"
       WHERE "cake"."id" IN ({..cake_ids})"#
))
.one(db)
.await?;

Paginate raw SQL query​

You can paginate SelectorRaw and fetch Model in batch.

let ids = vec![1, 2, 3, 4];

let mut cake_pages = cake::Entity::find()
    .from_raw_sql(raw_sql!(
        Postgres,
        r#"SELECT "cake"."id", "cake"."name" FROM "cake" WHERE "id" IN ({..ids})"#
    ))
    .paginate(db, 10);

while let Some(cakes) = cake_pages.fetch_and_next().await? {
    // Do something on cakes: Vec<cake::Model>
}

Role Based Access Control​

#2683 We will cover this in detail in a future blog post, but here's a sneak peek.

SeaORM RBAC​

1. A hierarchical RBAC engine that is table scoped
   • a user has 1 (and only 1) role
   • a role has a set of permissions on a set of resources
     • permissions here are CRUD operations and resources are tables
     • but the engine is generic so can be used for other things
   • roles have hierarchy, and can inherit permissions from multiple roles
   • there is a wildcard * (opt-in) to grant all permissions or resources
   • individual users can have rules override
2. A set of Entities to load / store the access control rules to / from database
3. A query auditor that dissect queries for necessary permissions (implemented in SeaQuery)
4. Integration of RBAC into SeaORM in form of RestrictedConnection. It implements ConnectionTrait, behaves like a normal connection, but will audit all queries and perform permission check before execution, and reject them accordingly. All Entity operations except raw SQL are supported. Complex nested joins, INSERT INTO SELECT FROM, and even CTE queries are supported.

// load rules from database
db_conn.load_rbac().await?;

// admin can create bakery
let db: RestrictedConnection = db_conn.restricted_for(admin)?;
let seaside_bakery = bakery::ActiveModel {
    name: Set("SeaSide Bakery".to_owned()),
    ..Default::default()
};
assert!(Bakery::insert(seaside_bakery).exec(&db).await.is_ok());

// manager cannot create bakery
let db: RestrictedConnection = db_conn.restricted_for(manager)?;
assert!(matches!(
    Bakery::insert(bakery::ActiveModel::default())
        .exec(&db)
        .await,
    Err(DbErr::AccessDenied { .. })
));

// transaction works too
let txn: RestrictedTransaction = db.begin().await?;

baker::Entity::insert(baker::ActiveModel {
    name: Set("Master Baker".to_owned()),
    bakery_id: Set(Some(1)),
    ..Default::default()
})
.exec(&txn)
.await?;

txn.commit().await?;

🖥️ SeaORM Pro: Professional Admin Panel​

SeaORM Pro is an admin panel solution allowing you to quickly and easily launch an admin panel for your application - frontend development skills not required, but certainly nice to have!

Features:

• Full CRUD
• Built on React + GraphQL
• Built-in GraphQL resolver
• Customize the UI with simple TOML
• RBAC (coming soon with SeaORM 2.0)

More to come​

SeaORM 2.0 is shaping up to be our most significant release yet - with a few breaking changes, plenty of enhancements, and a clear focus on developer experience. We'll unpack everything in the posts to come, so keep an eye out for the next update!

If you have suggestions on breaking changes, you are welcome to post them in the discussions.

Sponsors​

If you feel generous, a small donation will be greatly appreciated, and goes a long way towards sustaining the organization.

Gold Sponsor​

QDX pioneers quantum dynamics–powered drug discovery, leveraging AI and supercomputing to accelerate molecular modeling. We're grateful to QDX for sponsoring the development of SeaORM, the SQL toolkit that powers their data intensive applications.

GitHub Sponsors​

A big shout out to our GitHub sponsors 😇:

Subscribe Pro

Variant9

Ryan Swart

OteroRafael

Yuta Hinokuma

wh7f

MS

Numeus

Data Intuitive

Caido Community

Marcus Buffett

MasakiMiyazaki

KallyDev

Manfred Lee

Afonso Barracha

Dean Sheather

Rustacean Sticker Pack 🦀​

The Rustacean Sticker Pack is the perfect way to express your passion for Rust. Our stickers are made with a premium water-resistant vinyl with a unique matte finish. Stick them on your laptop, notebook, or any gadget to show off your love for Rust!

Moreover, all proceeds contributes directly to the ongoing development of SeaQL projects.

Sticker Pack Contents:

• Logo of SeaQL projects: SeaQL, SeaORM, SeaQuery, Seaography, FireDBG
• Mascot of SeaQL: Terres the Hermit Crab
• Mascot of Rust: Ferris the Crab
• The Rustacean word

Support SeaQL and get a Sticker Pack!

SeaQuery 0.1.0 was first released on 2020-12-16 - it's been a few years! Since then, there have been 32 releases, each introducing a set of new features. As with many software projects, the organic evolution driven by a diverse community of open source contributors has led to occasional inconsistencies across the codebase. It's a good problem to have, and a testament to our vibrant community. But now, it's time to stabilize SeaQuery and address some of these issues.

A very brief recap of important SeaQuery verisons:

Architectural changes​

There are a few architectural changes that can only be made by breaking the API, so let's go through them one by one:

Forbid unsafe code​

#930 #![forbid(unsafe_code)] has been added to all workspace crates, ensuring that SeaQuery no longer contains any unsafe code. While only one instance of unsafe was previously used, and has now been removed, this change reinforces our commitment to maintaining code quality.

Unified Expr and SimpleExpr as one type​

#890 Previously, a lot of operator methods (e.g. eq) were duplicated across Expr and SimpleExpr, but the list of methods was slightly different for each. Also, it wasn't clear when to use each of the two types. The type conversions were sometimes non-obvious. It complicated the type system and made writing generic code difficult.

In 0.32.0, almost a year ago, we added ExprTrait (#771) to standardize and share the list of methods, and to allow calling them on other "lower-level" types like so: 1_i32.cast_as("REAL"). At that time, we decided to keep the original inherent methods for compatibility, at the cost of ~1300 lines of code bloat.

Later, we looked into the Expr vs SimpleExpr distinction. It turned out that Expr was originally meant to be a "namespace" of static constructors for SimpleExpr, similar to Func vs FunctionCall. Unlike Func, which is a unit struct, Expr has data fields, which led to Exprs being passed around, making it hard for functions to accept / return "expression fragments".

In 1.0, SimpleExpr is "merged into" Expr, meaning that SimpleExpr is now just a type alias of Expr. Both names can be used interchangeably. A lot of redundant type conversions (.into()) and generic code (T: ExprTrait) can now be removed.

The resulting "merged" type has all methods from the two original types, except for the methods defined by ExprTrait. Those inherent methods have been removed and have saved us 1300 lines of code.

enum Expr { // the AST node enum
    Column(ColumnRef),
    Tuple(Vec<Expr>),
    ..
}

type SimpleExpr = Expr; // now: just an alias

impl Expr {
    pub fn equals<C>(self, col: C) -> Self; // removed
}

trait ExprTrait: Sized {
    fn equals<C>(self, col: C) -> Expr; // please use this
}

Potential compile errors​

If you implemented some trait for both of those types, two impls for one type will no longer compile and you'll need to delete one of the impls.

If you encounter the following error, please add use sea_query::ExprTrait in scope.

error[E0599]: no method named `like` found for enum `sea_query::Expr` in the current scope
    |
    |         Expr::col((self.entity_name(), *self)).like(s)
    |
    |     fn like<L>(self, like: L) -> Expr
    |        ---- the method is available for `sea_query::Expr` here
    |
    = help: items from traits can only be used if the trait is in scope
help: trait `ExprTrait` which provides `like` is implemented but not in scope; perhaps you want to import it
    |
 -> + use sea_query::ExprTrait;

error[E0308]: mismatched types
  --> src/sqlite/discovery.rs:27:57
   |
   |             .and_where(Expr::col(Alias::new("type")).eq("table"))
   |                                                      -- ^^^^^^^ expected `&Expr`, found `&str`
   |                                                      |
   |                                                      arguments to this method are incorrect
   |
   = note: expected reference `&sea_query::Expr`
              found reference `&'static str`

Revamped Iden type system.​

#909 Previously, DynIden is lazily rendered, i.e. the identifier is only constructed while serializing the AST. Now, it's an eagerly rendered string Cow<'static, str>, constructed while constructing the AST.

pub type DynIden = SeaRc<dyn Iden>;               // old
pub struct DynIden(pub(crate) Cow<'static, str>); // new

pub struct SeaRc<I>(pub(crate) RcOrArc<I>);       // old
pub struct SeaRc;                                 // new

The implications of this new design are:

1. Type info is erased from Iden early
2. SeaRc is no longer an alias to Rc / Arc. As such, Send / Sync is removed from the trait Iden

Potential compile errors​

The method signature of Iden::unquoted is changed. If you're implementing Iden manually, you can modify it like below:

error[E0050]: method `unquoted` has 2 parameters but the declaration in trait `types::Iden::unquoted` has 1
  --> src/tests_cfg.rs:31:17
   |
   |     fn unquoted(&self, s: &mut dyn std::fmt::Write) {
   |                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ expected 1 parameter, found 2
   |
  ::: src/types.rs:63:17
   |
   |     fn unquoted(&self) -> &str;
   |                 ----- trait requires 1 parameter

impl Iden for Glyph {
  - fn unquoted(&self, s: &mut dyn fmt::Write) {
  + fn unquoted(&self) -> &str {
  -     write!(
  -         s,
  -         "{}",
            match self {
                Self::Table => "glyph",
                Self::Id => "id",
                Self::Tokens => "tokens",
            }
  -     )
  -     .unwrap();
    }
}

Alias::new is no longer needed​

#882 SeaQuery encourages you to define all column / table identifiers in one place and use them throughout the project. But there are places where an alias is needed once off. Now &'static str is an Iden, so it can be used in all places where Alias are needed. The Alias type remains for backwards compatibility, so existing code should still compile. This can reduce the verbosity of code, for example:

let query = Query::select()
    .from(Character::Table)
 -  .expr_as(Func::count(Expr::col(Character::Id)), Alias::new("count"))
 +  .expr_as(Func::count(Expr::col(Character::Id)), "count")
    .to_owned();

Unboxed Value variants​

#925 Most Value variants are now unboxed (except BigDecimal and Array). Previously the size is 24 bytes, now it's 32.

assert_eq!(std::mem::size_of::<Value>(), 32);

If you were constructing / pattern matching Value variants manually, Box::new can now be removed and pattern matching is simpler.

It also improved performance because memory allocation and indirection is removed in most cases.

Potential compile errors​

If you encounter the following error, simply remove the Box

error[E0308]: mismatched types
    |
 >  | Value::String(Some(Box::new(string_value.to_string()))));
    |               ---- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ expected `String`, found `Box<String>`
    |               |
    |               arguments to this enum variant are incorrect

non_exhaustive AST node enums​

#891 #[non_exhaustive] are added to all AST node enums. It allows us to add new features and extend the AST without breaking the API.

+ #[non_exhaustive]
enum Mode {
    Creation,
    Alter,
    TableAlter,
}

Potential compile errors​

If you encounter the following error, please add a wildcard match _ => {..}

error[E0004]: non-exhaustive patterns: `&_` not covered
    |
    |     match table_ref {
    |           ^^^^^^^^^ pattern `&_` not covered
    |
note: `TableRef` defined here
    |
    | pub enum TableRef {
    | ^^^^^^^^^^^^^^^^^
    = note: the matched value is of type `&TableRef`
    = note: `TableRef` is marked as non-exhaustive, so a wildcard `_` is necessary to match exhaustively
help: ensure that all possible cases are being handled by adding a match arm with a wildcard pattern or an explicit pattern as shown
    |
    | TableRef::FunctionCall(_, tbl) => SeaRc::clone(tbl),
 -> | &_ => todo!(),

Reworked TableRef and ColumnRef​

#927 Previously, the TableRef variants are a product of all valid combinations of Option<Database>, Option<Schema>, Table and Option<Alias>. It is excessive and makes pattern matching difficult.

Now they're collapsed into one. It makes constructing and pattern-matching TableRef / ColumnRef much easier.

// the following variants are collapsed into one:
enum TableRef {
    Table(DynIden),
    SchemaTable(DynIden, DynIden),
    DatabaseSchemaTable(DynIden, DynIden, DynIden),
    TableAlias(DynIden, DynIden),
    SchemaTableAlias(DynIden, DynIden, DynIden),
    DatabaseSchemaTableAlias(DynIden, DynIden, DynIden, DynIden),
    ..
}
// now it's just:
enum TableRef {
    Table(TableName, Option<DynIden>), // optional Alias
    ..
}

pub struct DatabaseName(pub DynIden);
pub struct SchemaName(pub Option<DatabaseName>, pub DynIden);
/// A table name, potentially qualified as [database.][schema.]table
pub struct TableName(pub Option<SchemaName>, pub DynIden);

Similarly for ColumnRef:

// before
enum ColumnRef {
    Column(DynIden),
    TableColumn(DynIden, DynIden),
    SchemaTableColumn(DynIden, DynIden, DynIden),
    Asterisk,
    TableAsterisk(DynIden),
}
// now
enum ColumnRef {
    /// A column name, potentially qualified as [database.][schema.][table.]column
    Column(ColumnName),
    /// An `*` expression, potentially qualified as [database.][schema.][table.]*
    Asterisk(Option<TableName>),
}

pub struct ColumnName(pub Option<TableName>, pub DynIden);

Potential compile errors​

TableRef

error[E0061]: this enum variant takes 2 arguments but 1 argument was supplied
   --> src/entity/relation.rs:526:15
    |
 >  |     from_tbl: TableRef::Table("foo".into_iden()),
    |               ^^^^^^^^^^^^^^^-------------------
    |                              ||
    |                              |expected `TableName`, found `DynIden`
    |                              argument #2 of type `Option<DynIden>` is missing

It's recommended to use the IntoTableRef trait to convert types instead of constructing AST manually.

use sea_orm::sea_query::IntoTableRef;

from_tbl: "foo".into_table_ref(),

ColumnRef

error[E0277]: the trait bound `fn(std::option::Option<TableName>) -> sea_query::ColumnRef {sea_query::ColumnRef::Asterisk}: IntoColumnRef` is not satisfied
    --> src/executor/query.rs:1599:21
    |
 >  |             .column(ColumnRef::Asterisk)
    |              ------ ^^^^^^^^^^^^^^^^^^^ the trait `sea_query::Iden` is not implemented for fn item `fn(std::option::Option<TableName>) -> sea_query::ColumnRef {sea_query::ColumnRef::Asterisk}`
    |              |
    |              required by a bound introduced by this call

error[E0308]: mismatched types
    --> src/executor/query.rs:1607:54
    |
 >  |                 SimpleExpr::Column(ColumnRef::Column("id".into_iden()))
    |                                    ----------------- ^^^^^^^^^^^^^^^^ expected `ColumnName`, found `DynIden`
    |                                    |
    |                                    arguments to this enum variant are incorrect

In the former case Asterisk has an additional inner Option<TableName>, you can simply put None.

.column(ColumnRef::Asterisk(None))

In the latter case, &'static str can now be used in most methods that accepts ColumnRef.

Expr::column("id")

New Features​

Query Audit​

#908 In order to support Role Based Access Control (RBAC) in SeaORM, a given SQL query has to be analyzed to determine what permissions are needed to act on which resources.

It supports all the query types: SELECT, INSERT, UPDATE, DELETE and CTE. This requires the audit feature flag.

let query = Query::select()
    .columns([Char::Character])
    .from(Char::Table)
    .left_join(
        Font::Table,
        Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
    )
    .inner_join(
        Glyph::Table,
        Expr::col((Char::Table, Char::Character)).equals((Glyph::Table, Glyph::Image)),
    )
    .take();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "character"
    FROM "character"
    LEFT JOIN "font" ON "character"."font_id" = "font"."id"
    INNER JOIN "glyph" ON "character"."character" = "glyph"."image""#
);

assert_eq!(
    query.audit()?.selected_tables(),
    [
        Char::Table.into_iden(),
        Font::Table.into_iden(),
        Glyph::Table.into_iden(),
    ]
);

Ergonomic raw SQL​

#952 This is already covered in a previous blog post. In case you've missed it, we've created a new raw_query! macro with neat features to make writing raw SQL queries more ergononmic.

let a = 1;
struct B { b: i32 }
let b = B { b: 2 };
let c = "A";
let d = vec![3, 4, 5];

let query = sea_query::raw_query!(
    PostgresQueryBuilder,
    r#"SELECT ("size_w" + {a}) * {b.b} FROM "glyph"
       WHERE "image" LIKE {c} AND "id" IN ({..d})"#
);

assert_eq!(
    query.sql,
    r#"SELECT ("size_w" + $1) * $2 FROM "glyph"
       WHERE "image" LIKE $3 AND "id" IN ($4, $5, $6)"#
);
assert_eq!(
    query.values,
    Values(vec![1.into(), 2.into(), "A".into(), 3.into(), 4.into(), 5.into()])
);

The snippet above demonstrated:

1. named parameter: {a} injected
2. nested parameter access: {b.b} inner access
3. array expansion: {..d} expanded into three parameters

Breaking Changes​

Replaced SERIAL with GENERATED BY DEFAULT AS IDENTITY (Postgres)​

#918 SERIAL is deprecated in Postgres because identity column (GENERATED AS IDENTITY) is more modern and, for example, can avoid sequence number quirks.

let table = Table::create()
    .table(Char::Table)
    .col(ColumnDef::new(Char::Id).integer().not_null().auto_increment().primary_key())
    .to_owned();

assert_eq!(
    table.to_string(PostgresQueryBuilder),
    [
        r#"CREATE TABLE "character" ("#,
            r#""id" integer GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,"#,
        r#")"#,
    ].join(" ")
);

If you need to support legacy systems you can still do:

let table = Table::create()
    .table(Char::Table)
    .col(ColumnDef::new(Char::Id).custom("serial").not_null().primary_key())
    .to_owned();

assert_eq!(
    table.to_string(PostgresQueryBuilder),
    [
        r#"CREATE TABLE "character" ("#,
            r#""id" serial NOT NULL PRIMARY KEY"#,
        r#")"#,
    ].join(" ")
);

Changed IntoXXX traits into Into<XXX>​

Changed IntoCondition (etc) traits to be defined as trait IntoCondition: Into<Condition>. A blanket impl is added. Now IntoCondition and Into<Condition> are completely interchangable, but you can still use .into_condition() for readability.

// before
trait IntoCondition {
    fn into_condition(self) -> Condition;
}

// now
trait IntoCondition: Into<Condition> {
    fn into_condition(self) -> Condition {
        self.into()
    }
}

impl<T> IntoCondition for T where T: Into<Condition> {}

If you have manually implemented Into* traits, it may cause conflicts. You should rewrite your impls as as impl From<..> for TableRef.

Full list of changed traits:

• IntoColumnDef #975
• IntoColumnRef #959
• IntoCondition #939
• IntoIden #973
• IntoIndexColumn #976
• IntoLikeExpr #974
• IntoTableRef #958
• IntoTypeRef #969
• IntoValueTuple #960

Performance Improvements​

We benchmarked the query-building process - and found out that the bulk of the overhead came from serializing queries into strings, not from the AST building. By optimizing the string handling part of the serialization process, we improved the query-building performance by up to 15%!

Replaced write! with write_str​

#947 This simple but not-so-obvious change by far contributed the biggest gain.

We won't go into the details here, as there are two tracking issues in rust-lang:

• format_args! is slow rust/#76490
• Tracking issue for improving std::fmt::Arguments and format_args!() rust/#99012

// before
write!(
    sql,
    "CONSTRAINT {}{}{} ",
    self.quote().left(),
    name,
    self.quote().right()
);

// now
sql.write_str("CONSTRAINT ");
sql.write_char(self.quote().left());
sql.write_str(name);
sql.write_char(self.quote().right());
sql.write_str(" ");

Refactored Writer to avoid string allocation​

#945 Less strings is better!

// before: an intermediate string is allocated
let value: String = self.value_to_string(value);
write!(sql, "{value}");

// now: write to the buffer directly
self.write_value(sql, value);

fn write_value(&self, sql: &mut dyn Write, value: &Value);

Refactored Tokenizer to avoid string allocation​

#952 Note that the tokenizer is not part of the runtime query-building code path, but still worth mentioning.

// before
enum Token {
    Quoted(String),
    Unquoted(String),
    Space(String),
    Punctuation(String),
}

// now
enum Token<'a> {
    Quoted(&'a str),
    Unquoted(&'a str),
    Space(&'a str),
    Punctuation(&'a str),
}

Release Plan​

SeaQuery 1.0 is currently an rc release, and we plan to finalize it soon - meaning no more major breaking changes. If you feel adventurous or want to use some of the latest features, you can upgrade today. Please let us know the problems you faced, this will help us and the community. If you have ideas / feedback please join the discussion on GitHub!

As SeaORM is based on top of SeaQuery, the breaking changes above would impact SeaORM users as well. We tried to minimize the impact to SeaORM users that use SeaQuery lightly and most migrations can be done mechanically.

After SeaQuery 1.0, it will be the most exciting release - SeaORM 2.0!

Our New Team​

SeaQuery 1.0 wouldn't have happened without two contributors who joined us recently - Dmitrii Aleksandrov and Huliiiiii. They've made huge contributions that helped define this release, and we're super grateful for the effort and care they've poured into the project.

Chris Tsang

Maintainer

Dmitrii Aleksandrov

Maintainer

Huliiiiii

Contributor

Sponsors​

If you feel generous, a small donation will be greatly appreciated, and goes a long way towards sustaining the organization.

Gold Sponsor​

QDX pioneers quantum dynamics–powered drug discovery, leveraging AI and supercomputing to accelerate molecular modeling. We're grateful to QDX for sponsoring the development of SeaORM, the SQL toolkit that powers their data intensive applications.

GitHub Sponsors​

A big shout out to our GitHub sponsors 😇:

Subscribe Pro

Variant9

vealth

AurLemon

Ryan Swart

OteroRafael

Yuta Hinokuma

wh7f

MS

Numeus

Data Intuitive

Caido Community

Marcus Buffett

MasakiMiyazaki

KallyDev

Manfred Lee

Afonso Barracha

Dean Sheather

Rustacean Sticker Pack 🦀​

The Rustacean Sticker Pack is the perfect way to express your passion for Rust. Our stickers are made with a premium water-resistant vinyl with a unique matte finish. Stick them on your laptop, notebook, or any gadget to show off your love for Rust!

Moreover, all proceeds contributes directly to the ongoing development of SeaQL projects.

Sticker Pack Contents:

• Logo of SeaQL projects: SeaQL, SeaORM, SeaQuery, Seaography, FireDBG
• Mascot of SeaQL: Terres the Hermit Crab
• Mascot of Rust: Ferris the Crab
• The Rustacean word

Support SeaQL and get a Sticker Pack!

You enjoy writing raw SQL queries, SeaQuery just made it better!

We've created a new raw_query! macro with neat features to make writing raw SQL queries more ergononmic.

The biggest headache when crafting complex queries is parameter binding, whether you use ? or $N assigning parameters manually is laborious and error-prone.

SeaQuery's new raw_query! macro is designed to solve this exact problem.

Gist​

let a = 1;
struct B { b: i32 }
let b = B { b: 2 };
let c = "A";
let d = vec![3, 4, 5];

let query = sea_query::raw_query!(
    PostgresQueryBuilder,
    r#"SELECT ("size_w" + {a}) * {b.b} FROM "glyph"
       WHERE "image" LIKE {c} AND "id" IN ({..d})"#
);

assert_eq!(
    query.sql,
    r#"SELECT ("size_w" + $1) * $2 FROM "glyph"
       WHERE "image" LIKE $3 AND "id" IN ($4, $5, $6)"#
);
assert_eq!(
    query.values,
    Values(vec![1.into(), 2.into(), "A".into(), 3.into(), 4.into(), 5.into()])
);

There are several features packed into the snippet above!

Let's have a quick overview and we'll dive into the details:

1. named parameter: {a} injected
2. nested parameter access: {b.b} inner access
3. array expansion: {..d} expanded into three parameters

There are two more features that will be showcased later:

4. tuple expansion: {values.0:2}
5. repeating group: {..(values.0),}

Motivation​

While SeaQuery has long offered a way to build dynamic queries solving basically the same problem, not every one liked SeaQuery's code structure.

I recently came across this SQLx issue, and was enticed to solve it.

I am heavily inspired by Rust's format! macro and other languages' template literals for string interpolation, and figured it could probably be achieved in Rust as well!

Challenges​

First, the above cannot be implemented with standard macro_rules because of caller hygiene, such that expanded code cannot access the variables in the surrounding scope. But proc macros do not have this limitation.

Second, if we want to expand an array, the number of elements cannot be known until runtime. So a compile-time approach couldn't work.

Third, we have to support all of Rust's primitive and container types as well as third party data types.

Dive in​

Let's take a look at what the above code expands into!

let a = 1;
struct B {
    b: i32,
}
let b = B { b: 2 };
let c = "A";
let d = <[_]>::into_vec(#[rustc_box] ::alloc::boxed::Box::new([3, 4, 5]));

let query = {
    use sea_query::raw_sql::*;
    let mut builder = RawSqlQueryBuilder::new(sea_query::PostgresQueryBuilder);
    builder
        .push_fragment("SELECT (\"size_w\" + ")
        .push_parameters(1)
        .push_fragment(") * ")
        .push_parameters(1)
        .push_fragment(" FROM \"glyph\"\n        WHERE \"image\" LIKE ")
        .push_parameters(1)
        .push_fragment(" AND \"id\" IN (")
        .push_parameters((&d).len())
        .push_fragment(")");
    let sql = builder.finish();
    let mut query = seaql::query(&sql);
    query = query.bind(&a);
    query = query.bind(&b.b);
    query = query.bind(&c);
    for v in (&d).iter() {
        query = query.bind(v);
    }
    query
};

I created RawSqlQueryBuilder that is somewhat similar to SeaQuery's query building backend. It serializes the query string in a single pass.

The derive macro first tokenize and parse the raw SQL to identify the splice points, and then call a special method push_parameters to push a variable number of parameters. This can be numbered, i.e. for Postgres.

After finishing the SQL building part, it then proceeds to bind the parameters. This mechanism is designed around SQLx's query API.

If the variable has a spread .. operator, we'd loop over it and bind all items.

Other ideas​

Originally I wanted to implement auto-expansion, meaning if the parameter is a container type, we'd expand it automagically. But there are quite a few special cases - most importantly Vec<u8> is used as bytes and should be bound as a single parameter. Moreover, in Postgres you can use arrays directly, and so we can't decide whether to expand.

More goodies​

Tuple expansion​

There are two more features that'll make your life much easier.

let var = (1, "2".to_owned(), 3);

let query = sea_query::raw_query!(
    PostgresQueryBuilder,
    "SELECT {var.0}, {var.1}, {var.2}"
);

let new_query = sea_query::raw_query!(
    PostgresQueryBuilder,
    r#"SELECT {var.0:2}"#
);

assert_eq!(query, new_query);

We can already support accessing tuple members, why not offer a range operator?

The : token is chosen because it somewhat resembles the Python operator. [0:2] is un-natural because tuple members in Rust can only be accessed by .0. Feel free to offer your thoughts!

It's not possible to automatically expand a tuple like an array because its arity (the number of elements) is not known at the time the macro is expanded. If the tuple consists of elements with a uniform type, it can be made iterable like a vector by implementing the appropriate traits. However, that approach doesn't apply in the case above, where the tuple's structure is not uniform.

You can do inserts with this:

let values = (1, "2", 3);

let query = sea_query::raw_query!(
    MysqlQueryBuilder,
    "INSERT INTO `glyph` (`aspect`, `image`, `font_size`) VALUES ({values.0:2})"
);
assert_eq!(
    query.sql,
    "INSERT INTO `glyph` (`aspect`, `image`, `font_size`) VALUES (?, ?, ?)"
);
assert_eq!(query.values, Values(vec![1.into(), "2".into(), 3.into()]));

You may ask, then how do we insert multiple elements? Which brings us to the next feature:

Repeating Group​

let values = vec![(1, "2", 3), (4, "5", 6)];

let query = sea_query::raw_query!(
    PostgresQueryBuilder,
    r#"INSERT INTO "glyph" ("aspect", "image", "font_size")
       VALUES {..(values.0:2),}"#
);
assert_eq!(
    query.sql,
    r#"INSERT INTO "glyph" ("aspect", "image", "font_size")
       VALUES ($1, $2, $3), ($4, $5, $6)"#
);

This syntax almost looks like regex now. Please let me explain:

It's expanded upon the previous example, in which values.0:2 means tuple expansion. We want to repeat this tuple as a group, surrounded by parenthesis, so we wrap it with (). Then we apply the same spread operator .. to expand the vector of tuples. Finally, the trailing , means they should be connected with ,.

This repeating group is not fully-generalized yet, but is quite flexible:

struct Item {
    a: i32,
    b: String,
    c: u16,
}

let values = vec![
    Item { a: 1, b: "2".to_owned(), c: 3 },
    Item { a: 4, b: "5".to_owned(), c: 6 },
];

let query = sea_query::raw_query!(
    PostgresQueryBuilder,
    r#"INSERT INTO "glyph" ("aspect", "image", "font_size")
       VALUES {..(values.a, values.b, values.c),}"#
);

This is equivalent to the previous example.

SQLx Integration​

SeaQuery offers tight SQLx integration. So in practice you can do (requires sqlx-utils feature flag):

let mut sql;
let res = sea_query::sqlx::sqlite::query!(
    sql = r#"INSERT INTO "character"
             ("uuid", "font_size", "character")
             VALUES {..(values.0:2),}"#
).execute(pool).await?;

Note the salient sql variable. SQLx's Query object can only borrow the SQL as &str, and so someone has to own the String. I couldn't think of a better API, suggestions welcome.

It calls the underlying Query's bind method directly, so no extra copy is involved. This is the lowest possible overhead!

One final example:

let mut character = Character { id: 1, font_size: 0 };
character.font_size = 18;

let res = sea_query::sqlx::sqlite::query!(
    sql = r#"UPDATE "character"
             SET "font_size" = {character.font_size}
             WHERE "id" = {character.id}"#
).execute(pool).await?;

Full example can be found here.

It almost feels like a mini ORM ... although SeaORM 🐚 is still highly recommended by us!

SeaQuery 1.0​

This is just one of many new features we've added while preparing SeaQuery 1.0. This is currently an rc release, if you have ideas please join the discussion.

Sponsor​

If you feel generous, a small donation will be greatly appreciated, and goes a long way towards sustaining the organization.

A big shout out to our GitHub sponsors 😇:

Gold Sponsor​

QDX pioneers quantum dynamics–powered drug discovery, leveraging AI and supercomputing to accelerate molecular modeling. We're grateful to QDX for sponsoring the development of SeaORM, the SQL toolkit that powers their data intensive applications.

Rustacean Sticker Pack 🦀​

The Rustacean Sticker Pack is the perfect way to express your passion for Rust. Our stickers are made with a premium water-resistant vinyl with a unique matte finish. Stick them on your laptop, notebook, or any gadget to show off your love for Rust!

Moreover, all proceeds contributes directly to the ongoing development of SeaQL projects.

Sticker Pack Contents:

• Logo of SeaQL projects: SeaQL, SeaORM, SeaQuery, Seaography, FireDBG
• Mascot of SeaQL: Terres the Hermit Crab
• Mascot of Rust: Ferris the Crab
• The Rustacean word

Support SeaQL and get a Sticker Pack!

This blog post summarizes the new features and enhancements introduced in SeaORM 1.1:

• 2025-03-30 1.1.8
• 2025-04-14 1.1.9
• 2025-04-14 1.1.10
• 2025-05-07 1.1.11
• 2025-05-27 1.1.12

New Features​

Implement DeriveValueType for enum strings​

DeriveValueType now supports enum types. It offers a simpler alternative to DeriveActiveEnum for client-side enums backed by string database types.

#[derive(DeriveValueType)]
#[sea_orm(value_type = "String")]
pub enum Tag {
    Hard,
    Soft,
}

// `from_str` defaults to `std::str::FromStr::from_str`
impl std::str::FromStr for Tag {
    type Err = sea_orm::sea_query::ValueTypeErr;
    fn from_str(s: &str) -> Result<Self, Self::Err> { .. }
}

// `to_str` defaults to `std::string::ToString::to_string`.
impl std::fmt::Display for Tag {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result { .. }
}

The following trait impl are generated, removing the boilerplate previously needed:

#[automatically_derived]
impl std::convert::From<Tag> for sea_orm::Value {
    fn from(source: Tag) -> Self {
        std::string::ToString::to_string(&source).into()
    }
}

#[automatically_derived]
impl sea_orm::TryGetable for Tag {
    fn try_get_by<I: sea_orm::ColIdx>(res: &sea_orm::QueryResult, idx: I)
        -> std::result::Result<Self, sea_orm::TryGetError> {
        let string = String::try_get_by(res, idx)?;
        std::str::FromStr::from_str(&string).map_err(|err| sea_orm::TryGetError::DbErr(sea_orm::DbErr::Type(format!("{err:?}"))))
    }
}

#[automatically_derived]
impl sea_orm::sea_query::ValueType for Tag {
    fn try_from(v: sea_orm::Value) -> std::result::Result<Self, sea_orm::sea_query::ValueTypeErr> {
        let string = <String as sea_orm::sea_query::ValueType>::try_from(v)?;
        std::str::FromStr::from_str(&string).map_err(|_| sea_orm::sea_query::ValueTypeErr)
    }

    fn type_name() -> std::string::String {
        stringify!(Tag).to_owned()
    }

    fn array_type() -> sea_orm::sea_query::ArrayType {
        sea_orm::sea_query::ArrayType::String
    }

    fn column_type() -> sea_orm::sea_query::ColumnType {
        sea_orm::sea_query::ColumnType::String(sea_orm::sea_query::StringLen::None)
    }
}

#[automatically_derived]
impl sea_orm::sea_query::Nullable for Tag {
    fn null() -> sea_orm::Value {
        sea_orm::Value::String(None)
    }
}