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

As previously announced, Prisma has adopted SemVer strictly and this is the first major release which means it has some breaking changes.

For all the breaking changes, there are guides and documentation to assist you with the upgrade.

This release promotes many Preview features to General Availability. This means that they are ready for production use and have passed rigorous testing both internally and by the community.

We recommend that you read through the breaking changes below carefully and make sure that you've correctly upgraded your application.

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

Major improvements

Today's release is packed with many new features that are now Generally Available!

Here's a summary:

• Referential Actions
• Named Constraints
• Microsoft SQL Server and Azure SQL Connector
• Seeding with prisma db seed has been revamped
• Node-API
• Order by Aggregate in Group By
• Order by Relation
• Select Relation Count

Read along to dive deeper into all the new Generally Available improvements.

To read more about what Generally Available means, check out the maturity levels in the Prisma docs.

Referential Actions is now Generally Available

Referential Actions is a feature that allows you to control how relations are handled when an entity with relations is changed or deleted. Typically this is done when defining the database schema using SQL.

Referential Actions allows you to define this behavior from the Prisma schema by passing in the onDelete and onUpdate arguments to the @relation attribute.

For example:

model LitterBox {
  id   Int     @id @default(autoincrement())
  cats Cat[]
  full Boolean @default(false)
}

model Cat {
  id    String    @id @default(uuid())
  boxId Int
  box   LitterBox @relation(fields: [boxId], references: [id], onDelete: Restrict)
}

Here, you would not be able to delete a LitterBox as long as there still is a Cat linked to it in your database, because of the onDelete: Restrict annotation. If we had written onDelete: Cascade, deleting a LitterBox would also automatically delete the Cats linked to it.

Referential Actions was first released in in 2.26.0 with the referentialActions Preview flag. Since then, we've worked to stabilize the feature.

Today, we're delighted to announce that Referential Actions is now General Available, meaning it is enabled by default. 🐱

On PostgreSQL, MySQL, SQLite and Microsoft SQL Server, referential actions will be enforced by the database.

If you use Prisma Migrate to manage your database schema, you can use introspection with prisma db pull to automatically fill in the existing referential actions from your database. Please read the upgrade guide for all the details.

🚨 Please note that the defaults change in this release. In some cases, this can lead to queries behaving differently in Prisma 3.x compared to Prisma 2.x, since the database will be enforcing referential actions, and not Prisma Client anymore. This means that if you only update your client without changing your Prisma schema, you will lose Prisma-level protections against cascading deletes. Read more in the Breaking Changes section below and the upgrade guide for a detailed explanation and steps to follow. 🚨

Named Constraints is now Generally Available

One of the core concepts in Prisma is the Prisma schema which serves as a single source of truth for your database schema and application models. For Prisma to automatically generate migrations and Prisma Client, it's necessary for the Prisma schema to represent the database schema as accurately as possible.

Named Constraints allows the Prisma schema to more accurately represent the names of all currently supported constraints such as unique constraints, primary keys, etc.

By having this representation in the Prisma schema, you now have finer control over two things:

• The names of constraints in the database schema which are generated by Prisma Migrate and the prisma db push command.
• The name mapping for the constraint in Prisma Client for findUnique queries on compound indices.

Named Constraints was initially released in Preview in 2.29.0. Since then, we've made minor fixes and improvements to ease the upgrade experience.

Today, we're happy to launch Named Constraints to General Availability, meaning it will be enabled by default.

Named Constraints are about reflecting another aspect of the database in your Prisma schema: many objects in your schema, like indexes, unique constraints, and foreign keys, have a name in the database. That was not properly reflected in the Prisma Schema Language in earlier versions. The addition of Named Constraints fills this gap and improves overall correctness.

Given the following example:

model Post {
  id      Int    @id(map: "pk_of_Post")
  blog_id Int
  blog    Blog   @relation(fields: [blog_id], references: [id], map: "Post_blog_fk")
  title   String

  @@unique([title, blog_id], name: "Post_title_blog_id_unique", map: "Post.title_blog_id_unique")
}

model Blog {
  id    Int    @id
  posts Post[]
}

You can see that @id, @relation and @@unique now can take a map argument corresponding to the database name of the primary key/foreign key/constraint.

@@unique can also take a name argument to control the naming of the WhereUnique argument in Prisma Client.

A quick way to remember: The new API is uniform. map: "..." always corresponds to a name in the database, whereas name: "..." always corresponds to names in the Prisma Client API.

🚨 Please note that you will have to make conscious decisions about constraint names when you upgrade to Prisma 3. Prisma will help you with the upgrade process using introspection with prisma db pull and Prisma Migrate. Read more in the Breaking Changes section below and the upgrade guide for a detailed explanation and steps to follow. 🚨

Microsoft SQL Server and Azure SQL Connector is now Generally Available

Today we are excited to announce that Prisma support for Microsoft SQL Server and Azure SQL is Generally Available and ready for production!

Since we released Prisma Client for General Availability over a year ago with support for PostgreSQL, MySQL, SQLite, and MariaDB, we've heard from thousands of engineers about how the Prisma ORM is helping them be more productive and confident when building data-intensive applications.

After passing rigorous testing internally and by the community over the last year since the Preview release in version 2.10.0, we are thrilled to bring Prisma's streamlined developer experience and type safety to developers using Microsoft SQL Server and Azure SQL in General Availability 🚀.

Learn more in the release blog post 🤓

Seeding with prisma db seed has been revamped and is now Generally Available

When developing locally, it's common to seed your database with initial data to test functionality. In version 2.15 of Prisma, we initially introduced a Preview version of seeding using the prisma db seed command.

Today, we're excited to share that the prisma db seed command has been revamped and simplified with a better developer experience and is now Generally Available.

The seeding functionality is now just a hook for any command defined in "prisma"."seed" in your package.json.

For example, here's how you would define a TypeScript seed script with ts-node:

1. Open the package.json of your project
2. Add the following example to it:

// package.json
"prisma": {
  "seed": "ts-node prisma/seed.ts"
}

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

async function main() {
  const alice = await prisma.user.upsert({
    where: { email: 'alice@prisma.io' },
    update: {},
    create: {
      email: 'alice@prisma.io',
      name: 'Alice',
    },
  })

  console.log({ alice })
}

main()
  .catch((e) => {
    console.error(e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

This approach gives you more flexibility and makes fewer assumptions about how you choose to seed. You can define a seed script in any language as long as you it's just a terminal command.

For example, here's how you would seed using an SQL script and the psql CLI tool.

// package.json
"prisma": {
  "seed": "psql --dbname=mydb --file=./prisma/seed.sql"
}

🚨 Please note that if you already have a seed script that worked created in versions prior, you will need to add the script to prisma.seed in your package.json and adapt the script to the new API. Read more in the Breaking Changes section below and the seeding docs for a complete explanation and walkthroughs of common use cases.

Node-API is Generally Available

Node-API is a new technique for binding Prisma's Rust-based query engine directly to Prisma Client. This reduces the communication overhead between the Node.js and Rust layers when resolving Prisma Client's database queries.

Earlier versions of Prisma (since version 2.0.0) used the Prisma Query Engine binary, which runs as a sidecar process alongside your application and handles the heavy lifting of executing queries from Prisma Client against your database.

In 2.20.0 we introduced a Preview feature, the Node-API library, as a more efficient way to communicate with the Prisma Engine binary. Using the Node-API library is functionally identical to running the Prisma engine binary while reducing the runtime overhead by making direct binary calls from Node.js.

Starting with today's 3.0.1 release we are making the Node-API library engine the default query engine type. If necessary for your project, you can fall back to the previous behavior of a sidecar Prisma Engine binary, however, we don't anticipate a reason to do so.

If you've been using this preview feature, you can remove the nApi flag from previewFeatures in your Prisma Schema.

Learn more about the Query Engine in our documentation.

Order by Aggregate in Group By is Generally Available

Let's say you want to group your users by the city they live in and then order the results by the cities with the most users. Order by Aggregate Group allows you to do that, for example:

await prisma.user.groupBy({
  by: ['city'],
  _count: {
    city: true,
  },
  orderBy: {
    _count: {
      city: 'desc',
    },
  },
}),

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  city  String
  name  String?
  posts Post[]
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User?    @relation(fields: [authorId], references: [id])
  authorId  Int?
}

Order by Aggregate Group was initially released as a Preview feature in 2.21.0.

Starting with today's release it is Generally Available 🤩

If you've been using this Preview feature, you can remove the orderByAggregateGroup flag from previewFeatures in your Prisma Schema.

Learn more about this feature in our documentation.

Order by Relation is Generally Available

Ever wondered how you can query posts and have the results ordered by their author's name?

With Order by Relations, you can do this with the following query:

await prisma.post.findMany({
  orderBy: {
    author: {
      name: 'asc',
    },
  },
  include: {
    author: true,
  },
})

```tsx
model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  city  String
  name  String?
  posts Post[]
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User?    @relation(fields: [authorId], references: [id])
  authorId  Int?
}
```

Order by Relation was initially released in Preview in 2.16.0.

Starting with today's 3.0.1 release it is Generally Available 🧙

If you've been using this preview feature, you can remove the orderByRelation flag from previewFeatures in your Prisma Schema.

Learn more about this feature in our documentation.

Select Relation Count is Generally Available

Select Relation Count allows you to count the number of related records by passing _count to the select or include options and then specifying which relation counts should be included in the resulting objects via another select.

Select Relation Count helps you query counts on related models, for example, counting the number of posts per user:

const users = await prisma.user.findMany({
  include: {
    _count: {
      select: { posts: true },
    },
  },
})

If you've been using this Preview feature, you can remove the selectRelationCount flag from previewFeatures in your Prisma Schema.

Learn more about this feature in our documentation.

Breaking Changes

Some of the features above introduce breaking changes. In this section, we cover the breaking changes in more detail with links to upgrade guides in the documentation. We recommend that you read through the breaking changes carefully and make sure that you've upgraded and tested your application.

Named Constraints

Starting with Prisma 3, the names of database constraints and indexes are reflected in the Prisma schema. This means that Introspection with db pull as well as migrate and db push will work towards keeping your constraint and index names in sync between your schema and your database.

Additionally, a new convention for default constraint names is now built into the Prisma Schema Language logic. This ensures reasonable, consistent defaults for new greenfield projects. The new defaults are more consistent and friendlier to code generation. It also means that if you have an existing schema and/or database, you will either need to migrate the database to the new defaults, or introspect the existing names.

⚠️ This means you will have to make conscious choices about constraint names when you upgrade. Please read the Named Constraints upgrade guide for a detailed explanation and steps to follow. ⚠️

Referential Actions

The default referential actions behaviors when you update or delete data changes between Prisma 2 and Prisma 3. This will lead to queries behaving differently in Prisma 3.x compared to Prisma 2.x.

In some cases, a delete operation will delete more data than previously. This is ****because in Prisma 2, cascading deletes and updates defined at the database level were prevented by Prisma Client, but will be allowed to happen in Prisma 3.

On relational databases, Prisma now relies on the database for referential actions — using db pull after you upgrade will reflect the actual behavior in your Prisma Schema.

Please read the Referential Actions upgrade guide for a detailed explanation and steps to follow.

Seeding with prisma db seed , prisma migrate dev, prisma migrate reset

The mechanism through which seeds are discovered changes. If you were already using seeding since Prisma 2.15.0, you will see a warning with steps to follow the next time seeding is triggered.

In summary, you will need to:

• Add the script to prisma.seed in your package.json
• Adapt the script to the new API.

See the example above in the Major Improvements section and read the seeding docs for a complete explanation.

⚠️ Please note that the breaking change applies if you have an existing seed file since Prisma 2.15.0 without using prisma db seed. You will have to switch to the new mechanism. ⚠️

$queryRaw API changes

We’ve taken this opportunity to cleanup $queryRaw and $executeRaw. In Prisma 2.x, the Prisma Client had different behavior depending on how you called $queryRaw or $executeRaw:

1. Tagged Template: prisma.$queryRaw...``. This API sent a prepared statement and was safe from SQL injections.
2. Function Call: prisma.$queryRaw(...). This API sent a raw query string and was not safe from SQL injections.

This was too subtle. Starting with Prisma 3, we've split our raw query APIs into "safe" and "unsafe":

1. $queryRaw...``: Safe from SQL injections. Sends a prepared statement and returns the resulting rows.
2. $executeRaw...``: Safe from SQL injections. Sends a prepared statement and returns the result count.
3. $queryRawUnsafe(...): Not safe from SQL injections. Sends the query as a string and returns the resulting rows. Useful for queries that can't be prepared, like using a variable for the table name.
4. $executeRawUnsafe(...): Not safe from SQL injections. Sends the query as a string and returns the result count. Useful for queries that can't be prepared, like altering a column's data type.

To update your application, you can do a "Find All" in your codebase for $queryRaw( and $executeRaw(. Then you can either turn them into tagged templates or use the unsafe functions.

Changes to how you query Null values on JSON fields

While Filtering on a Json field was in Preview, we learned from a community member that you couldn't filter a Json field by the JSON value null.

This is because { equals: null } checks if the value in the database is NULL, not if the value inside the column is a JSON null.

To fix this problem, we decided to split "null" on Json fields into JsonNull, DbNull and AnyNull:

• JsonNull: Selects the null value in JSON.
• DbNull: Selects the NULL value in the database.
• AnyNull: Selects both null JSON values and NULL database values.

Given the following model in your Prisma Schema:

model Log {
  id   Int  @id
  meta Json
}

Starting in 3.0.1, you'll see a TypeError if you try to filter by null on a Json field:

prisma.log.findMany({
  where: {
    data: {
      meta: {
        equals: null
         //      ^ TypeError: Type 'null' is not assignable to type
        }
    },
  },
});

To fix this, you'll import and use one of the new null types:

import { Prisma } from '@prisma/client'

prisma.log.findMany({
  where: {
    data: {
      meta: {
        equals: Prisma.AnyNull,
      },
    },
  },
})

This also applies to create, update and upsert. To insert a null value into a Json field, you would write:

import { Prisma } from '@prisma/client'

prisma.log.create({
  data: {
    meta: Prisma.JsonNull,
  },
})

And to insert a database NULL into a Json field, you would write:

import { Prisma } from '@prisma/client'

prisma.log.create({
  data: {
    meta: Prisma.DbNull,
  },
})

Learn more about JSON filtering in our documentation.

Renamed Aggregate Fields

In 2.23.0, we announced that aggregate fields like count will be deprecated in favor of the prefixed notation, i.e. _count in order to avoid clashing with model fields names in your application.

For example:

const result = await prisma.user.groupBy({
  by: ['name'],
-   count: true,
+   _count: true,
})

In this release, we're removing the deprecated old notation. You now must prefix your aggregate fields with an underscore.

To help with this transition, we suggest searching your codebase for .aggregate({ and .groupBy({, and prefixing count, max, min, avg and sum are all prefixed with an underscore, i.e. _count, _max, _min, _avg , and _sum.

You can learn more about the original deprecation in the 2.23.0 Release Notes.

The minimum required version of Node.js is v12.6

Up until this release, Prisma supported versions 12.2 of Node.js and up

Starting with this release, the minimum required version of Node.js is 12.6.

Fixes and improvements

Prisma Migrate

• Remove microsoftSqlServer preview flag
• db seed tests need to run on multiple platforms, not just Ubuntu
• Validate referentialActions with Introspection and Migration CI
• Introspection adds an extra space to map properties in enums in prisma.schema
• Remove referentialActions preview flag
• [Introspection] @@unique name with spaces are not supported
• EPIC: re-design of db seed feature
• MySQL: Support SSL with system certificate store
• Enable TLS by default on SQL Server
• Make order of introspected foreign keys stable

Prisma Client

• Prisma Client Node-API Support
• [BREAKING CHANGE] Disconnect/connect (for relations) throws errors needlessly
• Error classes are wrapped twice
• $queryRaw availability isn't derived from the DMMF
• Can't filter by null values on JSONB fields
• Remove preview flag for orderByRelations
• Remove preview flag for orderByAggregateGroup
• Remove preview flag for selectRelationCount
• Make Node-API library default engine
• Add runtime check to $queryRaw("...") to throw for JS calls
• Write a codemod to rename $queryRaw(...) to $queryRawUnsafe(...)
• Using invalid connection string parameters leads to suboptimal error message with Node-API library
• Bump Node.js requirement from to >=12.2 to >=12.6
• Running a statement results in different prepared statements
• A NOT within a NOT creates a SQL query with only a single NOT in 2.30.0
• Remove non-underscored fields (e.g. count should be _count)

Prisma

• Json filters array_starts_with and array_ends_with don't work on MariaDB
• SQL Server cycle detection doesn't detect multiple paths correctly
• Adapt CLI update notifier for major upgrade

Language tools (e.g. VS Code)

• Node-API: engineType autocompletion support

Prisma Studio

• npx prisma studio throws spawn undefined\System32\WindowsPowerShell\v1.0\powershell ENOENT error on windows

Credits

Huge thanks to @saintmalik, @benkenawell, @ShubhankarKG, @hehex9 for helping!

📺 Join us for another "What's new in Prisma" livestream

Learn about the latest 3.0.1 release and other news from the Prisma community by joining Matt Muller and Daniel Norman from the Prisma team for another "What's new in Prisma" livestream.

The stream takes place on Youtube on Thursday, September 9 at 5pm Berlin | 8am San Francisco.