Python Flask SQLAlchemy Integration

Integrating SQLAlchemy into a Flask application requires more than installing two packages. You need to configure the engine, bind it to the Flask app, declare models, and manage sessions so that each request uses a single connection and rolls back on errors. This article walks through a practical python flask sqlalchemy integration: setting up the engine, defining models, handling sessions, querying, and running migrations.

Setting Up the Database Engine and Session

The first step is to create a SQLAlchemy engine and a session factory. The engine holds the database connection pool, and the session is your workspace for querying and persisting objects. A common pattern is to define these in a separate module so models and views can import them without circular dependencies.

# db.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base

engine = create_engine("postgresql://user:pass@localhost/mydb")
SessionLocal = sessionmaker(bind=engine)
Base = declarative_base()

The create_engine call takes a database URL. For PostgreSQL, the format is postgresql://user:password@host/dbname. For SQLite, use sqlite:///path/to/file.db. The sessionmaker returns a session class that you can instantiate later. The declarative_base returns a base class for your models.

Declaring Models with SQLAlchemy

Models are Python classes that map to database tables. Each attribute maps to a column, and the table name is derived from the class name unless you specify it explicitly. Here is a minimal User model:

# models.py
from sqlalchemy import Column, Integer, String
from db import Base

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    email = Column(String(120), unique=True, nullable=False)

The Column types and constraints are declared directly. After defining models, you create the tables with Base.metadata.create_all(engine). In a production environment you would use migrations instead, but this is useful for quick prototypes.

Managing the Session Lifecycle in Flask

Flask requests should get a fresh session and close it after the request finishes. If you reuse a session across requests, you risk sharing transactions and stale objects. The standard approach is to use a Flask extension or a simple teardown_appcontext hook.

Using the flask-sqlalchemy extension simplifies this, but if you want to keep SQLAlchemy plain, you can manage sessions manually:

from flask import Flask, g
from db import SessionLocal

app = Flask(__name__)

@app.before_request
def create_session():
    g.db = SessionLocal()

@app.teardown_request
def close_session(exception=None):
    db = g.pop("db", None)
    if db is not None:
        db.close()

Now every view can access g.db to query and commit. This pattern ensures the session is closed even if an exception occurs.

Querying with the ORM

SQLAlchemy's query API lets you retrieve objects without writing raw SQL. For example, to get all users with a specific email:

user = g.db.query(User).filter(User.email == "alice@example.com").first()

The query method accepts the model class. filter adds a WHERE clause, and first returns the first result or None. To add a new user, you instantiate the model, add it to the session, and commit:

new_user = User(name="Alice", email="alice@example.com")
g.db.add(new_user)
g.db.commit()

If a commit fails, you should roll back to keep the session usable. The ORM also supports all(), count(), order_by(), and many other methods.

Handling Relationships and Lazy Loading

Relationships between models are defined with relationship() and foreign keys. For instance, a Post model that belongs to a User:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Post(Base):
    __tablename__ = "posts"
    id = Column(Integer, primary_key=True)
    title = Column(String(100))
    user_id = Column(Integer, ForeignKey("users.id"))
    author = relationship("User", back_populates="posts")

User.posts = relationship("Post", order_by=Post.id, back_populates="author")

By default, relationships are lazy-loaded, meaning the related objects are fetched only when you access them. This can lead to extra queries in loops. Use joinedload() or selectinload() in queries to eagerly load relationships when you know you'll need them.

Running Migrations with Alembic

create_all is fine for a fresh database, but it does not alter existing tables when you change a model. Alembic is the migration tool used by SQLAlchemy. It generates migration scripts that track schema changes.

After installing Alembic, run alembic init alembic to create the migration environment. Configure the script location and the database URL in alembic.ini and env.py. Then generate a migration with:

alembic revision --autogenerate -m "add users table"

This compares the model metadata with the current database and writes a script. Apply it with alembic upgrade head. Always review generated scripts before applying them to a production database.

Common Integration Pitfalls and How to Avoid Them

One frequent mistake is creating a new engine or session for every request. This defeats connection pooling and adds overhead. Reuse the engine and use the session factory to create short-lived sessions.

Another issue is mixing session usage across threads. SQLAlchemy sessions are not thread-safe by default. In a Flask app, each request runs in its own context, so creating a session per request avoids this problem.

A third pitfall is forgetting to call commit() or rollback() after a failed operation. If a commit raises an exception, the session remains in a partially modified state. Always wrap commits in a try/except block and call rollback() on failure.

Finally, be careful with Base.metadata.create_all() in production. It does not update existing tables. Use Alembic for any schema evolution after the initial deployment.