Prisma1: [RFC] Datamodel v1.1 - Indexes and Constraints

[mavilein]

This part of the spec describes directives that are used to specify constraints and indexes. See the issue #3408 to learn about the other parts of the spec.

The unique directive

• valid locations: scalar non list fields on non embedded types
• behaviour: If the directive is present the field must contain only unique values. No two nodes can contain the same value. If Migrations are activated this will create a unique constraint in the underlying database. This implies that an index is created.
• overlaps with: indexes
• optional: yes.
• arguments: none

Examples

type Blog {
  id: ID! @id
  name: String!
  slug: String! @unique
}

The indexes directive

• valid locations: on non-embedded types
• behaviour:
  
  
  
  • Specifies the indexes that should be created for this type.
  
  
  • Indexes can be created for embedded types as well, which means they have to be created once for each parent type.
  
  
• overlaps with: unique
• optional: yes.
• arguments:
  
  
  
  • value: [IndexInput]
    
    
    
    
    
    • Specifies the indexes that should be created for this type.
    
    
    
    • optional: no
    
    
    
    • IndexInput:
      
      
      
      
      
      
      
      • fields: [String]!
        
        
        
        
        
        
        
        
        
        • Contains an array of the field names that should make up the index.
        
        
        
        
        
        • A field name can reference an embedded type through dot notation, e.g. Blog.posts.title
        
        
        
        
        
        • optional: no.
        
        
        
        
        
      
      
      
      
      • name: String!
        
        
        
        
        
        
        
        
        
        • The name of the index.
        
        
        
        
        
        • optional: no
        
        
        
        
        
      
      
      
      
      • unique: Boolean
        
        
        
        
        
        
        
        
        
        • Whether this index imposes a unique constraint.
        
        
        
        
        
        • optional: yes. Defaults to false.
        
        
        
        
        
      
      
      
      
    
    
    
  
  
• validations:
  
  
  
  • The specified indexes must not contradict any indexes specified through the unique and index directives.
  
  
  • When relation fields to non-embedded types are used within an index, the relation link must be stored within the type.
  
  
  • All used field names must exist within the type or within its embedded types.
  
  

Examples

Specifying an index on one field.

type Post @indexes(value: [
  { fields: ["published"] name: "Post_published_idx" }
]) {
  id: ID! @id
  title: String!
  published: DateTime!
  viewCount: Int!
  author: User!
}

Specifying an index with a unique constraint on one field. This could be also achieved through the unique directive.

type Post @indexes(value: [
  { fields: ["title"] name: "Post_title_idx" unique: true }
]) {
  id: ID! @id
  title: String!
  published: DateTime!
  viewCount: Int!
  author: User!
}

Specifying an index on two fields.

type Post @indexes(value: [
  { fields: ["title", "viewCount"] name: "Post_title_viewCount_idx" }
]) {
  id: ID! @id
  title: String!
  published: DateTime!
  viewCount: Int!
  author: User!
}

Specifying an index that spans a non-embedded and embedded type:

type Blog @indexes(value: [
  { fields: ["name", "posts.title"] name: "Blog_name_posts_title_idx" }
]){
  id: ID! @id
  name: String!
  posts: [Post]
}

type Post @embedded {
  title: String!
}

Specifying a index with unique constraint on two fields to express that the combination of author and title must be unique. The field author field is a relation field and therefore the id of an Author will be part of the index. Creating this index is only possible if the link to the author will be stored within the Post node.

type Post @indexes(value: [
  { fields: ["title", "author"] name: "Post_title_author_idx" unique: true }
]) {
  id: ID! @id
  title: String! 
  published: DateTime!
  viewCount: Int!
  author: User! @relation(strategy: EMBED)
}

[sachaarbonel]

I think it might be considered in the spec to add a field for the use of index types. In the case of full text search in Postgresql for example one would need to create a GIN index.

[jhalborg]

This sounds great, it would be awesome to have the option to add custom indexes to models!

[Siyfion]

I think that having the ability to specify an index with multiple fields is the killer feature in this spec; I can't wait to see this added.

[jide]

I'm curious how this would affect the "where" query. Would it be possible to query a type by 2 unique fields ?

[mavilein]

@jide : Yes i think this is what we would have to do.

[schickling]

Suggestion: Let's introduce a new optional config option inputName which if provided allows you to override the input field name for the generated where input types as well as the generated orderBy enum values (e.g. titleViewCount_DESC).

type Post @indexes(value: {
  fields: ["title", "author"]
  name: "Post_title_author_idx"
  inputName: "authorAndTitle"
  unique: true
}) {
  id: ID! @id
  title: String!
  published: DateTime!
  viewCount: Int!
  author: User!
}

Example TS client query:

// with `inputName` provided
const post = await prisma.post({ authorAndTitle: { author: { id: 123 } title: 'Hello' } })

// without `inputName`
const post = await prisma.post({ Post_title_author_idx: { author: {id: 123} title: 'Hello' } })

// Order by compound index
const posts = await prisma.posts({ orderBy: 'authorAndTitle' })

[brainafesoh]

Hi guys. About this:

Specifying a index with unique constraint on two fields to express that the combination of author and title must be unique. The field author field is a relation field and therefore the id of an Author will be part of the index. Creating this index is only possible if the link to the author will be stored within the Post node.

type Post @indexes(value: [
  { fields: ["title", "author"] name: "Post_title_author_idx" unique: true }
]) {
  id: ID! @id
  title: String! 
  published: DateTime!
  viewCount: Int!
  author: User! @relation(strategy: EMBED)
}

I tried implementing the feature in my datamodel but no changes observed.

My datamodel looks like this;

type Class @indexes(value: [{
    fields: ["faculty", "level", "option"]
    name: "faculty_level_option_identifies_class" 
    unique: true
}]) {
    id: ID! @unique
    faculty: String!
    level: Int!
    option: String
    name: String!
    courses: [Course!]!
    createdAt: DateTime!
    updatedAt: DateTime!
}

_At the end I expect the constraints to keep me from creating a 2 or more classes with similar properties but to allow when one or more properties are changed: e.g classes with the same faculty and level but different option_

I'll be grateful for any help or other possible ways of accomplishing this.

[mavilein]

@brainafesoh : It does not work yet because it is not implemented yet. This is still in draft state.

[brainafesoh]

@brainafesoh : It does not work yet because it is not implemented yet. This is still in draft state.

Ok @mavilein , hope it'll be available soon enough. Thumbs up already for the work u guys are doing. Prisma is dooope

[schickling]

We should also consider sparse: true for MongoDB indexes. (See their docs.)

Generally we should consider providing a "raw escape hatch" to allow for non-trivial indexes. See here:

• https://www.postgresql.org/docs/8.3/sql-createindex.html
• https://www.citusdata.com/blog/2017/10/17/tour-of-postgres-index-types/

[colinhacks]

@mavilein I'm confused about the implementation status here. Why does the Release page say that v2 is in Preview? Aren't things in Preview already implemented and available for use (like Prisma Client)? I'm confused 😕

[mavilein]

@colinmcd94 : The information on that page is only partially correct. I apologize for the confusion. 🙏 The truth is that the datamodel v1.1 is only partially in preview. The features for indexes and polymorphic relations are still missing in this preview. However implementing the indexes feature is on the list of my next things to do and i will start it at the beginning of February. So stay tuned 🙂

[colinhacks]

Excellent! Thanks for the clarification. So excited for v1.1!

[colinhacks]

@mavilein Followup quesiton - is Postgres support for v1.1 still way off? I'm okay using Mongo as my backend but if Postgres support is coming in the next, say, 2 months I'll wait for it.

[mavilein]

@colinmcd94 : We are aiming for having a closed beta of v1.1 for Postgres at the beginning of February. Please contact me in our Slack if you would like to participate.

[nikolasburk]

Hey @colinmcd94, quick heads-up that I've edited your comments and changed v2 to v1.1 since that's how we refer to this upcoming version of the datamodel now 🙌

[FluorescentHallucinogen]

@nikolasburk Why the version have been changed from v2 to v1.1? Will changes be not breaking? Or is it not just a renaming, but a division of changes into two parts v1.1 and v2 to release v1.1 as soon as possible?

What about using MAJOR.MINOR.PATCH (e.g. 1.1.0) semantic versioning?

[nolandg]

Does the proposed @indexes directive plan on solving performance issues for queries on non-unique fields like where: {title_contains: "text"} ?

As far as I understand, this query will be very slow unless there's a trigram index built for title. Would specifying @indexes for it cause Prisma to do something like this for Postgres:
sql CREATE INDEX Post_title_index ON Post USING gin (col gin_trgm_ops);
And then where queries would automatically pass something like %my_query%?

As a workaround for now, if I manually CREATE INDEX on my Postgres database after Prisma deploys, will that work? Will Prisma kill it on every deploy?

[mavilein]

@nolandg:

1. Currently we have just planned for normal b-tree indexes. Do you want to open a feature request for this? I guess we should allow for customizing the type of index.
2. Yes that will work. We are not touching existing indexes.

[sandorTuranszky]

Will TTL Indexes feature be available? @mavilein

I need to set TTL for values so they get removed after some time automatically by MongoDB.
E.g. in Mongoose I can do this:

var verificationTokenSchema = new Schema({
    _userId: {type: ObjectId, required: true},
    token: {type: String, required: true},
    createdAt: {type: Date, required: true, default: Date.now, expires: '4h'}
});

so the validation token is removed after 4 hours

[mavilein]

@sandorTuranszky : We haven't planned for that yet. Can you create a separete issue for that feature? Then we can implement this quickly after the initial implementation.

[sandorTuranszky]

@mavilein Created an issue 4102

[diversit]

I don't see anything about the order of the index.
Shouldn't there be a way to order the index Ascending or Descending?

[amadeus-x1]

@mavilein Hey, is there an ETA for this feature in beta release?

[mark-stephenson-]

This is currently the only thing that is preventing me from using this. REALLY looking forward to this being implemented

[solarsoft0]

i'm new to prisma, and the progress on this feature is really turning me off, but awesome work @prisma so far

[sorenbs]

Thank you for all the comments here. We expect to include support for indexes when we release the next major version of Prisma in ~3 months. Constraints will be added shortly after.

[matthewmueller]

Hey everyone, we're currently specing constraints over at: https://github.com/prisma/rfcs/blob/0001-datamodel-2/text/0001-datamodel-2.md#checks-constraints

It's currently in draft mode and we'd really love your thoughts to help us make it fit your use cases.

[jide]

Hey, glad to hear this will get some attention soon ! Does 3 months mean a stable release ? Can we expect alpha/beta sooner than that ?

[markstreich]

Hey everyone, we're currently specing constraints over at: https://github.com/prisma/rfcs/blob/0001-datamodel-2/text/0001-datamodel-2.md#checks-constraints

https://github.com/prisma/rfcs/ gives 404 for me, is it private or has it moved?

[danvim]

So is the indices directive out yet? Or when can we expect it to be released?