Database & Models

Chapter 4: Storing and retrieving data with SQLAlchemy.

Most apps need to store data. In sparQ, you define your data structures using SQLAlchemy models. Let's learn how to create models and work with the database.

What is a Model?

A model is a Python class that represents a database table. Each instance of the class is a row in that table. SQLAlchemy handles all the SQL for you.

# This Python class...
class Task(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200))
    done = db.Column(db.Boolean, default=False)

# ...becomes this database table
# CREATE TABLE task (
#     id INTEGER PRIMARY KEY,
#     title VARCHAR(200),
#     done BOOLEAN
# )

Creating Your First Model

Models go in the models/ folder of your app. Let's create a Task model:

# models/task.py
from system.db import db

class Task(db.Model):
    __tablename__ = 'myapp_tasks'  # Name your table with app prefix

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    description = db.Column(db.Text)
    done = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=db.func.now())

    def __repr__(self):
        return f'<Task {self.title}>'

Column Types

SQLAlchemy provides many column types:

Type	Python Type	Use For
db.Integer	int	Numbers, IDs
db.String(n)	str	Short text (n = max length)
db.Text	str	Long text, descriptions
db.Boolean	bool	True/False flags
db.DateTime	datetime	Dates and times
db.Float	float	Decimal numbers
db.JSON	dict/list	JSON data

The Fat Model Pattern

sparQ encourages putting your business logic inside models. This keeps your controllers thin and your code organized:

# models/task.py
class Task(db.Model):
    __tablename__ = 'myapp_tasks'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    done = db.Column(db.Boolean, default=False)

    # CREATE
    @classmethod
    def create(cls, title, description=None):
        task = cls(title=title, description=description)
        db.session.add(task)
        db.session.commit()
        return task

    # READ
    @classmethod
    def get_all(cls):
        return cls.query.order_by(cls.created_at.desc()).all()

    @classmethod
    def get_by_id(cls, id):
        return cls.query.get(id)

    @classmethod
    def get_incomplete(cls):
        return cls.query.filter_by(done=False).all()

    # UPDATE
    def mark_done(self):
        self.done = True
        db.session.commit()

    def update(self, **kwargs):
        for key, value in kwargs.items():
            if hasattr(self, key):
                setattr(self, key, value)
        db.session.commit()

    # DELETE
    def delete(self):
        db.session.delete(self)
        db.session.commit()

Now your controllers can simply call these methods:

# In your controller
tasks = Task.get_incomplete()
task = Task.create(title="Buy groceries")
task.mark_done()

Relationships

Models can be related to each other. Here's a one-to-many relationship where a Project has many Tasks:

# models/project.py
class Project(db.Model):
    __tablename__ = 'myapp_projects'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False)

    # One project has many tasks
    tasks = db.relationship('Task', backref='project', lazy=True)


# models/task.py
class Task(db.Model):
    __tablename__ = 'myapp_tasks'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)

    # Foreign key to project
    project_id = db.Column(db.Integer, db.ForeignKey('myapp_projects.id'))

Now you can access tasks through a project:

project = Project.query.get(1)
for task in project.tasks:
    print(task.title)

# Or get a task's project
task = Task.query.get(1)
print(task.project.name)

Querying Data

SQLAlchemy provides a powerful query interface:

# Get all tasks
Task.query.all()

# Get one by ID
Task.query.get(1)

# Filter
Task.query.filter_by(done=False).all()
Task.query.filter(Task.title.contains('grocery')).all()

# Order
Task.query.order_by(Task.created_at.desc()).all()

# Limit
Task.query.limit(10).all()

# First result only
Task.query.filter_by(done=False).first()

# Count
Task.query.filter_by(done=False).count()

Seeding Initial Data

You can add initial data using the init_database hook in module.py:

# module.py
from .models.task import Task

def init_database(db):
    """Called after database tables are created."""
    # Only seed if table is empty
    if Task.query.count() == 0:
        Task.create(title="Welcome! This is your first task")
        Task.create(title="Click the checkbox to mark a task done")

Model Mixins

sparQ provides reusable mixins that add common functionality to your models:

SoftDeleteMixin

Instead of permanently deleting records, soft delete marks them as deleted while keeping the data. Users can restore accidentally deleted items.

from system.db.mixins import SoftDeleteMixin

class Contact(db.Model, SoftDeleteMixin):
    __tablename__ = 'myapp_contacts'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False)

The mixin adds deleted_at and deleted_by_id columns, plus these methods:

Method	Description
Contact.active()	Query only non-deleted records
Contact.deleted()	Query only deleted records
Contact.with_deleted()	Query all records
contact.soft_delete()	Mark as deleted
contact.restore()	Restore a deleted record

# Get active contacts only (default for user-facing queries)
contacts = Contact.active().filter_by(is_vip=True).all()

# Delete and restore
contact.soft_delete()
contact.restore()

AuditMixin

Tracks who created and updated records:

from system.db.mixins import AuditMixin

class Task(db.Model, AuditMixin):
    # Adds: created_by_id, updated_by_id, created_at, updated_at
    pass

Key Takeaways

• Models are Python classes that represent database tables
• Use the db.Column to define fields with types
• Put business logic in model methods (fat model pattern)
• Use relationships to connect models together
• SQLAlchemy provides powerful querying capabilities

Routes & Controllers

Chapter 5: Handling HTTP requests and returning responses.

Controllers are the traffic cops of your app. They receive HTTP requests, call your models to get or save data, and return responses (usually HTML pages). Let's see how to build them.

What is a Controller?

A controller is a Python file with Flask routes that handle different URLs. When a user visits your app's URL, Flask finds the matching route and runs your code.

# controllers/main.py
from flask import Blueprint, render_template

bp = Blueprint('main', __name__, url_prefix='/tasks')

@bp.route('/')
def index():
    # This runs when user visits your app
    return render_template('tasks/desktop/index.html')

URL Structure for Marketplace Apps

Marketplace apps use a special URL pattern that includes the app's mappid:

/m/{mappid}/{slug}
 │    │        │
 │    │        └── From your main_route in __manifest__.py
 │    └── Your app's unique 6-char identifier
 └── Marketplace prefix

For example, if your manifest has:

manifest = {
    "mappid": "x7k2m9",
    "main_route": "/tasks",
    ...
}

Your app will be accessible at /m/x7k2m9/tasks.

Creating a Controller

Controllers go in the controllers/ folder. Here's a complete example:

# controllers/main.py
from flask import Blueprint, render_template, request, redirect, url_for, flash
from ..models.task import Task

bp = Blueprint('main', __name__, url_prefix='/tasks')

@bp.route('/')
def index():
    """Show all tasks."""
    tasks = Task.get_all()
    return render_template('tasks/desktop/index.html', tasks=tasks)

The Blueprint groups related routes together. The url_prefix means all routes in this file start with /tasks.

Common Route Patterns

Most apps need these standard CRUD (Create, Read, Update, Delete) routes:

List View

@bp.route('/')
def index():
    tasks = Task.get_all()
    return render_template('tasks/desktop/index.html', tasks=tasks)

Detail View

@bp.route('/<int:id>')
def show(id):
    task = Task.get_by_id(id)
    if not task:
        abort(404)
    return render_template('tasks/desktop/show.html', task=task)

Create Form

@bp.route('/new', methods=['GET', 'POST'])
def create():
    if request.method == 'POST':
        title = request.form.get('title')
        Task.create(title=title)
        flash('Task created!', 'success')
        return redirect(url_for('tasks.main.index'))
    return render_template('tasks/desktop/form.html')

Edit Form

@bp.route('/<int:id>/edit', methods=['GET', 'POST'])
def edit(id):
    task = Task.get_by_id(id)
    if request.method == 'POST':
        task.update(title=request.form.get('title'))
        flash('Task updated!', 'success')
        return redirect(url_for('tasks.main.show', id=id))
    return render_template('tasks/desktop/form.html', task=task)

Delete

@bp.route('/<int:id>/delete', methods=['POST'])
def delete(id):
    task = Task.get_by_id(id)
    task.delete()
    flash('Task deleted!', 'success')
    return redirect(url_for('tasks.main.index'))

Getting Form Data

Flask provides the request object to access form data:

from flask import request

# Form data (POST)
title = request.form.get('title')
done = request.form.get('done') == 'on'  # Checkbox

# Query parameters (GET)
search = request.args.get('q', '')
page = request.args.get('page', 1, type=int)

Redirects and Flash Messages

After creating or updating data, redirect the user and show a message:

from flask import redirect, url_for, flash

@bp.route('/new', methods=['POST'])
def create():
    Task.create(title=request.form.get('title'))
    flash('Task created successfully!', 'success')
    return redirect(url_for('tasks.main.index'))

Flash message types: success, error, warning, info

Authentication

Protect routes so only logged-in users can access them:

from system.auth import login_required, current_user

@bp.route('/dashboard')
@login_required
def dashboard():
    # Only runs if user is logged in
    user_tasks = Task.query.filter_by(user_id=current_user.id).all()
    return render_template('tasks/desktop/dashboard.html', tasks=user_tasks)

The @login_required decorator redirects anonymous users to the login page.

HTMX Endpoints

HTMX lets you update parts of a page without a full reload. Return just the HTML fragment, not the whole page:

@bp.route('/<int:id>/toggle', methods=['POST'])
def toggle(id):
    task = Task.get_by_id(id)
    task.done = not task.done
    db.session.commit()
    # Return just the updated task row
    return render_template('tasks/desktop/partials/task_row.html', task=task)

And in your template:

<tr hx-post="/tasks/{{ task.id }}/toggle"
    hx-swap="outerHTML">
    <td>{{ task.title }}</td>
    <td>{{ "Done" if task.done else "Pending" }}</td>
</tr>

Route Naming

Use url_for() to generate URLs instead of hardcoding them:

# In Python
url_for('tasks.main.index')           # /tasks/
url_for('tasks.main.show', id=5)      # /tasks/5
url_for('tasks.main.edit', id=5)      # /tasks/5/edit

<!-- In templates -->
<a href="{{ url_for('tasks.main.show', id=task.id) }}">View</a>

The naming pattern is: app_name.blueprint_name.function_name

Device-Aware Rendering

sparQ provides render_device_template() as the recommended way to render templates. It automatically serves the mobile version of a template on mobile devices, falling back to the desktop version if no mobile template exists.

from system.device.template import render_device_template

@bp.route('/')
def index():
    tasks = Task.get_all()
    return render_device_template('tasks/desktop/index.html', tasks=tasks)

Use render_device_template instead of Flask's render_template in all your controllers. You can test mobile rendering by appending ?device=mobile to any URL.

Key Takeaways

• Controllers handle HTTP requests and return responses
• Use Blueprints to organize routes with a URL prefix
• Standard CRUD routes: index, show, create, edit, delete
• Use @login_required to protect routes
• Use render_device_template instead of render_template for device-aware rendering
• Return HTML fragments for HTMX endpoints
• Always use url_for() to generate URLs