How do I specify column type of ::jsonb in graphQL? [resolved]

Get Help and Help Others
1

I switched over to migrations and got rid of my [prisma blocking] cross-schema foreign keys

That works…

But unfortunately I lost my ::jsonb column types

How do I specify ::jsonb colum types in schema.prisma ?

I can use Json in the prisma.schema – but that gives me column types of ‘text’

2

Hi @ajoslin103

I am not sure I understand what you asked correctly but what about this?

Assume flexible props are required for member:

schema.prisma

model Member {
  id       Int   @id @default(autoincrement())
  meta     Json? @default("{}")
}

member.sdl.ts

  type MemberMeta {
    age: Int!
    hobbies: [String!]!
  }

  type Member {
    id: Int!
    meta: MemberMeta
  }
3

Hi @sangheestyle, sorry it was late and I was tired - this is a more complete explanation

I need a column type of jsonb (not just json)

In order to do this, I can modify the [prisma] generated migration manually to change certain column types to jsonb but I would rather not modify generated tables

-- CreateTable
CREATE TABLE "profile" (
    "id" TEXT NOT NULL,
    "aws" jsonb NOT NULL,
    "billing" jsonb NOT NULL,
    "transactions" jsonb NOT NULL,
    "history" jsonb NOT NULL,
    "inserted_at" TIMESTAMP(3) NOT NULL,
    "updated_at" TIMESTAMP(3) NOT NULL
);

-- CreateIndex
CREATE UNIQUE INDEX "profile.id_unique" ON "profile"("id");

I would much rather change the GQL to gain the desired result (a column type of jsonb)

Question: what is the type I should specify to get jsonb back as the resultant column type in postgresql ?

(using Json in the GQL results in column type text)

4

Instead of returning raw JSONB from the database to the service in the api and down to the client, can you not extract out the info from that Json into other types?

That way your cells and other code with have defined proper types and that will play dividends on web side.

5

Or even create a view or another view that does the extraction via SQL on

    "billing" jsonb NOT NULL,
    "transactions" jsonb NOT NULL,

and then join in on that using sql to link to the Profile?

Ie a new model with nice attributes from that on UserBilling or UserTransaction ?

6

yes there would be benefits to less opaque data but I am interfacing with other code

this worked well enough

model profile {
  id    String  @unique
  aws Json @default("{}")
  billing Json @default("{}")
  transactions Json @default("{}")
  history Json @default("{}")
  inserted_at DateTime @default(now())
  updated_at DateTime @updatedAt
}