What is Prisma ORM?
Prisma is an object relational mapper (ORM) that can be used alongside several databases including PostgreSQL, MySQL, MariaDB, SQLite, MongoDB, and more. Prisma claims that its main goal is to make application developers more productive when working with databases. Some of its strengths are that it enables developers to think in terms of objects rather than mapping relational data, and it tries to accomplish this while balancing tradeoffs between developer productivity and developer control over a database.
Should you use Prisma?
In an environment where you are working with raw SQL, you might have an increased level of control over your database and the queries that you serve to it. However, this can have tradeoffs especially as it pertains to productivity.
Many developers might be inclined to work with traditional ORMs so that they can abstract away from SQL; this approach is generally taken so that they can achieve some balance between productivity and control. However, this inherently causes some issues and misconceptions with our database. Prisma documentation describes this wonderfully with the following statement:
As an application developer, the mental model you have for your data is that of an object. The mental model for data in SQL on the other hand are tables. The divide between these two different representations of data is often referred to as the object-relational impedance mismatch. The object-relational impedance mismatch also is a major reason why many developers don't like working with traditional ORMs.
Prisma lists the following concerns for developers, claiming that its ORM might be beneficial for you, your team, or your application if these concerns exist:
- You are building a server-side application that talks to a database.
- You care about productivity and the developer experience.
- You are working in a team or otherwise collaborative environment.
- You want or require a tool that holistically addresses database workflows.
- You want or require some form of type-safety in your environment.
- You want to have the option to write raw and type-safe SQL.
How does Prisma ORM work?
Prisma contains several components that address database workflows, which include the Prisma Client, Prisma Migration Sytem, and the Prisma Studio. In this article we will mainly cover the functionality of the Prisma Client and the Prisma Migration System. More information about these features can be found here; for our purposes we are going to explore how to setup Prisma in a project and how we can begin integrating it into a local server and database.
Getting Started
Initialize an empty database in MySQL
In our examples, we will be working with MySQL but our setup would (generally) remain unchanged across databases. The first thing that you might want to do is setup your database, if you haven't already. In this article we are going to assume that you have MySQL installed locally, that your service is running, that you are able to login to the MySQL monitor, and that you have a empty repository that is ready to be developed as a TypeScript project.
You do not need to perform these steps locally in order to understand the content that we are going to review, but it might help.
The first step that we are going to take is to create a database that we would like to use alongside Prisma. In our case, we are going to just create a database called Catchee.
> mysql -u root
> CREATE DATABASE Catchee;
> Query OK, 1 row affected (0.04 sec)
Installing and initializing Prisma
If you are interested in following along, Prisma provides a section dedicated to creating a TypeScript project and setting up the Prisma ORM. This article will somewhat follow along with this tutorial, although there are some deviations later.
In order to install the Prisma CLI as a project dependency, you can run the following command.
npm install prisma --save-dev
As a general recommendation, you should also install the Prisma Client package for use within your project. We are going to use the Prisma Client later on in this article.
npm i @prisma/client
In order to actually setup the Prisma ORM locally, we recommend that you use the following command to initialize the basic files and folder structures that Prisma expects to exist.
npx prisma init --datasource-provider mysql
Once the previous command has been used within your command-line interface, there should be a message indicating that a Prisma schema was successfully created in your project. If we open our Prisma file, we will see something akin to the following (depending on the datasource-provider you have selected).
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema
// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
}
Our schema.prisma file is arguably the most important file as you begin setting the ORM to work with your database. It is important for a few reasons, which we are going to explore in the next section. It is highly recommended that, if you are using VSCode, you download the Prisma extension for syntax highlighting, auto-completion, and formatting.
The Prisma Schema
What is included at start?
The Prisma schema file enables us to do many things, and it is important for us to break this apart in order to understand what Prisma is doing but also what it isn't doing. The first item that we will review is the generator client block.
generator client {
provider = "prisma-client-js"
}
This code block within our schema file is telling Prisma that we want to be using the Prisma Client for our interactions. What does this mean, however? The Prisma Client will eventually enable us to query our database; it is a very powerful tool that offers a degree of auto-completion whenever we do begin to write queries to our database. It might be somewhat difficult to envision now, but generally this code block remains untouched except for special use cases. Now let's take a look at our datasource db code block.
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
}
This code block is, essentially, informing Prisma what database system it is working with and also what database it should be targeting. The provider informs Prisma that we will be working with MySQL; this is helpful because as we begin developing models (tables) Prisma can infer data types, syntax, and more in order to ensure that the model will be supported by our database system.
The url helps direct Prisma to a specific database (in our case it is Catchee) within our database management system (MySQL). Notice that it is trying to pull this information from a .env file in our repository; we can setup the DATABASE_URL in this file to match the format Prisma expects. This format might vary depending on your DBMS, in our case Prisma provides an example DATABASE_URL that follows the structure mysql://USER:PASSWORD@HOST:PORT/DATABASE.
What are models?
Currently, our schema file only includes the information necessary to build a client (which will allow us to perform CRUD operations later) and the information necessary to target a database for with these CRUD operations. However, what else can we include in this file to ease the development process?
In the Prisma schema, you can also define models that you can eventually migrate over to your targeted database. If we expanded the previous template that was provided to us, we might include something like a User model.
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
}
model User {
id Int @id @default(autoincrement())
email String @unique
name String?
}
It might be intimidating to see us jump from having no model (table) to including a complete one here. This is an example from Prisma, which we have copied over, but if it eases the mind an important thing to consider is that Prisma recommends and informs developers of available data types (and constraints) as you begin writing a model. This is helpful because it enables developers to quickly verify that the data type they are prescribing is valid, and that it will also be accepted by the datasource provider. If you feel like these features are not enough, Prisma has a dedicated section within their documentation that discusses data modelling.
How can we use models?
After we have described models within our Prisma schema, we can add these models to our database so that we can begin interacting with them in the Prisma Client. There are two crucial steps here in order to accomplish this.
Build the Prisma Client
The first step is to attempt to build the Prisma Client; this will update the Prisma Client so that it is aware of the new models we have described and so that it understands how to query them. In order to build the client, you can run the command npx prisma generate. If there are any errors within our schema, Prisma will inform us of these errors when we try to build our client.
Migrate our models to the database
Once our client has been built successfully, we can attempt to migrate the models contained therein to the datasource that has been prescribed in the datasource db block of the Prisma schema. In order to accomplish this, we can run the command npx prisma migrate dev. If you are following along and encounter errors at this step, verify the following:
- Is the MySQL service running locally?
- Have you defined the
DATABASE_URLin a.envfile? - Have you properly formatted your
DATABASE_URLfor the DBMS that you are using?
Querying with Prisma Client
If you are following along with the previous steps, the processes that we are going to cover now depend on a successful client generation and a successful migration. If you encountered an error, please resolve those errors before continuing.
Now we are able to import the Prisma Client to a TypeScript file so that we can attempt to query the database. In order to open a connection to our database with the Prisma Client, we can use the following code.
import { PrismaClient } from '@prisma/client';
const database = new PrismaClient();
database.$connect()
.then(() => {
console.log(`Prisma has connected to the database...`);
})
.catch((error) => {
console.error(`Failure on database connection...`)
})
If our project necessitated it, we could export this Prisma Client database or we could also attempt to export the connection itself. This is useful if we wanted to export this for use in a server, and it is especially useful so that we don't have multiple clients open (if we have routers).
Now we can begin to actually interact with our database and the structures provided to it. Because we have successfully built the client, you will notice that as you type there will exist properties on the Prisma Client that refer to the models we have defined. Let's take a look at an example below.
import { PrismaClient } from '@prisma/client';
const database = new PrismaClient();
database.$connect()
.then(() => {
console.log(`Prisma has connected to the database...`);
})
.catch((error) => {
console.error(`Failure on database connection...`)
})
// Here we are defining a new user to create
const newUser = {
email: 'myCoolEmail@gmail.com',
name: 'Justin Sandrock'
}
// We pass that information to the Prisma Client to perform the operation
database.user.create({data: newUser})
.then((userCreated) => {
console.log(userCreated);
})
.catch((error) => {
console.error(error);
})
If this operation is successful, then we should be able to view the newly created user within our console. If we encounter any errors, then we will also be informed of the error. It's just that easy! There are many different CRUD operations that you can perform through the Prisma Client, for a full list you can view them here as well as some examples.
Conclusion
Prisma is a versatile ORM that can make developers more productive without removing a significant amount of control; if you prefer to work with raw SQL then there are also some options to perform these raw operations with the Prisma Client. If you feel like Prisma might be a good addition to your project, consider reading more at the official website.
Our team (OS-IMS Code-Blooded) has been using the Prisma ORM as we attempt to create a multiplayer game. Thus far, Prisma has enabled us to move efficiently and rapidly as we establish our architecture! We felt as though the querying capabilities offered by Prisma were quite powerful, despite the abstraction away from traditional SQL syntax, and the inclusion of Prisma Studio allowed for us to quickly observe whether or not our game was storing data as intended.
Our team will be publishing a joint article on our project in the near future, and we will also be providing a link to our website if you would like to check it out! We expect that it will be in a stable status at the start of 2025 (we are currently overhauling features to ensure a pleasant end-user experience).
Top comments (1)