@prisma/client 2.0.0-preview024

on Node.js NPM

Today, we are issuing the twenty-fourth Preview release: 2.0.0-preview024 (short: preview024).

Major improvements

Reduced size of Prisma Client & Azure functions support

This release contains major improvements for Prisma Client. It now supports Windows Azure functions. In addition to that, the generated Prisma Client code inside your node_modules directory now is a lot smaller.

Another improvement is a better debugging experience. When setting the DEBUG environment variable (e.g. with export DEBUG="*"), the logging output now contains the names of Prisma Client API calls.

Use relation fields as ID on a Prisma model

In this release, it's now possible to use relation fields of Prisma models as IDs. In "database-speak", this means that you can now have both a primary key and a foreign key constraint on the same column.

Reference a single-field ID

For example, a Movie could always be identified by its Director:

model Movie {
  director Director @id
  title    String
}

model Director {
  id Int @id @default(@autoincrement())
  name String
}

This is what the corresponding SQL (in SQLite dialect) looks like:

CREATE TABLE "Movie" (
    "director" INTEGER NOT NULL  ,
    "title" TEXT NOT NULL DEFAULT '' ,
    PRIMARY KEY ("director"),
    FOREIGN KEY ("director") REFERENCES "Director"("id")
);

CREATE TABLE "Director" (
    "id" INTEGER NOT NULL  PRIMARY KEY AUTOINCREMENT,
    "name" TEXT NOT NULL DEFAULT '' 
);

// Run inside `async` function
const movie = await prisma.movie.create({
  data: {
    title: "Hello World",
    director: {
      create: {
        name: "Alice"
      }
    }
  },
})

// Run inside `async` function
const director = await prisma.director.create({
  data: {
    name: "Bob",
    movies: {
      create: [{
        title: "Hello World"
      }]
    }
  },
})

Reference a multi-field ID

You can also create a relation to a multi-field ID:

model Movie {
  director Director @id @map(["firstName", "lastName"]) 
  title    String
}

model Director {
  firstName   String
  lastName    String

  @@id([firstName, lastName])
}

Note that in this case, the Movie table in the underlying database will actually have two physical columns called firstName and lastName. These are referencing the respective firstName and lastName column on the Director table.

Here is what the above models correspond to in SQL:

CREATE TABLE "Movie" (
    "firstName" TEXT NOT NULL  ,
    "lastName" TEXT NOT NULL  ,
    "title" TEXT NOT NULL DEFAULT '' ,
    PRIMARY KEY ("firstName","lastName"),
    FOREIGN KEY ("firstName","lastName") REFERENCES "Director"("firstName","lastName") 
);

CREATE TABLE "Director" (
    "firstName" TEXT NOT NULL DEFAULT '' ,
    "lastName" TEXT NOT NULL DEFAULT '' ,
    PRIMARY KEY ("firstName","lastName")
);

In many cases, it might make sense to name the columns on Movie differently. For example, they could be called directorFirstName and directorLastName. This can be achieved via adding the @map attribute to the field:

model Movie {
  director Director @id @map(["directorFirstName", "directorLastName"]) @relation(references: [firstName, lastName])
  title    String
}

model Director {
  firstName   String
  lastName    String

  @@id([firstName, lastName])
}

Note that in this case you could also omit the @relation attribute, the result would be the same:

model Movie {
  director Director @id @map(["directorFirstName", "directorLastName"])
  title    String
}

model Director {
  firstName   String
  lastName    String

  @@id([firstName, lastName])
}

In this case, the field names in @map on Movie get matched with the field names in @@id on Director.

Both cases correspond to the following SQL:

CREATE TABLE "Movie" (
    "directorFirstName" TEXT NOT NULL  ,
    "directorLastName" TEXT NOT NULL  ,
    "title" TEXT NOT NULL DEFAULT '' ,
    PRIMARY KEY ("directorFirstName","directorLastName"),
    FOREIGN KEY ("directorFirstName","directorLastName") REFERENCES "Director"("firstName","lastName")
);

CREATE TABLE "Director" (
    "firstName" TEXT NOT NULL DEFAULT '' ,
    "lastName" TEXT NOT NULL DEFAULT '' ,
    PRIMARY KEY ("firstName","lastName")
);

// Run inside `async` function
const movie = await prisma.movie.create({
  data: {
    title: 'Hello World',
    director: {
      create: {
        firstName: 'Alice',
        lastName: 'Allen',
      },
    },
  },
})

// Run inside `async` function
const director = await prisma.director.create({
  data: {
    firstName: 'Bob',
    lastName: 'Nolan',
    movies: {
      create: [
        {
          title: 'Hello World',
        },
      ],
    },
  },
})

Multi-field ID with a relation field (which targets a model with a single-field ID)

You can also create a multi-field ID on a model that contains a relation field:

model Movie {
  director Director
  title    String

  @@id([director, title])
}

model Director {
  id   String @id @default(cuid())
  name String
}

This corresponds to the following SQL:

CREATE TABLE "Movie" (
    "director" TEXT NOT NULL  ,
    "title" TEXT NOT NULL DEFAULT '' ,
    PRIMARY KEY ("director","title"),
    FOREIGN KEY ("director") REFERENCES "Director"("id")
);

CREATE TABLE "Director" (
    "id" TEXT NOT NULL  ,
    "name" TEXT NOT NULL DEFAULT '' ,
    PRIMARY KEY ("id")
);

// Run inside `async` function
const movie = await prisma.movie.create({
  data: {
    title: 'Hello World',
    director: {
      create: {
        name: 'Alice',
      },
    },
  },
})

// Run inside `async` function
const director = await prisma.director.create({
  data: {
    name: 'Bob',
    movies: {
      create: [
        {
          title: 'Hello World 2',
        },
      ],
    },
  },
})

Multi-field ID with a relation field (which targets a model with a multi-field ID)

You can also define a multi-field ID on a model which contains a relation field that targets a model with a multi-field ID:

model Movie {
  director Director
  title    String

  @@id([director, title])
}

model Director {
  firstName String
  lastName  String

  @@id([firstName, lastName])
}

This is what the above code translates to in SQL:

CREATE TABLE "Movie" (
    "director_firstName" TEXT NOT NULL  ,
    "director_lastName" TEXT NOT NULL  ,
    "title" TEXT NOT NULL DEFAULT '' ,
    PRIMARY KEY ("director_firstName","director_lastName","title"),
    FOREIGN KEY ("director_firstName","director_lastName") REFERENCES "Director"("firstName","lastName") 
);

CREATE TABLE "Director" (
    "firstName" TEXT NOT NULL DEFAULT '' ,
    "lastName" TEXT NOT NULL DEFAULT '' ,
    PRIMARY KEY ("firstName","lastName")
);

Similar to the case before, you can also give names to the added columns on Movie by using the @map attributed:

model Movie {
  director Director @map(["directorFirstName", "directorLastName"]) @relation(references: [firstName, lastName])
  title    String

  @@id([director, title])
}

model Director {
  firstName String
  lastName  String

  @@id([firstName, lastName])
}

And as before you can also omit the @relation attribute in this scenario:

model Movie {
  director Director @map(["directorFirstName", "directorLastName"])
  title    String

  @@id([director, title])
}

model Director {
  firstName String
  lastName  String

  @@id([firstName, lastName])
}

In both cases, the models correspond to the following tables:

CREATE TABLE "Movie" (
    "directorFirstName" TEXT NOT NULL  ,
    "directorLastName" TEXT NOT NULL  ,
    "title" TEXT NOT NULL DEFAULT '' ,
    PRIMARY KEY ("directorFirstName","directorLastName","title"),
    FOREIGN KEY ("directorFirstName","directorLastName") REFERENCES "Director"("firstName","lastName")
);

CREATE TABLE "Director" (
    "firstName" TEXT NOT NULL DEFAULT '' ,
    "lastName" TEXT NOT NULL DEFAULT '' ,
    PRIMARY KEY ("firstName","lastName")
);

// Run inside `async` function
const movie = await prisma.movie.create({
  data: {
    title: 'Hello World',
    director: {
      create: {
        firstName: 'Alice',
        lastName: 'Allen',
      },
    },
  },
})

// Run inside `async` function
const director = await prisma.director.create({
  data: {
    firstName: 'Bob',
    lastName: 'Nolan',
    movies: {
      create: [
        {
          title: 'Hello World',
        },
      ],
    },
  },
})

Breaking changes

MODELGetSelectPayload and MODELGetIncludePayload have been merged into MODELGetPayload. More info here.

Fixes and improvements per Prisma 2 repository

prisma2

• fix(#1728) Handle disconnected error for introspection engine
• Better handling of introspect crash
• [Introspection] Remove type annotation from Postgres default in cases other than Text
• Integration tests: SQLite
• Integration tests: MySQL
• Introspection composite foreign keys pointing to composite primary keys
• Prisma doesn't work on Azure windows functions
• [Introspection] Postgres @default include type information
• Invalid response data: the query result was required, but an empty Object((Weak)) was returned instead (drupal)
• Introspection bug for @@unique name on MySQL
• Integration tests for SQLite and MySQL
• Put @prisma/sdk version in lockstep with prisma2
• introspect for SQLite returns id as String when it should be Int
• Update docs about PostgreSQL socket
• Document logging options (via DEBUG env var)
• Kebab-case parameters due to a change in QE/IE/ME

migrate

• Kebab-case-parameters in ME

prisma-client-js

• Reduce size of generated client
• Print query parameters
• Nested create broken in latest alpha
• Query engine generates wrong SQL queries for Hierarchical Data
• Log all Prisma Client method executions
• Switching to kebab-case on QE/ME/IE params
• Change docs about PostgreSQL socket connection

prisma-engines

• Comment out fields with empty names
• Allow Default Values for Enums that look like Booleans
• Postgres Default Values
• Test and fix mapping of mysql and postgres native types
• Start Adding Test Cases for Using a Relation or Parts of it as Primary Key
• Fix prisma-engines#567
• Fix @unique on composite relation fields
• Better way to query with many compound ids
• ME: Indices to composite foreign keys are impossible to create
• Update README with proper cargo test command
• Return a more intelligible error on unexpectedly empty response
• improve nested pagination from O(n log n) to O(n)
• optimize m2m join with in clause
• Index join tables on the B side
• Make foreign key fields cascade on delete by default
• Test and fix Id as part of relation in migration engine
• ME: Many-to-many table indices sub-optimal
• Recognize int as Integer on Sqlite
• Unify cli input parsing
• Remove From DMMF