- Courses
- 📚Libraries & Frameworks
- ⚡FastAPI
- Intermediate API Design
- Database Integration
- Creating Models and Tables
On this page
SQLModel Database Setup: Engine, Tables, and Sessions
Published on August 10, 2026
By Enrico Hochkoeppler
Web Developer & TutoriaLibre Founder
Setting up a SQLModel database in FastAPI comes down to three things: an engine that connects to the database file, a call to SQLModel.metadata.create_all() that creates the tables, and a session dependency that routes use to perform queries. This article works through all three in full, explains the mechanics behind each step, and documents the most common mistake developers run into with SQLModel — a silent import-order bug that prevents tables from being created with no error message at all.
What Happens When a Database Gets Set Up
It helps to see the full sequence before writing any code. Model classes get defined first, as plain SQLModel classes marked with table=True. The moment Python imports those classes, they register themselves in SQLModel.metadata. An engine gets created next — an object holding the configuration needed to connect to a specific database. Calling SQLModel.metadata.create_all(engine) then reads everything currently registered in metadata and issues CREATE TABLE IF NOT EXISTS for each one, which is what actually creates the database file and its tables on disk. From that point on, FastAPI creates a short-lived session for every incoming request — a workspace for that request's database operations — and discards it once the request finishes.
The registration step is the one worth understanding carefully: if a model class hasn't been imported anywhere by the time create_all() runs, SQLModel.metadata is simply empty at that moment, and no tables get created — silently, with no error raised anywhere.
Project File Structure
project/
├── main.py ← FastAPI app, lifespan, router registration
├── db.py ← engine, create_db_and_tables(), get_session()
├── models/
│ ├── task.py ← Task model (table=True)
│ └── user.py ← User model
└── routers/
└── tasks.py ← route handlers
Creating db.py: Engine and Session
from sqlmodel import SQLModel, Session, create_engine
DATABASE_URL = "sqlite:///database.db"
engine = create_engine(
DATABASE_URL,
connect_args={"check_same_thread": False},
echo=False,
)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def get_session():
with Session(engine) as session:
yield session
The connection string
"sqlite:///database.db" is the database URL, and its structure carries two pieces of information: sqlite:// is the protocol, telling SQLAlchemy which database driver to use, and /database.db is the path to the file, relative to the directory the application is started from. SQLite only creates that file on disk the first time create_all() actually runs — the engine object alone, on its own, never touches the disk. For an in-memory database that resets every time the application restarts, useful for testing, the standard connection string is "sqlite:///:memory:". Moving to PostgreSQL later requires changing only this one string:
DATABASE_URL = "postgresql://user:password@localhost:5432/mydb"
Everything else — the engine, the session, the models, the queries — stays identical.
connect_args={"check_same_thread": False}
This is a SQLite-specific setting. By default, SQLite raises an error if the same connection is accessed from a different thread than the one that created it, and FastAPI processes requests across multiple threads, which triggers exactly that error. Setting check_same_thread to False disables the restriction; thread safety is still handled correctly by SQLAlchemy's own session management regardless of this setting. Omitting this argument with SQLite produces ProgrammingError: SQLite objects created in a thread can only be used in that same thread the moment the application handles concurrent requests.
echo=False
When echo=True, SQLAlchemy prints every SQL statement it executes — SELECT, INSERT, CREATE TABLE — directly to the terminal, which is genuinely useful while learning or debugging, since it shows exactly what SQL is being generated for each operation. Setting it to False (or omitting it, since False is the default) before deploying avoids the log noise and minor performance overhead that comes from logging every single statement in production.
create_db_and_tables()
Wrapping SQLModel.metadata.create_all(engine) in a named function rather than calling it directly at module level matters for two reasons. First, a module-level call runs every single time the file is imported anywhere in the project — importing db.py from a test file would immediately attempt to create the database, which isn't what a test run wants. A function call only happens when it's explicitly invoked. Second, this function gets called from the lifespan context manager in main.py, which gives precise control over exactly when table creation happens: once, at startup, before any request is handled.
get_session()
get_session() is a FastAPI dependency — a generator function creating a database Session for each incoming request. The with Session(engine) as session: block guarantees the session closes cleanly once the request finishes, even if an exception is raised partway through it, and yield session is what makes this a generator, handing the session directly to the route handler that depends on it. One session belongs to one request — a session should never be stored in a global variable or shared between requests.
The Import Order Caveat: The Most Common SQLModel Bug
SQLModel.metadata is a registry. Every time Python executes a class MyModel(SQLModel, table=True): statement, that class registers itself in SQLModel.metadata automatically. When create_all() runs, it reads whatever is currently in that registry and creates tables only for those classes — nothing more.
The consequence is worth stating plainly: if a model class hasn't been imported by the time create_all() runs, metadata is empty for that class specifically, and its table never gets created — with no error, no warning, and no indication anything went wrong. This is the single most common SQLModel bug, and the hardest to diagnose precisely because it fails silently rather than raising anything.
The version that fails silently:
# main.py — WRONG
from sqlmodel import SQLModel
from db import engine
# SQLModel.metadata is empty here — Task and User have never been imported
SQLModel.metadata.create_all(engine) # Creates zero tables silently
The version that works correctly:
# main.py — CORRECT
from sqlmodel import SQLModel
from db import engine
from models.task import Task # This import registers Task in SQLModel.metadata
from models.user import User # This import registers User in SQLModel.metadata
SQLModel.metadata.create_all(engine) # Now creates both tables correctly
The import doesn't need to reference the class name anywhere else in the file — even an import that's never explicitly used still executes the class definition and registers the model, since that registration happens as a side effect of the class statement running, not of the class being referenced afterward. Importing the classes explicitly and visibly, as shown above, is the cleaner habit regardless, since it makes the dependency obvious to anyone reading the file. Importing models from inside db.py instead, so any file importing db.py triggers registration automatically, also works, but it's a pattern that can introduce circular import issues once a project grows — the explicit import directly in main.py is simpler to reason about.
Wiring Everything Into main.py
from contextlib import asynccontextmanager
from fastapi import FastAPI
from db import create_db_and_tables
from models.task import Task # Must be imported before create_db_and_tables() runs
from routers.tasks import router as tasks_router
@asynccontextmanager
async def lifespan(app: FastAPI):
# Runs once at startup — creates tables for all imported models
create_db_and_tables()
yield
# Runs once at shutdown — add resource cleanup here if needed
app = FastAPI(lifespan=lifespan)
app.include_router(tasks_router)
The model import happens at module load time, before lifespan ever runs. By the time create_db_and_tables() executes inside lifespan, Task is already registered in SQLModel.metadata, and its table gets created correctly. This is exactly why the import needs to sit in main.py — or anywhere else that executes before lifespan does — rather than being deferred to inside the lifespan function itself.
How SQLModel.metadata Works
SQLModel.metadata is an instance of SQLAlchemy's MetaData class — a single class-level attribute shared across every SQLModel subclass in the application, not one instance per model. Writing:
class Task(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
title: str
triggers SQLModel's metaclass to register Task in SQLModel.metadata at class definition time — by the time this class statement finishes executing, its table definition already exists in the registry. create_all(engine) then iterates over everything currently registered and, for each model, checks whether a table with that name already exists in the database: if it doesn't, it generates and executes the CREATE TABLE statement; if it does, it skips that model silently — create_all() never drops or alters an existing table. This makes it safe to call on every startup: nothing happens to tables that already exist, and tables for any newly added models get created without disturbing what's already there.
This safety has a real limit worth being precise about: create_all() only creates tables that don't yet exist — it never modifies the structure of a table that's already there. Adding a new column to an existing model and restarting the app does not add that column to the existing database table, since create_all() sees the table already exists and skips it entirely. Managing schema changes after the initial deployment is what a migration tool like Alembic — built for SQLAlchemy and fully compatible with SQLModel — is for.
Column Types: What the Generated SQL Looks Like
Running the application with echo=True prints the actual CREATE TABLE statement SQLAlchemy generates. For the Task model, it looks like this:
CREATE TABLE task (
id INTEGER,
title VARCHAR NOT NULL,
description VARCHAR,
completed INTEGER NOT NULL,
PRIMARY KEY (id)
)
A few details here are worth understanding rather than taking for granted. SQLAlchemy generates VARCHAR for str fields because it's the type compatible with the widest range of databases; SQLite internally treats VARCHAR and TEXT identically, while PostgreSQL and MySQL use VARCHAR natively — this dialect difference is handled automatically, with nothing to configure. SQLite has no native boolean storage type, so SQLAlchemy maps a Python bool field to INTEGER, storing 1 for True and 0 for False; SQLModel and Pydantic handle that conversion transparently on the Python side, so application code only ever sees True and False. A field without | None in its annotation maps to a NOT NULL column, while a field with | None maps to a nullable one — title: str becomes VARCHAR NOT NULL, and description: str | None becomes a plain, nullable VARCHAR. Finally, primary_key=True in Field() is what generates the PRIMARY KEY constraint, which SQLite then uses to auto-increment id whenever a new row is inserted with id left as None.
Verifying the Database
fastapi dev main.py
Checking the project directory afterward shows database.db now exists. Confirming the table was actually created can be done directly from the SQLite command-line tool:
sqlite3 database.db
.tables
-- Output: task
.schema task
-- Output: CREATE TABLE task (...)
.quit
Alternatively, opening database.db with DB Browser for SQLite — a free visual tool — shows the same information without the command line: its "Database Structure" tab lists every table and column, and its "Browse Data" tab shows the actual rows.
Using the Session in Route Handlers
With get_session() defined in db.py, any route handler can request a database session through Depends:
from fastapi import APIRouter, Depends
from sqlmodel import Session, select
from db import get_session
from models.task import Task
router = APIRouter(prefix="/tasks", tags=["Tasks"])
@router.post("/", status_code=201)
def create_task(task: Task, session: Session = Depends(get_session)):
session.add(task)
session.commit()
session.refresh(task)
return task
@router.get("/")
def get_tasks(session: Session = Depends(get_session)):
return session.exec(select(Task)).all()
Depends(get_session) tells FastAPI to call get_session() and inject the yielded Session object before the route handler runs. Once the handler returns, FastAPI resumes get_session() right after its yield, which exits the with block and closes the session — cleanup that happens automatically, without either route needing to manage it directly.
Common Mistakes to Avoid
Calling create_db_and_tables() before importing the relevant model classes is the silent killer covered above — SQLModel.metadata ends up empty, no tables get created, and nothing raises an error to signal it. Every model class needs to be imported somewhere that runs before create_all() does. Omitting connect_args={"check_same_thread": False} with SQLite raises a ProgrammingError the moment concurrent requests reach a database-using route, since FastAPI handles requests across threads by default. Leaving echo=True set in production produces a log line for every single SQL statement — every SELECT, every INSERT, every UPDATE — creating significant log volume and measurable overhead on a busy application. Expecting create_all() to update an existing table's structure doesn't hold: adding a new field to a model and restarting does nothing to a table that already exists, since create_all() skips any table it already finds — schema changes after deployment need a migration tool like Alembic instead. Creating a new engine per request is a different mistake from mismanaging the session: the engine is deliberately expensive to create and designed to be shared across the whole application, created once at module level and reused everywhere — it's the session, not the engine, that needs to be scoped per request.
Practice: Adding a User Model
models/user.py:
from sqlmodel import SQLModel, Field
from datetime import datetime, timezone
class User(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
username: str = Field(index=True, unique=True)
email: str
created_at: datetime = Field(default_factory=lambda: datetime.now(timezone.utc))
Importing it in main.py alongside the existing Task import — before create_db_and_tables() runs inside lifespan — and restarting the application creates both tables together. Running sqlite3 database.db followed by .tables afterward confirms both task and user now exist. Field(index=True) on username generates a database index on that column, keeping lookups by username fast even once the table holds millions of rows. Field(unique=True) adds a UNIQUE constraint at the database level, preventing duplicate usernames from being inserted at all, independent of any checks the application code might also perform.
Key Takeaways
A SQLModel database comes together from three pieces working in a specific order: model classes that register themselves in SQLModel.metadata the moment they're imported, an engine created once and shared across the whole application, and a session created fresh for every request and discarded once that request finishes. The single most consequential detail in that sequence is import order — create_all() can only create tables for models that have already been imported somewhere upstream of it, and skipping that step fails with no error at all rather than an obvious one. create_all() is safe to call on every startup precisely because it only creates missing tables and never touches or modifies ones that already exist, which is also exactly why schema changes to an existing table need a real migration tool rather than another call to create_all().
Frequently Asked Questions
What three things are required to set up a SQLModel database in FastAPI?
Setting up a SQLModel database in FastAPI requires an engine that connects to the database file, a call to SQLModel.metadata.create_all() to create the tables, and a session dependency for executing route queries.
What is the silent import order bug in SQLModel?
The silent import order bug occurs if model classes are not imported before SQLModel.metadata.create_all(engine) runs. Because SQLModel.metadata is empty until models are executed, no tables will be created, and it fails silently without throwing any errors or warnings.
Why should SQLModel.metadata.create_all() be called inside a lifespan context manager instead of at the module level?
Calling it at the module level executes the code immediately upon import, which can cause unwanted table creation when running tests. Wrapping it in a lifespan function ensures table creation happens exactly once at application startup before requests are handled.
What is the purpose of adding connect_args={'check_same_thread': False} when using SQLite with FastAPI?
By default, SQLite restricts access to a single thread. Since FastAPI processes concurrent requests across multiple threads, omitting this setting will cause a ProgrammingError. Disabling it allows safe multi-threaded database access, as SQLAlchemy manages session thread-safety.
What happens when echo=True is set in create_engine()?
When echo=True, SQLAlchemy prints every executed SQL statement to the terminal, making it highly useful for development and debugging. However, it should be set to False or omitted in production to avoid log noise and performance overhead.
Does SQLModel.metadata.create_all() modify or update existing table schemas?
No, create_all() only checks for and creates missing tables; it never drops or modifies existing table structures. If you add a new column to an existing model, create_all() will skip it silently, and you must use a migration tool like Alembic to update the schema.
How are Python bool types mapped in a SQLite database via SQLModel?
Since many databases (including simpler forms of SQLite) do not have a native boolean type, SQLAlchemy maps Python bool types to an INTEGER column, storing 1 for True and 0 for False. Pydantic and SQLModel handle this conversion transparently back into Python.
How should database sessions be managed across incoming requests in FastAPI?
Database sessions should be generated per request using a dependency like get_session() combined with a 'with Session(engine)' context manager to ensure clean closure. A session must never be stored in global variables or shared across different requests.
Found this helpful? Share the knowledge...
... or support this project to keep it alive!
Previous
Intro to SQLModel
Next
CRUD Operations with a Real Database
-
What Happens When a Database Gets Set Up
-
Project File Structure
-
Creating db.py: Engine and Session
- The connection string
connect_args={"check_same_thread": False}echo=Falsecreate_db_and_tables()get_session()
-
The Import Order Caveat: The Most Common SQLModel Bug
-
Wiring Everything Into main.py
-
How SQLModel.metadata Works
-
Column Types: What the Generated SQL Looks Like
-
Verifying the Database
-
Using the Session in Route Handlers
-
Common Mistakes to Avoid
-
Practice: Adding a User Model
-
Key Takeaways
-
Frequently Asked Questions
