Prisma: 2.16.0 Release

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: "[email protected]" },
    { email: "[email protected]" },
    { email: "[email protected]" },
    { email: "[email protected]" },
  ],
});

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.

<!-- However, they can be accessed using plain SQL queries submitted with $queryRaw. -->

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:

| PostgreSQL | Prisma | Note | | ---------- | --------- | ------------------------------- | | Inet | String | | | Money | Decimal | | | Oid | Int | | | Citext | String | Requires the Citext extension |

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.

<details><summary>Expand for some more info about how this affects current users</summary>

PostgreSQL and MySQL users

• If no action is taken, the next time a new migration is created with prisma migrate dev, Prisma Migrate will generate DDL statements to change columns of Prisma's type Float to use Double instead of Decimal. These changes may lead to rounding errors due to differences in precision.
• If changing the underlying column is not desired, users can specify Decimal and continue using that as as the column type.
  • Steps involved:
  • make sure the preview feature flag for native types is enabled
  • Change the field to be of scalar type Decimal
  • use the native type notation to pin the underlying type to Decimal or re-introspect your database with prisma introspect after enabling native types
  • Example before: myField Float
  • Example after: myField Decimal @db.Decimal(65,30)

SQLite

• If you were using Decimal before on SQLite, with the native type feature flag enabled, Prisma Migrate will change the type in the next migration from REAL to DECIMAL.
• This can be avoided by changing the scalar type in the Prisma schema from Decimal to Float, or running re-introspection which will do the same.

</details>

<!-- ### Skip native type syntax for default types during (re-)introspection

When re-introspecting a database and the native type preview feature flag is enabled, Prisma will not render the native type notation for fields that are using Prisma's default column types.

It will:

• omit (or remove) the native type notation for any field which is using the default mapping, e.g. name String? for a field of type varchar(191) on MySQL (new behavior)
• render (or ad the native type notation for all other fields, e.g. name String? @db.VarChar(50) for a field of type varchar(50) on MySQL (previous behavior) -->

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!