Flask-SQLAlchemy Setup

Difficulty: Intermediate

Flask-SQLAlchemy is the most popular database extension for Flask. It integrates SQLAlchemy, Python's premier ORM (Object-Relational Mapper), with Flask's application context and configuration system. An ORM lets you interact with databases using Python objects instead of writing raw SQL queries.

SQLAlchemy maps Python classes to database tables and Python objects to table rows. Instead of writing 'INSERT INTO users (name, email) VALUES (?, ?)' you write 'user = User(name="Alice", email="alice@example.com")' and 'db.session.add(user)'. This abstraction makes your code database-agnostic, more readable, and less prone to SQL injection.

Setting up Flask-SQLAlchemy involves installing the package, configuring the database URI, creating the SQLAlchemy instance, and initializing it with your app. The SQLALCHEMY_DATABASE_URI config value tells SQLAlchemy which database to connect to. SQLite uses 'sqlite:///filename.db' (three slashes for relative path, four for absolute). PostgreSQL uses 'postgresql://user:password@host:port/dbname'.

The db object (typically a SQLAlchemy instance) is the gateway to all database operations. db.Model is the base class for all your models. db.session provides the transactional interface for queries and writes. db.create_all() creates all tables defined by your models.

With the application factory pattern, you create db without an app and call db.init_app(app) in the factory. Models are defined using db.Model as the base class, and columns use db.Column with type classes like db.String, db.Integer, db.Boolean, db.DateTime, and db.Text.

Flask-SQLAlchemy adds several features on top of plain SQLAlchemy. It provides a preconfigured scoped session tied to Flask's request lifecycle (the session is automatically removed at the end of each request). It adds a default __tablename__ based on the class name. It includes pagination support via query.paginate(). And it integrates with Flask's app context so you can use db.session in view functions.

The SQLALCHEMY_TRACK_MODIFICATIONS config should be set to False in production. When True, it adds overhead by tracking all changes to objects and emitting signals. The default changed to False in recent versions, but it is good practice to set it explicitly.

Code examples

Basic Setup with Factory Pattern

# app/extensions.py
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

# app/__init__.py
from flask import Flask
from app.extensions import db

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

    db.init_app(app)

    with app.app_context():
        from app import models  # Import models
        db.create_all()         # Create tables

    return app

The db object is created without an app, then initialized in the factory with init_app(). db.create_all() inside an app context creates all tables defined by models. Import models before calling create_all().

Database URI Examples

# SQLite (file-based, good for development)
SQLALCHEMY_DATABASE_URI = 'sqlite:///app.db'        # Relative path
SQLALCHEMY_DATABASE_URI = 'sqlite:////tmp/app.db'   # Absolute path
SQLALCHEMY_DATABASE_URI = 'sqlite:///:memory:'       # In-memory (testing)

# PostgreSQL (production)
SQLALCHEMY_DATABASE_URI = 'postgresql://user:password@localhost:5432/mydb'

# MySQL
SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://user:password@localhost/mydb'

# From environment variable (recommended)
import os
SQLALCHEMY_DATABASE_URI = os.environ.get(
    'DATABASE_URL',
    'sqlite:///dev.db'  # Fallback for development
)

The database URI format is dialect+driver://username:password@host:port/database. SQLite is great for development and testing. Use PostgreSQL or MySQL for production. Always use environment variables for credentials.

Defining a Simple Model

from app.extensions import db
from datetime import datetime

class User(db.Model):
    __tablename__ = 'users'  # Optional: defaults to 'user'

    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)
    is_active = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

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

Models inherit from db.Model. Each class attribute with db.Column becomes a database column. Common types: Integer, String(max_length), Text, Boolean, DateTime, Float. Set constraints like primary_key, unique, nullable, and default.

Using the Flask Shell

# In terminal: flask shell
# This opens a Python shell with app context

>>> from app.extensions import db
>>> from app.models import User

# Create tables (if not using migrations)
>>> db.create_all()

# Create a user
>>> user = User(username='alice', email='alice@example.com', password_hash='...')
>>> db.session.add(user)
>>> db.session.commit()

# Query users
>>> User.query.all()
[<User alice>]
>>> User.query.filter_by(username='alice').first()
<User alice>

# Drop all tables (careful!)
>>> db.drop_all()

The flask shell command opens a Python REPL with the app context active. This is useful for testing queries, creating initial data, and debugging. db.create_all() creates tables, db.drop_all() removes them.

Key points

Flask-SQLAlchemy wraps SQLAlchemy with Flask-specific features: session management, pagination, and context integration.
Use the init_app pattern with the factory: create db = SQLAlchemy() then db.init_app(app) in create_app().
Set SQLALCHEMY_TRACK_MODIFICATIONS = False to avoid unnecessary overhead.
Use SQLite for development and PostgreSQL for production.

Concepts covered

Flask-SQLAlchemy, ORM, Database URI, db.create_all, SQLite, PostgreSQL