SQLModel: Simplifying SQL Databases in Python with Pydantic and SQLAlchemy

Introduction

SQLModel is a powerful Python library for interacting with SQL databases, designed for simplicity, compatibility, and robustness. It builds upon the strengths of Pydantic for data validation and SQLAlchemy for database interaction, offering a seamless experience for developers. Created by the author of FastAPI, SQLModel is particularly well-suited for FastAPI applications, aiming to reduce code duplication and enhance developer experience.

Key features of SQLModel include:

• Intuitive to write: Excellent editor support with autocompletion everywhere, reducing debugging time and making it easy to learn.
• Easy to use: Sensible defaults simplify the code you write, handling much of the underlying complexity.
• Compatible: Designed for high compatibility with FastAPI, Pydantic, and SQLAlchemy.
• Extensible: Provides access to the full power of SQLAlchemy and Pydantic when needed.
• Short: Minimizes code duplication, allowing a single type annotation to perform extensive work without needing separate SQLAlchemy and Pydantic models.

Installation

To get started with SQLModel, ensure you have a virtual environment set up. You can install it using pip:

pip install sqlmodel

Examples

SQLModel simplifies common database operations. Here are some quick examples to illustrate its usage.

Create a SQLModel Model

Define your database table structure using a Python class that inherits from SQLModel and sets table=True. Each class attribute corresponds to a table column.

from sqlmodel import Field, SQLModel


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: int | None = None

Create Rows

Create instances of your SQLModel class to represent rows in your table.

hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)

Write to the Database

Combine your model definitions and instances with an engine and session to persist data to a database. This example uses SQLite.

from sqlmodel import Field, Session, SQLModel, create_engine


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: int | None = None


hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)


engine = create_engine("sqlite:///database.db")


SQLModel.metadata.create_all(engine);

with Session(engine) as session:
    session.add(hero_1)
    session.add(hero_2)
    session.add(hero_3)
    session.commit()

Select from the Database

Query data from your database using the select function and session. SQLModel ensures you retain excellent editor support even after retrieving data.

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: int | None = None


engine = create_engine("sqlite:///database.db")

with Session(engine) as session:
    statement = select(Hero).where(Hero.name == "Spider-Boy")
    hero = session.exec(statement).first()
    print(hero)

Why Use SQLModel

SQLModel offers several compelling reasons for Python developers, especially those working with FastAPI:

• Unified Models: It acts as both a SQLAlchemy model and a Pydantic model, eliminating the need to define separate models for database interaction and data validation/serialization. This significantly reduces code duplication.
• Exceptional Developer Experience: Leveraging Python type annotations, SQLModel provides excellent editor support, including autocompletion and inline error checking, both when defining models and when querying data.
• FastAPI Integration: Designed by the creator of FastAPI, SQLModel provides a natural and highly compatible way to integrate SQL databases into FastAPI applications.
• Robust and Extensible: While simplifying common tasks, it retains the full power and extensibility of SQLAlchemy and Pydantic underneath, allowing for complex scenarios when needed.

Links

For more detailed information and comprehensive guides, refer to the official documentation and source code:

• Official Documentation
• Source Code on GitHub