Create a Python function working with a PostgreSQL cluster

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.

◉ 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.

<codefdk>=0.1.41 psycopg2-binary=""></codefdk>=0.1.41>

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.

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.

Source: oracle.com