Skip to main content

TCB MySQL Database

TCB MySQL Database provides a fully managed relational database service that supports efficient data operations in cloud functions. This document details how to use MySQL database in cloud functions, including core features such as connection configuration, data operations, and transaction processing.

Quick Start​

Database Connection​

Initialize the SDK​

Before using the MySQL database, you need to initialize the TCB SDK and obtain the database reference:

const cloudbase = require('@cloudbase/node-sdk');

// Initialize the TCB App instance
const app = cloudbase.init({
  env: 'your-env-id' // Replace with your Environment ID
});

// Obtain the MySQL database reference (using the default instance and database)
const db = app.rdb();

Basic Operations​

Querying Data​

Use the select() method to query table data, which supports features such as conditional filtering and join queries.

// Query all data.
const { data, error } = await db.from('articles').select();

// Query specified fields.
const { data, error } = await db.from('articles').select('id, title, created_at');

// Use * to query all fields.
const { data, error } = await db.from('articles').select('*');

console.log('Query result:', data);

Inserting Data​

Use the insert() method to insert data into a table, supporting single-row insertion and batch insertion.

// Insert a single record
const { error } = await db.from('articles').insert({
  title: 'TCB Getting Started Guide',
  content: 'This is an introductory article about TCB...',
  author_id: 1,
  status: 'draft'
});

if (error) {
  console.error('Insertion failed:', error);
} else {
  console.log('Insertion succeeded');
}

Updating Data​

Use the update() method to update data in the table. Note: It must be used in conjunction with a filter.

// Update the record with the specified ID
const { error } = await db.from('articles')
  .update({
    title: 'Updated Title',
    status: 'published',
    updated_at: new Date().toISOString()
  })
  .eq('id', 1);

console.log('Update result:', error ? 'Failed' : 'Succeeded');

Delete data​

Use the delete() method to delete data in the table. Note: It must be used in conjunction with a filter.

// Delete the record with the specified ID
const { error } = await db.from('articles')
  .delete()
  .eq('id', 1);

console.log('Delete result:', error ? 'Failed' : 'Succeeded');

HTTP API Access​

Besides the Node.js SDK, you can also directly access the MySQL database via the HTTP API. The HTTP API follows the REST specification and supports standard HTTP methods for data operations.

Basic Configuration​

// HTTP API requests must include authentication information
const headers = {
  'Content-Type': 'application/json',
  'Authorization': 'Bearer your-access-token',
  // or use other authentication methods
  'X-CloudBase-Credentials': 'your-credentials'
};

const baseURL = 'https://your-env-id.service.tcloudbase.com/v1/rdb/rest';

Querying Data​

Use the GET method to query data in a table:

# Query all data
curl -X GET "https://your-env-id.service.tcloudbase.com/v1/rdb/rest/users" \
  -H "Authorization: Bearer your-access-token" \
  -H "Content-Type: application/json"

# Querying specified fields
curl -X GET "https://your-env-id.service.tcloudbase.com/v1/rdb/rest/users?select=id,name,email" \
  -H "Authorization: Bearer your-access-token"

# Pagination query
curl -X GET "https://your-env-id.service.tcloudbase.com/v1/rdb/rest/users?limit=10&offset=20" \
  -H "Authorization: Bearer your-access-token"

JavaScript Invocation Example​

// Query user list
async function queryUsers() {
  try {
    const response = await fetch(`${config.baseURL}/users?select=*&limit=20`, {
      method: 'GET',
      headers: {
        'Authorization': `Bearer ${config.accessToken}`,
        'Content-Type': 'application/json'
      }
    });

    if (!response.ok) {
      throw new Error(`HTTP error! status: ${response.status}`);
    }

    const users = await response.json();
    console.log('Query result:', users);
    return users;
  } catch (error) {
    console.error('Query failed:', error);
    throw error;
  }
}

// Conditional Query
async function queryActiveUsers() {
  const response = await fetch(`${config.baseURL}/users?status=eq.active&order=created_at.desc`, {
    headers: {
      'Authorization': `Bearer ${config.accessToken}`,
      'Content-Type': 'application/json'
    }
  });
  
  return await response.json();
}

Advanced Features​

// Obtain exact count.
async function getUserCount() {
  const response = await fetch(`${config.baseURL}/users?select=*`, {
    headers: {
      'Authorization': `Bearer ${config.accessToken}`,
      'Prefer': 'count=exact'
    }
  });
  
  // Obtain the count from the response headers.
  const count = response.headers.get('Content-Range');
  console.log('Total users:', count);
  
  return count;
}

Query Parameters Description​

ParameterDescriptionExample
selectSelect fields, supports join queriesselect=id,name,categories(name)
limitLimit the number of returned recordslimit=20
offsetOffset for paginationoffset=40
orderSort fieldorder=created_at.desc
field_name=operator.valueFilter conditionsstatus=eq.active

Filter Operators​

OperatorDescriptionExample
eqEqual tostatus=eq.active
neqNot equal tostatus=neq.inactive
gtGreater thanage=gt.18
gteGreater than or equal toage=gte.18
ltLess thanage=lt.65
lteLess than or equal toage=lte.65
likePattern matchingname=like.*John*
inIs inid=in.(1,2,3)
isNull checkemail=is.null

Advanced Query​

Query Modifiers​

// Pagination
const { data, error } = await db.from('articles')
  .select('*')
  .order('created_at', { ascending: false })
  .range(0, 9); // Retrieve the first 10 records

// Use limit and offset
const { data, error } = await db.from('articles')
  .select('*')
  .limit(10)
  .offset(20);

Complex Query Example​

// Compound condition query
const { data, error } = await db.from('articles')
  .select(`
    id,
    title,
    category:categories(name),
    author:users(name, email)
  `)
  .eq('status', 'published')
  .gte('created_at', '2024-01-01')
  .or('featured.eq.true,priority.gte.5')
  .order('created_at', { ascending: false })
  .limit(20);

// Inner Join Query (excluding NULL associations)
const { data, error } = await db.from('articles')
  .select('title, categories!inner(name)')
  .eq('categories.status', 'active');

Transaction Processing​

TCB MySQL supports transaction operations, ensuring data consistency:

exports.transferPoints = async (event, context) => {
  const { fromUserId, toUserId, points } = event;
  
  try {
    // Start the transaction.
    const { data, error } = await db.rpc('transfer_points', {
      from_user: fromUserId,
      to_user: toUserId,
      point_amount: points
    });
    
    if (error) {
      throw new Error(error.message);
    }
    
    return {
      success: true,
      message: 'Points transfer successful',
      data: data
    };
  } catch (error) {
    console.error('Points transfer failed:', error);
    return {
      success: false,
      error: error.message
    };
  }
};

Native SQL Query​

For complex query requirements, you can use native SQL:

// Use RPC to call stored procedures or functions
const { data, error } = await db.rpc('get_user_statistics', {
  user_id: 123,
  start_date: '2024-01-01',
  end_date: '2024-12-31'
});

// Complex Statistical Query Example
const { data, error } = await db.rpc('complex_report', {
  category: 'technology',
  limit_count: 50
});

Error Handling​

exports.main = async (event, context) => {
  try {
    const { data, error } = await db.from('users')
      .select('*')
      .eq('id', event.userId);
    
    if (error) {
      // Handle database errors
      console.error('Database query error:', error);
      return {
        success: false,
        error: 'Failed to query user information',
        details: error.message
      };
    }
    
    if (!data || data.length === 0) {
      return {
        success: false,
        error: 'User does not exist'
      };
    }
    
    return {
      success: true,
      data: data[0]
    };
  } catch (error) {
    console.error('System error:', error);
    return {
      success: false,
      error: 'Internal system error'
    };
  }
};

Best Practices​

1. Query Optimization​

// ✅ Good practice: Query only the required fields
const { data, error } = await db.from('users')
  .select('id, name, email')
  .eq('status', 'active');

// ❌ Avoid: querying all fields
const { data, error } = await db.from('users')
  .select('*')
  .eq('status', 'active');

2. Security​

// ✅ Good practice: Use parameterized queries
const { data, error } = await db.from('users')
  .select('*')
  .eq('email', userEmail)
  .eq('status', 'active');

// ✅ Data Validation
function validateUserInput(userData) {
  if (!userData.email || !userData.email.includes('@')) {
    throw new Error('Invalid email address');
  }
  
  if (!userData.name || userData.name.length < 2) {
    throw new Error('Username must be at least 2 characters');
  }
}

3. Performance Monitoring​

// Query performance monitoring
async function monitoredQuery(queryFunction) {
  const startTime = Date.now();
  
  try {
    const result = await queryFunction();
    const duration = Date.now() - startTime;
    
    if (duration > 1000) {
      console.warn(`Slow query detected: ${duration}ms`);
    }
    
    return result;
  } catch (error) {
    const duration = Date.now() - startTime;
    console.error(`Query failed: ${duration}ms`, error);
    throw error;
  }
}

// Using monitoring
const result = await monitoredQuery(() => 
  db.from('articles').select('*').limit(100)
);
Tip
  • All update and delete operations must be used in conjunction with a filter to prevent accidental operations
  • It is recommended to implement error handling for all database operations in the production environment
  • Using JOIN queries can reduce the number of database requests and improve performance
  • Regularly monitor query performance and optimize slow queries
Warning
  • Avoid executing database queries in loops; consider using batch operations
  • Use pagination for large data volume queries to avoid memory overflow
  • Always validate and filter user input in production environments
Security Reminder
  • Always use parameterized queries to prevent SQL injection risks
  • Avoid exposing database connection information in client-side code
  • Adhere to the principle of least privilege by granting only necessary database permissions