Fastapi: Why use peewee over sqlalchemy?

[dsmurrell]

I see that in your peewee setup docs there is a line "But it doesn't give Peewee async super-powers. You should still use normal def functions and not async def."

Does this mean that you can have significant advantages using sqlalchemy instead of peewee? If so why did you choose to use peewee over sqlalchemy in some of your projects?

[nsidnev]

Specifically this line does not mean that SQLAlchemy is better than peewee.
It just means that this peewee "patch" will allow you to use this ORM with asynchronous frameworks, one of which is FastAPI.

Since FastAPI is not tied to any particular ORM or library for database queries, you can choose what you like.
Since SQLAlchemy is currently the most popular ORM with which many developers are familiar, and it also includes many different plugins (and alembic as a system for migrations), many can choose it. On the other hand, SQLAlchemy has very complex DSL (IMHO). And peewee is very similar to the ORM that comes with Django and for beginners it may seem a lot easier.

In any case, the choice of ORM for the project is too individual to say that the choice of, for example, SQLAlchemy instead of peewee will be the only right one. For example, you can look in the direction of asynchronous orm like tortoise-orm.

[dsmurrell]

Thanks @nsidnev, I'm trying to get at this in particular though: "doesn't give Peewee async super-powers"

What are you able to do in an async framework with SQLAlchemy that you might not be able to do with peewee? i.e. what restrictions does using peewee come with when used with FastAPI? You didn't seem to address this in your answer.

[nsidnev]

Perhaps this explanation will be better for explaining the main principle.

In general, the only limitation that peewee will have when used with FastAPI is that it cannot be used in asynchronous functions, since it blocks the asyncio event loop.
But exactly the same restriction applies to SQLAlchemy itself (if you use its standard functionality, and not user libraries based on it, such as gino or encode/database).

This is what is meant in this line:

But it doesn't give Peewee async super-powers. You should still use normal def functions and not async def.

You should not use async def function declarations when using peewee or SQLAlchemy, as they are blocking and incompatible with asynchronous python programming. When you use regular def definitions for route functions, these routes run in separate threads and do not block the asyncio event loop.

@app.get('/sa-sync-route')
def sa_sync_route(user_id: int):
    # FastAPI will be able to handle other routes
    print(SessionLocal().query(User).filter(User.id == user_id).first())

@app.get('/sa-async-route')
async def sa_async_route(user_id: int):
    # FastAPI will hang here until the end of this route
    print(SessionLocal().query(User).filter(User.id == user_id).first())

@app.get('/peewee-sync-route')
def peewee_sync_route(user_id: int):
    # FastAPI will be able to handle other routes
    print(User.filter(User.id == user_id).first())

@app.get('/peewee-async-route')
async def peewee_async_route(user_id: int):
    # FastAPI will hang here until the end of this route
    print(User.filter(User.id == user_id).first())

In all other places there are no more restrictions. Everything will depend only on the capabilities of ORM itself.

[Trim21]

you can use encode/databases as sqlalchemy asyncio driver, and peewee-async as peewee asyncio driver.

I choose peewee because I was using peewee before before using faseapi.

[euri10]

But exactly the same restriction applies to SQLAlchemy itself (if you use its standard functionality, and not user libraries based on it, such as gino or encode/database).

Just to explain a little bit the above, most people think of sqlalchemy as it orm usage part

This shadows the other usage of sqlalchemy, it's probably because the main usage is the orm that people use this shortcut.

In fact sqlalchemy is 2 things, you get sqlalchemy core, which quote

The breadth of SQLAlchemy’s SQL rendering engine, DBAPI integration, transaction integration, and schema description services are documented here. In contrast to the ORM’s domain-centric mode of usage, the SQL Expression Language provides a schema-centric usage paradigm.

Databases is powdered by sqlalchemy core for instance.

And you get the orm part.

[dsmurrell]

Thanks @nsidnev and @euri10!

@Trim21, did you use the setup instructions provided here for peewee-async?

https://fastapi.tiangolo.com/advanced/sql-databases-peewee/

but plug in peewee-async instead of peewee?

Or was it more simple and you only had to use peewee-async without any of these complications?

[Trim21]

@dsmurrell It's much more simple than peewee section of docs.

But you have to use peewee-async with peewee, not instead of. peewee-async just handles all connections in asyncio way, you will still need to define your table and query with peewee.

[tiangolo]

What I meant by "it doesn't give Peewee superpowers" is that it doesn't give it any advantage, and that it barely allows you to use Peewee if you already have code using it.

But in any case, I wouldn't recommend Peewee with FastAPI. You are a lot better with SQLAlchemy than with Peewee.

If you want a different syntax you might want to check Tortoise-ORM, that recently got native support for Pydantic :tada:

In short, please don't use Peewee with FastAPI.

[github-actions[bot]]

Assuming the original issue was solved, it will be automatically closed now. But feel free to add more comments or create new issues.