Database with SQLAlchemy — Flask

Install Flask-SQLAlchemy and Flask-Migrate

bash

pip install flask-sqlalchemy flask-migrate

Database setup with Flask-SQLAlchemy

python

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

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///app.db"
# PostgreSQL: "postgresql://user:pass@localhost/dbname"

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

# Define models as Python classes
class Course(db.Model):
    id          = db.Column(db.Integer, primary_key=True)
    title       = db.Column(db.String(200), nullable=False)
    slug        = db.Column(db.String(200), unique=True, nullable=False)
    description = db.Column(db.Text)
    level       = db.Column(db.String(20), default="beginner")
    is_live     = db.Column(db.Boolean, default=False)
    created_at  = db.Column(db.DateTime, server_default=db.func.now())

    def to_dict(self):
        return { "id": self.id, "title": self.title, "slug": self.slug }

    def __repr__(self):
        return f"<Course {self.title}>"

Migrations with Flask-Migrate

bash

# Initialise migrations (run once)
flask db init

# Create migration after changing models
flask db migrate -m "Add course model"

# Apply migrations
flask db upgrade

# Roll back
flask db downgrade

CRUD operations with SQLAlchemy

python

# Create
course = Course(title="Flask", slug="flask", level="intermediate")
db.session.add(course)
db.session.commit()

# Read
all_courses   = Course.query.all()
live_courses  = Course.query.filter_by(is_live=True).all()
one_course    = Course.query.get(42)
by_slug       = Course.query.filter_by(slug="flask").first()

# Update
course.is_live = True
db.session.commit()

# Delete
db.session.delete(course)
db.session.commit()