Integrate OceanBase MCP Server with Trae

MCP (Model Context Protocol) is an open-source protocol introduced by Anthropic in November 2024. It enables large language models (LLMs) to interact with external tools or data sources without requiring manual intervention. With MCP, LLMs can directly instruct tools to perform specific actions.

OceanBase MCP Server provides the capability for LLMs to interact with OceanBase through the MCP protocol, allowing the execution of SQL statements. The project is open-sourced on GitHub.

Trae is an IDE that integrates with MCP Server. You can download the latest version from the official website.

This article will guide you through integrating Trae IDE with OceanBase MCP Server to quickly build backend applications.

Prerequisites

• You have deployed OceanBase Database and created a MySQL-compatible mode user tenant. For more information, see Create a tenant.

• Install Python 3.11 or later and the corresponding pip. If your system has a lower version of Python, you can use Miniconda to create a new environment with Python 3.11 or later. For more information, see Install Miniconda.

• Install Git based on your operating system.

• Install the Python package manager uv. After the installation is complete, you can use the uv --version command to verify the installation:

  pip install uv
  uv --version
  

• Download Trae IDE and install the version suitable for your operating system.

Step 1: Obtain the database connection information

Contact the deployment personnel or administrator of OceanBase Database to obtain the corresponding database connection string, for example:

obclient -h$host -P$port -u$user_name -p$password -D$database_name

Parameter description:

• $host: the IP address for connecting to OceanBase Database. If you connect through OceanBase Database Proxy (ODP), use the IP address of the ODP. If you connect directly, use the IP address of the OBServer node.

• $port: the port for connecting to OceanBase Database. If you connect through ODP, the default port is 2883, which can be customized during ODP deployment. If you connect directly, the default port is 2881, which can be customized during OceanBase Database deployment.

• $database_name: the name of the database to be accessed.

  Notice

  The user for connecting to the tenant must have the CREATE, INSERT, DROP, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL-compatible mode.

• $user_name: the tenant connection account. For ODP connection, the common format is username@tenant name#cluster name or cluster name:tenant name:username. For direct connection, the format is username@tenant name.

• $password: the password of the account.

For more information about the connection string, see Connect to an OceanBase tenant by using OBClient.

Step 2: Configure OceanBase MCP Server

Clone the OceanBase MCP Server repository

Run the following command to download the source code to your local machine:

git clone https://github.com/oceanbase/mcp-oceanbase.git

Navigate to the source code directory:

cd mcp-oceanbase

Install dependencies

In the mcp-oceanbase directory, run the following commands to create a virtual environment and install the dependencies:

uv venv
source .venv/bin/activate
uv pip install .

Create a working directory for Trae

Manually create a working directory for Trae and open it. Trae will generate files in this directory. An example directory name is trae-generate.

Configure OceanBase MCP Server in Trae

Use the shortcut Ctrl + U (Windows) or Command + U (MacOS) to open the chat dialog. Click the gear icon in the top right corner and select MCP.

Add and configure MCP Servers

1. Click Add MCP Servers and select Manual Configuration.

   

   

2. Fill in the configuration file, and delete the sample content in the editing box.

   

   Then enter the following content:

   {
     "mcpServers": {
       "oceanbase": {
         "command": "uv",
         "args": [
           "--directory",
           // Replace this with the absolute path to the oceanbase_mcp_server folder
           "/path/to/your/mcp-oceanbase/src/oceanbase_mcp_server",
           "run",
           "oceanbase_mcp_server"
         ],
         "env": {
           // Replace these with your OceanBase database connection details
           "OB_HOST": "***",
           "OB_PORT": "***",
           "OB_USER": "***",
           "OB_PASSWORD": "***",
           "OB_DATABASE": "***"
         }
       }
     }
   }
   

3. If the configuration is successful, the status will change to Available.

Test the MCP server

1. Select the Builder with MCP agent.

2. In the dialog box, enter the prompt: How many tables are in the test database?. The Trae client will display the SQL statement to be executed. Confirm that it is correct and click the Run button to execute the query.

3. The Trae client will display all the table names in the test database, indicating that we have successfully connected to OceanBase Database.

Create a RESTful API project using FastAPI

You can use FastAPI to quickly create a project with a RESTful API style. FastAPI is a Python web framework that allows you to build RESTful APIs quickly.

1. Create a customer table

   Enter the prompt: Create a customer table with ID as the primary key, and fields name, age, telephone, and location in the dialog box. Confirm the SQL statement and click the Run button to execute the query.

2. Insert test data

   Enter the prompt: Insert 10 records into the customer table in the dialog box. Confirm the SQL statement and click the Run button to execute the query.

   After the insertion is successful, the execution result will be displayed:

3. Create a FastAPI project

   Enter the prompt: Create a FastAPI project and generate a RESTful API based on the customer table in the dialog box. Confirm the SQL statement and click the Run button to execute the query.

   This step will automatically generate three files. It is recommended to select "Accept All" for the first use, as the content of the files generated by AI may be uncertain, and they can be adjusted according to actual needs later.

4. Create a virtual environment and install dependencies

   Execute the following commands to create a virtual environment using the uv package manager in the current directory and install the required packages:

   uv venv
   source .venv/bin/activate
   uv pip install -r requirements.txt
   

5. Start the FastAPI project

   Execute the following command to start the FastAPI project:

   uvicorn main:app --reload
   

6. View the data in the table

   Run the following command in the command line, or use other request tools, to view the data in the table:

   curl http://127.0.0.1:8000/customers
   

   The returned result is as follows:

   [{"ID":1,"name":"Zhang San","age":25,"telephone":"13800138000","location":"Beijing"},{"ID":2,"name":"Li Si","age":30,"telephone":"13900139000","location":"Shanghai"},{"ID":3,"name":"Wang Wu","age":35,"telephone":"13700137000","location":"Guangzhou"},{"ID":4,"name":"Zhao Liu","age":22,"telephone":"13600136000","location":"Shenzhen"},{"ID":5,"name":"Sun Qi","age":40,"telephone":"13500135000","location":"Chengdu"},{"ID":6,"name":"Zhou Ba","age":28,"telephone":"13400134000","location":"Hangzhou"},{"ID":7,"name":"Wu Ji","age":33,"telephone":"13300133000","location":"Nanjing"},{"ID":8,"name":"Zheng Shi","age":27,"telephone":"13200132000","location":"Wuhan"},{"ID":9,"name":"Chen Shiyi","age":31,"telephone":"13100131000","location":"Xi'an"},{"ID":10,"name":"Lin Shier","age":24,"telephone":"13000130000","location":"Chongqing"}]
   

   We can see that the RESTful APIs for adding, deleting, modifying, and querying have been successfully generated:

   from fastapi import FastAPI
   from pydantic import BaseModel
   import mysql.connector
   
   app = FastAPI()
   
   # Database connection configuration
   config = {
       'user': '*******',
       'password': '******',
       'host': 'xx.xxx.xxx.xx',
       'database': 'test',
       'port':xxxx,
       'raise_on_warnings': True
   }
   
   class Customer(BaseModel):
       id: int
       name: str
       age: int
       telephone: str
       location: str
   
   @app.get('/customers')
   async def get_customers():
       cnx = mysql.connector.connect(**config)
       cursor = cnx.cursor(dictionary=True)
       query = 'SELECT * FROM customer'
       cursor.execute(query)
       results = cursor.fetchall()
       cursor.close()
       cnx.close()
       return results
   
   @app.get('/customers/{customer_id}')
   async def get_customer(customer_id: int):
       cnx = mysql.connector.connect(**config)
       cursor = cnx.cursor(dictionary=True)
       query = 'SELECT * FROM customer WHERE ID = %s'
       cursor.execute(query, (customer_id,))
       result = cursor.fetchone()
       cursor.close()
       cnx.close()
       return result
   
   @app.post('/customers')
   async def create_customer(customer: Customer):
       cnx = mysql.connector.connect(**config)
       cursor = cnx.cursor()
       query = 'INSERT INTO customer (ID, name, age, telephone, location) VALUES (%s, %s, %s, %s, %s)'
       data = (customer.id, customer.name, customer.age, customer.telephone, customer.location)
       cursor.execute(query, data)
       cnx.commit()
       cursor.close()
       cnx.close()
       return {'message': 'Customer created successfully'}
   
   @app.put('/customers/{customer_id}')
   async def update_customer(customer_id: int, customer: Customer):
       cnx = mysql.connector.connect(**config)
       cursor = cnx.cursor()
       query = 'UPDATE customer SET name = %s, age = %s, telephone = %s, location = %s WHERE ID = %s'
       data = (customer.name, customer.age, customer.telephone, customer.location, customer_id)
       cursor.execute(query, data)
       cnx.commit()
       cursor.close()
       cnx.close()
       return {'message': 'Customer updated successfully'}
   
   @app.delete('/customers/{customer_id}')
   async def delete_customer(customer_id: int):
       cnx = mysql.connector.connect(**config)
       cursor = cnx.cursor()
       query = 'DELETE FROM customer WHERE ID = %s'
       cursor.execute(query, (customer_id,))
       cnx.commit()
       cursor.close()
       cnx.close()
       return {'message': 'Customer deleted successfully'}