I have published FastSQLA - an SQLAlchemy extension to FastAPI
Posted by hadriendavid@reddit | Python | View on Reddit | 18 comments
Hi folks,
I have published FastSQLA:
- Documentation: https://hadrien.github.io/FastSQLA/
- Github repo: https://github.com/hadrien/fastsqla
What is it?
FastSQLA is an SQLAlchemy 2.0+ extension for FastAPI.
It streamlines the configuration and async connection to relational databases using SQLAlchemy 2.0+.
It offers built-in & customizable pagination and automatically manages the SQLAlchemy session lifecycle following SQLAlchemy's best practices.
It is licenced under the MIT Licence.
Comparison to alternative
- fastapi-sqla allows both sync and async drivers. FastSQLA is exclusively async, it uses fastapi dependency injection paradigm rather than adding a middleware as fastapi-sqla does.
- fastapi-sqlalchemy: It hasn't been released since September 2020. It doesn't use FastAPI dependency injection paradigm but a middleware.
Target Audience
It is intended for Web API developers who use or want to use python 3.12+, FastAPI and SQLAlchemy 2.0+, who need async only sessions and who are looking to following SQLAlchemy best practices, latest python, FastAPI & SQLAlchemy.
I use it in production on revenue-making projects.
Feedback wanted
I would love to get feedback:
- Are there any features you'd like to see added?
- Is the documentation clear and easy to follow?
- What’s missing for you to use it?
Thanks for your attention, enjoy the weekend!
Hadrien
milong0@reddit
but is it written in Rust?
dailytadpole@reddit
Lol for real. Seeing a lot of the X but written in rust in the Python community nowadays.
hadriendavid@reddit (OP)
😅 No it is not.
Pryther@reddit
obviously not, a rust library would have way more emoji in the description 🦀🦀🦀
enigmaticboom@reddit
Could you add a comparison to SQLModel?
hadriendavid@reddit (OP)
By the way, FastSQLA now suports SQLModel -> https://hadrien.github.io/FastSQLA/#features:~:text=rollback%20on%20failure.-,SQLModel%20support%3A,-...
hadriendavid@reddit (OP)
Done! I added it to the post.
Much_Raccoon5442@reddit
I would really like to see how well it can paginate and search over a large table.
Consider a table with 1 million rows and 20 columns, how well does it paginate and sort the results when a user is filtering on say 5 columns of data?
hadriendavid@reddit (OP)
This depends much more on the table schema and on the data stored in that table, rather than on the query, doesn’t it?
Also, FastSQLA just adds an offset and a limit to a provided query so really it does not affect performance significantly.
Where FastSQLA may be costly is when querying for the total items in the result set. Count * can be very costly. But it can be customized in FastSQLA so, not a problem.
By the way, a nice talk on why count * is costly: https://youtu.be/GtQueJe6xRQ?si=qj375dylMcS_yo1-
Much_Raccoon5442@reddit
Not necessarily, for example fastapi-pagination is very slow at returning paginated results of large datasets compared to a well crafted query with the pyobc cursor. I don't know the reasoning of the difference unfortunately.
Perhaps that's the exact nature of the beast? If you know your data data well perhaps you can always paginate faster? In fact connectorx can offer a nice speed improvement as well if your destination is a data frame. I am still trying to figure out the fastest way to get pagination into json.
andrewthetechie@reddit
This feels a lot like some internal libraries I've seen at companies.
Looking over the code, I see it looks clean, and you've got some good tests. I would echo what some of the other commenters have said - it doesn't make sense for me to add this as a dependency to my project. This boilerplate isn't complicated enough that I want to introduce the extra effort of managing a dependency for it.
Pryther@reddit
I think, unless youre solving some common repetitive operation, a library like this is not a dependency most people will want in their project. Setting up an engine and basic pagination are not hard to do, and theres examples everywhere on how to do it well.
For me, making a CRUD router, simple search, filters, aggregation operations are usually the operations that i'd like to abstract away, not a single-time operation like injecting a connection to my db.
hadriendavid@reddit (OP)
Thanks for the feedback.
At my work, we have 6 FastAPI apps using postgres. The rationale of having a library to handle the DB configuration + basic utilities is to have a single way of configurating and paginating etc... Lastly, we only maintain one library rather than 6.
Have you checked FastCRUD?
Pryther@reddit
Yeah I have. I'm not saying I was looking for such a library, just saying that 'connect to a database' is not something most people want to have in some external dependency. For internal use it totally makes sense, for most developers here I don't think it would. More dependencies = more things you don't control that can fail.
hadriendavid@reddit (OP)
Indeed 😅 Also, will that dependency continue to be maintain in one year?
riwait@reddit
I’m not sure what problem you’re trying to solve, but since this is a boilerplate setup, I might only use it if it already has the reader-writer split in place—which it doesn’t, right?
hadriendavid@reddit (OP)
And the problem it solves mainly is DRY: write fastapi-sqlalchemy glue code once, use everywhere needed.
hadriendavid@reddit (OP)
Thanks for the feedback.
No it does not support read/write split at the moment. fastapi-sqla supports multi session, you may want to check it out.