Skip to main content

MySQL Database

Overview​

Cloud function provides multiple ways to connect to and operate MySQL databases, meeting the application requirements of different scenarios. This document details the following connection methods:

Connection MethodUse CaseAdvantages
Public network connectionConnect to any publicly accessible MySQL instanceHigh flexibility, broad applicability
Private network interconnectionConnect to MySQL instances in the Tencent Cloud Shanghai regionDirect private network connection, high security, good performance

Database Connection via Public Network​

Preparations​

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

Public network connection poses certain security risks. Please ensure that:

  • Use strong passwords
  • Configure firewall rules to restrict access sources
  • Regularly update the database version and security patches

Implementation Steps​

1. Create Database and Table Schema​

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

CREATE DATABASE IF NOT EXISTS dev;
USE dev;

CREATE TABLE persons (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  age INT NOT NULL,
  email VARCHAR(255) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

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

Table Structure Description​

  • id: Auto-increment primary key that uniquely identifies each record
  • name: Name, VARCHAR type, maximum 100 characters, cannot be null
  • age: Age, INT type, cannot be null
  • email: Email address, VARCHAR type, with a unique constraint
  • created_at: Creation time, automatically set to the current time
  • updated_at: Update time, automatically updated when the record is modified

2. Install Database Driver​

npm install mysql2 --save
Tip

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 the following content:

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

// Create 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,
  acquireTimeout: 60000,
  timeout: 60000,
  reconnect: true,
  charset: 'utf8mb4'
});

// Health Check API
router.get('/health', async (req, res) => {
  try {
    const connection = await pool.getConnection();
    await connection.ping();
    connection.release();
    res.json({ status: 'healthy', timestamp: new Date().toISOString() });
  } catch (error) {
    console.error('Database health check failed:', error);
    res.status(500).json({ status: 'unhealthy', error: error.message });
  }
});

// Obtain user list
router.get('/', async (req, res) => {
  try {
    const { page = 1, limit = 10 } = req.query;
    const offset = (page - 1) * limit;
    
    const connection = await pool.getConnection();
    try {
      // Query total count
      const [countResult] = await connection.query('SELECT COUNT(*) as total FROM persons');
      const total = countResult[0].total;
      
      // Pagination query
      const [rows] = await connection.query(
        'SELECT * FROM persons ORDER BY created_at DESC LIMIT ? OFFSET ?',
        [parseInt(limit), parseInt(offset)]
      );
      
      res.json({ 
        success: true, 
        data: rows,
        pagination: {
          page: parseInt(page),
          limit: parseInt(limit),
          total,
          pages: Math.ceil(total / limit)
        }
      });
    } finally {
      connection.release();
    }
  } catch (err) {
    console.error('Database query error:', err);
    res.status(500).json({ success: false, error: 'Database query failed' });
  }
});

// Create a user
router.post('/users', async (req, res) => {
  const { name, age, email } = req.body;
  
  // Validate parameters
  if (!name || !age || !email) {
    return res.status(400).json({ 
      success: false, 
      error: 'Missing required parameters: name, age, email' 
    });
  }
  
  try {
    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 (err) {
    console.error('Failed to create user:', err);
    
    // Handle duplicate email error
    if (err.code === 'ER_DUP_ENTRY') {
      return res.status(409).json({ 
        success: false, 
        error: 'Email address already exists' 
      });
    }
    
    res.status(500).json({ success: false, error: 'Failed to create user' });
  }
});

// Update user
router.put('/users/:id', async (req, res) => {
  const { id } = req.params;
  const { name, age, email } = req.body;
  
  try {
    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({ 
          success: false, 
          error: 'User not found' 
        });
      }
      
      res.json({ success: true, message: 'User updated successfully' });
    } finally {
      connection.release();
    }
  } catch (err) {
    console.error('Failed to update user:', err);
    res.status(500).json({ success: false, error: 'Failed to update user' });
  }
});

// Delete user
router.delete('/users/:id', async (req, res) => {
  const { id } = req.params;
  
  try {
    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({ 
          success: false, 
          error: 'User not found' 
        });
      }
      
      res.json({ success: true, message: 'User deleted successfully' });
    } finally {
      connection.release();
    }
  } catch (err) {
    console.error('Failed to delete user:', err);
    res.status(500).json({ success: false, error: 'Failed to delete user' });
  }
});

module.exports = router;

4. Configure Environment Variables and Deploy​

Configure the following environment variables in the cloud hosting service:

Environment VariableDescriptionExample Value
DB_HOSTDatabase host addressmysql-example.mysql.database.tencentcloud.com
DB_USERDatabase usernameroot
DB_PASSWORDDatabase passwordyour_password
DB_NAMEDatabase namedev
DB_PORTDatabase port3306
Security Alert

Sensitive information such as database passwords should be stored using the environment variables feature of cloud hosting to avoid being hard-coded in the code.

5. Test Connection​

After the deployment is complete, you can test the database connection in the following ways:

# Test Health Check
curl https://your-app-domain.com/health

# Obtain User List
curl https://your-app-domain.com/

# Create New User
curl -X POST https://your-app-domain.com/users \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Test User",
    "age": 28,
    "email": "test@example.com"
  }'

Connecting to Tencent Database via Private Network Interconnection​

Applicable Scenarios​

This approach is applicable to connecting to MySQL database instances purchased in the Tencent Cloud Shanghai region. Via private network interconnection, it can achieve more efficient and secure database access.

Configuration Steps​

1. Purchase a Tencent Cloud MySQL Instance​

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

2. Configure Private Network Interconnection​

  • In the CloudBase Console, select Network Configuration > Private Network Interconnection
  • Click Configure VPC Connection
  • Select the target VPC network (where your MySQL instance resides)
  • Save the configuration

3. Connect using the private network address​

  • Obtain the private network address of the MySQL instance (available in the Tencent Cloud MySQL console)
  • Configure the database connection information in the environment variables of the CloudBase service using the private network address
  • Use the same code as 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.
  • Stability: The private network environment is more stable and reliable.
Note

The private network interconnection feature only supports connecting resources under the same account and in the same region. If cross-region connection is required, please consider using the public network connection method.

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 connections
  queueLimit: 0,             // Queue limit, 0 indicates unlimited
  acquireTimeout: 60000,     // acquire connection timeout
  timeout: 60000,            // Query timeout
  
  // Reconnection configuration
  reconnect: true,           // Auto-reconnect
  
  // Character set configuration
  charset: 'utf8mb4',        // Supports emoji and special characters
  
  // SSL configuration (recommended for production environments)
  ssl: process.env.NODE_ENV === 'production' ? {
    rejectUnauthorized: false
  } : false,
  
  // Timezone Configuration
  timezone: '+08:00'
});

Error Handling and Retry Mechanism​

// Database operations 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 whether the 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's 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's balance
    await connection.query(
      'UPDATE users SET balance = balance - ? WHERE id = ?',
      [amount, fromUserId]
    );
    
    // Increase recipient's balance
    await connection.query(
      'UPDATE users SET balance = balance + ? WHERE id = ?',
      [amount, toUserId]
    );
    
    // Log the transfer
    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 succeeded' };
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

Performance Monitoring​

// Query performance monitoring
function withPerformanceMonitoring(queryFunction) {
  return async function(...args) {
    const startTime = Date.now();
    const queryId = Math.random().toString(36).substr(2, 9);
    
    console.log(`[${queryId}] Query started:`, args[0]);
    
    try {
      const result = await queryFunction.apply(this, args);
      const duration = Date.now() - startTime;
      
      console.log(`[${queryId}] Query completed: ${duration}ms`);
      
      // Log slow queries
      if (duration > 1000) {
        console.warn(`[${queryId}] Slow query detected: ${duration}ms`, {
          sql: args[0],
          params: args[1]
        });
      }
      
      return result;
    } catch (error) {
      const duration = Date.now() - startTime;
      console.error(`[${queryId}] Query failed: ${duration}ms`, {
        error: error.message,
        sql: args[0]
      });
      throw error;
    }
  };
}

// Wrap the connection's query method
const originalQuery = pool.query.bind(pool);
pool.query = withPerformanceMonitoring(originalQuery);

Frequently Asked Questions​

Connection Issues​

What to do if a connection times out?

Possible causes:

  1. Network latency is too high
  2. The database server is overloaded
  3. The firewall is blocking the connection

Solution:

// Increase the timeout period
const pool = mysql.createPool({
  // ... Other configurations
  acquireTimeout: 120000,  // increased to 2 minutes
  timeout: 120000,         // query timeout: 2 minutes
  connectTimeout: 60000    // Connection timeout 1 minute
});
How to handle too many connections?

Solution:

  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,
    Idle connections: pool._freeConnections.length,
    Connections in use: pool._acquiringConnections.length
  });
}, 30000);

Performance Issues​

How to optimize slow queries?

Optimization policy:

  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

Protective Measures:

  1. Always use parameterized queries
  2. Validate input data
  3. Use the principle of least privilege
// Proper parameterized queries
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 pools in the production environment to improve performance.
  • For frequently queried fields, add indexes
  • 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 the database version and security patches
  • Configure appropriate firewall rules