Create a Python function working with a PostgreSQL cluster

September 7, 2022 | 7 minute read
Tony Zhang
Master Principle Cloud Architect
Text Size 100%:

The Oracle Cloud Infrastructure (OCI) Functions service is a fully managed, multi-tenant, highly scalable, on-demand, functions-as-a-service platform. It’s built on enterprise-grade OCI and powered by the Fn Project open source engine. When the functions are deployed, you can call them directly or trigger them in response to events, and you’re billed only for the resources consumed during the run.

The serverless and elastic architecture of OCI Functions means you have no infrastructure administration or software administration to perform. Functions ensures that your app is highly available, scalable, secure, and monitored, and you can write code in Java, Python, Node, Go, Ruby, and C#. Advanced users can build functions directly from their own Docker file, which is especially helpful when you need to build functions with customized library requirements or want to build your functions based on a special Docker images, instead from the standard runtime.

PostgreSQL is one of the most widely used open source databases in the industry. OCI Compute provides better performance for PostgreSQL clusters than many other clouds. Python is a popular language for writing functions, and many independent software vendors (ISVs) build their cloud native solutions on Python and Postgres. Sometimes they need to migrate their functions from other clouds to OCI with corresponding support.

Python requires Psycopg2 library to connect to a Postgre database. Psycopg2 depends on libpq, which the default OCI Functions environment doesn’t include. So, we need to build the function from a Docker image with PostgreSQL library installed to allow our functions to connect to the PostgreSQL cluster.

This blog walks through the steps to build a Python function that can connect with a Postgres cluster. Because we’re building the foundation, the function only shows you how to connect to a Postgres database. From there, you can build your business logic. The environment can run on OCI’s Free Tier, so try it out!

Prerequisites

The tutorial requires some knowledge of Python and Docker. Refer to corresponding documents to start and have the following prerequisites:

  • Because we’re building our function from a Docker image, we need Docker Desktop running locally. For more information, refer to the Docker get started documentation.

  • Install the Fn project on your development environment. The steps of this tutorial are built on Mac OS and Linux. If your development environment is Windows, the installation steps are similar. After installation, you can use command "fn start" to start the Fn server to verify that Fn is properly installed.

  • Install and configure the OCI CLI.

  • Function development kit (FDK) is installed. FDK lets you write functions with Python.

  • Configure a compartment, virtual cloud network (VCN), and subnet to run the function.

  • Create an OCI registry and login. The registry stores the OCI Fn image.

  • A Postgres cluster running in the tenancy. I installed Postgres on an OCI instance.

  • Necessary policies, security groups, and route tables configured to allow the function to run in the tenancy

Create a function

Usually, we manage a function using the Fn CLI. An OCI function is a containerized function that FDK can run. However, the default function image doesn’t allow us to install any OS level libraries, such as the necessary libpg program that Python requires to access a Postgres cluster. So, we use a Linux image as a base image to create our own Docker image that packages FDK, Postgres, and Psycopg2 to support the function to access the backend Postgres database.

  1. Create a Python virtual environment to develop the function. In a working folder to store the function project, create a folder.

    mkdir pgdemo

    Then enable the newly created Python environment with the following command:

    python3 -m env pgdemo
    source pgdemo/bin/activate
  2. Create a context for Functions development environment. The context is a placeholder that describes the repository and compartment for the function. A proper configuration contains these directives. Your specific configuration is different, with your tenancy and compartment to be deployed.

    api-url: https://functions.us-phoenix-1.oci.oraclecloud.com
    
    oracle.compartment-id: ocid1.compartment.oc1..aaaaaaaauifmbfrwd34pswaqrjrwqh7ivhckdglrhvyrdt4mlkh2jcgxqfsq
    
    oracle.profile: DEFAULT
    
    provider: oracle
    
    registry: phx.ocir.io/orasenat/pgdemo
  3. Use your favorite editor to create the Docker file from which we build the Docker image.

    FROM oraclelinux:7-slim
    
    RUN  yum install -y oracle-softwarecollection-release-el7 && \
    
         yum -y install scl-utils rh-python38 && \
    
         scl enable rh-python38 bash
    
    RUN yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm && \
    
        yum install -y postgresql13 && \
    
         rm -rf /var/cache/yum
    
    ENV PATH="/opt/rh/rh-python38/root/usr/bin:/opt/rh/rh-python38/root/usr/local/bin:/usr/pgsql-13/bin:${PATH}"
    
    WORKDIR /function
    
    ADD . /function
    
    RUN pip3 install -r requirements.txt
    
    CMD fdk pgdemo.py handler
  4. Create the Python dependency file requirements.txt for the function. It includes the required Python libraries.

  5. Create the function metadata file, func.yml. The Fn CLI command depends on the func.yml file to deploy function to OCI. It also provides the entry point to allow FDK to bootstrap the function.

    schema_version: 20180708
    
    name: pgdemo
    
    version: 0.0.1
    
    runtime: docker
    
    entrypoint: python3 /function/pgdemo.py handler
    
    memory: 256
  6. Create the function. This function is for demo purposes only. It connects to the test database and retrieves the ID column in the test1 table.

    import logging
    
    import datetime
    
    import psycopg2 as pg
    
    import json
    
    import io
    
    from fdk import response
    
    PG_HOST="10.0.1.240" #Change to your server address or DNS name
    
    PG_PORT=5432
    
    PG_USER='pguser'
    
    PG_PASSWORD='pg1234'
    
    PG_DB = 'test'
    
    def handler(ctx,data: io.BytesIO=None):
    
        logging.getLogger().info("Start hander")
    
        return response.Response(ctx, response_data=json.dumps(
    
                {"message": "Postgres Function" ,
    
                "pg":pg_result()}),
    
            headers={"Content-Type": "application/json"}
    
        )
    
    
    def pg_result():
    
        result=[]
    
        conn = pg.connect(
    
            host=PG_HOST,
    
            port=PG_PORT,
    
            user=PG_USER,
    
            password= PG_PASSWORD,
    
            database= PG_DB)
    
        pg_cursor = conn.cursor()
    
        pg_cursor.execute("SELECT ID FROM TEST1")
    
        for row in pg_cursor:
    
            logging.getLogger().info(f'row value: {row}')
    
            result.append(row)
    
        conn.close()
    
        return result

Deploy and invoke the function

Now that we have all the codes ready, we can deploy the function to OCI and push the image to the registry. Here, we’re using OCI Registry (OCIR) to store the image.

  1. Log in to the registry. The OCIR is a regional resource. Use the region where you want to deploy. For example, you use the following command to log in into the private OCIR:

    docker login phx.ocir.io
  2. Create an application. An OCI function needs to belong to an application to exist. We can quickly create the application in the Oracle Cloud Console.

    A screenshot of the Create Application screen in the Oracle Cloud Console.

    You also can use the Fn command to create the application. To verify that the application was created properly, use the following command:

    fn list app

    The terminal displays the current app similar to the following example:

    “pgdemo ocid1.fnapp.oc1.phx.aaaaaaaa7wa4u657h2tlnqiiqxc3svcfmmbc63o4izjwdnxgz6awe4db7zma”
  3. Deploy this function and switch to the folder of the function. Because we already created a function meta file, we can use Fn CLI to deploy the function. The “fn deploy” command builds the function, tags the image, and pushes the image to OCIR.

    fn deploy --app pgdemo

    You can see similar outputs on the terminal.

    Bumped to version 0.0.2
    
    Building image phx.ocir.io/orasenatdpltintegration03/pgdemo/pgdemo:0.0.2 .......
    
    Parts:  [phx.ocir.io orasenatdpltintegration03 pgdemo pgdemo:0.0.2]
    
    Pushing phx.ocir.io/orasenatdpltintegration03/pgdemo/pgdemo:0.0.2 to docker registry...The push refers to repository [phx.ocir.io/orasenatdpltintegration03/pgdemo/pgdemo]
    
    758ef514ba41: Pushed 
    
    e1df353b4576: Pushed 
    
    ed69c4e08095: Layer already exists 
    
    f4f452bf3b05: Layer already exists 
    
    dbd8fc086680: Layer already exists 
    
    43038c62fc57: Layer already exists 
    
    0.0.6: digest: sha256:4c3317c49c47d325a260c8b9dcfd4366d630b91ad3a3a3d240b8a9d4750e9506 size: 1580
    
    Updating function pgdemo using image phx.ocir.io/orasenatdpltintegration03/pgdemo/pgdemo:0.0.2…
  4. Invoke the function. When the function is deployed successfully, you can invoke the function from the terminal with the following command:

    fn invoke pgdemo pgdemo

    We return the query from the Postgres database for the result. The table in the result for reference has two rows with ID 1 and 2.

    {"message": "Postgres Function", "pg": [[1], [2]]}

Congratulations! We created our own function that can call Postgres database successfully. Based on the guidance, you can implement your business logic using Postgres.

Conclusion

This blog described how to create an OCI function that requires third-party library support with Python. The Fn CLI can work with Docker images directly with proper function metadata. Although we use Postgres as an example, you can use this methodology for any customized Docker image to run as functions in Oracle Cloud Infrastructure.

For more information, see the following resources:

 

Tony Zhang

Master Principle Cloud Architect


Previous Post

Structure your OCI organization into groups for cost optimization and reporting: Part 3 of 3

Tim Tam | 4 min read

Next Post


Allowlisting by custom return-path

Dickie LaFlamme | 3 min read