Today, we are excited to share the `3.0.1` stable release 🎉
[As previously announced](https://www.prisma.io/blog/prisma-adopts-semver-strictly), 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](https://twitter.com/intent/tweet?text=Check%20out%20the%20latest%20Prisma%203.0.1%20Release!%20%F0%9F%9A%80&url=https%3A%2F%2Fgithub.com%2Fprisma%2Fprisma%2Freleases%2F3.0.1) 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](https://www.prisma.io/docs/about/prisma/releases#generally-available-ga) 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:
prisma
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 `Cat`s linked to it.
Referential Actions was first released in in [2.26.0](https://github.com/prisma/prisma/releases/tag/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](https://www.prisma.io/docs/concepts/components/prisma-schema/relations/referential-actions#upgrade-paths) 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](https://www.prisma.io/docs/concepts/components/prisma-schema/relations/referential-actions#upgrade-paths) 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](https://github.com/prisma/prisma/releases/tag/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](https://www.prisma.io/docs/concepts/components/prisma-schema/names-in-underlying-database/) 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:
prisma
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](https://prisma.io/docs/guides/upgrade-guides/upgrading-versions/upgrading-to-prisma-3/named-constraints) 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](https://github.com/prisma/prisma/releases/tag/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](https://www.prisma.io/blog/prisma-microsoft-sql-server-azure-sql-production-ga) 🤓
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](https://github.com/prisma/prisma/releases/tag/2.15.0) 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:
json
// package.json
"prisma": {
"seed": "ts-node prisma/seed.ts"
}
<details><summary>Expand to view an example seed script</summary>
ts
import { PrismaClient } from 'prisma/client'
const prisma = new PrismaClient()
async function main() {
const alice = await prisma.user.upsert({
where: { email: 'aliceprisma.io' },
update: {},
create: {
email: 'aliceprisma.io',
name: 'Alice',
},
})
console.log({ alice })
}
main()
.catch((e) => {
console.error(e)
process.exit(1)
})
.finally(async () => {
await prisma.$disconnect()
})
</details>
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.
json
// 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](https://www.prisma.io/docs/guides/database/seed-database) 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](https://github.com/prisma/prisma/releases/tag/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](http://prisma.io/docs/concepts/components/prisma-engines/query-engine#configuring-the-query-engine) 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](http://prisma.io/docs/concepts/components/prisma-engines/query-engine#configuring-the-query-engine).
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:
ts
await prisma.user.groupBy({
by: ['city'],
_count: {
city: true,
},
orderBy: {
_count: {
city: 'desc',
},
},
}),
<details><summary>Expand to view the underlying Prisma schema</summary>
prisma
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?
}
</details>
Order by Aggregate Group was initially released as a Preview feature in [2.21.0](https://github.com/prisma/prisma/releases/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](https://www.prisma.io/docs/concepts/components/prisma-client/aggregation-grouping-summarizing#order-by-aggregate-group-preview).
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:
ts
await prisma.post.findMany({
orderBy: {
author: {
name: 'asc',
},
},
include: {
author: true,
},
})
<details><summary>Expand to view the underlying Prisma schema</summary>
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?
}
</details>
Order by Relation was initially released in Preview in [2.16.0](https://github.com/prisma/prisma/releases/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](https://www.prisma.io/docs/concepts/components/prisma-client/filtering-and-sorting#sort-by-relation-preview).
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**:
ts
const users = await prisma.user.findMany({
include: {
_count: {
select: { posts: true },
},
},
})
<details><summary>Expand to view the structure of the returned `users`</summary>
ts
[
{
id: 2,
email: 'bobprisma.io',
city: 'London',
name: 'Bob',
_count: { posts: 2 },
},
{
id: 1,
email: 'aliceprisma.io',
city: 'Berlin',
name: 'Alice',
_count: { posts: 1 },
},
]
</details>
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](https://www.prisma.io/docs/concepts/components/prisma-client/aggregation-grouping-summarizing#count-relations).
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](https://www.prisma.io/docs/guides/upgrade-guides/upgrading-versions/upgrading-to-prisma-3/named-constraints) 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](https://www.prisma.io/docs/concepts/components/prisma-schema/relations/referential-actions#upgrade-paths) 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](https://www.prisma.io/docs/guides/database/seed-database) for a complete explanation.
⚠️ **Please note** that the breaking change applies if you have an existing seed file since Prisma [2.15.0](https://github.com/prisma/prisma/releases/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](https://www.prisma.io/docs/concepts/components/prisma-client/working-with-fields/working-with-json-fields#filtering-on-a-json-field) was in Preview, we learned from [a community member](https://github.com/prisma/prisma/issues/8399) 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:
prisma
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:
ts
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:
ts
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:
ts
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:
ts
import { Prisma } from 'prisma/client'
prisma.log.create({
data: {
meta: Prisma.DbNull,
},
})
Learn more about JSON filtering in [our documentation](https://www.prisma.io/docs/concepts/components/prisma-client/working-with-fields/working-with-json-fields#filtering-on-a-json-field).
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:
diff
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](https://github.com/prisma/prisma/releases/tag/2.23.0).
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](https://github.com/prisma/prisma/issues/4537)
- [`db seed` tests need to run on multiple platforms, not just Ubuntu](https://github.com/prisma/prisma/issues/8033)
- [Validate `referentialActions` with Introspection and Migration CI](https://github.com/prisma/prisma/issues/8176)
- [Introspection adds an extra space to map properties in enums in prisma.schema](https://github.com/prisma/prisma/issues/8479)
- [Remove `referentialActions` preview flag](https://github.com/prisma/prisma/issues/8633)
- [[Introspection] unique name with spaces are not supported](https://github.com/prisma/prisma/issues/8645)
- [EPIC: re-design of `db seed` feature](https://github.com/prisma/prisma/issues/8732)
- [MySQL: Support SSL with system certificate store](https://github.com/prisma/prisma/issues/8843)
- [Enable TLS by default on SQL Server](https://github.com/prisma/prisma/issues/8863)
- [Make order of introspected foreign keys stable](https://github.com/prisma/prisma/issues/8975)
Prisma Client
- [Prisma Client Node-API Support](https://github.com/prisma/prisma/issues/4911)
- [[BREAKING CHANGE] Disconnect/connect (for relations) throws errors needlessly](https://github.com/prisma/prisma/issues/5005)
- [Error classes are wrapped twice](https://github.com/prisma/prisma/issues/7035)
- [`$queryRaw` availability isn't derived from the DMMF](https://github.com/prisma/prisma/issues/8189)
- [Can't filter by `null` values on JSONB fields](https://github.com/prisma/prisma/issues/8399)
- [Remove preview flag for `orderByRelations`](https://github.com/prisma/prisma/issues/8629)
- [Remove preview flag for `orderByAggregateGroup`](https://github.com/prisma/prisma/issues/8630)
- [Remove preview flag for `selectRelationCount`](https://github.com/prisma/prisma/issues/8631)
- [Make Node-API library default engine](https://github.com/prisma/prisma/issues/8731)
- [Add runtime check to `$queryRaw("...")` to throw for JS calls](https://github.com/prisma/prisma/issues/8808)
- [Write a codemod to rename `$queryRaw(...)` to `$queryRawUnsafe(...)`](https://github.com/prisma/prisma/issues/8809)
- [Using invalid connection string parameters leads to suboptimal error message with Node-API library](https://github.com/prisma/prisma/issues/8836)
- [Bump Node.js requirement from to >=12.2 to >=12.6](https://github.com/prisma/prisma/issues/8839)
- [Running a statement results in different prepared statements](https://github.com/prisma/prisma/issues/8870)
- [A `NOT` within a `NOT` creates a SQL query with only a single `NOT` in 2.30.0](https://github.com/prisma/prisma/issues/8909)
- [Remove non-underscored fields (e.g. `count` should be `_count`)](https://github.com/prisma/prisma/issues/9029)
Prisma
- [Json filters `array_starts_with` and `array_ends_with` don't work on MariaDB](https://github.com/prisma/prisma/issues/8874)
- [SQL Server cycle detection doesn't detect multiple paths correctly](https://github.com/prisma/prisma/issues/8969)
- [Adapt CLI update notifier for major upgrade](https://github.com/prisma/prisma/issues/9061)
Language tools (e.g. VS Code)
- [Node-API: `engineType` autocompletion support](https://github.com/prisma/language-tools/issues/843)
Prisma Studio
- [`npx prisma studio` throws `spawn undefined\System32\WindowsPowerShell\v1.0\powershell ENOENT` error on windows](https://github.com/prisma/studio/issues/767)
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"](https://youtu.be/pJ6fs5wXnyM) livestream.
The stream takes place [on Youtube](https://www.youtube.com/watch?v=Dt9uEq1WVvQ) on **Thursday, September 9** at **5pm Berlin | 8am San Francisco**.