Phase 5 - Databases (Flask-SQLAlchemy)

Most real apps need persistent data.

In Flask, the most common approach is:

• Flask-SQLAlchemy (integration layer)
• SQLAlchemy (ORM)
• Flask-Migrate (migrations via Alembic)

You’ll learn

• What an ORM is and why it matters
• Setting up Flask-SQLAlchemy
• Defining models and data types
• Creating tables with db.create_all() (dev only)
• CRUD operations (create/read/update/delete)
• Relationships (one-to-many, many-to-many)
• Migrations (Flask-Migrate)
• Raw SQL when you need it

Phase checklist

1. Introduction to ORMs
2. Setting up Flask-SQLAlchemy
3. Configuring Database URI
4. Creating Database Models
5. Primary Keys and Column Types
6. Creating the Database (db.create_all)
7. CRUD - Create Record
8. CRUD - Read Record
9. CRUD - Update Record
10. CRUD - Delete Record
11. One-to-Many Relationships
12. Many-to-Many Relationships
13. Database Migrations (Flask-Migrate)
14. Executing Raw SQL

The phase at a glance

This phase exists to store data that outlives the request. It assumes forms producing validated values, and once it is done you can build a site whose data survives a restart.

diagram Diagram mermaid

  flowchart TD
  subgraph G0["Setting up"]
    N0_0["Introduction to ORMs"]
    N0_1["Setting up Flask-SQLAlchemy"]
    N0_2["Configuring Database URI"]
    N0_3["Creating Database Models"]
  end
  subgraph G1["Modelling and creating"]
    N1_0["Primary Keys and Column Types"]
    N1_1["Creating the Database (db.create_all)"]
  end
  N0_3 --> N1_0
  subgraph G2["Reading and writing"]
    N2_0["CRUD - Create Record"]
    N2_1["CRUD - Read Record"]
    N2_2["CRUD - Update Record"]
    N2_3["CRUD - Delete Record"]
  end
  N1_1 --> N2_0
  subgraph G3["Relationships and change"]
    N3_0["One-to-Many Relationships"]
    N3_1["Many-to-Many Relationships"]
    N3_2["Database Migrations (Flask-Migrate)"]
    N3_3["Executing Raw SQL"]
  end
  N2_3 --> N3_0

Where this sits in the track

sketch The track, and what each phase unlocks p5.js Pause Restart

Each phase assumes the one before it. Click a phase to see what you can build once it is done.

The pages, in reading order

#	page
1	Introduction to ORMs
2	Setting up Flask-SQLAlchemy
3	Configuring Database URI
4	Creating Database Models
5	Primary Keys and Column Types
6	Creating the Database (db.create_all)
7	CRUD - Create Record
8	CRUD - Read Record
9	CRUD - Update Record
10	CRUD - Delete Record
11	One-to-Many Relationships
12	Many-to-Many Relationships
13	Database Migrations (Flask-Migrate)
14	Executing Raw SQL

14 pages. They are ordered so that each one only uses ideas from the pages above it — reading out of order mostly works, but the examples assume what came before.

Check yourself

pch.quizTag pch.quizDefaultTitle

1. What is the largest phase in the track about?

   A making data outlive the request, and modelling relationships between it B improving performance C styling database output D deploying a database server
   pch.quizShowAnswer

   A — making data outlive the request, and modelling relationships between it — Fourteen pages cover setup, modelling, CRUD, relationships and migrations — the point at which an app stops being stateless.

2. Why does Database Migrations appear near the end of this phase rather than next to db.create_all?

   A migrations are optional B create_all is enough until the schema changes, and migrations are the answer to that specific later problem C Flask-Migrate cannot be installed earlier D migrations only work on relationships
   pch.quizShowAnswer

   B — create_all is enough until the schema changes, and migrations are the answer to that specific later problem — create_all creates missing tables and never alters existing ones. Migrations exist for the moment a schema has to change after it has been deployed.

3. What do the phase overview pages give you that the individual pages do not?

   A the full code for each topic B the reading order, what the phase assumes, and what you can build once it is done C a downloadable project template D the deployment configuration
   pch.quizShowAnswer

   B — the reading order, what the phase assumes, and what you can build once it is done — Each overview lists its pages in sidebar order and names the phase's prerequisites and outcome, so you can tell whether you are ready for it.