On this page

SQLModel Database Setup: Engine, Tables, and Sessions

Published on August 10, 2026

Enrico Hochkoeppler Profile

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!

Buy Me a Coffee

Previous

Intro to SQLModel

Next

CRUD Operations with a Real Database