Fastapi: Question. SQL Alchemy and blocking calls

[codefather-labs]

in the documentation it is suggested to use sql alchemy as a orm, but isn't alchemy completely synchronous? wouldn't that way create blocking calls?

[Kludex]

I don't know at which point you've read. But there's also a section about databases, which is the asynchronous suggestion about the topic.

I can't give you the references right now, but search for "encode databases" in Google or in the documentation and you'll find more information.

About this topic, if someone is willing to open a PR about how to use FastAPI with Gino and/or Tortoise, I think it will be gladly accepted. Those are the most recommended async ORM solutions nowadays, even by @tiangolo himself (based on last Friday YouTube presentation).

Just remember that FastAPI is DB Independent. :)

About what you probably saw in the documentation, you need to understand that SQLAlchemy is more than 10 years old, and the async ORM frameworks are still quite new. GINO (build on top of SQLAlchemy) was born February 2020 and Tortoise has more or less the same age as FastAPI (2 years old +). My point is that going for SQLAlchemy will avoid surprises in your development path.

Answering your question: yes, it will.

Last curious points:

• Tortoise ORM has a Pydantic feature which is quite nice for us.
• @tiangolo is experimenting some nice stuff that will make you able to create a Pydantic and SQLAlchemy model at the same time (this is based on a single Twitter post). We don't have more information about it, but he said he was just experimenting. Hope he can achieve something nice and share with us :)

P.S.: Sorry for the lack of links, I'm unable to add them now.

Edit: references below:

• FastAPI docs about async SQL databases
• databases from encode
• GINO
• Template using GINO with FastAPI
• Tortoise ORM - FastAPI example
• Sebastián's YouTube talk about FastAPI from last Friday
• Sebastián's tweet about Pydantic + SQLAlchemy models

[codefather-labs]

I am using fastapi and tortoise orm in production and tortoise orm has problems with migrations. or rather, they are not at all out of the box. this creates the need for third-party solutions such as aerich, which in turn is poorly optimized for postgresql. The question arises - are there any plans to write a migration library for tortoise orm or support for aerich? or not?
and what to do with migrations on fastapi if there is no normal, asynchronous orm? because in the current implementation it turns out that fastapi does not have a stable orm. its a serious problem

[Kludex]

The question arises - are there any plans to write a migration library for tortoise orm or support for aerich? or not?

We are talking in the FastAPI GitHub issues, if you want details about Tortoise, maybe you should ask on their issues... But anyway, using Tortoise search engine you can find their roadmap:

and what to do with migrations on fastapi if there is no normal, asynchronous orm? because in the current implementation it turns out that fastapi does not have a stable orm. its a serious problem

Please, do not make strong statements like "its a serious problem" without concrete arguments. I'll quote what I said in my last reply:

Just remember that FastAPI is DB Independent. :)

Now, aerich was created a few months ago (May 19) so it's expected to not be perfect. But be aware that SQLAlchemy's most common migration tool used is Alembic and GINO as well is using the same tool (GINO's solution). So none of those ORMs have native migrations.

Additional comments:

• Alembic was created by SQLAlchemy creator...
• Django has native migrations on the Django ORM... If that's something that you really need...

[mreschke]

Using a basic wrk performance test with the exact tutorial about SQL Alchemy does indeed cause the server to crash. See #1562. My work around is to use encode databases for async. Just note that encode database is only the query builder core of SQL Alchemy so if you want an ORM you will need to build that layer as I am...or looks like tomchristie from encode is working on one himself (https://github.com/encode/orm) though it resembles Django ORM which I am not fond of.

[codefather-labs]

Using a basic wrk performance test with the exact tutorial about SQL Alchemy does indeed cause the server to crash. See #1562. My work around is to use encode databases for async. Just note that encode database is only the query builder core of SQL Alchemy so if you want an ORM you will need to build that layer as I am...or looks like tomchristie from encode is working on one himself (https://github.com/encode/orm) though it resembles Django ORM which I am not fond of.

https://github.com/encode/orm is too raw. it don't even have ManyToMany Field

https://github.com/encode/orm/blob/master/orm/fields.py

[codefather-labs]

and in the remainder we get a situation in which we do not have a stable orm for an asynchronous python with migrations and other functionality necessary for life.

[codefather-labs]

if I had more free time, I would write migrations for tortoise orm myself as it seems to me the most convenient orm solution at the moment

[long2ice]

I'm sorry the support wasn't that good for postgres with aerich, since I don't use postgres in my work and I'm not so familiar with postgres...But it's great welcome to accept PR.😄

[Kludex]

For those who don't know and are reading this: @long2ice is the creator of aerich

[toinbis]

Guys, just found this discussion prior raise to raising a new issue with a very similar topic. Hope it's OK if I jump in. Am in very similar situation trying to decide on database access using FastAPI.

My criterias are as follows:
a) Migrations - a must. On postgres, so aerich along tortoise ORM had to be left out from options.
b) sync VS async - does not really matter.
c) 'lightweight' - I feel I might not just need ORM at all.

Am choosing between:

1. SqlAlchemy Core - bit more boilerplate, but full control. Sadly only place where I found some guidelines on how to use fastAPI with SQLA Core was here https://fastapi.tiangolo.com/advanced/async-sql-databases/ .
2. Reading this I discovered gino which seems to fit all my requirements at all.
3. Just ignore the 'lightweight' personal preference ant stick to SQLAlchemy ORM.

Making the production client-facing SAAS system here. Am being torn by decision I have to make.

3rd option seems safest option by far, by it feels like overbloating otherwise super lightweight-elegant system by a compelexity layer which can reduced.
1st option is also safe(maybe slightly less, as more code of mine - more places to make errors), just a bit more boilerplate, which I don't mind at all.
2nd option might just be the best of both worlds - is Gino stable enough for production system?

I know i'm asking more of your opinion on pros and cons of the choices provided and not specific question, but any (any!) input would be much valued here. Thanks a lot!

[mreschke]

@toinbis If I were you, and you don't care about ORM, just use encode/databases as outlined in that https://fastapi.tiangolo.com/advanced/async-sql-databases/ doc. This is what I use (though I built my own ORM on top of it). Its great and is full async with all 3 MySQL, Postgress and SQLite so you won't get locks/crashes as you would with plain old sync SQLAlchemy as FastAPI uses it. Ginno looks really great and I was going to use it until I learned they only support Postgress (I use MySQL and SQLite for testing). Keep in mind, Ginno is going through a full-rewrite to version 2.0, so I would feel uncomfortable using either 1.0 since its "dead" or 2.0 since it is not ready yet.

[mreschke]

Maybe of help. encode/databases is an SQLAlchemy Core adapter for the 3 main async libraries asyncpg, aiomysql, or aiosqlite. Core meaning query builder only, no ORM. Because of this all docs here https://docs.sqlalchemy.org/en/13/core/ work, so lots of documentation at your disposal. See also the main sites docs https://www.encode.io/databases/ which are basic because you can just goto SQLAlchemy's website for full docs. Because encode/database is the full SQLAlchemy Core, alembic migrations work perfectly.

The reasons for this issue ticket are that sticking to SQLAlchemy ORM as defined in https://fastapi.tiangolo.com/tutorial/sql-databases/ causes performance problems and flat out gunicorn/uvicorn crashes and locks because if its synchronous nature. Just run a basic wrk performance test on the exact tutorial on that page and you will see what I mean. Its not stable for production in my simple opinion, its not even an option.

If async is a priority (which it should be), and if supporting multiple database is a priority (which is smart), and if migrations are a priority (duh), use encode/databases. If all of those + an ORM is a priority, well...there is no good existing solution yet. Which is why I simply built my own (they really are relatively easy which is why the source code for encode/orm is basically 2 files). Maybe we can all pursuade @tomchristie and encode to finish up their ORM. That would be a nice thing right now.

[toinbis]

Hi @mreschke - thanks a lot for your insights. Those are much apppreciated!

A note about gino's rewrite is indeed something to have in mind whilst making a choice!

As for using encode/databases, I've read https://github.com/encode/hostedapi/blob/master/PROGRESS.md whilst researching. And somehow after reading Data migrations are awkward when they start to involve relationships. in there I made myself (not necessary correct) conclusion that it's too early for production-ready migrations once using encode/databases. Or maybe it's async approach in general that makes harder to implement migrations, but gino seems to find it's way around...

So yeah, as it's relatively easy to work around CRUD with more or less any stack, production-level migrations support is much harder to manage yourself and, at least in my case, seems to be the primary criteria for choose the stack...

[mreschke]

@toinbis studying that "Data migrations are awkward" line you mentioned I don't believe @tomcristie is talking about actual alembic "database migrations" but business level data migrations when it comes to bulk inserts and id relations. From what I have read and seen with encode/databases is that it is fully SQLA core it works perfectly with alembic. Though I cannot say from detailed personal experience yet. I have only built basic tables with a few relations with no alembic problems. Ill know more in a few weeks since I am currently building a larger project with encode/databases to flesh out my custom ORM. Hopeful full alembic migrations will work no problem.

[bubthegreat]

For those jumping in - if you want a plug and play alternative to SQLAlchemy, GINO has a great implementation that mimicks SQLAlchemy, has some great tutorials (Including use in FastAPI) and has, so far, been great to use.

[collerek]

Hi, for me GINO was disqualified cause it only supports postgres, and I needed something with mysql support so I took inspiration from encode/orm and created ormar. It depends on sqlalchemy core and databases but uses modified pydantic models - so you get both pydantic model and sqlalchemy table for free under the hood. It's based on sqlalchemy core so you can use alembic for migrations, and ormar models can be used directly in fastapi instead of pydantic models. Feel free to comment, point any issues or contribute!