@prisma/client

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

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

Major improvements & new features

JSON Filtering is now in Preview

Starting today, you can now filter rows by data inside a Json type. JSON filtering support is available in PostgreSQL and MySQL. You can try it today by adding the filterJson preview flag to your generator block. This has been one of our most popular feature requests, so we're very excited to be getting it into your hands!

To get an idea of how JSON filtering works, let's see how you might query application logs stored in your database. Given the following Prisma schema:

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

model Log {
  id      Int    @id @default(autoincrement())
  level   Level
  message String
  meta    Json
}

enum Level {
  INFO
  WARN
  ERROR
}

And the following records in your PostgreSQL database:

id	level	message	meta
2	INFO	application listening on port 3000	{"host": "bob"}
3	INFO	upgrading account	{"host": "alice", "request_id": 10}
4	INFO	charging customer	{"host": "alice", "amount": 20, "request_id": 10}
5	ERROR	credit card expired	{"host": "alice", "amount": 20, "request_id": 10}
6	INFO	signing up	{"host": "bob", "request_id": 1}
7	INFO	application listening on port 3000	{"host": "alice"}
8	INFO	signed up	{"host": "bob", "email": "[email protected]", "request_id": 1}

We can now filter logs by the data inside the meta field. Let's query by the request_id inside the meta field to track the journey that led up to the error.

We can write the following query:

const logs = await prisma.log.findMany({
  where: {
    meta: {
      // path looks for the request_id key inside meta
      path: ["request_id"],
      // and we select rows whose request_id is 10
      equals: 10,
    },
  },
  orderBy: {
    id: "asc",
  },
});

Giving us the entire journey of this person's request:

[
  {
    id: 3,
    level: 'INFO',
    message: 'upgrading account',
    meta: { host: 'alice', request_id: 10 }
  },
  {
    id: 4,
    level: 'INFO',
    message: 'charging customer',
    meta: { host: 'alice', amount: 20, request_id: 10 }
  },
  {
    id: 5,
    level: 'ERROR',
    message: 'credit card expired',
    meta: { host: 'alice', amount: 20, request_id: 10 }
  }
]

Please note that the path syntax varies depending on the database. We pass the path query directly to the database, so there will be syntactical differences.

For example, querying by key in Postgres is request_id, while in MySQL it would be $.request_id. Please consult your database's documentation to learn more.

If you run into any questions or have any feedback, we're available in this issue.

📚 Documentation: Working with Json fields

Improvement for prisma db seed

In previous versions, a seed file could only be executed as a script. prisma db seed was simply executing the script by either calling node ./prisma/seed.js for JavaScript or ts-node ./prisma/seed.ts for TypeScript.

Now, you can directly export a function that Prisma executes on your behalf. Here's the rules that describe the new behavior: Prisma checks if the seed file has ...

• ... an exported function named seed and executes it
• ... an exported function (via a default export) and executes it

If there's no function exported as seed or via a default export, the behaviour of prisma db seed is exactly the same as before, meaning it will simply be executed as a script file.

Breaking changes

Options in groupBy queries are now prefixed with an underscore

The options in groupBy are now prefixed with an underscore, for example:

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

Here's an overview of the exact changes of each option:

Before 2.23.0	2.23.0 and later
count	_count
max	_max
min	_min
avg	_avg
sum	_sum

Note that this also changes the names of the fields in the objects that are returned by Prisma Client. For example, in the above case you now access the returned count value like so:

- console.log(result.count)
+ console.log(result._count)

We made this change to avoid clashes with user-defined fields. You can learn more about the problem in these issues.

We're sorry for the inconvenience! If you have any questions or need any help, please reach out in this discussion.

Deprecations

Deprecating options in aggregate queries

With the changes to groupBy discussed above and recent features like orderBy an aggregate, we took this opportunity to unify min, max, avg, sum and count keywords across Prisma Client queries.

  const result = await prisma.user.aggregate({
-   avg: {
+   _avg: {
      age: true,
    },
  })

- result.avg
+ result._avg

Similar to groupBy, this is the case for all of our aggregations:

Before 2.23.0	2.23.0 and later
count	_count
max	_max
min	_min
avg	_avg
sum	_sum

This is not a breaking change. The aggregate queries you wrote prior to 2.23.0 will continue to work as expected. We ask that you make adjustments when you can to future-proof your application.

If you have any questions or need any help, please reach out in this discussion.

Fixes and improvements

Prisma Migrate

• Changing foreign key column from optional to required causes migration error
• Failed Migration Log file output moved to ~/prisma folder
• prisma db push creates new prisma client inside of ./node_modules/@prisma/client
• Do not force seed to be script
• Prisma migrate dev prompts for migration name when running after --create-only (2.19)
• "Name of migration" input unclear when migrations exist
• Error: Error in migration engine. Reason: [C:\Users\runneradmin.cargo\git\checkouts\quaint-9f01e008b9a89c14\8196f13\src\connector\result_set\result_row.rs:59:64] index out of bounds: the len is 1 but the index is 10
• Error: [libs/sql-schema-describer/src/sqlite.rs:452:76] get name
• Prisma migrate repeatedly generates default value for dbgenerated("gen_random_uuid()::TEXT")
• Error: [libs/datamodel/core/src/transform/ast_to_dml/lift.rs:387:73] called Option::unwrap() on a None value
• Error: [libs/datamodel/connectors/dml/src/model.rs:161:64] Could not find relation field nodes on model nodes.
• Indexing error when run migration again
• Improve diagnostics for timeouts in Migrate
• Prisma Migrate with SQL Server errors with Error: Invalid data source URL, see https://www.prisma.io/docs/reference/database-reference/connection-urls in 2.22.0

Prisma Client

• Prisma Client hangs and eventually panics when there is no MongoDB server to connect to
• Where filter not working with JSON.not with another condition
• Ordering By Relation count incorrectly orders 0 count
• Incorrect AggregateOutputType DMMF definition of array fields
• [Feature Request] Ability to use RETURNING in SQLite - Bump SQLite Version up to 3.35.0
• PANIC in query-engine/core/src/query_graph_builder/read/aggregations/mod.rs:24:18Expected at least oe selection for aggregate

Prisma Studio

• Prisma studio showed no data
• Fatal Error
• Prisma Studio not showing data from database upon launch
• Prisma Studio not working with Safari 14.0.3
• npx prisma studio fails to start up on the browser
• Refreshing prisma studio is stuck on loading screen
• Prisma studio not showing the data from the db

Prisma Engines

• Testdb setup ignores Migrate errors
• Found bug in migration checksum

Credits

Huge thanks to @Sytten, @schiller-manuel, @mongolyy, @paularah, @Iamshankhadeep, @meeq for helping!

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

Learn about the latest release and other news from the Prisma community by joining us for another "What's new in Prisma" livestream.

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

🌎 Prisma Day is coming

Save the date for Prisma Day 2021 and join us for two days of talks and workshops by the most exciting members of the Prisma community.

• June 29th: Workshops
• June 30th: Talks (Submit a talk proposal)

We look forward to seeing you there!