Database Integration in Flask

Introduction to Databases in Flask

Most web applications need to store and retrieve data, making database integration a crucial aspect of Flask development. Flask doesn't come with built-in database support, but it's designed to be easily integrated with various database solutions.

In this tutorial, we'll focus on using SQLAlchemy with Flask, which is the most popular approach for database integration in Flask applications.

Database Options for Flask

Flask can work with various database systems, each with its own strengths:

For most Flask applications, especially when starting out, a relational database like SQLite (for development) or PostgreSQL (for production) is a good choice.

Flask-SQLAlchemy

Flask-SQLAlchemy is an extension that adds support for SQLAlchemy to your Flask application. SQLAlchemy is a powerful ORM (Object Relational Mapper) that allows you to interact with your database using Python objects instead of writing raw SQL.

Installation

pip install Flask-SQLAlchemy

Basic Configuration

# In your config.py
import os
basedir = os.path.abspath(os.path.dirname(__file__))

class Config:
    # SQLite database for development
    SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(basedir, 'app.db')
    SQLALCHEMY_TRACK_MODIFICATIONS = False

# In your app/__init__.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from config import Config

db = SQLAlchemy()

def create_app(config_class=Config):
    app = Flask(__name__)
    app.config.from_object(config_class)
    
    db.init_app(app)
    
    # Create tables if they don't exist
    with app.app_context():
        db.create_all()
    
    return app

The SQLALCHEMY_DATABASE_URI configuration variable tells Flask-SQLAlchemy which database to connect to. For different database systems, the URI format will be different.

Defining Models

Models are Python classes that represent tables in your database. Each attribute of the class represents a column in the table.

# In app/models.py
from app import db
from datetime import datetime

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), index=True, unique=True)
    email = db.Column(db.String(120), index=True, unique=True)
    password_hash = db.Column(db.String(128))
    posts = db.relationship('Post', backref='author', lazy='dynamic')
    
    def __repr__(self):
        return f'<User {self.username}>'

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100))
    body = db.Column(db.Text)
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    
    def __repr__(self):
        return f'<Post {self.title}>'

In this example, we've defined two models: User and Post. The User model has a one-to-many relationship with the Post model, which is defined using db.relationship and db.ForeignKey.

Common Column Types

Database Operations

Once you've defined your models, you can perform various operations on your database:

Creating Records

from app import db
from app.models import User, Post

# Create a new user
user = User(username='john', email='[email protected]')
user.set_password('password')  # Assuming you have a set_password method

# Add to the session and commit
db.session.add(user)
db.session.commit()

# Create a post for this user
post = Post(title='My First Post', body='This is the post content', author=user)
db.session.add(post)
db.session.commit()

Querying Records

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

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

# Filter users
users = User.query.filter_by(username='john').all()

# More complex filters
users = User.query.filter(User.email.endswith('@example.com')).all()

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

# Limit results
recent_posts = Post.query.order_by(Post.timestamp.desc()).limit(5).all()

# Pagination
posts = Post.query.paginate(page=1, per_page=10)
for post in posts.items:
    print(post.title)

Updating Records

# Get the user
user = User.query.get(1)

# Update attributes
user.username = 'new_username'

# Commit changes
db.session.commit()

Deleting Records

# Get the user
user = User.query.get(1)

# Delete the user
db.session.delete(user)
db.session.commit()

Relationships

SQLAlchemy makes it easy to define relationships between models:

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

class Post(db.Model):
    # ...
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

# Association table
tags = db.Table('tags',
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')),
    db.Column('post_id', db.Integer, db.ForeignKey('post.id'))
)

class Post(db.Model):
    # ...
    tags = db.relationship('Tag', secondary=tags,
                          backref=db.backref('posts', lazy='dynamic'))

class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True)

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

class Profile(db.Model):
    # ...
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

class Employee(db.Model):
    # ...
    manager_id = db.Column(db.Integer, db.ForeignKey('employee.id'))
    reports = db.relationship('Employee', backref=db.backref('manager', remote_side=[id]))

Database Migrations with Flask-Migrate

As your application evolves, you'll need to make changes to your database schema. Flask-Migrate (based on Alembic) helps you manage these changes:

# Install Flask-Migrate
pip install Flask-Migrate

# In your app/__init__.py
from flask_migrate import Migrate

db = SQLAlchemy()
migrate = Migrate()

def create_app(config_class=Config):
    app = Flask(__name__)
    app.config.from_object(config_class)
    
    db.init_app(app)
    migrate.init_app(app, db)
    
    return app

After setting up Flask-Migrate, you can use the following commands to manage migrations:

# Initialize migrations (first time only)
flask db init

# Create a migration
flask db migrate -m "Initial migration"

# Apply migrations
flask db upgrade

# Rollback a migration
flask db downgrade

Migrations are stored in a directory called migrations in your project, and they track changes to your database schema over time.

Best Practices

• Use environment variables for database URIs: Don't hardcode database credentials in your code
• Use migrations: Always use Flask-Migrate to manage database schema changes
• Keep models organized: For larger applications, consider splitting models into separate files
• Use transactions: Wrap related database operations in transactions to ensure atomicity
• Validate data: Validate data before inserting it into the database
• Handle errors: Properly handle database errors to prevent application crashes
• Use indexes: Add indexes to columns that are frequently used in queries
• Test with real databases: Test your application with the same database system you'll use in production