Using Prisma as an ORM in Node.js for SQL Databases

Prisma is a modern and widely used ORM (Object-Relational Mapping) tool designed to simplify database operations in Node.js applications. It offers a clean and easy-to-use API that allows developers to interact with SQL databases more efficiently, minimizing boilerplate code and improving productivity.

In this article, we’ll learn how to set up and use Prisma with Node.js to build the backend of a web application. We’ll walk through key steps such as:

• Initializing a Prisma project

• Creating a data model

• Running database queries

• Performing create, update, and delete operations

Getting Started: Setting Up Prisma in Node.js

To begin using Prisma in your Node.js project, you’ll first need to install the Prisma CLI. This tool helps manage your Prisma setup and automatically generates the necessary code for database interaction.

Use the following command in your terminal to install the Prisma CLI:

npm install prisma --save-dev

Once installed, you can initialize a new Prisma project with:

npx prisma init

This creates a prisma folder with a sample schema file and a .env file where you’ll configure your database connection.

This command sets up the basic Prisma project structure and creates essential files, including a schema.prisma file. You’ll use this file to define your data models and connect to the SQL database of your choice.

Note: Prisma used to have a prisma.yml file in older versions, but now it uses schema.prisma — so this update is both accurate and modern.

Defining a Data Model

After setting up your Prisma project, the next step is to define the structure of your database — this is called the data model. Prisma uses the schema.prisma file for this purpose.

In the schema.prisma file (found inside the prisma folder), you’ll describe your database tables, fields (columns), and how they relate to each other using Prisma's schema syntax.

Example: Blog Data Model

Let’s say you’re building a simple blog. Your data model might look like this:

model User {
  id    Int     @id @default(autoincrement())
  name  String
  email String  @unique
  posts Post[]  // one-to-many relationship
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String
  published Boolean
  author    User    @relation(fields: [authorId], references: [id])
  authorId  Int
}

Explanation:

• @id marks the primary key.

• @default(autoincrement()) generates unique IDs automatically.

• @relation(...) sets up the foreign key and link between Post and User.

• Post[] in the User model means one user can have multiple posts.

In this data model, we have defined two types: Post and User. The Post type represents a blog post, with columns for the title, content, and publication status of the post. The User type represents a user, with columns for their name and email address.

We have also defined a relationship between the Post and User types, using the @relation directive. This tells Prisma that each post is associated with a single user, and that each user can have multiple posts.

Querying the Database

After defining your data model, the next step is to interact with your database using Prisma. Prisma generates a client library for you—a JavaScript tool that makes it easy to run queries and manage data.

To create this Prisma Client, run the following command in your terminal:

npx prisma generate

This command creates the Prisma Client based on your data model, which you can then import into your Node.js code to perform database operations.

To fetch data from your database, use the Prisma Client in your Node.js application. For example, this code retrieves all posts:

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function getPosts() {
  const posts = await prisma.post.findMany();
  console.log(posts);
}

getPosts()
  .catch(e => console.error(e))
  .finally(async () => {
    await prisma.$disconnect();
  });

In this code, we first import and initialize the Prisma Client. Then, using the findMany method, we fetch all records from the Post table and print them to the console.

Creating, Updating, and Deleting Data

Prisma Client also lets you add new records, update existing ones, and delete data from your database easily.

For example, to create a new post, you can use the following code:

const newPost = await prisma.post.create({
  data: {
    title: 'My First Post',
    content: 'This is the content of the post.',
    published: false,
    authorId: 1, // assuming user with ID 1 exists
  },
});
console.log(newPost);

In this code, the create method adds a new post by providing details like title, content, published status, and author ID.

To update or delete a post, you can use the following methods:

// Update a post by ID
const updatedPost = await prisma.post.update({
  where: { id: 1 },
  data: { published: true },
});
console.log(updatedPost);

// Delete a post by ID
const deletedPost = await prisma.post.delete({
  where: { id: 1 },
});
console.log(deletedPost);

Conclusion

Prisma makes working with SQL databases in Node.js easy and efficient. With its clear data modeling and powerful client API, you can quickly set up your backend to handle creating, reading, updating, and deleting data. Using Prisma helps you write cleaner code and focus more on building your application rather than managing complex database queries.