ORM

Objective: Create a PostgreSQL database that preserves state of a stock. Insert a few items. Then select some of them and present the results.

Python

ORM in use: sqlalchemy. Migration tool: alembic.

# DB in a container:
# docker run --name productsdb -e POSTGRES_PASSWORD=pass -p 5432:5432 -d postgres:15.3

import sqlalchemy as db
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy.orm import MappedAsDataclass, Session

class Base(MappedAsDataclass, DeclarativeBase):
#    metadata = db.MetaData()
    pass

class StockItem(Base):
    __tablename__ = "stock"
    name: Mapped[str] = mapped_column(primary_key=True)
    vendor: Mapped[str]
    quantity: Mapped[int]

engine = db.create_engine("postgresql+psycopg2://postgres:pass@127.0.0.1/postgres",)
#Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add_all([
        StockItem(name="Toothbrush", vendor="Limo", quantity=1497),
        StockItem(name="Comb", vendor="Takoon", quantity=210),
        StockItem(name="Towel", vendor="Beana", quantity=362),
        ])

    session.commit()    

    query = db.select(StockItem).where(StockItem.quantity >= 300)
    for user in session.scalars(query):
        print(user)

Restore commented lines to Initialize database.

Rust

ORM in use: diesel. Diesel comes with built-in migration tool.

// models.rs
use crate::schema::stock;
use diesel::prelude::*;

#[derive(Debug)]
#[derive(Insertable)]
#[derive(Queryable, Selectable)]
#[diesel(table_name = stock)]
#[diesel(check_for_backend(diesel::pg::Pg))]
pub struct StockItem {
    pub name: String,
    pub vendor: String,
    pub quantity: i32,
}

// schema.rs
// @generated automatically by Diesel CLI.

diesel::table! {
    stock (name) {
        name -> Varchar,
        vendor -> Varchar,
        quantity -> Int4,
    }
}

// main.rs
mod models;
mod schema;

use diesel::pg::PgConnection;
use diesel::prelude::*;
use self::schema::stock::dsl::*;
use self::models::*;

pub fn establish_connection() -> PgConnection {
     let database_url = "postgres://postgres:pass@127.0.0.1/postgres";
     PgConnection::establish(&database_url)
         .unwrap_or_else(|_| panic!("Error connecting to {}", database_url))
}

fn main() {
    let connection = &mut establish_connection();
    
    let new_item1 = StockItem { name: "Toothbrush".to_string(), vendor: "Limo".to_string(), quantity: 1497 };
    let new_item2 = StockItem { name: "Comb".to_string(), vendor: "Takoon".to_string(), quantity: 210 };
    let new_item3 = StockItem { name: "Towel".to_string(), vendor: "Beana".to_string(), quantity: 362 };
    
    diesel::insert_into(self::schema::stock::table)
        .values([new_item1, new_item2, new_item3])
        .returning(StockItem::as_returning())
        .get_results(connection)
        .expect("Error saving new stock item");
    
    let results = stock
        .filter(quantity.ge(300))
        .select(StockItem::as_select())
        .load(connection)
        .expect("Error loading stock items");

    for item in results {
        println!("{:?}", item);
    }
}

Crystal

ORM in use: granite. Migration tool: micrate. These are part of amber framework.

require "granite/adapter/pg"

Granite::Connections << Granite::Adapter::Pg.new(name: "testdb", url: "postgres://postgres:pass@127.0.0.1/postgres")

class Stock < Granite::Base
  connection testdb
  table stock

  column name : String, primary: true, auto: false
  column vendor : String
  column quantity : Int32
end

Stock.new(name: "Toothbrush", vendor: "Limo", quantity: 1497).save
Stock.new(name: "Comb", vendor: "Takoon", quantity: 210).save
Stock.new(name: "Towel", vendor: "Beana", quantity: 362).save

Stock.where(:quantity, :gteq, 300).select.each do |item|
  # puts item.name # accessing fields also possible
  puts item.to_json
end

• Initialize DB by calling: shards run micrate -- up
• Uninitialize DB by calling: shards run micrate -- down