Prisma: Where and OrderBy Inputs

[P4sca1]

How do you write where and order by input objects for your Prisma backed GraphQL schema?
Currently, I manually define the types like this:

// Filters
import { builder } from '../builder'

export const NegatedIntFilter = builder.inputType('NegatedIntFilter', {
	fields: (t) => ({
		equals: t.int(),
	}),
})

export const IntFilter = builder.inputType('IntFilter', {
	description: 'Used to filter integer values.',
	fields: (t) => ({
		equals: t.int(),
		gt: t.int(),
		gte: t.int(),
		in: t.intList(),
		lt: t.int(),
		lte: t.int(),
		not: t.field({ type: NegatedIntFilter }),
		notIn: t.intList(),
	}),
})

export const NegatedDecimalFilter = builder.inputType('NegatedDecimalFilter', {
	fields: (t) => ({
		equals: t.field({ type: 'Decimal' }),
	}),
})

export const DecimalFilter = builder.inputType('DecimalFilter', {
	description: 'Used to filter decimal values.',
	fields: (t) => ({
		equals: t.field({ type: 'Decimal' }),
		gt: t.field({ type: 'Decimal' }),
		gte: t.field({ type: 'Decimal' }),
		in: t.field({ type: ['Decimal'] }),
		lt: t.field({ type: 'Decimal' }),
		lte: t.field({ type: 'Decimal' }),
		not: t.field({ type: NegatedDecimalFilter }),
		notIn: t.field({ type: ['Decimal'] }),
	}),
})

export const BooleanFilter = builder.inputType('BooleanFilter', {
	description: 'Used to filter boolean values.',
	fields: (t) => ({
		equals: t.boolean(),
	}),
})

export const NegatedStringFilter = builder.inputType('NegatedStringFilter', {
	fields: (t) => ({
		contains: t.string(),
		endsWith: t.string(),
		equals: t.string(),
		startsWith: t.string(),
	}),
})

export const StringFilter = builder.inputType('StringFilter', {
	description: 'Used to filter integer values.',
	fields: (t) => ({
		contains: t.string(),
		endsWith: t.string(),
		equals: t.string(),
		gt: t.string(),
		gte: t.string(),
		in: t.stringList(),
		lt: t.string(),
		lte: t.string(),
		not: t.field({ type: NegatedStringFilter }),
		notIn: t.stringList(),
		startsWith: t.string(),
	}),
})

export const NegatedDateTimeFilter = builder.inputType(
	'NegatedDateTimeFilter',
	{
		fields: (t) => ({
			equals: t.field({ type: 'DateTime' }),
		}),
	}
)

export const DateTimeFilter = builder.inputType('DateTimeFilter', {
	description: 'Used to filter DateTime values.',
	fields: (t) => ({
		equals: t.field({ type: 'DateTime' }),
		gt: t.field({ type: 'DateTime' }),
		gte: t.field({ type: 'DateTime' }),
		in: t.field({ type: ['DateTime'] }),
		lt: t.field({ type: 'DateTime' }),
		lte: t.field({ type: 'DateTime' }),
		not: t.field({ type: NegatedDateTimeFilter }),
		notIn: t.field({ type: ['DateTime'] }),
	}),
})

// Inputs for Prisma model game
import { builder } from '../../builder'
import { SortOrder } from '../../types'
import { BooleanFilter, DateTimeFilter, StringFilter } from '../filters'

export const GameOrderBy = builder.inputType('GameOrderBy', {
	fields: (t) => ({
		short: t.field({ type: SortOrder }),
		name: t.field({ type: SortOrder }),
		consoleCommandsEnabled: t.field({ type: SortOrder }),
		updatedAt: t.field({ type: SortOrder }),
		createdAt: t.field({ type: SortOrder }),
	}),
})

export const GameWhereInput = builder.inputType('GameWhereInput', {
	fields: (t) => ({
		short: t.field({ type: StringFilter }),
		name: t.field({ type: StringFilter }),
		description: t.field({ type: StringFilter }),
		image: t.field({ type: StringFilter }),
		consoleCommandsEnabled: t.field({ type: BooleanFilter }),
		updatedAt: t.field({ type: DateTimeFilter }),
		createdAt: t.field({ type: DateTimeFilter }),
	}),
})

This can be quite cumbersome, so I thought it might be great if there were helper methods:

builder.prismaOrderBy(modelName: string, fieldsThatAreIncluded: string[])
builder.prismaWhereInput(modelName: string, fieldsThatAreIncluded: string[])

Both methods would generate the filters and SortOrder enum the first time they are used.
What do you think about that? It would eliminate a lot of repetitive work and would make writing those input objects type safe.

One issue I have with my current manual approach is that types for args.where and args.orderBy are incorrect. Take the following query:

import { prisma } from '../../../gateways/prisma'
import { builder } from '../builder'
import { GameOrderBy, GameWhereInput } from '../inputs/game'

builder.queryType({
	fields: (t) => ({
		games: t.prismaConnection(
			{
				type: 'Game',
				cursor: 'short',
				args: {
                                        where: t.arg({ type: GameWhereInput }),
					orderBy: t.arg({ type: [GameOrderBy] }),
				},
				complexity: (args) => ({
					field: 10,
					multiplier: args.first ?? args.last ?? 1,
				}),
				resolve: (query, _parents, args) =>
					prisma.game.findMany({
						...query,
                                                where: args.where,
						orderBy: args.orderBy,
					}),
			},
		),
	}),
})

The problem is that where and orderBy are type | null | undefined, but prisma expects type | undefined (not only the root object, but also nested properties). A helper method to convert the inputs from pothos to Prisma compatibly inputs, would be great.

[hayes]

This is definitely something I want to add. It would probably be a new plugin (maybe prisma-crud). The core prisma plugin I wanted to be very explicit about not tying the GraphQL API too closely with the db model. Something like this gets closer to 1-1 mapping of DB to API which I think is generally a bad idea. That being said, its really useful, and being able to quickly build up input types like this can be really useful, which is why I want to create it as a new plugin.

I can't give you a timeline on when I would have time to build it, but I would be more than happy to help if its something you wanted to start working on. It's probably fairly high on my list of things to build next, but I can't make any guarantees about when it might be ready.

[P4sca1]

That's great to hear! Do you have an idea on how I can currently make args.where compatible with the where attribute of prisma?

[hayes]

If you are just talking about type compatibility, probably just doing an explicit cast to something like as Prisma.GameWhereInput

If you want to actually strip out potential null values, thats more complicated. The closest thing built into Pothos is probably https://pothos-graphql.dev/docs/guide/writing-plugins#wrapping-arguments-and-inputs Which I'll probably use when I write a plugin for this.

[hayes]

Put together a placeholder package here: #348

Doesn't actually do anything yet. Won't have much time this weekend to though, might be able to start adding some functionality next week.

[hayes]

Hey, started a discussion to get feedback on an early implementation of this here: #544

[P4sca1]

Thanks for your effort! I'm not available for the next 4 weeks, but I will definitely get back to this.

[hayes]

hey, just wanted to check in to see if you were still using pothos, and if the utils from above where helpful.

I am working on an update to support input types for mutations, and trying to find some people to get some feedback if you are interested

[P4sca1]

Hey, yes I am still enjoying Pothos, but did not yet have the time to test your utils. The reason is that I have written my own utils for my specific use case, before the new package has been released. I will check if the new package can replace my own implementation, once I get more time.