Function Compute:Best practices for accessing RDS PostgreSQL

Step 1: Set up database whitelist

Step 2: Access PostgreSQL database in the function

1. Log on to the Function Compute console, find the target function in the function list, on the function details page, click the Code tab, and write the following sample code in the code editor according to the database table.

   from flask import Flask, jsonify
   import psycopg2  # Need to install psycopg2 library first
   import os
   import sys
   from datetime import datetime
   import logging
   
   
   app = Flask(__name__)
   
   # Global variable for storing PostgreSQL singleton connection
   _postgresql_connection = None
   
   # Create database connection (singleton pattern)
   def getConnection():
       global _postgresql_connection
       try:
           # If the connection already exists and is not disconnected, return it directly
           if _postgresql_connection is not None:
               try:
                   # Test if the connection is valid (simple command test)
                   with _postgresql_connection.cursor() as cursor:
                       cursor.execute("SELECT 1")  # Simple query to test connection status
                       result = cursor.fetchone()
                       if result and result[0] == 1:
                           return _postgresql_connection
               except psycopg2.OperationalError:
                   # If the connection is disconnected, reset it
                   _postgresql_connection = None
   
           # If the connection does not exist or is disconnected, recreate it
           _postgresql_connection = psycopg2.connect(
               database=os.environ['DATABASE'],
               user=os.environ['USER'],
               password=os.environ['PASSWORD'],
               host=os.environ['HOST'],
               port=os.environ['PORT'],
           )
           return _postgresql_connection
       except Exception as e:
           print(f"ERROR: Unexpected error: Could not connect to PostgreSQL instance: {e}")
           raise
   
   
   @app.route('/', defaults={'path': ''})
   @app.route('/<path:path>', methods=['GET', 'POST', 'PUT', 'DELETE'])
   def hello_world(path):
       conn = getConnection()
       try:
           with conn.cursor() as cursor:
               # Query all records in the users table, modify users table according to the actual table name
               sql = "SELECT * FROM users"
               cursor.execute(sql)
               result = cursor.fetchall()
               
               columns = [desc[0] for desc in cursor.description]  # Get the list of field names
   
               # Convert query results to a list of dictionaries
               users = []
               for row in result:
                   user = {}
                   for idx, column_name in enumerate(columns):
                       value = row[idx]
                       if isinstance(value, datetime):  # Handle date type fields
                           user[column_name] = value.strftime('%Y-%m-%d %H:%M:%S')
                       else:
                           user[column_name] = value
                   users.append(user)
                   
               if users:
                   # Return JSON response for all users
                   return jsonify(users), 200
               else:
                   # If no users are found, return 404 error
                   return jsonify({'error': 'No users found'}), 404
   
       except Exception as e:
           logging.error(f"Error occurred during database operation: {e}")
           return jsonify({'error': 'Database error'}), 500
   
   
   if __name__ == '__main__':
       app.run(host='0.0.0.0', port=9000)
   

2. Install the required dependencies for the function: pip3 install -t . psycopg2 , click Deploy Code to make the dependencies take effect. For detailed steps, see Install dependencies through the console Web IDE terminal.

3. On the Function Details page, choose Configuration > Environment Variables, click Edit, and configure the following environment variables in the Environment Variables panel.

   Environment Variable Name	Environment Variable Value	Description
   HOST	pgm-*****q.pg.rds.aliyuncs.com	The access address of the database instance. If you choose the PostgreSQL database within the same VPC scenario, set this environment variable value to the internal network address of the database. If you choose the Access PostgreSQL database across VPCs or regions scenario, set this environment variable value to the external network address of the database. Visit Instance List, select a region at the top, and then click the target instance ID. In the left-side navigation pane, click Database Connection, and in the Database Connection information area, obtain the connection address information for connecting to the database.
   PORT	5432	Database internal network port number
   USER	*****	The account name created in the PostgreSQL instance.
   PASSWORD	*****	Database Password
   DATABASE	*****	The database name created in the instance.

4. On the function details page, select the Code tab, click Test Function, and view the return result after successful execution.