Database with SQLAlchemy

Reviewed & published by Brayan K

Flask-SQLAlchemy is an extension that lets you define database tables as Python classes and query them with Python code instead of raw SQL, giving your Flask app a clean, object-oriented way to store and read data.

Part of the free Flask course at LearnCodingFast — hands-on lessons with worked examples and the output they print, plus practice exercises and a quick quiz.

In this lesson you'll configure a database, define your first model, create the tables, and add and read rows using the SQLAlchemy ORM.

What You'll Learn in This Lesson

• What an ORM is and why it helps
• How to install and configure Flask-SQLAlchemy
• Defining a model class with db.Model
• Creating tables with db.create_all()
• Adding rows with db.session.add and commit
• Reading rows with Model.query

1 Configuring the Database

Flask-SQLAlchemy is an ORM extension — it maps database rows to Python objects so you query data with Python instead of SQL.

You create a SQLAlchemy object bound to your app, and tell it where the database lives through the SQLALCHEMY_DATABASE_URI config. For learning, SQLite is ideal: the whole database is one file, no server required.

# Install the extension first
pip install flask-sqlalchemy

# Then in app.py:
#   app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///app.db"
#   db = SQLAlchemy(app)

2 Defining a Model

A model is a Python class that inherits from db.Model. Each class becomes a table, and each attribute defined with db.Column becomes a column. Calling db.create_all() builds the tables.

The code below defines a User model with three columns and creates the table:

# app.py — define a model and create the table
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///app.db"

# Bind SQLAlchemy to the application.
db = SQLAlchemy(app)

# Each model class maps to one database table.
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), nullable=False)

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

# Create tables inside an application context.
with app.app_context():
    db.create_all()
    print("Tables created:", list(db.metadata.tables.keys()))

# ✅ Expected output:
# Tables created: ['user']

The primary_key=True column auto-numbers each row. The constraints unique and nullable are enforced by the database. Run flask run after this to serve an app backed by the table.

3 Adding and Reading Rows

To insert data you create a model instance, add it to the session with db.session.add(), then save it permanently with db.session.commit(). To read data you use User.query.

# app.py — insert rows and read them back
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///app.db"
db = SQLAlchemy(app)

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), nullable=False)

with app.app_context():
    db.create_all()

    # Create and stage two new rows.
    db.session.add(User(username="ada", email="ada@example.com"))
    db.session.add(User(username="grace", email="grace@example.com"))

    # Persist them to the database.
    db.session.commit()

    # Read every row back out.
    for user in User.query.all():
        print(user.id, user.username, user.email)

    # Filter for a single row.
    ada = User.query.filter_by(username="ada").first()
    print("Found:", ada.email)

query.all() returns a list of every row; query.filter_by(...).first() returns a single matching object or None. Nothing is saved until commit() runs.

🔬 Anatomy of a Model

🎯 Your Turn: Fill in the Blanks

Complete the code below. Replace each ___ so it defines a Book model, creates the table, and adds one row.

# 🎯 YOUR TURN: replace each ___ to store a book
from flask import Flask
from flask_sqlalchemy import ___        # the extension class

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///app.db"
db = ___(app)                            # bind the ORM

class Book(db.___):                      # inherit from the model base
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(120), nullable=False)

with app.app_context():
    db.___()                             # create the tables
    db.session.add(Book(title="Flask 101"))
    db.session.___()                     # save the row
    print(Book.query.all())

Common Errors & Quick Reference

❌ RuntimeError: Working outside of application context

Database calls like db.create_all() and query need an app context. Wrap them in with app.app_context(): when running outside a request.

❌ My new rows disappear after restart

You added them but never called db.session.commit(). Staged changes are only saved permanently when you commit the session.

🏆 Mini Challenge: A Task Table

Build a small to-do database with one model and a few rows.

• Define a Task model with an id and a title
• Add a done boolean column that defaults to False
• Insert two tasks, commit, then print every task

# 🏆 Mini Challenge: a to-do table
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///tasks.db"
db = SQLAlchemy(app)

class Task(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    done = db.Column(db.Boolean, default=False)

with app.app_context():
    db.create_all()
    db.session.add(Task(title="Learn SQLAlchemy"))
    db.session.add(Task(title="Build an app", done=True))
    db.session.commit()
    for task in Task.query.all():
        print(task.id, task.title, "done" if task.done else "todo")

❓ Frequently Asked Questions

Lesson complete — your app has a database!

You configured Flask-SQLAlchemy, defined a model, created tables with db.create_all(), and inserted and queried rows. Your data now persists between runs.

Practice quiz

Continue this course

• Previous: Flask Blueprints
• Next: CRUD Operations — Create, read, update, and delete database records
• Quick reference: Flask cheat sheet › Database (SQLAlchemy)

Frequently asked questions

What is Flask-SQLAlchemy?

Flask-SQLAlchemy is an extension that integrates the SQLAlchemy ORM with Flask. It lets you define database tables as Python classes (models) and query them with Python instead of writing raw SQL.

What is an ORM?

An ORM (Object-Relational Mapper) maps database rows to Python objects. With SQLAlchemy, each model class becomes a table and each instance becomes a row, so you work with normal Python objects instead of SQL strings.

How do I create the database tables?

Define your model classes inheriting from db.Model, then call db.create_all() inside an application context. This creates any tables that do not yet exist based on your model definitions.

What database should I use to start?

SQLite is perfect for learning — it stores the whole database in a single file and needs no separate server. Set SQLALCHEMY_DATABASE_URI to 'sqlite:///app.db' and Flask-SQLAlchemy handles the rest.