Connect to AlloyDB for PostgreSQL using Cloud Functions

Published in

Google Cloud - Community

7 min readApr 5, 2023

In this blog we will see how we can connect to AlloyDB using Cloud Functions. As we know that Cloud Function is a serverless compute service, it requires some additional configuration to access private VPC resources such as an AlloyDB instance or a private Cloud SQL instance. We will use Serverless VPC Access Connector to establish a connection between AlloyDB instance and the Cloud Function.

Before we jump into the implementation, first lets understand some basic information about AlloyDB, Cloud Function and VPC Serverless Access Connectors.

What is AlloyDB ?

AlloyDB is a fully managed, PostgreSQL-compatible database service that offers enterprise-grade performance, availability, and scale.

AlloyDB offers a number of features that make it a good choice for demanding transactional and analytical workloads, including:

High performance: AlloyDB is designed to be highly performant for both transactional and analytical workloads. It uses a number of techniques to achieve this, including a shared-nothing architecture, columnar storage, and vectorized execution.
High availability: AlloyDB offers a 99.99% uptime SLA, inclusive of maintenance. It uses a number of techniques to achieve this, including synchronous replication, automatic failover, and hot backups.
Scale: AlloyDB can scale to meet the needs of even the most demanding applications. It can be scaled up to support up to 128 vCPUs and 1 TB of memory per instance, and it can be scaled out to support up to 100 instances per cluster.
Security: AlloyDB is designed with security in mind. It uses a number of features to protect your data, including encryption, auditing, and access control.
Compliance: AlloyDB is designed to meet the needs of organisations with demanding compliance requirements. It supports a number of compliance standards, including HIPAA, PCI DSS, and SOC 2.

What is Cloud Function ?

Cloud Function is a lightweight compute solution for developers to create single-purpose, stand-alone functions that respond to Cloud events without needing to manage a server or runtime environment.

What are Serverless VPC Connectors ?

A Serverless VPC Access connector is a tool that enables you to connect your serverless environment to your Virtual Private Cloud (VPC) network. This allows you to access Compute Engine virtual machine (VM) instances, Memorystore instances, and any other resources with an internal IP address.

To use a Serverless VPC Access connector, you first need to create a connector in your Google Cloud project. You then need to attach the connector to a specific VPC network and region. Finally, you need to configure your serverless environment to use the connector.

What you’ll learn ?

How to access the Cloud Functions web UI in the Google Cloud Console.
How to create a Cloud Function.
How to access database instances created in a VPC from Cloud Functions.
How to create Serverless VPC Connectors.
How to test a Cloud Function.
How to connect to an AlloyDB database instance (either primary or read pool instance) using Python.

Requirements

A browser, such as Chrome or Firefox.
A Google Cloud Platform project that contains your AlloyDB cluster and a primary / read pool instance.
Your instance contains a database with a table.
Your instance’s private IP address, the database and table names, the database user name and the user’s password.
A Service account with the AlloyDB Client role.

In the Console UI, navigate to the IAM & Admins > IAM page.
You can use the default Compute Engine service account. It has the suffix compute@developer.gserviceaccount.com.
Select Edit using the pencil icon.
Click ADD ANOTHER ROLE and add AlloyDB > Client.
Click ADD ANOTHER ROLE and add Secret Manager > Secret Accessor.
Click SAVE.

6. A VPC Serverless Connector which is connected to the VPC where AlloyDB instance is present.

Search for “Serverless VPC access” in the Google Cloud Console.
Click on CREATE CONNECTOR
Provide a name to the connector (and take a note), it will be required while configuring the Cloud Function.
Select Region.
Select the VPC network.
Provide a range of IP addresses which will be used by the connectors. For example, 10.0.0.0/28
Click on Create and wait till it appears in the dashboard with the green tick.

7. Create secrets mentioned in the next section using following process

Search for “Secret Manger” in the Google Cloud Console.
Click on CREATE Secret
Add Secret Name
Add Secret Value
Click on Create and wait till it appears in the dashboard with the green tick.

Create the function by using below mentioned steps

Create secrets for storing project and database credentials with the following names

alloydb-secrets-dev-database : Store database name into this secret
alloydb-secrets-dev-host : Store private IP address of the instance into this secret
alloydb-secrets-dev-username : Store username of the database user into this secret
alloydb-secrets-dev-password : Store password of the database user into this secret

Create the function

In a browser, go to the Google Cloud Platform Console UI.
Select Cloud Functions from the Navigation menu.
Click CREATE FUNCTION on the button bar.
Enter a name for the function.
Select the HTTP trigger. (Make a note of the URL displayed beneath the trigger item. It will be in this format: https://REGION-PROJECT_ID.cloudfunctions.net/FUNCTION_NAME)
Under Authentication, select Allow unauthenticated invocations to make the function public.
Expand the Runtime, Build and Connections Settings In Runtime service account, select a service account that has the AlloyDB Client role.
Under Connections, select the VPC connector created earlier.
Click the NEXT button.
Select Python 3.8 for the runtime option.
Select Inline editor for the source code option.
SelIn the source code editor windows, delete the existing content for both requirements.txt and main.py, and replace them with your edited versions of the code above.
Enter main as the name of the Entry point.

Create the following Cloud Function Environment Variables

user-secret : alloydb-secrets-dev-username
pass-secret : alloydb-secrets-dev-password
project-id : <project-id>
version : 1
database-nm : alloydb-secrets-dev-database
database-host : alloydb-secrets-dev-host

Prepare the code

The Cloud Function code for connecting to an AlloyDB database is right here.

The Cloud Functions UI in the Cloud Console includes a text editor. You can copy/paste and edit the code there, or edit the code locally first, and then copy/paste it into the UI.

requirements.txt

# This file tells Python which modules it needs to import
psycopg2==2.9.5
google-cloud-secret-manager==2.16.1

main.py

# This file will get executed when the function is executed
from alloydb_utility import AlloyDB


def main(request):
    request_json = request.get_json()
    query_type = request_json["query_type"]
    try:
        alloydb_obj = AlloyDB()
        if query_type == "select":
            result = alloydb_obj.select_query(
                """SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';"""
            )
            print(result)
        return "Task Completed Successfully"
    except Exception as e:
        print(e)
        return e

alloydb_utility.py

import psycopg2
import json
import os
from google.cloud import secretmanager


class AlloyDB:
    def __init__(self):

        self.user = self.get_secret_data(
            os.environ["project-id"], os.environ["user-secret"], os.environ["version"]
        )
        self.password = self.get_secret_data(
            os.environ["project-id"], os.environ["pass-secret"], os.environ["version"]
        )
        self.host = self.get_secret_data(
            os.environ["project-id"], os.environ["database-host"], os.environ["version"]
        )
        self.database_nm = self.get_secret_data(
            os.environ["project-id"], os.environ["database-nm"], os.environ["version"]
        )
        self.port = "5432"
        self.cursor = self._connection()

    def get_secret_data(self, project_id, secret_id, version_id):

        client = secretmanager.SecretManagerServiceClient()
        secret_detail = (
            f"projects/{project_id}/secrets/{secret_id}/versions/{version_id}"
        )
        response = client.access_secret_version(request={"name": secret_detail})
        data = response.payload.data.decode("UTF-8")
        return data

    def _connection(self):

        try:
            conn = psycopg2.connect(
                database=self.database_nm,
                user=self.user,
                password=self.password,
                host=self.host,
                port=self.port,
            )
            cursor = conn.cursor()
            print(f"Connection Established with {self.host}")
            return cursor
        except Exception as e:
            print(e)
            print(f"Exception {e} occured while connecting to {self.host}")

    def select_query(self, query):

        self.cursor.execute(query)
        result_data = self.cursor.fetchall()
        return result_data

Click Deploy and wait while the function is created. The spinner stops spinning and a green check appears on the subsequent page when the function is ready to use.

Test the function

In a browser, go to the Google Cloud Platform Console UI.
Select Cloud Functions from the Navigation menu.
Click on the name of the function you created earlier.
Select the TESTING option which is the last option present in the options.

Pass {“query_type”:”select”} .
Select TEST THE FUNCTION.

The result should appear: “Task Completed Successfully” (If the test fails, you’ll see a stack trace to help with debugging.)

We successfully made a connection from Cloud Function to AlloyDB. I hope you learnt how we can access VPC resources from serverless compute service like Cloud Function.

Thanks for reading !

Additional References :