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:

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:

# 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