`Conversion failed: input contains invalid characters`

[jboxman]

Bug description

Pulled from database for new schema, then attempted query with generated client.

DATABASE_URL=file:./db/digikam4.db node hack/import-db.js
PrismaClientKnownRequestError: 
Invalid `dk.images.findMany()` invocation in
/Users/jasonb/Self/projects/photo-tagging/libs/db/hack/import-db.js:13:30

  10 await dk.$connect();
  11 let images;
  12 try {
→ 13   images = await dk.images.findMany(
Inconsistent column data: Conversion failed: input contains invalid characters
    at Kr.handleRequestError (/Users/jasonb/Self/projects/photo-tagging/libs/db/prisma/generated/import/runtime/library.js:163:19499)
    at Kr.handleAndLogRequestError (/Users/jasonb/Self/projects/photo-tagging/libs/db/prisma/generated/import/runtime/library.js:163:19031)
    at Kr.request (/Users/jasonb/Self/projects/photo-tagging/libs/db/prisma/generated/import/runtime/library.js:163:18869)
    at async t._request (/Users/jasonb/Self/projects/photo-tagging/libs/db/prisma/generated/import/runtime/library.js:173:9714)
    at async main (/Users/jasonb/Self/projects/photo-tagging/libs/db/hack/import-db.js:13:14) {
  code: 'P2023',
  clientVersion: '4.10.1',
  meta: { message: 'Conversion failed: input contains invalid characters' },
  batchRequestIdx: undefined

Any hints? I have no idea what the input is or what characters are invalid. Help?

How to reproduce

Run code.

Expected behavior

Query works.

Prisma information

const { PrismaClient: PrismaClientDk } = require('../prisma/generated/import');

async function main() {
  const dk = new PrismaClientDk();

  await dk.$connect();
  let images;
  try {
    images = await dk.images.findMany({
      include: {
        info: true,
        metadata: true,
      },
    });
  } catch (e) {
    console.log(e);
  }
  //console.log(images.length);
}

main();

DATABASE_URL=file:db/digikam4.db node_modules/.bin/prisma db pull --schema prisma/digikam.prisma

model Images {
  id               Int       @id @default(autoincrement())
  album            Int?
  name             String
  status           Int
  category         Int
  modificationDate DateTime?
  fileSize         Int?
  uniqueHash       String?
  manualOrder      Int?
  metadata ImageMetadata? //added
  info ImageInformation? // added

  @@unique([album, name], map: "sqlite_autoindex_Images_1")
  @@index([name], map: "image_name_index")
  @@index([uniqueHash], map: "hash_index")
  @@index([album], map: "dir_index")
}

model ImageInformation {
  imageid          Int       @id
  rating           Int?
  creationDate     DateTime?
  digitizationDate DateTime?
  orientation      Int?
  width            Int?
  height           Int?
  format           String?
  colorDepth       Int?
  colorModel       Int?
  image Images @relation(fields: [imageid], references: [id]) //added 

  @@index([creationDate], map: "creationdate_index")
}

model ImageMetadata {
  imageid                      Int     @id
  make                         String?
  model                        String?
  lens                         String?
  aperture                     Float?
  focalLength                  Float?
  focalLength35                Float?
  exposureTime                 Float?
  exposureProgram              Int?
  exposureMode                 Int?
  sensitivity                  Int?
  flash                        Int?
  whiteBalance                 Int?
  whiteBalanceColorTemperature Int?
  meteringMode                 Int?
  subjectDistance              Float?
  subjectDistanceCategory      Int?
  image Images @relation(fields: [imageid], references: [id]) /added
}

Environment & setup

• OS:
• Database:
• Node.js version:

macOS

node: v16.18.0

SQLite

Prisma Version

Environment variables loaded from .env
prisma                  : 4.10.1
@prisma/client          : 4.10.1
Current platform        : darwin
Query Engine (Node-API) : libquery-engine aead147aa326ccb985dcfed5b065b4fdabd44b19 (at ../../node_modules/.pnpm/@prisma+engines@4.10.1/node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Migration Engine        : migration-engine-cli aead147aa326ccb985dcfed5b065b4fdabd44b19 (at ../../node_modules/.pnpm/@prisma+engines@4.10.1/node_modules/@prisma/engines/migration-engine-darwin)
Format Wasm             : @prisma/prisma-fmt-wasm 4.10.1-1.80b351cc7c06d352abe81be19b8a89e9c6b7c110
Default Engines Hash    : aead147aa326ccb985dcfed5b065b4fdabd44b19
Studio                  : 0.481.0

[jboxman]

This works, so it seems to be one of the fields. By brute force I can discover which.

    images = await dk.images.findMany({
      select: {
        id: true,
      },
    });

[jboxman]

So this originates from:

modificationDate DateTime?

Which Prisma derived for me from a database pull.

Presents as:

2022-08-12T21:40:46.248

So this is invalid? Why does Prisma assign a DateTime column, then? It looks like Prisma doesn't support this native SQLite column type?

[maxcountryman]

@jboxman were you able to work around this?

[jboxman]

@maxcountryman, I cheated. I setup a view with the data type that Prisma wants and used the view to transform the underlying column to this "correct" type. I use the view as the relation instead of the underlying table. I'm only doing this for an import from a legacy database, so the new schema conforms to what Prisma expects.

[maxcountryman]

@jboxman when you say, "conforms to what Prisma expects" what do you mean by that? I'm trying to migrate a Postgres dataset to SQLite and I think running into this problem. Prisma was fine with Postgres but apparently DateTime? is not okay with Prisma + SQLite. (That's not great, because it's the correct data model for my use case.)

[jboxman]

@maxcountryman, so this is what I was able to surmise: https://boxman.dev/blog/use-prisma-with-an-sq-lite-datetime-datatype

[designcise]

i also see the following error on SQLite DateTime column:

PrismaClientKnownRequestError: 
Invalid `prisma.post.findMany()` invocation:

Inconsistent column data: Conversion failed: input contains invalid characters

[SevInf]

Easiest way I found to reproduce the issue:

Schema:

generator client {
  provider = "prisma-client-js"
  output   = "../node_modules/.prisma/client"
}

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

model Model {
  id   Int      @id @default(autoincrement())
  date DateTime
}

Code:

const prisma = new PrismaClient()
await prisma.$executeRaw`INSERT INTO Model("date") VALUES ('2022-08-12T21:40:46.248')`
await prisma.model.findMany()

The value is correctly written into DataTime column on sqlite side, Prisma has a hiccup while reading it though.

Still happens on 5.9.0-dev

[santiago26]

Bump. Still relevant. Amazing! A modern library does not fix a problem with the basic feature for a year.

Thanks boxman for exploration. Btw, seems that actual link should now be like this https://dev.to/chingu/use-prisma-with-an-sqlite-datetime-datatype-29o9