Creating the Database (db.create_all)

When you have models, you need to create tables.

For quick demos, you can use db.create_all().

Example

python

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
 
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///app.db"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
 
db = SQLAlchemy(app)
 
 
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
 
 
with app.app_context():
    db.create_all()

Why app.app_context()?

Database operations often need the Flask application context.

Important warning

db.create_all():

creates missing tables
does not handle schema changes safely over time

That’s why real apps use migrations (Flask-Migrate).

Use create_all() for:

learning
small prototypes

Use migrations for:

anything you deploy

`create_all()` creates what is missing, and nothing else

create.py

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

Measured behaviour:

first run

tables now: ['user']
columns: id INTEGER not-null pk, name VARCHAR(20) not-null, age INTEGER not-null

running it a second time

no error, no change

It is safe to call on every start. It issues CREATE TABLE IF NOT EXISTS for each model it knows about, so existing tables are left alone.

That last part is also its limitation:

diagram Diagram mermaid

  flowchart TD
  C["db.create_all()"] --> Q{"does the table exist?"}
  Q -->|"no"| N["CREATE TABLE"]
  Q -->|"yes"| S["LEAVE IT ALONE
even if the model has changed"]
  S --> D["schema drift:
the model and the table disagree,
silently"]

The silent failure that follows

Measured: a column was added to the model, create_all() was run again, and the table was unchanged.

measured

added a column to the model, ran create_all() -> columns still ['id','title','body','views']
no ALTER TABLE, no error, no warning

The application then fails at query time with something like no such column: note.author, far from the change that caused it.

It cannot add, drop, rename or retype a column. As soon as a schema outlives its first deployment you need real migrations — Flask-Migrate, which wraps Alembic:

migrations

flask db init
flask db migrate -m "add author to note"
flask db upgrade

Alembic diffs the models against the database and generates the ALTER TABLE steps, and each one is a file you can review and roll back.

For a development reset, dropping is explicit and obviously destructive:

reset.py

with app.app_context():
    db.drop_all()      # deletes every table and everything in them
    db.create_all()

See it move

sketch Why create_all cannot fix a changed model p5.js

create_all issues CREATE TABLE IF NOT EXISTS. An existing table is skipped entirely, so a model change never reaches the schema.

Check yourself

pch.quizTag pch.quizDefaultTitle

You add a column to a model and run db.create_all() again. What happens to the existing table?

pch.quizShowAnswer

C — nothing at all: create_all skips tables that already exist, with no error or warning — Measured: the columns were unchanged. The mismatch surfaces much later as a query error such as no such column. create_all is not a migration tool.
Is it safe to call db.create_all() every time the app starts?

pch.quizShowAnswer

B — yes: it only creates tables that do not exist, and running it twice measured no error and no change — It issues CREATE TABLE IF NOT EXISTS for each known model. It is harmless to repeat — and equally powerless to update anything.
What should you use once a schema needs to change after its first deployment?

pch.quizShowAnswer

B — Flask-Migrate, which wraps Alembic to generate reviewable ALTER TABLE steps — flask db migrate diffs models against the database and writes a migration file you can review and roll back. drop_all would destroy production data.

pch.coffeeTagline

pch.coffeeCta

pch.feedbackHeading

pch.feedbackSubheading