🗄️ Database Integration with Flask-SQLAlchemy

Why Databases & What's an ORM?

Databases exist because applications need to remember things reliably. A database gives you:

• Persistence — data survives restarts and crashes
• Structure & integrity — rules and constraints keep data valid
• Efficient retrieval — indexed queries find records fast
• Concurrency — many users read and write safely at once

💡 The library analogy: A database is a library. Data are the books, tables are the shelves, and the schema is the catalog system. The database engine is the librarian who finds a book instantly, enforces the rules, and makes sure two people don't check out the same copy.

What is an ORM?

An Object-Relational Mapper translates between two worlds: Python objects and relational database rows. Instead of writing raw SQL, you work with classes and instances, and the ORM generates the SQL for you.

SQLAlchemy is the leading Python ORM. Flask-SQLAlchemy wraps it with Flask-friendly defaults: automatic session handling tied to the request lifecycle, a base db.Model class, and helpers like db.paginate() and db.get_or_404().

Setting Up Flask-SQLAlchemy

Install the extension. For anything other than SQLite you also need a driver:

pip install Flask-SQLAlchemy

# Database drivers (pick what you use)
pip install psycopg2-binary   # PostgreSQL
pip install mysqlclient        # MySQL / MariaDB
# SQLite needs no driver — it's built into Python

Configure the database URI and create the db object. This example uses the modern application-factory-friendly style:

import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# Use an env var in production; fall back to a local SQLite file in dev
app.config['SQLALCHEMY_DATABASE_URI'] = os.environ.get(
    'DATABASE_URL', 'sqlite:///site.db'
)

db = SQLAlchemy(app)

Database URIs at a glance

Defining Models

A model is a Python class that maps to a database table. Each db.Column attribute becomes a column. Subclass db.Model:

from datetime import datetime, timezone

class User(db.Model):
    __tablename__ = 'users'   # optional; defaults to a lowercased class name

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False, index=True)
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))

    def __repr__(self):
        return f'<User {self.username}>'

This defines a users table with four columns. The __repr__ makes objects readable when debugging in the shell.

Common column types & options

Column options fine-tune each column: primary_key=True, unique=True, nullable=False, index=True, default=value, and onupdate=func (run on every update).

Relationships Between Models

The real power of a relational database is connecting tables. There are four relationship shapes:

One-to-many (the most common)

One user writes many posts. The "many" side holds a foreign key; the "one" side declares a relationship:

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)

    # Access every post by this user via user.posts
    posts = db.relationship('Post', back_populates='author', lazy=True)


class Post(db.Model):
    __tablename__ = 'posts'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(120), nullable=False)
    content = db.Column(db.Text, nullable=False)

    # The foreign key points at users.id
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    # Access the writer via post.author
    author = db.relationship('User', back_populates='posts')

Now some_user.posts is a list of that user's posts, and some_post.author is the User who wrote it. Using matched back_populates on both sides makes the two-way link explicit and readable.

Many-to-many

A post can have many tags, and a tag applies to many posts. This needs an association table:

post_tags = db.Table(
    'post_tags',
    db.Column('post_id', db.Integer, db.ForeignKey('posts.id'), primary_key=True),
    db.Column('tag_id', db.Integer, db.ForeignKey('tags.id'), primary_key=True),
)

class Post(db.Model):
    __tablename__ = 'posts'
    id = db.Column(db.Integer, primary_key=True)
    tags = db.relationship('Tag', secondary=post_tags, back_populates='posts')

class Tag(db.Model):
    __tablename__ = 'tags'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True, nullable=False)
    posts = db.relationship('Post', secondary=post_tags, back_populates='tags')

One-to-one

Add uselist=False so the relationship returns a single object instead of a list, and mark the foreign key unique=True:

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

class Profile(db.Model):
    __tablename__ = 'profiles'
    id = db.Column(db.Integer, primary_key=True)
    bio = db.Column(db.Text)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'),
                        unique=True, nullable=False)
    user = db.relationship('User', back_populates='profile')

Creating the Database

Once models are defined, create the tables. Flask-SQLAlchemy needs an application context to know which app (and database) to talk to:

with app.app_context():
    db.create_all()   # creates any tables that don't yet exist

CRUD Operations

CRUD — Create, Read, Update, Delete — is the everyday work of a database app. Flask-SQLAlchemy routes everything through db.session, a staging area for changes that you commit() to make permanent.

Create

user = User(username='ray', email='ray@example.com')
db.session.add(user)
db.session.commit()   # nothing is saved until you commit

Read

Modern SQLAlchemy 2.x style uses db.session.get() for primary-key lookups and db.select() for queries:

# Fetch by primary key
user = db.session.get(User, 1)

# Fetch by attribute
user = db.session.scalar(
    db.select(User).filter_by(username='ray')
)

# All matching rows
recent = db.session.scalars(
    db.select(User).order_by(User.created_at.desc()).limit(5)
).all()

Update

user = db.session.get(User, 1)
user.email = 'new@example.com'   # just change the attribute
db.session.commit()              # commit saves it

Delete

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

add / change / delete  →  db.session.commit()  →  persisted

Querying & Filtering

SQLAlchemy's query API expresses complex SQL in Python. A few patterns cover most needs:

from sqlalchemy import or_, func

# WHERE with multiple conditions
users = db.session.scalars(
    db.select(User).where(
        User.active == True,
        User.email.endswith('@example.com'),
    )
).all()

# OR conditions
admins = db.session.scalars(
    db.select(User).where(
        or_(User.role == 'admin', User.role == 'moderator')
    )
).all()

# Aggregation — count active users
active_count = db.session.scalar(
    db.select(func.count()).select_from(User).where(User.active == True)
)

# Join posts to their authors
rows = db.session.execute(
    db.select(Post, User).join(User, Post.user_id == User.id)
).all()

Pagination

For list pages, db.paginate() returns a page object with .items, .pages, .has_next, and more:

@app.route('/users')
def list_users():
    page = request.args.get('page', 1, type=int)
    pagination = db.paginate(
        db.select(User).order_by(User.username),
        page=page, per_page=20, error_out=False,
    )
    return render_template('users.html', pagination=pagination)

Fetch-or-404

In views, db.get_or_404() and db.first_or_404() raise a clean 404 when a record is missing — no manual if user is None checks:

@app.route('/users/<int:user_id>')
def show_user(user_id):
    user = db.get_or_404(User, user_id)
    return render_template('user.html', user=user)

Migrations with Flask-Migrate

Once your app is live with real data, you can't just drop_all() to change the schema. Flask-Migrate (built on Alembic) generates versioned migration scripts that alter tables in place — add a column, rename it, create an index — without losing data.

from flask_migrate import Migrate

migrate = Migrate(app, db)

Then drive it from the command line:

flask db init                          # once, sets up the migrations folder
flask db migrate -m "add active flag"  # auto-generate a migration from model changes
flask db upgrade                        # apply it to the database
flask db downgrade                      # roll back the last migration if needed

Hands-on Exercise

from datetime import datetime, timezone

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

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(120), nullable=False)
    body = db.Column(db.Text, nullable=False)
    created_at = db.Column(db.DateTime,
                           default=lambda: datetime.now(timezone.utc))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    author = db.relationship('User', back_populates='posts')

with app.app_context():
    db.create_all()

    ray = User(username='ray')
    ray.posts = [
        Post(title='Hello', body='First post'),
        Post(title='Again', body='Second post'),
    ]
    db.session.add(ray)
    db.session.commit()

    # Query: this user's posts, newest first
    newest = db.session.scalars(
        db.select(Post)
          .where(Post.user_id == ray.id)
          .order_by(Post.created_at.desc())
    ).all()
    for p in newest:
        print(p.title, '—', p.author.username)

Summary & Quiz

📚 Further Reading

• Flask-SQLAlchemy documentation
• SQLAlchemy 2.0 ORM quickstart
• Flask-Migrate documentation

🚀 What's Next?

You can now collect data with forms and store it in a database. As the app grows, though, a single file gets unwieldy. Next we organize everything into modular Blueprints and the application factory pattern.