DEV Community

Azhar Alvi
Azhar Alvi

Posted on

My App Can Finally Remember Stuff! (Phase 1 — The Database)

Design it on paper, model it in Python, and NEVER touch the schema by hand.

So Phase 0 got my little FastAPI app running. One route, one JSON response, a lot of pride. But here's the thing nobody warns you about: that app had the memory of a goldfish. Restart it and… nothing. No users, no data, no clue who anyone is.

Phase 1 fixes that. This is where the app gets an actual database — a place to remember things. And honestly? This is where "I'm following a tutorial" started turning into "oh, I kind of get how backends work now." I went in knowing zero SQL. Came out having designed tables, built models, run a real migration, and read/written actual data. Let me dump what I learned [and the parts that tripped me up, because there were a few].

The Structure is as follows. Lets call it PHASE 1 — The Data Layer:

  • Get the right tools installed in the right place (SQLAlchemy + Alembic)
  • Design the tables on paper BEFORE writing any code
  • Turn that design into Python models
  • Wire up the database connection
  • Set up Alembic and run my first migration
  • Actually write and read a row [the payoff!]

First, the two new toys

Two libraries do the heavy lifting here:

  • SQLAlchemy — the ORM. Fancy word, simple job: it lets me talk to the database in Python instead of writing raw SQL. I write session.add(user), it writes the INSERT for me.
  • Alembic — the migration tool. Think of it as git, but for the shape of your database. Every time I change the table structure, it saves that change as a numbered file I can replay or roll back. 
source venv/Scripts/activate      # Windows/Git Bash = Scripts/, not bin/ [learned this the hard way in Phase 0]
pip install sqlalchemy alembic
pip freeze > requirements.txt
Enter fullscreen mode Exit fullscreen mode

Gotcha #1: I first installed these in a random throwaway folder, then wondered why my real project couldn't see them. Turns out a virtual environment is per-folder — packages installed in one venv are invisible to another. Reinstalled in the actual project. Lesson: check for (venv) in your prompt and make sure it's the right one before you install anything.

Step 1: Draw it on paper (yes, really)

Before touching code, I sketched two boxes:

USERS                              EXPENSES
  id       (primary key)             id         (primary key)
  name                               amount     (money)
  email    (unique)                  description
  created_at / updated_at            spent_on   (the date)
                                     user_id  --> points to USERS.id
                                     created_at / updated_at
Enter fullscreen mode Exit fullscreen mode

The two ideas that made everything click:

  • A primary key (id) is just a unique, auto-numbered tag for each row. Names repeat, IDs don't.
  • A foreign key (user_id) is a column that stores another table's id. That's the whole magic of relational databases — Azhar's expenses all carry his user_id, so the DB always knows whose lunch was whose.

Sounds obvious now. Was NOT obvious an hour earlier.

Step 2: Turn the drawing into models

This is models.py — basically my paper sketch, but in Python. One class = one table.

from datetime import date, datetime
from decimal import Decimal

from sqlalchemy import MetaData, String, DateTime, Numeric, Date, ForeignKey, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


NAMING_CONVENTION = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}


class Base(DeclarativeBase):
    metadata = MetaData(naming_convention=NAMING_CONVENTION)


class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    email: Mapped[str] = mapped_column(String(255), unique=True, index=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
    expenses: Mapped[list["Expense"]] = relationship(back_populates="user")


class Expense(Base):
    __tablename__ = "expenses"

    id: Mapped[int] = mapped_column(primary_key=True)
    amount: Mapped[Decimal] = mapped_column(Numeric(10, 2))
    description: Mapped[str] = mapped_column(String(255))
    spent_on: Mapped[date] = mapped_column(Date)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
    user: Mapped["User"] = relationship(back_populates="expenses")
Enter fullscreen mode Exit fullscreen mode

A few things I picked up here that felt like "real developer" moments:

  • Money is NEVER a float. Ever. Floats can't hold 0.10 + 0.20 exactly [it comes out as 0.30000000000000004, I'm not kidding], so over enough expenses your totals drift. Store it as Numeric(10, 2) → Python Decimal. Exact, always.
  • Every table gets created_at and updated_at. You will always, always want to know when something was made or changed.
  • The type annotation controls nullability. Mapped[str] = required. Mapped[str | None] = optional. Neat.
  • Those two relationship(...) lines aren't columns — they're just Python convenience so I can write user.expenses and get a list back without writing a query. The actual link is still the user_id foreign key.

Gotcha #2: I originally named the expense date column date. Bad idea — naming a column the same as its Python type (date: Mapped[date]) confuses everything and breaks type inference. Renamed it spent_on. Moral: don't name your stuff after builtins.

Step 3: The connection [kept separate on purpose]

database.py — this is how to connect, deliberately kept apart from models.py [which is what the tables are]:

import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

DATABASE_URL = os.getenv("DATABASE_URL", "sqlite:///./expenses.db")
connect_args = {"check_same_thread": False} if DATABASE_URL.startswith("sqlite") else {}

engine = create_engine(DATABASE_URL, echo=True, connect_args=connect_args)
SessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False)
Enter fullscreen mode Exit fullscreen mode

Why the os.getenv dance instead of just hardcoding the path? Because future-me deploying to a real Postgres database will just set an environment variable and NOT have to change a single line of code. Same reason secrets live in .env. One source of truth. Also — echo=True prints every SQL statement the ORM generates, which is weirdly satisfying to watch.

Oh, and before creating any of this I added *.db and *.sqlite3 to .gitignore. The database file is local junk data — it has no business in my repo. [And yeah, same "before it exists" trick I used for .env — git can't accidentally track what it was already told to ignore.]

Step 4: Alembic and my first migration

Here's the part I was low-key scared of, and it turned out to be the coolest bit.

alembic init alembic     # creates the migration setup
Enter fullscreen mode Exit fullscreen mode

Then I pointed Alembic at my project in alembic/env.py — told it where my models live and where the database is:

from database import DATABASE_URL
from models import Base

config.set_main_option("sqlalchemy.url", DATABASE_URL)
target_metadata = Base.metadata
Enter fullscreen mode Exit fullscreen mode

This is the important bit: Alembic's autogenerate works by comparing my models to the actual database and writing the difference. target_metadata is how it "sees" my models. Without it, it detects nothing.

I also added that NAMING_CONVENTION thing (up in models.py) BEFORE generating anything. Why bother? Because otherwise the database invents random names for your constraints/indexes — and SQLite often gives them no name at all. Then a future migration that needs to change one has nothing to grab onto. Set the convention early, and everything gets clean, predictable names like fk_expenses_user_id_users. Set-it-and-forget-it.

Then the magic command:

alembic revision --autogenerate -m "create users and expenses tables"
Enter fullscreen mode Exit fullscreen mode

And it wrote the migration for me — two create_table calls, the foreign key, the indexes, plus a matching downgrade() that undoes it all. I read the whole file before running it [apparently this is a habit real teams enforce — autogenerate isn't perfect, so you always eyeball it first]. Looked right. So:

alembic upgrade head     # actually builds the tables
alembic current          # shows my revision with (head) = up to date
Enter fullscreen mode Exit fullscreen mode

Watching the real CREATE TABLE SQL scroll past because of echo=True? Chef's kiss. That was the "oh, THIS is what an ORM does" moment.

Gotcha #3: My -m message accidentally said "expense" (singular), so the migration filename said expense too — and I panicked thinking the tables were wrong. They weren't. The -m text is just a label; the real table names come from __tablename__. Totally cosmetic.

Gotcha #4: When I pasted my code into an email, Gmail helpfully turned users.id into a clickable link http://users.id/. Nearly gave me a heart attack. It's just email being email — the real file was fine. Check your code in the editor, not in email.

Step 5: The payoff — write a row, read it back 

from decimal import Decimal
from datetime import date
from sqlalchemy import select
from database import SessionLocal
from models import User, Expense

with SessionLocal() as session:
    user = User(name="Azhar", email="azhar@example.com")
    session.add(user)
    session.commit()            # NOW it's in the DB, and user.id gets assigned

    session.add(Expense(
        amount=Decimal("12.50"), description="Lunch",
        spent_on=date.today(), user_id=user.id,
    ))
    session.commit()

    fetched = session.scalars(select(User)).first()
    print(fetched.id, fetched.name, [(e.description, e.amount) for e in fetched.expenses])
Enter fullscreen mode Exit fullscreen mode

And it printed my user and their lunch. My app remembers things now. 🎉

Couple of things worth knowing:

  • Nothing actually hits the database until commit(). That's also when the primary key gets assigned — which is exactly why I commit the user before the expense that needs its id.
  • Decimal("12.50") uses quotes on purpose. Decimal(12.50) [no quotes] would drag the float imprecision back in. Little detail, big deal for money.
  • select(User) with session.scalars(...) is the modern SQLAlchemy 2.0 way. If you see session.query(...) in older tutorials, that's the legacy style.

Gotcha #5: Ran the script a second time and it blew up with an IntegrityError on the email. That's not a bug — it's my unique constraint literally doing its job and refusing a duplicate. Honestly kind of reassuring to see it work.

Stuff I want to remember [the honest takeaways]

  • Design on paper first. The code is just the drawing, translated.
  • Money is Decimal/Numeric, never float. Build it from a string.
  • Give every table created_at / updated_at.
  • Never hardcode the database URL — read it from an env var.
  • NEVER edit the database structure by hand. Models → migration, every single time.
  • Always read an autogenerated migration before you run it.
  • Set your naming convention before the first migration, not after.
  • Virtual environments are per-project. Install in the right one. Freeze requirements.txt after every install.
  • Ignore .db files and secrets before they exist. And always commit your migration files — they're the schema's shared history.
  • Keep echo=True on while learning so you can actually see the SQL. It demystifies everything.

Next up: Phase 2, where I turn all this into real API endpoints [POST an expense, GET the list, the whole CRUD gang]. If Phase 1 gave the app a memory, Phase 2 gives it a mouth. See you there.

Top comments (0)