Learn Python with Talk Python's 270 hours of courses

SQLModel: The New ORM for FastAPI and Beyond

Episode #353, published Fri, Feb 18, 2022, recorded Mon, Jan 17, 2022

Two frameworks that have taken the Python world by storm are FastAPI and Pydantic. Once you already have your data exchange modeled in Pydantic, you might want to use that code for storing it in the database. And, if you have DB models you might want to somehow use them to power and document the APIs built with FastAPI.

But the popular ORMs, such as SQLAlchemy and others, far predate Pydantic. But could they be put together?

Sebastián Ramírez is here to tell us the answer is yes. We're covering his project SQLModel which is the marriage between Pydantic and SQLAlchemy.

Watch this episode on YouTube
Play on YouTube
Watch the live stream version

Episode Deep Dive

Guest Introduction and Background

Sebastian Ramirez is the creator of FastAPI, a modern, high-performance Python web framework that leverages Python’s latest language features such as type hints and async/await. He also created Typer, a library for building friendly command-line interfaces, and is the author of SQLModel, a library that marries SQLAlchemy and Pydantic for streamlined database and API development. Sebastian’s work focuses on developer experience, simplicity, and strong tooling support, all while building on top of powerful, time-tested Python libraries.

What to Know If You're New to Python

If you’re just beginning to explore Python, here are a few essentials to help you follow the discussion about SQLModel and FastAPI:

  • Basic Data Types and Functions – Understanding Python’s integers, strings, lists, and dictionaries is important, as well as how to define simple functions.
  • Type Hints – This episode emphasizes how Python type hints supercharge editor support and validation, so a quick look at how Python 3+ uses these will go a long way.
  • Virtual Environments – You’ll hear about working on multiple projects and dependencies. Knowing how to create and use venv or other virtual environment tools is helpful.
  • Package Installation – Be sure to understand the basics of installing packages via pip or conda, as tools like FastAPI, Pydantic, and SQLAlchemy are third-party libraries.

Key Points and Takeaways

  1. SQLModel: Combining Pydantic and SQLAlchemy This library gives you the power of SQLAlchemy’s robust database connectivity and Pydantic’s data modeling and validation. It eliminates much duplication between database models and API data models by letting you define them in one place. Because it’s built directly on top of SQLAlchemy, the queries and back-end engine stay fully compatible with all the advanced SQLAlchemy features. At the same time, you benefit from Pydantic’s type validation for your incoming and outgoing data.
  2. Seamless Integration with FastAPI SQLModel was designed to complement FastAPI’s structure. Because FastAPI automatically generates docs and does validation based on Pydantic models, SQLModel slots right into this workflow. You can create powerful APIs where your database tables match your validation and documentation models without writing separate classes for each.
  3. Why Type Hints and Editor Support Matter A central theme is the power of Python’s type hints: They make your code more predictable for tools like Pydantic and SQLModel, but they also provide you immediate benefits in your IDE, such as auto-completion and error-checking. This reduces logical mistakes and speeds up refactoring because your tooling can understand how your objects are supposed to work.
  4. Avoiding Duplicate Models in Your Codebase Traditionally, you had separate classes for database models (SQLAlchemy) and API data models (Pydantic). That meant repeated attributes and potential inconsistencies when one model was updated but not the other. SQLModel encourages a single source of truth, which can reduce code duplication and the chance of drifting models.
  5. Handling Relationships and Lazy Loading Many SQLAlchemy features still apply unchanged when you use SQLModel: relationships, foreign keys, lazy loading, and advanced joins. While SQLModel layers simpler interfaces on top, you can still drop down to raw SQLAlchemy any time for complex queries and updates.
  6. Handling Passwords and Sensitive Data The episode discusses having distinct models for data you receive (which may contain secret info like passwords) and data you return (which should not expose passwords). With SQLModel, you can inherit one base class for general fields, then create specialized child classes for specific use-cases (like read-only or write-only).
  7. Creating a Single Session per Request By leveraging FastAPI’s dependency injection, you can ensure each request has exactly one database session that opens at the start and closes at the end. This neatly scopes your database interactions, avoiding threading or concurrency issues.
  8. Automatic Interactive Documentation FastAPI’s interactive docs—driven by Pydantic’s schema—mean that, once you adopt SQLModel, your endpoints become even more self-documenting. You can try out endpoints and see exactly which fields are required or optional based on your single shared data model.
  9. Async Support Through SQLAlchemy 1.4+ SQLModel works with SQLAlchemy 1.4’s new async engine, so you can run your database queries asynchronously where appropriate. This is a game-changer for web apps expecting high concurrency, and it dovetails perfectly with FastAPI’s async routes.
  10. Choosing When to Use an ORM vs. Raw SQL Sebastian highlights that some devs prefer raw SQL for total control and clarity. That’s still possible in Python, but an ORM like SQLModel (via SQLAlchemy) can reduce errors, give strong type support, and simplify refactoring. Decide which approach best suits each part of your application or project.

Interesting Quotes and Stories

  • On bridging Pydantic and SQLAlchemy: “I wanted to have all the benefits of Python’s new features, but most ORMs were built before these features existed—so I figured, why not bring them together?” – Sebastian Ramirez
  • On the bus factor: “FastAPI is in my personal GitHub, but the community is huge, and you can always fall back to forking if I vanish. That’s how open source works.” – Sebastian Ramirez
  • On building new libraries: “I try to avoid building new tools unless nothing else does exactly what I need. Then I decide I might as well put it together for everyone.” – Sebastian Ramirez

Key Definitions and Terms

  • ORM (Object-Relational Mapping): A library or system that lets developers interact with relational databases using Python classes rather than raw SQL statements.
  • Pydantic: A Python library for data validation and settings management using Python type hints.
  • SQLAlchemy: A powerful Python SQL toolkit and ORM that provides a full suite of well-organized enterprise-level persistence patterns.
  • Dependency Injection (DI): A design pattern for supplying a function or class with outside resources, enabling cleaner, more testable code.
  • Alembic: A migrations tool for SQLAlchemy-based apps to keep database schemas in sync with model changes over time.

Learning Resources

Below are some additional resources for a deeper look into Python, FastAPI, and type-driven development.

Overall Takeaway

SQLModel shows how modern Python libraries can come together for a smoother developer experience, bridging the gap between APIs and databases. By integrating Pydantic for data validation and SQLAlchemy for robust SQL handling, Sebastian Ramirez has given the Python community a tool that reduces duplication, simplifies validation, and encourages best practices. With robust editor support, a clean API design, and easy integration into frameworks like FastAPI, SQLModel helps developers build high-quality, maintainable applications that leverage the best of Python’s new language features.

Links from the show

Sebastián Ramírez: @tiangolo

SQLModel: sqlmodel.tiangolo.com
Create a SQLModel Model: sqlmodel.tiangolo.com
Multiple Models: sqlmodel.tiangolo.com

FastAPI Talk Python episode: talkpython.fm/284
FastAPI Dependency Injection: fastapi.tiangolo.com

ODMantic for MongoDB: github.com
Beanie for MongoDB: github.com

Michael's Short video on Pydantic: youtube.com
FastAPI courses by Michael: training.talkpython.fm/fastapi-courses
Watch this episode on YouTube: youtube.com
Episode transcripts: talkpython.fm

--- Stay in touch with us ---
Subscribe to Talk Python on YouTube: youtube.com
Talk Python on Bluesky: @talkpython.fm at bsky.app
Talk Python on Mastodon: talkpython
Michael on Bluesky: @mkennedy.codes at bsky.app
Michael on Mastodon: mkennedy

Talk Python's Mastodon Michael Kennedy's Mastodon