Database & ORM¶

Desi includes a Django-inspired database module with ORM, query builder, migrations, and transactions.

Quick Start¶

import db

# Define model
db.model("users")
db.auto_field("id")
db.char_field("name", 100, 0, 0)
db.int_field("age", 0, 0)

# Generate SQL
let sql: str = db.create_table_sql("users")
print(sql)
# CREATE TABLE IF NOT EXISTS users (
#   id SERIAL PRIMARY KEY,
#   name VARCHAR(100) NOT NULL,
#   age INTEGER NOT NULL DEFAULT 0
# )

CRUD Operations¶

Create (INSERT)¶

db.objects("users")
db.create(name="Alice", email="alice@test.com", age="30")

Uses **kwargs — pass any number of field="value" pairs. No fixed argument limits.

Read (SELECT)¶

# Filter with Django-style lookups
db.objects("users")
db.filter(name="Alice", age__gt="21")
let rows: int = db.fetch_all()

# Read results
let name: str = db.get_field(0, "name")
let email: str = db.get_field(0, "email")

Supported lookups:

Lookup	SQL	Example
`name`	`name = 'value'`	`db.filter(name="Alice")`
`age__gt`	`age > 'value'`	`db.filter(age__gt="21")`
`age__lt`	`age < 'value'`	`db.filter(age__lt="65")`
`name__contains`	`name LIKE '%value%'`	`db.filter(name__contains="Ali")`

Update¶

db.objects("users")
db.filter(name="Alice")
db.update_fields(email="new@test.com", age="31")

Delete¶

db.objects("users")
db.filter(name="Alice")
db.delete()

Get Single Row¶

db.objects("users")
let found: int = db.get(name="Alice")
if found > 0:
    let name: str = db.get_field(0, "name")

Model Definition¶

Django-Style `@model` Decorator (Recommended)¶

model("users")
class User:
    pub name: CharField(100)
    pub email: CharField(200, unique=true)
    pub age: IntField(default=0)
    pub active: BoolField(default=true)

model("posts")
class Post:
    pub title: CharField(200)
    pub body: TextField(nullable=true)
    pub author_id: ForeignKey(User, on_delete=CASCADE)

See the Models Guide for all field types, options, and ForeignKey details.

Procedural API (Low-Level)¶

db.model("products")
db.auto_field("id")
db.char_field("name", 200, 0, 0)
db.text_field("description", 1)
db.int_field("price", 0, 0)
db.float_field("weight", 1)
db.bool_field("active", 1, 0)
db.datetime_field("created_at", 0, 1, 0)
db.json_field("metadata", 1)
db.foreign_key("category_id", "categories", "id", "CASCADE", 0)

SQL Generation¶

let create_sql: str = db.create_table_sql("products")
let drop_sql: str = db.drop_table_sql("products")
let alter_sql: str = db.add_column_sql("products", "sku")

Query Builder¶

For complex queries, use the low-level query builder:

db.find("users")                       # SELECT
db.columns("name, email")              # columns
db.where("age", ">", "21")            # WHERE
db.join("INNER", "orders", "users.id = orders.user_id")
db.order_by("name", 0)                # ORDER BY name ASC
db.limit(10)
db.offset(20)
let sql: str = db.build_sql()

Database Connection¶

# Connect to PostgreSQL
db.connect("postgres", "localhost", 5432, "mydb", "user", "pass")

# Or MySQL
db.connect("mysql", "localhost", 3306, "mydb", "user", "pass")

# Execute SQL
db.execute(sql)
let rows: int = db.query("SELECT * FROM users")

# Check connection
let connected: int = db.is_connected()
db.close()

Transactions¶

db.begin()
db.objects("accounts")
db.filter(id="1")
db.update_fields(balance="900")
db.commit()
# Or: db.rollback_tx()

# Savepoints (nested transactions)
db.begin()
db.savepoint("sp1")
# ... operations ...
db.savepoint_rollback("sp1")  # undo to savepoint
db.commit()

Migrations¶

# Apply pending migrations
let applied: int = db.migrate()

# Rollback last migration
db.rollback()

# Check status
let status: str = db.migration_status()
print(status)