Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[RFC] Datamodel v1.1 - Indexes and Constraints #3405

Open
mavilein opened this issue Nov 1, 2018 · 31 comments

Comments

@mavilein
Copy link
Member

commented Nov 1, 2018

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)
}
@Sach97

This comment has been minimized.

Copy link

commented Nov 2, 2018

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 comment has been minimized.

Copy link

commented Nov 7, 2018

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

@Siyfion

This comment has been minimized.

Copy link
Contributor

commented Nov 12, 2018

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

This comment has been minimized.

Copy link

commented Nov 19, 2018

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

@mavilein

This comment has been minimized.

Copy link
Member Author

commented Nov 20, 2018

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

@schickling schickling changed the title [Data Model Spec] Indexes and Constraints [RFC] Datamodel v2 - Indexes and Constraints Dec 3, 2018
@schickling

This comment has been minimized.

Copy link
Member

commented Dec 7, 2018

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' })
@schickling schickling added the area/next label Dec 7, 2018
@brainafesoh

This comment has been minimized.

Copy link

commented Dec 20, 2018

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

This comment has been minimized.

Copy link
Member Author

commented Dec 21, 2018

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

@brainafesoh

This comment has been minimized.

Copy link

commented Dec 21, 2018

@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

This comment has been minimized.

Copy link
Member

commented Jan 9, 2019

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:

@colinmcd94

This comment has been minimized.

Copy link

commented Jan 20, 2019

@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

This comment has been minimized.

Copy link
Member Author

commented Jan 21, 2019

@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 🙂

@colinmcd94

This comment has been minimized.

Copy link

commented Jan 21, 2019

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

@colinmcd94

This comment has been minimized.

Copy link

commented Jan 22, 2019

@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

This comment has been minimized.

Copy link
Member Author

commented Jan 22, 2019

@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 nikolasburk changed the title [RFC] Datamodel v2 - Indexes and Constraints [RFC] Datamodel v1.1 - Indexes and Constraints Feb 13, 2019
@nikolasburk

This comment has been minimized.

Copy link
Member

commented Feb 13, 2019

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

This comment has been minimized.

Copy link

commented Feb 13, 2019

@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

This comment has been minimized.

Copy link

commented Feb 18, 2019

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:

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

This comment has been minimized.

Copy link
Member Author

commented Feb 19, 2019

@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

This comment has been minimized.

Copy link

commented Feb 26, 2019

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

This comment has been minimized.

Copy link
Member Author

commented Feb 26, 2019

@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

This comment has been minimized.

Copy link

commented Feb 26, 2019

@mavilein Created an issue 4102

@diversit

This comment has been minimized.

Copy link

commented Apr 8, 2019

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

This comment has been minimized.

Copy link

commented Apr 9, 2019

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

@mark-stephenson-

This comment has been minimized.

Copy link

commented Apr 24, 2019

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

@solarsoft0

This comment has been minimized.

Copy link

commented May 1, 2019

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

@sorenbs

This comment has been minimized.

Copy link
Member

commented May 2, 2019

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

This comment has been minimized.

Copy link
Contributor

commented May 2, 2019

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

This comment has been minimized.

Copy link

commented May 2, 2019

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

This comment has been minimized.

Copy link

commented Sep 5, 2019

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

This comment has been minimized.

Copy link

commented Sep 19, 2019

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
You can’t perform that action at this time.