@prisma/client 2.17.0 on Node.js NPM

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

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

Overview

Native types are now stable
Prisma Migrate now works with cloud-hosted databases (e.g. Heroku)
Soft resets for cloud-hosted environments
More improvements and bug fixes for Prisma Migrate
Improvements and changes for prisma db push
prisma db seed now supports custom schema locations
Improvements and bug fixes in Prisma Client

Note that this release comes with some breaking changes. Read the Breaking changes section below to learn more.

Major improvements & new features

Native types are now stable

The nativeTypes preview feature flag has first been introduced in 2.10.0. Thanks to your continuous and awesome feedback for this feature, we're now able to release usage of native database types in the Prisma schema for General Availability 🎉

Note that this release comes with a few minor breaking changes compared to previous versions. Please read about the Breaking Changes below.

If you haven't followed previous releases, expand below to learn more about everything that's now possible with the new native types.

Expand to learn more about the benefits of native types

Rich column type mapping for Prisma types

Each Prisma type can now map to one of multiple native database types. Native database type attributes are:

Specific to the underlying provider - for example, PostgreSQL uses @db.Boolean for Boolean whereas MySQL uses @db.TinyInt
Written in PascalCase (for example, VarChar or Text)
Prefixed by @db, where db is the name of the datasource block in your Prisma schema

Type attributes give you:

Exact control over what native type Prisma Migrate creates in the database - for example, a String can be @db.VarChar(200) or @db.Char(50)
An enriched schema when you introspect - you can see if String is varchar(200) or just text.

To learn more about all the possible native type attributes, check out the type mapping reference in the docs.

Extending Prisma schema beyond supported column types

Column types which are not (yet?) supported by Prisma Migrate can be used with Prisma Migrate and introspection through the Prisma type Unsupported which was introduced in Preview in the last release:

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

dbgenerated() in the @default directive can now take a String argument that enables developers to reflect database-level DEFAULT constraints not yet supported by Prisma Migrate. These default values will be surfaced when introspecting with prisma introspect and created/changed when using Prisma Migrate.

Developers can now add @@ignore and @ignore attributes to models and fields, for fields they want to manage via Prisma Migrate but not surfaced in Prisma Client. These attributes are added by Prisma when introspecting entities which are not supported, e.g. a table with no unique column. They are now also kept in the Prisma schema when re-introspecting a database.

Prisma Migrate now works with cloud-hosted databases (e.g. Heroku)

Before this release, Prisma Migrate could be used to apply migrations in a cloud-hosted environment (CI/CD pipeline, manual deployment to production, staging, etc.), but it was impossible to create new migrations, due to the requirement of a shadow database. Prisma Migrate expects to have privileges to create the shadow database using the same credentials, but cloud providers generally do not allow creating logical databases.

Starting from this release, prisma migrate dev can now be used in development with cloud-hosted databases by configuring a separate connection URL for the shadow database.

To develop natively in the cloud with Prisma Migrate, developers can create two cloud-hosted databases, one being the development- and the other being the shadow-database.

The connection URI for the shadow database can be configured in the datasource block of the Prisma schema file, similarly to the datasource URL, by defining a shadowDatabaseUrl variable:

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

Soft resets for cloud-hosted environments

Another common limitation of cloud-hosted environments is that the database cannot be dropped and re-created using DROP DATABASE and CREATE DATABASE, due to insufficient privileges. Prisma Migrate so far relied on these statements to ensure the database is empty when it needs to be reset.

Database resets in the context of Prisma Migrate now gracefully fall back to dropping constraints, indexes and tables, if there are insufficient privileges to reset the database using DROP DATABASE.

Note that this comes with the caveat that there could be other entities in the database, which Prisma Migrate could fail to clean up.

More improvements and bug fixes for Prisma Migrate

Prisma Migrate has now a built-in locking functionality to prevent multiple migrations from running concurrently.
Ensure the Prisma schema is valid before prompting developers to reset the database.
Better error message when using migrate dev - if a non-interactive environment is detected, you'll be suggested to use prisma migrate deploy instead.
Improved error handling when Prisma Migrate finds empty migration directories, e.g. prisma/migrations/20210119114009_init (missing migration.sql file).
In some occasions, when dealing with invalid schemas, e.g., duplicate constraint names, a panic in the Migration Engine would be triggered. These errors are now surfaced as validation errors instead.
In certain cases, when dealing with UUID columns, Prisma Migrate would drop and re-create the columns every time a migration was generated. This has now been fixed.

Improvements and changes for `prisma db push`

prisma db push now handles unexecutable migrations better, offering a path forward by resetting the database. For example, adding a new required field without a default value when there are rows in the table is considered an unexecutable migration; in such situations you will be prompted to first reset the database.
Changes to command options:
- The flag —-force has been renamed to --accept-data-loss to be more explicit - this is required for certain changes that involve losing data, e.g. dropping a table or dropping a column if there are rows.
- We've added a new flag —-force-reset which first resets the database and then updates the schema - this can be useful to start from scratch and as a way to deal with unexecutable migrations (see above).

`prisma db seed` now supports custom schema locations

You can now point the prisma db seed command to a custom schema location using either of two approaches:

Use the --schema option when running the command
Define a default schema location in your package.json which will be picked up every time you run the command.

Improvements and bug fixes in Prisma Client

Transaction rollback fix: We fixed an issue where if there was an error within the Prisma Client's runtime validation, the transaction wouldn't rollback. Learn more in this issue.
SQL Server server_name fix: Before we couldn't connect to certain kind of SQL Server instances. If the server was a managed instance from Azure, connecting to it with Prisma would return Server name cannot be determined. Additionally, when running a shared Azure SQL database, if the firewall setting was set to redirect (the default setting), our connection would first fail with advising the user to connect to a new server, and when changing the connection string regarding the error, the new connection would fail with the same error Server name cannot be determined. This is now fixed. Azure managed instances just work, as do redirections (which are done under the hood, automatically).
Native type fix for SQL Server: Our native type validations limits were set too low. We'd consider the maximum of 2000 for NVarChar/NChar length and a maximum of 4000 for VarChar/Char/VarBinary/Binary length. The actual maximums are 4000 for first and 8000 for the latter types.
PostgreSQL numeric type fix: In certain cases, when using executeRaw to insert number values to a numeric type in PostgreSQL, sometimes the stored value would be zero instead of the user input. An example value of 12345.0 is converted by JavaScript as an integer of 12345, which then is written as an integer to the table. The table column being numeric, the integer representation would always be zero in these cases. Now we should be able to convert integers to numeric without any trouble.

Bug fixes in Prisma Studio

Studio can now display fields that are of type Byte and BigInt.
Usage of the createMany preview feature doesn't crash Studio any more

Breaking changes

Type mapping from Prisma schema to the database for `Float` has changed from `Decimal` to `Double` in MySQL and PostgreSQL

Overview

If you use the Float scalar type in your Prisma schema and used Prisma Migrate to create the database schema in previous Prisam versions, the corresponding database column has the type DECIMAL(65,30).

For example, given the following Prisma schema:

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  reward    Float // Previously mapped to DECIMAL(65,30). From 2.17.0 will map to Double
  published Boolean @default(false)
}

Previous version of Prisma Migrate would generate the following migration:

-- CreateTable
CREATE TABLE "Post" (
    "id" SERIAL NOT NULL,
    "title" TEXT NOT NULL,
    "content" TEXT,
    "reward" DECIMAL(65,30) NOT NULL, // 
    "published" BOOLEAN NOT NULL DEFAULT false,
    PRIMARY KEY ("id")
);

As of 2.17.0, the remapping of the Float type from Decimal(65,30) to Double will cause Migrate to attempt to alter the database type of the reward column to Double the next time you create a migration.

What does this mean for users?

Nothing changes in Prisma Client until the next time you want to make a change to your schema. In that case, you'll need to decide if you want to keep using the Decimal(65,30) type in the database:

If you want to continue using Decimal(65,30), you need to change the type in the Prisma schema from Float to Decimal. Alternatively, you can also run prisma introspect which will automatically remap the previous Float fields to Decimal. Note that this will also change the type that Prisma Client returns from Number to Decimal.js.
If you would like to change the column's type in the database from Decimal(65,30) to Double, leave the Prisma schema as is and create a new migration. Prisma Migrate will alter the column's type to Double. Note that if you have rows with data for the column, they will be cast from Decimal(65,30) to Double.

Check out this video guide, which covers how to upgrade and address the remapping of Float.

Breaking changes due to strict type diffing and native types

Overview

Prisma has default mappings between each scalar type in the Prisma schema to the underlying database type. For example, the String scalar type in Prisma schema is mapped to a TEXT column on PostgreSQL by default.

Before this release, Prisma supported using a range of database column types for a given Prisma scalar. For example, define a field in Prisma schema as String and use VARCHAR(50) as the column type in the database using the @db.varchar(50) type annotation .

With the introduction of native types in General Availability, you can now specify your desired database type for columns in the Prisma schema via the @db.DB_TYPE field attributes, e.g., @db.varchar(50).

Because the @db.DB_TYPE attribute now exists, Prisma no longer allows the loose mapping of Prisma scalar types to database column types without the specific notation. The only exception to this rule is when you want to use default mapping, e.g., the String Prisma scalar will map to TEXT on PostgreSQL.

Before

Given the following table in PostgreSQL:

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "nickName" VARCHAR(50),
    "name" TEXT NOT NULL,

    PRIMARY KEY ("id")
);

Prisma would introspect the table as follows:

model User {
  id       Int      @id @default(autoincrement())
  nickName String?  //defaults to TEXT on PostgreSQL but works with varchar
  name     String   //defaults to TEXT on PostgreSQL but works with varchar
}

After

Because VARCHAR(50) can be expressed in native type notation. The matching Prisma schema for the User database table above on PostgreSQL is the following:

// Example for PostgreSQL

model User {
  id       Int      @id @default(autoincrement())
  nickName String?  @db.VarChar(50) // Prisma expects the column to be varchar and Prisma Migrate will change it if not
  name     String   // Prisma expects the column to be of type TEXT (default for String) and Prisma Migrate will change it if not
}

What does this mean for users?

Moving forward, if you want specific database column types, which are supported by Prisma, you should make sure to use the native type notation for the corresponding fields in the Prisma schema.

For users of Prisma Migrate with existing databases, you must understand that Prisma Migrate will try to migrate every column of a type different than what's defined in the schema.

If we go back to the previous example with loose type mapping, with this Prisma schema:

model User {
  id       Int      @id @default(autoincrement())
  nickName String?  //defaults to TEXT on PostgreSQL but works with varchar
  name     String   //defaults to TEXT on PostgreSQL but works with varchar
}

and this initial database schema:

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "nickName" VARCHAR(50),
    "name" TEXT NOT NULL,

    PRIMARY KEY ("id")
);

On PostgreSQL, from this release on, Prisma will the columns for the fields nickName and name to be of type TEXT and will generate a migration to alter the type of the nickName column:

-- AlterTable
ALTER TABLE "User" ALTER COLUMN "nickName" SET DATA TYPE TEXT;

To avoid unnecessary migrations to change types you may have defined on purpose, you can run introspection once, which will add the native annotations to any fields when they do not match the default mappings by Prisma.

For the initial database schema we used in the example

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "nickName" VARCHAR(50),
    "name" TEXT NOT NULL,

    PRIMARY KEY ("id")
);

This would be the resulting Prisma schema after running prisma introspect

model User {
  id       Int      @id @default(autoincrement())
  nickName String?  @db.VarChar(50)
  name     String   
}

Fixes and improvements

Prisma Client

Prisma Migrate

Language tools

Prisma Studio

Prisma Engines

Check out the official Prisma roadmap

You can find all the features that are currently planned and in progress on our roadmap.

Credits

Huge thanks to @safinsingh for helping!

@prisma/client 2.17.0 on Node.js NPM

Overview

Major improvements & new features

Native types are now stable

Rich column type mapping for Prisma types

Extending Prisma schema beyond supported column types

Prisma Migrate now works with cloud-hosted databases (e.g. Heroku)

Soft resets for cloud-hosted environments

More improvements and bug fixes for Prisma Migrate

Improvements and changes for prisma db push

prisma db seed now supports custom schema locations

Improvements and bug fixes in Prisma Client

Bug fixes in Prisma Studio

Breaking changes

Type mapping from Prisma schema to the database for Float has changed from Decimal to Double in MySQL and PostgreSQL

Overview

What does this mean for users?

Breaking changes due to strict type diffing and native types

Overview

Before

After

What does this mean for users?

Fixes and improvements

Prisma Client

Prisma Migrate

Language tools

Prisma Studio

Prisma Engines

Check out the official Prisma roadmap

Credits

@prisma/client 2.17.0
on Node.js NPM

Improvements and changes for `prisma db push`

`prisma db seed` now supports custom schema locations

Type mapping from Prisma schema to the database for `Float` has changed from `Decimal` to `Double` in MySQL and PostgreSQL