🎉 We are pleased to release SeaQuery 0.32.0 / 0.32.1! Here are some feature highlights 🌟:

New Features​

Unify Expr and SimpleExpr Methods with ExprTrait #791​

Previously, "operator" methods (e.g. add, eq) are duplicated across Expr and SimpleExpr, but the list of methods is slightly different for each. And since Expr and SimpleExpr are distinct types, it makes writing generic code difficult.

The ExprTrait looks like this:

pub trait ExprTrait: Sized {
    // Required methods
    fn as_enum<N>(self, type_name: N) -> SimpleExpr
        where N: IntoIden;
    fn binary<O, R>(self, op: O, right: R) -> SimpleExpr
        where O: Into<BinOper>,
    R: Into<SimpleExpr>;
    fn cast_as<N>(self, type_name: N) -> SimpleExpr
        where N: IntoIden;
    fn unary(self, o: UnOper) -> SimpleExpr;

    // Provided methods
    fn add<R>(self, right: R) -> SimpleExpr where R: Into<SimpleExpr> { ... }
    fn and<R>(self, right: R) -> SimpleExpr where R: Into<SimpleExpr> { ... }
    fn between<A, B>(self, a: A, b: B) -> SimpleExpr
        where A: Into<SimpleExpr>,
              B: Into<SimpleExpr> { ... }
    fn div<R>(self, right: R) -> SimpleExpr where R: Into<SimpleExpr> { ... }
    fn eq<R>(self, right: R) -> SimpleExpr where R: Into<SimpleExpr> { ... }
    fn equals<C>(self, col: C) -> SimpleExpr where C: IntoColumnRef { ... }

    // omitting the where clause below to make it more concise ..

    fn gt<R>(self, right: R) -> SimpleExpr;
    fn gte<R>(self, right: R) -> SimpleExpr;
    fn in_subquery(self, sel: SelectStatement) -> SimpleExpr;
    fn in_tuples<V, I>(self, v: I) -> SimpleExpr;
    fn is<R>(self, right: R) -> SimpleExpr;
    fn is_in<V, I>(self, v: I) -> SimpleExpr;
    fn is_not<R>(self, right: R) -> SimpleExpr;
    fn is_not_in<V, I>(self, v: I) -> SimpleExpr;
    fn is_not_null(self) -> SimpleExpr;
    fn is_null(self) -> SimpleExpr;
    fn left_shift<R>(self, right: R) -> SimpleExpr;
    fn like<L>(self, like: L) -> SimpleExpr;
    fn lt<R>(self, right: R) -> SimpleExpr;
    fn lte<R>(self, right: R) -> SimpleExpr;
    fn modulo<R>(self, right: R) -> SimpleExpr;
    fn mul<R>(self, right: R) -> SimpleExpr;
    fn ne<R>(self, right: R) -> SimpleExpr;
    fn not(self) -> SimpleExpr;
    fn not_between<A, B>(self, a: A, b: B) -> SimpleExpr;
    fn not_equals<C>(self, col: C) -> SimpleExpr;
    fn not_in_subquery(self, sel: SelectStatement) -> SimpleExpr;
    fn not_like<L>(self, like: L) -> SimpleExpr;
    fn or<R>(self, right: R) -> SimpleExpr;
    fn right_shift<R>(self, right: R) -> SimpleExpr;
    fn sub<R>(self, right: R) -> SimpleExpr;
    fn bit_and<R>(self, right: R) -> SimpleExpr;
    fn bit_or<R>(self, right: R) -> SimpleExpr;
}

• Added ExprTrait to unify Expr and SimpleExpr methods
• Added impl<T> ExprTrait for T where T: Into<SimpleExpr> to maintain backwards compatibility for all Into<SimpleExpr> types, such as Value and FunctionCall
• Added trait PgExpr: ExprTrait: database specific expression for Postgres and impl PgExpr for FunctionCall, ColumnRef, Keyword, LikeExpr, Value
• Added trait SqliteExpr: ExprTrait: database specific expression for SQLite and impl SqliteExpr for FunctionCall, ColumnRef, Keyword, LikeExpr, Value

Support of Postgres Vector #774​

• Construct Postgres query with vector extension
• Added postgres-vector feature flag
• Added Value::Vector, ColumnType::Vector, ColumnDef::vector(), PgBinOper::EuclideanDistance, PgBinOper::NegativeInnerProduct and PgBinOper::CosineDistance

Example:

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]'"#
);

Support Partial Index #478​

• Support partial index CREATE INDEX .. WHERE ..

Example (Postgres):

assert_eq!(
    Index::create()
        .unique()
        .nulls_not_distinct()
        .name("partial-index-glyph-image-not-null")
        .table(Glyph::Table)
        .col(Glyph::Image)
        .and_where(Expr::col(Glyph::Image).is_not_null())
        .to_string(PostgresQueryBuilder),
    r#"CREATE UNIQUE INDEX "partial-index-glyph-image-not-null" ON "glyph" ("image") NULLS NOT DISTINCT WHERE "image" IS NOT NULL"#
);

Example (Sqlite):

assert_eq!(
    Index::create()
        .if_not_exists()
        .unique()
        .name("partial-index-glyph-image-not-null")
        .table(Glyph::Table)
        .col(Glyph::Image)
        .and_where(Expr::col(Glyph::Image).is_not_null())
        .to_string(SqliteQueryBuilder),
    r#"CREATE UNIQUE INDEX IF NOT EXISTS "partial-index-glyph-image-not-null" ON "glyph" ("image") WHERE "image" IS NOT NULL"#
);

Get Null Value​

This one may seem a little bummer, but it is type system problem. In order to support the Postgres protocol, SeaQuery's Value enum does not have a Null variant. This new Value::as_null method allows you to:

1. construct a typed null value
2. nullify a value
3. define generic functions (impl Into<Value>)

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

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

// one liner:
assert_eq!(Into::<Value>::into(2.2).as_null(), Value::Double(None));

Bitwise AND/OR Operators #841​

• Added bitwise and/or operators (bit_and, bit_or)

Examples:

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

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

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

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

Enhancements​

• #817 Replaced Educe with manual implementations
  • This is an effort to cut down compilation time
• #844 Added GREATEST & LEAST function
• #836 Added ValueType::enum_type_name()
• #835 Removed "one common table" restriction on recursive CTE

sea-query-derive​

We've finally done it! Removing the last bit of syn v1 from our dependency tree:

sea-query % cargo tree |grep 'syn '
│   └── syn v2.0.39
    │   │   └── syn v2.0.39 (*)
    │       └── syn v2.0.39 (*)
    ├── syn v2.0.39 (*)
            └── syn v2.0.39 (*)
│   │   └── syn v2.0.39 (*)

• Merged #[enum_def] into sea-query-derive
• #769 #[enum_def] now impl additional IdenStatic and AsRef<str>

sea-query-attr​

We've merged this crate into sea-query-derive, and they will be maintained together from now on.

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

Upgrades​

• #798 Upgraded sqlx to 0.8
• #798 Upgraded bigdecimal to 0.4
• #802 Upgraded rusqlite to 0.32

Integration Examples​

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

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

Community​

SeaQL.org is an independent open-source organization run by passionate ️developers. If you like our projects, please star ⭐ and share our repositories. If you feel generous, a small donation via GitHub Sponsor will be greatly appreciated, and goes a long way towards sustaining the organization 🚢.

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

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!

🎉 We are pleased to release SeaStreamer 0.5.x!

Here is the summary of new features and enhancements:

sea-streamer-types​

• Added From<Url> and FromIterator<Url> for StreamerUri #28
• Impl Default for Payload
• Impl serde Serialize & Deserialize for StreamKey (enabled by the feature flag serde), so this is now possible:

#[derive(Serialize, Deserialize)]
struct MyStruct {
    stream_key: StreamKey,
}

sea-streamer-socket​

• The Socket library can now be compiled without the stdio backend #35

sea-streamer-redis​

• Support nanosecond timestamp in Redis (under feature flag nanosecond-timestamp). Redis's default Stream ID resolution is millisecond, and it can be changed to nanosecond with RedisConnectOptions::set_timestamp_format:

  let mut options = RedisConnectOptions::default();
  options.set_timestamp_format(TimestampFormat::UnixTimestampNanos);
  
• Added RedisConnectOptions::set_message_field to set custom message field (the default used to be msg):

  let mut options = RedisConnectOptions::default();
  options.set_message_field("event");
  
• Added RedisProducer::send_with_ts to specify custom timestamp:

  producer.send_with_ts(&stream_key, timestamp, message)?;
  
• Added RedisProducer::flush_immut. This method is same as RedisProducer::flush but without &mut self
• Added RedisProducer::trim to perform XTRIM MAXLEN:

  producer.trim(&stream_key, maxlen).await?;
  
• Fixed capacity overflow error in some cases

sea-streamer-file​

• Added a special SEA_STREAMER_WILDCARD stream key to subscribe to all streams in a file:

  let consumer: SeaConsumer = streamer
      .create_consumer(&[StreamKey::new(SEA_STREAMER_WILDCARD)?], options)
      .await?;
  

sea-streamer-fuse​

We've shipped the first component library for stream processing! It currently only has one class, StreamJoin.

It is designed to be used in stream replay. In live streaming, if you have multiple streams from different sources and you want to multiplex them together, you can use the awesome futures_concurrency crate's Merge, and it just works!

use futures_concurrency::{stream::Merge, vec::Merge as Merged};

let consumers: Vec<SeaConsumer> = vec![stream_a, stream_b];
let streams: Vec<SeaMessageStream<'a>> = consumers.iter_mut().map(|ss| ss.stream()).collect();
let merged: Merged<SeaMessageStream<'a>> = streams.merge();

stream_a and stream_b can be heterogeneous, meaning they can be Kafka, Redis or even File.

How about in replay? In replay, different streams can flow at different pace, and thus if we try to naively merge them, the messages would come out-of-order.

To solve this problem, you can use StreamJoin::muxed:

type LiveStream<'a> = Merged<SeaMessageStream<'a>>;
let joined: StreamJoin<LiveStream<'a>, SeaMessage<'a>, StreamErr<BackendErr>> = StreamJoin::muxed(merged);

StreamJoin::align must be called manually to specify which streams should be aligned. Otherwise, messages will be out of order until the first message of each key arrives. Imagine a severely delayed stream sending its first message one day later; it would invalidate everything that came before it. However, the issue lies with the delayed stream itself, not the others.

In the example below, messages from the fast stream will be buffered, until a message from the slow stream arrives.

fast | (1) (2) (3) (4) (5)
slow |         (2)         (6)

Messages 1, 2 from fast will be buffered, until 2 from the slow stream arrives. Likewise, messages 3, 4, 5 will be buffered until 6 arrives.

The StreamJoin component is generic, and can actually be used outside of SeaStreamer, the only requirement is that the thing we want to align implements sea_streamer::Message:

impl Message for MyMessage {
    fn stream_key(&self) -> StreamKey { /* implement this */ }

    fn timestamp(&self) -> Timestamp { /* implement this */ }

    fn shard_id(&self) -> ShardId { /* doesn't matter */ }

    fn sequence(&self) -> SeqNo { /* doesn't matter */ }

    fn message(&self) -> Payload { /* doesn't matter */ }
}

Anecdote​

Over the past year, we've been using SeaStreamer heavily in production and it served us well!

SeaStreamer File is really handy, because it supports live streaming and also duals as an archive, in which it can be rotated and uploaded to the data lake every day. It has replaced our use of Redis in some same-host mpmc streaming scenario.

Redis Streams is also super nice (fast and reliable) and especially easy with SeaStreamer. IMO it's been underrated, it became our default choice for cross-host streaming.

By the way, SeaStreamer File is used as the tracing file format in FireDBG.

Community​

SeaQL.org is an independent open-source organization run by passionate ️developers. If you like our projects, please star ⭐ and share our repositories. If you feel generous, a small donation via GitHub Sponsor will be greatly appreciated, and goes a long way towards sustaining the organization 🚢.

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

In this tutorial, we would develop a GraphQL based admin dashboard with Seaography and Loco.

Read our first and second tutorial of the series, Getting Started with Loco & SeaORM, Adding GraphQL Support to Loco with Seaography, if you haven't.

The full source code can be found here.

What is Seaography​

Seaography is a GraphQL framework for building GraphQL resolvers using SeaORM entities. It ships with a CLI tool that can generate ready-to-compile Rust GraphQL servers from existing MySQL, Postgres and SQLite databases.

Setup React-Admin Frontend Boilerplate​

We use React-Admin as the frontend framework. It provides a convenient boilerplate to start with:

$ npm init react-admin frontend

Select the data provider you want to use, and validate with Enter:
❯ None
  I'll configure the data provider myself.

Select the auth provider you want to use, and validate with Enter:
❯ Hard coded local username/password

Enter the name of a resource you want to add, and validate with Enter (leave empty to finish):
❯ (Leave empty and press Enter)

How do you want to install the dependencies?
❯ Using npm

Run the boilerplate then visit http://localhost:5173/, you should see the welcome page:

$ cd frontend

$ npm install

$ npm run dev

> dev
> vite

  VITE v4.5.3  ready in 440 ms

  ➜  Local:   http://localhost:5173/

Now, we want to display the React-Admin data table template with mock data. First, we need to add ra-data-json-server dependency, it provides a ready-to-go mock data loader:

$ npm install ra-data-json-server

To prepare the mock data loader, we create a new file:

import jsonServerProvider from 'ra-data-json-server';

export const dataProvider = jsonServerProvider('https://jsonplaceholder.typicode.com');

Then, we change the UI file:

+ import { Admin, Resource, ListGuesser, ShowGuesser } from 'react-admin';
+ import { dataProvider } from './dataProvider';

ReactDOM.createRoot(document.getElementById('root')!).render(
-   <React.StrictMode>
-       <App />
-   </React.StrictMode>
+   <Admin dataProvider={dataProvider}>
+       <Resource name="users" list={ListGuesser} show={ShowGuesser} />
+   </Admin>
);

Run the boilerplate now you should see the user listing page:

$ npm run dev

Click on each row to view its detail page.

Add NPM dependency​

Next, we start to integrate our Loco and Seaography backend with React-Admin frontend. We use axios for sending POST request to our GraphQL backend:

$ npm install axios

GraphQL Data Provider​

Then, we can start implementing the GraphQL data provider by replacing the content of dataProvider.ts:

- import jsonServerProvider from 'ra-data-json-server';
- 
- export const dataProvider = jsonServerProvider('https://jsonplaceholder.typicode.com');

Integrating with our GraphQL endpoint at http://localhost:3000/api/graphql. We implemented two handler below, one fetch data for the post listing and the other to fetch data for a single post:

import { DataProvider } from "react-admin";
import axios from 'axios';

const apiUrl = 'http://localhost:3000/api/graphql';

export const dataProvider: DataProvider = {
    // Fetch data for post listing
    getList: (resource, params) => {
        // Paginator status
        const { page, perPage } = params.pagination;
        // Sorter status
        const { field, order } = params.sort;

        // POST request to GraphQL endpoint
        return axios.post(apiUrl, {
            query: `
            query {
              notes (
                orderBy: { ${field}: ${order} },
                pagination: { page: { limit: ${perPage}, page: ${page - 1} }}
              ) {
                nodes {
                  id
                  title
                  createdAt
                  updatedAt
                }
                paginationInfo {
                  pages
                  current
                  offset
                  total
                }
              }
            }
            `
        })
            .then((response) => {
                // Unwrap the response
                const { nodes, paginationInfo } = response.data.data.notes;
                // Return the data array and total number of pages
                return {
                    data: nodes,
                    total: paginationInfo.total,
                };
            });
    },

    // Fetch data for a single post
    getOne: (resource, params) => {
        // POST request to GraphQL endpoint
        return axios.post(apiUrl, {
            query: `
            query {
              notes(filters: {id: {eq: ${params.id}}}) {
                nodes {
                  id
                  title
                  content
                  createdAt
                  updatedAt
                }
              }
            }
            `
        })
            .then((response) => {
                // Unwrap the response
                const { nodes } = response.data.data.notes;
                // Return the one and only data
                return {
                    data: nodes[0],
                };
            });
    },

    getMany: (resource, params) => { },

    getManyReference: (resource, params) => { },

    update: (resource, params) => { },

    updateMany: (resource, params) => { },

    create: (resource, params) => { },

    delete: (resource, params) => { },

    deleteMany: (resource, params) => { },
};

Customize React-Admin Frontend​

Replace the React-Admin template frontend with our own custom UI to list all notes from the database.

- ReactDOM.createRoot(document.getElementById('root')!).render(
-    <Admin dataProvider={dataProvider}>
-        <Resource name="users" list={ListGuesser} show={ShowGuesser} />
-    </Admin>
- );

Implement the list and details page with specific columns:

import ReactDOM from 'react-dom/client';
import { Admin, Resource, List, Datagrid, TextField, Show, SimpleShowLayout } from 'react-admin';
import { dataProvider } from './dataProvider';

const PostList = () => (
    <List>
        <Datagrid bulkActionButtons={false}>
            <TextField source="id" />
            <TextField source="title" />
            <TextField source="content" />
            <TextField source="createdAt" />
            <TextField source="updatedAt" />
        </Datagrid>
    </List>
);

const PostShow = () => (
    <Show>
        <SimpleShowLayout>
            <TextField source="id" />
            <TextField source="title" />
            <TextField source="content" />
            <TextField source="createdAt" />
            <TextField source="updatedAt" />
        </SimpleShowLayout>
    </Show>
);

ReactDOM.createRoot(document.getElementById('root')!).render(
    <Admin dataProvider={dataProvider}>
        <Resource name="posts" list={PostList} show={PostShow} />
    </Admin>
);

Auth Free GraphQL Endpoint​

Disabled user authentication on GraphQL POST handler endpoint for convenient:

async fn graphql_handler(
-   _auth: auth::JWT,
    State(ctx): State<AppContext>,
    req: Request<Body>,
) -> Result<Response> {
    const DEPTH: usize = 1_000;
    const COMPLEXITY: usize = 1_000;
    // Construct the the GraphQL query root
    let schema = query_root::schema(ctx.db.clone(), DEPTH, COMPLEXITY).unwrap();
    // GraphQL handler
    let mut graphql_handler = async_graphql_axum::GraphQL::new(schema);
    // Execute GraphQL request and fetch the results
    let res = graphql_handler.call(req).await.unwrap();

    Ok(res)
}

Put It into Action!​

Run the React-Admin frontend:

$ cd frontend
$ npm run dev

Run the Loco backend:

$ cd backend
$ cargo run start

Visit http://localhost:5173/, you should see the post listing page:

We are fetching data from the GraphQL backend:

Click on column header to sort by the column in ascending or descending order:

Click on each row to view its detail page:

Conclusion​

Adding GraphQL support to Loco application is easy with the help of Seaography. It is an ergonomic library that perfectly integrate with any frontend framework. This tutorial only cover the basic integration of LOco and Seaography including only the querying of data via the GraphQL endpoint. GraphQL mutations are not demonstrated and we leave it for you to code it out!

SQL Server Support​

SQL Server for SeaORM is now available as a closed beta. If you are interested`, please signup here.

Migrating from sea-orm to sea-orm-x is straightforward with two simple steps. First, update the existing sea-orm dependency to sea-orm-x and enable the sqlz-mssql feature. Note that you might need to patch SeaORM dependency for the upstream dependencies.

sea-orm = { path = "<SEA_ORM_X_ROOT>/sea-orm-x", features = ["runtime-async-std-rustls", "sqlz-mssql"] }
sea-orm-migration = { path = "<SEA_ORM_X_ROOT>/sea-orm-x/sea-orm-migration" }

# Patch SeaORM dependency for the upstream dependencies
[patch.crates-io]
sea-orm = { path = "<SEA_ORM_X_ROOT>/sea-orm-x" }
sea-orm-migration = { path = "<SEA_ORM_X_ROOT>/sea-orm-x/sea-orm-migration" }

Second, update the connection string to connect to the MSSQL database.

# If the schema is `dbo`, simply write:
mssql://username:password@host/database

# Or, specify the schema name by providing an extra `currentSchema` query param.
mssql://username:password@host/database?currentSchema=my_schema

# You can trust peer certificate by providing an extra trustCertificate query param.
mssql://username:password@host/database?trustCertificate=true

SeaORM X has full Loco support and integrate seamlessly with many web frameworks:

• Actix
• Axum
• Async GraphQL
• jsonrpsee
• Loco
• Poem
• Salvo
• Tonic

Happy Coding!

🎉 We are pleased to release SeaORM 1.0 today! This is an special occasion for us, so this blog post will be a little more than a release notes.

Our Journey​

It's nearly been three years since our SeaORM 0.2 release. At that time, we set out to build a SQL ORM for the async Rust ecosystem, bringing together the best crates to allow developers to build high-performance and robust web services.

We would like to thank all early-adoptors, contributors and sponsors of SeaORM. Thank you to all our users for your trust and for being a part of our journey.

Today, many startups and companies are building applications in Rust, with SeaORM being an integral part of the stack. We are particularly pleased to see application frameworks, such as Loco, that provide tight integration with SeaORM, thereby offering a streamlined developer experience.

I think we've achieved our initial goal! We understand that maturity and stability are important considerations for teams when making technology choices. Therefore, we believe it's now time to stabilize SeaORM.

New Features​

Here are the highlights of some new features and enhancements introduced in SeaORM 1.0.

Refreshed migration schema definition​

#2099 Thanks to the clever design made by Loco, we've refreshed the schema definition syntax.

An old migration script looks like this:

#[async_trait::async_trait]
impl MigrationTrait for Migration {
    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        manager
            .create_table(
                Table::create()
                    .table(Users::Table)
                    .if_not_exists()
                    .col(
                        ColumnDef::new(Users::Id)
                            .integer()
                            .not_null()
                            .auto_increment()
                            .primary_key(),
                    )
                    .col(ColumnDef::new(Users::Pid).uuid().not_null())
                    .col(ColumnDef::new(Users::Email).string().not_null().unique_key())
                    // ...
    }
}

Now, using the new schema helpers, you can define the schema with a simplified syntax!

// Remember to import `sea_orm_migration::schema::*`
use sea_orm_migration::{prelude::*, schema::*};

#[derive(DeriveMigrationName)]
pub struct Migration;

#[async_trait::async_trait]
impl MigrationTrait for Migration {
    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        manager
            .create_table(
                Table::create()
                    .table(Users::Table)
                    .if_not_exists()
                    .col(pk_auto(Users::Id)) // Primary key with auto-increment
                    .col(uuid(Users::Pid)) // UUID column
                    .col(string_uniq(Users::Email)) // String column with unique and not null constraint
                    .col(string(Users::Password)) // String column
                    .col(string(Users::ApiKey).unique_key())
                    .col(string(Users::Name))
                    .col(string_null(Users::ResetToken)) // Nullable string column
                    .col(timestamp_null(Users::ResetSentAt)) // Nullable timestamp column
                    .col(string_null(Users::EmailVerificationToken))
                    .col(timestamp_null(Users::EmailVerificationSentAt))
                    .col(timestamp_null(Users::EmailVerifiedAt))
                    .to_owned(),
            )
            .await
    }

    // ...
}

There are three variants for each commonly used column type:

• <COLUMN_TYPE>() helper function, e.g. string(), define a non-null string column
• <COLUMN_TYPE>_null() helper function, e.g. string_null(), define a nullable string column
• <COLUMN_TYPE>_uniq() helper function, e.g. string_uniq(), define a non-null and unique string column

The new schema helpers can be used by importing sea_orm_migration::schema::*. The migration library is fully backward compatible, so there is no rush to migrate old scripts. The new syntax is recommended for new scripts, and all examples in the SeaORM repository have been updated for demonstration. For advanced use cases, the old SeaQuery syntax can still be used.

Reworked SQLite Type Mappings​

sea-orm#2077 sea-query#735 sea-schema#117 We've reworked the type mappings for SQLite across the SeaQL ecosystem, such that SeaQuery and SeaSchema are now reciprocal to each other. Migrations written with SeaQuery can be rediscovered by sea-orm-cli and generate compatible entities! In other words, the roundtrip is complete.

Data types will be mapped to SQLite types with a custom naming scheme following SQLite's affinity rule:

• INTEGER: integer, tiny_integer, small_integer, big_integer and boolean are stored as integer
• REAL: float, double, decimal and money are stored as real
• BLOB: blob and varbinary_blob are stored as blob
• TEXT: all other data types are stored as text, including string, char, text, json, uuid, date, time, datetime, timestamp, etc.

The full type mapping table is documented here. For more information, please refer to our previous blog post.

Alternative casing support for EntityModel & ActiveEnum​

#2170 Added rename_all attribute to DeriveEntityModel & DeriveActiveEnum

#[derive(DeriveEntityModel)]
#[sea_orm(table_name = "user", rename_all = "camelCase")]
pub struct Model {
    #[sea_orm(primary_key)]
    id: i32,
    first_name: String, // firstName
    #[sea_orm(column_name = "lAsTnAmE")]
    last_name: String, // lAsTnAmE
}

#[derive(EnumIter, DeriveActiveEnum)]
#[sea_orm(rs_type = "String", db_type = "String(StringLen::None)", rename_all = "camelCase")]
pub enum TestEnum {
    DefaultVariant, // defaultVariant
    #[sea_orm(rename = "kebab-case")]
    VariantKebabCase, // variant-kebab-case
    #[sea_orm(rename = "snake_case")]
    VariantSnakeCase, // variant_snake_case
    #[sea_orm(string_value = "CuStOmStRiNgVaLuE")]
    CustomStringValue, // CuStOmStRiNgVaLuE
}

Other Enhancements​

• #2185 PrimaryKeyArity trait with ARITY Constant

fn get_arity_of<E: EntityTrait>() -> usize {
    E::PrimaryKey::iter().count() // before; runtime
    <<E::PrimaryKey as PrimaryKeyTrait>::ValueType as PrimaryKeyArity>::ARITY // now; compile-time
}

• #2137 DerivePartialModel macro attribute entity now supports syn::Type

#[derive(DerivePartialModel)]
#[sea_orm(entity = "<entity::Model as ModelTrait>::Entity")]
struct EntityNameNotAIdent {
    #[sea_orm(from_col = "foo2")]
    _foo: i32,
    #[sea_orm(from_col = "bar2")]
    _bar: String,
}

• #2146 Added RelationDef::from_alias()

#[derive(DeriveIden)]
pub struct Cf;

assert_eq!(
    cake::Entity::find()
        .join_as(
            JoinType::LeftJoin,
            cake_filling::Relation::Cake.def().rev(),
            Cf
        )
        .join(
            JoinType::LeftJoin,
            cake_filling::Relation::Filling.def().from_alias(Cf)
        )
        .build(DbBackend::MySql)
        .to_string(),
    [
        "SELECT `cake`.`id`, `cake`.`name` FROM `cake`",
        "LEFT JOIN `cake_filling` AS `cf` ON `cake`.`id` = `cf`.`cake_id`",
        "LEFT JOIN `filling` ON `cf`.`filling_id` = `filling`.`id`",
    ]
    .join(" ")
);

• #2256 Added non-TLS runtime
• #2244 Added Insert::on_conflict_do_nothing
• #2255 Migration schema nullable column set NULL explicitly
• #2194 Added ActiveValue::set_if_not_equals()
• #2197 Added ActiveValue::try_as_ref()
• #2228 Added QuerySelect::order_by_with_nulls
• #2233 Expose get_xxx_connection_pool by default
• #2148 Added QueryResult::column_names
• #2199 [sea-orm-macro] Add @generated in generated code
• #1665 [sea-orm-macro] Qualify traits in DeriveActiveModel macro
• #2064 [sea-orm-cli] Fix migrate generate on empty mod.rs files

Release Planning​

SQLx 0.8 is recently released! We want to upgrade from 0.7 as soon as possible. However, since sea-orm 1.0 has been in release candidate status for a while, and considering the breaking changes in sqlx 0.8, we decided to keep sea-orm 1.0 on sqlx 0.7.

We plan to release sea-orm 1.1 on sqlx 0.8 soon. To avoid unplanned build failures, we recommend all users to specify SeaORM dependency with tilde requirement:

sea-orm = { version = "~1.0" }

According to the Cargo Book, this will prevent automatic upgrade to 1.1, so you can perform the upgrade at a convenient time.

If you've been depending on sea-orm 0.12, you're recommended to upgrade to 1.0 today. As they're both using sqlx 0.7, this upgrade focuses on SeaORM. And then upgrade to 1.1 down the line, which will then focus on SQLx, as we won't introduce any breaking changes. We recommend taking it one step at a time!

SeaORM 1.x will be maintained for at least 1 year. By then, we'll decide whether we want to release 2.0 and/or extend the lifecycle of 1.x.

Our Future​

You may ask, does this mean SeaORM is "done"? No, not at all! SeaORM 1.0 provides us and the community a solid foundation to build more ambitious features around SeaORM. Here are some facets of our vision:

1. GraphQL support via Seaography. We want to further develop Seaography to allow developers to turn a set of SeaORM entities into a fully-fledged GraphQL server!

2. Admin dashboard. We want to build a first-class frontend framework for SeaORM. It will be a breeze to develop admin / client portals with SeaORM!

3. Data science / analytics. SeaORM's been focusing on OLTP for now, and we're aware that people also have been using SeaORM for OLAP workloads. We want to provide better integration with dataframe libraries and develop more features tailored for data science and engineering.

4. Scale-out features. We want to develop features to help with scaling applications, e.g. sharding, caching and multi-tenancy.

SQL Server Support​

We've been planning SQL Server for SeaORM for a while, and SQL Server is finally coming to SeaORM 1.0! It will first be offered as a closed beta to our partners. If you are interested, please join our waiting list.

Sponsor​

A small donation will be greatly appreciated, and goes a long way towards sustaining the organization.

A big shout out to our sponsors 😇:

Gold Sponsors​

osmos offers a data ingestion platform to streamline data ingestion, transformation and workflow management, and they're using SeaQL libraries under the hood!

Contact us if you also want to become a company sponsor and be featured here.

GitHub Sponsors​

Afonso Barracha

Dean Sheather

Marcus Buffett

Kentaro Tanaka

Naoki Ikeguchi

Miles Granger

Data Intuitive

Marlon Mueller-Soppart

Anshul Sanghi

MasakiMiyazaki

Manfred Lee

KallyDev

ellik159

Caido

Coolpany SE

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!

In this tutorial, we would add a GraphQL endpoint with Seaography based on our Loco starter application. Read our first tutorial of the series, Getting Started with Loco & SeaORM, if you haven't.

The full source code can be found here.

What is Seaography​

Seaography is a GraphQL framework for building GraphQL resolvers using SeaORM entities. It ships with a CLI tool that can generate ready-to-compile Rust GraphQL servers from existing MySQL, Postgres and SQLite databases.

Adding Dependency​

Modify Cargo.toml and add a few more dependencies: seaography, async-graphql, async-graphql-axum and lazy_static.

seaography = { version = "1.0.0-rc.4", features = ["with-decimal", "with-chrono"] }
async-graphql = { version = "7.0", features = ["decimal", "chrono", "dataloader", "dynamic-schema"] }
async-graphql-axum = { version = "7.0" }
lazy_static = { version = "1.4" }
tower-service = { version = "0.3" }

Setting up SeaORM Entities for Seaography​

Seaography Entities are basically SeaORM Entities with some additions. They are fully compatible with SeaORM.

You can generate Seaography Entities by using sea-orm-cli with the extra --seaography flag.

sea-orm-cli generate entity -o src/models/_entities -u postgres://loco:loco@localhost:5432/loco_seaography_development --seaography

use sea_orm::entity::prelude::*;
use serde::{Serialize, Deserialize};

#[derive(Clone, Debug, PartialEq, DeriveEntityModel, Eq, Serialize, Deserialize)]
#[sea_orm(table_name = "notes")]
pub struct Model {
    pub created_at: DateTime,
    pub updated_at: DateTime,
    #[sea_orm(primary_key)]
    pub id: i32,
    pub title: Option<String>,
    pub content: Option<String>,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    #[sea_orm(has_many = "super::files::Entity")]
    Files,
}

impl Related<super::files::Entity> for Entity {
    fn to() -> RelationDef {
        Relation::Files.def()
    }
}

+ // Defining `RelatedEntity` to relate one entity with another
+ #[derive(Copy, Clone, Debug, EnumIter, DeriveRelatedEntity)]
+ pub enum RelatedEntity {
+     #[sea_orm(entity = "super::files::Entity")]
+     Files,
+ }

We can see that a new enum RelatedEntity is generated in the Entity files. This helps Seaography to locate the related Entities for making relational queries.

Implementing GraphQL Query Root​

We have finished setting up SeaORM entity for Seaography. Now, we implement the query root of Seaography where we bridge SeaORM and Async GraphQL with the help of Seaography.

use async_graphql::dynamic::*;
use sea_orm::DatabaseConnection;
use seaography::{Builder, BuilderContext};

use crate::models::_entities::*;

lazy_static::lazy_static! { static ref CONTEXT: BuilderContext = BuilderContext::default(); }

pub fn schema(
    database: DatabaseConnection,
    depth: usize,
    complexity: usize,
) -> Result<Schema, SchemaError> {
    // Builder of Seaography query root
    let mut builder = Builder::new(&CONTEXT, database.clone());
    // Register SeaORM entities
    seaography::register_entities!(
        builder,
        // List all models we want to include in the GraphQL endpoint here
        [files, notes, users]
    );
    // Configure async GraphQL limits
    let schema = builder
        .schema_builder()
        // The depth is the number of nesting levels of the field
        .limit_depth(depth)
        // The complexity is the number of fields in the query
        .limit_complexity(complexity);
    // Finish up with including SeaORM database connection
    schema.data(database).finish()
}

Writing Loco Controller to Handle GraphQL Endpoint​

For convenience we use the built-in GraphQL playground UI in async-graphql to test the GraphQL endpoint. And handle the GraphQL request with async_graphql_axum and Seaography.

use async_graphql::http::{playground_source, GraphQLPlaygroundConfig};
use axum::{body::Body, extract::Request};
use loco_rs::prelude::*;
use tower_service::Service;

use crate::graphql::query_root;

// GraphQL playground UI
async fn graphql_playground() -> Result<Response> {
    // The `GraphQLPlaygroundConfig` take one parameter
    // which is the URL of the GraphQL handler: `/api/graphql`
    let res = playground_source(GraphQLPlaygroundConfig::new("/api/graphql"));

    Ok(Response::new(res.into()))
}

async fn graphql_handler(
    State(ctx): State<AppContext>,
    req: Request<Body>,
) -> Result<Response> {
    const DEPTH: usize = 10;
    const COMPLEXITY: usize = 100;
    // Construct the the GraphQL query root
    let schema = query_root::schema(ctx.db.clone(), DEPTH, COMPLEXITY).unwrap();
    // GraphQL handler
    let mut graphql_handler = async_graphql_axum::GraphQL::new(schema);
    // Execute GraphQL request and fetch the results
    let res = graphql_handler.call(req).await.unwrap();

    Ok(res)
}

pub fn routes() -> Routes {
    // Define route
    Routes::new()
        // We put all GraphQL route behind `graphql` prefix
        .prefix("graphql")
        // GraphQL playground page is a GET request
        .add("/", get(graphql_playground))
        // GraphQL handler is a POST request
        .add("/", post(graphql_handler))
}

Opening GraphQL Playground​

Compile and run the Loco application, then visit http://localhost:3000/api/graphql.

$ cargo run start

    Finished `dev` profile [unoptimized + debuginfo] target(s) in 0.60s
     Running `target/debug/loco_seaography-cli start`
2024-06-24T08:04:52.173924Z  INFO app: loco_rs::config: loading environment from selected_path="config/development.yaml" environment=development
2024-06-24T08:04:52.180447Z  WARN app: loco_rs::boot: pretty backtraces are enabled (this is great for development but has a runtime cost for production. disable with `logger.pretty_backtrace` in your config yaml) environment=development
2024-06-24T08:04:52.272392Z  INFO app: loco_rs::db: auto migrating environment=development
2024-06-24T08:04:52.275198Z  INFO app: sea_orm_migration::migrator: Applying all pending migrations environment=development
2024-06-24T08:04:52.280720Z  INFO app: sea_orm_migration::migrator: No pending migrations environment=development
2024-06-24T08:04:52.281280Z  INFO app: loco_rs::boot: initializers loaded initializers="" environment=development
2024-06-24T08:04:52.308827Z  INFO app: loco_rs::controller::app_routes: [GET] /api/_ping environment=development
2024-06-24T08:04:52.308936Z  INFO app: loco_rs::controller::app_routes: [GET] /api/_health environment=development
2024-06-24T08:04:52.309021Z  INFO app: loco_rs::controller::app_routes: [GET] /api/notes environment=development
2024-06-24T08:04:52.309088Z  INFO app: loco_rs::controller::app_routes: [POST] /api/notes environment=development
2024-06-24T08:04:52.309158Z  INFO app: loco_rs::controller::app_routes: [GET] /api/notes/:id environment=development
2024-06-24T08:04:52.309234Z  INFO app: loco_rs::controller::app_routes: [DELETE] /api/notes/:id environment=development
2024-06-24T08:04:52.309286Z  INFO app: loco_rs::controller::app_routes: [POST] /api/notes/:id environment=development
2024-06-24T08:04:52.309334Z  INFO app: loco_rs::controller::app_routes: [POST] /api/auth/register environment=development
2024-06-24T08:04:52.309401Z  INFO app: loco_rs::controller::app_routes: [POST] /api/auth/verify environment=development
2024-06-24T08:04:52.309471Z  INFO app: loco_rs::controller::app_routes: [POST] /api/auth/login environment=development
2024-06-24T08:04:52.309572Z  INFO app: loco_rs::controller::app_routes: [POST] /api/auth/forgot environment=development
2024-06-24T08:04:52.309662Z  INFO app: loco_rs::controller::app_routes: [POST] /api/auth/reset environment=development
2024-06-24T08:04:52.309752Z  INFO app: loco_rs::controller::app_routes: [GET] /api/user/current environment=development
2024-06-24T08:04:52.309827Z  INFO app: loco_rs::controller::app_routes: [POST] /api/files/upload/:notes_id environment=development
2024-06-24T08:04:52.309910Z  INFO app: loco_rs::controller::app_routes: [GET] /api/files/list/:notes_id environment=development
2024-06-24T08:04:52.309997Z  INFO app: loco_rs::controller::app_routes: [GET] /api/files/view/:files_id environment=development
2024-06-24T08:04:52.310088Z  INFO app: loco_rs::controller::app_routes: [GET] /api/graphql environment=development
2024-06-24T08:04:52.310172Z  INFO app: loco_rs::controller::app_routes: [POST] /api/graphql environment=development
2024-06-24T08:04:52.310469Z  INFO app: loco_rs::controller::app_routes: [Middleware] Adding limit payload data="5mb" environment=development
2024-06-24T08:04:52.310615Z  INFO app: loco_rs::controller::app_routes: [Middleware] Adding log trace id environment=development
2024-06-24T08:04:52.310934Z  INFO app: loco_rs::controller::app_routes: [Middleware] Adding cors environment=development
2024-06-24T08:04:52.311008Z  INFO app: loco_rs::controller::app_routes: [Middleware] Adding etag layer environment=development

                      ▄     ▀
                                 ▀  ▄
                  ▄       ▀     ▄  ▄ ▄▀
                                    ▄ ▀▄▄
                        ▄     ▀    ▀  ▀▄▀█▄
                                          ▀█▄
▄▄▄▄▄▄▄  ▄▄▄▄▄▄▄▄▄   ▄▄▄▄▄▄▄▄▄▄▄ ▄▄▄▄▄▄▄▄▄ ▀▀█
 ██████  █████   ███ █████   ███ █████   ███ ▀█
 ██████  █████   ███ █████   ▀▀▀ █████   ███ ▄█▄
 ██████  █████   ███ █████       █████   ███ ████▄
 ██████  █████   ███ █████   ▄▄▄ █████   ███ █████
 ██████  █████   ███  ████   ███ █████   ███ ████▀
   ▀▀▀██▄ ▀▀▀▀▀▀▀▀▀▀  ▀▀▀▀▀▀▀▀▀▀  ▀▀▀▀▀▀▀▀▀▀ ██▀
       ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
                https://loco.rs

environment: development
   database: automigrate
     logger: debug
compilation: debug
      modes: server

listening on [::]:3000

Creating Notes​

Create a new notes with the GraphQL mutator.

mutation {
  notesCreateOne(
    data: {
      id: 1
      title: "Notes 001"
      content: "Content 001"
      createdAt: "2024-06-24 00:00:00"
      updatedAt: "2024-06-24 00:00:00"
    }
  ) {
    id
    title
    content
    createdAt
    updatedAt
  }
}

Querying Notes​

Query notes with its related files.

query {
  notes {
    nodes {
      id
      title
      content
      files {
        nodes {
          id
          filePath
        }
      }
    }
  }
}

Adding User Authentication to GraphQL Endpoint​

Our GraphQL handler can be accessed without user authentication. Next, we want to only allow logged in user to access the GraphQL handler.

To do so, we add _auth: auth::JWT to the graphql_handler function.

async fn graphql_handler(
+   _auth: auth::JWT,
    State(ctx): State<AppContext>,
    req: Request<Body>,
) -> Result<Response> {
    const DEPTH: usize = 10;
    const COMPLEXITY: usize = 100;
    // Construct the the GraphQL query root
    let schema = query_root::schema(ctx.db.clone(), DEPTH, COMPLEXITY).unwrap();
    // GraphQL handler
    let mut graphql_handler = async_graphql_axum::GraphQL::new(schema);
    // Execute GraphQL request and fetch the results
    let res = graphql_handler.call(req).await.unwrap();

    Ok(res)
}

Then, run the Loco application and visit the GraphQL playground again. You should see unauthorize error.

Adding Authentication header to GraphQL Playground​

First, we generate a valid authorization token by logging in the user account with the corresponding email and password:

$ curl --location 'http://localhost:3000/api/auth/login' \
--data-raw '{
    "email": "cwchan.billy@gmail.com",
    "password": "password"
}'

{
    "token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzUxMiJ9.eyJwaWQiOiIwN2NjMDk5Ni03YWYxLTQ5YmYtYmY2NC01OTg4ZjFhODM2OTkiLCJleHAiOjE3MTk4MjIzMzN9.CgKp_aE-DyAuBJIvFGJ6l68ooAlEiJGhjWeaetDtHrupaYDm0ldVxf24vj3fPgkCqZ_njv2129n2pSCzHOjaow",
    "pid": "07cc0996-7af1-49bf-bf64-5988f1a83699",
    "name": "Billy",
    "is_verified": true
}

Go to the setting page of GraphQL playground. And add a new header under request. globalHeaders:

Then, we can access GraphQL handler as usual.

Conclusion​

Adding GraphQL support to Loco application is easy with the help of Seaography. It is an ergonomic library that turns SeaORM entities into GraphQL nodes and provides a set of utilities and combined with a code generator makes GraphQL API building a breeze.

SQL Server Support​

SQL Server for SeaORM is now available as a closed beta. If you are interested`, please signup here.

Migrating from sea-orm to sea-orm-x is straightforward with two simple steps. First, update the existing sea-orm dependency to sea-orm-x and enable the sqlz-mssql feature. Note that you might need to patch SeaORM dependency for the upstream dependencies.

sea-orm = { path = "<SEA_ORM_X_ROOT>/sea-orm-x", features = ["runtime-async-std-rustls", "sqlz-mssql"] }
sea-orm-migration = { path = "<SEA_ORM_X_ROOT>/sea-orm-x/sea-orm-migration" }

# Patch SeaORM dependency for the upstream dependencies
[patch.crates-io]
sea-orm = { path = "<SEA_ORM_X_ROOT>/sea-orm-x" }
sea-orm-migration = { path = "<SEA_ORM_X_ROOT>/sea-orm-x/sea-orm-migration" }

Second, update the connection string to connect to the MSSQL database.

# If the schema is `dbo`, simply write:
mssql://username:password@host/database

# Or, specify the schema name by providing an extra `currentSchema` query param.
mssql://username:password@host/database?currentSchema=my_schema

# You can trust peer certificate by providing an extra trustCertificate query param.
mssql://username:password@host/database?trustCertificate=true

SeaORM X has full Loco support and integrate seamlessly with many web frameworks:

• Actix
• Axum
• Async GraphQL
• jsonrpsee
• Loco
• Poem
• Salvo
• Tonic

Happy Coding!

In this tutorial, we would create a REST notepad backend starting from scratch and adding a new REST endpoint to handle file uploads in Loco.

The full source code can be found here. The documentation of the REST API is available here.

What is Loco?​

Loco is a Rails inspired web framework for Rust. It includes many Rails feature with Rust ergonomics. Loco integrates seamlessly with SeaORM, offering a first-class development experience!

• Controllers and routing via axum
• Models, migration, and ActiveRecord via SeaORM
• Views via serde
• Seamless, Background jobs via sidekiq-rs, multi modal: in process, out of process, async via Tokio
• ...and more

REST API Starter Template​

Install loco-cli:

cargo install loco-cli

The loco-cli provides three starter templates:

• SaaS Starter
• Rest API Starter
• Lightweight Service Starter

For this tutorial, we want the "Rest API Starter" template:

$ loco new

✔  You are inside a git repository. Do you wish to continue? · Yes
✔  App name? · loco_starter
✔  What would you like to build? · Rest API (with DB and user auth)

🚂 Loco app generated successfully in:
/sea-orm/examples/loco_starter