MySQL Database Integration

Overview

CloudBase provides multiple methods to connect to and operate MySQL databases, meeting application requirements in different scenarios. This document details the following connection methods:

Connection Method	Applicable Scenario	Advantages
CloudBase MySQL Database	Applications within the same CloudBase environment	Direct private network connection, safe and efficient
Public Network Connection	Connect to any MySQL instance that is publicly accessible	High flexibility, broad applicability
Private Network Interconnection	Connect to MySQL instances in the Tencent Cloud Shanghai region	High security, good performance
Data Model OpenAPI	Applications requiring advanced data model features	Simplifies data operations, provides permission control

Note: The examples in this document are based on the Express application framework. You can choose the appropriate database driver and connection method according to your technology stack.

Connecting to the CloudBase MySQL Database

Enabling the CloudBase MySQL Database

1. Log in to the Tencent CloudBase Console and go to your CloudBase environment.
2. In the left sidebar, select Database > MySQL Database
3. Select the Database Management tab
4. Select based on your situation:
   • Existing Data Model: If the direct database connection feature is not enabled, you need to upgrade and migrate your database before it can be provided. Submit a ticket, and the technical support team will assist you in upgrading the database.
   • First-time use: The system will prompt you to initialize a new database.
     • Select the desired MySQL version.
     • Set the database administrator password (keep it securely)
     • Click Confirm and wait until the database initialization is completed.
5. Once initialization is completed, you can view the database connection information on the Database Management page, including:
   • Private network connection address
   • Port
   • Username

Using CloudBase MySQL Database in Cloud Hosting Service

Cloud Hosting Service can directly connect to the CloudBase MySQL Database via the private network address without additional configuration:

1. Obtain the connection information for the CloudBase MySQL Database (private network address, port, username, and password)
2. Configure these connection details in the environment variables of the Cloud Hosting Service
3. Use the code example in the following section Connecting to Database via Public Network, and replace the connection parameters with the private network connection information of the CloudBase MySQL Database.

Advantage: Faster connection speed and higher security via private network, without exposing the database to the public network

Connecting to Database via Public Network

Preparation

1. Ensure that your MySQL database has public network access enabled and appropriate access control rules configured.
2. Prepare the database connection information (host address, port, username, password, and database name)

Implementation Steps

1. Create Database and Table

First, create a database named express_dev in your MySQL database, and create a user table:

CREATE DATABASE IF NOT EXISTS express_dev;
USE express_dev;

CREATE TABLE persons (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  age INT NOT NULL
);

-- Optional: Insert some test data.
INSERT INTO persons (name, age) VALUES ('Zhang San', 25), ('Li Si', 30), ('Wang Wu', 35);

Table Structure Description:

• id: Auto-increment primary key that uniquely identifies each record
• name: Person's name, VARCHAR type, maximum 100 characters, not null
• age: Age, INT type, not null

2. Install the database driver

Install the MySQL driver in your Express project:

npm install mysql2 --save

It is recommended to use mysql2 instead of mysql, as the former supports Promise API and offers better performance

3. Write Database Connection Code

Open the routes/index.js file and modify it to the following:

const express = require('express');
const mysql = require('mysql2/promise');
const router = express.Router();

// Create a MySQL connection pool
const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  port: process.env.DB_PORT ? Number(process.env.DB_PORT) : 3306,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

/* GET home page. */
router.get('/', async function(req, res, next) {
  try {
    const connection = await pool.getConnection();
    try {
      const [rows] = await connection.query('SELECT * FROM persons LIMIT 10');
      res.json({ success: true, data: rows });
    } finally {
      // Ensure the connection is returned to the pool
      connection.release();
    }
  } catch (err) {
    console.error('Database query error:', err);
    res.status(500).json({ success: false, error: 'Database query failed' });
  }
});

module.exports = router;

4. Configure environment variables and deploy

Configure the following environment variables in the Cloud Hosting Service:

Environment Variable	Description	Example Value
DB_HOST	Database host address	mysql-example.mysql.database.tencentcloud.com
DB_USER	Database username	root
DB_PASSWORD	Database password	your_password
DB_NAME	Database name	express_dev
DB_PORT	Database port	3306

Security Tip: Sensitive information such as database passwords should be stored using the environment variables feature of Cloud Hosting to avoid hardcoding in the code

5. Test connection

After the deployment is complete, access the root path of the Cloud Hosting Service. If everything is working properly, you will see user data queried from the database.

Connecting to Tencent Database via Private Network Interconnection

Applicable Scenarios

This method is applicable for connecting to MySQL database instances purchased in the Tencent Cloud Shanghai region. More efficient and secure database access can be achieved via private network interconnection.

Configuration Steps

1. Purchase Tencent Cloud MySQL instance

   • Log in to the Tencent Cloud Console
   • Choose the database MySQL service
   • When purchasing, choose the Shanghai region
   • Complete the purchase and initialize the database.

2. Configure Private Network Interconnection

   • In the Cloud Hosting Console, choose Network Configuration > Private Network Interconnection
   • Click Configure VPC Connection
   • Select the target VPC network (where your MySQL instance is located)
   • Save the configuration

3. Connect Using Private Network Address

   • Obtain the private network address of the MySQL instance (can be viewed in the Tencent Cloud MySQL console)
   • Configure the database connection information in the environment variables of the Cloud Hosting Service using the private network address
   • Use the same code as in Public Network Connection, but change the connection address to the private network address

Advantages

• Security: Database traffic does not traverse the public network, reducing security risks
• Performance: Low latency and high throughput for private network connections
• Cost: Avoid public network traffic fees

Note: The Private Network Interconnection feature only supports connecting to resources within the same account and same region. For cross-region connections, consider using the public network connection method.

Operating Databases via the Data Model OpenAPI

Data Model OpenAPI provides a more advanced way to operate databases. It encapsulates the underlying database operations and offers features such as permission control and data validation.

Related Resources

• Database/Data Model Introduction
• Data Model HTTP API Documentation

Implementation Steps

1. Create the Data Model

1. Log in to the Cloud Development Console
2. Click Create Data Model and configure as follows:
   • Create Schema: Create a database and configure the data model.
   • Creation Method: Create from scratch
   • Write to Database: Database (MySQL)
   • Model Name: User
   • Model Identifier: users
3. Add Field:
   • Field 1:
     • Field Name: Name
     • Field Identifier: name
     • Type: Text
   • Field 2:
     • Field Name: Age
     • Field Identifier: age
     • Type: Number
4. Click Confirm to complete the creation.

2. Create user and grant permissions

1. Go to Tencent Cloud Development > Extensions > Cloud Admin Console
2. Choose User Management > Create User, fill in the user information and create.
3. Choose Permission Control > System Roles > Super Administrator > Add Member
4. Add the newly created user as a Super Administrator

3. Create an Express route to handle data model API calls

1. Install necessary dependencies:

npm install axios express --save

2. Create users.js under the routes directory:

const express = require("express");
const router = express.Router();
const axios = require("axios");

// Read configuration from environment variables
const envId = process.env.ENV_ID;
const username = process.env.USERNAME;
const password = process.env.PASSWORD;
const modelName = process.env.MODEL_NAME || "users";

// User login to obtain token
async function signin(envId, username, password) {
  const url = `https://${envId}.api.tcloudbasegateway.com/auth/v1/signin`;

  try {
    const response = await axios.post(
      url,
      { username, password, verification_token: "" },
      {
        headers: {
          "Content-Type": "application/json",
          Accept: "application/json",
        },
      }
    );

    const data = response.data;
    if (!data.token_type || !data.access_token) {
      throw new Error("The response data is missing token_type or access_token");
    }

    return `${data.token_type} ${data.access_token}`;
  } catch (error) {
    console.error("Login failed:", error.response?.data || error.message);
    throw error.response?.data || error;
  }
}

// Obtain the data model list
async function getModelList(envId, modelName, token) {
  const url = `https://${envId}.api.tcloudbasegateway.com/v1/model/prod/${encodeURIComponent(
    modelName
  )}/list`;

  try {
    const response = await axios.get(url, {
      headers: {
        Accept: "application/json",
        Authorization: token,
      },
    });

    return response.data.data;
  } catch (error) {
    console.error("Failed to obtain model list:", error.response?.data || error.message);
    throw error.response?.data || error;
  }
}

// Route to obtain user list
router.get("/", async (req, res) => {
  if (!envId || !username || !password) {
    return res.status(400).json({ success: false, error: "Missing necessary environment variable configuration" });
  }

  try {
    // First, log in to obtain token
    const token = await signin(envId, username, password);
    
    // Obtain the model list with token
    const data = await getModelList(envId, modelName, token);
    
    res.json({ success: true, data });
  } catch (err) {
    res.status(500).json({ success: false, error: err.message || "Unknown error" });
  }
});

module.exports = router;

3. Register routes in the main application file app.js:

const usersRouter = require('./routes/users');
app.use('/api/users', usersRouter);

4. Configure environment variables

Configure the following environment variables in the Cloud Hosting Service:

Environment Variable	Description	Example Value
ENV_ID	Cloud Development Environment ID	your-env-id
USERNAME	Existing username	admin
PASSWORD	User password	your-password
MODEL_NAME	Data model identifier	users

5. Deployment and Testing

1. Deploy the application to Cloud Hosting
2. Access the /api/users endpoint; if configured correctly, it will return the user list in the data model.

Advantages of the Data Model API

• Access Control: Role-Based Access Control
• Data Validation: Automatically verifying data formats and types
• Simplified Development: No need to write complex SQL queries
• Unified Interface: Standardized RESTful API

Best Practices

Connection Pool Management

• Use connection pools instead of single connections to improve performance and reliability
• Set the connection pool size appropriately, typically number of container instances × 5-10
• Ensure proper release of connections to prevent connection leaks

Security

• Use environment variables to store sensitive information and avoid hardcoding
• Set minimum permissions for database users
• Regularly update database passwords
• Use parameterized queries to prevent SQL injection

Performance Optimization

• Create indexes for frequently queried fields
• Optimize queries to avoid full table scans
• Use appropriate data types and table structures
• Consider using read/write splitting to improve performance

Error Handling

• Implement robust error handling and logging
• Set connection timeout and retry mechanism
• Monitor database connection status and performance metrics

Troubleshooting Common Issues

Issue	Possible Cause	Solution
Connection timeout	Network issues or high database load	Check network configuration and increase connection timeout
Too many connections	Connections not properly released or inappropriate connection pool configuration	Ensure connections are released and adjust the connection pool size

• Authentication failed | Poor query performance | Missing indexes or unoptimized queries | Add appropriate indexes and optimize the query |