Database and ORM

Ushka includes a powerful asynchronous ORM built on top of SQLAlchemy 2.0. It's designed to be intuitive by implementing an Active Record pattern, which means your model classes themselves have methods for querying the database.

Configuration

First, configure your database URL in ushka.toml. By default, it's set up for a local SQLite database, but you can use any database supported by SQLAlchemy (like PostgreSQL or MySQL).

For PostgreSQL, for example:

# in ushka.toml
[database]
url = "postgresql+asyncpg://user:password@host:port/dbname"

Ushka automatically handles creating the asynchronous engine and session management for you.

Defining Models

To define a database model, create a class that inherits from ushka.contrib.db.Model. Ushka will automatically generate the table name based on your class name (e.g., ProductItem becomes product_items).

Primary key id columns are automatically added. You can define other columns using methods on the db object.

# in apps/inventory/models.py
from ushka.contrib.db import db, Model

class Product(Model):
    name = db.String(100, unique=True, nullable=False)
    description = db.Text(nullable=True)
    price = db.Float(nullable=False)
    stock = db.Int(default=0)

class Category(Model):
    name = db.String(50, unique=True, nullable=False)

Active Record Operations

You interact with the database directly through your model classes and instances. All database operations are async and must be awaited.

Creating Records

Create an instance of your model and call the .save() method.

async def create_new_product():
    new_product = Product(
        name="Super Gadget",
        description="The best gadget ever.",
        price=99.99,
        stock=50
    )
    await new_product.save()
    print(f"Created product with ID: {new_product.id}")

You can also use the .create() class method as a shortcut:

product = await Product.create(name="Another Gadget", price=49.99)

Querying Records

Get a single record by its primary key:

product = await Product.get(1)
if product:
    print(f"Found: {product.name}")

Get all records:

all_products = await Product.all()
for product in all_products:
    print(product.name)

Count records:

num_products = await Product.count()

Simple Search: The .search() method performs a case-insensitive LIKE query across all String and Text columns in your model.

# Finds products where the name or description contains "gadget"
gadgets = await Product.search("gadget")

Advanced Queries with SQLAlchemy Core: For more complex queries, you can use the .select() method to get a SQLAlchemy Select object and build your query.

from sqlalchemy import and_

async def find_expensive_products():
    query = Product.select().where(
        and_(
            Product.price > 100,
            Product.stock > 0
        )
    )
    # To execute the query, you need a session
    # (This part of the API might be improved in future Ushka versions)
    async with db.atomic() as session:
        result = await session.execute(query)
        products = result.scalars().all()
        return products

Updating Records

To update a record, change its attributes and call .save().

product = await Product.get(1)
if product:
    product.price = 129.99
    await product.save()

Deleting Records

To delete a record, call the .delete() method on an instance.

product = await Product.get(1)
if product:
    await product.delete()

Database Migrations

Ushka integrates with Alembic for database schema migrations. This allows you to evolve your database schema as you change your Model definitions.

1. Initialize the migration environment: This command creates a migrations/ directory and an alembic.ini file. You only need to run this once per project.

ushka db init

2. Generate a new migration: After you change a model (e.g., add a new column), Ushka can auto-generate a migration script for you.

ushka db make -m "add_description_to_product"

3. Apply the migration: This runs the migration script to update your database schema.

ushka db migrate

Other useful migration commands include: * ushka db revert: Reverts the last migration. * ushka db status: Shows the current migration status. * ushka db history: Lists all migration scripts.