MongoDB: `Inconsistent column data: Failed to convert '\"31\"' to 'Int'`

ChristophP commented 2 years ago

Quick description

With already existing data in a MongoDB, the prisma client fails to convert numeric strings into numbers and throws an error.

Longer Description

Schema

model test {
  id       String             @id @default(auto()) @map("_id") @db.ObjectId
  name     String
  age      Int
}

Value (note how the age field has a string value even though the schema says it should be an Int)

Query

  const test = await prisma.test.findMany();

Error

Inconsistent column data: Failed to convert '"31"' to 'Int' for the field 'age'.:
{
  "code": "P2023",
  "clientVersion": "4.4.0",
  "meta": {
    "message": "Failed to convert '\"31\"' to 'Int' for the field 'age'."
  },
  "stack": "Error: \nInvalid `prisma.test.findMany()` invocation:\n\n\nInconsistent column data: Failed to convert '\"31\"' to 'Int' for the field 'age'.\n    at RequestHandler.handleRequestError (/app/node_modules/@prisma/client/runtime/index.js:30851:13)\n    at RequestHandler.request (/app/node_modules/@prisma/client/runtime/index.js:30834:12)\n    at async PrismaClient._request (/app/node_modules/@prisma/client/runtime/index.js:31812:16)\n    at async file:///app/src/controllers/calendar.controller.js:351:16"
}

Question

Shouldn't conversions like this be handled by prisma? (I realize the data in the DB is of the wrong type, but unfortunately this is quite common for a NoSQL DB like MongoDB to have some records where inconsitent data snuck in inconsistent data types).

Basically this means for me that introducing prisma is risky if I can't guarantee that all my data is consistent because I'm risking getting errors that way. My preferred behavior would be or Prisma to try and convert the data and throw if that can't be done.

janpio commented 2 years ago

The problem if Prisma would do that, would be that after editing some data and writing it back it would change its type to what Prisma expects to be in that column. For Prisma itself, that would be fine (and actually desirable), but for other tools that access the database it could lead to them getting unexpected types of data. So for now Prisma really expects the defined types in a column.

(We have gotten surprisingly few requests to change that in any way for now.)

ChristophP commented 1 year ago

I see, it could be confusing and lead to something breaking to get read/write back and get a different type in the DB all of a sudden. (Although from schema perspective it would be nice).

I guess the larger problem is that unlike with SQL DB, making sure that something that all data conforms to a specific type in the DB (without something like Prisma) is very hard in practice.

My plan is to use prisma to remove all Mongoose code in our app and then switch to a Postgres DB where I won't have those data inconsistencies anymore. To get there I'll be using type casting queries in the DB to ensure consitent types in fields.

So as far as I'm concerned, this issue can be closed

prisma / prisma