Using Prisma with a MySQL database

What is Prisma?

Prisma is an open-source ORM (Object-Relational Mapping) tool that provides a type-safe and intuitive way of accessing databases. With Prisma, developers can write database queries using their favorite preferred programming language, which is then translated into SQL queries by Prisma.

Figure 1 - Prisma integrations - source

Prisma uses schema files to generate database models and queries for interacting with the database and provides a powerful migration system to manage changes to the database schema. Prisma is compatible with a variety of databases, but overall, the general workflow and language used to define the data model will be the same regardless of the database you choose. There are, however, some slight differences that come with each database implementation, such as connection details and configuration options. This article focuses on using Prisma with a MySQL database, so we’ll dive into some of those MySQL-specific details and go through an example of connecting a PlanetScale MySQL database to Prisma. If you haven’t already, sign up for a PlanetScale account now.

The Prisma data model and its features

Prisma provides a way to define your application's data model using a declarative schema language. It allows you to specify the types of fields, relationships between tables, and various constraints on the data. An example of a Prisma model representing a User object is:

model User {
  id          Int     @id @default(autoincrement())
  name   String
  email   String  @unique
}

Its features include a type-safe client API. Type safety refers to the ability of a programming language or framework to enforce type checking at compile time, preventing errors related to incompatible types or data types mismatch. In the context of Prisma, type safety means that the generated Prisma client provides a type-safe API that matches the data model defined in the Prisma schema. This ensures that the data types used in the application match the data types expected by the database, reducing the likelihood of runtime errors and inconsistencies.

It also offers optimized SQL queries and a powerful migration system to manage database schema changes. Prisma also allows you to define relations between tables, validate data, and enforce constraints. It provides a flexible and efficient way to work with data and is well-suited for modern, cloud-native applications.

How to connect Prisma to a MySQL database

Let’s go through some of the specifics of using Prisma with MySQL by looking at an example using a PlanetScale MySQL database. To follow along, sign up for a PlanetScale account, and follow the quickstart guide to spin up a new database.

Create the Prisma schema file

In your application, create a new Prisma schema file at the root of your directory, prisma/schema.prisma. This is where you’ll define some key information about your database, such as:

• The datasource – Your database provider and connection string.
• Your data model – The schema that defines your data. In MySQL, these will be your tables.
• Generators – Defines any additional assets you need created. The most-used generator is the prisma-client-js, which, when defined in this file, is generated when you run prisma generate.

Let’s first start with connecting our datasource. For MySQL, the format should look like this:

Let’s go through some of the specifics of using Prisma with MySQL by looking at an example using a PlanetScale MySQL database. To follow along, sign up for a PlanetScale account, and follow the quickstart guide to spin up a new database.

// prisma/schema.prisma
datasource db {
  provider = "mysql"
  url           = env("DATABASE_URL")
}
// .env
DATABASE_URL = "mysql://USERNAME:PASSWORD@HOSTNAME:PORT/DATABASENAME"

Note: You should always store sensitive information, like your database connection string, in a separate environment variables file that does not get committed to your repository.

Add your database credentials

Since we’re using a PlanetScale database, you need to replace the DATABASE_URL connection string in your .env file with your PlanetScale connection string. To do this, head to the PlanetScale dashboard, click on your database, and click “Connect.” Select “Prisma” from the dropdown, copy the connection string, and paste it in as the value for DATABASE_URL.

You may notice that the PlanetScale connection string has an additional argument at the end: ?sslaccept=strict. PlanetScale requires SSL to be enabled to prevent man-in-the-middle attacks. Adding this argument enforces that. You can find additional argument options for your connection strings in the Prisma documentation.

Configure generators

For our example, we want to use the prisma-client-js generator so we can easily build SQL queries. To use this, add the following to your schema.prisma file:

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

PlanetScale does not support foreign key constraints, so we have to add this previewFeatures = ["referentialIntegrity"] to enforce that.

To install the prisma-client-js, you can run:

npm install @prisma/client

Add your data model

Next, let’s add in a data model. We can use this Next.js starter repository as an example. Below, we’ve added 2 models: Product and Category. Each model corresponds to a table in MySQL. The columns of that table are defined inside the model object with the column name, data type, and any other constraints you need to add.

To work with PlanetScale, we’ve also updated the datasource db relationMode definition with referentialIntegrity = "prisma" to accommodate no foreign key constraints.

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


model Product {
    id          Int       @id @default(autoincrement())
    name        String
    description String
    price       Decimal
    image       String
    category    Category? @relation(fields: [category_id], references: [id])
    category_id Int
    @@index([category_id])
}


model Category {
    id          Int       @id @default(autoincrement())
    name        String
    description String
    products    Product[]
}

Run your migrations

Finally, running the following:

npx prisma db push

Once you have configured the above, it is a matter of defining the data model object(s), as also described in the previous section, and finally proceed with the Prisma migration. In doing so, your Prisma schema is translated into SQL, and runs against the MySQL database that is configured in the datasource block.

Note: In PlanetScale performing the migration is best completed with executing the Prisma db push command rather than running Prisma migrate, since PlanetScale automates the background tasks that take place during the migration operation. For other MySQL configurations, you can use prisma migrate.

Conclusion

Prisma is a powerful ORM tool that enables modern applications to access databases in a type-safe manner. When used with MySQL and PlanetScale, it provides an effective solution for building and deploying cloud-native applications. Prisma's advanced features, including transactions, schema migrations, and support for database-specific data types, make it an ideal choice for complex applications. With Prisma, you can easily define database schema using a declarative syntax and generate models and queries automatically, simplifying the development process.