@prisma/client 2.16.0

on Node.js NPM

Today, we are excited to share the 2.16.0 stable release 🎉

🌟 Help us spread the word about Prisma by starring the repo or tweeting about the release. 🌟 

Major improvements

The Prisma CLI moves from the @prisma/cli to the prisma npm package

Going forward, you can install the Prisma CLI via the prisma npm package:

npm install prisma --save-dev

or

yarn add prisma --dev

The reason for this is that a number of users were experiencing issues when running the npx prisma command. Without a local installation of the @prisma/cli package, this would invoke the Prisma 1 CLI leading to an error message. From now on, npx prisma is always going to work.

We will also deprecate the @prisma/cli package. Please do a find and replace across your codebase to transition over from @prisma/cli to prisma. To make this transition easier, we'll keep publishing updates to both packages for another month and plan to stop updating the @prisma/cli package with release 2.18.0.

There are no changes to the @prisma/client npm package name.

Efficient bulk creates with createMany

Insert data a whole lot faster with createMany. Here's an example:

const result = await prisma.user.createMany({
  data: [
    { email: "alice@prisma.io" },
    { email: "mark@prisma.io" },
    { email: "jackie@prisma.io" },
    { email: "bob@prisma.io" },
  ],
});

console.log(`Created ${result.count} users!`);

This feature is in preview right now. Enable it with the createMany preview flag:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["createMany"]
}

📚 Documentation: Create multiple records

Learn more in this issue.

Order by relation fields with orderBy

Ever wish you could order posts by an author's name? Or sort transactions by account? Now you can! We've expanded orderBy to support ordering by relations:

cons posts = await prisma.post.findMany({
  orderBy: [
    {
      author: {
        name: "asc",
      },
    },
  ],
});

This feature is in preview right now. Enable it with the orderByRelation preview flag:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["orderByRelation"]
}

📚 Documentation: Sort by relation

Learn more in this issue.

Improvements to the nativeTypes Preview feature

We are working hard on making the Prisma experience smoother for folks that are using the nativeTypes Preview feature and are getting close to releasing it for General Availability.

If you have any feedback for this feature, please share it on GitHub.

New Unsupported type allows to surface any database type in the Prisma schema

This release introduces a new Unsupported type in the Prisma schema. It acts as an escape hatch and allows to surface fields in Prisma schema for database types that are not yet supported by Prisma. For example, MySQL's POLYGON type is not yet natively supported by Prisma, but can now be added to the Prisma schema using the Unsupported("polygon") type.

The new type is especially relevant during introspection where previously fields of unsupported types would have been commented out, they're now included in the Prisma schema and of type Unsupported.

Fields of the Unsupported type are added to the Prisma schema in either of two ways:

• via introspection in cases when Prisma detects a database column with a type that is not yet natively supported by Prisma
• manually in combination with Prisma Migrate; in this case Prisma Migrate generates a SQL migration with the type that's provided as an argument to Unsupported in the Prisma schema

Note that fields of type Unsupported will not be surfaced in the Prisma Client API.

Example with Prisma Migrate

Here's an example of the new Unsupported type that uses MySQL's multilinestring:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["nativeTypes"]
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

model User {
  id    Int                            @id @default(autoincrement())
  email String                         @unique
  name  String?
  bio   Unsupported("multilinestring") @unique
}

Prisma Migrate generates the following SQL for the User model:

CREATE TABLE `User` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(191) NOT NULL,
  `name` VARCHAR(191),
  `bio` multilinestring NOT NULL,

  UNIQUE INDEX `User.email_unique`(`email`),
  UNIQUE INDEX `User.bio_unique`(`bio`),
  PRIMARY KEY (`id`)
);

How Unsupported fields affect the Prisma Client API

Since Prisma Client doesn't yet "understand" the Unsupported type and therefore cannot read or write to its fields, we've also disabled creates and upserts on models that have a required Unsupported field without a default value. This prevents constraint violations at runtime.

To make this explicit, a new @@ignore attribute is added to the models that contain fields of type Unsupported during introspection:

model User {
  id       Int                  @id @default(autoincrement())
  location Unsupported("point")

  @@ignore
}

// Not permitted because the database requires a `location`, but it can't be
// provided because the `point` type is unsupported by Prisma Client.
const user = await prisma.user.create({});

If the @@ignore attribute is added to a model, the relation fields of that model on other models will be annotated with the @ignore attribute, for example:

model User {
  id       Int                  @id @default(autoincrement())
  location Unsupported("point")

  @@ignore
}

model Post {
  id     Int    @id @default(autoincrement())
  title  String
  author User   @ignore
}

New notation for default values via dbgenerated()

dbgenerated() in the @default field directive can now take a String argument that enables developers to reflect database-level default values that are not yet natively supported by Prisma.

This changes the signature of the attribute from dbgenerated() to dbgenerated(default: String). As an example, you can use @default(dbgenerated("''")) to define an empty string as the default value for a column in MySQL.

These default values will be surfaced when introspecting the database or created/changed when defined manually in combination with Prisma Migrate.

This feature can be used alongside the new Unsupported type as well as with regular Prisma types.

Examples with Prisma Migrate

Setting a default value on a multilinestring column:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["nativeTypes"]
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

model User {
  id    Int                            @id @default(autoincrement())
  email String                         @unique
  name  String?
  bio   Unsupported("multilinestring") @unique @default(dbgenerated("''"))
}

Prisma Migrate generates the following SQL for the User model:

CREATE TABLE `User` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(191) NOT NULL,
  `name` VARCHAR(191),
  `multilinestringField` multilinestring NOT NULL DEFAULT '',

  UNIQUE INDEX `User.email_unique`(`email`),
  UNIQUE INDEX `User.bio_unique`(`bio`),
  PRIMARY KEY (`id`)
);

Setting a default value for UUIDs via the pgcrypto PostgreSQL extension:

model User {
  id   String  @id @db.Uuid @default(dbgenerated("gen_random_uuid()"))
  name String?
}

Prisma Migrate generates the following SQL for the User model:

CREATE TABLE "User" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "name" TEXT,

    PRIMARY KEY ("id")
);

Note that the example above requires the corresponding extension to be enabled (in this case: CREATE EXTENSION "pgcrypto";).

New native type attributes available on PostgreSQL

We've added new native types for the postgresql provider. Here's an overview of the new type mappings:

Here's an example that uses the four new types in a Prisma schema:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["nativeTypes"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id                    Int     @id @default(autoincrement())
  ipAddress             String? @db.Inet
  balance               Decimal @db.Money
  oid                   Int     @db.Oid
  citextName            String  @db.Citext
}

Prisma Migrate generates the following SQL for the User model:

CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "ipAddress" INET,
    "balance" MONEY NOT NULL,
    "oid" OID NOT NULL,
    "citextName" CITEXT NOT NULL,
    PRIMARY KEY ("id")
);

More changes to the nativeTypes Preview feature

• When using the nativeTypes Preview feature, introspection will only render a native attribute if the column type does not map to Prisma's corresponding default type. This might remove any existing type annotations upon (re-)introspection where the default mapping is currently in place.
• MySQL
  • Columns of type BIT(1) can now be mapped to Prisma's Boolean type as well using the Bit native type attribute, e.g. myField Boolean @db.Bit(1).
  • Default mapping for Prisma's Float type has been changed from DECIMAL(65,30) to Double.
• PostgreSQL
  • Default mapping for Prisma's Float type has been changed from DECIMAL(65,30) to Double.
• SQLite
  • Default mapping for Prisma's Decimal type has been changed from REAL to DECIMAL.

queryRaw Breaking Changes

On MySQL only, fields of type Boolean are stored as TINYINT(1) in the database. We no longer coerce the value to a Boolean. That means queryRaw now returns integers of value 0 or 1 instead of true or false for Boolean fields.

Host Prisma Studio on Vercel

You can host your own instance of Prisma Studio on Vercel by following the instructions in the README of this example repo.

More improvements

• You can now skip seeding when calling prisma migrate dev or prisma migrate reset with the —-skip-seed flag.
• You can now use the --schema option on the prisma db seed command to execute the command with a custom Prisma schema location.

Prisma Client Go gets dynamic filters

Give your users more control over how they filter and order their data. Build up filters over time instead of all at once.

Here's an example of setting specific attributes dynamically:

func CreateUser(w http.ResponseWriter, r *http.Request) {
    var params []db.UserSetParam
    email := r.PostFormValue("email")
    kind := r.PostFormValue("kind")
    if kind == "customer" {
        // Set the referrer for users of type customer only
        params = append(params, db.User.Referer.Set(r.Header.Get("Referer"))
    }
    user, err := client.User.CreateOne(
        db.User.Kind.Set(kind),
        db.User.Email.Set(email),
        params...,
    ).Exec(r.Context())
    // ... Handle the response
}

Learn more in the Go documentation.

Fixes and improvements

Prisma Client

• Improve Prisma Client error handling
• TypeScript deploy "Cannot find module .prisma/client"
• Environmental variables conflict
• Inconsistent DMMF for relation lists
• querying an integer from a mysql tinyint(1) column fails
• Efficiently create many records
• Support Order by Relations
• PANIC: 1
• PANIC: 1, select same model twice in different levels
• PANIC: called Result::unwrap() on an Err value: Could not parse stored DateTime string: 2020-11-27 01:17:30 (input contains invalid characters)
• Narrow type for rejectOnNotFound
• Remove misleading "in use" numbers from Connection Pool timeout error message
• count query not working in react-prisma

Prisma Migrate

• Error: Error in migration engine. Reason: [C:\Users\runneradmin.cargo\git\checkouts\quaint-9f01e008b9a89c14\6efc6f6\src\connector\postgres\error.rs:138:29] index out of bounds: the len is 1 but the index is 1
• Better error message when seeding if dependencies are missing
• DB seed does not work with custom schema location
• Do not render specific native notation when the underlying column matches the default mapping.
• Improve Migrate automatic panic reports by including the command ran
• Introspection: Remove comments for unsupported fields
• Improvement: Optionally skip seeding during migrate reset

Language tools (e.g. VS Code extension)

• Support for Unsupported, dbgenerated() and @@ignore

Prisma Studio

• Feature Request: Run prisma studio remotely
• Open in new tab for relation lists does not work as expected

Prisma Engines

• Change default mapping of scalar type Float
• Query Engine is generating min and max aggregate for Json types to DMMF
• Test ldd output of engines

Credits

Huge thanks to @aruld for helping!