sea-query

Release Notes: sea-query 1.0.2

(since 1.0.1)

New Features

clear_group_by() and clear_having() on SelectStatement (#1086)

SelectStatement now has methods to clear a previously set GROUP BY or HAVING clause, which is useful when a query is being modified dynamically.

• clear_group_by(): clears all group by expressions.
• clear_having(): clears the having condition.

let query = Query::select()
    .from(Char::Table)
    .column(Char::Character)
    .add_group_by([Expr::col(Char::SizeW).into()])
    .clear_group_by()
    .to_owned();
// SELECT `character` FROM `character`

Fixes

sea_value_to_json_value no longer converts a null date/time value to the string "NULL" (#1097)

A None date/time Value was passed through value_to_string, which produced the literal string "NULL" and serialized it as Json::String("NULL") instead of Json::Null. Null date/time values now correctly convert to Json::Null, and non-null values are still rendered as their string representation. This affects with-chrono, with-time, and with-jiff date/time variants.

Compatibility Notes

This is a patch release of sea-query. It adds new SelectStatement methods and fixes JSON conversion of null date/time values; it does not intentionally change generated SQL for existing queries.

sea-query-derive remains on 1.0.0; no derive crate release is needed for this change.

• 1.0.2 - clear_group_by() / clear_having(), fix null date/time JSON conversion

• 1.0.1 - Borrowed scalar primitive conversions for Value

(since 1.0.0-rc.34)

Highlights

• Published the stable sea-query 1.0.0 release.
• Published the companion crates as final non-rc versions.
• Updated the SQLx binder crate for SQLx 0.9.
• Fixed PostgreSQL array handling for null array elements.
• Fixed tokenizer handling of backend-specific backslash escaping.
• Updated README and examples from rc dependencies to the stable 1.0 dependency line.

Breaking Changes

• Updated sea-query-sqlx to SQLx 0.9 #1075 #1076

The SQLx binder line for SeaQuery 1.0 now targets SQLx 0.9. Users staying on SQLx 0.8 should use sea-query-sqlx 0.8.1; users on SQLx 0.9 should use sea-query-sqlx 0.9.0.

# SQLx 0.8 compatibility line
sea-query-sqlx = "0.8.1"

# SQLx 0.9 line
sea-query-sqlx = "0.9.1"

SQLx feature wiring was updated for the SQLx 0.9 runtime and TLS feature split. SeaQuery now exposes explicit TLS feature flags such as tls-none, tls-native-tls, tls-rustls, and the rustls provider variants. The older combined runtime/TLS feature names remain available as compatibility aliases.

• Temporarily gated Jiff SQLx binders on SQLx 0.9

Jiff SQLx binders were temporarily disabled because jiff-sqlx still targeted SQLx 0.8 at the time of the SeaQuery 1.0.0 release. Enabling Jiff values with SQLx 0.9 may panic for unsupported Jiff argument binding.

Stable Crate Versions

Use these stable companion crate versions with sea-query 1.0.0:

• sea-query 1.0.0
• sea-query-derive 1.0.0
• sea-query-rusqlite 0.8.0
• sea-query-postgres 0.6.0
• sea-query-diesel 0.3.0
• sea-query-rbatis 0.2.0

sea-query = "1.0"
sea-query-derive = "1.0"
sea-query-rusqlite = "0.8"
sea-query-postgres = "0.6"
sea-query-diesel = "0.3"
sea-query-rbatis = "0.2"

Fixes

• Fixed null element handling in PostgreSQL arrays #1068

PostgreSQL array values can now contain null elements without panicking during conversion. This fixes Value::Array conversion paths where arrays contain null values.

• Fixed tokenizer handling of backend-specific backslash escaping

The tokenizer now distinguishes MySQL, PostgreSQL, and SQLite string escaping rules. In particular, PostgreSQL backslash escaping is handled only for escape strings, avoiding incorrect parsing around single quotes and backslashes.

Dependency Updates

• Removed the inherent dependency from the main crate.
• Aligned ipnetwork to 0.21.1 across SeaQuery and binder crates.
• Updated sea-query-postgres-types to 0.1.1.
• Raised binder crate Rust versions to Rust 1.88.0 where needed.

Documentation And Tests

• Updated README installation snippets from rc versions to stable versions.
• Updated SQLx examples for MySQL, PostgreSQL, and SQLite.
• Added regression coverage for null PostgreSQL array elements.
• Added tokenizer tests for backend-specific backslash escaping.

Migration Notes

• Use sea-query-sqlx 0.8.1 if your application still depends on SQLx 0.8.
• Use sea-query-sqlx 0.9.0 with SQLx 0.9.
• Review SQLx feature flags if your application used the old combined runtime/TLS feature names.
• Avoid enabling Jiff SQLx binding with SQLx 0.9 until jiff-sqlx support is available for SQLx 0.9.

• 1.0.0 - Stable SeaQuery 1.0 release notes

Release Candidates

• 1.0.0-rc.34 — Table partitioning, Jiff binder support, JSON_TABLE refactor
• 1.0.0-rc.33 — Value::Enum, Postgres advisory locks, SelectExprTrait
• 1.0.0-rc.32 — EXPLAIN, FILTER on aggregates, ALTER TABLE DROP CONSTRAINT
• 1.0.0-rc.31 — SELECT INTO, eq_any/ne_all, Value::array_type
• 1.0.0-rc.30 — Tokenizer comment parsing, dependency reductions
• 1.0.0-rc.29 — Legacy serial option for Postgres
• 1.0.0-rc.28 — Restore Value system
• 1.0.0-rc.27 — Revert impl Iden for String
• 1.0.0-rc.26 — From<Vec<Value>> for Array, FromIterator<T> for Array
• 1.0.0-rc.24 — Array API redesign
• 1.0.0-rc.23 — Value::Enum, ON CONFLICT ON CONSTRAINT, CTE VALUES clause
• 1.0.0-rc.22 — Index operator class, DROP COLUMN IF EXISTS, nullable values
• 1.0.0-rc.20 — ValueTupleIter, SQLite decimal fix

New features

• Support EXPLAIN statements https://github.com/SeaQL/sea-query/pull/1044
• #![forbid(unsafe_code)] in all workspace crates https://github.com/SeaQL/sea-query/pull/930
• Unify Expr and SimpleExpr as one type. SimpleExpr is kept as an alias of Expr, but they can now be used interchangeably. There may be a few compile errors and some clippy warnings, basically just remove the redundant .into() https://github.com/SeaQL/sea-query/pull/889

pub type SimpleExpr = Expr; // !
impl From<Expr> for SimpleExpr { .. } // now removed

• New Iden type system. Previously, DynIden is an alias to SeaRc<dyn Iden>, and is lazily rendered. Now, it's an Cow<'static, str>, and is eagerly rendered. SeaRc is no longer an alias to Rc / Arc, now is only a unit struct. As such, Send / Sync is no longer needed. It's still possible to dynamically serialize a String as identifier, see example usage. https://github.com/SeaQL/sea-query/pull/909

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

• Reworked TableRef and ColumnRef variants https://github.com/SeaQL/sea-query/pull/927

// 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);

// 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);

Enhancements

• Supports Jiff types except jiff::Zoned. At the moment this support is only available through the SQLx binder. When using SQLx with multiple backends, enable unimplemented-jiff-sqlx-mysql to suppress the compile-time error. After enabling it, runtime panics may occur.
• Add Expr::not_exists https://github.com/SeaQL/sea-query/pull/983
• Add serde feature. Currently, enabling it allows Value to be serializable https://github.com/SeaQL/sea-query/pull/966
• Add Keyword::Default https://github.com/SeaQL/sea-query/pull/965
• Enable clippy::nursery https://github.com/SeaQL/sea-query/pull/938
• Removed unnecessary 'static bounds from type signatures https://github.com/SeaQL/sea-query/pull/921
• cast_as_quoted now allows you to qualify the type name. https://github.com/SeaQL/sea-query/pull/922

let query = Query::select()
    .expr(Func::cast_as_quoted("hello", ("MySchema", "MyType")))
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT CAST('hello' AS "MySchema"."MyType")"#
);

• Most Value variants are now unboxed (except BigDecimal and Array). Previously the size is 24 bytes. https://github.com/SeaQL/sea-query/pull/925

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

• Merged Func/Function and PgFunc/PgFunction. Now the latter is just an alias of the former https://github.com/SeaQL/sea-query/pull/944

// old
condition.add(Func::lower(Expr::col(column)).eq(SimpleExpr::FunctionCall(Func::lower(value))))
// new
condition.add(Func::lower(Expr::col(*column)).eq(Func::lower(value)));

• impl From<Expr> for Condition. Now you can use Expr instead of ConditionExpression, which has been removed from the public API https://github.com/SeaQL/sea-query/pull/915

Cond::all().add(ConditionExpression::Expr(Expr::new(..))) // old
Cond::all().add(Expr::new(..))                            // new

• Replaced serial with GENERATED BY DEFAULT AS IDENTITY (Postgres) https://github.com/SeaQL/sea-query/pull/918 To restore legacy behaviour, you can enable the option-postgres-use-serial feature flag

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 needed to support legacy system 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(" ")
);

Breaking Changes

• Removed inherent SimpleExpr methods that duplicate ExprTrait. If you encounter the following error, please add use sea_query::ExprTrait in scope https://github.com/SeaQL/sea-query/pull/890

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`

• Added non_exhaustive to AST enums. It allows us to add new features and extend the AST without breaking the API. If you encounter the following error, please add a wildcard match _ => {..} https://github.com/SeaQL/sea-query/pull/891

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!(),

• ExprTrait::eq collided with std::cmp::Eq. If you encounter the following error, please use std::cmp::PartialEq::eq(a, b) or sea_query::ExprTrait::eq(a, b) explicitly https://github.com/SeaQL/sea-query/pull/890

error[E0308]: mismatched types
    |
    |     fn eq(&self, other: &Self) -> bool {
    |                                   ---- expected `bool` because of return type
    |         format!("{:?}", self.0).eq(&format!("{:?}", other.0))
    |         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ expected `bool`, found `Expr`

For more information about this error, try `rustc --explain E0308`.
error: could not compile `seaography` (lib) due to 1 previous error

• The method signature of Iden::unquoted is changed. If you're implementing Iden manually, you can modify it like below https://github.com/SeaQL/sea-query/pull/909

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();
    }
}

• Removed ConditionExpression from the public API. Instead, just convert between Condition and Expr using From/Into https://github.com/SeaQL/sea-query/pull/915

error[E0603]: enum `ConditionExpression` is private
   --> tests/mysql/query.rs:734:20
    |
 >  |     use sea_query::ConditionExpression;
    |                    ^^^^^^^^^^^^^^^^^^^ private enum
 >  |     Cond::all().add(ConditionExpression::Expr(Expr::new(
    |                     ^^^^^^^^^^^^^^^^^^^ use of undeclared type `ConditionExpression`

Simply do the following:

Cond::all().add(Expr::new(..))

• Reworked ColumnRef variants may cause compile error.

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")

• Reworked TableRef variants may cause compile error.

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(),

• Replace dyn <Trait> with impl <Trait> https://github.com/SeaQL/sea-query/pull/982 This gained us up to 10% performance, however it does mean dyn QueryBuilder is no longer possible.

Minor breaking changes

• Changed Into* traits (like IntoCondition) to be defined as trait IntoCondition: Into<Condition> and implemented for all T: Into<Condition>. Now IntoCondition and Into<Condition> are completely interchangable. But you can still use .into_condition() for readability.

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

  Full list of changed traits:

  • IntoColumnDef https://github.com/SeaQL/sea-query/pull/975
  • IntoColumnRef https://github.com/SeaQL/sea-query/pull/959
  • IntoCondition https://github.com/SeaQL/sea-query/pull/939
  • IntoIden https://github.com/SeaQL/sea-query/pull/973
  • IntoIndexColumn https://github.com/SeaQL/sea-query/pull/976
  • IntoLikeExpr https://github.com/SeaQL/sea-query/pull/974
  • IntoTableRef https://github.com/SeaQL/sea-query/pull/958
  • IntoTypeRef https://github.com/SeaQL/sea-query/pull/969
  • IntoValueTuple https://github.com/SeaQL/sea-query/pull/960

• Unboxed Value variants may cause compile error. Simply remove the Box in these cases https://github.com/SeaQL/sea-query/pull/925

error[E0308]: mismatched types
   --> /home/runner/.cargo/registry/src/index.crates.io-1949cf8c6b5b557f/sea-schema-0.17.0-rc.3/src/sqlite/def/table.rs:248:59
    |
 >  | Value::String(Some(Box::new(string_value.to_string()))));
    |               ---- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ expected `String`, found `Box<String>`
    |               |
    |               arguments to this enum variant are incorrect

• Blanket-implemented SqliteExpr and PgExpr for T where T: ExprTrait https://github.com/SeaQL/sea-query/pull/914

  Now you can use database-specific operators with all expression types. If you had custom implementations in your own code, some may no longer compile and may need to be deleted.

• Replaced ColumnSpec::Check(Expr) with ColumnSpec::Check(Check) to support named check constraints https://github.com/SeaQL/sea-query/pull/920

• SelectStatement::cross_join no longer accepts a condition https://github.com/SeaQL/sea-query/pull/956

• Turned TypeRef from an enum into a struct that reuses TableName. https://github.com/SeaQL/sea-query/pull/969

• Changed Expr::TypeName(DynIden) to Expr::TypeName(TypeRef), which can be qualified.

  If you manually construct this variant and it no longer compiles, just add .into().

• Renamed QueryBuilder::prepare_simple_expr to prepare_expr https://github.com/SeaQL/sea-query/pull/988

• Changed signature of Expr::Custom https://github.com/SeaQL/sea-query/pull/940

enum Expr {
  - Custom(String),
  + Custom(Cow<'static, str>),
}

fn cust<T>(s: T) -> Self
where
  - T: Into<String>,
  + T: Into<Cow<'static, str>>,
{
    Self::Custom(s.into())
}

You many encounter the following error:

    |         let sql = self.sql.trim();
    |                   ^^^^^^^^ borrowed value does not live long enough
...
    |             Expr::cust_with_values(sql, values.0)
    |             ------------------------------------- argument requires that `self.stmt.sql` is borrowed for `'static`

Simply convert the &str to String:

let sql = self.sql.trim().to_owned();

Bug Fixes

• Removed invalid condition requirement on SelectStatement::cross_join https://github.com/SeaQL/sea-query/pull/956

Upgrades

• Upgraded to Rust Edition 2024 https://github.com/SeaQL/sea-query/pull/885

Maintainence

• sea-query-binder has been superseded with sea-query-sqlx

Highlights

• Added CREATE TABLE partitioning support for PostgreSQL and MySQL.
• Added SQLx and rusqlite binder support for Jiff values.
• Fixed PostgreSQL unsigned integer column mappings to avoid overflow-prone type choices.
• Fixed MySQL IndexCreateStatement::cond_where behavior so partial index filters are no longer silently dropped.
• Refactored the PostgreSQL JSON_TABLE builder API.

Breaking Changes

• Changed PostgreSQL type mapping for unsigned integer columns to use wider signed integer types #1028

PostgreSQL does not have unsigned integer types. SeaQuery now maps unsigned column types to signed types that can represent a wider range:

This affects generated PostgreSQL schema SQL. Existing migrations remain unchanged, but newly generated migrations may produce different column types for SmallUnsigned and Unsigned.

• MySQL index creation now emits cond_where filters instead of silently dropping them #1067

MySQL does not support partial indexes. SeaQuery now keeps the WHERE clause when IndexCreateStatement::cond_where is used with MysqlQueryBuilder, causing the database to reject the statement instead of silently creating an index with different semantics.

• Removed jiff::Zoned support from the with-jiff value API #1061

The Jiff value mapping now excludes jiff::Zoned, because the SQLx Jiff binder does not support lossless Zoned values. Code using Value::jiff_zoned, Value::JiffZoned, ArrayType::JiffZoned, is_jiff_zoned, or as_ref_jiff_zoned should migrate to a supported Jiff type or store zoned datetimes as an application-defined text value.

• Changed Jiff datetime type mappings #1061

Jiff datetime mappings now align with SQLx binder support:

• Refactored PgFunc::json_table builder APIs #1029

The PostgreSQL JSON_TABLE API now uses value objects for columns and nested paths instead of chained sub-builders:

explicit_path was removed. Nested paths now render as NESTED PATH ....

New Features

• Added table partitioning support for CREATE TABLE #1039

PostgreSQL support includes:

• PARTITION BY RANGE
• PARTITION BY LIST
• PARTITION BY HASH
• PARTITION OF
• FOR VALUES IN
• FOR VALUES FROM ... TO
• FOR VALUES WITH

MySQL support includes:

• PARTITION BY RANGE
• PARTITION BY LIST
• PARTITION BY HASH
• PARTITION BY KEY
• Partition definitions with VALUES IN and VALUES LESS THAN

PartitionDefinition is kept crate-private; users create partition definitions through TableCreateStatement::add_partition.

• Added SQLx and rusqlite binder support for Jiff values, except jiff::Zoned #1061

Supported Jiff values:

sea-query-sqlx intentionally rejects with-jiff together with sqlx-mysql or sqlx-any unless the unimplemented-jiff-sqlx-mysql feature is enabled to acknowledge the limitation.

Fixes

• Wrapped PostgreSQL ON CONFLICT index expressions in parentheses where required by PostgreSQL syntax #1055

For example, SeaQuery now renders:

ON CONFLICT ("name", ("variant" IS NULL)) DO NOTHING

instead of:

ON CONFLICT ("name", "variant" IS NULL) DO NOTHING

This matches PostgreSQL's conflict_target grammar for index expressions.

• Fixed MySQL partial index filters being silently ignored #1067

IndexCreateStatement::cond_where now renders the filter for both standalone MySQL CREATE INDEX statements and inline indexes inside CREATE TABLE.

Documentation And Tests

• Added documentation for the unsigned integer PostgreSQL type mapping changes.
• Added table partitioning doctests and backend-specific coverage for PostgreSQL and MySQL.
• Added focused PostgreSQL JSON_TABLE tests for the refactored API.
• Added documentation and regression tests for MySQL cond_where behavior on indexes.

Migration Notes

• Review newly generated PostgreSQL migrations for SmallUnsigned and Unsigned column type changes.
• Replace old PgFunc::json_table chained sub-builder calls with json_table::Column, json_table::ExistsColumn, and json_table::NestedPath.
• Replace any jiff::Zoned Value usage with supported Jiff types or application-level text storage.
• Treat MySQL partial indexes as unsupported. SeaQuery now emits invalid MySQL SQL intentionally when cond_where is used, so applications should branch by backend when they need a partial index on PostgreSQL or SQLite only.

New Features

• Add Value::Enum — a typed enum value that generates a literal cast in Postgres #1051

let value = sea_query::Enum {
    type_name: "FontSizeEnum".to_owned().into(),
    value: "large".into(),
};

assert_eq!(
    Query::insert()
        .into_table(Char::Table)
        .columns([Char::FontSize])
        .values_panic([Expr::val(value)])
        .to_string(PostgresQueryBuilder),
    r#"INSERT INTO "character" ("font_size") VALUES ('large'::"FontSizeEnum")"#
);

Arrays of enum values are also supported (requires postgres-array):

let value = Value::Array(
    ArrayType::Enum(Box::new("FontSizeEnum".to_owned().into())),
    Some(Box::new(vec![
        sea_query::Enum { type_name: "FontSizeEnum".to_owned().into(), value: "large".into() }.into(),
    ])),
);

// Generates: INSERT INTO "character" ("font_size") VALUES ($1::"FontSizeEnum"[])

• Add Postgres advisory lock functions #1062

assert_eq!(
    Query::select()
        .expr(PgFunc::advisory_lock(Expr::val(12345_i64)))
        .to_owned()
        .to_string(PostgresQueryBuilder),
    r#"SELECT PG_ADVISORY_LOCK(12345)"#
);

Full set of functions added: PgFunc::advisory_lock, advisory_lock_shared, try_advisory_lock, try_advisory_lock_shared, advisory_unlock, advisory_unlock_shared, advisory_unlock_all, advisory_xact_lock, advisory_xact_lock_shared, try_advisory_xact_lock, try_advisory_xact_lock_shared

• Add SelectExprTrait for ergonomic alias and window chaining #1040

// Attach alias directly on an expression
Query::select()
    .expr(Expr::col(Char::Character).alias("C"))
    .from(Char::Table);

// Chain window function inline
Query::select()
    .from(Char::Table)
    .expr(
        Expr::col(Char::Character)
            .max()
            .over(WindowStatement::partition_by(Char::FontSize))
            .alias("C"),
    );

// Reference a named window
Query::select()
    .from(Char::Table)
    .expr(Expr::col(Char::Character).max().over("w"))
    .window("w", WindowStatement::partition_by(Char::FontSize));

Enhancements

• Allow schema-referencing foreign keys in SQLite backend — the schema qualifier is stripped since SQLite foreign key syntax does not support it #1056
• Fix null array handling: Array::Null.is_empty() now correctly returns false; null and empty are distinct states #1034
• Rename Value::as_ref_array to Value::as_array; old name kept as a deprecated alias #1034
• Add PostgresValues::as_types() — returns the corresponding postgres::Type for each bound value #967

House Keeping

• Simplify FunctionCall::new(PgFunc::...) calls

New Features

• Support EXPLAIN statements #1044

// Postgres
assert_eq!(
    ExplainStatement::new()
        .analyze()
        .format(ExplainFormat::Json)
        .statement(
            Query::select()
                .column(Char::Character)
                .from(Char::Table)
                .to_owned(),
        )
        .to_string(PostgresQueryBuilder),
    r#"EXPLAIN (ANALYZE, FORMAT JSON) SELECT "character" FROM "character""#
);

// MySQL
assert_eq!(
    ExplainStatement::new()
        .format(ExplainFormat::Json)
        .statement(
            Query::select()
                .column(Char::Character)
                .from(Char::Table)
                .to_owned(),
        )
        .to_string(MysqlQueryBuilder),
    "EXPLAIN FORMAT = JSON SELECT `character` FROM `character`"
);

// SQLite
assert_eq!(
    ExplainStatement::new()
        .query_plan()
        .statement(
            Query::select()
                .column(Char::Character)
                .from(Char::Table)
                .to_owned(),
        )
        .to_string(SqliteQueryBuilder),
    r#"EXPLAIN QUERY PLAN SELECT "character" FROM "character""#
);

• Support for FILTER clause on aggregate functions #1043

let query = Query::select()
    .expr_as(
        Func::count(Expr::val(1))
            .filter(
                Cond::all()
                    .add(Expr::col(Char::Character).eq("foo"))
                    .add(Expr::col(Char::SizeW).eq(1))
            ),
        Alias::new("filtered_total")
    )
    .expr_as(Func::count(Expr::val(1)), Alias::new("total"))
    .from(Char::Table)
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT COUNT(1) FILTER (WHERE "character" = 'foo' AND "size_w" = 1) AS "filtered_total", COUNT(1) AS "total" FROM "character""#
);

• Add ALTER TABLE DROP CONSTRAINT

let table = Table::alter()
    .table(Font::Table)
    .drop_constraint("font_name_key")
    .to_owned();

assert_eq!(
    table.to_string(MysqlQueryBuilder),
    r#"ALTER TABLE `font` DROP CONSTRAINT `font_name_key`"#
);
assert_eq!(
    table.to_string(PostgresQueryBuilder),
    r#"ALTER TABLE "font" DROP CONSTRAINT "font_name_key""#
);

House Keeping

• Bump Rust version to 1.88
• Bump rusqlite to 0.38.0 #1059
• Update time to 0.3.47 #1057

• Implement SELECT INTO for Postgres #1021
• Added PgExpr eq_any and ne_all

Add option to use serial for Postgres primary key (legacy)

Enhancements

• Added ValueType::is_option

Bug Fixes

• Fix incorrect casting of ChronoDateTimeWithTimeZone in Value::Array #933
• Add missing parenthesis to WINDOW clause #919

SELECT .. OVER "w" FROM "character" WINDOW "w" AS (PARTITION BY "ww")

• Fix serializing iden as a value in ALTER TYPE ... RENAME TO ... statements #924

ALTER TYPE "font" RENAME TO "typeface"

• Fixed the issue where milliseconds were truncated when formatting Value::Constant #929

'2025-01-01 00:00:00.000000'
                    ^^^^^^^

Enhancements

• Added ValueType::is_option

Bug Fixes

• Fix incorrect casting of ChronoDateTimeWithTimeZone in Value::Array https://github.com/SeaQL/sea-query/pull/933
• Add missing parenthesis to WINDOW clause https://github.com/SeaQL/sea-query/pull/919

SELECT .. OVER "w" FROM "character" WINDOW "w" AS (PARTITION BY "ww")

• Fix serializing iden as a value in ALTER TYPE ... RENAME TO ... statements https://github.com/SeaQL/sea-query/pull/924

ALTER TYPE "font" RENAME TO "typeface"

• Fixed the issue where milliseconds were truncated when formatting Value::Constant https://github.com/SeaQL/sea-query/pull/929

'2025-01-01 00:00:00.000000'
                    ^^^^^^^

Enhancements

• impl From<Condition> and From<ConditionExpression> for SimpleExpr https://github.com/SeaQL/sea-query/pull/886

New features

• Support for creating functional indexes in Postgres and MySQL https://github.com/SeaQL/sea-query/pull/869

Enhancements

• Make RcOrArc a documented type alias instead of a direct reexport https://github.com/SeaQL/sea-query/pull/875
• Impl Iden for &'static str (don't wrap strings in Alias::new) https://github.com/SeaQL/sea-query/pull/882

New Features

• Added support for temporary tables https://github.com/SeaQL/sea-query/pull/878

let statement = Table::create()
    .table(Font::Table)
    .temporary()
    .col(
        ColumnDef::new(Font::Id)
            .integer()
            .not_null()
            .primary_key()
            .auto_increment()
    )
    .col(ColumnDef::new(Font::Name).string().not_null())
    .take();

assert_eq!(
    statement.to_string(MysqlQueryBuilder),
    [
        "CREATE TEMPORARY TABLE `font` (",
        "`id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,",
        "`name` varchar(255) NOT NULL",
        ")",
    ]
    .join(" ")
);

• Added Value::dummy_value

use sea_query::Value;
let v = Value::Int(None);
let n = v.dummy_value();
assert_eq!(n, Value::Int(Some(0)));

Bug Fixes

• Quote type properly in AsEnum casting https://github.com/SeaQL/sea-query/pull/880

let query = Query::select()
    .expr(Expr::col(Char::FontSize).as_enum(TextArray))
    .from(Char::Table)
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT CAST("font_size" AS "text"[]) FROM "character""#
);

New Features

• Support Update FROM .. https://github.com/SeaQL/sea-query/pull/861

let query = Query::update()
    .table(Glyph::Table)
    .value(Glyph::Tokens, Expr::column((Char::Table, Char::Character)))
    .from(Char::Table)
    .cond_where(
        Expr::col((Glyph::Table, Glyph::Image))
            .eq(Expr::col((Char::Table, Char::UserData))),
    )
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"UPDATE "glyph" SET "tokens" = "character"."character" FROM "character" WHERE "glyph"."image" = "character"."user_data""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"UPDATE "glyph" SET "tokens" = "character"."character" FROM "character" WHERE "glyph"."image" = "character"."user_data""#
);

• Support TABLESAMPLE (Postgres) https://github.com/SeaQL/sea-query/pull/865

use sea_query::extension::postgres::PostgresSelectStatementExt;

let query = Query::select()
    .columns([Glyph::Image])
    .from(Glyph::Table)
    .table_sample(SampleMethod::SYSTEM, 50.0, None)
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "image" FROM "glyph" TABLESAMPLE SYSTEM (50)"#
);

• Support ALTER COLUMN USING .. (Postgres) https://github.com/SeaQL/sea-query/pull/848

let table = Table::alter()
    .table(Char::Table)
    .modify_column(
        ColumnDef::new(Char::Id)
            .integer()
            .using(Expr::col(Char::Id).cast_as(Alias::new("integer"))),
    )
    .to_owned();

assert_eq!(
    table.to_string(PostgresQueryBuilder),
    [
        r#"ALTER TABLE "character""#,
        r#"ALTER COLUMN "id" TYPE integer USING CAST("id" AS integer)"#,
    ]
    .join(" ")
);

House Keeping

• Updated ordered-float to 4
• Updated thiserror to 2

New Features

• Added with_cte to use WITH clauses in all statements https://github.com/SeaQL/sea-query/pull/859

let select = SelectStatement::new()
    .columns([Glyph::Id, Glyph::Image, Glyph::Aspect])
    .from(Glyph::Table)
    .to_owned();
let cte = CommonTableExpression::new()
    .query(select)
    .table_name(Alias::new("cte"))
    .to_owned();
let select = SelectStatement::new()
    .columns([Glyph::Id, Glyph::Image, Glyph::Aspect])
    .from(Alias::new("cte"))
    .with_cte(cte)
    .to_owned();
assert_eq!(
    select.to_string(PostgresQueryBuilder),
    [
        r#"WITH "cte" AS"#,
        r#"(SELECT "id", "image", "aspect""#,
        r#"FROM "glyph")"#,
        r#"SELECT "id", "image", "aspect" FROM "cte""#,
    ]
    .join(" ")
);

Enhancements

• Added Expr::column https://github.com/SeaQL/sea-query/pull/852
• Added Postgres function DATE_TRUNC https://github.com/SeaQL/sea-query/pull/825
• Added INCLUDE clause for Postgres BTree index https://github.com/SeaQL/sea-query/pull/826

Bug Fixes

• Write empty Postgres array as '{}' https://github.com/SeaQL/sea-query/pull/854

New Features

• Added Value::as_null

let v = Value::Int(Some(2));
let n = v.as_null();

assert_eq!(n, Value::Int(None));

• Added bitwise and/or operators (bit_and, bit_or) https://github.com/SeaQL/sea-query/pull/841

let query = Query::select()
    .expr(1.bit_and(2).eq(3))
    .to_owned();

assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT (1 & 2) = 3"#
);

Enhancements

• Added GREATEST & LEAST function https://github.com/SeaQL/sea-query/pull/844
• Added ValueType::enum_type_name() https://github.com/SeaQL/sea-query/pull/836
• Removed "one common table" restriction on recursive CTE https://github.com/SeaQL/sea-query/pull/835

House keeping

• Remove unnecessary string hashes https://github.com/SeaQL/sea-query/pull/815

Releases

2024-08-09

• sea-query/0.32.0-rc.1
• sea-query-binder/0.7.0-rc.1
• sea-query-binder/0.7.0-rc.2
• sea-query-rusqlite/0.7.0-rc.1
• sea-query-postgres/0.5.0-rc.1

2024-10-05

• sea-query/0.32.0-rc.2
• sea-query-attr/0.1.3
• sea-query-derive/0.4.2
• sea-query-rusqlite/0.7.0-rc.2

New Features

• Construct Postgres query with vector extension https://github.com/SeaQL/sea-query/pull/774
  • Added postgres-vector feature flag
  • Added Value::Vector, ColumnType::Vector, ColumnDef::vector(), PgBinOper::EuclideanDistance, PgBinOper::NegativeInnerProduct and PgBinOper::CosineDistance

  assert_eq!(
      Query::select()
          .columns([Char::Character])
          .from(Char::Table)
          .and_where(
              Expr::col(Char::Character).eq(Expr::val(pgvector::Vector::from(vec![1.0, 2.0])))
          )
          .to_string(PostgresQueryBuilder),
      r#"SELECT "character" FROM "character" WHERE "character" = '[1,2]'"#
  );
  

• Added ExprTrait to unify Expr and SimpleExpr methods https://github.com/SeaQL/sea-query/pull/791
• Support partial index CREATE INDEX .. WHERE .. https://github.com/SeaQL/sea-query/pull/478

Enhancements

• Replace Educe with manual implementations https://github.com/SeaQL/sea-query/pull/817

sea-query-derive

• Merged #[enum_def] into sea-query-derive
• #[enum_def] now impl additional IdenStatic and AsRef<str> https://github.com/SeaQL/sea-query/pull/769

sea-query-attr

• Updated syn, heck and darling
• sea-query-attr is now deprecated

Upgrades

• Upgrade sqlx to 0.8 https://github.com/SeaQL/sea-query/pull/798
• Upgrade bigdecimal to 0.4 https://github.com/SeaQL/sea-query/pull/798
• Upgrade rusqlite to 0.32 https://github.com/SeaQL/sea-query/pull/802

Enhancements

• Derive Eq, Ord, Hash for Alias https://github.com/SeaQL/sea-query/pull/818
• Added Func::md5 function https://github.com/SeaQL/sea-query/pull/786
• Added Postgres Json functions JSON_BUILD_OBJECT and JSON_AGG https://github.com/SeaQL/sea-query/pull/787
• Added Postgres function ARRAY_AGG https://github.com/SeaQL/sea-query/pull/846
• Added Func::cast_as_quoted https://github.com/SeaQL/sea-query/pull/789
• Added IF NOT EXISTS to ALTER TYPE ADD VALUE https://github.com/SeaQL/sea-query/pull/803

Versions

• sea-query/0.31.0-rc.1: 2024-01-31
• sea-query/0.31.0-rc.4: 2024-02-02
• sea-query/0.31.0-rc.5: 2024-04-14
• sea-query/0.31.0-rc.6: 2024-05-03
• sea-query/0.31.0-rc.7: 2024-06-02
• sea-query/0.31.0-rc.8: 2024-06-19
• sea-query-binder/0.6.0-rc.1: 2024-01-31
• sea-query-binder/0.6.0-rc.2: 2024-04-14
• sea-query-binder/0.6.0-rc.3: 2024-06-19
• sea-query-binder/0.6.0-rc.4: 2024-06-25
• sea-query-binder/0.6.0: 2024-08-02
• sea-query-rusqlite/0.6.0-rc.1: 2024-02-19
• sea-query-rusqlite/0.6.0: 2024-08-02
• sea-query-attr/0.1.2: 2024-04-14
• sea-query-diesel/0.2.0: 2024-08-02

New Features

• Added table_name attribute to enum_def macro https://github.com/SeaQL/sea-query/pull/759
• Added ColumnType::Blob https://github.com/SeaQL/sea-query/pull/777

Breaking Changes

• Rework SQLite type mapping https://github.com/SeaQL/sea-query/pull/735

assert_eq!(
    Table::create()
        .table(Alias::new("strange"))
        .col(ColumnDef::new(Alias::new("id")).integer().not_null().auto_increment().primary_key())
        .col(ColumnDef::new(Alias::new("int1")).integer())
        .col(ColumnDef::new(Alias::new("int2")).tiny_integer())
        .col(ColumnDef::new(Alias::new("int3")).small_integer())
        .col(ColumnDef::new(Alias::new("int4")).big_integer())
        .col(ColumnDef::new(Alias::new("string1")).string())
        .col(ColumnDef::new(Alias::new("string2")).string_len(24))
        .col(ColumnDef::new(Alias::new("char1")).char())
        .col(ColumnDef::new(Alias::new("char2")).char_len(24))
        .col(ColumnDef::new(Alias::new("text_col")).text())
        .col(ColumnDef::new(Alias::new("json_col")).json())
        .col(ColumnDef::new(Alias::new("uuid_col")).uuid())
        .col(ColumnDef::new(Alias::new("decimal1")).decimal())
        .col(ColumnDef::new(Alias::new("decimal2")).decimal_len(12, 4))
        .col(ColumnDef::new(Alias::new("money1")).money())
        .col(ColumnDef::new(Alias::new("money2")).money_len(12, 4))
        .col(ColumnDef::new(Alias::new("float_col")).float())
        .col(ColumnDef::new(Alias::new("double_col")).double())
        .col(ColumnDef::new(Alias::new("date_col")).date())
        .col(ColumnDef::new(Alias::new("time_col")).time())
        .col(ColumnDef::new(Alias::new("datetime_col")).date_time())
        .col(ColumnDef::new(Alias::new("boolean_col")).boolean())
        .col(ColumnDef::new(Alias::new("binary2")).binary_len(1024))
        .col(ColumnDef::new(Alias::new("binary3")).var_binary(1024))
        .col(ColumnDef::new(Alias::new("binary4")).blob())
        .to_string(SqliteQueryBuilder),
    [
        r#"CREATE TABLE "strange" ( "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,"#,
            r#""int1" integer,"#,
            r#""int2" tinyint,"#,
            r#""int3" smallint,"#,
            r#""int4" bigint,"#,
            r#""string1" varchar,"#,
            r#""string2" varchar(24),"#,
            r#""char1" char,"#,
            r#""char2" char(24),"#,
            r#""text_col" text,"#,
            r#""json_col" json_text,"#,
            r#""uuid_col" uuid_text,"#,
            r#""decimal1" real,"#,
            r#""decimal2" real(12, 4),"#,
            r#""money1" real_money,"#,
            r#""money2" real_money(12, 4),"#,
            r#""float_col" float,"#,
            r#""double_col" double,"#,
            r#""date_col" date_text,"#,
            r#""time_col" time_text,"#,
            r#""datetime_col" datetime_text,"#,
            r#""boolean_col" boolean,"#,
            r#""binary2" blob(1024),"#,
            r#""binary3" varbinary_blob(1024),"#,
            r#""binary4" blob"#,
        r#")"#,
    ]
    .join(" ")
);

• MySQL money type maps to decimal
• MySQL blob types moved to sea_query::extension::mysql::MySqlType; ColumnDef::blob() now takes no parameters

assert_eq!(
    Table::create()
        .table(BinaryType::Table)
        .col(ColumnDef::new(BinaryType::BinaryLen).binary_len(32))
        .col(ColumnDef::new(BinaryType::Binary).binary())
        .col(ColumnDef::new(BinaryType::Blob).blob())
        .col(ColumnDef::new(BinaryType::TinyBlob).custom(MySqlType::TinyBlob))
        .col(ColumnDef::new(BinaryType::MediumBlob).custom(MySqlType::MediumBlob))
        .col(ColumnDef::new(BinaryType::LongBlob).custom(MySqlType::LongBlob))
        .to_string(MysqlQueryBuilder),
    [
        "CREATE TABLE `binary_type` (",
            "`binlen` binary(32),",
            "`bin` binary(1),",
            "`b` blob,",
            "`tb` tinyblob,",
            "`mb` mediumblob,",
            "`lb` longblob",
        ")",
    ]
    .join(" ")
);

• ColumnDef::binary() set column type as binary with default length of 1
• Removed BlobSize enum
• Added StringLen to represent length of var-char/binary

/// Length for var-char/binary; default to 255
#[derive(Debug, Default, Clone, Copy, PartialEq, Eq)]
pub enum StringLen {
    /// String size
    N(u32),
    Max,
    #[default]
    None,
}

• ValueType::columntype() of Vec<u8> maps to VarBinary(StringLen::None)
• ValueType::columntype() of String maps to String(StringLen::None)
• ColumnType::Bit maps to bit for Postgres
• ColumnType::Binary and ColumnType::VarBinary map to bytea for Postgres
• Value::Decimal and Value::BigDecimal bind as real for SQLite
• ColumnType::Year(Option<MySqlYear>) changed to ColumnType::Year

Enhancements

• Added IntoColumnDef trait, allowing &mut ColumnDef / ColumnDef as argument
• Added ColumnType::string() and ColumnType::var_binary() as shim for old API
• Added ON DUPLICATE KEY DO NOTHING polyfill for MySQL https://github.com/SeaQL/sea-query/pull/765
• Added non-TLS runtime https://github.com/SeaQL/sea-query/pull/783

House keeping

• Added ColumnType mapping documentation
• Replace derivative with educe https://github.com/SeaQL/sea-query/pull/763

Upgrades

• Upgrade rusqlite to 0.31 https://github.com/SeaQL/sea-query/pull/755
• Upgrade time to 0.3.36 https://github.com/SeaQL/sea-query/pull/788

Enhancements

• Added SelectStatement::apply https://github.com/SeaQL/sea-query/pull/730

House keeping

• Slight refactors and documentation update

House keeping

• Fix clippy warnings on Rust 1.75 https://github.com/SeaQL/sea-query/pull/729

New Features

• Added feature flag option-more-parentheses to have more parentheses in expressions https://github.com/SeaQL/sea-query/pull/723
• Added feature flag option-sqlite-exact-column-type to only use integer for SQLite
• Support COUNT(DISTINCT "column") https://github.com/SeaQL/sea-query/pull/700
• Support index hints for MySQL (via extension::mysql::MySqlSelectStatementExt) https://github.com/SeaQL/sea-query/pull/636
• Support expressions for ON CONFLICT targets https://github.com/SeaQL/sea-query/pull/692

Enhancements

• Add from_clear to allow emptying current from tables in select statement https://github.com/SeaQL/sea-query/pull/716

Breaking Changes

• Caution: do not use the --all-features param in Cargo. If you want to enable all features, use the all-features feature flag instead.