Crate ssql

An easy-to-use asynchronous ms sql server ORM.

Way of Getting Data.

Query is built using Table::query method, it returns QueryBuilder object. Through this object u can join other tables, apply filters, make query or make bulk inserts.

use ssql::prelude::*;
use serde::{Deserialize, Serialize};
use chrono::NaiveDateTime;

#[derive(ORM, Debug, Default, Serialize, Deserialize)]
#[ssql(table = person, schema = SCHEMA1)] // other schema
struct Person {
    #[ssql(primary_key)]
    id: i32,
    email: Option<String>, // wrap nullable column in option
}

#[derive(ORM, Debug, Default, Serialize, Deserialize)]
#[ssql(table = posts)] // default schema
struct Posts {
    id: i32,
    post: String,
    #[ssql(foreign_key = "SCHEMA1.Person.id")] // if it belongs to default schema, just write TABLE.COLUMN
    person_id: i32,
}

async fn _get<'a>(client: &'a mut tiberius::Client<Compat<TcpStream>>) -> SsqlResult<()> {
    let mut query = Person::query();

    // return Vec<Person>
    let vec1 = query.all(client).await?;

    // return Vec<Value>
    let js = query.json(client).await?;

    // return Polars DataFrame
    let df = query.df(client).await?;

    // return Vec<(Person, Posts)>
    let query = query.left_join::<Posts>();
    let vec2 = query.all(client).await?;

    Ok(())
}

Filters

Filters can be applied to query builder via provided filter method. Filters can be chained. For all filter expression please refer to ColExpr.

let query = query.filter(
    Person::col("email")?.eq(&"abc@gmail.com")
).filter(
    Person::col("id")?.gt(&3)
);

Manipulating Data

Data can be insert,delete,update,insert_ignore_pk for any instance that #[derive(ORM)] and set #[ssql(primary_key)]. Or calling bulk insert with Struct::insert_many(&mut conn) method.

async fn _test<'a>(client: &'a mut tiberius::Client<Compat<TcpStream>>) -> SsqlResult<()> {
    let new_p = Person {
        id: 2,
        email: Some("a@a.com".to_string()),
    };

    //insert with data in this instance.
    new_p.insert(client).await?;

    //insert with data in this instance ignoring the primary key.
    new_p.insert_ignore_pk(client).await?;

    // delete it based on its primary key mark.
    // like here i mark id with #[ssql(primary_key)]
    new_p.delete(client).await?;

    // update it based on its primary key mark.
    new_p.update(client).await?;


    // insert many accepts anything that can turn into iterator and return specific type, here is <Person>
    let vec = vec![new_p.clone(), new_p.clone()];
    Person::insert_many(vec, client).await?;

    let it = vec![1, 2, 3].into_iter().zip(
        vec!["a", "b", "c"].into_iter()
    ).map(|(id, email)| Person {
        id,
        email: Some(email.to_string()),
    });
    Person::insert_many(it, client).await?;
    Ok(())
}

Raw Sql Query

Using raw_query method to construct a raw sql query. Field name are reflecting as column name in sql query result.

use ssql::prelude::*;
 // structs reflecting complex raw query
 // leave the table attribute empty
 #[derive(ORM)]
 #[ssql(table)]
 pub struct PersonRaw {
    #[ssql(primary_key)]
    id: i32,
    email: String,
    dt: Option<NaiveDateTime>
 }
async fn _get<'a>(client: &'a mut tiberius::Client<Compat<TcpStream>>) -> SsqlResult<()> {

let query = PersonRaw::raw_query("SELECT id, email, dt FROM Person where id = @p1", &[&1]);
 let data = query.all(client).await;
 Ok(())
}

Modules

prelude

All necessary imports for using this crate.

utils

Utility functions.

Structs

ColExpr

Column Expression

FilterExpr

Filter expression used in query.filter method.

QueryBuilderI

Struct representing one TABLE.

RowStream

stream

Enums

JoinArg

Represents different JOIN methods in sql.

SsqlError

Error enum representing different errors during execution.

Traits

QueryAble

Core trait for constructing and conducting query.

SsqlMarker

a trait automatically derived via #[derive(ORM)] macro, all these methods are available.

Type Aliases

SsqlResult

Alias for Result<T, SsqlError>