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

PrismaINTERMEDIATEHIGHUpdated Dec 29, 2025· 155 views

The P2020 error occurs when Prisma receives a value that exceeds the limits of the database column type. This commonly happens with invalid datetime values (like 0000-00-00 in MySQL), numeric values exceeding column precision/scale in PostgreSQL, or passing overly large decimal numbers. The error indicates a mismatch between your data and the database schema constraints.

Quick fix

Fixes P2020

# 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;"
  1. Check your database column type definition
  2. For MySQL datetime issues: clean invalid zero dates
  3. For Decimal overflow: adjust precision or pass as string
  4. For integer overflow: use BigInt for large numbers
prisma — error
# 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;"
Value out of range for the type

What this error means

The "P2020: Value out of range for the type" error indicates that Prisma is trying to store or retrieve a value that is incompatible with the column's data type definition. This typically occurs in three scenarios: 1. **Invalid datetime values**: MySQL databases containing zero datetime values (`0000-00-00 00:00:00`) are incompatible with Prisma. These happen in legacy WordPress databases or when MySQL allows zero dates. 2. **Numeric precision overflow**: PostgreSQL columns defined with `Decimal(precision, scale)` constraints will fail if the value exceeds the specified range. For example, `Decimal(8, 6)` can only store numbers from -99.999999 to 99.999999. 3. **Overflow in integer columns**: Attempting to store a JavaScript number that exceeds the column's integer type range (e.g., storing a value > 2,147,483,647 in a 32-bit integer). The error is most common during database migrations, when seeding data, or when accepting user input that hasn't been validated against your schema constraints.

Typical symptoms

  • Query fails with 'P2020: Value out of range for the type' during create/update operations
  • Migration fails when trying to introspect or query a legacy database
  • Decimal or numeric operations result in precision errors
  • DateTime queries fail when the database contains 0000-00-00 values
  • Seed script fails partway through when inserting numeric data
  • Operations work in development but fail in production with different data

Common causes

  1. Database column contains invalid zero datetime values (0000-00-00 00:00:00 in MySQL)
  2. Attempting to store a Decimal value with more digits than the column's precision allows
  3. Passing a very large number that exceeds the column's integer range
  4. Creating or updating with a Decimal value that has too many decimal places for the scale definition
  5. Importing data from a legacy database with incompatible data types
  6. Using Prisma.Decimal() wrapper with values that lose precision during conversion

How to fix "Value out of range for the type"

1Check your database column type definition

First, identify which column is causing the error. Check the schema definition in your Prisma schema and the actual database:

bash
# 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;"

Look at your Prisma schema to see the declared type:

prisma
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
}

Document the column constraints: precision (total digits) and scale (decimal places).

2For MySQL datetime issues: clean invalid zero dates

If the error occurs with datetime columns that contain invalid zero values, update those records:

sql
-- 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';

After cleaning, update your Prisma schema to make the field optional if it wasn't already:

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

Then run the migration:

bash
npx prisma migrate dev --name make_datetime_nullable
3For Decimal overflow: adjust precision or pass as string

If you're getting overflow errors with Decimal fields, you have two options:

Option 1: Increase the column precision

If your values need more decimal places or whole digits:

prisma
// 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)

Create a migration:

bash
npx prisma migrate dev --name increase_decimal_precision

Option 2: Pass Decimal values as strings instead of using Prisma.Decimal()

When creating or updating records:

typescript
// ❌ 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',
  },
});

The string approach preserves precision and avoids JavaScript float conversion issues.

4For integer overflow: use BigInt for large numbers

If you're storing numbers that exceed the Int range (up to 2,147,483,647):

prisma
// Before: Max value ~2 billion
itemCount   Int

// After: Max value ~9 quintillion
itemCount   BigInt

Create and run the migration:

bash
npx prisma migrate dev --name change_int_to_bigint

In your TypeScript code, BigInt is accessible:

typescript
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'),
  },
});

Note: When querying, BigInt values are returned as BigInt type, not number.

5Validate and sanitize user input before database operations

Prevent out-of-range errors by validating input against your column constraints:

typescript
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);
}

This prevents invalid values from reaching Prisma and provides better error messages to users.

6Check for precision loss when working with very large decimals

JavaScript numbers have limited precision (safe up to 2^53 - 1). When working with financial or scientific data requiring high precision:

typescript
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 },
});

Always use string representation for high-precision decimal values to avoid floating-point rounding errors.

7Test your data model against edge cases

Create tests to verify your data types can handle expected value ranges:

typescript
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);
  });
});

Running these tests during development helps catch schema constraint violations early.

Advanced notes

Understanding Decimal Precision and Scale

When you define Decimal(precision, scale):
- Precision: Total number of digits (both sides of decimal point)
- Scale: Number of digits after the decimal point

Example: Decimal(10, 2) stores up to 10 digits total with 2 after the decimal:
- Maximum: 99,999,999.99
- Minimum: -99,999,999.99

If you try to store 100,000,000.00 (11 digits), PostgreSQL will reject it with P2020.

MySQL Zero Datetime Issue

MySQL's default behavior allows zero datetime values (0000-00-00 00:00:00) for backward compatibility. However, Prisma cannot represent this value in JavaScript (there's no Date equivalent). When Prisma encounters this during introspection or querying, it throws P2020.

This is especially common with:
- WordPress databases (uses zero dates for unpublished posts)
- Legacy applications that set defaults to '0000-00-00'
- Databases imported from MySQL 5.5 and earlier

Solutions:
1. Update all zero dates to NULL or valid dates
2. Set NO_ZERO_DATE MySQL strict mode to prevent new zero dates
3. Map the column to a custom type with @map if you need the legacy behavior

JavaScript Number Precision

JavaScript's native number type (IEEE 754 double-precision float) safely stores integers only up to 2^53 - 1 (9,007,199,254,740,991). Beyond this, precision is lost.

For financial or scientific data:
- Always use Decimal or BigInt in Prisma
- Pass values as strings, not JavaScript numbers
- Never perform arithmetic on Decimal values in JavaScript; do it in the database with raw SQL if precision is critical

Integer Range Reference

| Type | Min Value | Max Value |
|------|-----------|-----------|
| Int (32-bit) | -2,147,483,648 | 2,147,483,647 |
| BigInt (64-bit) | -9,223,372,036,854,775,808 | 9,223,372,036,854,775,807 |
| Decimal(p,s) | Depends on precision | Depends on precision |

Performance Considerations

- BigInt and Decimal fields are slightly slower than Int for database operations
- PostgreSQL's numeric type (used for Decimal) is slower than native integer types
- If you don't need the range, use Int for better performance
- Use proper indexing on numeric columns used in WHERE clauses

Seeding Data with Large Numbers

When using prisma db seed, validate all numeric values:

typescript
// scripts/seed.ts
const seedData = [
  { price: '999.99' },      // String for Decimal
  { count: BigInt(1000) },  // BigInt for large integers
  { id: 1 },                // Int for small integers
];

for (const item of seedData) {
  await prisma.model.create({ data: item });
}

Type-checking helps catch P2020 errors before running the seed.

Frequently asked questions

What does "P2020" mean?
The "P2020: Value out of range for the type" error indicates that Prisma is trying to store or retrieve a value that is incompatible with the column's data type definition. This typically occurs in three scenarios: 1. Invalid datetime values: MySQL databases containing zero datetime values (0000-00-00 00:00:00) are incompatible with Prisma.
How do I fix "P2020"?
The P2020 error occurs when Prisma receives a value that exceeds the limits of the database column type. This commonly happens with invalid datetime values (like 0000-00-00 in MySQL), numeric values exceeding column precision/scale in PostgreSQL, or passing overly large decimal numbers. The error indicates a mismatch between your data and the database schema constraints.
What causes "P2020"?
Database column contains invalid zero datetime values (0000-00-00 00:00:00 in MySQL) Attempting to store a Decimal value with more digits than the column's precision allows Passing a very large number that exceeds the column's integer range
Is the "P2020" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading