MySQL Database Integration

Overview

CloudBase Run provides multiple ways to connect and operate MySQL databases to meet different application scenarios. Choose the appropriate connection method based on your database deployment location and network environment:

Connection Method	Use Case	Advantages
CloudBase MySQL (Recommended)	Using MySQL database built-in with CloudBase environment	Simple configuration, automatic VPC connection
VPC Peering	Connecting to other Tencent Cloud MySQL instances	High performance, low latency, secure
Public Network Connection	Connecting to any publicly accessible MySQL instance	Flexible, wide applicability

📄️ Quick Start

Connect to CloudBase MySQL database in 5 minutes

📄️ Connection Methods Explained

Learn configuration steps and applicable scenarios for three connection methods

📄️ Database Operations

Learn how to perform CRUD operations in CloudBase Run

📄️ Best Practices

Practical tips for connection pool optimization, error handling, transaction management, and more

📄️ FAQs

Resolve common issues like connection timeout and performance optimization

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 tech stack.

Quick Start

Step 1: Prepare the Database

CloudBase MySQL (Recommended)

1. Log in to CloudBase Platform/MySQL Database
2. Choose based on your situation:
   • Existing Database: Refer to MySQL Migration to User Account document to migrate the database to your own Tencent Cloud account. After migration, VPC internal network connection is supported
   • First-time Use: The system will prompt you to initialize the database. Select the VPC and subnet, then confirm
3. Copy the "Internal Network Connection Address" on the Database Settings page

Connection String Format

Format: mysql://root:password@internal_address:3306/tcb

Tencent Cloud MySQL

1. Log in to Tencent Cloud MySQL Console
2. Ensure the MySQL instance and CloudBase Run are in the same region (Shanghai recommended)
3. Record the instance's internal network address, port, username, and password

Public Network MySQL

1. Ensure the MySQL database has public network access enabled
2. Configure firewall rules to allow CloudBase Run access
3. Record the database's public network address, port, username, and password

Security Notice

Public network connection poses security risks. Internal network connection is recommended for production environments

Step 2: Install Database Driver

Install mysql2 in your CloudBase Run project:

npm

npm install mysql2 --save

yarn

yarn add mysql2

pnpm

pnpm add mysql2

Step 3: Configure Network Connection

CloudBase MySQL

1. Go to CloudBase Platform/CloudBase Run
2. Select your CloudBase Run service and enter the "Service Configuration" page
3. Enable "VPC" in "Network Configuration"
4. Select the VPC where the CloudBase MySQL database is located

Tencent Cloud MySQL

1. Go to CloudBase Platform/CloudBase Run
2. Select your CloudBase Run service and enter the "Service Configuration" page
3. Enable "VPC" in "Network Configuration"
4. Select the target VPC (the VPC where the MySQL instance is located)

View VPC Information

You can view VPC and subnet information on the instance details page in the Tencent Cloud MySQL Console

Public Network MySQL

No network configuration required. CloudBase Run can access the public network by default

Step 4: Write CloudBase Run Code

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

// Global connection pool
let pool;

function getPool() {
  if (!pool) {
    pool = mysql.createPool({
      host: process.env.DB_HOST,
      port: process.env.DB_PORT || 3306,
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME || 'tcb',
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0,
      acquireTimeout: 60000,
      timeout: 60000,
      charset: 'utf8mb4'
    });
  }
  return pool;
}

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

module.exports = router;

Step 5: Configure Environment Variables

Configure in the "Environment Variables" of your CloudBase Run service:

Environment Variable	Description	Example Value
DB_HOST	Database address	gz-xxxxx.mysql.tencentcdb.com
DB_PORT	Port	3306
DB_USER	Username	root
DB_PASSWORD	Password	your_password
DB_NAME	Database name	tcb

Using Connection String

You can also configure the CONNECTION_URI environment variable to directly use the complete connection string

Connection Methods Explained

Configuration Differences Comparison

The core difference between the three connection methods lies only in network configuration. The code implementation is completely identical:

Connection Method	Network Configuration	Database Address	Applicable Scenario
CloudBase MySQL	Configure VPC (database VPC)	Internal network address	First choice for CloudBase projects
VPC Peering	Enable VPC peering (target VPC)	Internal network address	Existing Tencent Cloud MySQL
Public Network Connection	No configuration required	Public network address	Third-party or self-built database

Configuration Recommendations

• CloudBase MySQL: Directly use "Quick Start" - the simplest
• Tencent Cloud MySQL: Only need to adjust network configuration to "VPC Peering", other steps are the same
• Public Network MySQL: No network configuration required, but ensure database has public network access enabled

Database Operations

Basic CRUD Operations

Implement complete Create, Read, Update, Delete operations in CloudBase Run (applicable to all connection methods):

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

let pool;

function getPool() {
  if (!pool) {
    pool = mysql.createPool({
      host: process.env.DB_HOST,
      port: process.env.DB_PORT || 3306,
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME || 'tcb',
      connectionLimit: 10,
      waitForConnections: true,
      charset: 'utf8mb4'
    });
  }
  return pool;
}

// Query list
router.get('/users', async (req, res) => {
  try {
    const pool = getPool();
    const connection = await pool.getConnection();
    try {
      const [rows] = await connection.query('SELECT * FROM persons ORDER BY created_at DESC LIMIT 10');
      res.json({ success: true, data: rows });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('Query failed:', error);
    res.status(500).json({ success: false, error: 'Query failed' });
  }
});

// Create record
router.post('/users', async (req, res) => {
  const { name, age, email } = req.body;
  
  if (!name || !age || !email) {
    return res.status(400).json({ error: 'Missing required parameters' });
  }
  
  try {
    const pool = getPool();
    const connection = await pool.getConnection();
    try {
      const [result] = await connection.query(
        'INSERT INTO persons (name, age, email) VALUES (?, ?, ?)',
        [name, age, email]
      );
      res.status(201).json({ 
        success: true, 
        data: { id: result.insertId, name, age, email }
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    if (error.code === 'ER_DUP_ENTRY') {
      return res.status(409).json({ error: 'Email already exists' });
    }
    res.status(500).json({ error: 'Creation failed' });
  }
});

// Update record
router.put('/users/:id', async (req, res) => {
  const { id } = req.params;
  const { name, age, email } = req.body;
  
  try {
    const pool = getPool();
    const connection = await pool.getConnection();
    try {
      const [result] = await connection.query(
        'UPDATE persons SET name = ?, age = ?, email = ? WHERE id = ?',
        [name, age, email, id]
      );
      
      if (result.affectedRows === 0) {
        return res.status(404).json({ error: 'User not found' });
      }
      res.json({ success: true });
    } finally {
      connection.release();
    }
  } catch (error) {
    res.status(500).json({ error: 'Update failed' });
  }
});

// Delete record
router.delete('/users/:id', async (req, res) => {
  const { id } = req.params;
  
  try {
    const pool = getPool();
    const connection = await pool.getConnection();
    try {
      const [result] = await connection.query('DELETE FROM persons WHERE id = ?', [id]);
      
      if (result.affectedRows === 0) {
        return res.status(404).json({ error: 'User not found' });
      }
      res.json({ success: true });
    } finally {
      connection.release();
    }
  } catch (error) {
    res.status(500).json({ error: 'Deletion failed' });
  }
});

module.exports = router;

Test API

cURL

# Get user list
curl https://your-domain.com/users

# Create user
curl -X POST https://your-domain.com/users \
  -H "Content-Type: application/json" \
  -d '{"name":"Zhang San","age":25,"email":"test@example.com"}'

# Update user
curl -X PUT https://your-domain.com/users/1 \
  -H "Content-Type: application/json" \
  -d '{"name":"Li Si","age":30,"email":"lisi@example.com"}'

# Delete user
curl -X DELETE https://your-domain.com/users/1

JavaScript

// Get user list
async function getUsers() {
  const response = await fetch('https://your-domain.com/users');
  const result = await response.json();
  console.log(result);
}

// Create user
async function createUser() {
  const response = await fetch('https://your-domain.com/users', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({
      name: 'New User',
      age: 25,
      email: 'newuser@example.com'
    })
  });
  const result = await response.json();
  console.log(result);
}

Best Practices

Connection Pool Configuration Optimization

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,
  
  // Connection pool configuration
  connectionLimit: 10,        // Maximum number of connections
  queueLimit: 0,             // Queue limit, 0 means unlimited
  acquireTimeout: 60000,     // Acquire connection timeout
  timeout: 60000,            // Query timeout
  
  // Reconnection configuration
  reconnect: true,           // Auto reconnect
  
  // Charset configuration
  charset: 'utf8mb4',        // Support emoji and special characters
  
  // SSL configuration (recommended for production)
  ssl: process.env.NODE_ENV === 'production' ? {
    rejectUnauthorized: false
  } : false,
  
  // Timezone configuration
  timezone: '+08:00'
});

Error Handling and Retry Mechanism

// Database operation with retry
async function executeWithRetry(operation, maxRetries = 3) {
  let lastError;
  
  for (let i = 0; i < maxRetries; i++) {
    try {
      return await operation();
    } catch (error) {
      lastError = error;
      
      // Determine if error is retryable
      if (isRetryableError(error) && i < maxRetries - 1) {
        const delay = Math.pow(2, i) * 1000; // Exponential backoff
        await new Promise(resolve => setTimeout(resolve, delay));
        continue;
      }
      
      throw error;
    }
  }
  
  throw lastError;
}

function isRetryableError(error) {
  const retryableCodes = [
    'ECONNRESET',
    'ETIMEDOUT',
    'ENOTFOUND',
    'ER_LOCK_WAIT_TIMEOUT',
    'PROTOCOL_CONNECTION_LOST'
  ];
  
  return retryableCodes.includes(error.code);
}

// Usage example
router.get('/users', async (req, res) => {
  try {
    const result = await executeWithRetry(async () => {
      const connection = await pool.getConnection();
      try {
        const [rows] = await connection.query('SELECT * FROM persons');
        return rows;
      } finally {
        connection.release();
      }
    });
    
    res.json({ success: true, data: result });
  } catch (error) {
    console.error('Query failed:', error);
    res.status(500).json({ success: false, error: 'Query failed' });
  }
});

Transaction Processing

// Transaction processing example
async function transferMoney(fromUserId, toUserId, amount) {
  const connection = await pool.getConnection();
  
  try {
    await connection.beginTransaction();
    
    // Check sender balance
    const [fromUser] = await connection.query(
      'SELECT balance FROM users WHERE id = ? FOR UPDATE',
      [fromUserId]
    );
    
    if (fromUser[0].balance < amount) {
      throw new Error('Insufficient balance');
    }
    
    // Deduct sender balance
    await connection.query(
      'UPDATE users SET balance = balance - ? WHERE id = ?',
      [amount, fromUserId]
    );
    
    // Add receiver balance
    await connection.query(
      'UPDATE users SET balance = balance + ? WHERE id = ?',
      [amount, toUserId]
    );
    
    // Record transfer log
    await connection.query(
      'INSERT INTO transfer_logs (from_user_id, to_user_id, amount, created_at) VALUES (?, ?, ?, NOW())',
      [fromUserId, toUserId, amount]
    );
    
    await connection.commit();
    return { success: true, message: 'Transfer successful' };
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

Security

• Use environment variables to store sensitive information, avoid hardcoding
• Set minimum privileges 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, avoid full table scans
• Use appropriate data types and table structures
• Consider using read-write separation to improve performance

FAQs

Connection Issues

What to do about connection timeout?

Possible Causes:

1. High network latency
2. High database server load
3. Firewall blocking connection

Solutions:

// Increase timeout
const pool = mysql.createPool({
  // ... other configuration
  acquireTimeout: 120000,  // Increase to 2 minutes
  timeout: 120000,         // Query timeout 2 minutes
  connectTimeout: 60000    // Connection timeout 1 minute
});

How to handle too many connections?

Solutions:

1. Optimize connection pool configuration
2. Release connections promptly
3. Use connection monitoring

// Monitor connection pool status
setInterval(() => {
  console.log('Connection pool status:', {
    Total_connections: pool._allConnections.length,
    Free_connections: pool._freeConnections.length,
    Acquiring_connections: pool._acquiringConnections.length
  });
}, 30000);

Performance Issues

How to optimize slow queries?

Optimization Strategies:

1. Add appropriate indexes
2. Optimize SQL queries
3. Use connection pool
4. Implement query caching

-- Add index example
CREATE INDEX idx_persons_email ON persons(email);
CREATE INDEX idx_persons_age ON persons(age);
CREATE INDEX idx_persons_created_at ON persons(created_at);

Security Issues

How to prevent SQL injection?

Protection Measures:

1. Always use parameterized queries
2. Validate input data
3. Use the principle of least privilege

// Correct parameterized query
const [rows] = await connection.query(
  'SELECT * FROM persons WHERE name = ? AND age > ?',
  [userName, minAge]
);

// Incorrect string concatenation (vulnerable to SQL injection attacks)
// const query = `SELECT * FROM persons WHERE name = '${userName}'`;

Performance Recommendations

• Enable connection pool in production environment to improve performance
• Add indexes for frequently queried fields
• Use transactions to ensure data consistency
• Regularly monitor database performance and slow query logs

Security Reminder

• Avoid hardcoding database passwords in code
• Use parameterized queries to prevent SQL injection
• Regularly update database version and security patches
• Configure appropriate firewall rules