What's new in Prisma? (Q1/22)

Overview

• Releases & new features
  • MongoDB is now Generally Available
    • Embedded documents support
    • Introspection of embedded documents
    • Index support for composite type fields
    • Embedded document filters support
    • Ordering by embedded documents
    • Raw query support
    • Support for query logging
    • Filters no longer return undefined fields by default
    • New isSet filter operation
    • New unset operation
    • New updateMany operation
    • New deleteMany operation
    • Many-to-Many relations require a reference argument
    • @default(dbgenerated()) is now replaced with @default(auto())
    • @db.Array(ObjectId) is now updated to @db.ObjectId
  • Improved connection pooling resiliency
  • Prisma Client logger revamp
  • CockroachDB support is now in Preview
  • Prisma Migrate improvements
    • Trouble shooting migrations
    • Detecting state of a diff with migrate diff using an exit code
  • Full-text search for MySQL is now in Preview
  • dataProxy and interactiveTransactions Preview features are now mutually exclusive
  • Interactive transactions concurrency
• Community
  • Meetups
• Videos, livestreams & more
  • What's new in Prisma
  • Interviews
  • Written content
  • Prisma appearances
• We are hiring
• Stickers
• What's next?

Releases & new features

Our engineers have been working hard, issuing new releases with many improvements and new features. This quarter, we adjusted our release cadence to every three weeks. Here is an overview of the most exciting features we've launched in the last three months.

You can stay up-to-date about all upcoming features on our roadmap.

MongoDB is now Generally Available

This quarter was packed with lots of new features and improvements to the MongoDB connector, now Generally Available. MongoDB was launched in Preview in July 2021 we've been working hard towards pushing it towards stability and polishing it to make it production-ready.

Here are some of the feature highlights we developed over this period:

• Expressive and type-safe operations for querying MongoDB embedded documents
• Increased likelihood of referential integrity in your database
• Thorough introspection support for using Prisma with existing MongoDB databases
• Declarative index management right from your Prisma schema with prisma db push
• Powerful raw query APIs to help you incrementally migrate to Prisma

Embedded documents support

In 3.10.0 we introduced support for reading and modifying embedded documents. Embedded documents provide access to a new type keyword in your Prisma schema you can use to define composite types.

model Product {
  id     String  @id @default(auto()) @map("_id") @db.ObjectId
  name   String
  photos Photo[]
}

type Photo {
  height Int
  width  Int
  url    String
}

Given the schema above, you can now read and write to the embedded photos array:

// Create a new product with an embedded list of photos
const product = await prisma.product.create({
  data: {
    name: "Forest Runners",
    // Create an embedded list of photos in the product
    photos: [
      { height: 100, width: 200, url: "1.jpg" },
      { height: 300, width: 400, url: "2.jpg" },
    ],
  },
})

Introspection of embedded documents

In 3.4.0, we added preview support for the introspection of embedded documents. Running prisma db pull against your MongoDB database will generate type definitions within your Prisma schema.

When introspecting your database, you can switch off the introspection of composite types with --composite-type-depth=0 or limit the depth level of the analysis with, for example, --composite-type-depth=2.

We took this further and updated the type inference behavior for embedded documents with mixed data types on introspection. From version 3.11.0, Prisma now defaults to a Json type for all fields that have mixed data types.

Prisma will also show a warning on the console and add a comment to the introspected Prisma schema file to clarify where a field has a mixed data type.

Index support for composite type fields

In 3.12.0, we added support for adding indexes on embedded document fields in MongoDB. You can now define a normal, unique, or full-text index in your schema.

type Address {
  street String
  number Int
}

model User {
  id      Int     @id
  email   String
  address Address

  @@index([email, address.number])
  @@unique([email, address.street])
  @@fulltext([email, address.street])
}

Embedded document filters support

In 3.11.0, we added the ability to filter embedded documents.

Given the following schema:

model Product {
  id     String  @id @default(auto()) @map("_id") @db.ObjectId
  photos Photo[]
}

model Order {
  id              String   @id @default(auto()) @map("_id") @db.ObjectId
  shippingAddress Address
  billingAddress  Address?
}

type Photo {
  height Int
  width  Int
  url    String
}

type Address {
  street String
  city   String
  zip    String
}

You can now add filters within an embedded document:

// find all orders with the same shipping address
const orders = await prisma.order.findMany({
  where: {
    shippingAddress: {
      equals: {
        street: "555 Candy Cane Lane",
        city: "Wonderland",
        zip: "52337",
      },
    },
  },
})

You can also add a filter on a "contains many" relationship:

// find all products that don't have photos
const product = prisma.product.findMany({
  where: {
    photos: {
      isEmpty: true
    }
  },
})

This is just the tip of the iceberg of what's possible. To learn more, refer to our documentation for a complete list of available operations.

Ordering by embedded documents

In addition to embedded document filters, 3.11.0 added support for sorting by embedded documents.

Using the schema from the previous example, you can sort orders by their zip code:

// sort orders by zip code in ascending order
const orders = await prisma.order.findMany({
  orderBy: {
    shippingAddress: {
      zip: "asc",
    },
  },
})

Raw query support

Raw queries in MongoDB help in writing queries Prisma doesn't support yet, such as:

// To find zero or more documents matching a filter
const result = await prisma.user.findRaw({
  filter: { age: { $gt: 25 } },
  options: { projection: { _id: false } },
})

// To perform aggregation operations on a collection
await prisma.user.aggregateRaw({
  pipeline: [
    { $match: { status: 'registered' } },
    { $group: { _id: '$country', total: { $sum: 1 } } },
  ],
})

// To run a command against the database
await prisma.$runCommandRaw({
  aggregate: 'User',
  pipeline: [
    { $match: { name: 'Bob' } },
    { $project: { email: true, _id: false } },
  ],
  explain: false,
})

The raw query API for MongoDB differs from Prisma's $queryRaw SQL API to handle some low-level differences between databases to give you a better API and developer experience.

Support for query logging

You can enable query logging in Prisma Client when working with MongoDB from version 3.11.0.

const prisma = new PrismaClient({
  log: [
    {
      emit: 'event',
      level: 'query',
    },
  ]
})

prisma.$on('query', (e) => console.log(e.query))

Prisma Client logs queries in the same format as the mongosh console. You can use the queries from your logs directly into your shell.

Filters no longer return undefined fields by default

In 3.11.1, we've changed the data returned when filtering MongoDB documents on undefined fields. The new rule is that undefined fields are excluded by default unless explicitly filtered for. This allows you to query for undefined and null values separately.

For example, given the following Prisma schema:

model Address {
    id     Int    @id @map("_id")
    city   String
    street String? // Note that street is optional
}

For MongoDB, optional fields can either be null or undefined (absent). The following documents are all valid for the schema above:

{ "_id": 1, "city": "San Fransisco", "street": "Market st." }
{ "_id": 2, "city": "Seattle", "street": null }
{ "_id": 3, "city": "Chicago" }

Prior to 3.11.1, if you queried for where: { street: null }, you'd get _id: 2 and _id: 3. In 3.11.1, you'll only get _id: 2. The ability to also query for the missing fields has also been added. Refer to the new isSet below to learn more.

There are a few exceptions to this new default:

• A having filter on an aggregated field will return undefined fields. This is because aggregation on undefined fields yields null, not undefined, thus matching the filter.
• Filters on undefined to-many relations (e.g., the backing array of a many-to-many is undefined) will currently include those relations in the result set.

New isSet filter operation

To compensate for missing fields on documents no longer being returned by the filters above, we’ve added a new isSet: bool filter. This filter can be used to include fields that are undefined on documents.

The isSet operation has been added to all scalar and embedded fields that are optional.

Using the example above, to include the undefined fields, you can use an OR:

await prisma.address.findMany({
  where: {
    OR: [
      { street: { isSet: false } },
      { street: null }
    ]
  }
})

New unset operation

From 3.11.1, you can also remove a field with the unset operation.

Using the example above, let's write a query to remove the street field:

// set the `street` field to `undefined` in the database
await prisma.address.update({
  where: {
    id: 10,
  },
  data: {
    street: {
      unset: true,
    },
  },
})

New updateMany operation

Prisma now supports updating embedded documents that match specific criteria.

For example, given the following schema:

model Product {
  id          Int  @id @map("_id")
  name        String  @unique
  photos      Photo[]
}

type Photo {
  height Int    @default(200)
  width  Int    @default(100)
  url    String
}

You would then update photo with a url of 1.jpg to 2.png:

const product = prisma.product.update({
  where: {
    id: 10,
  },
  data: {
    photos: {
      updateMany: {
        where: {
          url: '1.jpg',
        },
        data: {
          url: '2.png',
        },
      },
    },
  },
})

New deleteMany operation

Similar to updateMany, you can also remove embedded documents that match specific criteria.

Using the Prisma schema from the previous example, you can delete all photos with a height of 100:

const product = prisma.product.update({
  where: {
    id: 10,
  },
  data: {
    photos: {
      deleteMany: {
        where: {
          height: 100,
        },
      },
    },
  },
})

Many-to-Many relations require a reference argument

From 3.10.0, Prisma now enforces all the arguments in a MongoDB many-to-many relation. A @relation attribute must define fields and references argument to both sides.

The fields argument must point to a scalar field in the same model and must be an array. The references argument must point to a scalar field in the opposite model, and it must be a singular type of the same base type as the referencing array on the other side.

model Post {
  id           String     @id @map("_id") @default(auto()) @db.ObjectId
  category_ids String[]   @db.ObjectId
- categories   Category[] @relation(fields: [category_ids])
+ categories   Category[] @relation(fields: [category_ids], references: [id])
}

model Category {
  id       String   @id @map("_id") @default(auto()) @db.ObjectId
  post_ids String[] @db.ObjectId
- posts    Post[]   @relation(fields: [post_ids])
+ posts    Post[]   @relation(fields: [post_ids], references: [id])
}

@default(dbgenerated()) is now replaced with @default(auto())

The original purpose of dbgenerated is to support SQL expressions Prisma doesn’t understand yet. However, MongoDB doesn’t have a concept of default value expressions like SQL does. We took this opportunity to simplify how we handle the default values in MongoDB:

model Post {
-  id String @id @default(dbgenerated()) @map("_id") @db.ObjectId
+  id String @id @default(auto()) @map("_id") @db.ObjectId
}

@db.Array(ObjectId) is now updated to @db.ObjectId

We've adjusted the Prisma schema format for scalar lists with native types (like lists of Object IDs). This will likely affect those with many-to-many relationships in MongoDB. We made this change to align MongoDB with our existing SQL databases better:

model Post {
  id          String     @id @default(auto()) @map("_id") @db.ObjectId
- categoryIDs String[]   @db.Array(ObjectId)
+ categoryIDs String[]   @db.ObjectId
  categories  Category[] @relation(fields: [categoryIDs], references: [id])
}

model Category {
  id      String   @id @default(auto()) @map("_id") @db.ObjectId
- postIDs String[] @db.Array(ObjectId)
+ postIDs String[] @db.ObjectId
  posts   Post[]   @relation(fields: [postIDs], references: [id])
}

Improved connection pooling resiliency

In 3.12.0, we busted a ghost that has been bugging teams since the early days of the Prisma ORM. Under certain amounts of load, some people reported that the connection pool would sometimes drop connections or deadlock and not recover.

After many sightings and a lot of head-scratching, we could finally reproduce the issue. This allowed us to narrow down the problem to one of our dependencies and fix the problem.

CockroachDB support is now in Preview

CockroachDB is a distributed SQL database that shines in its ability to scale efficiently while maintaining developer agility and reducing operational overhead.

CockroachDB support is the product of collaboration with the Cockroach Labs team. You can use Prisma to introspect your existing database with db pull and evolve your schema and propagate changes to your database using Prisma Migrate.

Please give it a spin and let us know what you think to help us iron out the kinks before we push it to General Availability.

Prisma Client logger revamp

We rewrote our internal logger in 3.11.0 to reduce lock contention and enable future features like tracing. This is the first of many upcoming changes to improve Prisma Client's throughput.

Prisma Migrate improvements

Troubleshooting migrations

We introduced 2 new CLI commands into Preview to improve the experience of troubleshooting migrations:

• prisma migrate diff
• prisma db execute

Since we pushed Prisma Migrate for General Availability last year, we've gotten a ton of feedback to understand the challenges developers experience when building, testing, and deploying migrations.

The prisma migrate diff command creates a diff of your database schema, Prisma schema file, or the migration history. You would have to feed the command with a from state and a to state to get a SQL script or human-readable diff in return.

As a companion to the prisma migrate diff, we also built prisma db execute to execute SQL scripts against a database. You can pipe the output from prisma migrate diff directly to prisma db execute --stdin.

Both commands are non-interactive, so it's possible to build many new workflows such as forward and backward migrations with some automation tooling.

Detecting state of a diff with prisma migrate diff using an exit code

We also introduced a new --exit-code flag to the prisma migrate diff command to detect the state of a diff in several ways.

You can use the flag as follows:

npx prisma migrate diff --preview-feature \
--exit-code \
--from-[...] \
--to-[...]

These are the default and changed behaviour of the error codes:

## Default behavior of exit codes
0: Returned when the diff is empty or non-empty
1: Returned on error

## Changed behavior when --exit-code is used
0: Returned when the diff is empty
1: Returned on error
2: Returned when the diff is non-empty

Full-text search for MySQL is now in Preview

In 3.8.0 we added Preview support for full-text search in MySQL. You can enable full-text support by adding the fullTextIndex and fullTextSearch Preview flags in your Prisma schema and defining @@fulltext() indexes on fields you'd like to use full-text search on.

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextIndex", "fullTextSearch"]
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

model Post {
  id     Int    @id @default(autoincrement())
  title  String @unique
  @@fulltext([title])
}

Running prisma db push or prisma migrate dev will update your database schema, then running prisma generate will re-generate your Prisma Client, enabling you to use full-text search in your application.

// search for titles that contain cat, but not fox
await prisma.post.findMany({
  where: {
    title: {
      search: "+cat -fox",
    },
  },
})

dataProxy and interactiveTransactions Preview features are now mutually exclusive

Before 3.8.0, Prisma $transaction queries would fail whenever the Data Proxy and Interactive Transactions Preview features were used together. The interactiveTransactions and dataProxy Preview flags cannot be used together in this release. Generating the Prisma Client when both Preview features are enabled will throw an error.

Interactive transactions concurrency

We fixed a number of issues in version 3.9.0 around timeouts and rollbacks when there were concurrent reads and writes.

If you experienced timeouts or your interactive transactions weren't working quite as you expected, give it another go and let us know what you think. You can learn more about Interactive Transactions in our documentation.

Community

We wouldn't be where we are today without our amazing community of developers. Our Slack has almost 50k members and is a great place to ask questions, share feedback and initiate discussions all around Prisma.

Meetups

Prisma Meetup Online #9: Remix edition

Up and Running with Remix and the Prisma Data Proxy - Austin Crim

Update While you Wait: Optimistic UI with Remix and Prisma - Chance Strickland

Wrecking Remix Resource Routes - Alex Anderson

Meetup YouTube

Serverless Berlin Meetup – an online edition

Lessons from running AppSync in production - Yan Cui

Designing Testable Serverless Applications - Aleksandar Simović

Lambda Powertools - Heitor Lessa

Meetup YouTube

GraphQL Berlin Meetup #25

GraphQL caching demystified - Matteo Collina

Diving into the new Nexus Prisma - Jason Kuhrt

Federating the Content Layer with GraphCMS - Jamie Barton

Meetup YouTube

Videos, livestreams & more

What's new in Prisma

Every other Thursday, Nikolas Burk, Sabin Adams, and Alex Ruheni discuss the latest Prisma release and other news from the Prisma ecosystem and community. If you want to travel back in time and learn about a past release, you can find all the shows from this quarter here:

Some highlights of this quarter include the interviews with A-J Roos about prisma-redis-middleware, Michael Hayes about Pothos GraphQL, Peter Cilliers-Pistorius about seeding databases using Snaplet and Cerbos Authorization with Alex Olivier.

• 3.12.0
• 3.11.0
• 3.10.0
• 3.9.0

Interviews

We published several videos this quarter on our YouTube channel. Check them out and subscribe to not miss out on future videos.

This quarter, we also recorded some interviews with Prisma employees:

• Nika Music (Marketing Associate Intern)
• Alex Ruheni (Developer Advocate)

We also published several videos showcasing how to work with Prisma and Planetscale:

Be sure to subscribe to our YouTube channel to not miss any videos in the future:

Written content

During this quarter, we published several technical articles on the Data Guide that you might find useful:

• Traditional databases vs serverless databases
• Introduction to provisioning MongoDB Atlas
• Introduction to MongoDB indexes
• Introduction to MongoDB transactions
• How to create and delete users in MySQL
• How check your MySQL configuration for syntax errors
• Using mysql_config_editor to manage MySQL credentials
• Introduction to PostgreSQL connection URIs
• Identifying slow queries in MySQL
• Profiling and optimizing slow queries in MySQL
• How to create and delete users in PostgreSQL
• The United States' Most Popular Databases by state going into 2022

We also published several useful articles on our blog:

• Prisma Adds Support for MongoDB – Join Our Launch Week Celebrations
• Enabling static egress IPs in the Prisma Data Platform
• Improving Prisma Migrate DX with two new commands
• Prisma support for CockroachDB is now in Preview
• Fullstack App with TypeScript, PostgreSQL, Next.js, Prisma and GraphQL: Image Upload
• Fullstack App with TypeScript, PostgreSQL, Next.js, Prisma and GraphQL: Authentication

Prisma appearances

This quarter, several Prisma folks have appeared on external channels and livestreams. Here's an overview of all of them:

• Database Workflows & API Development with Prisma by Nikolas Burk @CityJS Conf London and Node Congress
• Database Access on the Edge with Cloudflare Workers & Prisma by Alex Ruheni @Node Congress
• Lightning Talk: What is Prisma by Alex Ruheni @ ConfrontJs

We are hiring

Also, we're hiring for various roles! If you're interested in joining us, check out our jobs page.

Stickers

We love seeing laptops decorated with Prisma stickers, so we're shipping them for free to our community members! In this quarter, we've sent out over 500(!) sticker packs to developers that are excited about Prisma!

What's next?

Prisma Day is back this year, and it'll be on June 15 - 16 at the James&June Sommergarten in Berlin. Join us in-person or online for talks and workshops on modern application development and databases. If you'd like to share your project or give a talk, feel free to submit a CFP.

The best places to stay up-to-date about what we're currently working on are GitHub issues and our public roadmap.

You can also engage in conversations in our Slack channel, and start a discussion on GitHub or join one of the many Prisma meetups around the world.

If you never want to miss any news from the Prisma community, follow us on Twitter.