Looking for SQL ORM for FastAPI

[SHIMURA0]

Dear sir,

thanks for your sharing of using FastAPI at production level, I am using FastAPI as well now, but I found that it seems that the only disturbing point to me is that the choice of ORM model for database. I wonder what ORM you used for your business and I will be grateful if you can share some experience on choosing ORM models, e.g. SQLALchemy, SQL-model or tortoise-orm.

[masroore]

SQLAlchemy is the safest choice. the learning curve is kinda steep, but it's well worth the effort once you;ve gotten the hang of it.

SQLModel is basically SQLA coupled with pydantic. I wouldn't recommend it for new projects.

for smaller, write-once-and-forget projects, I'd recommend peeweeorm - you can get started in basically 5 minutes.

tortoise-orm also has async support with the simplicity of peeweeorm.

piccolo-orm seems nice as well, async support and the admin backend could be useful.

[zhanymkanov]

Currently, the best choice is SQLAlchemy - it's stable, battle tested, and well documented.

I agree with @masroore and I wouldn't start a serious project with SQLModel, as it seems too early.

My suggestion is to spend some time with learning SQLAlchemy and stick to it.

Tortoise is good, but Alchemy is better - it has wider SQL coverage and better migration tools (Alembic), while Tortoise's migration library is kinda weird. Also, it has some problems with nested joins (at least in our codebase).

[masroore]

I can share an anecdote in this regard. I did a one-off scraping project with SQLModel. I was wrangling several million JSON files, which were poorly documented, the schema was not well-defined and each file was few 100 KBs large. I had evaluated multiple ORMs and decided on SQLModel. SQLModel fit the bill for my specific requirements, thanks to pydantic validation during the somewhat complicated ETL process of importing json data into a pgsql db.

Apart from this project, I pretty much always go with SQLAlchemy (if maintainability is a requirement). For write-once-and-forget small projects, I use any of the micro-orms (peewee).

I agree with @zhanymkanov as a python developer, I recommend spending some time learning SQLAlchemy. SQL/A may seem daunting at first, but it will be a time well-spent.

[SHIMURA0]

Thanks guys on your valuable sharing! The motivation for this question is that I don't know whether SQLAlchemy is good at asyncio 😂

[masroore]

@SHIMURA0 SQL/A 2.* (also >=v1.4) does support asyncio (sqlalchemy.ext.asyncio). See here: https://docs.sqlalchemy.org/en/20/_modules/examples/asyncio/basic.html
keep in mind, this is not a full asynchronous rewrite of the core framework. instead, the crafty developers found a way to enable compatibility with asyncio.

Also, check out this blog post: https://medium.com/@tclaitken/setting-up-a-fastapi-app-with-async-sqlalchemy-2-0-pydantic-v2-e6c540be4308

[anryangelov]

I would recommend piccolo if you use postgres. It is supper easy and have build in migrations. Also it is build for async but it can be sync as well.

[SHIMURA0]

Thanks guys!