7 ms·
SQLModel – SQL Databases in FastAPI
- deleted 5y ago[deleted]
- etimberg 5y agoDoes this have async query support? I’ve been using ormar lately with FastAPI and found it a pleasure to work with. I even built a number of PG specific extensions easily for things like JSONB and native UUID fields
- emptysea 5y agoI'm curious how the type hints for the `__init__` method are derived from the properties in the class without a type checking plugin Nothing stuck out to me when perusing through the code Edit: seems pylance can figure it out but mypy can't, maybe pylance is special casing something? from typing import Optional from sqlmodel import Field, SQLModel class Hero(SQLModel, table=True): id: Optional[int] = Field(default=None, primary_key=True) name: str secret_name: str age: Optional[int] = None reveal_type(Hero.__init__) # pylance: Type of "Hero.__init__" is "(self: Hero, *, id: int | None = Field(default=None, primary_key=True), name: str, secret_name: str, age: int | None = None) -> None" # mypy: main.py:18:13: note: Revealed type is "def (__pydantic_self__: sqlmodel.main.SQLModel, **data: Any)"
- jkrubin 5y agoHow does your test work with just pydantic basemodels?
- Xavdidtheshadow 5y ago> maybe pylance is special casing something? Anecdata, but when I was digging into how Dataclasses worked, Pylance had great completion, but only if I imported from the dataclass module itself. If I pasted the whole implementation into a local file and used that, I didn't get the hints. This makes me think that Pylance _does_ have some secret sauce.
- tiangolo 5y agoYep, but not secret, it's the in-progress dataclass_transform draft spec, already implemented in (open source) Pyright, and Pylance re-uses Pyright, so it gets that support. https://github.com/microsoft/pyright/blob/main/specs/dataclass_transforms.md https://github.com/microsoft/pyright/blob/main/specs/datacla...
- Xavdidtheshadow 5y agoThere you go! Not secret at all. Thanks for linking that!
- tiangolo 5y agoYep it uses the in-progress draft-spec dataclass_transforms https://github.com/microsoft/pyright/blob/main/specs/dataclass_transforms.md https://github.com/microsoft/pyright/blob/main/specs/datacla... As Pyright already supports it, and Pylance is built on Pyright, they can use it directly. Hopefully more editors will use it and hopefully it will be part of the standard Python (in a PEP, with typing.dataclass_transform, and with mypy support). And just in case you're wondering, there's no downside to having the dataclass_transform, anything that doesn't support it is unaffected, and nothing else would have completion either way.
- agordhandas 5y agoBeen a fan of FastAPI! Does this support async?
- simonw 5y agoThis bit looks like async support: https://github.com/tiangolo/sqlmodel/blob/0.0.3/sqlmodel/ext/asyncio/session.py https://github.com/tiangolo/sqlmodel/blob/0.0.3/sqlmodel/ext...
- tiangolo 5y agoYep, exactly. SQLAlchemy now has async support, so does SQLModel. But I haven't documented it yet.
- faizshah 5y agoWould this support pydantic dynamic models? https://pydantic-docs.helpmanual.io/usage/models/#dynamic-model-creation https://pydantic-docs.helpmanual.io/usage/models/#dynamic-mo... Or rather can I pass an existing pydantic class to SQLModel somehow?
- tiangolo 5y agoThe main benefit is actually being able to have statically typed models instead of dynamic, to get autocompletion, error checks, etc. In fact, I made a small utility library some time ago to create dynamic Pydantic models from SQLAlchemy models (https://github.com/tiangolo/pydantic-sqlalchemy https://github.com/tiangolo/pydantic-sqlalchemy), but that's only useful in a few cases, e.g. for response_model in FastAPI.
- sandGorgon 5y agohere's my attempt at unifying SQLAlchemy and Pydantic. I use the new dataclasses support in both libraries to do this. https://gist.github.com/sandys/671b8b86ba913e6436d4cb22d04b135f https://gist.github.com/sandys/671b8b86ba913e6436d4cb22d04b1... This example trys to play with some quirky table structures - Mixins, Surrogate primary keys, index, etc Both async and sync modes. I like what @tiangolo has done with SQLModel, but i suspect that a lot of people who are already using sqlalchemy in production will prefer to unify via dataclasses versus switching the DX to a new library. And this is possible today.
- afrnz 5y agoWell done Sébastian - I have been waiting for this. Having seperate SQLAlchemy and pydantic models always seemed like a spot where the whole FastAPI developer experience wasn't ideal imo. Looking forward to try this out :)
- nitred 5y agoI faced this exact problem a few days ago i.e. having to build one model for SQLAlchemy and an identical Pydantic model to validate the data (outside of the FastAPI context)! Will be using it the next I need it. @tiangolo I would like to use this for production at work, however I'm a bit hesitant about using the 0.0.x versions. Would you recommend me to wait until version 0.1.0 or 1.0.0?
- tiangolo 5y agoI just released it yesterday, so almost no one is using it in prod (I bet there's a couple using it already). But it's all SQLAlchemy underneath, the most wiidely used Python SQL library, with tons of years of usage, so that gives some safety. And the test coverage is at 97%. I will release 0.1.0 once test coverage reaches 100% and I have the main docs I want there. The only caveat with the version is upgrades, as, as of now, anything could change (probably I won't change anything, just add stuff). But you can solve it by pinning the version, writing tests, and upgrading the version only after tests pass. The only other issue you might have is that you really want to do some very advanced trick with SQLAlchemy and in some way it's still not supported. In that case you might want to use SQLAlchemy directly for that model, you can mix SQLAlchemy and SQLModel (although I haven't documented that yet).
- woile 5y agoSuper nice! Always delivering quality tools. This also seems to play good with starlette as well. And I see async and migrations are coming soon. Alembic is good but a bit cumbersome to use, I hope to see some improvements there as well
- tiangolo 5y agoNice! Yeah, it should work well with Starlette, Flask, or anything else. And yeah, I plan on adding docs and a thin layer on top of Alembic, but yeah, it's just SQLAlchemy so it will be just the same Alembic behind the scenes.
- gabereiser 5y agoWhile I'm a fan of both pydantic and SQLAlchemy, I feel like support for this should be more clear in pydantic. We have the `.from_orm` method and we have sqlalchemy model metadata. This project wraps them nicely but I shouldn't have to have separate glue code for this. Until we have cleaner pydantic/sqlalchemy integration for FastAPI, the OP's project will do just nicely. Great job. I fully expect FastAPI with it's reliance on pydantic to help push better support for ORM models though. Anyway, enough ranting and back to coding.
- easton 5y agoI was literally an hour ago trying to figure out how to get my complicated Pydantic models to convert correctly to the SQLAlchemy models I made. Thanks tiangolo!
- tiangolo 5y agoI'm glad to hear that!
- tiangolo 5y agoHey 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 agoConstructing 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 agoThanks 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 agoThank 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 agoThank 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.
- hangonhn 5y agoDoes it work outside of FastAPI or is it tied to it? We sort of shoved SQLAlchemy into Starlette (which FastAPI also uses) and it would be great if this library also works with Starlette. Thanks!
- tiangolo 5y agoYes of course! It is based on (and depends on) SQLAlchemy and Pydantic. And SQLModel, the same as those two dependencies, are independent of any framework, so you can combine them with anything you need.
- hangonhn 5y agoThanks! This could potentially save us a ton of work! Really appreciate all the amazing work you've done. FastAPI definitely being put to good use at my company.
- gigatexal 5y agoThat it uses SQLAlchemy bring the scenes is a deal breaker for me.
- remram 5y ago... why is that?
- gigatexal 5y agoWhy would I include a dependency that in turn pulls in a sprawling dependency like SQLALCHEMY when much other lightweight ORMs exist? If I was already using a number of tools that expect or build on SQLALCHEMY sure but I usually never am.
- command_tab 5y agoThe exact opposite for me -- I’ve been interested in using FastAPI with SQLAlchemy for some time, so this project is exactly what I’ve been looking for.
- jkrubin 5y agoIf tiangolo is involved it will be very polished by 0.1.0
- tiangolo 5y agoHehehe thanks for the trust! :D