Scaleway Documentationtutorials
connect function postgresql database

Jump toUpdate content

Connecting a Serverless Function to a PostgreSQL database

Reviewed on 25 October 2021Published on 25 October 2021
  • serverless
  • functions
  • database
  • postgresql
  • sql

Overview

In this tutorial, you learn the steps required to connect to a PostgreSQL Database to a Serverless Function using NodeJs or Python.

To achieve this, we use a Serverless Function and the following elements:

Requirements:

NodeJS

Tip:

If you do not know how to configure your package, refer to our documentation about packaging functions in zip files.

  1. Install the NodeJS pg dependency:

    npm i pg
  2. Create a handler.js file at the root of your folder using touch, then open it in a text editor such as nano:

    touch handler.js
    nano handler.js
  3. Copy the following code into the file to connect to the database. You can pass the required information using environment variables:

    const pg = require('pg')

    //Get Database info from environment variable
    const PGHOST=process.env.PGHOST;
    const PGUSER=process.env.PGUSER;
    const PGDATABASE=process.env.PGDATABASE;
    const PGPASSWORD=process.env.PGPASSWORD;
    const PGPORT=parseInt(process.env.PGPORT,10);

    const pool = new pg.Pool({
    user: PGUSER,
    host: PGHOST,
    database: PGDATABASE,
    password: PGPASSWORD,
    port: PGPORT
    })

    • Add a specific function to the handler.js file to handle querying errors:
    //Creating a function to handle queries errors
    async function query (q) {
    const client = await pool.connect()
    let res
    try {
    await client.query('BEGIN')
    try {
    res = await client.query(q)
    await client.query('COMMIT')
    } catch (err) {
    await client.query('ROLLBACK')
    throw err
    }
    } finally {
    client.release()
    }
    return res
    }
    • Finally, add the handle function to the file:
    module.exports.handle = async (event, context, callback) => {
    try {
    const { rows } = await query("SELECT CONCAT(first_name,' ', last_name) AS \"Full Name\" FROM actor LIMIT 10")
    console.log(JSON.stringify(rows[0]))
    var response = {
    "statusCode": 200,
    "headers": {
    "Content-Type" : "application/json"
    },
    "body": JSON.stringify(rows),
    "isBase64Encoded": false
    };
    callback(null, response);
    } catch (err) {
    //handling errors
    console.log('Database ' + err)
    callback(null, 'Database ' + err);
    }
    };
  4. Create the function in the Scaleway console, then upload your zipped package and add the environment variables in the Advanced section of the function.

    Tip:

    Alternatively use the serverless framework to create the function and upload your code automatically.

Python

Tip:

If you do not know how to configure your package, refer to our documentation about packaging functions in zip files.

Note:
The Python libraries to connect to PostgreSQL require the postgresql Linux library. [You will need to use Docker to generate the dependencies.](/compute/functions/how-to/package-function-in-zip/#specific-libraries-(with-needs-for-specific-c-compiled-code))
  1. Generate the dependencies for psycopg2-binary using our dedicated image (psycopg2 needs the postgresql linux library to be compiled):

    docker run --rm -v $(pwd):/home/app/function --workdir /home/app/function rg.fr-par.scw.cloud/scwfunctionsruntimes/python-dep3:v1.2.8 pip install psycopg2-binary --target ./package
  2. Create a handler.py file at the root of your folder using touch, then open it in a text editor, for example nano:

    touch handler.py
    nano handler.py
    • Copy the following code to connect to the database. You can pass the required information using the environment variables of your function:
    import psycopg2
    from psycopg2 import Error
    import os

    #Get Database info from environment variable
    PGHOST=os.environ.get('PGHOST')
    PGUSER=os.environ.get('PGUSER')
    PGDATABASE=os.environ.get('PGDATABASE')
    PGPASSWORD=os.environ.get('PGPASSWORD')
    PGPORT=int(os.environ.get('PGPORT'))

    # Connect to database
    try:
    connection = psycopg2.connect(user=PGUSER,
    host=PGHOST,
    database=PGDATABASE,
    password=PGPASSWORD,
    port=PGPORT
    )
    except (Exception, Error) as error:
    print("Error while connecting to PostgreSQL", error)

    • Create a cursor in the handle function to submit queries and fetch the result:
    def handle(event, context):
    try:
    # Create a cursor to perform database operations
    cursor = connection.cursor()
    print("Connected to Database")
    # Execute a query
    cursor.execute("SELECT CONCAT(first_name,' ', last_name) AS \"Full Name\" FROM actor LIMIT 10")
    # Get result
    record = cursor.fetchone()
    print("Succesfully fetch data")
    #Closing cursor, howver we keep the connection alive to enable faster loading for the next function calls
    cursor.close()
    return {
    "statusCode": 200,
    "body": {
    "message": record
    }
    }
    except (Exception, Error) as error:
    return {
    "statusCode": 500,
    "body": {
    "message": "Error while getting information from PostgreSQL, check logs for more information"
    }
    }
  3. Create the function on the Scaleway console, then upload your zipped package and add the environment variable in the advanced settings of the function.

    Tip:

    Alternatively use the Serverless Framework to create the function and upload your code automatically.