MCP (Model Context Protocol) is an open-source protocol launched by Anthropic in November 2024. It enables large language models (LLMs) to interact with external tools or data sources. With MCP, users no longer need to manually copy and execute the output of an LLM. Instead, the LLM can directly instruct the tool to perform specific actions (Actions).
OceanBase MCP Server provides the capability for large models to interact with OceanBase Database through the MCP protocol. It can execute SQL statements and help developers quickly build prototype applications.
GitHub Copilot is an AI programming assistant that helps developers turn ideas into code quickly. This topic will guide you on how to use GitHub Copilot in VS Code to connect to OceanBase MCP Server and quickly generate a FastAPI-style RESTful API project based on the database.
Prerequisites
You have a transactional instance available in your environment, and a MySQL-compatible tenant under the instance.
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 have the project administrator or instance administrator role to perform read and write operations on instances in the project. If you do not have the required permissions, contact your organization administrator to add the permissions.
Install 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 the Python package manager uv.
Install Visual Studio Code and install the
GitHub Copilotextension from the Extensions Marketplace.
Step 1: Get an OceanBase Cloud connection string
Log in to the OceanBase Cloud console. On the instance list, expand the target transactional instance. Under the target tenant, choose Connect > Get Connection String.
In the dialog, select Public Network.
On the Connect via Public Network page, complete the following to generate a connection string:
(Optional) If the tenant does not have a public endpoint yet, under Step 1: Get Public Endpoint, click Get Public Endpoint. If one already exists, skip this step. After the endpoint is ready, click Next Step.
Under Step 2: Security Settings, click Add to configure the allowlist as needed, then click Next Step.
Under Step 3: Access Database, set options according to your tenant mode:
MySQL-compatible tenant: Select the database and account you created in the prerequisites, and choose MySQL CLI as the connection method. Then copy the connection string for the next section.
Oracle-compatible tenant: Select the account you created in the prerequisites, and choose OBClient as the connection method. Then copy the connection string for the next section.
Step 2: Configure OceanBase MCP Server in GitHub Copilot
Create a workspace
Manually create a VS Code workspace and open it, for example, github-copilot-generate. The files generated by GitHub Copilot will be saved in this directory.
Add MCP Server configuration
You can add MCP Server by using the command panel or configuration file:
- Command panel method: Press
Ctrl + Shift + P(Windows/Linux) orCommand + Shift + P(macOS), enterMCP: Add Server, selectCommand (stdio), and then fill in the information as prompted. - Configuration file method: Create a
.vscode/mcp.jsonfile in the project root directory and write the following configuration:
{
"servers": {
"ob-mcp-server": {
"type": "stdio",
"command": "uvx",
"args": [
"--from",
"oceanbase-mcp",
"oceanbase_mcp_server"
],
"env": {
"OB_HOST": "***",
"OB_PORT": "***",
"OB_USER": "***",
"OB_PASSWORD": "***",
"OB_DATABASE": "***"
}
}
},
"inputs": []
}
Replace the following parameters with your own database information:
OB_HOSTOB_PORTOB_USEROB_PASSWORDOB_DATABASE
After the configuration is completed, you can view the connection status of the MCP Server by using the MCP: List Servers command in the command panel.
Test MCP connection
In the Copilot dialog box, enter the following prompt:
How many tables are there in the test database?
If the correct result is returned, it indicates that GitHub Copilot has successfully accessed the database through OceanBase MCP Server.
Step 3: Use GitHub Copilot to generate a FastAPI project
You can use FastAPI to quickly create a project in the RESTful API style. FastAPI is a Python web framework that allows you to build RESTful APIs quickly.
Create a sample table
In the Copilot dialog box, enter the following prompt:
Create a customer table with ID as the primary key, and name, age, telephone, and location as fields.
After the SQL statement is generated, click Allow to execute it.
Insert test data
Enter the following prompt:
Insert 10 rows of data into the customer table.
After the SQL statement is generated, click Allow to execute it.
Generate RESTful API code
Enter the following prompt to let Copilot generate a FastAPI project based on the customer table:
Create a FastAPI project and generate a RESTful API based on the customer table.
After the code is generated, click Allow to save the content.
Configure the database connection
Update the database connection parameters in database.py or DATABASE_URL to the actual OceanBase connection information.
Create a virtual environment and install dependencies
Run the following command in the current directory to create a virtual environment and install the required packages using the uv package manager:
uv venv
source .venv/bin/activate
uv pip install -r requirements.txt
Start the FastAPI service
Run the following command to start the service (modify the entry file name as needed):
uvicorn main:app --reload
Verify the API
After the service is started, you can verify it by:
- Accessing
http://127.0.0.1:8000/docsto view the Swagger documentation. - Using
curlor Postman to call the CRUD interfaces and verify the results.
For example, to query all customers:
curl "http://127.0.0.1:8000/customers"
After completing all the steps above, you will have a FastAPI RESTful API project generated by GitHub Copilot and driven by OceanBase, capable of performing common operations such as adding, deleting, modifying, and querying data in the customer table.
Appendix: Complete code example of the RESTful API
After the RESTful API for adding, deleting, modifying, and querying data is successfully generated, the code is as follows:
from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.orm import Session
from sqlalchemy import text
from typing import List
from database import get_db
from models import Customer, CustomerCreate, CustomerUpdate
app = FastAPI(title="Customer API", version="1.0.0")
@app.get("/customers", response_model=List[Customer])
def get_all_customers(db: Session = Depends(get_db)):
"""Get all customers"""
result = db.execute(text("SELECT id, name, age, telephone, location FROM test.customer"))
customers = []
for row in result:
customers.append({
"id": row[0],
"name": row[1],
"age": row[2],
"telephone": row[3],
"location": row[4]
})
return customers
@app.get("/customers/{customer_id}", response_model=Customer)
def get_customer(customer_id: int, db: Session = Depends(get_db)):
"""Get a customer by ID"""
result = db.execute(
text("SELECT id, name, age, telephone, location FROM test.customer WHERE id = :id"),
{"id": customer_id}
)
row = result.first()
if not row:
raise HTTPException(status_code=404, detail="Customer not found")
return {
"id": row[0],
"name": row[1],
"age": row[2],
"telephone": row[3],
"location": row[4]
}
@app.post("/customers", response_model=Customer)
def create_customer(customer: CustomerCreate, db: Session = Depends(get_db)):
"""Create a new customer"""
db.execute(
text("INSERT INTO test.customer (id, name, age, telephone, location) VALUES (:id, :name, :age, :telephone, :location)"),
{
"id": customer.id,
"name": customer.name,
"age": customer.age,
"telephone": customer.telephone,
"location": customer.location
}
)
db.commit()
return {
"id": customer.id,
"name": customer.name,
"age": customer.age,
"telephone": customer.telephone,
"location": customer.location
}
@app.put("/customers/{customer_id}", response_model=Customer)
def update_customer(customer_id: int, customer: CustomerUpdate, db: Session = Depends(get_db)):
"""Update a customer"""
# Get current customer
result = db.execute(
text("SELECT id, name, age, telephone, location FROM test.customer WHERE id = :id"),
{"id": customer_id}
)
row = result.first()
if not row:
raise HTTPException(status_code=404, detail="Customer not found")
# Prepare update data
update_data = {
"id": customer_id,
"name": customer.name if customer.name is not None else row[1],
"age": customer.age if customer.age is not None else row[2],
"telephone": customer.telephone if customer.telephone is not None else row[3],
"location": customer.location if customer.location is not None else row[4]
}
# Update customer
db.execute(
text("UPDATE test.customer SET name = :name, age = :age, telephone = :telephone, location = :location WHERE id = :id"),
update_data
)
db.commit()
return update_data
@app.delete("/customers/{customer_id}")
def delete_customer(customer_id: int, db: Session = Depends(get_db)):
"""Delete a customer"""
result = db.execute(
text("SELECT id FROM test.customer WHERE id = :id"),
{"id": customer_id}
)
if not result.first():
raise HTTPException(status_code=404, detail="Customer not found")
db.execute(
text("DELETE FROM test.customer WHERE id = :id"),
{"id": customer_id}
)
db.commit()
return {"message": "Customer deleted successfully"}
@app.get("/")
def read_root():
"""Root endpoint"""
return {"message": "Customer API - use /customers to manage customer data"}
if __name__ == "__main__":
import uvicorn
uvicorn.run(app, host="0.0.0.0", port=8000)