SQLModel: Pydantic-based ORM for Python

Introduction

SQLAlchemy and Pydantic are two powerful tools in a Python developer's toolkit: one for working with databases, the other for data validation. However, there has always been a gap between them. The solution to this problem is SQLModel – a Pydantic-based ORM that brings together the best of both worlds.

SQLModel lets you use typed Pydantic models as SQLAlchemy ORM tables. This simplifies development, speeds up API creation, and eliminates code duplication between database models and serializers.

What is SQLModel and Why You Need It

SQLModel is a modern ORM library for Python, created by Sebastián Ramírez (the creator of FastAPI) in 2021. It solves one of the biggest challenges in web development — code duplication between database models and data validation schemas.

Key Benefits of SQLModel

SQLModel provides developers with a unified interface for working with databases and data validation. Key benefits include:

• Single Data Model: one model for both database and API
• Automatic Validation: built-in data validation from Pydantic
• Type Safety: full Python type support and IDE integration
• Compatibility: works with existing SQLAlchemy projects
• Async Support: built-in async/await support out of the box

Installation and Setup

Basic Installation

pip install sqlmodel

Installation with Additional Dependencies

For PostgreSQL support:

pip install sqlmodel[postgresql]

For async support:

pip install sqlmodel[async]

Full installation with all dependencies:

pip install "sqlmodel[postgresql,async]"

Connecting to a Database

from sqlmodel import SQLModel, create_engine

# SQLite (for development)
engine = create_engine("sqlite:///database.db")

# PostgreSQL
engine = create_engine("postgresql://user:password@localhost/dbname")

# MySQL
engine = create_engine("mysql+pymysql://user:password@localhost/dbname")

SQLModel Basics

Creating a Basic Model

from sqlmodel import SQLModel, Field
from typing import Optional

class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    email: str = Field(unique=True)
    age: int = Field(ge=0, le=150)
    is_active: bool = Field(default=True)

Field Parameters

SQLModel uses an extended Field from Pydantic with additional capabilities:

class Product(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(max_length=100, index=True)
    price: float = Field(gt=0, description="Product price")
    category_id: int = Field(foreign_key="category.id")
    created_at: datetime = Field(default_factory=datetime.now)

Creating Database Tables

from sqlmodel import SQLModel, create_engine

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

# Create all tables
SQLModel.metadata.create_all(engine)

# Create a specific table
User.metadata.create_all(engine)

CRUD Operations with SQLModel

Creating Records

from sqlmodel import Session

# Create a single record
user = User(name="John", email="john@example.com", age=25)

with Session(engine) as session:
    session.add(user)
    session.commit()
    session.refresh(user)  # Get the ID from the database
    print(f"Created