Creating models and tables¶

🎯 What You’ll Learn¶

How to initialize a SQLite database
How to create tables programmatically
How to verify your schema and prepare for CRUD operations

🗃️ Step 1: Set Up the Database Engine¶

Create a new file to manage your database connection.

📄 db.py

from sqlmodel import SQLModel, create_engine

sqlite_url = "sqlite:///./tasks.db"
engine = create_engine(sqlite_url, echo=True)

sqlite:///./tasks.db creates a local SQLite file
echo=True logs SQL statements for debugging

🏗️ Step 2: Create Tables Programmatically¶

Now let’s create the tables based on your model.

📄 main.py

from fastapi import FastAPI
from db import engine
from sqlmodel import SQLModel
from routers import tasks

app = FastAPI()

@app.on_event("startup")
def on_startup():
    SQLModel.metadata.create_all(engine)

app.include_router(tasks.router)

🔍 What’s Happening¶

SQLModel.metadata.create_all(engine) scans all models and creates tables
This runs once when the app starts
You don’t need to write raw SQL — SQLModel handles it

🧪 Step 3: Verify Table Creation¶

Run your app:

uvicorn main:app --reload

Check your project folder — you’ll see a new file: tasks.db

You can inspect it using any SQLite viewer (e.g., DB Browser for SQLite) or CLI:

sqlite3 tasks.db
.tables

You should see:

Task

🧠 Recap¶

You now have:

A SQLModel class that defines your schema
A SQLite database file
Tables created automatically on startup

This sets the foundation for real CRUD operations using SQLModel sessions, which we’ll tackle next.

🧪 Practice Challenge¶

Add a new model:

User with fields: id, username, email, created_at
Create a separate models/user.py file
Register it in main.py so its table is created on startup