How to fix P2033: A number used in the query does not fit into a 64 bit signed integer in Prisma

// Before: May cause P2033 for large values
model Transaction {
  id        Int     @id @default(autoincrement())
  amount    Int     // May overflow for large amounts
  timestamp Int     // May overflow for future dates
}

// After: Uses BigInt to prevent overflow
model Transaction {
  id        Int     @id @default(autoincrement())
  amount    BigInt  // Can store arbitrarily large amounts
  timestamp BigInt  // Can store future timestamps safely
}

// Wrong: Using regular number (may lose precision or overflow)
const largeAmount = 10000000000000000000; // Exceeds MAX_SAFE_INTEGER

// Right: Using BigInt literal
const largeAmount = 10000000000000000000n; // BigInt, preserves precision

// Working with BigInt in Prisma queries
await prisma.transaction.create({
  data: {
    amount: 10000000000000000000n, // BigInt literal
    timestamp: BigInt(Date.now()) // Convert to BigInt
  }
});

// For values from user input or APIs
const userAmount = BigInt(req.body.amount); // Convert string to BigInt

// Migration script to convert Int to BigInt
async function migrateIntToBigInt() {
  // 1. First, update your Prisma schema to use BigInt
  // 2. Create and apply a migration
  // 3. Run data migration if needed

  const transactions = await prisma.transaction.findMany();
  
  for (const transaction of transactions) {
    // Convert Int values to BigInt
    await prisma.transaction.update({
      where: { id: transaction.id },
      data: {
        amount: BigInt(transaction.amount),
        timestamp: BigInt(transaction.timestamp)
      }
    });
  }
}

// For large datasets, use batch operations
async function batchMigrate() {
  const batchSize = 1000;
  let skip = 0;
  
  while (true) {
    const transactions = await prisma.transaction.findMany({
      take: batchSize,
      skip: skip
    });
    
    if (transactions.length === 0) break;
    
    await prisma.$transaction(
      transactions.map(transaction =>
        prisma.transaction.update({
          where: { id: transaction.id },
          data: {
            amount: BigInt(transaction.amount),
            timestamp: BigInt(transaction.timestamp)
          }
        })
      )
    );
    
    skip += batchSize;
  }
}

function validateNumberInput(value: string | number): BigInt | null {
  try {
    const bigIntValue = BigInt(value);
    
    // Check if it fits in 64-bit signed integer range
    const MAX_64BIT = 9223372036854775807n;
    const MIN_64BIT = -9223372036854775808n;
    
    if (bigIntValue > MAX_64BIT || bigIntValue < MIN_64BIT) {
      console.warn(`Value ${value} exceeds 64-bit signed integer range`);
      return null;
    }
    
    return bigIntValue;
  } catch (error) {
    console.error(`Invalid number input: ${value}`, error);
    return null;
  }
}

// Usage in your application
const userInput = req.body.amount;
const validatedAmount = validateNumberInput(userInput);

if (!validatedAmount) {
  return res.status(400).json({ error: 'Invalid amount specified' });
}

// Safe to use with Prisma
await prisma.transaction.create({
  data: {
    amount: validatedAmount,
    // ... other fields
  }
});

model FinancialRecord {
  id        Int     @id @default(autoincrement())
  
  // For currency amounts (use Decimal for precision)
  amount    Decimal @db.Decimal(19, 4) // 19 total digits, 4 decimal places
  
  // For large counters or IDs
  bigCounter BigInt
  
  // For normal integers within safe range
  quantity  Int
  
  // For floating point numbers
  ratio     Float
  
  // For timestamps (consider DateTime type instead)
  createdAt DateTime @default(now())
}

// Alternative: Store timestamps as DateTime instead of numeric
model Event {
  id        Int      @id @default(autoincrement())
  timestamp DateTime // Better than BigInt for timestamps
  
  // If you need millisecond precision
  timestampMs BigInt // Only if you need milliseconds specifically
}

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

async function createTransaction(data: any) {
  try {
    const transaction = await prisma.transaction.create({
      data: {
        amount: data.amount,
        timestamp: data.timestamp,
        // ... other fields
      }
    });
    return transaction;
  } catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError) {
      if (error.code === 'P2033') {
        // Handle the integer overflow case
        console.error('Number too large for database field', error.message);
        
        // Suggest using BigInt or validating input
        throw new Error(
          `The value ${data.amount} is too large. ` +
          `Consider using BigInt type or validating input size.`
        );
      }
    }
    // Re-throw other errors
    throw error;
  }
}

// Alternative: Proactive checking
function ensureFitsIn64Bit(value: BigInt | number): boolean {
  const bigIntValue = typeof value === 'bigint' ? value : BigInt(value);
  const MAX_64BIT = 9223372036854775807n;
  const MIN_64BIT = -9223372036854775808n;
  
  return bigIntValue >= MIN_64BIT && bigIntValue <= MAX_64BIT;
}

// Check before database operation
if (!ensureFitsIn64Bit(data.amount)) {
  throw new Error('Amount exceeds 64-bit integer range');
}