Easier Queries with Prisma ORM

This week, I finished the Node.js course from The Odin Project!

Before diving into Prisma specifically, it’s worth understanding what an ORM actually is. Put simply, an ORM is a tool that lets you interact with your database using your programming language instead of writing raw SQL queries. So instead of writing SELECT * FROM users WHERE id = 1;, you could write something like User.findById(1).

What I found really appealing about Prisma is how it combines several tools into one cohesive package. You get type safety, an intuitive API, and great developer experience all in one. Coming from learning SQL, I was honestly a bit skeptical about using an ORM at first. I thought it might abstract away too much and make me lose touch with what was actually happening in the database. But after using Prisma for a while, I realized it actually helps you understand your data better!

To get started with Prisma, you first need to install it. In your project directory, run:

npm install prisma --save-dev
npm install @prisma/client

Then, initialize Prisma with npx prisma init. This will create a prisma folder with a schema.prisma file inside. This schema file is where you’ll define your data models.

The schema file is actually quite readable. Here’s what a basic schema might look like:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

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

Let’s break down what’s happening here. The datasource block tells Prisma which database you’re using. In this case, it’s PostgreSQL, but Prisma also supports other relational database management systems. The url is pulled from your environment variables, which is where you’d store your database connection string.

The generator block specifies that we want to generate the Prisma Client, which is what we’ll use to actually query our database.

Then we have our models. Each model corresponds to a table in your database. The User model has an id (which is the primary key), an email (which is unique), an optional name (the ? makes it optional), a relation to Posts, and a createdAt timestamp.

The @id decorator marks a field as the primary key, while @default(autoincrement()) tells Prisma to automatically increment the ID for each new record. Similarly, @default(now()) sets the current timestamp by default.

One of the most powerful features of Prisma is how it handles relations. In the example above, we have a one-to-many relationship between User and Post. A user can have many posts, but each post belongs to one user. Prisma makes this relationship explicit in the schema!

The Post model has an author field that references the User model. The @relation decorator specifies that the authorId field in the Post table corresponds to the id field in the User table. Meanwhile, the User model has a posts field of type Post[], which means it can have multiple posts.

After defining your schema, you need to create a migration. Migrations are essentially a way to version control your database schema. To create a migration, run:

npx prisma migrate dev --name init

This will create a SQL migration file and apply it to your database. The --name init part is just a name for your migration. You can call it whatever you want!

After running the migration, Prisma will also automatically generate the Prisma Client for you. This client is what you’ll use to interact with your database in your application code.

Here’s how you’d use Prisma Client in your Express routes:

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

// create a new user
app.post("/users", async (req, res) => {
  try {
    const user = await prisma.user.create({
      data: {
        email: req.body.email,
        name: req.body.name,
      },
    });
    res.json(user);
  } catch (err) {
    res.status(500).json({ error: "Error creating user" });
  }
});

// get all users
app.get("/users", async (req, res) => {
  try {
    const users = await prisma.user.findMany();
    res.json(users);
  } catch (err) {
    res.status(500).json({ error: "Error fetching users" });
  }
});

// get a specific user
app.get("/users/:id", async (req, res) => {
  try {
    const user = await prisma.user.findUnique({
      where: { id: parseInt(req.params.id) },
    });
    if (!user) {
      return res.status(404).json({ error: "User not found" });
    }
    res.json(user);
  } catch (err) {
    res.status(500).json({ error: "Error fetching user" });
  }
});

// update a user
app.put("/users/:id", async (req, res) => {
  try {
    const user = await prisma.user.update({
      where: { id: parseInt(req.params.id) },
      data: {
        email: req.body.email,
        name: req.body.name,
      },
    });
    res.json(user);
  } catch (err) {
    res.status(500).json({ error: "Error updating user" });
  }
});

// delete a user
app.delete("/users/:id", async (req, res) => {
  try {
    await prisma.user.delete({
      where: { id: parseInt(req.params.id) },
    });
    res.json({ message: "User deleted successfully" });
  } catch (err) {
    res.status(500).json({ error: "Error deleting user" });
  }
});

The API is incredibly intuitive! For creating records, you use create(). For reading, you use findMany() to get all records or findUnique() to get a single record. For updating, you use update(). And for deleting, you use delete(). It’s essentially CRUD operations with a very clean syntax. You can refer to it here.

One thing I really appreciate about Prisma is the type safety. You get autocompletion and type checking for all your queries. So if you try to access a field that doesn’t exist on your model, your editor will warn you immediately. This is so helpful for catching mistakes early!

Prisma also makes it really easy to work with relations. Let’s say you want to get a user along with all of their posts:

const userWithPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: true,
  },
});

The include option tells Prisma to also fetch the related posts. You can even nest these includes to fetch deeply nested relations!

You can also create a user and their posts in a single query using nested writes:

const user = await prisma.user.create({
  data: {
    email: "alice@example.com",
    name: "Alice",
    posts: {
      create: [
        { title: "My first post", content: "Hello world!" },
        { title: "My second post", content: "This is great!" },
      ],
    },
  },
});

This will create the user and their posts in a single transaction, which is really convenient!

Filtering and sorting in Prisma is also quite straightforward. Here’s an example of a more complex query:

const publishedPosts = await prisma.post.findMany({
  where: {
    published: true,
    author: {
      email: {
        contains: "@example.com",
      },
    },
  },
  orderBy: {
    createdAt: "desc",
  },
  take: 10,
  skip: 0,
});

This query gets the 10 most recent published posts from users whose email contains ‘@example.com’. The where clause is for filtering, orderBy is for sorting, take is the limit, and skip is the offset (useful for pagination).

Prisma also supports aggregations and grouping, similar to what you’d do with SQL’s COUNT(), SUM(), AVG(), etc. For example:

const userCount = await prisma.user.count();

const averagePostsPerUser = await prisma.post.aggregate({
  _avg: {
    authorId: true,
  },
});

I will say though, while Prisma is great, it does have a bit of a learning curve when it comes to more complex queries. For instance, there were moments where I knew exactly how to write something in SQL, but translating it to Prisma’s syntax took some trial and error.

But after a while, I got the hang of it. Thankfully, the documentation is excellent and covers pretty much every use case you can think of. And now, I actually prefer using Prisma, as I find it easier to write more complex queries compared to SQL where I would have to JOIN tables properly.

Overall, I’m really impressed with Prisma. It strikes a good balance between abstraction and control. You’re not writing raw SQL for every little thing, but you’re also not so far removed that you don’t understand what’s happening. Plus, the type safety is a huge win for catching bugs early.

That’s about it for this week. Now that I’ve finished the Node.js course, I plan on spending my time next week on learning TypeScript. While The Odin Project talks about getting a job after the Node.js course, I want to prioritize learning other technologies first and building my own personal projects.

I’ll go over what I’ve learned about TypeScript next week!