How to fix P2023: Inconsistent column data in Prisma

import { Prisma } from '@prisma/client';

try {
  const record = await prisma.user.findUnique({
    where: { id: 'some-id' }
  });
} catch (error) {
  if (error instanceof Prisma.PrismaClientKnownRequestError) {
    if (error.code === 'P2023') {
      console.log('Full error message:', error.message);
      // Example: "Inconsistent column data: Conversion failed: Value 9223372036854775807
      // does not fit in an INT column, try migrating the 'int' column type to BIGINT."
    }
  }
}

model User {
  id       String   @id @default(cuid())
  largeNum BigInt   // Changed from Int to BigInt
  name     String
}

npx prisma migrate dev --name migrate_int_to_bigint

// Note: BigInt in JavaScript uses the 'n' suffix
const user = await prisma.user.findUnique({
  where: { id: 'user-1' }
});

// user.largeNum is now a BigInt
console.log(typeof user.largeNum); // 'bigint'

// Convert to string if needed
const numberAsString = user.largeNum.toString();

// JSON serialization requires custom handling
JSON.stringify(user, (key, value) =>
  typeof value === 'bigint' ? value.toString() : value
);

// Add a middleware or custom JSON replacer for API responses
const jsonReplacer = (key: string, value: any) => {
  if (typeof value === 'bigint') {
    return value.toString();
  }
  return value;
};

app.post('/api/users', async (req, res) => {
  const user = await prisma.user.findUnique({
    where: { id: 'user-1' }
  });

  res.json(JSON.parse(JSON.stringify(user, jsonReplacer)));
});

npx prisma db pull

# View what changed
git diff prisma/schema.prisma

// If database has VARCHAR but schema expects Int
model Product {
  id       String  @id @default(cuid())
  price    Decimal @db.Decimal(10, 2)  // Explicitly specify database type
  quantity Int     // Should match database type
}

npx prisma migrate dev --name fix_schema_mismatch

// MongoDB migration script
db.users.updateMany(
  { age: { $type: "string" } },
  [
    {
      $set: {
        age: { $toInt: "$age" }
      }
    }
  ]
);

// Prisma with explicit type casting
const users = await prisma.user.findMany({
  select: {
    id: true,
    age: true
  }
});

// Cast strings to numbers if needed
const usersWithCorrectTypes = users.map(user => ({
  ...user,
  age: typeof user.age === 'string' ? parseInt(user.age, 10) : user.age
}));

-- SQL migration for MongoDB (if using MongoDB with SQL migration tool)
UPDATE users SET age = CAST(age AS INTEGER) WHERE age IS NOT NULL;

import { validate as validateUUID } from 'uuid';

async function safeQuery(userId: string) {
  // Validate UUID format before querying
  if (!validateUUID(userId)) {
    throw new Error(`Invalid UUID format: ${userId}`);
  }

  try {
    return await prisma.user.findUnique({
      where: { id: userId }
    });
  } catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError &&
        error.code === 'P2023') {
      throw new Error('Invalid UUID in database or query');
    }
    throw error;
  }
}

// In your API handlers
app.get('/users/:id', async (req, res) => {
  try {
    const user = await safeQuery(req.params.id);
    res.json(user);
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

// Remove or comment out problematic features
generator client {
  provider = "prisma-client-js"
  // previewFeatures = ["relationJoins"]  // Comment out
}

npx prisma generate

import { Prisma } from '@prisma/client';

async function fetchUserWithErrorHandling(userId: string) {
  try {
    return await prisma.user.findUnique({
      where: { id: userId }
    });
  } catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError) {
      if (error.code === 'P2023') {
        console.error('Data consistency error:', error.message);
        // Log for debugging
        logger.error('P2023 inconsistent column data', {
          userId,
          originalError: error.meta
        });

        // Return graceful error to user
        throw new Error(
          'Unable to retrieve user data. Please contact support if this persists.'
        );
      }
    }
    throw error;
  }
}

// In Express/Next.js
app.get('/api/users/:id', async (req, res) => {
  try {
    const user = await fetchUserWithErrorHandling(req.params.id);
    res.json(user);
  } catch (error) {
    res.status(500).json({
      error: error.message,
      code: 'INTERNAL_ERROR'
    });
  }
});