Initialization

Activation and Usage

1. Access the console: Go to the CloudBase console

2. Select database: Switch to Database/MySQL

When creating a model, select the cloud database (MySQL type). If the database is not activated, you can choose to activate it.

The activation process will take 2 to 3 minutes. Please wait patiently.

After activation is complete, you can continue using the cloud database (MySQL type) to create models.

MySQL database is a relational database service provided by CloudBase, supporting full SQL functionality. This article will introduce how to initialize and use the MySQL database through data models.

📐 Table Structure Management

When creating a new MySQL data model, the corresponding table structure is simultaneously created in the MySQL database, enabling data visualization through the CMS management page and supporting standard SQL query operations.

For specific data model functionalities, please refer to Data Model

Data Model Management Page

🚀 Create Your First MySQL Data Model

🎯 Practical Case: E-commerce System

Let's demonstrate how to use the relational features of MySQL databases by creating an e-commerce system data model:

{
  "users": {
    "id": 1,
    "username": "zhangsan",
    "email": "zhangsan@example.com",
    "phone": "13800138000",
    "status": "active",
    "created_at": "2024-01-15T10:30:00Z"
  },
  "products": {
    "id": 1,
    "name": "iPhone 15 Pro",
    "description": "Latest iPhone",
    "price": 7999.00,
    "stock": 100,
    "category_id": 1,
    "status": "active"
  },
  "orders": {
    "id": 1,
    "user_id": 1,
    "total_amount": 7999.00,
    "status": "paid",
    "created_at": "2024-01-15T14:30:00Z"
  }
}

Design Model Structure

Taking the e-commerce system as an example, we need to create three related data models:

📦 Product Model (products)

Field Name	Field Type	Constraint	Description
id	INT	PRIMARY KEY AUTO_INCREMENT	Product ID
name	VARCHAR(200)	NOT NULL	Product Name
description	TEXT	NULL	Product Description
price	DECIMAL(10, 2)	NOT NULL	Product Price
stock	INT	DEFAULT 0	Stock Quantity
category_id	INT	NULL	Category ID
status	ENUM	DEFAULT 'active'	Product Status
created_at	DATETIME	DEFAULT CURRENT_TIMESTAMP	Creation time

📋 Order Model (orders)

Field Name	Field Type	Constraint	Description
id	INT	PRIMARY KEY AUTO_INCREMENT	Order ID
user_id	INT	NOT NULL	User ID (foreign key)
total_amount	DECIMAL(10, 2)	NOT NULL	Order Total Amount
status	ENUM	DEFAULT 'pending'	Order Status
created_at	DATETIME	DEFAULT CURRENT_TIMESTAMP	Creation time
updated_at	DATETIME	ON UPDATE CURRENT_TIMESTAMP	Update time

Data CRUD

For details, refer to Data Model/CRUD

Initialize the SDK

Mini Program

// Download and import the SDK files
const {
    init
} = require("./wxCloudClientSDK.umd.js");

// Initialize CloudBase
wx.cloud.init({
    env: "your-env-id", // Replace with your environment ID
});

// Initialize the data model client
const client = init(wx.cloud);
const models = client.models;

Web

import cloudbase from "@cloudbase/js-sdk"

// Initialize the app
const app = cloudbase.init({
    env: "your-env-id" // Replace with your environment ID
})

// Get the data model instance
const models = app.models

// Get the database instance (for collection operations)
const db = app.database()

Cloud Function

const cloudbase = require("@cloudbase/node-sdk")

// Initialize the app
const app = cloudbase.init({
    env: "your-env-id" // Replace with your environment ID
})

// Get the data model instance
const models = app.models

// Get the database instance (for collection operations)
const db = app.database()

Basic Data Operations

Create a Product

// Create a new product
const {
    data
} = await models.products.create({
    data: {
        name: "iPhone 15 Pro",
        description: "Latest iPhone, equipped with the A17 Pro chip",
        price: 7999.00,
        stock: 100,
        category_id: 1,
        status: "active"
    }
});

console.log("Product created successfully:", data.id);

Create an Order

// Create a new order
const {
    data
} = await models.orders.create({
    data: {
        user_id: 1, // Associated user ID
        total_amount: 7999.00,
        status: "pending"
    }
});

console.log("Order created successfully:", data.id);

Query Product List

// Query Available Products
const {
    data
} = await models.products.list({
    filter: {
        where: {
            status: {
                $eq: "active"
            },
            stock: {
                $gt: 0
            } // stock greater than 0
        }
    },
    orderBy: [{
            created_at: "desc"
        } // sorted by creation time in descending order
    ],
    pageSize: 20,
    pageNumber: 1,
    getCount: true
});

console.log("Product list:", data.records);
console.log("Total:", data.total);

🎯 Best Practices

1. Table Structure Design Suggestions

• Index Design: Create indexes for commonly queried fields to improve query performance.
• Enum Type: Use enum to restrict the optional values of status fields

2. Model Design Principles

• Normalized Design: Avoid data redundancy and follow database normalization principles
• Relationships: Design clear table relationships (one-to-one, one-to-many, many-to-many)
• Field Naming: Use meaningful English field names and follow naming conventions
• Required Field Validation: Properly configure mandatory constraints to ensure data integrity.

3. Performance Optimization

• Index Optimization: Create appropriate indexes for commonly queried fields.
• Pagination: Use pagaged queries for large datasets to avoid performance issues.
• Field Selection: Return only the required fields during queries.

4. Security Recommendations

• Parameterized Queries: Use the data model SDK to automatically prevent SQL injection.
• Access Control: Control data access permissions through security rules
• Password Encryption: Sensitive information such as passwords must be encrypted for storage.

🚀 Next Steps

Congratulations! You have successfully created your first MySQL data model. Next, you can:

• 📚 Learn more about CRUD operations
• 🔗 Master relationship query techniques
• 🎛️ Use Data Management
• 🔒 Configure data security rules

Start building your relational database application now! 🎉