The Prisma findUnique That Returned null (But the Data Existed)

"Your API is returning 404 for user profiles."

I checked the database. The user existed.

I checked the API endpoint with the same ID. 404.

I ran the query manually in psql. It worked.

This made zero sense.

The Setup

We had a PERN stack with:

• PostgreSQL 14
• Prisma 4.8 (ORM)
• Express.js
• UUID primary keys

A simple user lookup:

// Route handler
app.get('/api/users/:id', async (req, res) => {
  const { id } = req.params;
  
  const user = await prisma.user.findUnique({
    where: { id: id }
  });
  
  if (!user) {
    return res.status(404).json({ error: 'User not found' });
  }
  
  res.json(user);
});

It worked for 99% of users. But for about 1%, it returned 404 even though the user existed.

The Investigation

I added logging:

console.log('Looking for user with ID:', id);
const user = await prisma.user.findUnique({ where: { id } });
console.log('Found user:', user);

The logs showed:

Looking for user with ID: 123e4567-e89b-12d3-a456-426614174000
Found user: null

But in the database:

SELECT * FROM users WHERE id = '123e4567-e89b-12d3-a456-426614174000';
-- Returns the user!

How can Prisma return null when the exact same query works in psql?

The Root Cause

UUIDs in PostgreSQL are stored as 128-bit integers. But when you query with a string, PostgreSQL automatically casts:

-- This works
SELECT * FROM users WHERE id = '123e4567-e89b-12d3-a456-426614174000';

However, Prisma was generating a parameterized query:

-- Prisma generated
SELECT * FROM users WHERE id = $1
-- $1 = '123e4567-e89b-12d3-a456-426614174000'

Still works, right? Not if the case of the letters is different.

PostgreSQL UUIDs are case-insensitive when stored, but case-sensitive when comparing strings.

The problem: Our frontend was lowercasing the UUID before sending it to the API.

But the database had mixed-case UUIDs (some uppercase letters).

When we queried with lowercase:

-- Database has '123E4567-E89B-12D3-A456-426614174000'
-- Query with '123e4567-e89b-12d3-a456-426614174000'
-- In a string comparison, these are NOT equal

But why did psql return the row? Because psql's implicit casting treated the string as a UUID, not a string:

-- psql casts to UUID, which ignores case
SELECT * FROM users WHERE id = '123e4567...'::uuid;  -- works

Prisma was sending the value as a text parameter, not a UUID parameter.

The Fix

1. Ensure UUIDs are consistently formatted

// Normalize UUID to lowercase before storing
const normalizedUuid = uuid.v4().toLowerCase();

// Always query with lowercase
const user = await prisma.user.findUnique({
  where: { id: id.toLowerCase() }
});

2. Use Prisma's native UUID type

// schema.prisma
model User {
  id   String @id @default(uuid()) @db.Uuid
  name String
}

This ensures Prisma treats the field as UUID in queries.

3. Add a raw SQL fallback

// If findUnique fails, try raw query with UUID cast
if (!user) {
  const rawUser = await prisma.$queryRaw`
    SELECT * FROM users WHERE id = ${id}::uuid
  `;
  user = rawUser[0];
}

4. Migrate existing mixed-case UUIDs

-- Normalize all UUIDs to lowercase
UPDATE users SET id = LOWER(id);
-- Update foreign keys too
UPDATE orders SET user_id = LOWER(user_id);

The Prevention

Validate UUID format on input

import { validate, version } from 'uuid';

const isValidUUID = (id: string) => {
  return validate(id) && version(id) === 4;
};

// In route
if (!isValidUUID(id)) {
  return res.status(400).json({ error: 'Invalid UUID format' });
}

Use Prisma's $queryRaw with type hints

// Force UUID type in raw query
const users = await prisma.$queryRaw<Array<User>>`
  SELECT * FROM users 
  WHERE id = ${id}::uuid
`;

Add a database trigger to enforce case

CREATE OR REPLACE FUNCTION normalize_uuid()
RETURNS TRIGGER AS $$
BEGIN
  NEW.id = LOWER(NEW.id);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_lowercase_uuid
BEFORE INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION normalize_uuid();

Commands to Debug UUID Issues

Check UUID case sensitivity

-- These return different results depending on casting
SELECT '123E4567-E89B-12D3-A456-426614174000' = '123e4567-e89b-12d3-a456-426614174000'; -- false
SELECT '123E4567-E89B-12D3-A456-426614174000'::uuid = '123e4567-e89b-12d3-a456-426614174000'::uuid; -- true

Find mixed-case UUIDs

SELECT id FROM users WHERE id != LOWER(id);

See Prisma's actual query

// Enable query logging
const prisma = new PrismaClient({ log: ['query', 'info', 'warn', 'error'] });

What I Learned

• UUIDs in PostgreSQL are integers, not strings.
• Prisma sends them as parameters with type "text" unless you use "@db.Uuid".
• Always normalize UUIDs to a consistent case (lowercase) at the application boundary.
• Raw SQL with "::uuid" cast can save you in emergencies.

That 404 bug wasted 6 hours of debugging. Now we have a linter rule that forces "toLowerCase()" on all UUID parameters.