8 ms·

Hey all! Author here! Thanks for sharing! This is the biggest thing I've built since FastAPI and Typer... SQLModel is a library for interacting with SQL DBs,

Hey all! Author here! Thanks for sharing! This is the biggest thing I've built since FastAPI and Typer... SQLModel is a library for interacting with SQL DBs, based on Python type hints. Each model is both a Pydantic and SQLAlchemy model, and it's all optimized for FastAPI. GitHub here: https://github.com/tiangolo/sqlmodel https://github.com/tiangolo/sqlmodel More info in this Twitter thread: https://twitter.com/tiangolo/status/1430252646968004612 https://twitter.com/tiangolo/status/1430252646968004612 And the docs are here: https://sqlmodel.tiangolo.com/ https://sqlmodel.tiangolo.com/

simonw 5y ago
Constructing this such that a single class works as both a Pydantic model AND a SQLAlchemy model - while maintaining an elegant user-facing API - is one heck of a trick! Congratulations.
- tiangolo 5y ago
  Thanks Simon! It means a lot, even more coming from you (I might screenshot this comment and frame it in my living room). :D
zachthewf 5y ago
Thank you so much for your incredible work on FastAPI! This looks like another really cool project, can't wait to try it out.
tedmiston 5y ago
Thank you for building this! So I'm running a FastAPI deployment in prod already today using both Pydantic models for the app / API itself and also reflected SQLAlchemy ORM models for the database. In my case I am lucky in that my app interacts (read only) with a data warehouse as opposed to a schema that it manages directly. Am I understanding right that the main benefit of using sqlmodel is not needing to maintain the 2 separate models for the app vs db that one would need in most cases? Also, have you thought about an approach to migrations yet? Curious to hear your thoughts there as well.
- tiangolo 5y ago
  Yep, less code duplication between Pydantic and SQLAlchemy, autocompletion and inline errors in editors (e.g. when creating new instances and when fetching data from the database), because it has several type annotation tricks. It's all just SQLAlchemy, so the same migrations with Alembic would do it, I plan on documenting that in the future and probably adding a thin layer on top.
BiteCode_dev 5y ago
Using sqla as a base is such a sane decision. It leverage a rock solid layers with async support and a battle tested ecosystem while offering a versatile query builder when you need to opt out of the ORM paradigme. I have a huge respect for your work.
- tiangolo 5y ago
  Thanks! :D
daanluttik 5y ago
I have thought about implementing exactly this concept... And I'm so greatful that you have done it instead . Love your work man!
scrollaway 5y ago
I'm curious and excited. Have you talked about the differences between this and TortoiseORM yet? The latter has the big advantage of having an api familiar to Django users. In short, what motivated you to write your own rather than "bless" one of the existing ones? I see the approach that this isn't exactly an orm, it's more of an api wrapper around it (SQLalchemy in that case), is that correct?
- dkh 5y ago
  SQLModel isn't its own totally new thing so much as an abstraction that lets you create models that are both SQLAlchemy models as well as Pydantic models, simultaneously. If you use Pydantic, or want to be using it with your models, this sounds pretty great. Right now shoehorning the same kind of functionality into other frameworks can be a bit of a pain. I do love Tortoise though, and it is what I am using currently -- but also one thing I'm currently battling with it is its approach to working with Pydantic. So I'll be giving this a try.
  - tiangolo 5y ago
    Yep, I wanted to build it on top of SQLAlchemy to inherit all it's robustness and to be compatible with it, as it has the biggest widespread. SQLAlchemy now supports async too, so SQLModel inherits that as well. And in particular, other libraries have done a great job, but I wanted to take advantage of the features that editors can provide, with autocompletion, inline errors, etc. SQLModel has lots of tricks to provide the best developer experience possible, e.g. autocompletion while creating a new model instance. For example, the style of querying stuff, for example: https://sqlmodel.tiangolo.com/tutorial/where/#where-and-expressions-instead-of-keyword-arguments https://sqlmodel.tiangolo.com/tutorial/where/#where-and-expr...
kbumsik 5y ago
Cool project! Just wanted to mention that you emphasize "optimized for FastAPI" but it looks like it does nothing to do with FastAPI. Do you have any reason to narrow down the scope to FastAPI when other frameworks like Flask are still more widely used? Specifically mentioning only one framework causes loosing interest of people who use other frameworks.
- tomnipotent 5y ago
  > but it looks like it does nothing to do with FastAPI A SQLModel is also a Pydantic model. FastAPI uses Pydantic.
- tedmiston 5y ago
  > Do you have any reason to narrow down the scope to FastAPI when other frameworks like Flask are still more widely used? FastAPI is basically the (async) 2021 version of Flask just built on Starlette instead. If you have used Flask before, the learning curve will be pretty low, and the documentation is great. But of course people have been using SQLAlchemy and Flask together for ages so I don't see any reason you couldn't use this with Flask instead if you prefer. That said, I bet you'll be able to whip up the MVP even faster using FastAPI. I do not have any affiliation with the project other than being a happy user today.
- nomdep 5y ago
  > Do you have any reason to narrow down the scope to FastAPI when other frameworks like Flask are still more widely used? Because he is the author of FastAPI
- dkh 5y ago
  Flask was incredible and a joy to build with, but it's not the future. It's only the present in that so any legacy apps exist that use it and because people who don't knew better will likely still stumble upon it first and use it for their new project. But we are at the point where async python has really taken off, as well as where the performance gains are just impossible to ignore except for apps thats are either dead-simple or that are super CPU-bound. And FastAPI is unquestionably leading the pack with regards to async frameworks, so it makes sense to target that. (Also, yes, he's the creator.) (Additionally, FastAPI is using Starlette under-the-hood, which is built on ASGI, part of the point of which is to standardize async servers and frameworks to allow for wide compatibility. Anything that works on Starlette or any ASGI framework will work with FastAPI, and vice versa unless the component is tightly coupled to FastAPI-specific code.)

simonw 5y ago

Constructing this such that a single class works as both a Pydantic model AND a SQLAlchemy model - while maintaining an elegant user-facing API - is one heck of a trick! Congratulations.

tiangolo 5y ago
Thanks Simon! It means a lot, even more coming from you (I might screenshot this comment and frame it in my living room). :D

zachthewf 5y ago

Thank you so much for your incredible work on FastAPI! This looks like another really cool project, can't wait to try it out.

tedmiston 5y ago

Thank you for building this! So I'm running a FastAPI deployment in prod already today using both Pydantic models for the app / API itself and also reflected SQLAlchemy ORM models for the database. In my case I am lucky in that my app interacts (read only) with a data warehouse as opposed to a schema that it manages directly. Am I understanding right that the main benefit of using sqlmodel is not needing to maintain the 2 separate models for the app vs db that one would need in most cases? Also, have you thought about an approach to migrations yet? Curious to hear your thoughts there as well.

tiangolo 5y ago
Yep, less code duplication between Pydantic and SQLAlchemy, autocompletion and inline errors in editors (e.g. when creating new instances and when fetching data from the database), because it has several type annotation tricks. It's all just SQLAlchemy, so the same migrations with Alembic would do it, I plan on documenting that in the future and probably adding a thin layer on top.

BiteCode_dev 5y ago

Using sqla as a base is such a sane decision. It leverage a rock solid layers with async support and a battle tested ecosystem while offering a versatile query builder when you need to opt out of the ORM paradigme. I have a huge respect for your work.

tiangolo 5y ago
Thanks! :D

daanluttik 5y ago

I have thought about implementing exactly this concept... And I'm so greatful that you have done it instead . Love your work man!

scrollaway 5y ago

I'm curious and excited. Have you talked about the differences between this and TortoiseORM yet? The latter has the big advantage of having an api familiar to Django users. In short, what motivated you to write your own rather than "bless" one of the existing ones? I see the approach that this isn't exactly an orm, it's more of an api wrapper around it (SQLalchemy in that case), is that correct?

dkh 5y ago
SQLModel isn't its own totally new thing so much as an abstraction that lets you create models that are both SQLAlchemy models as well as Pydantic models, simultaneously. If you use Pydantic, or want to be using it with your models, this sounds pretty great. Right now shoehorning the same kind of functionality into other frameworks can be a bit of a pain. I do love Tortoise though, and it is what I am using currently -- but also one thing I'm currently battling with it is its approach to working with Pydantic. So I'll be giving this a try.
- tiangolo 5y ago
  Yep, I wanted to build it on top of SQLAlchemy to inherit all it's robustness and to be compatible with it, as it has the biggest widespread. SQLAlchemy now supports async too, so SQLModel inherits that as well. And in particular, other libraries have done a great job, but I wanted to take advantage of the features that editors can provide, with autocompletion, inline errors, etc. SQLModel has lots of tricks to provide the best developer experience possible, e.g. autocompletion while creating a new model instance. For example, the style of querying stuff, for example: https://sqlmodel.tiangolo.com/tutorial/where/#where-and-expressions-instead-of-keyword-arguments https://sqlmodel.tiangolo.com/tutorial/where/#where-and-expr...

kbumsik 5y ago

Cool project! Just wanted to mention that you emphasize "optimized for FastAPI" but it looks like it does nothing to do with FastAPI. Do you have any reason to narrow down the scope to FastAPI when other frameworks like Flask are still more widely used? Specifically mentioning only one framework causes loosing interest of people who use other frameworks.

tomnipotent 5y ago
> but it looks like it does nothing to do with FastAPI A SQLModel is also a Pydantic model. FastAPI uses Pydantic.
tedmiston 5y ago
> Do you have any reason to narrow down the scope to FastAPI when other frameworks like Flask are still more widely used? FastAPI is basically the (async) 2021 version of Flask just built on Starlette instead. If you have used Flask before, the learning curve will be pretty low, and the documentation is great. But of course people have been using SQLAlchemy and Flask together for ages so I don't see any reason you couldn't use this with Flask instead if you prefer. That said, I bet you'll be able to whip up the MVP even faster using FastAPI. I do not have any affiliation with the project other than being a happy user today.
nomdep 5y ago
> Do you have any reason to narrow down the scope to FastAPI when other frameworks like Flask are still more widely used? Because he is the author of FastAPI
dkh 5y ago
Flask was incredible and a joy to build with, but it's not the future. It's only the present in that so any legacy apps exist that use it and because people who don't knew better will likely still stumble upon it first and use it for their new project. But we are at the point where async python has really taken off, as well as where the performance gains are just impossible to ignore except for apps thats are either dead-simple or that are super CPU-bound. And FastAPI is unquestionably leading the pack with regards to async frameworks, so it makes sense to target that. (Also, yes, he's the creator.) (Additionally, FastAPI is using Starlette under-the-hood, which is built on ASGI, part of the point of which is to standardize async servers and frameworks to allow for wide compatibility. Anything that works on Starlette or any ASGI framework will work with FastAPI, and vice versa unless the component is tightly coupled to FastAPI-specific code.)