Databases & ORM

Module 2 · Backend · ⏱ ~35 min

Why this matters: All booking data, shop info, and admin accounts live in PostgreSQL. SQLAlchemy is the Python library that lets us read and write that data without writing raw SQL. Every await session.execute(...) in the code is a database query.

What Is a Relational Database?

Think of a database as a collection of spreadsheets, where each spreadsheet is a "table". Each row is one record; each column is a piece of data. Tables can reference each other using IDs.

shops table:
┌──────────────────────────────────┬────────────────────┬──────────┐
│ id (UUID)                        │ name_en            │ category │
├──────────────────────────────────┼────────────────────┼──────────┤
│ fa2fd810-04a8-41f7-bcc1-dcb3...  │ BKK Sports Club    │ courts   │
│ 9b3c1234-...                     │ Paw & Groom        │ grooming │
└──────────────────────────────────┴────────────────────┴──────────┘

bookings table:
┌──────────────────┬──────────────────────────────────┬──────────┬────────────────────┐
│ id               │ shop_id (FK → shops.id)          │ status   │ customer_name      │
├──────────────────┼──────────────────────────────────┼──────────┼────────────────────┤
│ 705f95b6-...     │ fa2fd810-04a8-41f7-bcc1-dcb3...  │ confirmed│ Test User          │
└──────────────────┴──────────────────────────────────┴──────────┴────────────────────┘

The shop_id in bookings REFERENCES the id in shops.
This is a FOREIGN KEY — it links rows in different tables.

Primary Keys and Foreign Keys

Primary Key (PK): A unique identifier for each row. In JongYang, every table uses a UUID as the PK.
Foreign Key (FK): A column that references the PK of another table. bookings.shop_id is a FK that points to shops.id. The database enforces this — you can't create a booking with a shop_id that doesn't exist in the shops table.

Why UUIDs Instead of Auto-Increment IDs?

Auto-increment IDs (1, 2, 3...) are simple, but have problems:

Predictable: If your last booking ID is 42, an attacker can guess other IDs and enumerate bookings
Collision risk: If you ever merge two databases, the IDs overlap
Order-dependent: All inserts go through a single counter; causes contention in distributed systems

UUIDs like 705f95b6-010f-4038-adf1-427f1e0f5527 are random 128-bit numbers. The chance of collision is astronomically small. You can generate them anywhere (no database round-trip), and they reveal no information about the count of records.

What Is an ORM?

ORM = Object Relational Mapper. It maps Python classes to database tables, so you work with Python objects instead of writing raw SQL strings.

Without ORM (raw SQL)	With SQLAlchemy ORM
`"SELECT * FROM bookings WHERE id = ?"`	`select(Booking).where(Booking.id == id)`
SQL injection risk if you concatenate strings	Parameters are always safely escaped
Returns raw tuples — you map them manually	Returns Python objects with attributes

SQLAlchemy 2.x Models

Each database table is defined as a Python class. Here's the Booking model from models/booking.py:

python — backend/app/models/booking.py

class Booking(Base):
    __tablename__ = "bookings"           # the actual DB table name
    __table_args__ = (
        CheckConstraint(                 # database-level enum validation
            "status IN ('pending_payment', 'confirmed', 'cancelled')",
            name="chk_booking_status",
        ),
    )

    # Mapped[T] says "this column holds type T"
    id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=uuid.uuid4)
    shop_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("shops.id"), nullable=False)
    resource_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("resources.id"), nullable=False)
    service_id: Mapped[uuid.UUID | None] = mapped_column(ForeignKey("services.id"))
    customer_name: Mapped[str] = mapped_column(String(200), nullable=False)
    customer_phone: Mapped[str] = mapped_column(String(20), nullable=False)
    customer_line_user_id: Mapped[str | None] = mapped_column(String(100))  # optional
    booking_date: Mapped[date] = mapped_column(Date, nullable=False)
    start_time: Mapped[time] = mapped_column(Time, nullable=False)
    end_time: Mapped[time] = mapped_column(Time, nullable=False)
    duration_min: Mapped[int] = mapped_column(SmallInteger, nullable=False)
    total_price: Mapped[float] = mapped_column(Numeric(10, 2), nullable=False)
    status: Mapped[str] = mapped_column(String(20), nullable=False, default="pending_payment")
    omise_charge_id: Mapped[str | None] = mapped_column(String(100))
    # reminder flags — prevent sending the same notification twice
    reminder_24h_sent: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    reminder_2h_sent: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    confirmed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    expires_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    cancelled_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    # Relationships — SQLAlchemy can load related objects automatically
    shop: Mapped["Shop"] = relationship(back_populates="bookings")
    resource: Mapped["Resource"] = relationship(back_populates="bookings")
    service: Mapped["Service | None"] = relationship(back_populates="bookings")

Key points:

Mapped[uuid.UUID] — SQLAlchemy 2.x uses Python type hints to declare column types
Mapped[str | None] — the | None means the column is nullable (can be NULL in the DB)
Numeric(10, 2) — money stored with exactly 2 decimal places (not float, which has rounding errors)
server_default=func.now() — the database itself sets created_at, not Python

The Many-to-Many Junction Table

A staff member (Resource) can offer many Services. A Service can be offered by many staff members. This is a Many-to-Many relationship, which requires a junction table:

python — backend/app/models/resource.py

# The junction table — just two columns, both are foreign keys
resource_services = Table(
    "resource_services",
    Base.metadata,
    Column("resource_id", ForeignKey("resources.id", ondelete="CASCADE"), primary_key=True),
    Column("service_id", ForeignKey("services.id", ondelete="CASCADE"), primary_key=True),
)
# ondelete="CASCADE" means: if a resource is deleted, also delete its rows in resource_services
# primary_key=True on both columns = composite primary key (the pair must be unique)

class Resource(Base):
    ...
    services: Mapped[list["Service"]] = relationship(
        secondary=resource_services,  # uses the junction table
        back_populates="resources"
    )

resources table          resource_services          services table
┌──────────┬────────┐   ┌────────────┬────────────┐   ┌──────────┬─────────────────┐
│ id       │ name   │   │resource_id │ service_id │   │ id       │ name_en         │
├──────────┼────────┤   ├────────────┼────────────┤   ├──────────┼─────────────────┤
│ staff_1  │Khun Nid│   │ staff_1    │ svc_bath   │   │ svc_bath │ Bath & Blow Dry │
│ staff_2  │Khun Toy│   │ staff_1    │ svc_groom  │   │ svc_groom│ Full Groom      │
└──────────┴────────┘   │ staff_2    │ svc_bath   │   └──────────┴─────────────────┘
                         │ staff_2    │ svc_groom  │
                         └────────────┴────────────┘
Both Khun Nid and Khun Toy offer both Bath and Full Groom.

Async Session and Queries

The database session is the connection handle for queries. From database.py:

python — backend/app/database.py

engine = create_async_engine(settings.DATABASE_URL, echo=False)
AsyncSessionLocal = async_sessionmaker(engine, expire_on_commit=False)

async def get_db() -> AsyncGenerator[AsyncSession, None]:
    async with AsyncSessionLocal() as session:
        yield session
        # session automatically closes when the context exits

Querying with the session:

python — query patterns used in the project

# SELECT one row or None
resource = (await session.execute(
    select(Resource).where(Resource.id == resource_id, Resource.is_active == True)
)).scalar_one_or_none()

# SELECT all matching rows
bookings = (await session.execute(
    select(Booking)
    .where(Booking.shop_id == admin.shop_id)
    .order_by(Booking.booking_date, Booking.start_time)
)).scalars().all()

# INSERT
session.add(booking)     # stages the insert
await session.commit()   # executes all staged changes in a transaction
await session.refresh(booking)  # re-reads the row to get server-generated values (like created_at)

# UPDATE
booking.status = "confirmed"
booking.confirmed_at = datetime.now(timezone.utc)
await session.commit()  # the UPDATE is sent to the DB here

flush() vs commit()

session.flush() — sends SQL to the database but does NOT commit the transaction. Useful when you need the auto-generated ID of a just-inserted row to create a related row, without fully committing yet.
session.commit() — commits the transaction. All changes become permanent. Other connections to the database can now see them.

In the seed script, flush() was used to get the shop's UUID before creating its ShopAdmin:

python — why flush() is needed

session.add(bkk)
await session.flush()    # sends INSERT, DB assigns bkk.id, but doesn't commit yet
# Now bkk.id is available:
session.add(ShopAdmin(shop_id=bkk.id, email="bkk@demo.com", ...))
# Later:
await session.commit()   # both bkk and its admin are committed together

🧠 Self-Check Quiz

1. What is a foreign key and what does the database do when you try to insert a booking with a shop_id that doesn't exist in the shops table?

A foreign key is a column that references the primary key of another table. If you try to insert a booking with a shop_id that doesn't exist in shops, the database rejects the INSERT with a foreign key constraint violation error. This prevents "orphan" records — bookings that reference non-existent shops.

2. Why does JongYang use UUID primary keys instead of auto-increment integers?

Three reasons: (1) Security — auto-increment IDs are predictable; users could enumerate all bookings by trying 1, 2, 3... UUIDs are random and reveal nothing about total record count. (2) Distributed safety — UUIDs can be generated anywhere without a database round-trip; auto-increments need the DB sequence. (3) Merge safety — if you ever merge databases, UUIDs don't collide.

3. What is the resource_services table and why does it have two primary key columns?

It's a junction table implementing a Many-to-Many relationship between resources (staff) and services. Two primary key columns form a "composite primary key" — the combination of (resource_id, service_id) must be unique. This prevents the same staff member from being linked to the same service twice. The table has no other columns — its entire purpose is to record which staff offer which services.

4. What's the difference between session.flush() and session.commit()?

flush() sends the SQL to the database but keeps the transaction open — the changes are visible within this session but not yet visible to other connections. Use it when you need a server-generated value (like an auto-UUID or server_default) before continuing. commit() finalizes the transaction — changes become permanent and visible to all connections. If you call commit() without flush(), SQLAlchemy automatically flushes first.

5. Why is total_price stored as Numeric(10, 2) in the database instead of Float?

Float uses binary floating-point arithmetic, which can't represent most decimal fractions exactly. For example, 0.1 + 0.2 = 0.30000000000000004 in float. Money requires exact decimal arithmetic. NUMERIC(10, 2) stores numbers with exactly 2 decimal places using base-10 arithmetic — 150.00 is always exactly 150.00. Pydantic's Decimal type in Python also avoids float arithmetic for the same reason.