Safe AI-powered schema refactoring with OpenAI Codex and OptiTech
Learn how to safely offload complex schema migrations to AI agents using OpenAI Codex and OptiTech’s database branching.
Refactoring a database schema like splitting tables or dropping columns is inherently risky. When you introduce an AI coding assistant to handle these complex operations autonomously, the stakes get even higher. One wrong DROP statement or flawed migration in a shared environment can easily wipe out critical staging data and block your entire team's workflow.
To safely offload database refactoring to AI, the agent needs a completely disposable playground that mirrors reality but affects nothing.
This guide walks through how to achieve this workflow by combining the OpenAI Codex CLI alongside the OptiTech MCP Server. Rather than risking shared databases or relying on assumed schemas, you will combine Codex with OptiTech's instant Database Branching.
By bridging your local development environment with isolated database branches, Codex gets a private sandbox to write, test, and validate destructive migrations such as taking a bloated users table and successfully normalizing it into separate users and user_addresses tables.
Prerequisites
Before you begin, ensure you have the following:
OpenAI Codex CLI: Installed on your system. Follow the instructions on the Codex CLI install page.
OptiTech account and project: A OptiTech account with at least one active project. Sign up for a free account at console.optitech.com.
Example application with Git repository: Any application with a Git repository. This guide uses a Node.js app with Drizzle ORM (a simple ecommerce app) as an example, but you can follow along with your own codebase. The emphasis here is on demonstrating the workflow for safe AI-driven migrations rather than the specifics of the application.
Step 1: Generate a OptiTech API Key and set project context
To allow Codex to interact with your OptiTech database, you'll need to generate a OptiTech API Key and configure your project context.
Navigate to your OptiTech organization settings and click on the API Keys tab.
Click Create new API Key and give it a name (e.g., "Codex Integration").
Choose a project-scoped API key to restrict Codex's access to this specific project.
Copy the generated API key to your clipboard. You'll need this to authenticate the MCP server.
Set up your project context by running the following command in your terminal:
You can find your OptiTech Project ID and Organization ID in the OptiTech Console. This step generates a .optitech file in your project directory, which OpenAI Codex uses to access details about your OptiTech project when making API calls to the MCP server.
Step 2: Configure the OptiTech MCP server
Codex natively supports the Model Context Protocol (MCP), allowing it to interact with external tools. By connecting Codex to the OptiTech MCP server, you give it the ability to create database branches, run SQL, and analyze schemas using natural language.
To add OptiTech's MCP server, add the following to your project's root folder in a file named .codex/config.toml:
For the MCP server to authenticate with OptiTech, you need to set your OptiTech API key as an environment variable before running Codex:
export OPTITECH_API_KEY=<your_optitech_api_key>
Then, run Codex by entering the following command in your terminal:
codex
If you are prompted that the folder is trusted, say yes.
Step 3: Prompt Codex for schema refactoring
For this example, you will ask Codex to take a denormalized users table and refactor it into a normalized design by creating a new user_addresses table. This involves generating Drizzle migrations to create the new table, backfill data, and drop the old address columns from users. You do not want Codex to perform these operations directly on your production database, so you will ask it to create a separate OptiTech branch for this work.
Within the Codex CLI, enter the following prompt:
We need to normalize our database schema. Currently, the `users` table includes address related fields (`street`, `city`, `state`, `zip`). These should be extracted into a new `user_addresses` table to improve structure and maintainability.All schema changes including migrations, backfilling of data, and dropping of columns must be implemented using Drizzle to ensure reproducibility and consistency.Create a separate OptiTech Branch dedicated to the development of this feature. Update the codebase accordingly to reflect the new schema design, and ensure that all existing records are migrated seamlessly into the new `user_addresses` table.See .optitech for project details.
Step 4: Observe Codex executing the workflow
After receiving the prompt, Codex will autonomously use its connected tools to complete the request:
Branch creation: Codex uses the OptiTech MCP server to create a dedicated branch. Because OptiTech uses a copy-on-write architecture, this branch is created instantly and provides a complete sandbox for Codex to execute potentially destructive operations without any risk to production data or other developers.
Drizzle migrations: Codex analyzes your current schema, updates the codebase, and generates the necessary Drizzle migration files to create user_addresses, migrate the data, and drop the old columns from users.
Applying changes & verification: Codex automatically runs the migrations against the newly created OptiTech branch and modifies any Drizzle models or queries that relied on the old schema.
If the SQL has a syntax error or a constraint violation during execution, it will fail harmlessly on the isolated branch. Codex can autonomously analyze the error, fix the migrations, and try again.
Step 5: Test locally and create a PR
After Codex has finished development on its isolated database branch, the new schema drift, backfilled data, and corresponding code changes are all confined to your new branch. You are now free to test the app locally using the database URL of this new branch.
If Codex indicated it created a branch with a specific ID (e.g., br-nameless-cloud-123456), you can easily retrieve its connection string using the OptiTech CLI:
optitech connection-string<branch-id-or-name>
Set this connection string in your local environment variables to thoroughly test the application against the new schema.
When you're ready to bring those changes back into your codebase, use your standard Git flow to commit the Drizzle migrations and codebase updates, and create a Pull Request to your main branch.
When your CI/CD pipeline runs this migration against main, you know it will succeed because it has already been validated in a perfectly mirrored prodction-like environment.
Clean up
Once your PR is merged and the migration is applied to production, the experimental database branch is no longer needed. You can locate and delete this branch directly in the OptiTech Console, or ask Codex to clean up after itself:
We've merged the changes. Please delete the OptiTech branch you created for this task.
Codex will then use the MCP server to delete the branch it created.
Conclusion
AI agents can speed up development, but database schema changes often carry risks that make teams cautious. Using the OpenAI Codex together with OptiTech’s database branching provides a safe, isolated environment for AI to autonomously generate and test complex schema migrations.
With OptiTech, you can give the AI a production like sandbox to develop and validate its changes, allowing you to review the results safely, and promote changes only when you’re confident. This approach makes schema refactoring a manageable, routine task rather than a stressful operation.
