Getting Started with SQLModel for Python

SQLModel is a handy library that combines SQLAlchemy’s ORM with Pydantic’s data validation, making it easier to work with databases in Python.

With many other ORMs, you have to create separate models for validation and database operations. However, SQLModel lets you handle both in a single model, so you don’t have to manage duplicate code. This keeps your API and database in sync automatically.

In this tutorial, you’ll build a simple app using SQLModel and SQLite. We’ll walk through the basics and show how this library makes database work easier while keeping your code clean and reliable.

Prerequisites

Before starting this tutorial, ensure you have:

• Python 3.13 or higher installed (SQLModel requires a newer Python version for its type annotation features)
• Basic understanding of Python programming
• Familiarity with Python's type hints is beneficial but not required

Step 1 — Setting up your SQLModel project

In this section, you'll establish your project environment and install SQLModel, preparing everything needed to build a type-safe database application.

First, create a directory for the project and navigate into it:

mkdir sqlmodel-tutorial && cd sqlmodel-tutorial

Next, create a Python virtual environment to isolate your project dependencies:

python3 -m venv venv

Activate the virtual environment:

source venv/bin/activate

With your virtual environment active, install SQLModel using pip:

pip install sqlmodel

This single command installs SQLModel and its dependencies, including SQLAlchemy (for database operations) and Pydantic (for data validation). SQLModel acts as a unifying layer on top of these libraries, providing a streamlined interface for both.

For this tutorial, we'll use SQLite as our database engine. SQLite stores data in a single file and doesn't require a separate server process, making it ideal for learning and small applications.

Now that your environment is set up, you can start building with SQLModel.

Step 2 — Understanding SQLModel components and creating your first model

In this section, you'll learn the basics of SQLModel and create your first database model. SQLModel is designed to make working with databases easier while keeping your code safe and structured with Python’s type system.

Before we dive into the code, let’s go over some important parts of SQLModel:

• SQLModel: The base class for your models, combining Pydantic’s data validation with SQLAlchemy’s ORM features.
  
• Field: A function that defines model attributes, sets validation rules and database properties.
  
• Session: Manages database queries and transactions.
  
• create_engine: Sets up the database connection, just like in SQLAlchemy.
  
• select: Helps you write SQL SELECT queries in a type-safe way.
  

Create a database.py file in the root directory to set up the database connection:

from sqlmodel import SQLModel, create_engine

# SQLite connection string
sqlite_file_name = "library.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

# Create engine with SQL statement logging enabled
engine = create_engine(sqlite_url, echo=True)

# Function to create database tables from SQLModel classes
def create_db_and_tables():
    SQLModel.metadata.create_all(engine)

This script sets up the database connection. Unlike SQLAlchemy, which requires a separate base class, SQLModel includes this functionality by default.

The create_engine function connects to an SQLite database file named library.db, and with echo=True, it logs SQL queries so you can see what’s happening behind the scenes.

The create_db_and_tables function creates tables automatically based on your model definitions. SQLModel keeps track of all classes inherited from SQLModel with table=True, simplifying database management.

Now, let’s move forward by defining a model for books in a new file called models.py as part of our library management system:

from typing import Optional
from datetime import date
from sqlmodel import Field, SQLModel

class Book(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    title: str = Field(index=True)
    author: str
    pages: Optional[int] = None
    publication_date: Optional[date] = None
    price: Optional[float] = None

    def __repr__(self):
        return f"<Book(title='{self.title}', author='{self.author}')>"

The Book class showcases SQLModel’s unified approach, combining data validation, database mapping, and type hints in a single definition.

This means the same class serves as a Pydantic model for data validation, an SQLAlchemy ORM model that maps to a database table, and a structure with type hints for better IDE support and type checking.

Setting table=True tells SQLModel to map this class to a database table. Each attribute acts as both a Pydantic field with validation and a database column, ensuring consistency between the application logic and the database.

The type annotations play an important role:

• id: Optional[int] indicates an integer that can be None (before database insertion)
• title: str defines a required string field with an index for faster lookups
• author: str specifies a required string field
• Other fields are marked as Optional, meaning they can be null in the database

The Field function allows customization of Pydantic validation and SQLAlchemy column properties. For example, primary_key=True designates the id field as the table's primary key.

Next, set up the database tables by creating a new file named create_tables.py:

from database import create_db_and_tables
from models import Book  # Import Book to register it with SQLModel

def main():
    create_db_and_tables()
    print("Database tables created!")

if __name__ == "__main__":
    main()

Run this script to create the tables:

python create_tables.py

You should see output like:

2025-03-20 08:37:07,823 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-03-20 08:37:07,823 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("book")
2025-03-20 08:37:07,823 INFO sqlalchemy.engine.Engine [raw sql] ()
2025-03-20 08:37:07,823 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("book")
2025-03-20 08:37:07,823 INFO sqlalchemy.engine.Engine [raw sql] ()
2025-03-20 08:37:07,823 INFO sqlalchemy.engine.Engine 
CREATE TABLE book (
        id INTEGER NOT NULL, 
        title VARCHAR NOT NULL, 
        author VARCHAR NOT NULL, 
        pages INTEGER, 
        publication_date DATE, 
        price FLOAT, 
        PRIMARY KEY (id)
)


2025-03-20 08:37:07,824 INFO sqlalchemy.engine.Engine [no key 0.00003s] ()
2025-03-20 08:37:07,824 INFO sqlalchemy.engine.Engine CREATE INDEX ix_book_title ON book (title)
2025-03-20 08:37:07,824 INFO sqlalchemy.engine.Engine [no key 0.00003s] ()
2025-03-20 08:37:07,824 INFO sqlalchemy.engine.Engine COMMIT
Database tables created!

The output reveals SQLModel's operations. First, it checks if the table exists using a PRAGMA statement. Then, it creates a table with columns that match the model's attributes, including their types and constraints. Finally, it creates an index on the title column as specified.

Notice that SQLModel automatically converts Python types to appropriate SQLite types. For example, Optional[int] becomes INTEGER that allows NULL values, while str becomes VARCHAR NOT NULL.

With the database structure in place, you can start adding data.

Step 3 — Adding data to your database

In this section, you'll learn how to add and save records in your SQLite database using SQLModel’s type-safe API. SQLModel ensures that only valid data is stored by combining data validation with database operations.

Now, create a file named add_books.py to insert some sample book records:

from datetime import date
from sqlmodel import Session
from database import engine
from models import Book

def add_books():
    # Create book instances with type validation
    books = [
        Book(
            title="The Hobbit",
            author="J.R.R. Tolkien",
            pages=295,
            publication_date=date(1937, 9, 21),
            price=19.99
        ),
        Book(
            title="1984",
            author="George Orwell",
            pages=328,
            publication_date=date(1949, 6, 8),
            price=14.95
        ),
        Book(
            title="To Kill a Mockingbird",
            author="Harper Lee",
            pages=281,
            publication_date=date(1960, 7, 11),
            price=12.99
        )
    ]

    # Use context manager for automatic session cleanup
    with Session(engine) as session:
        # Add all book instances to the session
        for book in books:
            session.add(book)

        # Commit changes to the database
        session.commit()

        # Now books have IDs assigned by the database
        for book in books:
            print(f"Added: {book} with ID: {book.id}")

if __name__ == "__main__":
    add_books()

The process starts with creating Book instances. Since SQLModel integrates with Pydantic, these objects are automatically validated based on the type annotations in the model. If pages were set to a string or the required author field was missing, SQLModel would raise a validation error before attempting any database operation.

For managing database sessions, SQLModel follows Python’s context manager pattern (with Session(engine) as session). This ensures the session closes automatically when the block exits, preventing resource leaks, even if an error occurs.

Within the session block, each book is added using session.add(book). SQLModel tracks the objects at this stage but does not immediately insert them into the database. The actual SQL INSERT operations are executed only when session.commit() is called, making the changes permanent.

After committing, SQLModel updates the Python objects with database-generated values, such as the id field. These objects remain fully typed and can still be used in the application.

Run this script to add the books:

python add_books.py

You should see output like:

2025-03-20 09:00:35,185 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-03-20 09:00:35,186 INFO sqlalchemy.engine.Engine INSERT INTO book (title, author, pages, publication_date, price) VALUES (?, ?, ?, ?, ?) RETURNING id
2025-03-20 09:00:35,186 INFO sqlalchemy.engine.Engine [generated in 0.00008s (insertmanyvalues) 1/3 (ordered; batch not supported)] ('The Hobbit', 'J.R.R. Tolkien', 295, '1937-09-21', 19.99)
2025-03-20 09:00:35,187 INFO sqlalchemy.engine.Engine INSERT INTO book (title, author, pages, publication_date, price) VALUES (?, ?, ?, ?, ?) RETURNING id
2025-03-20 09:00:35,187 INFO sqlalchemy.engine.Engine [insertmanyvalues 2/3 (ordered; batch not supported)] ('1984', 'George Orwell', 328, '1949-06-08', 14.95)
2025-03-20 09:00:35,187 INFO sqlalchemy.engine.Engine INSERT INTO book (title, author, pages, publication_date, price) VALUES (?, ?, ?, ?, ?) RETURNING id
2025-03-20 09:00:35,187 INFO sqlalchemy.engine.Engine [insertmanyvalues 3/3 (ordered; batch not supported)] ('To Kill a Mockingbird', 'Harper Lee', 281, '1960-07-11', 12.99)
2025-03-20 09:00:35,187 INFO sqlalchemy.engine.Engine COMMIT
2025-03-20 09:00:35,188 INFO sqlalchemy.engine.Engine BEGIN (implicit)
...
Added:  with ID: 3
2025-03-20 09:00:35,192 INFO sqlalchemy.engine.Engine ROLLBACK

The logged SQL statements show how SQLModel translates your Python objects into database operations. It generates parameterized INSERT statements with the appropriate values for each book, protecting against SQL injection vulnerabilities.

With data successfully stored, you can retrieve and query this information.

Step 4 — Querying data from your database

This section covers how to query data using SQLModel, providing a type-safe way to fetch and filter records from your SQLite database. SQLModel’s query system allows you to write queries using familiar Python syntax while efficiently generating SQL behind the scenes.

Create a file named query_books.py to retrieve books from the database.

from sqlmodel import Session, select
from database import engine
from models import Book

def query_books():
    # Create a session using a context manager
    with Session(engine) as session:
        # Build a query using the select function
        print("==== All Books ====")
        statement = select(Book)
        books = session.exec(statement).all()
        for book in books:
            print(f"{book.title} by {book.author}, {book.pages} pages, ${book.price:.2f}")

if __name__ == "__main__":
    query_books()

SQLModel introduces a functional approach to querying with the select() function, which constructs a SQL SELECT statement.

The select(Book) statement builds a query for all records from the book table. You use session.exec(statement) to execute this query. The exec() method is an SQLModel enhancement that returns type-aware results based on your model definitions.

The .all() method collects all matching records into a list of Book objects. These fully-typed Python instances allow IDE autocompletion and type-checking for all model attributes.

Run this script to see all the books:

python query_books.py

You should see output like:

==== All Books ====
2025-03-20 09:03:46,761 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-03-20 09:03:46,762 INFO sqlalchemy.engine.Engine SELECT book.id, book.title, book.author, book.pages, book.publication_date, book.price 
FROM book
2025-03-20 09:03:46,762 INFO sqlalchemy.engine.Engine [generated in 0.00007s] ()
The Hobbit by J.R.R. Tolkien, 295 pages, $19.99
1984 by George Orwell, 328 pages, $14.95
To Kill a Mockingbird by Harper Lee, 281 pages, $12.99
2025-03-20 09:03:46,763 INFO sqlalchemy.engine.Engine ROLLBACK

The SQL logging shows that SQLModel generates a clean SELECT statement that retrieves all columns from the book table.

To filter data based on conditions, you can use the where() method with SQLModel's type-safe operators:

# Query books by a specific author
print("\n==== Tolkien Books ====")
statement = select(Book).where(Book.author == "J.R.R. Tolkien")
tolkien_books = session.exec(statement).all()
for book in tolkien_books:
    print(f"{book.title}, published on {book.publication_date}")

# Query books with more than 300 pages
print("\n==== Long Books ====")
statement = select(Book).where(Book.pages > 300)
long_books = session.exec(statement).all()
for book in long_books:
    print(f"{book.title}: {book.pages} pages")

The where() method applies conditions to your query. SQLModel translates Python comparison operators into SQL conditions. For example, Book.author == "J.R.R. Tolkien" becomes a SQL WHERE clause. Because these conditions use your model's typed attributes, you get compile-time type checking and IDE autocompletion.

To sort results, use the order_by() method:

# Order books by price (cheapest first)
print("\n==== Books by Price (Ascending) ====")
statement = select(Book).order_by(Book.price)
ordered_books = session.exec(statement).all()
for book in ordered_books:
    print(f"{book.title}: ${book.price:.2f}")

# Order by price in descending order (most expensive first)
print("\n==== Books by Price (Descending) ====")
statement = select(Book).order_by(Book.price.desc())
expensive_first = session.exec(statement).all()
for book in expensive_first:
    print(f"{book.title}: ${book.price:.2f}")

For retrieving single records, SQLModel offers convenient methods like first() and direct lookup by primary key:

# Get the first book
statement = select(Book)
first_book = session.exec(statement).first()
print(f"\nFirst book: {first_book.title}")

# Get a specific book by ID using the session.get() shortcut
book_by_id = session.get(Book, 2)  # Gets book with ID 2
print(f"Book with ID 2: {book_by_id.title}")

The session.get() method in SQLModel provides a simple way to fetch a record using its primary key. This approach is more direct and readable than manually writing a SELECT query with a filter condition.

SQLModel’s query system balances Python’s simplicity with SQL’s efficiency. The context manager ensures the session closes properly, even if an error occurs during query execution.

Now that you can retrieve data, let’s update existing records.

Step 5 — Updating records in your database

This section covers how to modify existing data in your SQLite database using SQLModel. The update process leverages Pydantic’s validation and SQLAlchemy’s session management to ensure that only valid changes are saved.

To begin, create a file named update_books.py to explore different ways to update records.

from sqlmodel import Session, select
from database import engine
from models import Book

def update_books():
    # Open a session using a context manager
    with Session(engine) as session:
        # Find a book by title
        statement = select(Book).where(Book.title == "1984")
        book = session.exec(statement).first()

        print("=== Before update ===")
        print(f"{book.title} current price: ${book.price:.2f}")

        # Modify the book's price - change is tracked by the session
        book.price = 16.99

        # Tell the session to update this object
        session.add(book)

        # Commit changes to the database
        session.commit()

        # Retrieve the updated book to verify changes
        updated_statement = select(Book).where(Book.title == "1984")
        updated_book = session.exec(updated_statement).first()
        print(f"=== After update ===")
        print(f"{updated_book.title} new price: ${updated_book.price:.2f}")

if __name__ == "__main__":
    update_books()

SQLModel updates a record by first retrieving it with a select query, such as where(Book.title == "1984"). Once fetched, the book is a Python object that you can modify directly, like changing price to 16.99.

To save the changes, you call session.add(book), which marks it as modified. Then, session.commit() executes the SQL UPDATE statement. SQLModel enforces type safety, preventing invalid data from being stored.

Run the script to apply the update and modify the book's record in the database:

python update_books.py

You should see output like:

=== Before update ===
1984 current price: $14.95
...
UPDATE book SET price=? WHERE book.id = ?
...
COMMIT
...
=== After update ===
1984 new price: $16.99
...

The logged SQL confirms that SQLModel generates a precise UPDATE statement, modifying only the changed field price for the specific book.

You can modify multiple attributes in a single operation:

# Find a book to update
statement = select(Book).where(Book.title == "The Hobbit")
book = session.exec(statement).first()

# Update multiple attributes with type checking
book.pages = 300
book.price = 21.99
book.author = "J.R.R. Tolkien (Revised Edition)"

# Persist all changes with a single commit
session.add(book)
session.commit()

Each attribute change is type-checked against your model definition. SQLModel will generate a single UPDATE statement that modifies all changed attributes, optimizing database performance.

For batch updates to multiple records, you can query for a set of objects and modify each one:

# Find books with low page count
statement = select(Book).where(Book.pages < 300)
short_books = session.exec(statement).all()

# Mark each as "quick reads"
for book in short_books:
    book.title = f"{book.title} (Quick Read)"
    session.add(book)

# Commit all changes at once
session.commit()

For more complex bulk updates, SQLModel allows you to use SQLAlchemy's lower-level API:

from sqlalchemy import update

# Bulk update - increase all book prices by 10%
statement = update(Book).values(price=Book.price * 1.1)
result = session.exec(statement)
session.commit()
print(f"Updated prices for {result.rowcount} books")

This bypasses object loading for better performance when updating many records at once.

Now that you've learned how to update records, it's time to explore how to delete them from the database.

Step 6 — Deleting records from your database

This section covers how to remove data from your SQLite database using SQLModel. You can delete records individually or perform bulk deletions using SQL conditions.

Now, create a file named delete_books.py to demonstrate different deletion methods:

from sqlmodel import Session, select
from database import engine
from models import Book

def delete_books():
    # Open a database session
    with Session(engine) as session:
        # Count total books before deletion
        statement = select(Book)
        books = session.exec(statement).all()
        count_before = len(books)
        print(f"Total books before deletion: {count_before}")

        # Find a specific book by title
        statement = select(Book).where(Book.title == "To Kill a Mockingbird")
        book_to_delete = session.exec(statement).first()

        if book_to_delete:
            print(f"Found book to delete: {book_to_delete.title} by {book_to_delete.author}")
            # Mark the book for deletion
            session.delete(book_to_delete)
            # Execute the deletion
            session.commit()
            print(f"Book deleted successfully")
        else:
            print("Book not found")

        # Verify deletion by counting remaining books
        statement = select(Book)
        remaining = session.exec(statement).all()
        count_after = len(remaining)
        print(f"Total books after deletion: {count_after}")

        # List remaining books
        print("\nRemaining books:")
        for book in remaining:
            print(f"- {book.title} by {book.author}")

if __name__ == "__main__":
    delete_books()

The deletion process begins by finding the record you want to remove. Using select(Book).where(Book.title == "To Kill a Mockingbird"), you locate the specific book in the database.

Once you have the book object, you call session.delete(book_to_delete) to mark it for deletion. This doesn't immediately remove the record from the database. Instead, it tells SQLModel to generate a DELETE statement when you commit the session.

The deletion happens when you call session.commit(), which executes the DELETE statement with the appropriate WHERE clause to target only the specified book.

After committing, you verify the deletion by counting and listing the remaining books, confirming that "To Kill a Mockingbird" is no longer in the database.

Run this script:

python delete_books.py

You should see output like:

Total books before deletion: 3
...
Found book to delete: To Kill a Mockingbird by Harper Lee
...
DELETE FROM book WHERE book.id = ?
...
COMMIT
Book deleted successfully
...
Total books after deletion: 2
...
Remaining books:
- The Hobbit by J.R.R. Tolkien
- 1984 by George Orwell
...

The logged SQL shows that SQLModel generates a targeted DELETE statement with a WHERE clause that precisely identifies the book to remove by its primary key.

For cases where you know the primary key directly, SQLModel provides a more concise approach:

# Delete a book by primary key
book_id = 2  # ID of the book to delete
book = session.get(Book, book_id)
if book:
    session.delete(book)
    session.commit()
    print(f"Deleted book with ID {book_id}")

The session.get() method efficiently fetches a record by its primary key, which you can then delete using the standard session.delete() method.

For bulk deletions that match specific criteria, SQLModel allows you to use SQLAlchemy's delete operation:

from sqlalchemy import delete

# Perform a bulk deletion without loading objects
statement = delete(Book).where(Book.price < 15.00)
result = session.exec(statement)
session.commit()
print(f"Deleted {result.rowcount} inexpensive books")

This method deletes records directly in the database without loading them into Python memory, making it efficient for large-scale deletions. The WHERE clause ensures that only the specified records are removed.

With these deletion techniques, you've completed the fundamental CRUD operations in SQLModel.

Final thoughts

This tutorial showed how SQLModel combines SQLAlchemy for database operations and Pydantic for data validation into a straightforward solution. Unifying data models removes duplication and keeps your API and database in sync.

SQLModel’s type safety helps catch errors early, making applications more reliable and improving the coding experience with autocompletion and static analysis.

Check out the official documentation to learn more. With its modern design, SQLModel makes working with Python databases more manageable and efficient.

Article by

Stanley Ulili

Stanley Ulili is a technical educator at Better Stack based in Malawi. He specializes in backend development and has freelanced for platforms like DigitalOcean, LogRocket, and AppSignal. Stanley is passionate about making complex topics accessible to developers.

Got an article suggestion? Let us know

Next article

Getting Started with Peewee ORM for Python

Learn how to use Peewee, a lightweight and powerful Python ORM, to manage SQLite databases with minimal code. This step-by-step tutorial covers setting up Peewee, defining models, performing CRUD operations, and efficiently handling database queries.

→

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.