How to fix Value out of range for the type in Prisma

# For PostgreSQL, check the column definition
psql -d your_database -c "\d your_table"

# For MySQL
mysql -u user -p -e "DESCRIBE your_database.your_table;"

model YourModel {
  id       Int
  price    Decimal @db.Decimal(10, 2)  // Can store up to 99999999.99
  count    Int     @db.Int              // Range: -2,147,483,648 to 2,147,483,647
  amount   BigInt  @db.BigInt           // Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
  date     DateTime
}

-- Find records with zero datetime values
SELECT * FROM your_table WHERE date_column = '0000-00-00 00:00:00';

-- Update them to NULL
UPDATE your_table SET date_column = NULL WHERE date_column = '0000-00-00 00:00:00';

-- Or update to current timestamp
UPDATE your_table SET date_column = NOW() WHERE date_column = '0000-00-00 00:00:00';

model YourModel {
  id       Int     @id
  createdAt DateTime?  // Now nullable to accept NULL values
}

npx prisma migrate dev --name make_datetime_nullable

// Before: Can only store -99.99 to 99.99
price   Decimal @db.Decimal(4, 2)

// After: Can store -999999999.99 to 999999999.99
price   Decimal @db.Decimal(11, 2)

npx prisma migrate dev --name increase_decimal_precision

// ❌ This may cause precision loss
const record = await prisma.model.create({
  data: {
    price: new Prisma.Decimal('123.456789'),
  },
});

// ✅ Pass as string directly for accurate precision
const record = await prisma.model.create({
  data: {
    price: '123.456789',
  },
});

// Before: Max value ~2 billion
itemCount   Int

// After: Max value ~9 quintillion
itemCount   BigInt

npx prisma migrate dev --name change_int_to_bigint

const record = await prisma.model.create({
  data: {
    itemCount: BigInt(9007199254740991), // JavaScript safe integer limit
  },
});

// Or use string representation
const record = await prisma.model.create({
  data: {
    itemCount: BigInt('9223372036854775807'),
  },
});

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

// Define validation schema matching your column constraints
const PriceSchema = z.number()
  .min(0, 'Price cannot be negative')
  .max(99999999.99, 'Price exceeds maximum allowed value')
  .transform(val => new Prisma.Decimal(val.toFixed(2)));

const QuantitySchema = z.number()
  .int('Quantity must be an integer')
  .gte(0, 'Quantity cannot be negative')
  .lte(2147483647, 'Quantity exceeds maximum Int32 value');

const LargeNumberSchema = z.string()
  .regex(/^-?\d+$/, 'Must be a valid integer string')
  .transform(str => BigInt(str))
  .refine(val => val <= BigInt('9223372036854775807'), 'Exceeds BigInt maximum');

// Use in your application
try {
  const price = PriceSchema.parse(userInput.price);
  const quantity = QuantitySchema.parse(userInput.quantity);

  const record = await prisma.model.create({
    data: { price, quantity },
  });
} catch (error) {
  // Handle validation error before it reaches the database
  console.error('Validation failed:', error.errors);
}

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

// ❌ Wrong: JavaScript number loses precision
const amount = 123456789.123456789; // Becomes 123456789.12345679
const record = await prisma.model.create({
  data: { amount },
});

// ✅ Correct: Use string representation
const amount = '123456789.123456789';
const record = await prisma.model.create({
  data: { amount },
});

// ✅ Also correct: Prisma.Decimal from string
const amount = new Prisma.Decimal('123456789.123456789');
const record = await prisma.model.create({
  data: { amount },
});

import { describe, it, expect } from 'vitest';
import { Prisma } from '@prisma/client';
import { prisma } from '@/lib/db';

describe('Decimal and numeric constraints', () => {
  it('should handle maximum Decimal(11,2) value', async () => {
    const result = await prisma.model.create({
      data: {
        price: '999999999.99',  // Max for Decimal(11, 2)
      },
    });
    expect(result.price.toString()).toBe('999999999.99');
  });

  it('should reject value exceeding Decimal(11,2)', async () => {
    await expect(
      prisma.model.create({
        data: {
          price: '1000000000.00',  // Exceeds limit
        },
      })
    ).rejects.toThrow('P2020');
  });

  it('should handle BigInt edge cases', async () => {
    const maxBigInt = BigInt('9223372036854775807');
    const result = await prisma.model.create({
      data: {
        count: maxBigInt,
      },
    });
    expect(result.count).toBe(maxBigInt);
  });
});