"Gather" plugin example: treating unique indexes similarly to unique constraints

[MarioSimou]

Summary

PostGraphile recognizes columns as unique only if they have a unique constraint and not when a unique index is created

While this is not a direct problem with postgraphile, the solution below can help those that use postgraphile in conjunction with prisma. As mentioned here, prisma doesn't set a unique constraint on a column using the @unique directive. Instead, it only creates a unique index. Consequently, postgraphile doesn't recognize those columns as unique, which causes a few issues.

Steps to reproduce

Add the @unique directive in a prisma schema. Also, run the migration against the database.

Expected results

Columns marked with @unique directive to be considered unique by postgraphile.

Actual results

Columns marked with @unique directive are ignored.

Possible Solution

Create this plugin and include it in your graphile config.

// uniqueIndexesPlugin.ts
import type {PgResourceUnique, PgCodecAttributes, PgResourceOptions} from 'postgraphile/@dataplan/pg'
import type {PgAttribute} from 'postgraphile/graphile-build-pg/pg-introspection'

const createUnique = <TAttributes extends PgCodecAttributes = PgCodecAttributes>(
  isPrimary: boolean,
  attributes: PgAttribute[],
  description?: string
): PgResourceUnique<TAttributes> => {
  const names = attributes.map(attribute => attribute.attname)
  const tags = attributes.reduce((tags, attribute) => ({...tags, ...attribute.getTags()}), {})

  return {
    isPrimary,
    attributes: names,
    description,
    extensions: {tags},
  }
}

const isPgAttribute = (attribute: PgAttribute | null): attribute is PgAttribute =>
  Boolean(attribute && attribute._type === 'PgAttribute')

const createUniquesLookup = (options: PgResourceOptions) => {
  const createUniqueId = (unique: PgResourceUnique) => unique.attributes.join('-')

  const hash =
    options.uniques?.reduce<Record<string, true>>(
      (uniques, unique) => ({
        ...uniques,
        [createUniqueId(unique)]: true,
      }),
      {}
    ) ?? {}

  return (unique: PgResourceUnique) => Boolean(hash[createUniqueId(unique)])
}

export const UniqueIndexesPlugin: GraphileConfig.Plugin = {
  name: 'UniqueIndexesPlugin',
  version: '0.0.1',
  gather: {
    hooks: {
      async pgTables_PgResourceOptions(_, event) {
        const relkinds = ['r']
        const {pgClass, resourceOptions} = event

        if (!relkinds.includes(pgClass.relkind)) {
          return
        }
        if (!resourceOptions.uniques) {
          return
        }

        const indexes = pgClass.getIndexes().filter(idx => idx.indisunique)
        const uniquesToAdd: PgResourceUnique[] = []
        const lookup = createUniquesLookup(resourceOptions)

        for (const idx of indexes) {
          const attributes = idx.getKeys()

          if (!attributes.every(isPgAttribute)) {
            continue
          }

          const isPrimary = idx.indisprimary ?? false

          const unique = createUnique(isPrimary, attributes)

          if (lookup(unique)) {
            continue
          }

          uniquesToAdd.push(unique)
        }

        resourceOptions.uniques = [...resourceOptions.uniques, ...uniquesToAdd]
      },
    },
  },
}

[benjie]

Thanks for filling out the example from Discord and submitting it as an example for others! We really appreciate it :raised_hands:

[yuliswe]

~Is there any chance you can make this work for v4 too?~ I found this awesome plug in in v4 doc https://github.com/hansololai/postgraphile-index-to-unique-constraint-plugin