What's new in SeaQuery 0.27.0

🎉 We are pleased to release SeaQuery 0.27.0! Here are some feature highlights 🌟:

Dependency Upgrade

[#356] We have upgraded a major dependency:

• Upgrade sqlx to 0.6.1

You might need to upgrade the corresponding dependency in your application as well.

Drivers support

We have reworked the way drivers work in SeaQuery: priori to 0.27.0, users have to invoke the sea_query_driver_* macros. Now each driver sqlx, postgres & rusqlite has their own supporting crate, which integrates tightly with the corresponding libraries. Checkout our integration examples below for more details.

[#383] Deprecate sea-query-driver in favour of sea-query-binder

[#422] Rusqlite support is moved to sea-query-rusqlite

[#433] Postgres support is moved to sea-query-postgres

// before
sea_query::sea_query_driver_postgres!();
use sea_query_driver_postgres::{bind_query, bind_query_as};

let (sql, values) = Query::select()
    .from(Character::Table)
    .expr(Func::count(Expr::col(Character::Id)))
    .build(PostgresQueryBuilder);

let row = bind_query(sqlx::query(&sql), &values)
    .fetch_one(&mut pool)
    .await
    .unwrap();

// now
use sea_query_binder::SqlxBinder;

let (sql, values) = Query::select()
    .from(Character::Table)
    .expr(Func::count(Expr::col(Character::Id)))
    .build_sqlx(PostgresQueryBuilder);

let row = sqlx::query_with(&sql, values)
    .fetch_one(&mut pool)
    .await
    .unwrap();

// You can now make use of SQLx's `query_as_with` nicely:
let rows = sqlx::query_as_with::<_, StructWithFromRow, _>(&sql, values)
    .fetch_all(&mut pool)
    .await
    .unwrap();

Support sub-query operators: EXISTS, ALL, ANY, SOME

[#118] Added sub-query operators: EXISTS, ALL, ANY, SOME

let query = Query::select()
    .column(Char::Id)
    .from(Char::Table)
    .and_where(
        Expr::col(Char::Id)
            .eq(
                Expr::any(
                    Query::select().column(Char::Id).from(Char::Table).take()
                )
            )
    )
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `id` FROM `character` WHERE `id` = ANY(SELECT `id` FROM `character`)"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"SELECT "id" FROM "character" WHERE "id" = ANY(SELECT "id" FROM "character")"#
);

Support ON CONFLICT WHERE

[#366] Added support to ON CONFLICT WHERE

let query = Query::insert()
    .into_table(Glyph::Table)
    .columns([Glyph::Aspect, Glyph::Image])
    .values_panic(vec![
        2.into(),
        3.into(),
    ])
    .on_conflict(
        OnConflict::column(Glyph::Id)
            .update_expr((Glyph::Image, Expr::val(1).add(2)))
            .target_and_where(Expr::tbl(Glyph::Table, Glyph::Aspect).is_null())
            .to_owned()
    )
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"INSERT INTO `glyph` (`aspect`, `image`) VALUES (2, 3) ON DUPLICATE KEY UPDATE `image` = 1 + 2"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") WHERE "glyph"."aspect" IS NULL DO UPDATE SET "image" = 1 + 2"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") WHERE "glyph"."aspect" IS NULL DO UPDATE SET "image" = 1 + 2"#
);

Changed cond_where chaining semantics

[#414] Changed cond_where chaining semantics

// Before: will extend current Condition
assert_eq!(
    Query::select()
        .cond_where(any![Expr::col(Glyph::Id).eq(1), Expr::col(Glyph::Id).eq(2)])
        .cond_where(Expr::col(Glyph::Id).eq(3))
        .to_owned()
        .to_string(PostgresQueryBuilder),
    r#"SELECT WHERE "id" = 1 OR "id" = 2 OR "id" = 3"#
);
// Before: confusing, since it depends on the order of invocation:
assert_eq!(
    Query::select()
        .cond_where(Expr::col(Glyph::Id).eq(3))
        .cond_where(any![Expr::col(Glyph::Id).eq(1), Expr::col(Glyph::Id).eq(2)])
        .to_owned()
        .to_string(PostgresQueryBuilder),
    r#"SELECT WHERE "id" = 3 AND ("id" = 1 OR "id" = 2)"#
);
// Now: will always conjoin with `AND`
assert_eq!(
    Query::select()
        .cond_where(Expr::col(Glyph::Id).eq(1))
        .cond_where(any![Expr::col(Glyph::Id).eq(2), Expr::col(Glyph::Id).eq(3)])
        .to_owned()
        .to_string(PostgresQueryBuilder),
    r#"SELECT WHERE "id" = 1 AND ("id" = 2 OR "id" = 3)"#
);
// Now: so they are now equivalent
assert_eq!(
    Query::select()
        .cond_where(any![Expr::col(Glyph::Id).eq(2), Expr::col(Glyph::Id).eq(3)])
        .cond_where(Expr::col(Glyph::Id).eq(1))
        .to_owned()
        .to_string(PostgresQueryBuilder),
    r#"SELECT WHERE ("id" = 2 OR "id" = 3) AND "id" = 1"#
);

Added OnConflict::value and OnConflict::values

[#451] Implementation From<T> for any Into<Value> into SimpleExpr

// Before: notice the tuple
OnConflict::column(Glyph::Id).update_expr((Glyph::Image, Expr::val(1).add(2)))
// After: it accepts `Value` as well as `SimpleExpr`
OnConflict::column(Glyph::Id).value(Glyph::Image, Expr::val(1).add(2))

Improvement to ColumnDef::default

[#347] ColumnDef::default now accepts Into<SimpleExpr> instead Into<Value>

// Now we can write:
ColumnDef::new(Char::FontId)
    .timestamp()
    .default(Keyword::CurrentTimestamp)

Breaking Changes

• [#386] Changed in_tuples interface to accept IntoValueTuple
• [#320] Removed deprecated methods
• [#440] CURRENT_TIMESTAMP changed from being a function to keyword
• [#375] Update SQLite boolean type from integer toboolean`
• [#451] Deprecated OnConflict::update_value, OnConflict::update_values, OnConflict::update_expr, OnConflict::update_exprs
• [#451] Deprecated InsertStatement::exprs, InsertStatement::exprs_panic
• [#451] Deprecated UpdateStatement::col_expr, UpdateStatement::value_expr, UpdateStatement::exprs
• [#451] UpdateStatement::value now accept Into<SimpleExpr> instead of Into<Value>
• [#451] Expr::case, CaseStatement::case and CaseStatement::finally now accepts Into<SimpleExpr> instead of Into<Expr>
• [#460] InsertStatement::values, UpdateStatement::values now accepts IntoIterator<Item = SimpleExpr> instead of IntoIterator<Item = Value>
• [#409] Use native api from SQLx for SQLite to work with time
• [#435] Changed type of ColumnType::Enum from (String, Vec<String>) to Enum { name: DynIden, variants: Vec<DynIden>}

Miscellaneous Enhancements

• [#336] Added support one dimension Postgres array for SQLx
• [#373] Support CROSS JOIN
• [#457] Added support DROP COLUMN for SQLite
• [#466] Added YEAR, BIT and VARBIT types
• [#338] Handle Postgres schema name for schema statements
• [#418] Added %, << and >> binary operators
• [#329] Added RAND function
• [#425] Implements Display for Value
• [#427] Added INTERSECT and EXCEPT to UnionType
• [#448] OrderedStatement::order_by_customs, OrderedStatement::order_by_columns, OverStatement::partition_by_customs, OverStatement::partition_by_columns now accepts IntoIterator<Item = T> instead of Vec<T>
• [#452] TableAlterStatement::rename_column, TableAlterStatement::drop_column, ColumnDef::new, ColumnDef::new_with_type now accepts IntoIden instead of Iden
• [#426] Cleanup IndexBuilder trait methods
• [#436] Introduce SqlWriter trait
• [#448] Remove unneeded vec! from examples

Bug Fixes

• [#449] distinct_on properly handles ColumnRef
• [#461] Removed ON for DROP INDEX for SQLite
• [#468] Change datetime string format to include microseconds
• [#452] ALTER TABLE for PosgreSQL with UNIQUE constraint

Integration Examples

SeaQuery plays well with the other crates in the rust ecosystem.

• Postgres Example
• Rusqlite Example
• SQLx Any Example
• SQLx Postgres Example
• SQLx MySql Example
• SQLx Sqlite Example

Community

SeaQL is a community driven project. We welcome you to participate, contribute and together build for Rust's future.