Flask-SQLAlchemy Basics

Every lesson so far has kept Bookshelf's data in memory or in a session — fine for flashing a message or remembering a form's values, useless the moment the server restarts and every book a user logged simply vanishes. This lesson introduces the piece that actually persists Bookshelf's data: a real database, accessed through Flask-SQLAlchemy.

Flask Doesn't Ship a Database Layer

Recall from Why Flask? that Flask's "microframework" design means a bare pip install flask gives you routing, requests, and templating — and nothing about databases at all. That's a deliberate omission, not a missing feature: Django ships a built-in ORM that every Django project uses whether it wants to or not, while Flask leaves the choice of database toolkit, or whether to use one at all, entirely up to you.

For Bookshelf, that choice is Flask-SQLAlchemy — the standard, most widely used extension for the job. It isn't a new ORM written from scratch; it's a thin, Flask-aware wrapper around SQLAlchemy, Python's mature and powerful general-purpose ORM. Flask-SQLAlchemy's entire job is to make SQLAlchemy feel native to a Flask app: it wires database configuration into app.config, manages a session per request automatically, and gives your models a clean db.Model base class to inherit from.

Installing Flask-SQLAlchemy

With Bookshelf's virtual environment active (see Installation & Virtual Environments), install the extension with pip:

pip install flask-sqlalchemy

This pulls in both Flask-SQLAlchemy itself and SQLAlchemy as a dependency, so nothing else needs installing separately to start defining models.

Initializing db in the App Factory

Recall the app factory pattern from Project Structure & App Factory: Flask extensions in that pattern are created at module level, unattached to any specific app, and only bound to a real app instance inside create_app(). Flask-SQLAlchemy follows that exact two-step shape:

# extensions.py
from flask_sqlalchemy import SQLAlchemy

# Created once, at module level, with no app attached yet
db = SQLAlchemy()

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

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

    # Bind this app instance to the shared db object
    db.init_app(app)

    return app

Why this two-step pattern specifically pays off with the app factory: db = SQLAlchemy() at module level gives every part of Bookshelf — models, blueprints, test files — one single, importable db object to reference, regardless of which app instance is currently running. db.init_app(app) then connects that shared object to a specific app inside the factory, which is exactly what lets create_app() be called more than once with different configurations — say, a real SQLite file for development and a separate in-memory database for automated tests — without the models themselves ever needing to change.

Configuring the Database Connection

Flask-SQLAlchemy reads exactly one required setting to know which database to talk to: SQLALCHEMY_DATABASE_URI, a connection string set on app.config. For Bookshelf during local development, a SQLite database is the simplest possible choice:

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///bookshelf.db'

That URI tells Flask-SQLAlchemy to store the entire database in a single file named bookshelf.db, created automatically in the project's working directory the first time it's needed. No separate database server, no login credentials, nothing to install beyond the Python packages already in requirements.txt.

Defining a First Model

A model in Flask-SQLAlchemy is a plain Python class that inherits from db.Model, with each attribute declared as a db.Column describing that column's SQL type and constraints. Here is Bookshelf's Book model, kept intentionally simple for now — the next lesson expands it with foreign keys and relationships:

# models.py
from extensions import db

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    author = db.Column(db.String(120), nullable=False)
    pages = db.Column(db.Integer)
    status = db.Column(db.String(20), default='want_to_read')
    rating = db.Column(db.Integer)

    def __repr__(self):
        return f'<Book {self.title}>'

Each db.Column call mirrors an actual SQL column definition: db.Integer and db.String(200) map to real column types, primary_key=True marks id as the table's primary key, nullable=False means the database will reject a row missing that value, and default='want_to_read' pre-fills status for a book that hasn't been assigned one yet.

Creating the Tables

Defining Book as a Python class doesn't create anything in the actual database file — it's still just a class until Flask-SQLAlchemy is told to build the matching table. Inside an application context (commonly from a Flask shell or a one-off setup script), that's a single call:

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

db.create_all() inspects every model that has been imported and, for each one that doesn't already have a matching table, creates it — including bookshelf.db itself, if it doesn't exist yet. This is genuinely fine for Bookshelf's very first prototype, where there's no existing data to protect.

SQLite vs. a Production Database

SQLite is an excellent choice while Bookshelf is a project of one developer running on one machine: it needs no separate server process, the entire database is one portable file, and setup is a single line of configuration. That simplicity comes from a trade-off worth knowing about early:

Bookshelf uses SQLite throughout this track because it keeps every example runnable with zero extra setup. But once Bookshelf is a real app with many users logging books at the same time, that single-file-locks-per-write behavior becomes a genuine bottleneck — which is exactly why Deployment revisits this same decision and walks through switching SQLALCHEMY_DATABASE_URI to a PostgreSQL connection string for production.