Flask SQLAlchemy: Database Connection and Models

A Python list only exists for the lifetime of the application process. Once the server stops or restarts, every item in that list disappears. A database stores data outside the Flask process, so a note created today can still be read the next time the application starts.

This example uses SQLite and Flask-SQLAlchemy. SQLite does not require a separate database server, while Flask-SQLAlchemy connects SQLAlchemy to the Flask application lifecycle.

Install the Database Extension

Activate the project virtual environment, then install the package:

# File: terminal
python -m pip install flask-sqlalchemy

The package provides Flask integration, while the central concepts still come from SQLAlchemy: classes represent tables, objects represent rows, and a session manages database operations.

Define the Note Model

Create models.py so the database object and model do not crowd the application file:

# File: models.py
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()


class Note(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(120), nullable=False)

    def __repr__(self):
        return f"<Note {self.id}: {self.title}>"

The Note class maps to a note table. Every instance represents one row. The id column is the primary key that distinguishes notes, while title is limited to 120 characters and cannot be NULL.

nullable=False is a database-level rule, not a replacement for form validation. An empty string such as "" is not NULL, so a route must still ensure that the title contains useful text.

Connect SQLAlchemy to Flask

The application factory defines the database location and calls db.init_app(app):

# File: app.py
from flask import Flask
from models import Note, db


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

The sqlite:///app.db URI asks Flask-SQLAlchemy to use a SQLite file named app.db. With a relative URI like this, the file is normally created in the application’s instance/ folder rather than directly beside app.py.

The db object is created without binding it to a particular app. Inside create_app(), db.init_app(app) connects the extension to that Flask instance. Tests can therefore provide another database configuration without rewriting the model.

Importing Note ensures the model is registered before tables are created. Even though the Note name is not otherwise referenced in app.py, importing it loads the model definition into SQLAlchemy metadata.

Create the Table for the First Time

A model describes a table, but it does not automatically create that table in the database file. Open the Flask shell:

# File: terminal
python -m flask --app app shell

Then run:

# File: Flask shell
from models import db

db.create_all()
exit()

db.create_all() is suitable for practice and initial table creation. It is not designed to alter an existing table. Once the project grows, use a migration tool such as Flask-Migrate so column changes are recorded and applied incrementally.

Store and Query Data

To see the session without introducing a form yet, create this small script:

# File: seed.py
from app import create_app
from models import Note, db

app = create_app()

with app.app_context():
    note = Note(title="Learn Flask-SQLAlchemy")
    db.session.add(note)
    db.session.commit()

    notes = db.session.execute(db.select(Note)).scalars().all()
    for item in notes:
        print(item.id, item.title)

Run it with python seed.py. The app.app_context() block identifies which Flask app is active, allowing SQLAlchemy to read the correct database configuration.

db.session.add(note) places the object in the session’s unit of work, but the change is not permanent yet. db.session.commit() completes the transaction and writes it to the database. After the commit, note.id contains the primary key generated by SQLite.

The db.select(Note) query selects every note. .scalars() extracts Note objects from the result, and .all() collects them into a list.

Model Changes Need Migrations

If you add a body field to the model after the table exists, db.create_all() does not add that column to the old table. This is a common source of confusion when learning an ORM. Python models and the actual database schema are separate things that must remain synchronized through migrations.

For practice, add an optional body field and a created_at field with a default value. Because this is still an experiment, you can remove the local database and run db.create_all() again. For data that must be preserved, use migrations and never delete the database casually.

Once the script can run twice and previous notes still appear, the difference between in-memory and persistent data should be clear. The same model and query can now move into routes to build a CRUD flow.