1. Full Tutorial

Full tutorial: a small shop database

This tutorial builds a realistic mini-domain: users, products, orders and order items. It demonstrates primary keys, foreign keys, joins, grouping, indexes, persistence, SQL export and encrypted email.

1. Create schema

from relpy import RelPy, AutoNumber, col
from relpy import count, sum_, avg

key = RelPy.generate_encryption_key()
db = RelPy(encryption_key=key)

# Users
_db = db.create_table("users")
db.add_column("users", "id", AutoNumber, is_primary_key=True)
db.add_column("users", "name", str, nullable=False)
db.add_column("users", "email", str, nullable=False, is_encrypted=True)

# Products
db.create_table("products")
db.add_column("products", "id", AutoNumber, is_primary_key=True)
db.add_column("products", "name", str, nullable=False)
db.add_column("products", "price", float, nullable=False)

# Orders
db.create_table("orders")
db.add_column("orders", "id", AutoNumber, is_primary_key=True)
db.add_column("orders", "user_id", int, nullable=False, references="users.id", on_delete="CASCADE")
db.add_column("orders", "status", str, nullable=False)

# Order items - composite primary key
db.create_table("order_items")
db.add_column("order_items", "order_id", int, nullable=False, references="orders.id", on_delete="CASCADE")
db.add_column("order_items", "product_id", int, nullable=False, references="products.id")
db.add_column("order_items", "quantity", int, nullable=False)
db.set_primary_key("order_items", ["order_id", "product_id"])

2. Insert rows

alice = db.insert("users", {"name": "Alice", "email": "alice@example.com"})
bob = db.insert("users", {"name": "Bob", "email": "bob@example.com"})

keyboard = db.insert("products", {"name": "Keyboard", "price": 120.0})
mouse = db.insert("products", {"name": "Mouse", "price": 80.0})

order1 = db.insert("orders", {"user_id": alice["id"], "status": "paid"})
order2 = db.insert("orders", {"user_id": bob["id"], "status": "pending"})

db.insert("order_items", {"order_id": order1["id"], "product_id": keyboard["id"], "quantity": 1})
db.insert("order_items", {"order_id": order1["id"], "product_id": mouse["id"], "quantity": 2})
db.insert("order_items", {"order_id": order2["id"], "product_id": mouse["id"], "quantity": 1})

Foreign keys are validated on insert. A row that references a missing users.id, orders.id or products.id will fail.

3. Query one table

paid_orders = (
    db.query("orders")
      .where(col("status") == "paid")
      .to_list()
)

SELECT *
FROM orders
WHERE status = 'paid';

[{"id": 1, "user_id": 1, "status": "paid"}]

4. Join tables

rows = (
    db.query("orders")
      .join("users")
      .where(col("orders.status") == "paid")
      .select("orders.id", "users.name", "orders.status")
      .to_list(decrypt=True)
)

Because orders.user_id references users.id, RelPyDB can infer the join automatically.

[{"orders.id": 1, "users.name": "Alice", "orders.status": "paid"}]

5. Group and aggregate

summary = (
    db.query("orders")
      .group_by("status")
      .aggregate(
          order_count=count(),
      )
      .order_by("status")
      .to_list()
)

[{"status": "paid", "order_count": 1}, {"status": "pending", "order_count": 1}]

6. Index encrypted email and query naturally

db.create_index("users", "email")

user = (
    db.query("users")
      .where(col("email") == "alice@example.com")
      .one(decrypt=True)
)

The email is stored encrypted, but equality lookup can use a blind index.

7. Save, load and export SQL

db.save("shop.relpy.json")

loaded_db = RelPy.load("shop.relpy.json", encryption_key=key)
print(loaded_db.to_list("users"))
print(loaded_db.to_list("users", decrypt=True))
print(loaded_db.to_sql("users", decrypt=True))