Using Prisma with MongoDB

How to migrate existing data to match your Prisma schema

Migrating your database over time is an important part of the development cycle. During development, you will need to update your Prisma schema file (for example, to add new fields), then update the data in your development environment’s database, and eventually push both the updated schema and the new data to the production database.

These iterative tasks of updating the schema and the database can result in inconsistencies between your schema and the actual data in the database. Let’s look at one scenario where this can happen, and then examine several strategies for you and your team to consider for handling these inconsistencies.

Scenario: you need to include a phone number for users, as well as an email. You currently have the following User model in your schema.prisma file:

prisma/schema.prisma
1model User {
2  id    String @id @default(auto()) @map("_id") @db.ObjectId
3  email String
4}

There are a number of strategies you could use for migrating this schema:

• "On-demand" updates: with this strategy, you and your team have agreed that updates can be made to the schema as needed. However, in order to avoid migration failures due to inconsistencies between the data and schema, there is agreement in the team that any new fields added are explicitly defined as optional.

  In our scenario above, you can add an optional phoneNumber field to the User model in your Prisma schema:

  prisma/schema.prisma
  1model User {
  2  id          String  @id @default(auto()) @map("_id") @db.ObjectId
  3  email       String
  +  phoneNumber String?
  5}

  Then regenerate your Prisma Client using the npx prisma generate command. Next, update your application to reflect the new field, and redeploy your app.

  As the phoneNumber field is optional, you can still query the old users where the phone number has not been defined. The records in the database will be updated "on demand" as the application's users begin to enter their phone number in the new field.

  Another option is to add a default value on a required field, for example:

  prisma/schema.prisma
  1model User {
  2  id          String @id @default(auto()) @map("_id") @db.ObjectId
  3  email       String
  +  phoneNumber String @default("000-000-0000")
  5}

  Then when you encounter a missing phoneNumber, the value will be coerced into 000-000-0000.

• "No breaking changes" updates: this strategy builds on the first one, with further consensus amongst your team that you don't rename or delete fields, only add new fields, and always define the new fields as optional. This policy can be reenforced by adding checks in the CI/CD process to verify that there are no backwards-incompatible changes to the schema.

• "All-at-once" updates: this strategy is similar to traditional migrations in relational databases, where all data is updated to reflect the new schema. In the scenario above, you would create a script to add a value for the phone number field to all existing users in your database. You can then make the field a required field in the application because the schema and the data are consistent.

How to add in missing relations after Introspection

After introspecting an existing MongoDB database, you will need to manually add in relations between models. MongoDB does not have the concept of defining relations via foreign keys, as you would in a relational database. However, if you have a collection in MongoDB with a "foreign-key-like" field that matches the ID field of another collection, Prisma will allow you to emulate relations between the collections.

As an example, take a MongoDB database with two collections, User and Post. The data in these collections has the following format, with a userId field linking users to posts:

User collection:

• _id field with a type of objectId
• email field with a type of string

Post collection:

• _id field with a type of objectId
• title field with a type of string
• userId with a type of objectID

On introspection with db pull, this is pulled in to the Prisma schema file as follows:

prisma/schema.prisma
1model Post {
2  id     String @id @default(auto()) @map("_id") @db.ObjectId
3  title  String
4  userId String @db.ObjectId
5}
6
7model User {
8  id    String @id @default(auto()) @map("_id") @db.ObjectId
9  email String
10}

This is missing the relation between the User and Post models. To fix this, manually add a user field to the Post model with a @relation attribute using userId as the fields value, linking it to the User model, and a posts field to the User model as the back relation:

prisma/schema.prisma
1model Post {
2  id     String @id @default(auto()) @map("_id") @db.ObjectId
3  title  String
4  userId String @db.ObjectId
+  user   User   @relation(fields: [userId], references: [id])
6}
7
8model User {
9  id    String @id @default(auto()) @map("_id") @db.ObjectId
10  email String
+  posts Post[]
12}

For more information on how to use relations in Prisma, see our documentation.

How to filter for null and missing fields

To understand how MongoDB distinguishes between null and missing fields, consider the example of a User model with an optional name field:

model User {
  id    String  @id @default(auto()) @map("_id") @db.ObjectId
  email String
  name  String?
}

First, try creating a record with the name field explicitly set to null. Prisma will return name: null as expected:

const createNull = await prisma.user.create({
  data: {
    email: 'user1@prisma.io',
    name: null,
  },
})
console.log(createNull)

{
  id: '6242c4ae032bc76da250b207',
  email: 'user1@prisma.io',
  name: null
}

If you check your MongoDB database directly, you will also see a new record with name set to null:

{
  "_id": "6242c4af032bc76da250b207",
  "email": "user1@prisma.io",
  "name": null
}

Next, try creating a record without explicitly setting the name field:

const createMissing = await prisma.user.create({
  data: {
    email: 'user2@prisma.io',
  },
})
console.log(createMissing)

{
  id: '6242c4ae032bc76da250b208',
  email: 'user2@prisma.io',
  name: null
}

Prisma still returns name: null, but if you look in the database directly you will see that the record has no name field defined at all:

{
  "_id": "6242c4af032bc76da250b208",
  "email": "user2@prisma.io"
}

Prisma returns the same result in both cases, because we currently don't have a way to specify this difference in MongoDB between fields that are null in the underlying database, and fields that are not defined at all — see this Github issue for more information.

This means that you currently have to be careful when filtering for null and missing fields. Filtering for records with name: null will only return the first record, with the name explicitly set to null:

const findNulls = await prisma.user.findMany({
  where: {
    name: null,
  },
})
console.log(findNulls)

$[
$  {
$    id: '6242c4ae032bc76da250b207',
$    email: 'user1@prisma.io',
$    name: null
$  }
$]

This is because name: null is checking for equality, and a non-existing field isn't equal to null.

To include missing fields as well, use the isSet filter to explicitly search for fields which are either null or not set. This will return both records:

const findNullOrMissing = await prisma.user.findMany({
  where: {
    OR: [
      {
        name: null,
      },
      {
        name: {
          isSet: false,
        },
      },
    ],
  },
})
console.log(findNullOrMissing)

$[
$  {
$    id: '6242c4ae032bc76da250b207',
$    email: 'user1@prisma.io',
$    name: null
$  },
$  {
$    id: '6242c4ae032bc76da250b208',
$    email: 'user2@prisma.io',
$    name: null
$  }
$]