tencent cloud

Overview

Currently, TDSQL-C for MySQL supports serverless billing. In this billing mode, the service is billed based on the actual computing and storage usage which is calculated by second and settled by hour. The TDSQL-C component of Serverless Framework also supports creating this type of databases.

This document uses a function written in Node.js as an example to describe how to quickly create a TDSQL-C for MySQL serverless instance and call it in SCF.

Directions

Step 1. Configure environment variables

1. Create a local directory to store code and dependent modules. This document uses the test-MySQL folder as an example.

   mkdir test-MySQL && cd test-MySQL
   

2. Currently, TDSQL-C Serverless only supports four regions: ap-beijing-3, ap-guangzhou-4, ap-shanghai-2, and ap-nanjing-1, so you need to create the .env file in the project root directory and then configure the two environment variables REGION and ZONE:

   # .env
   REGION=xxx  
   ZONE=xxx 
   

Step 2. Configure a VPC

1. Create a VPC folder in the test-MySQL directory.

   mkdir VPC && cd VPC
   

2. Create a serverless.yml file in VPC and use the VPC component to create the VPC and subnet.
   The sample content of serverless.yml is as follows (for all configuration items, please see the product documentation):

   #serverless.yml
   app: mysql-app
   stage: dev
   component: vpc # (required) name of the component. In that case, it's vpc.
   name: mysql-app-vpc # (required) name of your vpc component instance.
   inputs:
       region: ${env:REGION}
       zone: ${env:ZONE}
       vpcName: serverless-mysql
       subnetName: serverless-mysql

Step 3. Configure Serverless DB

1. Create a DB folder in test-MySQL.

2. Create a serverless.yml file in the DB folder and enter the following content to use the Serverless Framework component to configure the TCB environment:
   The sample content of serverless.yml is as follows (for all configuration items, please see the product documentation):

   # serverless.yml 
   app: mysql-app
   stage: dev
   component: cynosdb
   name: mysql-app-db
   inputs:
     region: ${env:REGION}
     zone: ${env:ZONE}
     vpcConfig:
       vpcId: ${output:${stage}:${app}:mysql-app-vpc.vpcId}
       subnetId: ${output:${stage}:${app}:mysql-app-vpc.subnetId}

Step 4. Write the business code and configuration file

1. Create an src folder in test-MySQL to store the business logic code and relevant dependencies.

2. Create an index.js file in the src folder and enter the following sample code, so that you can use the SDK to connect to the MySQL database through the function and call the database in the environment:

   exports.main_handler = async (event, context, callback) => {
       var mysql      = require('mysql2');
       var connection = mysql.createConnection({
       host     : process.env.HOST,
       user     : 'root',
       password : process.env.PASSWORD
       });
       connection.connect();
       connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
       if (error) throw error;
       console.log('The solution is: ', results[0].solution);
       });
       connection.end();
    }

3. Install the required dependent modules.

   npm install mysql2
   

4. After writing the business code and installing the dependencies, create a serverless.yml file as shown below:

   app: mysql-app
   stage: dev
   component: scf
   name: mysql-app-scf
   
   inputs:
     src: ./
     functionName: ${name}
     region: ${env:REGION}
     runtime: Nodejs10.15
     timeout: 30
     vpcConfig:
       vpcId: ${output:${stage}:${app}:mysql-app-vpc.vpcId}
       subnetId: ${output:${stage}:${app}:mysql-app-vpc.subnetId}
     environment:
       variables:
         HOST: ${output:${stage}:${app}:mysql-app-db.connection.ip}
         PASSWORD: ${output:${stage}:${app}:mysql-app-db.adminPassword}

Step 5. Deploy

After the creation, the project directory structure is as follows:

  ./test-MySQL
  ├── vpc
  │   └── serverless.yml # VPC configuration file
  ├── db
  │   └── serverless.yml # Database configuration file
  ├── src
  │   ├── serverless.yml # SCF component configuration file
  │   ├── node_modules # Project dependency file
  │   └── index.js # Entry function
  └── .env # Environment variable file

1. Run the following command for deployment in test-MySQL on the command line:

   sls deploy
   

   Note：

   • During deployment, you need to scan the QR code to authorize. If you don't have a Tencent Cloud account yet, please sign up first.
   • If your account is a sub-account, please get the authorization first as instructed in Account and Permission Configuration.

   If the following result is returned, the deployment is successful:

   mysql-app-vpc: 
     region:        xxx
     zone:          xxx
     vpcId:         xxxx-xxx
     ...
   
   mysql-app-db: 
     dbMode:        xxxx
     region:        xxxx
     zone:          xxxx
     ...
   
   mysql-app-scf: 
     functionName:  xxxx
     description:   xxx
     ...
   
   59s › test-MySQL › "deploy" ran for 3 apps successfully.

2. After the deployment succeeds, you can view and debug the function in the SCF console.

Step 6. Remove the project (optional)

Run the following command in the test-MySQL directory to remove the project:

sls remove

If the following result is returned, the removal is successful:

serverless ⚡ framework
4s › test-MySQL › Success

Sample Code

Python

In Python, you can use the built-in pymysql dependency package in the SCF environment to connect to the database. The sample code is as follows:

# -*- coding: utf8 -*-
from os import getenv

import pymysql
from pymysql.err import OperationalError

mysql_conn = None

def __get_cursor():
    try:
        return mysql_conn.cursor()
    except OperationalError:
        mysql_conn.ping(reconnect=True)
        return mysql_conn.cursor()

def main_handler(event, context):
    global mysql_conn
    if not mysql_conn:
        mysql_conn = pymysql.connect(
        host        = getenv('DB_HOST', '<your db="" host="">'),
        user        = getenv('DB_USER','<your db="" user="">'),
        password    = getenv('DB_PASSWORD','<your db="" password="">'),
        db          = getenv('DB_DATABASE','<your db="" database="">'),
        port        = int(getenv('DB_PORT','<your db="" port="">')),
        charset     = 'utf8mb4',
        autocommit  = True
        )
    
    with __get_cursor() as cursor:
        cursor.execute('select * from employee')
        myresult = cursor.fetchall()
        print(myresult)
        for x in myresult:
            print(x)

Node.js

Node.js allows you to use a connection pool for connection, which supports automatic reconnection to effectively avoid connection unavailability due to connection release by the SCF underlying layer or database. The sample code is as follows:

Note：

Before using a connection pool, you need to install the mysql2 dependency package first. For more information, please see Dependency Installation.

'use strict';

const DB_HOST       = process.env[`DB_HOST`]
const DB_PORT       = process.env[`DB_PORT`]
const DB_DATABASE   = process.env[`DB_DATABASE`]
const DB_USER       = process.env[`DB_USER`]
const DB_PASSWORD   = process.env[`DB_PASSWORD`]

const promisePool = require('mysql2').createPool({
  host              : DB_HOST,
  user              : DB_USER,
  port              : DB_PORT,
  password          : DB_PASSWORD,
  database          : DB_DATABASE,
  connectionLimit   : 1
}).promise();

exports.main_handler = async (event, context, callback) => {
  let result = await promisePool.query('select * from employee');
  console.log(result);
}

PHP

In PHP, you can use the pdo_mysql or mysqli dependency package for data connection. The sample code is as follows:

• pdo_mysql

  <?php
  function handler($event, $context) {
  try{
  $pdo = new PDO('mysql:host= getenv("DB_HOST");dbname= getenv("DB_DATABASE"),getenv("DB_USER"),getenv("DB_PASSWORD")');
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  }catch(PDOException $e){
  echo 'Databases connection failed: '.$e->getMessage();
  exit;
  }
  }
  

• mysqli

  <?php
  function main_handler($event, $context) {
   $host = "";
  $username = "";
  $password = "";
    // Create a connection
  $conn = mysqli_connect($servername, $username, $password);
   // Test the connection
  if (!$conn) {
      die("Connection failed: " . mysqli_connect_error());
      }
  echo "Connected successfully"; 
   mysqli_close($conn);
  echo "Disconnected"; 
  }
  ?>
  

Java

1. Please install the following dependencies as instructed in Dependency Installation.

   <dependencies>
       <dependency>
           <groupid>com.tencentcloudapi</groupid>
           <artifactid>scf-java-events</artifactid>
           <version>0.0.2</version>
       </dependency>
       <dependency>
           <groupid>com.zaxxer</groupid>
           <artifactid>HikariCP</artifactid>
           <version>3.2.0</version>
       </dependency>
       <dependency>
           <groupid>mysql</groupid>
           <artifactid>mysql-connector-java</artifactid>
           <version>8.0.11</version>
       </dependency>
   </dependencies>

2. Use HikariCP for connection. The sample code is as follows:

   package example;
   
   import com.qcloud.scf.runtime.Context;
   import com.qcloud.services.scf.runtime.events.APIGatewayProxyRequestEvent;
   import com.qcloud.services.scf.runtime.events.APIGatewayProxyResponseEvent;
   import com.zaxxer.hikari.HikariConfig;
   import com.zaxxer.hikari.HikariDataSource;
   
   import javax.sql.DataSource;
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.util.HashMap;
   import java.util.Map;
   
   public class Http {
       private DataSource dataSource;
   
       public Http() {
           HikariConfig config = new HikariConfig();
           config.setJdbcUrl("jdbc:mysql://" + System.getenv("DB_HOST") + ":"+ System.getenv("DB_PORT") + "/" + System.getenv("DB_DATABASE"));
           config.setUsername(System.getenv("DB_USER"));
           config.setPassword(System.getenv("DB_PASSWORD"));
           config.setDriverClassName("com.mysql.jdbc.Driver");
           config.setMaximumPoolSize(1);
           dataSource = new HikariDataSource(config);
       }
       
       public String mainHandler(APIGatewayProxyRequestEvent requestEvent, Context context) {
           System.out.println("start main handler");
           System.out.println("requestEvent: " + requestEvent);
           System.out.println("context: " + context);
       
           try (Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement("SELECT * FROM employee")) {
               ResultSet rs = ps.executeQuery();
               while (rs.next()) {
                   System.out.println(rs.getInt("id"));
                   System.out.println(rs.getString("first_name"));
                   System.out.println(rs.getString("last_name"));
                   System.out.println(rs.getString("address"));
                   System.out.println(rs.getString("city"));
               }
           } catch (SQLException e) {
               e.printStackTrace();
           }
       
           APIGatewayProxyResponseEvent apiGatewayProxyResponseEvent = new APIGatewayProxyResponseEvent();
           apiGatewayProxyResponseEvent.setBody("API GW Test Success");
           apiGatewayProxyResponseEvent.setIsBase64Encoded(false);
           apiGatewayProxyResponseEvent.setStatusCode(200);
       
           Map<string, string=""> headers = new HashMap&lt;&gt;();
           headers.put("Content-Type", "text");
           headers.put("Access-Control-Allow-Origin", "*");
           apiGatewayProxyResponseEvent.setHeaders(headers);
       
           return apiGatewayProxyResponseEvent.toString();
       }
   }

SCF DB SDK for MySQL

For ease of use, the SCF team encapsulated the code related to connection pools in Node.js and Python as SCF DB SDK for MySQL. Please refer to Dependency Installation for installation and use. With this SDK, you can connect to MySQL, TDSQL-C, or TDSQL for MySQL databases and performs operations such as insertion and query.

SCF DB SDK for MySQL has the following features:

• It can automatically initialize the database client from environment variables.
• It can maintain a persistent database connection globally and handle reconnection after disconnection.
• The SCF team will continuously check issues to ensure that the database connection is available, so you don't need to pay attention to connection issues.

1. SDK for Node.js

'use strict';
const database = require('scf-nodejs-serverlessdb-sdk').database;

exports.main_handler = async (event, context, callback) => {
  let pool = await database('TESTDB2').pool()
  pool.query('select * from coffee',(err,results)=>{
    console.log('db2 callback query result:',results)
  })
  // no need to release pool

  console.log('db2 query result:',result)
}

Note：

For specific usage of the SDK for Node.js, please see SCF DB SDK for MySQL.

2. SDK for Python

from serverless_db_sdk import database

def main_handler(event, context):
    print('Start Serverlsess DB SDK function')

    connection = database().connection(autocommit=False)
    cursor = connection.cursor()
    
    cursor.execute('SELECT * FROM name')
    myresult = cursor.fetchall()
    
    for x in myresult:
        print(x)