Flask SQLAlchemy Models

You've built a Flask app that serves pages, handles forms, and maybe even talks to a database with raw SQL. But as your app grows, you start to feel the pain: SQL strings scattered across your views, manual mapping between rows and Python objects, and little bugs that only appear when you mistype a column name. This lesson solves that problem by introducing Flask-SQLAlchemy, the ORM that lets you define your database schema as Python classes—models—and interact with your data through clean, object-oriented code. By the end, you'll be able to use Flask SQLAlchemy for models with confidence, and you'll see why this is a game-changer for any Python web developer.

The problem this lesson solves

Raw SQL in a Flask app is like building a house with a hammer and no nails—it works, but it's slow, error-prone, and hard to maintain. Consider a typical view that fetches a user by email:

# app.py (no ORM — painful approach)
import sqlite3
from flask import Flask, g

app = Flask(__name__)

DATABASE = 'app.db'

def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE)
    return db

@app.route('/user/<email>')
def user_by_email(email):
    cur = get_db().execute(
        "SELECT id, name, email FROM users WHERE email = ?", (email,))
    row = cur.fetchone()
    if row is None:
        return "User not found", 404
    user = {"id": row[0], "name": row[1], "email": row[2]}
    return f"User: {user['name']} ({user['email']})"

This code is brittle: you must remember column names, handle tuple indexing manually, and you'll repeat these patterns for every table. Worse, it's not Pythonic—you're thinking in rows, not objects. Flask-SQLAlchemy eliminates this by mapping Python classes to database tables. The pain of raw SQL—query strings, object mapping, connection management—is replaced with a clean, declarative API that feels like writing normal Python.

Core concept / mental model

Think of a database as a collection of tables (like spreadsheets). A model is a Python class that represents one table. Each instance of that class is a row in the table, and each attribute of the instance is a column in that row. When you use Flask SQLAlchemy for models, you're essentially creating a translation layer between your Python objects and the relational database. This is called an Object-Relational Mapper (ORM).

Imagine you have a library. The card catalog (schemas) tells you where books are. The books themselves are rows. A model is like a librarian who knows how to find, add, or remove books without you needing to know the exact shelf numbers—you just say "give me all mystery books" and the librarian does the SQL for you.

In Flask-SQLAlchemy, you create a db object that holds the ORM's core. Then you define models by subclassing db.Model. Each model has columns defined using db.Column, with types like db.Integer, db.String, and db.DateTime. Relationships between tables are modeled with db.relationship, letting you navigate from one model to another as if they were linked objects.

How it works step by step

Here's the high-level flow of how Flask SQLAlchemy works under the hood when you use it in a Flask app:

1. Initialize the extension — Create a db = SQLAlchemy() instance in your application. This binds the ORM to your Flask app.
2. Define models — Create classes inheriting from db.Model. Each class represents a table. Define columns with db.Column and specify types, primary keys, and constraints.
3. Create the database — Call db.create_all() to generate tables from your model definitions. This is done once, typically in a shell or initialization script.
4. Interact with data — Use the session (db.session) to add, query, update, and delete records. The session tracks changes and commits them as a transaction to the database.
5. Query with ORM — Use Model.query to fetch data. It returns objects, not tuples, so you can access attributes directly.
6. Commit transactions — After adding or modifying records, call db.session.commit() to persist changes. Without a commit, changes are lost.

This flow replaces raw SQL's manual connection handling and cursor management. The ORM handles connection pooling, quoting, and escaping—reducing the risk of SQL injection attacks.

Hands-on walkthrough

Let's build a small Flask app that uses Flask-SQLAlchemy for models. First, install the library:

pip install flask flask-sqlalchemy

Now create app.py with a simple User model:

# app.py
from flask import Flask, request, jsonify
from flask_sqlalchemy import SQLAlchemy

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

db = SQLAlchemy(app)

# Define the User model
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)

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

# Create tables within the app context
with app.app_context():
    db.create_all()

# Routes to use the model
@app.route('/users', methods=['POST'])
def create_user():
    data = request.get_json()
    user = User(username=data['username'], email=data['email'])
    db.session.add(user)
    db.session.commit()
    return jsonify({'id': user.id, 'username': user.username}), 201

@app.route('/users/<int:user_id>')
def get_user(user_id):
    user = User.query.get(user_id)
    if user is None:
        return jsonify({'error': 'Not found'}), 404
    return jsonify({'id': user.id, 'username': user.username, 'email': user.email})

if __name__ == '__main__':
    app.run(debug=True)

Run the app, then test it with curl:

curl -X POST http://127.0.0.1:5000/users -H "Content-Type: application/json" -d '{"username":"alice","email":"alice@example.com"}'
# Output: {"id":1,"username":"alice"}

curl http://127.0.0.1:5000/users/1
# Output: {"id":1,"username":"alice","email":"alice@example.com"}

The model acts as a Python object, and Flask-SQLAlchemy does all the heavy lifting. Notice how we never wrote a single SQL statement.

Now, let's extend the example with a relationship. Imagine a Post model that belongs to a User:

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)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    user = db.relationship('User', backref=db.backref('posts', lazy=True))

Notice the foreign key syntax and the relationship. Now you can do user.posts to get all posts by that user, and post.user to get the author. This is the magic of using Flask SQLAlchemy for models—complex joins become simple attribute access.

Compare options / when to choose what

Flask-SQLAlchemy isn't the only way to handle models in Flask. Here's a comparison with other popular approaches:

When to choose Flask-SQLAlchemy: If you're building a standard CRUD app, need relationships, and want to move fast, this is your tool. When to avoid it: If you're only doing trivial lookups, or if you need to write highly optimized complex queries that ORMs mangle, consider SQLAlchemy Core or raw SQL. However, for 95% of development, Flask-SQLAlchemy is the pragmatic choice.

Troubleshooting & edge cases

Even with an ORM, things go wrong. Here are common pitfalls and how to fix them:

• ModuleNotFoundError: No module named 'flask_sqlalchemy' – Did you install Flask-SQLAlchemy? Run pip install flask-sqlalchemy in your virtual environment.
• RuntimeError: Working outside of application context – This happens when you call db.create_all() outside a request or app context. Wrap it in with app.app_context(): as shown above.
• sqlalchemy.exc.IntegrityError: UNIQUE constraint failed – You tried to insert a duplicate username or email. Add error handling to catch unique violations, or check existence first.
• AttributeError: 'NoneType' object has no attribute 'username' – The query returned None because you used get with a non-existent ID. Always check for None before accessing attributes.
• **Flask-SQLAlchemy uses a db object, but you forgot to set SQLALCHEMY_TRACK_MODIFICATIONS – not an error, just a warning: Disable it with app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False to silence it.

Edge case — lazy loading: When accessing user.posts, SQLAlchemy executes a query behind the scenes. If you're outside an app context (e.g., in a script without app.app_context()), you may encounter issues. Keep all model interactions inside request handlers or app context blocks.

Edge case — multiple databases: If you need to connect to more than one database, Flask-SQLAlchemy supports binds, but it adds complexity. For most apps, one database is enough.

What you learned & what's next

You now understand the core idea behind using Flask SQLAlchemy for models: defining database tables as Python classes, querying them with object-oriented methods, and managing relationships easily. You've completed a practical exercise that showed how to create, query, and relate models. You can now apply this in your own projects to reduce code duplication and improve safety.

Next step: In the next lesson of this Python web development track, you'll learn how to handle database migrations with Flask-Migrate, so you can evolve your schema without losing data. With models under your belt, you'll be ready to build real-world applications with proper data persistence.

What you should remember from this lesson:

• Models map to tables – Each Python class becomes a database table, and each attribute is a column.
• The db object is your bridge – It handles connections, sessions, and everything in between.
• Use db.create_all() in an app context – Or you'll hit the “working outside of application context” error.
• Querying returns objects, not tuples – This is the biggest productivity win.
• Relationships connect models – Foreign keys and db.relationship make joins trivial.
• Always commit you session – Uncommitted changes are lost.

Now, go ahead and try adding a new model to your own Flask app. You'll feel the difference immediately!