Integrate OceanBase MCP Server with Augment Code

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

OceanBase MCP Server provides the capability for large models to interact with OceanBase Database through the MCP protocol and execute SQL statements. With an appropriate client, you can quickly build a project prototype. It is open-sourced on GitHub.

Augment Code is a developer AI platform that helps you understand, debug, and deploy your code faster by learning your codebase. It allows you to chat, edit, and complete code to accomplish more tasks.

This topic will show you on how to use OceanBase MCP Server and Augment Code to quickly build a backend application.

Prerequisites

• An OceanBase Cloud 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.

• You have installed Git based on your operating system:

  • Windows: https://git-scm.com/downloads/win.

  • macOS: https://git-scm.com/download/mac.

  • Linux: https://git-scm.com/downloads/linux.

• You have installed the Python package manager uv.

  • For macOS and Linux, you can install it using the following script:

    curl -LsSf https://astral.sh/uv/install.sh | sh
    

  • For Windows, use the following script:

    irm https://astral.sh/uv/install.ps1 | iex
    

  • Alternatively, you can use the platform-independent pip installation method:

    pip install uv
    

• After the installation, run the uv --version command to verify the installation:

  pip install uv
  uv --version
  

• You have installed Augment Code:

  1. Open VS Code (if not installed, refer to the official documentation for installation).

  2. Click the Extensions icon in the sidebar.

  3. Search for "Augment Code" and click Install.

  4. Register and log in to your Augment Code account.

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 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/awesome-oceanbase-mcp.git

Navigate to the source code directory:

cd awesome-oceanbase-mcp

Install dependencies

In the oceanbase_mcp_server directory, run the following command to create a virtual environment and install the dependencies:

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

Add and configure MCP Server

1. Configure OceanBase MCP Server in Augment Code.

   

2. Click Import from json and fill in the MCP configuration file.

   

   

3. Fill in the configuration file and click Confirm.

   Replace /path/to/your/oceanbase_mcp_server with the absolute path of the oceanbase_mcp_server folder. Replace OB_HOST, OB_PORT, OB_USER, OB_PASSWORD, and OB_DATABASE with the corresponding information of your database:

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

4. Verify whether you can connect to the database.

   Enter the prompt "How many tables are in the test database?":

   Augment Code will display the number of tables in the test database, indicating a successful connection to OceanBase Database.

Step 3: Use FastAPI to quickly create a RESTful API project

FastAPI is a Python web framework for building RESTful APIs.

1. Create a table.

   Enter the prompt "Create a customer table with ID as the primary key and name, age, telephone, and location as fields":

2. Insert test data.

   Enter the prompt "Insert 10 test records":

3. Create a FastAPI project.

   Enter the prompt "Create a FastAPI project and generate a RESTful API based on the customer table". Multiple files are automatically generated, the code is automatically fixed, and the FastAPI project is started:

4. View the data in the table.

   Run curl http://127.0.0.1:8000/customers in the command line or use other request tools to view the data in the table:

   curl http://127.0.0.1:8000/customers
   [{"name":"Zhang San","age":28,"telephone":"13812345678","location":"Chaoyang District, Beijing","id":1},{"name":"Li Si","age":35,"telephone":"13987654321","location":"Pudong New Area, Shanghai","id":2},{"name":"Wang Wu","age":42,"telephone":"15612345678","location":"Tianhe District, Guangzhou","id":3},{"name":"Zhao Liu","age":29,"telephone":"18712345678","location":"Nanshan District, Shenzhen","id":4},{"name":"Qian Qi","age":33,"telephone":"13512345678","location":"West Lake District, Hangzhou","id":5},{"name":"Sun Ba","age":26,"telephone":"15987654321","location":"Jinjiang District, Chengdu","id":6},{"name":"Zhou Ji","age":38,"telephone":"18612345678","location":"Jianghan District, Wuhan","id":7},{"name":"Wu Shi","age":31,"telephone":"13712345678","location":"Gulou District, Nanjing","id":8},{"name":"Zheng Shiyi","age":45,"telephone":"15812345678","location":"Yanta District, Xi'an","id":9},{"name":"Wang Shier","age":27,"telephone":"18512345678","location":"Yuzhong District, Chongqing","id":10},{"name":"Test User","age":25,"telephone":"13900000000","location":"Test Address","id":11}]
   

5. The code for adding, deleting, modifying, and querying data has been generated.

   from sqlalchemy.orm import Session
   from database import Customer
   from schemas import CustomerCreate, CustomerUpdate
   from typing import List, Optional
   
   def get_customer(db: Session, customer_id: int) -> Optional[Customer]:
    """Get a single customer by ID"""
    return db.query(Customer).filter(Customer.id == customer_id).first()
   
   def get_customers(db: Session, skip: int = 0, limit: int = 100) -> List[Customer]:
    """Get a list of customers"""
    return db.query(Customer).offset(skip).limit(limit).all()
   
   def create_customer(db: Session, customer: CustomerCreate) -> Customer:
    """Create a new customer"""
    db_customer = Customer(**customer.dict())
    db.add(db_customer)
    db.commit()
    db.refresh(db_customer)
    return db_customer
   
   def update_customer(db: Session, customer_id: int, customer: CustomerUpdate) -> Optional[Customer]:
    """Update customer information"""
    db_customer = db.query(Customer).filter(Customer.id == customer_id).first()
    if db_customer:
        update_data = customer.dict(exclude_unset=True)
        for field, value in update_data.items():
            setattr(db_customer, field, value)
        db.commit()
        db.refresh(db_customer)
    return db_customer
   
   def delete_customer(db: Session, customer_id: int) -> bool:
    """Delete a customer"""
    db_customer = db.query(Customer).filter(Customer.id == customer_id).first()
    if db_customer:
        db.delete(db_customer)
        db.commit()
        return True
    return False
   
   def search_customers_by_name(db: Session, name: str) -> List[Customer]:
    """Search for customers by name"""
    return db.query(Customer).filter(Customer.name.contains(name)).all()
   
   def get_customers_by_location(db: Session, location: str) -> List[Customer]:
    """Get customers by location"""
    return db.query(Customer).filter(Customer.location.contains(location)).all()