M Saad Ahmad

Posted on Apr 25

Day 82 of 100 Days Of Code — Flask Models, Database, and ORM

#webdev #programming #flask #100daysofcode

Yesterday I covered forms with Flask-WTF. Today, for day 82, it was about adding a real database. In Django, this was built in. In Flask, you bring it in yourself with Flask-SQLAlchemy for the ORM and Flask-Migrate for migrations. The concepts are identical to Django; the setup is just more manual.

What is Flask-SQLAlchemy?

SQLAlchemy is Python's most popular ORM. It works with PostgreSQL, MySQL, SQLite, and more. Flask-SQLAlchemy is a Flask extension that integrates SQLAlchemy cleanly into Flask apps, handling the database connection and session management for you.

pip install flask-sqlalchemy flask-migrate

Two packages:

flask-sqlalchemy — the ORM itself
flask-migrate — handles database migrations, built on top of Alembic

Setup

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

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

db = SQLAlchemy(app)
migrate = Migrate(app, db)

SQLALCHEMY_DATABASE_URI is the connection string. For SQLite, it's a file path. For PostgreSQL, it looks like postgresql://user:password@localhost/dbname.

db is the SQLAlchemy instance. You'll use this everywhere to define models and run queries. migrate wires Flask-Migrate to your app and database.

Defining Models

from datetime import datetime

class User(db.Model):
    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)
    password_hash = db.Column(db.String(256), nullable=False)
    bio = db.Column(db.Text, nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

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

A model is a class that extends db.Model. Each column is a class attribute using db.Column. This is exactly like Django models: same concept, slightly different syntax.

__repr__ is Flask-SQLAlchemy's equivalent of Django's __str__. It controls how the object appears in the shell and debugger.

Common Column Types

Flask-SQLAlchemy	Django Equivalent
`db.Integer`	`IntegerField`
`db.String(n)`	`CharField(max_length=n)`
`db.Text`	`TextField`
`db.Boolean`	`BooleanField`
`db.Float`	`FloatField`
`db.DateTime`	`DateTimeField`
`db.Date`	`DateField`
`db.JSON`	`JSONField`

Column Options

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    content = db.Column(db.Text, nullable=False)
    is_published = db.Column(db.Boolean, default=False, nullable=False)
    slug = db.Column(db.String(200), unique=True, nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

primary_key=True — this column is the primary key
nullable=False — equivalent to Django's blank=False at the database level
unique=True — enforces unique values
default= — default value when creating a record
onupdate= — automatically updates the value when the record is updated. Django's equivalent is auto_now=True on DateTimeField

Relationships

One-to-Many

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

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

Two parts to a relationship:

db.ForeignKey('user.id') on the child model — the actual foreign key column
db.relationship('Post', backref='author') on the parent model — creates a Python-level accessor

backref='author' automatically adds a .author attribute to Post objects pointing back to the User. In Django, this was related_name.

lazy=True means posts are loaded from the database only when you access user.posts, not automatically when you fetch the user. Same as Django's lazy QuerySets.

Many-to-Many

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

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    tags = db.relationship('Tag', secondary=post_tags, backref='posts')

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

Many-to-many requires an association table defined with db.Table. In Django, this was handled automatically by ManyToManyField. Flask-SQLAlchemy requires you to define the junction table explicitly.

One-to-One

class UserProfile(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), unique=True)
    avatar = db.Column(db.String(200), nullable=True)
    website = db.Column(db.String(200), nullable=True)
    user = db.relationship('User', backref=db.backref('profile', uselist=False))

uselist=False on the backref turns it into a one-to-one relationship. user.profile returns a single object instead of a list. In Django, this was OneToOneField.

Migrations with Flask-Migrate

This is the equivalent of Django's makemigrations and migrate.

Initialize migrations. Only once per project:

flask db init

This creates a migrations/ folder in your project.

Generate a migration after changing models:

flask db migrate -m "initial migration"

Apply migrations to the database:

flask db upgrade

Roll back the last migration:

flask db downgrade

The workflow is identical to Django: change the model, generate the migration, and apply it. The commands are just different.

CRUD Operations

Create

# method 1
user = User(username='haris', email='haris@example.com', password_hash='hashed')
db.session.add(user)
db.session.commit()

# method 2 — add multiple at once
db.session.add_all([user1, user2, user3])
db.session.commit()

Every change to the database goes through db.session. You add objects, then commit. In Django, this was object.save(); here, you explicitly manage the session.

Read

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

# get by primary key — returns None if not found
user = User.query.get(1)

# get by primary key — raises 404 if not found
from flask import abort
user = User.query.get_or_404(1)

# filter
users = User.query.filter_by(username='haris').all()
user = User.query.filter_by(username='haris').first()

# filter with operators
from sqlalchemy import or_
posts = Post.query.filter(
    or_(Post.title.contains('flask'), Post.content.contains('flask'))
).all()

# ordering
posts = Post.query.order_by(Post.created_at.desc()).all()

# limiting
posts = Post.query.limit(10).all()

# chaining
posts = Post.query.filter_by(is_published=True)\
                  .order_by(Post.created_at.desc())\
                  .limit(5)\
                  .all()

get_or_404 is Flask-SQLAlchemy's equivalent of Django's get_object_or_404. It returns the object or aborts with a 404 response automatically.

Update

user = User.query.get_or_404(1)
user.username = 'new_username'
user.bio = 'Updated bio'
db.session.commit()

Fetch the object, change the attributes, commit. No .save() call needed. SQLAlchemy tracks changes to objects in the session.

Delete

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

Bulk Operations

# bulk update
Post.query.filter_by(is_published=False).update({'is_published': True})
db.session.commit()

# bulk delete
Post.query.filter_by(is_published=False).delete()
db.session.commit()

Pagination

@app.route('/posts')
def posts():
    page = request.args.get('page', 1, type=int)
    posts = Post.query.order_by(Post.created_at.desc()).paginate(
        page=page,
        per_page=10,
        error_out=False
    )
    return render_template('posts.html', posts=posts)

paginate() returns a Pagination object with:

posts.items — the records for this page
posts.has_next / posts.has_prev — navigation flags
posts.next_num / posts.prev_num — page numbers
posts.total — total record count
posts.pages — total page count

In the template:

{% for post in posts.items %}
    <p>{{ post.title }}</p>
{% endfor %}

{% if posts.has_prev %}
    <a href="{{ url_for('posts', page=posts.prev_num) }}">Previous</a>
{% endif %}
{% if posts.has_next %}
    <a href="{{ url_for('posts', page=posts.next_num) }}">Next</a>
{% endif %}

Django had a built-in Paginator class: Flask-SQLAlchemy's .paginate() is simpler and more direct.

Password Hashing

Flask has no built-in password hashing like Django does. Use werkzeug, which comes with Flask:

from werkzeug.security import generate_password_hash, check_password_hash

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), nullable=False)
    password_hash = db.Column(db.String(256), nullable=False)

    def set_password(self, password):
        self.password_hash = generate_password_hash(password)

    def check_password(self, password):
        return check_password_hash(self.password_hash, password)

# creating a user
user = User(username='haris')
user.set_password('securepassword')
db.session.add(user)
db.session.commit()

# verifying password
user = User.query.filter_by(username='haris').first()
if user and user.check_password('securepassword'):
    print('Login successful')

In Django, create_user() handled all of this automatically. In Flask, you wire it yourself. But the set_password and check_password methods on the model keep it clean.

Environment Variables for the Database URI

Don't hardcode your database URL:

pip install python-dotenv

Create .env:

SECRET_KEY=your-secret-key-here
DATABASE_URL=sqlite:///app.db

Update app.py:

import os
from dotenv import load_dotenv

load_dotenv()

app.config['SECRET_KEY'] = os.environ.get('SECRET_KEY')
app.config['SQLALCHEMY_DATABASE_URI'] = os.environ.get('DATABASE_URL')

Same principle as Django's python-decouple, sensitive values out of the codebase and into .env.

Django vs Flask-SQLAlchemy Side by Side

Django ORM	Flask-SQLAlchemy
`models.Model`	`db.Model`
`models.CharField`	`db.Column(db.String(n))`
`ForeignKey`	`db.Column(db.ForeignKey())` + `db.relationship()`
`ManyToManyField`	`db.Table` + `db.relationship(secondary=...)`
`OneToOneField`	`db.relationship(uselist=False)`
`makemigrations`	`flask db migrate`
`migrate`	`flask db upgrade`
`Model.objects.all()`	`Model.query.all()`
`Model.objects.get(pk=1)`	`Model.query.get(1)`
`get_object_or_404`	`Model.query.get_or_404(1)`
`Model.objects.filter()`	`Model.query.filter_by()`
`object.save()`	`db.session.add(obj)` + `db.session.commit()`
`object.delete()`	`db.session.delete(obj)` + `db.session.commit()`
Built-in password hashing	`werkzeug.security`
Built-in pagination	`.paginate()` on query

Wrapping Up

Flask-SQLAlchemy is more verbose than Django's ORM, but the concepts map directly. Models are classes, columns are attributes, relationships use foreign keys, and migrations track changes. The main differences are the session-based write pattern and the manual setup that Django handles automatically.

Thanks for reading. Feel free to share your thoughts!

DEV Community