Access an ApsaraDB RDS for SQL Server database - Function Compute

In Function Compute, you can use a function to call database APIs to insert or query data. In most cases, instance status is not shared among different execution environments in Function Compute. If you want to share instance status, you need to store structured data in a database. This topic describes how to deploy a function to access an ApsaraDB RDS for SQL Server database by using Serverless Devs. Python 3 is used in this topic.

Prerequisites

Create an ApsaraDB RDS for SQL Server instance
Important
- Make sure that the database instance that you create is in the same region as the function that needs to access the database instance.
- We recommend that you create the database instance in a zone that Function Compute supports. For more information about the message routing feature, see Zones where Function Compute is available.
  If your database instance is not deployed in a zone that is supported by Function Compute, create a vSwitch in your VPC. The vSwitch must be in the same zone as Function Compute. In addition, you must specify the vSwitch ID in the VPC configuration of the specified service in Function Compute. vSwitches in the same VPC can communicate with each other over the private network. Therefore, Function Compute can use the vSwitch to access resources in VPCs that reside in other zones. For more information, see How can I resolve the "vSwitch is in unsupported zone" error?.
A table named users is created.
In this example, the s init command is used to query all data in the users table in the database. You must create the users table in advance.

Step 1: Configure the IP address whitelist for the database

Note Accessing a database by using a security group results in unstable connections of functions. To ensure that your business runs as expected, we recommend that you configure an IP address whitelist to access the database.

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, click Whitelists and SecGroup.
On the Whitelist Settings tab, you can view the mode of the IP address whitelist.
Note Existing RDS instances may run in enhanced whitelist mode. All new RDS instances run in standard whitelist mode.
On the Whitelist Settings tab that appears, find the default whitelist group and click Modify.
In the Edit Whitelist dialog box, configure IP Addresses and click OK.
Important You must specify an IP address that belongs to the CIDR block of the vSwitch that you add when you configure the network for Function Compute.

Step 2: Deploy and invoke the function

Install Serverless Devs and Docker and add a key.
For more information, see Install Serverless Devs and Docker and Add a key.

Initialize a project.

s init start-fc-sql-server-python -d start-fc-sql-server-python

Access the project.
```
cd start-fc-sql-server-python
```

Edit the s.yaml file.

The following sample code provides a configuration example:

edition: 1.0.0          
name: fcDeployApp       
access: "default"  

services:
  fc-db-sql-server-python: 
    component: devsapp/fc  
    props: 
      region: cn-hangzhou
      service:
        name: fc-db-demo
        description: 'demo for fc visit db'
        internetAccess: true
        vpcConfig:
          vpcId: vpc-bp1hcg467ekqsv0zr**** # The ID of the virtual private cloud (VPC) in which the database instance resides. 
          securityGroupId: sg-bp1j6knvj40yjn0g**** # The ID of the security group. 
          vswitchIds:
            - vsw-bp1lgecrqfslgji9b**** # Make sure that the CIDR block of the vSwitch is added to the whitelist of the database instance. 
      function:
        name: sql_server
        description: visit sql server
        runtime: python3
        codeUri: ./code
        handler: index.handler
        memorySize: 256
        timeout: 60
        environmentVariables:
          MSSQL_SERVER: rm-bp1lbgwj1pw87****.sqlserver.rds.aliyuncs.com # The endpoint of the database. 
          MSSQL_PORT: 14** # The port of the database. 
          MSSQL_USER: sqlse**** # The username of the database. 
          MSSQL_PASSWORD: Hang**** # The password of the database. 
          MSSQL_DATABASE: ni**** # The name of the database.

Run the following command to build the project:
```
s build --use-docker
```
Run the following command to deploy the project:
```
s deploy -y
```

Run the following command to invoke a function:

s invoke -e "{}"

Expected command output:

[2021-09-22T22:43:24.266] [INFO ] [S-CLI] - Start ...
========= FC invoke Logs begin =========
FC Invoke Start RequestId: 2768263f-1fb4-4146-be78-a00a6d8e656e
2021-09-22T14:43:24.816Z 2768263f-1fb4-4146-be78-a00a6d8e656e [INFO] None
FC Invoke End RequestId: 2768263f-1fb4-4146-be78-a00a6d8e656e

Duration: 46.54 ms, Billed Duration: 47 ms, Memory Size: 256 MB, Max Memory Used: 38.30 MB
========= FC invoke Logs end =========

FC Invoke Result:
null


End of method: invoke

Sample code

Access a SQL Server database in a Python runtime of Function Compute