SQLAlchemy and Alembic Support

[Red-Hammer]

Hello! I work a lot with Flask and I love its easy integration with SQLAlchemy and Alembic. Are there any future plans to integrate those or any other ORM/Migration packages with Blacksheep?

Thanks!

[RobertoPrevato]

Hi @Red-Hammer!
I love that you are asking this question, because in the last days I created my first service using my web framework (please take a look at this tweet I wrote a few days ago),
and I used Alembic for DB migrations on PostgreSQL - with deployments automated using GitHub Workflows.
I will soon publish a project template with this feature in place: I think you should find this project template interesting, it also includes a very cool logging of DB queries performance using OpenCensus and Azure Application Insights.

I propose to do this way:

• let me first publish my project template (it will happen in a few days' time)
• then I will ping you on this thread, to ask your feedback and recommendations on what could be done, in addition to the things I will share in the project template

Would you have time for that?

[RobertoPrevato]

For the record, and to better answer the original question, yes I am definitely going to prepare a library to integrate with SQLAlchemy. Integration with Alembic is not necessary in my opinion because the alembic CLI already provides everything needed to handle migrations, which by the way should be handled in CD pipelines and not by the application server - the application server shouldn't even have access to a connection string with sufficient privileges to run DB migrations in the first place! Side note: I am also working on a demo project that applies db migrations using GitHub Actions, see Venezia.

At a basic level, the integration with SQLAlchemy will enable the following:

• configuration of dependency injection for ORM Sessions and raw db connections, so they can easily be injected to request handlers or dependent services down the stack (e.g. business layer using the data access layer)
• proper configuration of services at application start and stop (e.g. if connection pools are used, they should be initialized at application start and gracefully disposed at application stop event)

[RobertoPrevato]

Development here -> https://github.com/Neoteroi/BlackSheep-SQLAlchemy

[Red-Hammer]

@RobertoPrevato This is awesome! I agree that alembic integration isn't necessary. Thank you for answering so quickly and thoroughly!

I'll for sure go and take a look at the new project, and see if I can provide any useful feedback/suggestions.

[RobertoPrevato]

@Red-Hammer I took your question seriously, in fact I already prepared a first library to simplify the integration with SQLAlchemy!

I just published it to PyPi -> https://pypi.org/project/blacksheep-sqlalchemy/

For now it's basic, as it registers the necessary services to:

• registers services to the DI container so that db connection, sessions, and the engine, so that they can be injected to request handlers (or dependent services) - there is a full example here
• dispose of the SQLAlchemy engine when the application stops

I thought we could also a CLI to generate automatically the code for basic CRUD API. I think the code in such case should be generated, so that the user can apply authorization logic depending and other changes as needed.