Crate sql-macros

sql-macros - it's simple lib for generate sql query for select, select_many, select_all, insert, update, delete

Install

# Cargo.toml
[dependencies]
sql-macros = { version = "0.1" }

Usage

Table name

use sql_macros::SqlSelect;

#[derive(SqlSelect)]
pub struct User {
    #[table(select)]
    pub id: i32,
    pub email: String,
}

Table name will be generated as users

If you need use special name use #[table(name = users)]

Select one

use sql_macros::SqlSelect;

#[derive(SqlSelect)]
pub struct User {
    #[table(select)]
    pub id: i32,
    pub email: String,
}

pub async fn get_by_id(pool: &sqlx::PgPool, id: i32) -> Result<Option<User>, sqlx::Error> {
    let user = User::select_by_id(pool, id).await?;
    Ok(user)
}

View generated code

impl User {
    #[doc = "SELECT id, email FROM users WHERE id=$1"]
    pub async fn select_by_id(pool: &sqlx::PgPool, id: i32) -> Result<Option<User>, sqlx::Error> {
        let object = sqlx::query_as!(User, "SELECT id, email FROM users WHERE id=$1", id)
            .fetch_optional(pool)
            .await?;
        Ok(object)
    }
}

Select all

use sql_macros::SqlSelectAll;

#[derive(SqlSelectAll)]
pub struct User {
    pub id: i32,
    pub email: String,
}
pub async fn get_all_users(pool: &sqlx::PgPool) -> Result<Vec<User>, sqlx::Error> {
    let users = User::select_all(pool).await?;
    Ok(users)
}

View generated code

impl User {
    #[doc = "SELECT id, email FROM users"]
    pub async fn select_all(pool: &sqlx::PgPool) -> Result<Vec<User>, sqlx::Error> {
        let object = sqlx::query_as!(User, "SELECT id, email FROM users")
            .fetch_all(pool)
            .await?;
        Ok(object)
    }
}

Select many

use sql_macros::SqlSelectMany;

#[derive(SqlSelectMany)]
pub struct User {
    pub id: i32,
    pub email: String,
    #[table(select_many)]
    pub is_removed: bool,
}
pub async fn get_by_removed(pool: &sqlx::PgPool, is_removed: bool) -> Result<Vec<User>, sqlx::Error> {
    let users = User::select_many(pool, is_removed).await?;
    Ok(users)
}

View generated code

impl User {
    #[doc = "SELECT id, email, is_removed FROM users WHERE is_removed=$1"]
    pub async fn select_many_by_is_removed(
        pool: &sqlx::PgPool,
        is_removed: bool,
    ) -> Result<Vec<User>, sqlx::Error> {
        let object = sqlx::query_as!(
            User,
            "SELECT id, email, is_removed FROM users WHERE is_removed=$1",
            is_removed
        )
        .fetch_all(pool)
        .await?;
        Ok(object)
    }
}

Insert

Insert without returning

use sql_macros::SqlInsert;

#[derive(Debug, SqlInsert)]
#[table(name = users)]
pub struct CreateUser {
    pub email: String,
}

pub async fn create(pool: &sqlx::PgPool, data: &CreateUser) -> Result<u64, sqlx::Error> {
    let query_result = data.insert(pool).await?;
    Ok(query_result.rows_affected())
}

View generated code

impl CreateUser {
    #[doc = "INSERT INTO users (email) VALUES ($1)"]
    pub async fn insert(
        &self,
        conn: &mut sqlx::PgConnection,
    ) -> Result<sqlx::any::AnyQueryResult, sqlx::Error> {
        let query_result = sqlx::query!("INSERT INTO users (email) VALUES ($1)", &self.email)
            .execute(&mut *conn)
            .await?;
        Ok(query_result.into())
    }
}

Insert with returning

use sql_macros::SqlInsert;

#[derive(Debug, SqlInsert)]
#[table(name = users, return_type = User)]
pub struct CreateUser {
    pub email: String,
}

pub async fn create(pool: &sqlx::PgPool, data: &CreateUser) -> Result<User, sqlx::Error> {
    let user = data.insert(pool).await?;
    Ok(user)
}

View generated code

impl CreateUser {
    #[doc = "INSERT INTO users (email) VALUES ($1) RETURNING *"]
    pub async fn insert(&self, conn: &mut sqlx::PgConnection) -> Result<User, sqlx::Error> {
        let object = sqlx::query_as!(
            User,
            "INSERT INTO users (email) VALUES ($1) RETURNING *",
            &self.email
        )
        .fetch_one(&mut *conn)
        .await?;
        Ok(object)
    }
}

Insert with returning fields

use sql_macros::SqlInsert;

#[derive(sqlx::FromRow)]
struct CreateUserResponse {
    pub id: i32,
}

#[derive(Debug, SqlInsert)]
#[table(name = users, return_type = CreateUserResponse, return_fields = "id")]
pub struct CreateUser {
    pub email: String,
}

pub async fn create(pool: &sqlx::PgPool, data: &CreateUser) -> Result<CreateUserResponse, sqlx::Error> {
    let user = data.insert(pool).await?;
    Ok(user)
}

View generated code

impl CreateUser {
    #[doc = "INSERT INTO users (email) VALUES ($1) RETURNING id"]
    pub async fn insert(&self, conn: &mut sqlx::PgConnection) -> Result<CreateUserResponse, sqlx::Error> {
        let object = sqlx::query_as!(
            CreateUserResponse,
            "INSERT INTO users (email) VALUES ($1) RETURNING id",
            &self.email
        )
        .fetch_one(&mut *conn)
        .await?;
        Ok(object)
    }
}

Update

Update without returning

It just return query result (see sqlx::any::AnyQueryResult)

use sql_macros::SqlUpdate;

#[derive(SqlUpdate)]
#[table(name = users)]
pub struct UpdateUser {
    #[table(update)]
    pub id: i32,
    pub email: String,
}

pub async fn update(pool: &sqlx::PgPool, data: &UpdateUser) -> Result<u64, sqlx::Error> {
    let query_result = data.update(pool).await?;
    Ok(query_result.rows_affected())
}

View generated code

impl UpdateUser {
    #[doc = "UPDATE users SET email=$1 WHERE id=$2"]
    pub async fn update(
        &self,
        conn: &mut sqlx::PgConnection,
    ) -> Result<sqlx::any::AnyQueryResult, sqlx::Error> {
        let result = sqlx::query!(
            "UPDATE users SET email=$1 WHERE id=$2",
           &self.email,
           &self.id
       )
       .execute(&mut *conn)
       .await?;
       Ok(result.into())
   }
}

Update with returning type

use sql_macros::SqlUpdate;

#[derive(SqlUpdate)]
#[table(name = users, return_type = User)]
pub struct UpdateUser {
    #[table(update)]
    pub id: i32,
    pub email: String,
}

pub async fn update(pool: &sqlx::PgPool, data: &UpdateUser) -> Result<User, sqlx::Error> {
    let user = data.update(pool).await?;
    Ok(user)
}

View generated code

impl UpdateUser {
   #[doc = "UPDATE users SET email=$1 WHERE id=$2 RETURNING *"]
   pub async fn update(&self, conn: &mut sqlx::PgConnection) -> Result<User, sqlx::Error> {
       let object = sqlx::query_as!(
           User,
           "UPDATE users SET email=$1 WHERE id=$2 RETURNING *",
            &self.email,
            &self.id
        )
        .fetch_one(&mut *conn)
        .await?;
        Ok(object)
    }
}

Update with returning fields

use sql_macros::SqlUpdate;

#[derive(sqlx::FromRow)]
struct UpdateUserResponse {
    pub id: i32,
}

#[derive(SqlUpdate)]
#[table(name = users, return_type = UpdateUserResponse, return_fields = "id")]
pub struct UpdateUser {
    #[table(update)]
    pub id: i32,
    pub email: String,
}

pub async fn update(pool: &sqlx::PgPool, data: &UpdateUser) -> Result<UpdateUserResponse, sqlx::Error> {
    let res = data.update(pool).await?;
    Ok(res)
}

View generated code

impl UpdateUser {
   #[doc = "UPDATE users SET email=$1 WHERE id=$2 RETURNING id"]
   pub async fn update(&self, conn: &mut sqlx::PgConnection) -> Result<UpdateUserResponse, sqlx::Error> {
       let object = sqlx::query_as!(
           UpdateUserResponse,
           "UPDATE users SET email=$1 WHERE id=$2 RETURNING id",
            &self.email,
            &self.id
        )
        .fetch_one(&mut *conn)
        .await?;
        Ok(object)
    }
}

Update with special columns

use sql_macros::SqlUpdate;

#[derive(SqlUpdate)]
#[table(name = users, spec_columns = "updated_at=NOW()")]
pub struct UpdateUser {
    #[table(update)]
    pub id: i32,
    pub email: String,
}

pub async fn update(pool: &sqlx::PgPool, data: &UpdateUser) -> Result<u64, sqlx::Error> {
    let query_result = data.update(pool).await?;
    Ok(query_result.rows_affected())
}

View generated code

impl UpdateUser {
   #[doc = "UPDATE users SET email=$1, updated_at=NOW() WHERE id=$2"]
   pub async fn update(
       &self,
       conn: &mut sqlx::PgConnection,
   ) -> Result<sqlx::any::AnyQueryResult, sqlx::Error> {
       let result = sqlx::query!(
            "UPDATE users SET email=$1, updated_at=NOW() WHERE id=$2",
            &self.email,
            &self.id
        )
        .execute(&mut *conn)
        .await?;
        Ok(result.into())
    }
}

Delete

use sql_macros::SqlDelete;

#[derive(SqlDelete)]
pub struct User {
    #[table(delete)]
    pub id: i32,
}

async fn delete(conn: &mut sqlx::PgConnection, id: i32) -> Result<u64, sqlx::Error> {
    let result = User::delete(pool, id).await?;
    Ok(result.rows_affected())
}

View generated code

impl User {
    #[doc = "DELETE FROM users WHERE id=$1"]
    pub async fn delete_by_id(
        conn: &mut sqlx::PgConnection,
        id: i32,
    ) -> Result<sqlx::any::AnyQueryResult, sqlx::Error> {
        let result = sqlx::query!("DELETE FROM users WHERE id=$1", id)
            .execute(&mut *conn)
            .await?;
        Ok(result.into())
    }
}

Generate methods with many fields

use sql_macros::SqlSelect;

#[derive(SqlSelect)]
#[table(select = get_active_user(is_active, is_removed))]
#[table(select_many = get_user_by_removed(is_active, is_removed))]
#[table(delete = delete_user(is_active, is_removed))]
pub struct User {
    pub id: i32,
    pub email: String,
    pub is_active: bool,
    pub is_removed: bool,
}

Worked with select, select_many, delete

Self methods can't named as select, select_many, delete

#[table(update = update_user(email, other_field))] - now is not supported

Select with enum

use sql_macros::SqlSelect;

#[derive(Debug, sqlx::Type)]
#[sqlx(type_name = "role", rename_all = "snake_case")]
pub enum Role {
    Admin,
    User,
    SuperAdmin,
}

#[derive(SqlSelect)]
pub struct User {
    #[table(select)]
    pub id: i32,
    pub email: String,
    #[table(as_type = "role!: Role")]
    pub role: Role,
}

pub async fn get_by_id(pool: &sqlx::PgPool, id: i32) -> Result<Option<User>, sqlx::Error> {
    let user = User::select_by_id(pool, id).await?;
    Ok(user)
}

View generated code

impl User {
    #[doc = "SELECT id, email, role AS \"role!: Role\" FROM users WHERE id=$1"]
    pub async fn select_by_id(pool: &sqlx::PgPool, id: i32) -> Result<Option<User>, sqlx::Error> {
        let object = sqlx::query_as!(
            User,
            "SELECT id, email, role AS \"role!: Role\" FROM users WHERE id=$1",
            id
        )
        .fetch_optional(pool)
        .await?;
        Ok(object)
    }
}

Attention

If you use return_type and you're table has a column with type enum - it will don't work because we can't get of type of return type since we have in macros token(it's just a string) not a type.

#[derive(Debug, SqlInsert)]
#[table(name = users, return_type = User)]
pub struct CreateUser {
    pub email: String,
}

If table User has a column with type enum SqlInsert or SqlUpdate will not be work

Name		Name	Last commit message	Last commit date
Latest commit History 1 Commit
sql-macros-core		sql-macros-core
sql-macros-derive		sql-macros-derive
sql-macros		sql-macros
.gitignore		.gitignore
Cargo.lock		Cargo.lock
Cargo.toml		Cargo.toml
Readme.md		Readme.md

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Repository files navigation

Crate sql-macros

Install

Usage

Table name

Select one

Select all

Select many

Insert

Insert without returning

Insert with returning

Insert with returning fields

Update

Update without returning

Update with returning type

Update with returning fields

Update with special columns

Delete

Generate methods with many fields

Select with enum

Attention

About

Uh oh!

Releases

Packages

Uh oh!

Languages

Cheban1996/sql-macros

Folders and files

Latest commit

History

Repository files navigation

Crate sql-macros

Install

Usage

Table name

Select one

Select all

Select many

Insert

Insert without returning

Insert with returning

Insert with returning fields

Update

Update without returning

Update with returning type

Update with returning fields

Update with special columns

Delete

Generate methods with many fields

Select with enum

Attention

About

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Uh oh!

Languages

Packages