Flask Database Models

When building Flask applications that need to interact with databases, you'll want to use a structured approach to define your data. This is where database models come into play. In Flask, models typically represent tables in your database and provide a Pythonic way to interact with your data.

What Are Flask Database Models?

Flask database models are Python classes that define the structure of your database tables. They serve as a bridge between your Python code and your database, allowing you to:

• Define your database schema in Python code
• Create, read, update, and delete records without writing raw SQL
• Establish relationships between different tables
• Validate data before saving it to the database

In Flask applications, the most common way to work with database models is through an ORM (Object-Relational Mapper) called SQLAlchemy.

Setting Up SQLAlchemy with Flask

Before we dive into creating models, we need to set up SQLAlchemy in our Flask application:

1. First, install the required packages:

bash

pip install flask-sqlalchemy

2. Configure SQLAlchemy in your Flask application:

python

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

The SQLALCHEMY_DATABASE_URI specifies which database to connect to. In this example, we're using SQLite, but you can connect to MySQL, PostgreSQL, or other databases by changing this URI.

Creating Your First Model

Let's create a simple model for a blog application. We'll start with a User model:

python

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password = db.Column(db.String(60), nullable=False)

    def __repr__(self):
        return f"User('{self.username}', '{self.email}')"

Let's break down what's happening here:

• We create a class that inherits from db.Model
• We define columns using db.Column()
• For each column, we specify the data type (Integer, String, etc.)
• We can add constraints like primary_key, unique, and nullable
• The __repr__ method defines how the model will be printed for debugging

Common Column Types in SQLAlchemy

SQLAlchemy provides many column types to match different data needs:

python

# Basic types
db.Integer       # Whole numbers
db.Float         # Decimal numbers
db.String(size)  # Text with maximum length
db.Text          # Longer text without size limit
db.Boolean       # True/False values
db.Date          # Date values
db.DateTime      # Date and time values

# Special types
db.Enum          # List of possible values
db.PickleType    # Stores serialized Python objects
db.LargeBinary   # Stores large binary data

Model Relationships

One of the most powerful features of an ORM is the ability to define relationships between tables. Let's add a Post model that relates to our User model:

python

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    content = db.Column(db.Text, nullable=False)
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    
    # Define a foreign key relationship
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    
    # Define the relationship with the User model
    author = db.relationship('User', backref=db.backref('posts', lazy=True))

    def __repr__(self):
        return f"Post('{self.title}', '{self.date_posted}')"

In this example:

• The user_id column is a foreign key that references the id column in the user table
• The author attribute creates a relationship between Post and User
• The backref creates a posts attribute in the User class that allows you to access all posts by a user

Types of Relationships

SQLAlchemy supports various relationship types:

1. One-to-Many: One record in the first table relates to many in the second (like one user having many posts)

python

class User(db.Model):
    # ...
    posts = db.relationship('Post', backref='author', lazy=True)

2. Many-to-One: This is the same relationship as One-to-Many, but viewed from the other side

3. One-to-One: One record in the first table relates to exactly one in the second

python

class User(db.Model):
    # ...
    profile = db.relationship('Profile', backref='user', uselist=False)

4. Many-to-Many: Records in both tables can relate to multiple records in the other

python

# Association table for many-to-many relationship
tags_posts = db.Table('tags_posts',
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')),
    db.Column('post_id', db.Integer, db.ForeignKey('post.id'))
)

class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True)
    posts = db.relationship('Post', secondary=tags_posts, backref=db.backref('tags', lazy='dynamic'))

Creating the Database Tables

After defining your models, you need to create the actual tables in your database. You can do this with the following code:

python

# In the Python interactive shell or a script
from your_app import db
db.create_all()

This command creates all tables that don't already exist in the database.

CRUD Operations with Models

Now let's look at how to perform basic CRUD (Create, Read, Update, Delete) operations with our models:

Create

python

# Create a new user
new_user = User(username='john_doe', email='[email protected]', password='password123')
db.session.add(new_user)
db.session.commit()

# Create a post for this user
new_post = Post(title='My First Post', content='This is my first post content!', author=new_user)
db.session.add(new_post)
db.session.commit()

Read

python

# Get all users
all_users = User.query.all()

# Get user by ID
user = User.query.get(1)

# Get user by username
user = User.query.filter_by(username='john_doe').first()

# Get all posts by a user
user_posts = Post.query.filter_by(author=user).all()
# Or use the relationship
user_posts = user.posts

Update

python

user = User.query.get(1)
user.username = 'john_smith'
db.session.commit()

Delete

python

user = User.query.get(1)
db.session.delete(user)
db.session.commit()

Advanced Querying

SQLAlchemy provides powerful querying capabilities:

python

# Filter with conditions
posts = Post.query.filter(Post.title.contains('Flask')).all()

# Order results
recent_posts = Post.query.order_by(Post.date_posted.desc()).all()

# Limit results
top_five = Post.query.limit(5).all()

# Pagination
page = Post.query.paginate(page=1, per_page=10)
posts = page.items

Real-world Example: Blog Application

Let's put everything together in a more complete example for a blog application:

python

from flask import Flask, render_template, redirect, url_for, flash, request
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
from flask_wtf import FlaskForm
from wtforms import StringField, TextAreaField, SubmitField
from wtforms.validators import DataRequired

app = Flask(__name__)
app.config['SECRET_KEY'] = 'your-secret-key'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///blog.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    posts = db.relationship('Post', backref='author', lazy=True)

    def __repr__(self):
        return f"User('{self.username}', '{self.email}')"

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    content = db.Column(db.Text, nullable=False)
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

    def __repr__(self):
        return f"Post('{self.title}', '{self.date_posted}')"

class PostForm(FlaskForm):
    title = StringField('Title', validators=[DataRequired()])
    content = TextAreaField('Content', validators=[DataRequired()])
    submit = SubmitField('Post')

@app.route('/')
def home():
    posts = Post.query.order_by(Post.date_posted.desc()).all()
    return render_template('home.html', posts=posts)

@app.route('/post/new', methods=['GET', 'POST'])
def new_post():
    form = PostForm()
    if form.validate_on_submit():
        # For simplicity, we're using a hardcoded user
        user = User.query.first()
        post = Post(title=form.title.data, content=form.content.data, author=user)
        db.session.add(post)
        db.session.commit()
        flash('Your post has been created!', 'success')
        return redirect(url_for('home'))
    return render_template('create_post.html', form=form)

if __name__ == '__main__':
    app.run(debug=True)

In this example:

1. We've defined our User and Post models with a one-to-many relationship
2. We've created a form for submitting new posts
3. We have routes for viewing all posts and creating new ones
4. We're using the SQLAlchemy ORM to interact with our database

Model Customization and Hooks

SQLAlchemy models can be customized with various hooks that run at specific times:

python

class User(db.Model):
    # ... columns
    
    # This runs before the model is saved
    @staticmethod
    def before_save(mapper, connection, target):
        target.username = target.username.lower()
    
    # This runs after the model is loaded from the database
    @staticmethod
    def after_load(target, context):
        target.full_name = f"{target.first_name} {target.last_name}"

# Register the event listeners
from sqlalchemy import event
event.listen(User, 'before_insert', User.before_save)
event.listen(User, 'before_update', User.before_save)
event.listen(User, 'load', User.after_load)

Best Practices for Flask Models

1. Separate Models into Their Own Files: As your application grows, consider moving models to a separate file or package.

/myapp
  /models
    __init__.py
    user.py
    post.py
  app.py

2. Use Migrations for Schema Changes: Instead of recreating your database when models change, use Flask-Migrate to update the schema:

bash

pip install Flask-Migrate

python

from flask_migrate import Migrate

migrate = Migrate(app, db)

3. Validate Data at the Model Level: Add validation logic to ensure data integrity.

4. Consider Using Mixins: For common patterns that appear in multiple models, create mixins:

python

class TimestampMixin:
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class User(db.Model, TimestampMixin):
    # This model now has created_at and updated_at columns
    pass

Summary

Flask database models provide a powerful way to interact with your database using Python code instead of raw SQL:

• Models are Python classes that inherit from db.Model
• Each model represents a table in your database
• Columns are defined using db.Column() with various types and constraints
• Relationships between models can be defined using db.relationship()
• SQLAlchemy provides a comprehensive API for CRUD operations and querying
• Models can be customized with hooks and validation

By using database models, you write cleaner, more maintainable code that's less prone to SQL injection and other database-related issues.

Further Exercises

1. Create a complete blog application with User, Post, and Comment models
2. Implement a many-to-many relationship between Posts and Tags
3. Add user authentication with password hashing
4. Implement pagination for posts
5. Create a search function that queries across multiple models

Additional Resources

• Flask-SQLAlchemy Documentation
• SQLAlchemy Documentation
• Flask-Migrate for Database Migrations
• Flask Mega-Tutorial: Databases

Happy coding with Flask and SQLAlchemy!