Database Schema Setup

You've built routes, connected to a database, maybe even defined a model or two. But when your app hits production, the last thing you want is a no such table error or a schema that doesn't match the code you wrote six months ago. The pain is real: without a disciplined approach to creating your database schema, you'll face migration chaos, data loss, and hours of debugging. In this lesson, you'll learn how to create the database schema for your app—the right way—so your database structure is versioned, reproducible, and ready for evolution.

The problem this lesson solves

When you're building a Python web app, the database schema is the backbone of your data. It defines the tables, columns, types, and relationships that your app relies on. Without a clear schema, you'll encounter:

• Inconsistent data – different environments (dev, staging, prod) drift apart, causing bugs that only appear in production.
• Hard-to-maintain code – SQL queries break because columns are missing or renamed.
• Painful deployments – Every change to the schema requires manual SQL scripts, and one mistake can destroy data.

This lesson gives you a structured method to create and manage your schema from the start. You'll learn how to define it in code, generate it, and apply it systematically—so you never have to wonder what your database looks like again.

Core concept / mental model

Think of your database schema as the blueprint of a building. You wouldn't start construction without a blueprint, and you shouldn't build an app without a schema. The schema declares what data is stored and how it's related, independent of the app code that uses it.

A solid mental model is the migration-based approach:

• Every change to the schema is a migration – a versioned, scripted change (e.g., create table, add column, create index).
• Migrations are applied in order, from an empty database to the current state.
• Your code and schema are always in sync because they're versioned together.

This approach is like a time machine for your database – you can go forward, backward, and replay any state. Tools like Alembic (for SQLAlchemy) and Django's built-in migrations implement this pattern for you.

Definitions

• Schema: The structure of tables, columns, indexes, and constraints.
• Migration: A script that changes the schema from one version to the next.
• Model: A Python class that represents a table in your ORM (Object-Relational Mapper).

How it works step by step

Let's walk through creating a schema for a typical blog app using Python and SQLAlchemy (with Alembic). We'll follow these steps:

1. Install the tools – sqlalchemy, alembic, and a driver like psycopg2-binary for PostgreSQL.
2. Define your models – Create Python classes that map to tables.
3. Set up Alembic – Initialize the migration environment.
4. Create an initial migration – Autogenerate from your models.
5. Apply the migration – Create the actual tables in your database.

Why this order?

• Models come first because they define the source of truth for your schema.
• Alembic uses them to generate migrations, so you avoid hand-writing SQL.
• Applying migrations updates your database in a repeatable way.

Hands-on walkthrough

1. Define your models in Python

Start by defining models for User and Post in models.py.

# models.py
from sqlalchemy import Column, Integer, String, ForeignKey, DateTime
from sqlalchemy.orm import relationship, declarative_base
from datetime import datetime

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True, nullable=False)
    email = Column(String(120), unique=True, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

    posts = relationship('Post', back_populates='author')

    def __repr__(self):
        return f'<User {self.username}>'

class Post(Base):
    __tablename__ = 'posts'

    id = Column(Integer, primary_key=True)
    title = Column(String(200), nullable=False)
    content = Column(String(5000), nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

    author = relationship('User', back_populates='posts')

2. Set up Alembic

Initialize Alembic in your project directory:

pip install alembic sqlalchemy

Then run:

alembic init alembic

This creates an alembic.ini file and an alembic/ directory. Edit alembic/env.py to point to your metadata:

# alembic/env.py
from models import Base

target_metadata = Base.metadata

3. Generate the initial migration

Now autogenerate a migration from your models:

alembic revision --autogenerate -m "create user and post tables"

This creates a script in alembic/versions/. Let's peek at the generated code:

# alembic/versions/abc123_create_user_and_post.py
RevID: abc123
down_revision = None
branch_labels = None
depends_on = None

def upgrade() -> None:
    op.create_table(
        'users',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('username', sa.String(length=50), nullable=False),
        sa.Column('email', sa.String(length=120), nullable=False),
        sa.Column('created_at', sa.DateTime(), nullable=False),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('username'),
        sa.UniqueConstraint('email')
    )
    # ... posts table ...

def downgrade() -> None:
    op.drop_table('posts')
    op.drop_table('users')

Pro tip: Always review the generated migration—autogenerate isn't perfect. Check that nullable and unique constraints match your model definitions.

4. Apply the migration

Now push the schema to your database:

alembic upgrade head

If you're using a fresh SQLite database named app.db, you'll see it appear with tables users and posts. You can verify with sqlite3:

sqlite3 app.db ".tables"

Expected output:

users  posts

5. Let's see it in action

Run a quick script to insert and query data using your models:

# test_db.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import Base, User, Post

engine = create_engine('sqlite:///app.db')
Session = sessionmaker(bind=engine)
session = Session()

# Insert
user = User(username='jane', email='jane@example.com')
session.add(user)
session.commit()

# Insert a post
post = Post(title='Hello World', content='First post!', user_id=user.id)
session.add(post)
session.commit()

# Query
jane = session.query(User).filter_by(username='jane').first()
print(jane.email)  # jane@example.com
print(jane.posts[0].title)  # Hello World

Expected output:

jane@example.com
Hello World

Compare options / when to choose what

You have several approaches to create your schema:

When to choose what?

• For a real app with future changes, use Alembic (or Django migrations). It's the professional choice.
• For a quick prototype or a test suite, Base.metadata.create_all(engine) is fine—just don't ship it.
• Raw SQL if you're not using an ORM or need low-level control, but you'll end up writing your own migration system—avoid unless necessary.

Troubleshooting & edge cases

• Autogenerate doesn't detect everything – It might miss table renames or column type changes. Always review and manually adjust migrations when needed.
• id is NULL when inserting a related row – If you get a NotNullViolation for the foreign key, you probably forgot to commit() the parent object before using its id. In SQLAlchemy, you can use flush() to get an ID without committing.
• Migration order is wrong – If you create a table that references another table, the referenced table must exist first. Alembic does this automatically based on foreign keys, but when writing manual migrations, double-check the order.
• Downgrade fails – If you drop a table that's still referenced by another, it will error. Always write downgrade() in reverse order of upgrade().

Pro tip: Before applying migrations to production, run alembic downgrade base and alembic upgrade head on a copy of your database to ensure you can roll back and forward without data loss.

What you learned & what's next

You now know how to create the database schema for your app using Python and Alembic. You learned how to define models, generate migrations, apply them, and handle common pitfalls. You can confidently set up a database structure that's versioned, reproducible, and ready to evolve.

Key takeaways:

• The schema is your database's blueprint—design it before you build.
• Use migrations to version every schema change.
• Alembic autogenerate saves time but requires review.
• Always test migrations on a copy before applying to production.

Next step: The next lesson in this track will cover seeding your database with initial data. You'll learn how to populate your new tables with default or test data, so your app has something to work with when it starts.

Now go create your schema—and if you get stuck, remember: every migration is just a small, reversible change. Happy coding!