Prisma for Beginners: Setting Up, Migrating, and Querying Data

Getting Started with Prisma

Prisma is a modern database toolkit that simplifies database access in Node.js and TypeScript applications. It provides an intuitive ORM (Object-Relational Mapping) that allows us to define schemas, run migrations, and query databases efficiently. In this guide, we’ll be using Prisma with Next.js as an example.

1. Install Prisma

First, install Prisma as a development dependency:

npm install prisma --save-dev

2. Initialize Prisma

Initialize a Prisma folder and create a schema.prisma file by running:

npx prisma init

3. Set Up a PostgreSQL Database

For a side project, you can get a free PostgreSQL database from Neon.

1. Sign up at neon.tech.

2. Create a new project and get the DATABASE_URL.

3. Replace the default DATABASE_URL in the .env file with your Neon database URL.

DATABASE_URL="your_neon_database_url_here"

4. Define a Model

After initializing Prisma, define a table (model) inside the schema.prisma file. Here’s an example:

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

5. Migrate the Schema

Once the schema is defined, the next step is to migrate it to the database. This converts the schema into SQL queries and executes them to create the actual tables in the database.

npx prisma migrate dev --name init

6. Generate the Prisma Client

After migrating, you need to interact with the database in your application. Prisma provides a client to connect with your database and perform queries. Generate the Prisma client by running:

npx prisma generate

7. Using the Prisma Client

Now, you can import and use the Prisma client in your application:

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

Example: Creating a User

Here’s an example of how you can use Prisma to create a new user:

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

async function main() {
  const user = await prisma.user.create({
    data: { 
      name: 'John Doe', 
      email: 'john@example.com' 
    },
  });
  console.log(user);
}

main();

This is a high-level example of how to set up Prisma in your project.

Making Schema Changes

Let’s say you want to add a new field age to the User table. First, update your model in schema.prisma:

model User {
  id       Int       @id @default(autoincrement())
  email    String    @unique
  name     String?
  profile  Profile?
  age      Int
}

Then, migrate these changes to the database so they reflect in your tables:

npx prisma migrate dev --name added-age 
# (For production, use: "npx prisma migrate deploy")

It’s also a good practice to regenerate the Prisma client after making schema changes:

npx prisma generate

Relationships in Prisma

1. One-to-One Relationship

model User {
  id      Int     @id @default(autoincrement())
  profile Profile
}

model Profile {
  id     Int  @id @default(autoincrement())
  userId Int  @unique
  user   User @relation(fields: [userId], references: [id])
}

A User can have one Profile, and a Profile must belong to one User. The userId in Profile links it to the User. The @unique constraint ensures each user has only one profile.

2. One-to-Many Relationship

model User {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

model Post {
  id     Int  @id @default(autoincrement())
  userId Int
  user   User @relation(fields: [userId], references: [id])
}

A User can have multiple Posts, and each Post belongs to one User. The userId in Post establishes this relationship, while the posts field in User is an array, allowing multiple posts per user.

3. Many-to-Many Relationship (Implicit)

model Post {
  id    Int    @id @default(autoincrement())
  tags  Tag[]
}

model Tag {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

A Post can have multiple Tags, and a Tag can belong to multiple Posts. Prisma automatically creates a join table to manage this relationship, so you don’t need to define it manually.

Querying Data

1. Fetching All Users

const users = await prisma.user.findMany();

This retrieves all users from the database.

2. Fetching a User by ID

const user = await prisma.user.findUnique({
  where: { 
    id: 1 
  } 
});

This fetches the user with id = 1.

3. Updating a User

const updatedUser = await prisma.user.update({
  where: { 
    id: 1 
  },
  data: { 
    name: 'Jane Doe' 
  },
});

This updates the user’s name to Jane Doe.

4. Deleting a User

await prisma.user.delete({ 
  where: { 
    id: 1 
  } 
});

This deletes the user with id = 1 from the database.

This guide provides a basic overview of getting started with Prisma, setting up models, handling migrations, defining relationships, and querying data. You can now integrate Prisma into your projects to simplify database management!

Keep Building, Anon!