Integrate OceanBase MCP Server with Cline

Model Context Protocol (MCP) is an open-source protocol introduced by Anthropic in November 2024. It allows large language models to interact with external tools or data sources. With MCP, you do not need to manually copy and execute the output of large language models. Instead, the large language model can directly command tools to perform specific actions.

OceanBase MCP Server enables large language models to interact with OceanBase Database through the MCP protocol and execute SQL statements. It allows you to quickly build a project prototype with the help of an appropriate client and has been open-sourced on GitHub.

Cline is an open-source AI coding assistant that supports the MCP protocol.

This topic uses Cline to demonstrate how to quickly build a backend application using OceanBase MCP Server.

Prerequisites

• A transactional instance is available in your environment. For instructions on how to create the instance, see Create an transactional instance.

• You have created a MySQL-compatible tenant in the instance. For instructions on how to create the tenant, see Create a MySQL-compatible tenant.

• You have a MySQL database and account available under the tenant, and you have granted read and write permissions to the database account. For more information, see Create an account and Create a database (MySQL only).

• You are a project admin or instance admin and have the permissions required to read and write data in the instance. If not, contact your organization admin to grant the required permissions.

• You have installed Python 3.11 or later and the corresponding pip. If your machine has a low Python version, you can use Miniconda to create a new Python 3.11 or later environment. For more information, see Miniconda installation guide.

• Install Git based on your operating system.

• Install uv, a Python package manager. After the installation, run the uv --version command to verify the installation:

  pip install uv
  uv --version
  

• Install Cline:

  • If you are using Visual Studio Code IDE, search for the Cline plugin and install it in the Extensions section. The plugin name is Cline. After the installation, click the settings icon to configure the large model API for Cline as follows:

  • If you do not have an IDE, download Cline from Cline and follow the installation guide.

Step 1: Obtain the database connection information

1. Log in to the OceanBase Cloud console.

2. In the instance list page, expand the the information of the target instance.

3. Select Connect > Get Connection String under the target tenant.

4. In the pop-up window, select Public Network as the connection method.

5. Follow the prompts in the pop-up window to obtain the public endpoint and the connection string.

Step 2: Configure the OceanBase MCP server

This example uses Visual Studio Code to demonstrate how to configure the OceanBase MCP server.

Clone the OceanBase MCP server repository

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

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

Go to the source code directory:

cd mcp-oceanbase

Install dependencies

Run the following command in the mcp-oceanbase directory to create a virtual environment and install dependencies:

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

Create a working directory for Visual Studio Code

Manually create a working directory for Visual Studio Code on your local device and open it with Visual Studio Code. The files generated by Cline will be placed in this directory. The name of the sample directory is cline-generate.

Configure OceanBase MCP server in Cline

Click the Cline icon on the left-side navigation pane to open the Cline dialog box.

Add and configure MCP servers

1. Click the MCP Servers icon in the following figure.

   

2. Manually configure the OceanBase MCP server as indicated by the numbers in the following figure.

   

3. Fill in the configuration file.

   In the cline_mcp_settings.json file that was opened in the previous step, enter the following configuration information and save the file. /path/to/your/mcp-oceanbase/src/oceanbase_mcp_server must be replaced with the absolute path of the mcp-oceanbase folder, and OB_HOST, OB_PORT, OB_USER, OB_PASSWORD, and OB_DATABASE must be replaced with the corresponding information of your database.

   The configuration file is as follows:

   {
   "mcpServers": {
       "oceanbase": {
       "command": "uv",
       "args": [
           "--directory", 
           "/path/to/your/mcp-oceanbase/src/oceanbase_mcp_server",
           "run",
           "oceanbase_mcp_server"
       ],
       "env": {
           "OB_HOST": "***",
           "OB_PORT": "***",
           "OB_USER": "***",
           "OB_PASSWORD": "***",
           "OB_DATABASE": "***"
       }
       }
   }
   }
   

4. If the configuration is successful, the status will be Available, and the MCP tools and resources information will be displayed, as shown in the following figure:

   

5. Click the switch button in the following figure to enable Cline to use it:

   

Test the MCP server

Open the Cline session dialog box and enter the prompt How many tables are there in the test database. Cline will display the SQL statement about to be executed. Confirm the SQL statement and click the Run button.

Cline will display the number of tables in the test database, indicating that it can be properly connected to OceanBase Database.

Create a RESTful API project using FastAPI

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

1. Create the customer table

   In the dialog box, enter the prompt: Create a "customer" table with "Id" as the primary key, including the fields of "name", "age", "telephone", and "location". Confirm the SQL statement and click the Run button.

2. Insert test data

   In the dialog box, enter the prompt: Insert 10 test data entries. Confirm the SQL statement and click the Run button.

   After the data is inserted, the execution result will be displayed:

3. Create a FastAPI project

   In the dialog box, enter the prompt: Create a FastAPI project and generate a RESTful API based on the "customer" table. Confirm the SQL statement and click the Run button.

   This step will automatically generate three files. We recommend that you select "All Accept" for the first time because the content of the AI-generated files may be uncertain, and you can adjust them as needed later.

4. Create a virtual environment and install dependencies

   Run the following command to create a virtual environment using the uv package manager and install the dependency packages in the current directory:

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

5. Start the FastAPI project

   Run the following command to start the FastAPI project:

   uvicorn main:app --reload
   

6. View data in the table

   Run the following command in the command line, or use another request tool, to view the data in the table:

   curl http://127.0.0.1:8000/customers
   

   The return result is as follows:

   [{"telephone":"138***8001","id":1,"name":"Zhang San","location":"Beijing","age":25},{"telephone":"138***8002","id":2,"name":"Li Si","location":"Shanghai","age":30},{"telephone":"138***8003","id":3,"name":"Wang Wu","location":"Guangzhou","age":22},{"telephone":"138***8004","id":4,"name":"Zhao Liu","location":"Shenzhen","age":28},{"telephone":"138***8005","id":5,"name":"Qian Qi","location":"Hangzhou","age":35},{"telephone":"138***8006","id":6,"name":"Sun Ba","location":"Nanjing","age":40},{"telephone":"138***8007","id":7,"name":"Zhou Jiu","location":"Chengdu","age":27},{"telephone":"138***8008","id":8,"name":"Wu Shi","location":"Wuhan","age":33},{"telephone":"138***8009","id":9,"name":"Zheng Shiyi","location":"Xian","age":29},{"telephone":"138***8010","id":10,"name":"Wang Shijun","location":"Chongqing","age":31}]
   

   You can see that the RESTful APIs for creating, reading, updating, and deleting data have been successfully generated:

   from fastapi import FastAPI, Depends, HTTPException
   from sqlalchemy.orm import Session
   from models import Customer
   from database import SessionLocal, engine
   from pydantic import BaseModel
   
   app = FastAPI()
   
   # Database dependency
   def get_db():
       db = SessionLocal()
       try:
           yield db
       finally:
           db.close()
   
   # Request model
   class CustomerCreate(BaseModel):
       name: str
       age: int
       telephone: str
       location: str
   
   # Response model
   class CustomerResponse(CustomerCreate):
       id: int
   
       class Config:
           from_attributes = True
   
   @app.post("/customers/")
   def create_customer(customer: CustomerCreate, db: Session = Depends(get_db)):
       db_customer = Customer(**customer.model_dump())
       db.add(db_customer)
       db.commit()
       db.refresh(db_customer)
       return db_customer
   
   @app.get("/customers/{customer_id}")
   def read_customer(customer_id: int, db: Session = Depends(get_db)):
       customer = db.query(Customer).filter(Customer.id == customer_id).first()
       if customer is None:
           raise HTTPException(status_code=404, detail="Customer not found")
       return customer
   
   @app.get("/customers/")
   def read_customers(skip: int = 0, limit: int = 10, db: Session = Depends(get_db)):
       return db.query(Customer).offset(skip).limit(limit).all()
   
   @app.put("/customers/{customer_id}")
   def update_customer(customer_id: int, customer: CustomerCreate, db: Session = Depends(get_db)):
       db_customer = db.query(Customer).filter(Customer.id == customer_id).first()
       if db_customer is None:
           raise HTTPException(status_code=404, detail="Customer not found")
       for field, value in customer.model_dump().items():
           setattr(db_customer, field, value)
       db.commit()
       db.refresh(db_customer)
       return db_customer
   
   @app.delete("/customers/{customer_id}")
   def delete_customer(customer_id: int, db: Session = Depends(get_db)):
       customer = db.query(Customer).filter(Customer.id == customer_id).first()
       if customer is None:
           raise HTTPException(status_code=404, detail="Customer not found")
       db.delete(customer)
       db.commit()
       return {"message": "Customer deleted successfully"}