ORM and Query Builder for Rust

Let’s talk about using RDBMS in OOP

• Relational Database Management System, RDBMS
• Object Oriented Progamming, OOP

Data repesentation between OOP and RDBMS

• OOP: object-oriented model that are almost always non-scalar values
  • such as Person object, Book object and Post object
• RDBMS: not object-oriented model and can only manipulate scalar values
  • such as integers and strings organized within tables

ORM; Object Relational Mapping

A programming technique for converting data between incompatible type systems using object-oriented programming languages

            |   << Object >>      |   << Relational  >>
MODEL       |   Rust Struct       |   SQL Schema       
=========   |   ===============   |   ==================
posts       |   posts: Struct     |   posts: TABLE    
---------   |   ---------------   |   ------------------
id          |   id: u32           |   id: INTEGER (PK)
title       |   title: String     |   title: VARCHAR
body        |   body: String      |   body: TEXT
published   |   published: bool   |   published: BOOLEAN
---------   |   ---------------   |   ------------------

Sequence getting data from RDBMS

1. Write a SQL Query
2. Execute the query using library
3. Convert the result to proper type (Row result -> Object)

Load data without ORM

var sql = "SELECT id, first_name, last_name age FRM persons WHRE id = 10";
var result = context.Persons.FromSqlRaw(sql).ToList();
var name = result[0]["first_name"];

Load data with ORM

var person = repository.GetPerson(10);
var firstName = person.GetFirstName();

Advatages of ORM

• Less time for debuging runtime errors
• Focus business logic not SQL
• Reusable code

Disadvantage of ORM

• High level of abstraction => Poor performance

ORM Framworks

|Language|ORM| |—|—| |Java|Java Persistent API, MyBatis, Spring DAO, Hibernate| |C++|sqlite_orm, ODB, QxOrm| |C#|Entity Framework |Rust|Diesel, SeaORM| |kotlin (Android)|Room instead of direct using SQLite API!|

Multi-paradigm programming languages

ORM Components

1. Schema Builder: Create Schema
2. Schema Mapper: Map object (Struct) to relational schema (Table)
3. Query Builder: Make query statement (CRUD)
4. Database driver: Execute query statement using DB Connector

Schema Mapper

1. Bind Struct to Table
2. Bind Member of Struct to Column

Query Builder

struct Admin {
    int id;
    int uid;
    std::string key;
    ...
};
DECLARE_TABLE(AdminTable, "admin", Admin::Id, Admin::Uid, Admin::Key);

auto query = AdminTable.select(Admin::Id, Admin::Uid, Admin::Key).where(Admin::Id > 3);
EXPECT_EQ(query, "SELECT uid, key FROM admin WHERE id > 3");

Schema Mapper #1 DSL with Template Code

• OsQuery (DSL with Jinja2, not ORM)

  // user.table
  table_name("users")
  schema([
    Column("uid", BIGINT, "User ID", index=True),
    Column("gid", BIGINT, "Group ID (unsigned)"),
    Column("username", TEXT, "Username", additional=True),
  ])
  

/// BEGIN[GENTABLE]
namespace tables {
class ${ class_name }$ {
 public:
    TableColumns columns() const override {
    return {
      ${ for column in schema: }$\
      std::make_tuple("${ write(column.name) }$", ${ write(column.type.affinity) }$,\
    };
};

Schema Mapper #2 Macro Expansion

struct Table1 {
    int column1;
    std::string column2;
 
    DECLARE_COLUMN(Column1, "column1", &Table1::column1);
    DECLARE_COLUMN(Column2, "column2", &Table1::column2);
};
DECLARE_TABLE(table1, "table1", Table1::Column1, Table1::Column2, Table1::Column3);

template<typename... Columns>
class Table : public Crud<Table<Columns...>> {
public:
    /// Make first stuct type to table type
    using Type = typename std::tuple_element<0, std::tuple<Columns...>>::type::Table;

    explicit Table(const std::string& name, Columns ...columns) : name(name), columns(columns...) {}
    std::vector<std::string> getColumnNames(void) const noexcept;

Query Builder

// Query: SELECT uid, key FROM admin
template<typename T>
template<typename... ColumnTypes>
T& Crud<T>::select(ColumnTypes&& ... cts)
{
        std::stringstream ss;
        ss << "SELECT ";

        auto columnNames = static_cast<T*>(this)->getColumnNames(std::forward<ColumnTypes>(cts)...);
        for (const auto& cname : columnNames) {
                ss << cname << ", ";
        }

        ss << " FROM ";

        auto tableName = static_cast<T*>(this)->getTableName(std::forward<ColumnTypes>(cts)...);
        ss << tableNAme;

        return *(static_cast<T*>(this));
}

Rust ORM (Diesel)

• Same goal: to offer you a complete solution in interfacing with databases

Diesel

• Preventing Runtime Errors
  • Compile time checking
• Built for Performance
  • focus on zero-cost abstractions
  • run your query and load your data even faster than C
• Supported backend: SQLite, MySQL, PostgreSQL

247 reverse dependencies of diesel

Diesel Practice

Setup Database

• Tell Diesel where to find our database
  • by setting the DATABASE_URL environment variable.

$ echo DATABASE_URL=postgres://username:password@localhost/diesel_demo > .env
$ diesel setup

Create table using diesel cli

$ diesel migration generate create_posts

Creating migrations/create_posts/up.sql
Creating migrations/create_posts/down.sql

# migrations/create_posts/up.sql
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR NOT NULL,
    body TEXT NOT NULL,
    published BOOLEAN NOT NULL DEFAULT 'f'
)

$ diesel migration run

Establish a database connection

#[macro_use]
extern crate diesel;

use diesel::prelude::*;
use diesel::pg::PgConnection;

pub fn establish_connection() -> PgConnection {
    let database_url = env::var("DATABASE_URL");
    PgConnection::establish(&database_url)
}

Define model using macro

Queryable generate all of the code needed to load a Post struct from a SQL query.

// src/models.rs
#[derive(Queryable)]
pub struct Post {
    pub id: i32,
    pub title: String,
    pub body: String,
    pub published: bool,
}

Under the hood: Schema Mapper (#1)

Typically the schema module isn’t created by hand, it gets generated by diesel cli.

table! {
    posts (id) {
        id -> Integer,
        title -> Text,
        body -> Text,
        published -> Boolean,
    }
}

Under the hood: Schema Mapper (#2)

#[macro_export]
macro_rules! table {
    ($($tokens:tt)*) => {
        $crate::__diesel_parse_table! {
            tokens = [$($tokens)*],
            schema = public,
            primary_key = id,
            ...
        }
    }
}

Under the hood: Query Builder

// cargo rustc --lib --profile=check -- -Zunpretty=expanded
impl AsQuery for table {
    type SqlType = SqlType;
    type Query = SelectStatement<Self>;
    fn as_query(self) -> Self::Query { SelectStatement::simple(self) }
}
impl Table for table {
    type PrimaryKey = (id);
    type AllColumns = (id, title, body, published);
    fn primary_key(&self) -> Self::PrimaryKey { (id) }
    fn all_columns() -> Self::AllColumns {
        (id, title, body, published)
    }
}

CRUD example: SELECT Posts

extern crate diesel_demo;
extern crate diesel;

use self::diesel_demo::*;
use self::models::*;
use self::diesel::prelude::*;

fn main() {
    use diesel_demo::schema::posts::dsl::*;

    let connection = establish_connection();
    let results = posts.load::<Post>(&connection);  // Load Post Table

    for post in results {
        println!("{}", post.title);                 // Print title Column
        println!("{}", post.body);                  // Print body Column
    }
}

CRUD example: INSERT Posts

use super::schema::posts;

#[derive(Insertable)]
#[table_name="posts"]
pub struct NewPost<'a> {
    pub title: &'a str,
    pub body: &'a str,
}

pub fn create_post<'a>(conn: &PgConnection, title: &'a str, body: &'a str) -> Post {
    use schema::posts;
    let new_post = NewPost {
        title: title,
        body: body,
    };

    diesel::insert_into(posts::table)
        .values(&new_post)
        .get_result(conn)
        .expect("Error saving new post")
}

Barrel: A powerful schema migration building API for Rust

A powerful database schema builder, that lets you write your SQL migrations in Rust! barrel offers callback-style builder functions for SQL migrations

use barrel::{types, Migration};
use barrel::backend::Pg;

fn main() {
    let mut m = Migration::new();

    m.create_table("users", |t| {
        t.add_column("name", types::varchar(255));
        t.add_column("age", types::integer());
        t.add_column("owns_plushy_sharks", types::boolean());
    });

    println!("{}", m.make::<Pg>());
}