I have found these related issues/pull requests

Relates to PR #3717

Description

When using FromRow, some SQL queries are recurring between projects.
These queries are not extremely complex but related to CRUD operations one often perform against a table.
These are:

Update/Save

• save(): To save or update an object, with the save/update decision depending on whether the primary key is set or not (similar to ActiveRecord)

Delete

• delete_by_(): To delete an object based on a matching field

Retrieve
Using a query that can be parameterized as follows:

• WHERE on several fields (AND logic)
• ORDER BY
• GROUP BY
• LIMIT and OFFSET to control page size and requested results page

Preferred solution

• Provide a new derive ToOrm with a few specific attributes to guide precisely the generated code.
• Code is generated against the struct, with both instance and struct methods.

For example, and to explain based on concrete examples, we may have the following structure:

    #[derive(Debug, Default, Clone, FromRow, ToOrm)]
    struct Account {
        #[sqlx(pk)]
        #[sqlx(new = "uuid::Uuid::new_v4()")]
       #[sqlx(is_default = "is_nil()")]
        pub id: Uuid,

        #[sqlx(by)]
        pub email: String,

        #[sqlx(readonly)]
        pub count: Option<i32>,

        #[sqlx(created_at)]
        #[sqlx(new = "chrono::Utc::now().fixed_offset()")]
        pub created_at: chrono::DateTime<FixedOffset>,

        #[sqlx(updated_at)]
        #[sqlx(new = "chrono::Utc::now().fixed_offset()")]
        pub updated_at: chrono::DateTime<FixedOffset>,
    }

Attributes explanation

• #[sqlx(new)] is optional as new, if not specified assumes the ::new() method.
• #[sqlx(is_default)] is optional as is_default, if not specified assumes the Default trait.
• #[sqlx(pk)] indicates what is the primary key
• #[sqlx(by)] generates both by_ class methods
• #[sqlx(readonly)] ensures the field cannot be updated (but can be inserted)
• #[sqlx(created_at)] and #[sqlx(updated_at)] are optional but update code, using the #[sqlx(new)] (or std::Time::Instant::now() for eg) is generated when calling the .save() instance method

Retrieve one object API (By)
This is triggered by the following attributes: #[sqlx(pk)] and #[sqlx(by)].
The trait <field name>ByTrait is created to hold all related methods.
For each field, it generates the following struct method:
::by_<field>(pool: &Pool<DB>, value: <field type>) -> sqlx::Result<Option<field type>>;

Delete API
This is always generated.
The trait <field name>DeleteTrait is created to hold all related methods.
The following instance method is generated:
.delete(&self, pool: &Pool<DB>) -> sqlx::Result<()>;

Save APIs
This is always generated.
The trait <field name>SaveTrait is created to hold all related methods.
The following instance method is generated:
.save(&self, pool: &Pool<DB>) -> sqlx::Result<Self>;

#[sqlx(pk)] is checked to decide if the data should be inserted or updated. The default method is to compare the PK field with the ::default() and it can be overriden by #[sqlx(is_default)].

#[sqlx(readonly)] fields are excluded from the UDPATE case.

#[sqlx(created_at)] and #[sqlx(updated_at)], if specified, generates update code, using std::time by default or #[sqlx(new)] if specified.

Query API
This is always generated.
Calling query() returns a Builder that allows for extensible parametization in the future.
The first version would contain a few useful options.

• An optional .limit() and .offset() to control the maximum number of results and which offset/page to return.
• Triggered by #[sqlx(by)] and #[sqlx(pk)], where_(), group_by_ and order_by_ to respectively chain WHERE statements (using an AND logic) and group and order by fields.

A query() call would be as follow:

         let res = User::query()
             .where_email("[email protected]")
             .group_by_email()
             .group_by_id()
             .order_by_email(OrderBy::Desc)
             .limit(2)
             .offset(2)
             .build(&pool)
             .await
             .unwrap();

A draft of this is available at #3717.

Is this a breaking change? Why or why not?

It is not a breaking change as this new derive is optional and does not involve new code generation if not used.