MCP (Model Context Protocol) is an open-source protocol launched by Anthropic in November 2024. It enables large language models to interact with external tools or data sources. With MCP, users no longer need to manually copy and execute the output of large models. Instead, the large models can directly command tools to perform specific actions.
OceanBase MCP Server provides the capability for large models to interact with OceanBase Database through the MCP protocol. It supports executing SQL statements, helping developers quickly build prototype applications.
Codex is an open-source AI programming agent developed by OpenAI. Built with Rust, it can be run in a terminal. Codex can read, modify, and execute code, helping developers quickly convert ideas into high-quality code. It supports three usage modes: CLI, IDE extensions, and Codex App. This topic demonstrates how to integrate OceanBase MCP Server with Codex CLI to quickly build backend applications.
Prerequisites
You have a transactional (MySQL) cluster instance available in your environment.
Currently, shared instance-related activities are being upgraded, and it is not supported. Please use a cluster instance to complete this tutorial.
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 read and write to instances in the project. If you do not have the required permissions, contact your organization administrator to grant you 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 Node.js 18 or later and Git.
Install Codex CLI and configure the OpenAI API Key.
npm install -g @openai/codex export OPENAI_API_KEY="your_api_key" # Verify the installation codex --help
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 Codex
Create a working directory
Manually create a Codex working directory and open it, for example, codex-generate. The files generated by Codex will be saved in this directory.
Add MCP Server configuration
You can access the MCP Server through the CLI based on the Codex CLI version:
codex mcp add ob-mcp-server \
--env OB_HOST=*** \
--env OB_PORT=*** \
--env OB_USER=*** \
--env OB_PASSWORD=*** \
--env OB_DATABASE=*** \
-- uvx --from oceanbase-mcp oceanbase_mcp_server
Please replace the following parameters with your own database information:
OB_HOSTOB_PORTOB_USEROB_PASSWORDOB_DATABASE
Test MCP connection
Start Codex in the project root directory:
codex
In the Codex dialog box, enter the prompt:
How many tables are there in the test database?
If the correct result is returned, it indicates that Codex has successfully accessed the database through OceanBase MCP Server.
Step 3: Use Codex to generate a FastAPI project
You can use FastAPI to quickly create a RESTful API project. FastAPI is a Python web framework that allows you to build RESTful APIs quickly.
Create a sample table
In the Codex chat, enter the following prompt:
Create a customer table with the following fields: ID (primary key), name, age, telephone, and location.
Insert test data
Continue with the following prompt:
Insert 10 records into the customer table.
After the insertion is successful, you will see a message like 10 rows inserted into test.customer.
Generate RESTful API code
Enter the following prompt to let Codex generate a FastAPI project based on the customer table:
Create a FastAPI project and generate a RESTful API based on the customer table.
Configure the database connection
Update the database connection parameters to the actual OceanBase connection information.
For example:
DATABASE_URL=mysql+pymysql://user:password@host:3306/test
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:
cd customer_api
uv venv
source .venv/bin/activate
uv pip install -r requirements.txt
Start the FastAPI service
Run the following command to start the project (adjust the entry file name as needed):
uvicorn main:app --reload
Verify the API
After the service starts, you can verify it as follows:
- Access
http://127.0.0.1:8000/docsto view the Swagger documentation. - Use
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 Codex and driven by OceanBase, capable of performing common operations such as adding, deleting, updating, and querying data in the customer table.
Appendix: Complete code example of the RESTful API
After the RESTful APIs for adding, deleting, updating, and querying are successfully generated, the code example is as follows:
from fastapi import Depends, FastAPI, HTTPException, status
from sqlalchemy.orm import Session
from . import crud, schemas
from .db import SessionLocal
app = FastAPI(title="Customer API")
def get_db():
db = SessionLocal()
try:
yield db
finally:
db.close()
@app.get("/customers", response_model=list[schemas.CustomerOut])
def list_customers(
skip: int = 0, limit: int = 100, db: Session = Depends(get_db)
):
return crud.get_customers(db, skip=skip, limit=limit)
@app.get("/customers/{customer_id}", response_model=schemas.CustomerOut)
def get_customer(customer_id: int, db: Session = Depends(get_db)):
customer = crud.get_customer(db, customer_id)
if not customer:
raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Not found")
return customer
@app.post("/customers", response_model=schemas.CustomerOut, status_code=201)
def create_customer(
customer_in: schemas.CustomerCreate, db: Session = Depends(get_db)
):
return crud.create_customer(db, customer_in)
@app.put("/customers/{customer_id}", response_model=schemas.CustomerOut)
def update_customer(
customer_id: int, customer_in: schemas.CustomerUpdate, db: Session = Depends(get_db)
):
customer = crud.get_customer(db, customer_id)
if not customer:
raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Not found")
return crud.update_customer(db, customer, customer_in)
@app.delete("/customers/{customer_id}", status_code=204)
def delete_customer(customer_id: int, db: Session = Depends(get_db)):
customer = crud.get_customer(db, customer_id)
if not customer:
raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Not found")
crud.delete_customer(db, customer)