Azure functions - Pooling dbconnection for snowflake python connector

Question

I have an azure functions application(python) which connects to snowflake database(creates a dbconnection object), executes query and returns the results and it has been working fine. The only challenge is time it takes to return the query. It creates the dbconnection object everytime when it is invoked.

Question: Is it possible to pool the connection using functions app ( considering the functions app is stateless)

Answer 1

The short answer is YES, here is my sample code of Azure Function with HTTP trigger as below.

import logging

import azure.functions as func

def inc(n):
    while True:
        n += 1
        yield n

count = inc(0)

def main(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Python HTTP trigger function processed a request.')

    return func.HttpResponse(f"Count: {count.__next__()}!")

I declared a global variable count with the value of a generator inc(0) outside the function main, then I can get an increased count response as the figure below.

It's the same as my another answer for the SO thread Azure use python flask framework for function app.

So I think it's indeed to realize a connection pool in Azure Functions, such as using snowflakedb/snowflake-sqlalchemy to create a connection pool for Snowflake database in Python outside the function main of Azure Function, as the code below by refering to the SQLAlchemy document Connection Pooling.

import logging
import azure.functions as func
from sqlalchemy import create_engine
import sqlalchemy.pool as pool

engine = create_engine(
    'snowflake://{user}:{password}@{account}/'.format(
        user='<your_user_login_name>',
        password='<your_password>',
        account='<your_account_name>',
    )
)

def getconn():
    connection = engine.connect()
    return connection

mypool = pool.QueuePool(getconn, max_overflow=10, pool_size=5)

def main(req: func.HttpRequest) -> func.HttpResponse:
    try:
        conn = mypool.connect()
        # Do the code what you want, such as
        # cursor = conn.cursor()
        # cursor.execute("select foo")
        ....
    finally:
        conn.close()
    return func.HttpResponse(f"......")

The explaination for the code above is that as below.

1. Azure Functions for Python start up to compile the source code from top to bottom and load its bytecode to memory, then the mypool object will be invoked from memory following the main function be triggered until mypool removed from memory after idle long time.
2. Next time to trigger the main function after idle, a new mypool object will be created and continous to do as above.