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.

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.

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.

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.

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

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

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

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.