Interact with a PostgreSQL database using pythonic functions

Requirements

This example assumes that you are familiar with some products of Scaleway's ecosystem:

• how serverless functions work. If needed, you can check Scaleway official documentation.
• how a managed database for PostgreSQL works, and especially how to create a database and create users with appropriate permissions. Please refer to scaleway's documentation here.

This example uses the Scaleway Serverless Framework Plugin. Please set up your environment with the requirements stated in the Scaleway Serverless Framework Plugin before trying out the example.

Context

This example shows how to connect to a managed PostgreSQL database and perform a query on it. This example can be extended to adding, deleting, or processing data within a database.

Description

The function connects to a PostgreSQL database and performs an example query on it. This example uses Python 3.10 runtime. Used packages are specified in package.json. As a requirement for this example we use a pre-compiled psycopg2 version.

Testing with serverless offline for Python

In order to test your function locally before deployment in a serverless function, you can install our python offline testing library with:

pip install -r requirements-dev.txt

Import your environment variables using:

export PG_HOST="your host IP address" PG_USER="your database username" PG_DATABASE="your database name" PG_PASSWORD="your database user password" PG_PORT="your database port"

Launch your function locally:

python handlers/handler.py

Test your local function using curl:

curl localhost:8080

Setup

Create a managed PostgreSQL database

Create a PostgreSQL database and a user profile with appropriate access permissions.

Fill environment variables

Fill your secrets within serverless.yml file:

secret:
    PG_HOST: "your host IP address"
    PG_USER: "your database username"
    PG_DATABASE: "your database name"
    PG_PASSWORD: "your database user password"
    PG_PORT: "your database port"

Install npm modules

Once your environment is set up, you can install npm dependencies from package.json file using:

npm install

Generate Python package folder from requirements.txt modules

Verify that you have the right Python runtime version in your /bin/deps.sh script as in your serverless.yml. Then you can use:

./bin/deps.sh

Deploy and call the function

Then deploy your function and dependencies using:

./bin/deploy.sh

From the given function URL, you can run your function using:

curl <function URL>

Then you can check your database using psql tool:

psql -h $PG_HOST --port $PG_PORT -d $ -U $PG_DATABASE -W $PG_PASSWORD