Azure Functions Python runtime V2 Model to use SQLAlchemy for ORM and use gcc, libpq, and other dependencies to interact with a PostgreSQL database

Question

Problem Statement:

In our application, we are utilizing Azure Functions to interact with a PostgreSQL database. We intend to use SQLAlchemy for ORM in Azure Functions. However, we are facing issues including necessary dependencies such as gcc, libpq, and others within the Azure Function Python runtime.

This interaction with the PostgreSQL database requires specific libraries, namely gcc and libpq, to be installed on the server end to enable the Azure Functions to communicate with the database, whether to retrieve or post data to our PostgreSQL tables.

Currently, we are facing a critical challenge as we are using consumption-based Azure Functions, which do not provide access to the ssh connect tool. This restriction prevents us from installing the necessary libraries on the server end, leading to the failure of our functions.

Considering an upgrade to a premium level Azure Function, where SSH access to the server is available, we encounter another issue. While this level allows us to install the gcc and libpq packages on the server end, these libraries are lost every time the server instance is rebooted. Consequently, our Azure Functions begin to fail again.

Current Challenges:

Consumption-based Azure Functions do not provide SSH access, preventing the installation of necessary libraries. Upgrading to a premium level allows for the installation of gcc and libpq, but they are lost after every server instance reboot.

Request for Assistance: We are looking for insights, guidance, or solutions from the Azure developer community that would enable us to install and maintain the required libraries in our Azure Function environment, thereby ensuring the seamless operation of our system. Any advice on how to integrate SQLAlchemy, along with managing the dependencies in the Azure Function Python runtime, would be immensely valuable.

Attempts to Resolve the Problem:

Switching to psycopg2-binary: We attempted to replace psycopg2 with psycopg2-binary in hopes of bypassing the need for system-level dependencies. Unfortunately, this did not resolve the issue.

Considering Different Azure Function Plans: We explored the possibility of upgrading to Azure Function Premium or Dedicated Plans, which could provide more control over the environment. This approach raised concerns about the persistence of the installed libraries across server reboots. Exploring Different Database Adapters: We also considered using a different database adapter that doesn't rely on the specific shared libraries we're having trouble with. However, this would require significant code changes and isn't a preferred solution. Impact: This situation creates a persistent problem, hindering our application's operation and our ability to manage data within our PostgreSQL database effectively.

Answer 1

@Yogesh Jain Thanks for reaching out and for the well-formatted post! Could you clarify why you needed to install the dependencies via SSH rather than defining them in the requirements.txt and have Azure install them?

• Package management

It seems that SQLAlchemy should work, per: https://learn.microsoft.com/en-us/answers/questions/568041/sqlalchemy-incompatible-with-python-azure-function

Answer 2

Hello, while I’m unable to provide exact solutions without investigating the code, design, and Azure resources directly, I would suggest looking into this article https://iotespresso.com/using-psycopg2-in-azure-functions/ In this specific case, you are better off using the alternative library that does not require you to be compiling C code on the function app. This way you can just install the dependencies when you deploy your function app, and they will persist through restarts since they are included in the files you upload to Azure. This will mean you wont have to manually SSH into your function app and you wont have to use Premium. I hope this proves helpful, Cheers, Yogesh