Table Schema Change Gone Wrong

How It Started

I was working on a codebase that had a table named mcp-server. This table contained a column called serverUrlEnvVar, which stored the environment variable reference for the server URL.

Later, we realized that we didn’t actually need to store the MCP server URL as an environment variable reference. Instead, we could directly store the server URL itself in the table.

So the task seemed simple: rename the column from serverUrlEnvVar to serverUrl.

I opened schema.prisma, which originally looked like this:

model McpServer {
  id                     String   @id @default(uuid())
  name                   String
  slug                   String   @unique @db.VarChar(100)
  description            String   @db.VarChar(512)
  serverUrlEnvVar        String
  createdAt              DateTime @default(now())
  updatedAt              DateTime @updatedAt
}

I updated it to:

model McpServer {
  id                     String   @id @default(uuid())
  name                   String
  slug                   String   @unique @db.VarChar(100)
  description            String   @db.VarChar(512)
  serverUrl              String
  createdAt              DateTime @default(now())
  updatedAt              DateTime @updatedAt
}

For context, this was a NestJS project using Prisma as the ORM.

How It Went Wrong

I ran the migration command and raised a PR. Prisma generated the following SQL:

ALTER TABLE "mcp_servers"
DROP COLUMN "serverUrlEnvVar",
ADD COLUMN "serverUrl" TEXT NOT NULL;

And I didn’t check it. That was my first mistake.

The PR was reviewed and merged and the issue wasn’t caught during review either.

We have a deployment pipeline where changes are first deployed to the dev environment for testing before going to production. As soon as this change was deployed to dev, the application went down completely.

Why?

Let’s break it down.

The mcp_servers table already had a few records, all with values in the serverUrlEnvVar column. During migration, Prisma attempted to:

1. Drop the serverUrlEnvVar column
2. Add a new column serverUrl
3. Mark it as NOT NULL

But there was no instruction on how to populate serverUrl for the existing rows. Since the column was non-nullable, the migration failed.

How We Found the Issue

Prisma maintains a table called _prisma_migrations, which records all executed migrations. This table includes a logs column that remains NULL if a migration runs successfully.

In our case, the logs column was not NULL and clearly described the failure, which helped us quickly identify the root cause.

How We Fixed the Issue

Since this feature was not yet live in production and the table contained only a few records, we chose the simplest fix:

• Deleted the existing entries from both the dev and prod databases
• Re-ran the migration
• Manually reinserted the records with the correct serverUrl values

Because the data was minimal and non-critical, this approach worked well. After that, deploying the change to production went smoothly.

We could have also change the query to just a rename query as the data type was already compatible.

ALTER TABLE "mcp_servers"
RENAME COLUMN "serverUrlEnvVar" TO "serverUrl";

Key Learnings

• Always review the SQL queries generated by your ORM.
• Be extra cautious with schema changes, especially on tables that already contain data.
• Understanding the _prisma_migrations table is extremely helpful for debugging failed migrations.