Prisma1: Prisma json with postgres json type

[dsinai14]

Describe the bug
Mapping between prisma JSON type to Postgres JSON throws an error but when using text type in postgres it works

To Reproduce

• create table in postgres with column type json
• Define the datamodel in prisma with type json
  type table@pgTable(name: "table") {
  id: Int! @unique
  description: String
  category: Json
  }

run a mutation to create a record, postgres throws an error: "category is of type json but expression is of type character varying"

Expected behavior
Json should be mapped correctly to postgres json type

Versions (please complete the following information):

• OS: OS X High Sierra
• Prisma Server: 1.19.1

[pantharshit00]

Hi @dsinai14,

If you are using the playground to make a mutation you will require to escape all of the " using \". Can you please confirm you are doing so.

Here is a sample mutation for you:

Copy/Paste this to try it:

mutation {
  createtable(
    data: { description: "something", category: "[{\"some\":\"thing\"}]" }
  ) {
    category
    id
    description
  }
}

PS: The id field you have provided in the datamodel is not allowed. id can only be id: ID! @unique or id: UUID! @unique

[dsinai14]

@pantharshit00 Yes, I can confirm that its not working from the playground with the above form

[pantharshit00]

@dsinai14 I can't reproduce it though. Can you set up a small reproduction in a git repo so that I can have a look? A screenshot of the playground will also be helpful.

Also please try this on the latest version. I am going to try this on your version now.

[dsinai14]

@pantharshit00 yes of course, till then, these are my definitions:

Prisma datamodel
`type SomeTest @pgTable(name: "some_test") {
id: Int! @unique
categoryJson: Json @pgColumn(name: "category_json")
categoryText: Json @pgColumn(name: "category_text")

}`

SQL
CREATE TABLE some_test( id INT GENERATED BY DEFAULT AS IDENTITY, category_json JSON, category_text TEXT, PRIMARY KEY (id) );

Prisma query
mutation { createSomeTest(data:{ categoryJson: "[{\"some\":\"thing\"}]" categoryText: "[{\"some\":\"thing\"}]" }) { id } }

Error

{"key":"error/unhandled","requestId":"local:api:cjpifrbqj009r0912q9uvacu1","clientId":"default$default","payload":{"exception":"org.postgresql.util.PSQLException: ERROR: column \"category_json\" is of type json but expression is of type character varying\n Hint: You will need to rewrite or cast the expression.\n Position: 85","query":"mutation {\n createSomeTest(data: {categoryJson: \"[{\\"some\\":\\"thing\\"}]\", categoryText: \"[{\\"some\\":\\"thing\\"}]\"}) {\n id\n }\n}\n","variables":"{\"serviceCall\":{\"storagePrefix\":\"YU5rO454zE\",\"description\":\"Some description - YU5rO454zE\",\"category\":{\"a\":\"a\"},\"entranceApproved\":true,\"serviceDate\":\"2018-12-10T13:33:24.486Z\",\"timeFrameStart\":\"2018-12-10T13:33:24.486Z\",\"timeFrameEnd\":\"2018-12-10T13:33:24.486Z\",\"location\":\"SHARED_SPACE\",\"status\":\"APPROVED\",\"user\":{\"connect\":{\"id\":7}}}}","code":"0","stack_trace":"org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)\n org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)\n org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)\n org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)\n org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)\n org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)\n org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:144)\n com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)\n com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)\n com.prisma.api.connector.jdbc.database.BuilderBase.$anonfun$insertReturningGeneratedKeysToDBIO$1(BuilderBase.scala:59)\n com.prisma.api.connector.jdbc.database.BuilderBase.$anonfun$jooqToDBIO$1(BuilderBase.scala:82)\n slick.jdbc.SimpleJdbcAction.run(StreamingInvokerAction.scala:70)\n slick.jdbc.SimpleJdbcAction.run(StreamingInvokerAction.scala:69)\n slick.basic.BasicBackend$DatabaseDef$$anon$2.liftedTree1$1(BasicBackend.scala:275)\n slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:275)\n java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n java.lang.Thread.run(Thread.java:748)","message":"ERROR: column \"category_json\" is of type json but expression is of type character varying\n Hint: You will need to rewrite or cast the expression.\n Position: 85"}}

If I'll write in the prisma query only to jsonText, it will work, the problem starts when postgres column is json

[pantharshit00]

Hi @dsinai14,

I can't even deploy the datamodel you have provided above in 1.19.1 and I am still unable to reproduce this issue in 1.22.2. You should definitely upgrade if you can.

[divyenduz]

@dsinai14 : Thanks for sharing all the details, I assume that you used prisma introspect command to get this datamodel?

As mentioned here: https://github.com/prisma/prisma/issues/3233
Type Int! for an id only works with migration: false (i.e. prisma is not managing the database migrations for you) and with existing database.

For us to reproduce this, can you please provide export of Postgres SQL schema in SQL format? That we can set it up and use introspection to reproduce the same error as you.

[dsinai14]

@divyenduz I can still reproduce this issue with prisma 1.22
postgres 10
we are using docker to run both prisma nd postgres locally: FROM prismagraphql/prisma:1.22

CREATE TABLE table_a (
  id uuid NOT NULL PRIMARY KEY,
  name text,
  meta json
);

type TableA @pgTable(name: "table_a") {
    id:             UUID! @unique
    name:           String!
    meta: Json
}

mutation {
  createTableA(data:{
    name: "d",
    meta: "{}"
  }){
    id
  }
}

 {"key":"error/unhandled","requestId":"local:cjpr29lkz00100a36hlvj8bjg","clientId":"default$default","payload":{"exception":"org.postgresql.util.PSQLException: ERROR: column \"meta\" is of type json but expression is of type character varying\n  Hint: You will need to rewrite or cast the expression.\n  Position: 88","query":"mutation {\n  createTableA(data: {name: \"d\", meta: \"{}\"}) {\n    id\n  }\n}\n","variables":"{\"serviceCall\":{\"storagePrefix\":\"ghgh1\",\"description\":\"Some description - pS51hcxqLt\",\"serviceDate\":\"2018-12-16T14:25:30.206Z\",\"timeFrameStart\":\"2018-12-16T14:25:30.206Z\",\"timeFrameEnd\":\"2018-12-16T14:25:30.206Z\",\"status\":\"APPROVED\",\"service\":{\"connect\":{\"id\":\"79103735-fbcf-402d-8713-4ad1dd73d95b\"}},\"user\":{\"connect\":{\"id\":\"06c5451d-3548-48ac-aaea-4ed044e98b67\"}}}}","code":"0","stack_trace":"org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)\\n org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)\\n org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)\\n org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)\\n org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)\\n org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)\\n org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:144)\\n com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)\\n com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)\\n com.prisma.connector.shared.jdbc.SharedSlickExtensions.$anonfun$insertReturningGeneratedKeysToDBIO$1(SharedSlickExtensions.scala:28)\\n com.prisma.connector.shared.jdbc.SharedSlickExtensions.$anonfun$jooqToDBIO$1(SharedSlickExtensions.scala:62)\\n slick.jdbc.SimpleJdbcAction.run(StreamingInvokerAction.scala:70)\\n slick.jdbc.SimpleJdbcAction.run(StreamingInvokerAction.scala:69)\\n slick.basic.BasicBackend$DatabaseDef$$anon$2.liftedTree1$1(BasicBackend.scala:275)\\n slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:275)\\n java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\\n java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\\n java.lang.Thread.run(Thread.java:748)","message":"ERROR: column \"meta\" is of type json but expression is of type character varying\n  Hint: You will need to rewrite or cast the expression.\n  Position: 88"}}
 [Bugsnag - local / testing] Error report: com.bugsnag.Report@53faaef4
 org.postgresql.util.PSQLException: ERROR: column "meta" is of type json but expression is of type character varying
   Hint: You will need to rewrite or cast the expression.
   Position: 88
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
        at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:144)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
        at com.prisma.connector.shared.jdbc.SharedSlickExtensions.$anonfun$insertReturningGeneratedKeysToDBIO$1(SharedSlickExtensions.scala:28)
        at com.prisma.connector.shared.jdbc.SharedSlickExtensions.$anonfun$jooqToDBIO$1(SharedSlickExtensions.scala:62)
        at slick.jdbc.SimpleJdbcAction.run(StreamingInvokerAction.scala:70)
        at slick.jdbc.SimpleJdbcAction.run(StreamingInvokerAction.scala:69)
        at slick.basic.BasicBackend$DatabaseDef$$anon$2.liftedTree1$1(BasicBackend.scala:275)
        at slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:275)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

[br-dev]

I'm also experiencing this same issue. I'm using Prisma v1.25.3.

Created my datamodel using introspection:

type Account @pgTable(name: "account") {
  address: Json
  email: String! @unique
  firstName: String!
  id: Int! @id @unique
  lastName: String!
  meta: Json
  passwordHash: String!
  paymentId: String
  permissions: Json!
  refreshtokens: [Refreshtoken!]!
  signUpDate: DateTime!
  status: String
  username: String! @unique
  verified: Boolean
}

Instead of adding this model from the playground I instead use:

  const savedUser = await prisma.createAccount({
    firstName: register.firstName,
    lastName: register.lastName,
    email: register.email,
    passwordHash: hash,
    signUpDate: new Date(),
    username: register.email,
    paymentId: '',
    verified: true,
    status: 'active',
    permissions: {
      scope: 'user',
    },
    address: null,
  });

This is the error I receive running in a docker container:

{"key":"error/unhandled","requestId":"local:cjrile6fv015f0847t4t6holh","clientId":"default$default","payload":{"exception":"org.postgresql.util.PSQLException: ERROR: column \"permissions\" is of type json but expression is of type character varying\n  Hint: You will need to rewrite or cast the expression.\n  Position: 275","query":"mutation ($data: AccountCreateInput!) {\n  createAccount(data: $data) {\n    address\n    email\n    firstName\n    id\n    lastName\n    meta\n    passwordHash\n    paymentId\n    permissions\n    signUpDate\n    status\n    username\n    verified\n  }\n}\n","variables":"{\"data\":{\"firstName\":\"User\",\"lastName\":\"Last\",\"email\":\"*******@****.com\",\"passwordHash\":\"$2a$10$fXyaKOSjw/umCIjpcJjR6ep3wpcjLqTbDCm4HuVzDAmMZ6Zb75EPq\",\"signUpDate\":\"2019-01-30T02:49:25.520Z\",\"username\":\"*******@****.com\",\"paymentId\":\"\",\"verified\":true,\"status\":\"active\",\"permissions\":{\"scope\":\"user\"},\"address\":null}}","code":"0","stack_trace":"org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)\\n org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)\\n org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)\\n org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)\\n org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)\\n org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)\\n org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:144)\\n com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)\\n com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)\\n com.prisma.connector.shared.jdbc.SharedSlickExtensions.$anonfun$insertReturningGeneratedKeysToDBIO$1(SharedSlickExtensions.scala:28)\\n com.prisma.connector.shared.jdbc.SharedSlickExtensions.$anonfun$jooqToDBIO$1(SharedSlickExtensions.scala:62)\\n slick.jdbc.SimpleJdbcAction.run(StreamingInvokerAction.scala:70)\\n slick.jdbc.SimpleJdbcAction.run(StreamingInvokerAction.scala:69)\\n slick.basic.BasicBackend$DatabaseDef$$anon$2.liftedTree1$1(BasicBackend.scala:275)\\n slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:275)\\n java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\\n java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\\n java.lang.Thread.run(Thread.java:748)","message":"ERROR: column \"permissions\" is of type json but expression is of type character varying\n  Hint: You will need to rewrite or cast the expression.\n  Position: 275"}}
[Bugsnag - local / testing] Error report: com.bugsnag.Report@49604a50
org.postgresql.util.PSQLException: ERROR: column "permissions" is of type json but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 275

[brafdlog]

Any updates on this?

[martinfranek]

Same issue. This means Prisma cannot do JSON with Postgres db. Any update on this bug?

[beeeku]

Any updates on this? can't deploy Prisma due to this bug

[cspags]

I ran into this issue when I needed a jsonb type column in Postgres. My workaround was to leave the text column created by Prisma, and add an additional column of type jsonb via SQL. Then I created a trigger to update my jsonb column whenever the text column is updated.

Here's the SQL script I used to create the trigger to copy data from my fields text column (added by Prisma) to my optimizedFields jsonb column (added by me).

-- 1. Add the jsonb column
ALTER TABLE "mytable" ADD COLUMN optimizedFields jsonb;

-- 2. Create the update function
CREATE OR REPLACE FUNCTION func_update_optimized_fields()
  RETURNS trigger AS
$BODY$
BEGIN
        UPDATE "mytable"
        SET optimizedFields=fields::jsonb
        WHERE id=NEW.id;
 
        RETURN NEW;
END;
$BODY$

LANGUAGE plpgsql VOLATILE
;

-- 3. Create the trigger which runs on every insert or update of the fields column
CREATE TRIGGER trig_update_optimized_fields
  AFTER INSERT OR UPDATE OF fields
  ON "mytable"
  FOR EACH ROW
  EXECUTE PROCEDURE func_update_optimized_fields();

Hopefully, that's helpful if you land here and are looking for a potential solution.