Using Prisma with PlanetScale

Prisma and PlanetScale together provide a development arena that optimizes rapid, type-safe development of data access applications, using Prisma's ORM and PlanetScale's highly scalable MySQL-based platform.

This document discusses the concepts behind using Prisma and PlanetScale, explains the commonalities and differences between PlanetScale and other database providers, and leads you through the process for configuring your application to integrate with PlanetScale.

What is PlanetScale?

PlanetScale uses the Vitess database clustering system to provide a MySQL-compatible database platform. Features include:

  • Enterprise scalability. PlanetScale provides a highly available production database cluster that supports scaling across multiple database servers. This is particularly useful in a serverless context, as it avoids the problem of having to manage connection limits.

  • Database branches. PlanetScale allows you to create branches of your database schema, so that you can test changes on a development branch before applying them to your production database.

  • Support for non-blocking schema changes. PlanetScale provides a workflow that allows users to update database schemas without locking the database or causing downtime.

Commonalities with other database providers

Many aspects of using Prisma with PlanetScale are just like using Prisma with any other relational database. You can still:

Differences to consider

PlanetScale's branching model and design for scalability means that there are also a number of differences to consider. You should be aware of the following points when deciding to use PlanetScale with Prisma:

  • Branching and deploy requests. PlanetScale provides two types of database branches: development branches, which allow you to test out schema changes, and production branches, which are protected from direct schema changes. Instead, changes must be first created on a development branch and then deployed to production using a deploy request. Production branches are highly available and include automated daily backups. To learn more, see How to use branches and deploy requests.

  • Referential actions and integrity. To support scaling across multiple database servers, PlanetScale does not allow the use of foreign key constraints, which are normally used in relational databases to enforce relationships between data in different tables, and asks users to handle this manually in their applications.
    With Prisma you can maintain these relationships in your data and allow the use of referential actions by using Prisma's ability to emulate referential integrity in the Prisma client. For more information, see How to enable emulation of referential integrity.

  • Creating indexes on foreign keys. When emulating referential integrity in Prisma, you will need to create indexes on foreign keys. In a standard MySQL database, if a table has a column with a foreign key constraint, an index is automatically created on that column. Because PlanetScale does not support foreign keys, these indexes are currently not created when emulating referential integrity, which can lead to issues with queries not being well optimised. To avoid this, you can create indexes in Prisma. For more information, see How to create indexes on foreign keys.

  • Making schema changes with db push. When you merge a development branch into your production branch, PlanetScale will automatically compare the two schemas and generate its own schema diff. This means that Prisma's prisma migrate workflow, which generates its own history of migration files, is not a natural fit when working with PlanetScale. These migration files may not reflect the actual schema changes run by PlanetScale when the branch is merged.

    Prisma recommends not using prisma migrate when making schema changes with PlanetScale. Instead, we recommend that you use the prisma db push command.

    For an example of how this works, see How to make schema changes with db push

  • Introspection. When you introspect on an existing database, you will get a schema with no relations, as they are usually defined based on foreign keys that connect tables. Because PlanetScale does not support foreign keys, and you use Prisma to emulate referential integrity, you will need to add the missing relations in manually. For more information, see How to add in missing relations after Introspection.

How to use branches and deploy requests

When connecting to PlanetScale with Prisma, you will need to use the correct connection string for your branch. The connection URL for a given database branch can be found from your PlanetScale account by going to the overview page for the branch and selecting the 'Connect' dropdown. In the 'Passwords' section, generate a new password and select 'Prisma' from the dropdown to get the Prisma format for the connection URL. See Prisma's Getting Started guide for more details of how to connect to a PlanetScale database.

Every PlanetScale database is created with a branch called main, which is initially a development branch that you can use to test schema changes on. Once you are happy with the changes you make there, you can promote it to become a production branch. Note that you can only push new changes to a development branch, so further changes will need to be created on a separate development branch and then later deployed to production using a deploy request.

If you try to push to a production branch, you will get the error message Direct execution of DDL (Data Definition Language) SQL statements is disabled on this database.

How to enable emulation of referential integrity

Referential integrity is currently a Preview feature. To enable this, add it to the previewFeatures list in the generator block of schema.prisma, and then set the referentialIntegrity type to "prisma" in the datasource block:

schema.prisma
1datasource db {
2 provider = "mysql"
3 url = env("DATABASE_URL")
4 referentialIntegrity = "prisma"
5}
6
7generator js {
8 provider = "prisma-client-js"
9 previewFeatures = ["referentialIntegrity"]
10}

How to create indexes on foreign keys

When emulating referential integrity in Prisma, you will need to create your own indexes. As an example of a situation where you would want to an add an index, take this schema for a blog with posts and comments:

schema.prisma
1model Post {
2 id Int @id @default(autoincrement())
3 title String
4 content String
5 likes Int @default(0)
6 comments Comment[]
7}
8
9model Comment {
10 id Int @id @default(autoincrement())
11 comment String
12 postId Int
13 post Post @relation(fields: [postId], references: [id], onDelete: Cascade)
14}

The postId field in the Comment model refers to the corresponding id field in the Post model. However this is not implemented as a foreign key in PlanetScale, so the column doesn't have an automatic index. This means that some queries may not be well optimised. For example, if you query for all comments with a certain post id, PlanetScale may have to do a full table lookup. This could be slow, and also expensive because PlanetScale's billing model charges for the number of rows read.

To avoid this, you can define an index on the postId field using Prisma's @@index argument:

schema.prisma
1model Post {
2 id Int @id @default(autoincrement())
3 title String
4 content String
5 likes Int @default(0)
6 comments Comment[]
7}
8
9model Comment {
10 id Int @id @default(autoincrement())
11 comment String
12 postId Int
13 post Post @relation(fields: [postId], references: [id], onDelete: Cascade)
14
+ @@index([postId])
16}

You can then add this change to your schema using db push.

One issue to be aware of is that implicit many-to-many relations cannot have an index added in this way. If query speed or cost is an issue, you may instead want to use an explicit many-to-many relation in this case.

How to make schema changes with db push

To use db push with PlanetScale, you will first need to enable emulation of referential integrity in Prisma. Pushing to your branch without referential emulation enabled will give the error message Foreign keys cannot be created on this database.

As an example, let's say you decide to decide to add a new excerpt field to the blog post schema above. You will first need to create a new development branch and connect to it.

Next, add the following to your schema.prisma file:

schema.prisma
1model Post {
2 id Int @id @default(autoincrement())
3 title String
4 content String
excerpt String?
6 likes Int @default(0)
7 comments Comment[]
8}
9
10model Comment {
11 id Int @id @default(autoincrement())
12 comment String
13 postId Int
14 post Post @relation(fields: [postId], references: [id], onDelete: Cascade)
15
16 @@index([postId])
17}

To push these changes, navigate to your project directory in your terminal and run

$npx prisma db push

Once you are happy with your changes on your development branch, you can open a deploy request to deploy these to your production branch.

For more examples, see PlanetScale's tutorial on automatic migrations with Prisma using db push.

How to add in missing relations after Introspection

After introspecting with npx prisma db pull, the schema you get may be missing some relations. For example, the following schema is missing a relation between the User and Post models:

schema.prisma
1model Post {
2 id Int @id @default(autoincrement())
3 createdAt DateTime @default(now())
4 title String @db.VarChar(255)
5 content String?
6 authorId Int
7
8 @@index([authorId])
9}
10
11model User {
12 id Int @id @default(autoincrement())
13 email String @unique
14 name String?
15}

In this case you need to add the relation in manually:

schema.prisma
1model Post {
2 id Int @id @default(autoincrement())
3 createdAt DateTime @default(now())
4 title String @db.VarChar(255)
5 content String?
+ author User @relation(fields: [authorId], references: [id])
7 authorId Int
8
9 @@index([authorId])
10}
11
12model User {
13 id Int @id @default(autoincrement())
14 email String @unique
15 name String?
+ posts Post[]
17}

For a more detailed example, see the Getting Started guide for PlanetScale.

More on using PlanetScale with Prisma

The fastest way to start using PlanetScale with Prisma is to refer to our Getting Started documentation:

These tutorials will take you through the process of connecting to PlanetScale, pushing schema changes, and using the Prisma Client.

For further tips on best practices when using Prisma and PlanetScale together, watch our video:

Edit this page on GitHub