Database with SQLAlchemy

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.

Learn Database with SQLAlchemy in our free Flask course — a beginner-friendly interactive lesson with worked examples, a practice exercise and a quick…

Part of the free Flask course at LearnCodingFast — hands-on lessons with examples you run in your browser, 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

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.

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:

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 .

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

Code

What it does

db = SQLAlchemy(app)

Binds the ORM to your application

class User(db.Model)

Defines a table as a Python class

db.Column(db.Integer, ...)

Declares a column and its type

db.create_all()

Builds tables from your models

db.session.commit()

Saves staged changes to the database

🎯 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.

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.

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

Task

Command / Code

Install

pip install flask-sqlalchemy

Create tables

Add a row

db.session.add(obj)

Read all rows

Model.query.all()

🏆 Mini Challenge: A Task Table

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

❓ Frequently Asked Questions

Lesson 11 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.

🚀 Up next: CRUD Operations — create, read, update, and delete records cleanly inside your routes.

Practice quiz

What is Flask-SQLAlchemy?

  • A template engine
  • An ORM extension that maps database rows to Python objects
  • A migration-only tool
  • A WSGI server

Answer: An ORM extension that maps database rows to Python objects. Flask-SQLAlchemy integrates the SQLAlchemy ORM with Flask so you query data with Python objects instead of raw SQL.

Which base class does a model inherit from?

  • db.Model
  • db.Table
  • SQLAlchemy
  • db.Base

Answer: db.Model. Each model class inherits from db.Model, and each class becomes a database table.

Which config key tells Flask-SQLAlchemy where the database lives?

  • DATABASE_PATH
  • DB_URI
  • SQLALCHEMY_DATABASE_URI
  • SQLALCHEMY_URL

Answer: SQLALCHEMY_DATABASE_URI. You set app.config['SQLALCHEMY_DATABASE_URI'], for example to 'sqlite:///app.db'.

How do you declare a column in a model?

  • db.Field(...)
  • Column.new(...)
  • db.Attribute(...)
  • db.Column(db.Integer, ...)

Answer: db.Column(db.Integer, ...). Each attribute defined with db.Column(type, ...) becomes a column in the table.

Which call builds the tables from your models?

  • db.create_all()
  • db.build()
  • db.migrate()
  • db.session.commit()

Answer: db.create_all(). db.create_all() creates any tables that do not yet exist based on your model definitions.

How do you stage a new row for insertion?

  • db.add(obj)
  • db.session.add(obj)
  • obj.save()
  • db.insert(obj)

Answer: db.session.add(obj). You add a model instance to the session with db.session.add(obj) before committing.

What permanently saves staged changes to the database?

  • db.flush()
  • db.save()
  • db.session.commit()
  • db.persist()

Answer: db.session.commit(). Nothing is saved until db.session.commit() runs, which writes the staged changes.

Which SQLite URI stores data in a file named app.db?

  • sqlite://app.db
  • file://app.db
  • sqlite:app.db
  • sqlite:///app.db

Answer: sqlite:///app.db. 'sqlite:///app.db' stores the whole database in a single app.db file next to your project.

How do you read every row from the User model?

  • User.query.all()
  • User.all()
  • db.query(User)
  • User.rows()

Answer: User.query.all(). Model.query.all() returns a list of every row in the table.

Why must db.create_all() run inside an application context?

  • To compress the file
  • To enable migrations
  • Because database calls need an active app context
  • To speed up queries

Answer: Because database calls need an active app context. Outside a request you wrap database calls in 'with app.app_context():' or you get a RuntimeError about working outside of application context.

Continue this course