ORM Basics

Keywords: database model, database queries, sql orm, sqlite database, insert records, filter query, save model, delete model, database objects, active record

Asok includes a powerful, built-in ORM supporting SQLite (default, zero dependencies), PostgreSQL, and MySQL.

By default, SQLite is used and a db.sqlite3 file is created automatically. Database tables are auto-created or updated from your model definitions on application start.

Define a model

# src/models/post.py
from asok import Model, Field, Relation

class Post(Model):
    title      = Field.String()
    body       = Field.String()
    slug       = Field.Slug(populate_from='title')
    published  = Field.Boolean(default=False)
    author_id  = Field.ForeignKey(lambda: User)
    cover      = Field.File(upload_to='posts')
    created_at = Field.CreatedAt()
    updated_at = Field.UpdatedAt()
    deleted_at = Field.SoftDelete()

Custom table name

By default, the table name is derived from the model name by converting CamelCase to snake_case and pluralizing (Post → posts, OrderItem → order_items, Category → categories). To override:

class Category(Model):
    __tablename__ = "categories"  # Explicit table name
    name = Field.String()

Schema Evolution & Migrations

Asok provides two ways to manage your database schema: an Auto-Evolution mode for rapid prototyping, and Versioned Migrations for professional projects.

1. Versioned Migrations (Recommended)

This is the professional way to manage schema changes. Asok detects changes in your models and generates migration files that you can review, version-control, and apply.

asok make migration add_bio_to_users
asok migrate

See the Migrations Documentation for full details.

2. Auto-Evolution (minimal setup)

For small projects or rapid prototyping, Asok can automatically update your database on app start. When Model.create_table() is called, the framework inspects the table and automatically adds any missing columns.

• Safe: It only adds columns (ALTER TABLE ... ADD COLUMN), never deletes or renames.

• Automatic: No migration files needed.

• Default: This is how Asok handles its internal tables and simple projects.

Query Scopes

Scopes allow you to define common sets of constraints as reusable methods. To define a scope, add a static method prefixed with scope_ to your Model.

class Product(Model):
    name = Field.text()
    price = Field.decimal()
    
    @staticmethod
    def scope_expensive(query, threshold=100):
        return query.where('price', '>', threshold)

# Usage:
products = Product.query().expensive(500).get()

The first argument to a scope is always the current Query object.

Field types

Field	SQLite	Notes
Field.String(max_length=255)	TEXT	Short text — <input type="text"> in admin / Form.from_model(). max_length defaults to 255
Field.Text(wysiwyg=False)	TEXT	Long text — <textarea> in admin. Pass wysiwyg=True for rich text editor.
Field.SearchableText()	TEXT	Full-text search field, automatically indexed via FTS5. Use Model.search() for ranked results.
Field.Slug(populate_from="title", always_update=False)	TEXT	URL-friendly string. Set always_update=True to regenerate it every time the source field changes.
Field.Email(max_length=255)	TEXT	Validated email — <input type="email"> and rejected on save if invalid
Field.Integer()	INTEGER
Field.Float(precision=2)	REAL	precision controls form input step (e.g. step="0.01").
Field.Boolean()	INTEGER	0/1 — rendered as <input type="checkbox">
Field.Date()	TEXT	ISO format
Field.DateTime()	TEXT	ISO format
Field.Time()	TEXT	Time-only value (HH:MM:SS)
Field.Password()	TEXT	Auto-hashed (PBKDF2-SHA256, 600k)
Field.EncryptedString()	TEXT	Symmetrically encrypted using AES-256 (Fernet) with the app’s SECRET_KEY. Requires pip install "asok[security]"
Field.URL()	TEXT	URL with format validation
Field.Color()	TEXT	Hex color code — rendered as <input type="color">
Field.ForeignKey(Model)	INTEGER	FK to another model. Use dropdown=True for rich select in forms.
Field.Dropdown(choices)	TEXT	Fixed choices (list of tuples (value, label)) — renders as a premium searchable dropdown.
Field.File(upload_to='dir')	TEXT	Stores filename, files saved under uploads/
Field.CreatedAt()	TEXT	Set once on first save
Field.UpdatedAt()	TEXT	Updated on every save
Field.SoftDelete()	TEXT	Enables soft delete (see below)

Common Field Parameters

All Field types support these optional parameters:

class User(Model):
    email = Field.String(
        unique=True,      # Creates UNIQUE constraint + index
        nullable=False,   # NOT NULL constraint
        default="",       # Default value
        index=True,       # Creates database index for fast lookups
        hidden=True,      # Hide from forms/admin
        protected=True,   # Exclude from mass assignment
        label="Email Address",  # Custom form label
    )

UI Customization (Tailwind CSS)

All fields also accept arbitrary keyword arguments (**kwargs) which are stored in field.attrs. These are automatically passed to Form.from_model(), allowing you to define deep UI styling (like nested Tailwind classes) directly on your model:

class Project(Model):
    status = Field.Dropdown(
        choices=[("active", "Active"), ("archived", "Archived")],
        option__class="text-indigo-600 font-medium", # Target nested options
        menu__class="shadow-2xl rounded-xl border-none" # Target the menu
    )
    
    is_public = Field.Boolean(
        form_type="toggle",
        slider__class="bg-indigo-500", # Target the toggle slider
        container__class="p-4 bg-gray-50 rounded-lg"
    )

Performance tip: Add index=True to columns frequently used in WHERE, ORDER BY, or GROUP BY clauses. See Advanced ORM for more details.

Rich Dropdowns and Selection

You can enable premium, searchable dropdowns directly from the model definition. These are automatically picked up by Form.from_model().

Fixed choices with Field.Dropdown

class Ticket(Model):
    status = Field.Dropdown(
        label="Ticket Status",
        choices=[("open", "Open"), ("pending", "Pending"), ("closed", "Closed")],
        searchable=True
    )

Relationships with Field.ForeignKey

Enable dropdown=True to replace the standard <select> with a rich searchable UI:

class Post(Model):
    category_id = Field.ForeignKey(
        Category, 
        dropdown=True,
        dropdown_title="name",      # Field to use as title
        dropdown_subtitle="desc",   # Optional subtitle field
        dropdown_image="icon_url",  # Optional image/avatar field
        dropdown_searchable=True
    )

Choosing the right text field

Three text-flavored fields to pick from depending on intent — they all map to SQLite TEXT but they affect form rendering and validation:

class Contact(Model):
    name    = Field.String(max_length=100)  # short text, <input type="text">
    email   = Field.Email()                  # email input + auto-validation
    message = Field.Text()                   # long text, <textarea>

Field.Email() validates the value on save() (and on Model.create()) using a basic regex. Invalid emails raise ModelError:

Contact.create(email='not-an-email', message='hi')
# → ModelError: Email is not a valid email address.

This validation happens regardless of whether the value comes from a Form, an API call, or hand-written code. Forms generated via Form.from_model() also pick up the email validation rule automatically (see Forms).

Field options

Field.String(max_length=80, default='draft', unique=True, nullable=False)

All fields accept default, unique, and nullable. String and Email additionally accept max_length. Float accepts precision.

Labels, rules and custom error messages

Fields can define labels, validation rules, and custom error messages that are automatically used when generating forms with Form.from_model():

class Contact(Model):
    __tablename__ = "contacts"

    name = Field.String(
        max_length=100,
        nullable=False,
        label="Full Name",              # Custom label (default: "Name")
        rules="min:4|alpha_spaces",      # Custom validation rules
        messages={
            "required": "Please enter your full name",
            "min": "Name must be at least 4 characters",
            "max": "Name cannot exceed 100 characters",
            "alpha_spaces": "Name can only contain letters and spaces"
        }
    )

    email = Field.Email(
        max_length=100,
        nullable=False,
        label="Email Address",
        messages={
            "required": "Email is required to contact you",
            "email": "Please provide a valid email address"
        }
    )

    message = Field.Text(
        nullable=False,
        label="Your Message",
        rules="min:10",
        messages={
            "required": "Please tell us what you want to say",
            "min": "Message must be at least 10 characters"
        }
    )

When you generate a form from this model:

form = Form.from_model(Contact, request)

The form will:

• Use “Full Name” as the label instead of “Name”

• Apply all validation rules (auto-generated + custom)

• Display your custom error messages when validation fails

How rules are combined:

Asok automatically combines:

1. Auto-generated rules based on field type and constraints:

   • required (if nullable=False)

   • max:N (if max_length=N)

   • email (if Field.Email())

   • tel (if Field.Tel())

   • etc.

2. Your custom rules (via the rules parameter)

For example, the name field above will have these combined rules:

required|max:100|min:4|alpha_spaces

Database Engines & Drivers

By default, Asok requires no configuration and uses SQLite. If you want to use PostgreSQL or MySQL, you must install the optional packages and define the DATABASE_URL in your .env file.

Supported Backends

Engine	Installation	DATABASE_URL Format
SQLite	Included by default	sqlite:///db.sqlite3 or sqlite:///:memory:
PostgreSQL	pip install "asok[postgres]" (or "asok[postgres-binary]")	postgresql://user:password@localhost:5432/dbname
MySQL	pip install "asok[mysql]"	mysql://user:password@localhost:3306/dbname

Installation Combined with other extras

If you are also using Redis for caching/sessions, you can combine extras:

pip install "asok[postgres-binary,redis]"

CRUD

# Create
post = Post.create(title='Hello', body='...')

# Read
Post.find(id=1)
Post.find(slug='hello')
Post.all()
Post.all(published=True, order_by='-created_at', limit=10)
Post.count(published=True)
Post.exists(slug='hello')

# Update
post.title = 'Updated'
post.save()

# Or set + save in one call (perfect with form.data)
post.update(title='Updated', body='...')

# Delete
post.delete()

Find-or-create helpers

Post.first_or_create(slug='hello', defaults={'title': 'Hello', 'body': '...'})
Post.update_or_create(slug='hello', defaults={'title': 'Updated'})

Query builder

For anything more complex than equality filters, use the chainable Query builder:

Post.query() \
    .where('views', '>', 100) \
    .where('published', True) \
    .order_by('-created_at') \
    .limit(20) \
    .get()

Post.where('title', 'LIKE', '%python%').get()
Post.where_in('id', [1, 2, 3]).get()
Post.like('title', '%python%').get() # Shortcut for LIKE

Methods

Method	Description
.where(col, op, val)	Add a WHERE clause (=, !=, <, >, <=, >=, LIKE, NOT LIKE, IN, NOT IN)
.where(col, val)	Shortcut for equality
.where_in(col, [vals])	WHERE col IN (…)
.like(col, pattern)	Shortcut for LIKE
.order_by('col') / .order_by('-col')	ASC / DESC
.limit(n) / .offset(n)	Pagination
.with_('relation', ...)	Eager-load relations (avoids N+1)
.get()	Execute, return list
.first()	Execute, return first or None
.count()	Aggregate count
.sum('col')	Sum values in column
.avg('col')	Average value
.min('col')	Minimum value
.max('col')	Maximum value
.select(*cols)	Select specific columns (e.g., aggregates)
.group_by(*cols)	GROUP BY results
.pluck('col')	List of single column values
.update(**vals)	Bulk UPDATE
.delete()	Bulk DELETE
.exists()	Bool
.to_sql()	Return SQL string with placeholders
.raw_sql()	Return SQL string with parameters interpolated (debug only)

Inspecting SQL

For debugging purposes, you can see the actual SQL that Asok is about to execute.

1. From a Query object

You can inspect a query before executing it:

query = Product.query().where('price', '>', 100)
print(query.to_sql())
# → SELECT * FROM products WHERE price > ?

print(query.raw_sql())
# → SELECT * FROM products WHERE price > 100

2. From a ModelList (Result)

Even after executing a query, the resulting ModelList remembers the SQL that produced it. This works for .all(), .get(), .search(), and even relationships:

contacts = Contact.all()
print(contacts.to_sql())
# → SELECT * FROM contacts

# Useful for debugging complex relationship filters
user = User.find(id=1)
user_posts = user.posts  # This returns a ModelList
print(user_posts.raw_sql())
# → SELECT * FROM posts WHERE user_id = 1

Debug Only: raw_sql() is intended strictly for debugging and inspection. The interpolation is naive and not secure against SQL injection. Never attempt to execute the output of these methods in your application logic.

3. Using the global helper

from asok.orm import convert_sql_to_text
print(convert_sql_to_text(Product.query().limit(5)))

Raw SQL

Post.raw("SELECT * FROM posts WHERE views > ?", [100])

Relationships

class User(Model):
    name = Field.String()
    posts = Relation.HasMany('Post')
    profile = Relation.HasOne('Profile')
    roles = Relation.BelongsToMany('Role')

class Post(Model):
    author_id = Field.ForeignKey(lambda: User)
    author = Relation.BelongsTo('User')

Relation	Returns	Default FK
HasMany('Post')	list	<owner>_id on the target
HasOne('Profile')	single or None	<owner>_id on the target
BelongsTo('User')	single or None	<target>_id on self
BelongsToMany('Role')	list	pivot table <a>_<b> (alphabetical)

Access them as properties (not methods):

user = User.find(id=1)
user.posts       # list of Post (property, no parentheses)
user.profile     # Profile or None
user.roles       # list of Role
post.author      # User or None

Custom keys

Relation.HasMany('Post', foreign_key='writer_id')
Relation.BelongsToMany('Tag', pivot_table='post_tags', pivot_fk='post_id', pivot_other_fk='tag_id')

Eager loading

Avoid N+1 queries by pre-loading relations:

posts = Post.query().with_('author').limit(20).get()
for p in posts:
    p.author  # served from cache, no extra query

BelongsToMany: attach / detach / sync

post.attach('tags', [1, 2, 3])     # add pivot rows
post.detach('tags', [2])           # remove specific
post.detach('tags')                # remove all
post.sync('tags', [4, 5])          # replace all with these ids

Soft delete

Add Field.SoftDelete() to enable it on a model:

class Post(Model):
    title = Field.String()
    deleted_at = Field.SoftDelete()

Now delete() only sets deleted_at. Soft-deleted rows are excluded from all queries by default.

post.delete()              # soft delete
post.restore()             # un-delete
post.force_delete()        # permanent

Post.with_trashed().get()  # include deleted
Post.only_trashed().get()  # only deleted

File fields

class Post(Model):
    cover = Field.File(upload_to='posts')

The column stores the filename. The actual file is saved to uploads/posts/<filename> when set via a form. Use request.files and UploadedFile.save() for manual handling — see File Storage.

Lifecycle hooks

Override any of these on your model:

class Post(Model):
    def before_save(self): ...
    def after_save(self): ...
    def before_create(self): ...
    def after_create(self): ...
    def before_update(self): ...
    def after_update(self): ...
    def before_delete(self): ...
    def after_delete(self): ...

Pagination

result = Post.paginate(page=2, per_page=10, order_by='-created_at', published=True)

result['items']         # list of Post
result['total']         # total count
result['pages']         # total pages
result['current_page']  # 2

Password hashing

Field.Password() auto-hashes on save (PBKDF2-SHA256, 600k iterations):

class User(Model):
    email = Field.String(unique=True)
    password = Field.Password()

user = User.create(email='a@b.com', password='secret')
user.check_password('password', 'secret')  # True

Encrypted fields

For storing sensitive information in the database (e.g. credit cards, SSNs, personal phone numbers) in compliance with GDPR or PCI-DSS, use Field.EncryptedString().

Values are symmetrically encrypted using AES-256 (via the cryptography package’s Fernet engine) using the application’s SECRET_KEY when saving to the database, and automatically decrypted when loaded.

class Customer(Model):
    name = Field.String()
    ssn = Field.EncryptedString()  # Automatically encrypted/decrypted

customer = Customer.create(name="John", ssn="123-45-6789")

# In memory, the attribute is plaintext:
print(customer.ssn)  # "123-45-6789"

# In the database, the value is securely encrypted:
# e.g., "gAAAAABmX..."

Because encrypted fields use non-deterministic AES encryption (the ciphertext differs each time), you cannot perform direct database queries or index searches on encrypted columns (e.g. Customer.where('ssn', '123-45-6789').get()). Querying must be done on another field or by filtering in Python.

The cryptography library is required for encrypted fields. Install it using pip install cryptography or pip install "asok[security]".

Slug auto-generation

slug = Field.Slug(populate_from='title')
# "Hello World!" → "hello-world"

Generated on save if empty.

Serialization

post.to_dict()
# {"id": 1, "title": "Hello", ...}

For controlled API output, use Schema (see Serialization):

from asok import Schema, Field

class PostSchema(Schema):
    title = Field.String()
    slug  = Field.String()

PostSchema().dump(post)
PostSchema(many=True).dump(posts)

Performance

• Thread-local connections — reused within the same thread

• WAL mode — concurrent reads while writing

• Eager loading — .with_(...) batches related queries

• Auto-indexes — unique=True and ForeignKey get indexed automatically

• No configuration needed