Skip to main content

Call MySQL Database

Overview​

Cloud Functions provide multiple methods to connect and operate MySQL databases, meeting application requirements in different scenarios. Choose the appropriate connection method based on your database deployment location and network environment:

Connection MethodUse CaseAdvantages
CloudBase MySQL (Recommended)Use the built-in MySQL database in CloudBase environmentSimple configuration, automatic VPC direct connection
VPC InterconnectionConnect to other MySQL instances in Tencent CloudHigh performance, low latency, secure
Public Network ConnectionConnect to any publicly accessible MySQL instanceHigh flexibility, wide applicability

Quick Start​

Step 1: Prepare the Database​

  1. Log in to CloudBase Platform / MySQL Database
  2. Choose according to your situation:
    • Existing Database: Refer to the MySQL Migration to User Account documentation 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" from the Database Settings page
Connection String Format

Format: mysql://root:password@internal-address:3306/tcb

Step 2: Install Database Driver​

Install mysql2 in your Cloud Function project:

npm install mysql2 --save

Step 3: Configure Network Connection​

  1. Go to CloudBase Platform / Cloud Functions
  2. Select the Cloud Function and go to the "Function Configuration" page
  3. Enable "Advanced Configuration / Private Network"
  4. Select the VPC where the CloudBase MySQL database is located
Important

After configuring VPC, Cloud Functions cannot access the public network by default. To access both public and internal networks simultaneously, refer to VPC Access Configuration

Step 4: Write Cloud Function Code​

const mysql = require('mysql2/promise');

// 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: 5,
      queueLimit: 0,
      acquireTimeout: 60000,
      timeout: 60000,
      charset: 'utf8mb4'
    });
  }
  return pool;
}

exports.main = async (event, context) => {
  try {
    const pool = getPool();
    const connection = await pool.getConnection();
    
    try {
      const [rows] = await connection.query('SELECT * FROM persons LIMIT 10');
      return { success: true, data: rows };
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('Database operation failed:', error);
    return { success: false, error: error.message };
  }
};

Step 5: Configure Environment Variables​

Configure in the "Environment Variables" of your Cloud Function:

Environment VariableDescriptionExample Value
DB_HOSTDatabase addressgz-xxxxx.mysql.tencentcdb.com
DB_PORTPort3306
DB_USERUsernameroot
DB_PASSWORDPasswordyour_password
DB_NAMEDatabase nametcb
Using Connection String

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

Step 6: Test Connection​

After deployment, click the "Test" button in the Cloud Function console

If the connection is successful, it will return records from the database.

Connection Methods Explained​

Configuration Differences Comparison​

The core difference among the three connection methods lies only in network configuration; the code implementation is identical:

Connection MethodNetwork ConfigurationDatabase AddressUse Case
CloudBase MySQLConfigure VPC (VPC where database is located)Internal network addressPreferred for CloudBase projects
VPC InterconnectionEnable VPC Interconnection (target VPC)Internal network addressFor existing Tencent Cloud MySQL
Public Network ConnectionNo configuration requiredPublic network addressThird-party or self-built databases
Configuration Recommendations
  • CloudBase MySQL: Use "Quick Start" directly, simplest method
  • Tencent Cloud MySQL: Only need to adjust network configuration to "VPC Interconnection", other steps are the same
  • Public Network MySQL: No network configuration required, but ensure database has public network access enabled

Public Network Connection Special Notes​

Security Configuration​

Security Notice

Public network connections pose security risks. Please ensure:

  • Use strong passwords
  • Configure firewall rules to restrict access sources
  • Regularly update database versions and security patches
  • For production environments, it's recommended to use internal network connections

Sample Table Structure​

If you need to create a test table, you can refer to the following SQL:

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
);

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

Database Operations​

Basic CRUD Operations​

Implement complete Create, Read, Update, Delete operations in Cloud Functions:

const mysql = require('mysql2/promise');

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: 5,
      waitForConnections: true,
      charset: 'utf8mb4'
    });
  }
  return pool;
}

exports.main = async (event, context) => {
  const { action, data } = event;
  
  try {
    const pool = getPool();
    const connection = await pool.getConnection();
    
    try {
      let result;
      
      switch (action) {
        case 'list':
          // Query list
          const [rows] = await connection.query(
            'SELECT * FROM persons ORDER BY created_at DESC LIMIT 10'
          );
          result = { success: true, data: rows };
          break;
          
        case 'create':
          // Create record
          const { name, age, email } = data;
          const [insertResult] = await connection.query(
            'INSERT INTO persons (name, age, email) VALUES (?, ?, ?)',
            [name, age, email]
          );
          result = { 
            success: true, 
            data: { id: insertResult.insertId, name, age, email }
          };
          break;
          
        case 'update':
          // Update record
          const { id, ...updateData } = data;
          const [updateResult] = await connection.query(
            'UPDATE persons SET ? WHERE id = ?',
            [updateData, id]
          );
          result = { 
            success: true, 
            affectedRows: updateResult.affectedRows 
          };
          break;
          
        case 'delete':
          // Delete record
          const [deleteResult] = await connection.query(
            'DELETE FROM persons WHERE id = ?',
            [data.id]
          );
          result = { 
            success: true, 
            affectedRows: deleteResult.affectedRows 
          };
          break;
          
        default:
          result = { success: false, error: 'Unsupported operation' };
      }
      
      return result;
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('Database operation failed:', error);
    return { success: false, error: error.message };
  }
};

Web Application Integration (Express/Koa)​

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

// Create 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 || 3306,
  connectionLimit: 10,
  charset: 'utf8mb4'
});

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

// Paginated query
router.get('/users', async (req, res) => {
  const { page = 1, limit = 10 } = req.query;
  const offset = (page - 1) * limit;
  
  try {
    const connection = await pool.getConnection();
    try {
      const [countResult] = await connection.query('SELECT COUNT(*) as total FROM persons');
      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: countResult[0].total
        }
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('Query failed:', error);
    res.status(500).json({ success: false, error: 'Query failed' });
  }
});

// Create user
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 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 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({ error: 'User does not exist' });
      }
      res.json({ success: true });
    } finally {
      connection.release();
    }
  } catch (error) {
    res.status(500).json({ error: 'Update failed' });
  }
});

// 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({ error: 'User does not exist' });
      }
      res.json({ success: true });
    } finally {
      connection.release();
    }
  } catch (error) {
    res.status(500).json({ error: 'Deletion failed' });
  }
});

module.exports = router;

Test API​

# Health check
curl https://your-domain.com/health

# 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

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,     // Connection acquisition timeout
  timeout: 60000,            // Query timeout
  
  // Reconnection configuration
  reconnect: true,           // Auto-reconnect
  
  // Character set 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;
      
      // Check 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'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]
    );
    
    // Add to receiver's 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();
  }
}

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 connection's query method
const originalQuery = pool.query.bind(pool);
pool.query = withPerformanceMonitoring(originalQuery);

Common Issues​

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 configurations
  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
-- 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 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)
// const query = `SELECT * FROM persons WHERE name = '${userName}'`;
Performance Recommendations
  • Enable connection pool in production environments to improve performance
  • Add indexes for frequently queried fields
  • Use transactions to ensure data consistency
  • Regularly monitor database performance and slow query logs
Security Notice
  • Avoid hardcoding database passwords in code
  • Use parameterized queries to prevent SQL injection
  • Regularly update database versions and security patches
  • Configure appropriate firewall rules