JSONB support (from Prisma 2.0.0.beta4)

Just wanted to log a vote for getting Redwood upgraded to depend on the latest Prisma release (2.0.0.beta4) as soon as it makes sense to do so specifically for one feature: JSON & JSONB support in new enough Postgres and MySQL.

I have two use cases for my app that are annoyingly hard to do without JSON/JSONB support:

  • polymorphic 1-1 associations for a strategy pattern. If you have an object that does something useful using a strategy (say an Asset object that is fetched either from S3 or sourced from Dropbox), it would be nice to be able to have a fetch_strategy field that either contained the config necessary to get the file from S3 or from Dropbox, which each would likely have different fields in an object. I could break each strategy out into it’s own model and then build a hacky polymorphic relation that Prisma doesn’t know about, but I’d rather keep it simple if I can! Rails supports this kind of thing via jsonb column support, or polymorphic association support, or column serializers.
  • app populated metafields (like Shopify’s) where the name, type and value of data hanging off a record isn’t know ahead of time. If API clients of my application want to be able to attach data to resources inside the application (which is super useful so they don’t have to sync all the data to their end), they should be able to hand it to my app, have it stored, and retrieve it on demand. I could again break it out into separate records, but I’d rather just store it as a JSON blob on the owning record so I can fetch it quickly with one query, store nested structures, and store it efficiently (using JSONB)

Prisma 2.0.0.beta4 is hot off the presses so I know it might take a while but I figured I’d let y’all know what my use cases were (and redwoodjs asked me to on twitter :). They’re also not yet done documenting JSONB support so it may be wise to wait for that as well. @rob and @thedavid was asked to tag you.

:pray: you wonderful people for putting Redwood together.


Thanks the for use cases @hornairs!

Have you ever used JSON with GraphQL? It doesn’t look like Apollo has any native JSON data types, but their docs show an example of adding support via the graphql-type-json package. @peterp will our custom resolver mapping stuff still work with this?

More of a philosophical question: the whole point of GraphQL seems to be that you are very explicit about what fields can and can’t be accessed, what their types are, whether they’re required, etc. Adding JSON in there seems to throw that all out the window…now you can just include whatever you want. Or is there additionally a way to define the allowed structure of the JSON itself?

Yeah, I have a few times indeed, using the same strategy as the graphql-type-json package to model JSON as an opaque scalar. Has worked just fine for me! At least with the Ruby graphql implementation I was using there was no extra serialization/deserialization step necessary as well in that the JSON isn’t rendered as a string and is part of the same JSON object the root GraphQL response is.

For sure this bucks the explicitness of GraphQL on the frontend! But I think it’s worth noting that storing JSON in the database can be considered separately from how it’s presented on the frontend. It may be that the JSON is in fact structured but we just want to store it in a column instead of a whole series of relations in the database, and you still use robust and explicit GraphQL types on the API side, similar to embedding a 1-1 association on a record.

On the actual API side, I’ve used it in the past as a kind of escape hatch. Make the right thing easy but don’t make the other things impossible is my preferred principle for this kind of stuff. I’ve used the JSON escape hatch when being lazy and not wanting to be fully explicit about a GraphQL type because it was still changing really frequently, or when I had truly unpredictable keys (like the metafields example) and didn’t want to have to model it as a list of {key: String!, value: AnythingReally!} when that didn’t really add much value over just JSONScalar!, you know?

1 Like

@hornairs huge thanks for taking the time to work on this with us. It’s helpful. And I’m glad you asked on Twitter.

To be clear, it does not sound like you are worried/interested in how/if Redwood generators will support JSON in schema models, correct? We actually aren’t sure yet if that’s going to be possible. (Or, if it is, when we might add support… it could be a while.)

^^ this is the biggest question we need to address through testing. There are a couple ways forward to do so:

  • if we had sample code snippets (or better yet an example repo) using JSON in a schema model and implementation somewhere in web/, we could run local tests with Redwood using Prisma beta4
  • and/or we could run our standard tests against current features using Prisma beta4 then publish a release candidate for you to use for testing

Thoughts and suggestions about other ways forward?

Yeah I feel like really the only thing Redwood could do is emit a JSONScalar type or something of that nature, if the database nor Prisma knows what the actual schema of the data is I think Redwood would be smart to leave it un-schema’d too. I think that for me at least, all I’m looking for is JSONB support to be possible and not necessarily super duper easy because as y’all mentioned Redwood’s and my opinion is you should use typed APIs where possible.

I put together a tiny example repo to mess around with here: https://github.com/airhorns/jsonb-metafields-example . Without any changes to Redwood, I was able to get my JSON string coming out of the API just by putting scalar Json in the SDL. I added the scalar resolver from graphql-type-json for that scalar, but it’s still being rendered in the GraphQL response as a string, not as an object. I think this is actually prisma’s fault as I looked at the objects coming back from the findMany call, and they had the field as a string, not and object, which seems strange and violates the details outlined here: https://github.com/prisma/prisma/issues/186#issuecomment-623413319

If someone is using a Prisma JSON column to store actually structured data, I think they’d be fine right now because they could change the GraphQL SDL to represent the structured data and GraphQL would validate it on the way in and out, and Prisma would (ideally) happily marshal whatever object was at the field to the database’s JSON representation and back.

There’s probably other stuff that’s broken with this whole setup but at least this is a starting point!


Prisma beta4 is now in RedwoodJS master. There’s now a canary RWJS package release on NPM, “^0.6.1-canary.28”, and I just created a PR against your repo including the canary version.

Very interested to hear how it goes once you have a chance to take 'er for a spin :rocket:

Having worked with beta4 for a bit I think JSON support still needs some love, see https://github.com/prisma/prisma/issues/2404 for some issues I’ve been hitting with it. Probably best to get that kind of stuff sorted before implementing specifics in Redwood depending on what the actual contract for JSON columns is going to be.

Ah, roger that and very helpful to know! We’ll keep watching this topic in new Prisma releases. But please stay in touch as well as you learn more.

Update: Prisma Beta.5 is now in master with a corresponding canary release.

This was one of the fixes that shipped with Beta.5. Not sure if it’s relevant or not: