Data Models & Schema: Defining Your Database Structure with SQLAlchemy

#backenddevelopment #python #database

What is a Data Model?

A data model defines the structure of your data before
it ever touches a database. Think of it as a blueprint :
every record must follow this structure, ensuring consistency.

Setting Up SQLAlchemy with SQLite

SQLAlchemy is Python's most popular ORM (Object-Relational Mapper).
It lets you define database tables as Python classes instead
of writing raw SQL.

from sqlalchemy import create_engine, Column, Integer, String, Text, Numeric, DateTime
from sqlalchemy.orm import declarative_base

engine = create_engine("sqlite:///items.db", echo=True)
Base = declarative_base()

Defining the Item Model

class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False)
    description = Column(Text)
    price = Column(Numeric(10, 2), nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

Each field maps directly to a database column:

id -> Primary Key, auto-incremented
name -> Required string
description -> Long text field
price -> Decimal with 2 decimal places
created_at -> Timestamp, auto-set on creation

Creating the Table & Inserting Records

Base.metadata.create_all(engine) creates the table automatically.
No manual SQL needed.

Then I inserted 5 sample records using SQLAlchemy's session:

db.add_all(items)
db.commit()

Proof it Works

Lessons Learned

ORMs abstract away raw SQL, but you still need to understand
what's happening underneath. The model is the contract between
your application and your database; get it right from the start.

Day 6 done. 24 more to go.