Integrate OceanBase MCP Server with Codex

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 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 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

1. 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.

2. In the dialog, select Public Network.

3. On the Connect via Public Network page, complete the following to generate a connection string:

   1. (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.

   2. Under Step 2: Security Settings, click Add to configure the allowlist as needed, then click Next Step.

   3. 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_HOST
• OB_PORT
• OB_USER
• OB_PASSWORD
• OB_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/docs to view the Swagger documentation.
• Use curl or 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)