Prisma1: Declarative support for multi-field unique constraints

[Trellian]

What feature are you missing?

Multi-field uniqueness constraints

How could this feature look like in detail? Tradeoffs?

As in the following post on the forums https://www.graph.cool/forum/t/how-to-add-multi-field-constraints/1555

As suggested by the original author, the problem is primarily due to not being able to include a 'parent' relation in the uniqueness directive for a field.

His excellent suggestion is as follows, by scoping the uniqueness to the Relation Name:

Expected behavior?

type Author @model {
  id: ID! @isUnique
  name: String! @isUnique

  posts: [Post!]! @relation(name: "AuthorPosts")
}

type Post @model {
  id: ID! @isUnique
  title: String! @isUniqueForAuthorPosts
  author: [Author!]! @relation(name: "AuthorPosts")
}

This only solves half the problem, albeit the biggest one; multi-field uniqueness constraints are still required for fields not involving relations.

[kbrandwijk]

I don't agree with unusable at all. This uniqueness requirement can as easily be checked in a beforeOperation hook, where you can do all validation you want, including checking if a combination of fields is already unique, even including the relation field, like the @isUniqueForAuthorPosts example.

So I would update the title to: 'Please add _declarative_ support for multi-field uniqueness constraints'.

[Trellian]

Good point, I will change it, and thanks for the pointer on the workaround!

(In my case, I have over 200 tables to deal with, with multiple FK's per table in most cases. A lot of them are join tables. 90% of those tables have uniqueness requirements, based on one or both FK's.

I'm using the Local framework, which uses MySQL. MySQL already handles this issue, you just need to tell it which columns are unique. I don't know about AWS Aurora, but I'm guessing they do too.

I generate my graphQL Schema direct from MySQL Workbench (sort of, there are a few extra steps). This keeps me honest, and reliable, because I can trace the DB design from the ERD right through to the generated graphQL schema.

It's going to be a real pain to write over 300 beforeHook functions!)

[kbrandwijk]

As you are doing some form of code generation already generating the GraphQL schema from MySQL, you might extend this to generate these hooks based on the unique indexes?

[Trellian]

yes, I could, but this is such a basic fundamental database issue that I'm struggling to believe that it was missed. I'm trying to evaluate GraphCool as a BaaS as a paid service. I appreciate that there is a workaround, but it's really not feasible for a production environment. It shouldn't even be a feature request, this should be logged as a bug. Graphcool is amazing, the amount of work and integration put into it is stunning. This one feature has been missed out on. I hope it is corrected soon.

[edit - It appears that AWS Aurora also supports multi-field unique keys, as it is a "drop-in replacement for MySQL and PostgresQL"]. Sorry for the late confirmation of this, I've never worked worked with AWS Aurora before.

[kbrandwijk]

Part of the issue is that because of the way the database tables are structured, foreign keys are not part of the Type table, so you can't rely on defining database indices alone for this to function. At least, only for multi-field uniqueness in the same table.

[sorenbs]

This new proposal covers adding arbitrary index support, including unique indexes covering multiple fields across relations: https://github.com/graphcool/framework/issues/1300

I would love your input regarding two key areas:

1. Is the proposal powerful/flexible enough for your use case?
2. The SDL syntax is less intuitive than I would like. Alternative suggestions greatly appreciated :-)

[Trellian]

@kbrandwijk Yes, I looked at the source, wanting to contribute. Sadly, I'm not a Scala progammer, and simply can't afford the time to get up to speed with it. What I could see though is that since we are talking about table-constraints here, it looks like it would be reasonably easy to add a 'TableConstraint.scala' table?

@sorenbs Having a look...

[ar1a]

Any update?

[brainafesoh]

Wow... issue has been here for a while.
Any updates coming soon?

[mehrad77]

@brainafesoh I don't think so