MySQL Database Integration

Overview

Cloud Hosting 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
Cloud Development MySQL database	Applications within the same Cloud Development environment	Direct intranet connection, secure and efficient
Public Network Connection	Connect to any publicly accessible MySQL instance	High flexibility, broad applicability
Private Network Interconnection	Connecting to MySQL instances in the Shanghai region of Tencent Cloud	High security, excellent performance
Data Model OpenAPI	Applications requiring advanced data model features	Simplifies data operations and provides access control

Note: The examples in this document are built on the Express application framework. You can select the corresponding database driver and connection method based on your technology stack.

Connecting to the Cloud Development MySQL Database

Activate the Cloud Development MySQL Cloud Database

1. Log in to the Tencent Cloud CloudBase Console and enter your CloudBase environment.
2. In the left navigation bar, select Database > MySQL Database
3. Select the Database Management tab
4. Select based on your requirements:
   • Existing Data Model: If the direct database connection capability is not enabled, it requires database upgrade and migration before becoming available. You can submit a ticket, and our technical support personnel will assist you with the database upgrade.
   • 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 for the database initialization to complete.
5. After initialization is complete, you can view the database connection information on the Database Management page, including:
   • Intranet connection address
   • Port
   • Username

Using the CloudBase MySQL Cloud Database in Cloud Hosting Services

Cloud Hosting Services can directly connect to the Cloud Development MySQL database via the intranet address without additional configuration:

1. Obtain the CloudBase MySQL database connection information (Intranet address, port, username, and password)
2. Configure this connection information in the environment variables of the cloud hosting service.
3. Use the code example in the following Connecting to the Database via Public Network section, replacing the connection parameters with the CloudBase MySQL database's intranet connection information

Advantages: Fast intranet connection speed, high security, and no need to expose the database to the public network.

Connecting to the Database via Public Network

Preparations

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

Implementation Steps

1. Create a 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 nullable
• age: Age, INT type, not nullable

2. Install the Database Driver

Install the MySQL driver in your Express project:

npm install mysql2 --save

It is recommended to use mysql2 rather than mysql because the former supports the Promise API and offers better performance

3. Write Database Connection Code

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

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 deployment is complete, access the root path of the cloud hosting service. If everything is configured correctly, you will see user data queried from the database.

Connecting to Tencent Cloud Database via Intranet Connectivity

Applicable Scenarios

This method is suitable for connecting to MySQL database instances purchased in the Shanghai region of Tencent Cloud. Through intranet interconnection, more efficient and secure database access can be achieved.

Configuration Steps

1. Purchase a Tencent Cloud MySQL instance

   • Log in to the Tencent Cloud Console
   • Select the Cloud Database MySQL service
   • Select the Shanghai region during purchase.
   • Complete the purchase and initialize the database.

2. Configuring Intranet Connectivity

   • In the Cloud Hosting console, select Network Configuration > Intranet Connectivity
   • Click Configure VPC Connection
   • Select the target VPC network (the VPC where your MySQL instance resides)
   • Save configuration

3. Connecting via Intranet Address

   • Obtain the Intranet address of the MySQL instance (can be viewed in the Tencent Cloud MySQL console)
   • Configure database connection information in the environment variables of the cloud hosting service using the intranet address.
   • Use the same code as public network connection, but change the connection address to the intranet address.

Advantages

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

Note: The Intranet connectivity feature only supports connecting resources within the same account and the same region. If cross-region connection is needed, consider using the public network connection method.

Operating the Database via Data Model OpenAPI

Data Model OpenAPI provides a more advanced approach to database operations, encapsulating underlying database operations while offering features such as access control and data validation.

Related Resources

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

Implementation Steps

1. Create Data Model

1. Log in to the Cloud Development Console
2. Click Create Data Model and configure as follows:
   • Creation Mode: Create a database and configure the data model
   • Creation Method: Create from Scratch
   • Write to Database: Cloud Database (MySQL Edition)
   • Model Name: User
   • Model Identifier: users
3. Add fields:
   • Field 1:
     • Field Name: Name
     • Field Identifier: name
     • Type: Text Type
   • 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. Select User Management > Create User, fill in the user information, and create.
3. Select Permission Control > System Roles > Super Administrator > Add Members
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 in 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";

// Get token via user login
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 returned 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;
  }
}

// Get 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 get model list:", error.response?.data || error.message);
    throw error.response?.data || error;
  }
}

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

  try {
    // First log in to get the token
    const token = await signin(envId, username, password);
    
    // Use the token to get the model list
    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	CloudBase environment ID	your-env-id
USERNAME	Created 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 list of users in the data model

Advantages of the Data Model API

• Access Control: Role-Based Access Control
• Data Validation: Automatically validates data format and type
• 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 `container instance count × 5-10
• Ensure connections are properly released to avoid connection leaks

Security

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

Performance Optimization

• Create indexes for frequently queried fields
• Optimize query statements 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 released properly or improper connection pool configuration	Ensure connections are released and adjust connection pool size
Authentication failed	Incorrect username or password	Check environment variable configuration
Poor query performance	Missing indexes or unoptimized queries	Add appropriate indexes and optimize query statements