Insta-Next: Constructing Database with Prisma

In part 1, I created a simple class diagram to show the relationships between the classes.

Picking up from where we dropped off in the last part, I will introduce Prisma, convert the class diagram into a Prisma Schema, and migrate and seed the database.

If you decided to skip the first part, no worry, I got you here, you can download the codes from where we stopped the last part here.

Sneak Peek:

Prisma

Overview

So what exactly is Prisma?

Next-generation Node.js and TypeScript ORM

Two keywords here, TypeScript and ORM.

TypeScript - All parameters and objects are fully typed when using Prisma, it'll be very useful later when we need to operate on these objects.

ORM - Object Relation Mapping, where rows in the database (or document for mongo db) are retrieved as objects in our codes.

In short, we use Prisma to connect & interact with the database in the form of typed objects. Even better, Prisma manages migrations for you, that is, they will help to update the database structure based on the Schema.

Schema

The Prisma Schema is like a database schema, it defines the database structure including the relationships between different entities. The Schema uses Prisma's own syntax, so no SQL is needed, its syntax is also simple.

To start off, each table in Prisma will be defined with a keyword model

model User {
  // The attributes go here
}

Types

Like SQL, Prisma provides a few types for us to use

• String
• Boolean
• Int
• BigInt
• Float
• Decimal
• DateTime
• Json
• Bytes

However, if we want to be more specific, for example, if we wish to create a fixed-length string, we can use annotations @db:

model Model {
  /* myField is char of length 12 in database */
  myField String @db.Char(12)
}

Annotations

Prisma also provides some other annotations for manipulating database attributes

1. @id - Indicates that this is a primary key

2. @unique - All data in this column must be unique

3. @updated - Marks that this field will be used to show the last updated datetime

4. @default - Default value of the column, aside from fixed value, Prisma allows these defaults to be used

   • uuid() / cuid() - These are unique serial ids, cuid is shorter than uuid, read more about them here
   • now() - current time
   • autoincrement() - Increases the integer value, usually for id

The annotations can just be used beside the field like the ones above, or here

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

Relationships

Relationships in Prisma is like super simple and very intuitive, we just add a field of the relationship in the model itself and add the @relation annotation to tell which column is the foreign key, and the reference key

model User {
  id      Int    @id @default(autoincrement())
  name    String
  // Prisma will resolve this! Referring to the relationship in Post
  posts   Post[]
}

model Post {
  user_id Int
  user    @relation(fields: [user_id], references: [id])
  caption String
}

So there's a one-to-many relationship, one-to-one is similar, we just need to remove the [] from Post[].

Meanwhile, many-to-many will require intermediate tables, like the UserFollower and PostLike tables from the class design. In essence, they'll look like two one-to-many relationships.

// I know my formatting is a bit off here, but bear with me for now
model User {
  id          Int    @id @default(autoincrement())
  name        String
  // Prisma will resolve this! Referring to the relationship in Post
  posts       Post[]
  posts_liked PostLike []
}

model Post {
  id          Int    @id
  user_id     Int
  user        User   @relation(fields: [user_id], references: [id])
  caption     String
  users_liked PostLike []
}

model PostLike {
  user_id    Int
  user       User @relation(fields: [user_id], references: [id])
  post_id    Int
  post       Post @relation(fields: [post], references: [id])
  created_at @default(now())
}

and that's it! many-to-many relationship. Nevertheless, Prisma also provides implicit implementation, where Prisma creates the intermediate table for you, but I usually prefer to do it myself. You can refer to the docs if you're interested.

Queries

Lastly, how do we make a query? Certainly, we aren't going to write SQL ourselves.

In Prisma, APIs are provided for all the models we defined, for example, to query for the first 10 users (e.g., id <= 10):

const users = prisma.user.findMany({
  where: {
    id: {
      lte: 10,
    },
  },
})

Pretty neat, isn't it? We are using a where to find for id that is less than or equal (lte) to 10. Here's the docs for your reference, but I'll guide you along the way.

Setting up Prisma & Database

You didn't think that you can start writing the schema right away, did you?

As usual, we install the dependencies, and setup our database.

Installing Prisma

yarn add -D prisma
yarn add @prisma/client

Then, we will use prisma-cli to bootstrap our project with the required setups

npx prisma init

After that, you might see a warning like this

warn You already have a .gitignore file. Don't forget to add .env in it to not commit any private information.

Let's follow the instruction, and add this line into the .gitignore file

.env

And that's it, the Prisma is now available in the system! You can now see the prisma folder which contains exactly one file, schema.prisma.

PS: Also remember to add Prisma extension to your IDE, here's the one for VS Code

Connecting to Database

We will first create a database in PostgreSQL (you can install it if you don't have). Not sure if you're a command line person, but for databases, I'd usually prefer to use GUI. I'm using is pgAdmin to create and DBeaver for browsing. DBeaver might look a little old, but it's good and solid.

Creating Database

After opening the app, just click on the local server (PostgreSQL 14), right-click the Databases and create a Database.

I'll name it instanext, remember the name, we're going to need it later.

Then, just click save, and voila you got the database now!

Connecting to Database

Now, head back to your IDE, open up .env file, you should see this line in it

DATABASE_URL="postgresql://johndoe:randompassword@localhost:5432/mydb?schema=public"

Prisma is going to refer to the env file's DATABASE_URL to find where is the database. Let's update the DATABASE_URL to that of the one we created just now.

DATABASE_URL="postgresql://postgres:secret@localhost:5432/instanext"

If you didn't do any specific configuration, this should look the same for you. The postgres is the username for the user of your postgreSQL server, secret is the password, and instanext is the database name. If you did make some configuration, do change them accordingly.

Testing Connection

And..... we're done now! It's time for testing.

Open up your package.json and add this line under scripts

  "scripts": {
     ....
     "migrate": "prisma migrate dev --schema=./prisma/schema.prisma"
  }

So this line basically lets you use the Prisma installed in your project and you tell it that the schema is located at ./prisma/schema.prisma. Next, run the migrate

yarn migrate

If all goes well, you can see these few lines appearing

Prisma schema loaded from prisma\schema.prisma
Datasource "db": PostgreSQL database "instanext", schema "public" at "localhost:5432"

Already in sync, no schema change or pending migration was found.

Error:
You don't have any models defined in your schema.prisma, so nothing will be generated.
...

Oh no, there's Error? False alert.

We haven't defined anything in the schema.prisma file yet, hence the error. But it shows that Prisma managed to connect to the database successfully, and that's what we need.

Building the Schema

Finally, here comes the exciting part, let's convert this class diagram into a Prisma Schema.

Basic Models

Let's start with the base entities: User, Post and Story, we will ignore relationships for now, open up your schema.prisma, add these lines in

model User {
  id          Int            @id @default(autoincrement())
  username    String         @unique
  email       String         @unique
  password    String
  description String         @default("")
  created_at  DateTime       @default(now())
}

model Post {
  id         Int        @id @default(autoincrement())
  caption    String
  user_id    Int
  created_at DateTime   @default(now())
}

model Story {
  id         Int        @id @default(autoincrement())
  caption    String
  user_id    Int
  created_at DateTime   @default(now())
}

Here's a challenge for you, can you write out the model for Image? Assuming id, associated_id and sequence are Int, and the rest are Strings.

model Image {
  id            Int    @id @default(autoincrement())
  type          String
  url           String
  associated_id Int
  sequence      Int
}

Relationships

One-to-Many

There are a few relationships there, let's go with the easier one-to-many relationships, that's the User to Post and Story, let's add the relationship to Post first

model Post {
  ...
  user      User       @relation(fields: [user_id], references: [id])
}

You'd probably notice that when you save after writing the relation, Prisma will add a line in the User Model. This line ensures that the relationship is two-way, User can access Post and Post can access User

model User {
  ...
  Post       Post[]
}

But I'd prefer to name it lowercase with plural form (grammar, you know)

model User {
  ...
  posts       Post[]
}

And you can do the same to Story

model Story {
  id      Int @id @default(autoincrement())
  caption String
  user_id Int
  user    User @relation(fields: [user_id], references: [id])
}

And rename the Story in User to stories

Many-to-Many

There's only one Many-to-Many relationship here, which is the User-Post to record the posts that have been liked by the user. Don't worry, it's quite simple too, we just need to create an intermediary model, PostLike and add the ids from both Post and User

model PostLike {
  user_id    Int
  post_id    Int
  created_at DateTime @default(now())
}

I also added a created_at there. Next, you'd hopefully notice that the IDE is screaming errors like this

Don't panic, it's normal. Since this model's user_id and post_id combinations must be normal (You can't like a post twice), let's create a composite key, this key means that both user_id and post_id combined will be used as the primary key

model PostLike {
  ...
  @@id([user_id, post_id])
}

And the error is gone, hooray!

Lastly, we will add the relationship into this model, it's supposed to be the same as above, PostLike --one-to-many--> User and PostLike--one-to-many--> Post.

model PostLike {
  ...
  user       User     @relation(fields: [user_id], references: [id])
  post       Post     @relation(fields: [post_id], references: [id])
}

Likewise, two fields will be autogenerated in User and Post. Let's rename those to make them better represent the relationships

model User {
  ...
  posts_liked PostLike[]
}

model Post {
  ...
  liked_bys PostLike[]
}

Self-Referencing relationships

The last relationship that we're implementing here is User-->UserFollower-->User relationship. While it doesn't look similar, it's actually just a many-to-many relationship under the hood! Let's just copy over the codes from PostLike and rename it

model UserFollower {
  user_id     Int
  user        User     @relation(fields: [user_id], references: [id])
  follower_id Int
  follower    User     @relation(fields: [follower_id], references: [id])
  created_at  DateTime @default(now())

  @@id([user_id, follower_id])
}

Oh no it's error again

Not a big issue, it's just that we have two relations that refer to the same model, so we can just add a name for each of them to distinguish them from each other, and similarly, in model User

model UserFollower {
  ...
  user        User     @relation("followers", fields: [user_id], references: [id])
  follower    User     @relation("followings", fields: [follower_id], references: [id])
}

model User {
  ...
  followers   UserFollower[] @relation("followers")
  followings  UserFollower[] @relation("followings")
}

If you're confused about the names, think of it this way, if you're the follower, then you must be finding the ones you're following, and the same vice versa.

And... that's it! Our schema is done, refer to GitHub for the complete version.

Wait, what about the Image?

Ah that, it's actually called a polymorphic relationship, where Image can belong to either of User, Post or Story, depending on the type.

Unfortunately, due to the limitation of Prisma, we can't implement it on the Prisma level. I'll implement it manually later.

Migration

Finally, let's do the migration! Migration is when we sync the database with our schema that was just created. It's easy, just run the migrate script we created earlier

PS: Don't run this script for production environment, we usually use prisma migrate deploy for it

yarn migrate

Then, you'll probably be prompted for a name of migration, I name it initial migration. You should see this message if all goes well:

✔ Generated Prisma Client (4.11.0 | library) to .\node_modules\@prisma\client in 333ms

And done! We can now use the Prima in Next.js! You can open up your database GUI client and view the tables being created

Seeding

While I'm as excited as you are to get started, sadly, the tables are empty. Let's do some seeding to fill the tables with arbitrary values

To do that, create a seed.ts file under the prisma folder, then we import the Prisma client which allows us to interact with the database.

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

Then, we create a main function, and start to create some values, say two users, with 3 posts and a story each, here's the first user

async function main() {
  const user1 = prisma.user.create({
    data: {
      username: 'ShenYien',
      email: 'hohshenyien@gmail.com',
      // we will get to encrypting the password next part
      password: '12345abc',
      description: 'I am Shen Yien!',
    },
  })
}

But it will start to get tedious with the number of data. It's time to introduce Faker! A library that helps to generate random data that look legitimate. First, we install the library

yarn add -D @faker-js/faker

Let's create a folder to store functions for data generation to keep them clean. I call these functions factory (coming from Laravel background)

prisma
|= migrations
 |- ....
|= factories
 |- image.ts
 |- index.ts  # This is for exporting everything from the same folder
 |- post.ts
 |- postLike.ts
 |- story.ts
 |- user.ts
 |- userFollower.ts
|- schema.prisma
|- seed.ts

I'll create the factory for the image, user and post here, you can try for the remaining yourself, or refer to GitHub for my implementations:

// factories/user.ts, creating fake users
import { faker } from '@faker-js/faker'
import { Prisma } from '@prisma/client'

// The return type will ensure the data is valid
export const fakeUser = (): Prisma.UserCreateInput => ({
  username: faker.name.firstName() + faker.name.lastName(),
  email: faker.internet.email(),
  password: faker.internet.password(),
  description: faker.lorem.paragraph(),
})

For Post, it'll be a little different because every post requires a user. I created it as follows: if a user is provided, then I'll use connect to connect the newly created post with the user, else, a new user will be created.

// prisma/factories/post.ts
import { faker } from '@faker-js/faker'
import { Prisma, User } from '@prisma/client'
import { fakeUser } from './user'

export const fakePost = (user?: User): Prisma.PostCreateInput => {
  const caption = faker.lorem.paragraph()
  // any date from the past 15 days
  const created_at = faker.date.recent(15)

  if (user) {
    // This is as if setting user_id to user.id
    return { caption, user: { connect: { id: user.id } }, created_at }
  }
  // randomly create a new user
  return { caption, user: { create: fakeUser() }, created_at }
}

Finally, for Image, we will just create the image url while the type, associated_id and sequence will be passed from outside

// prisma/factories/image.ts
import { faker } from '@faker-js/faker'
import { Prisma } from '@prisma/client'

export const fakeImage = (
  associated_id: number,
  type: string,
  sequence: number = 0
): Prisma.ImageCreateInput => ({
  type,
  associated_id,
  sequence,
  // I changed to use unsplash as the default loremflickr
  // was down when I was doing other parts
  url:
    faker.image.unsplash.image() +
    // This is make sure the browser doesn't just retrieve the image from cache
    '/?random=' +
    Math.ceil(Math.random() * 10000),
})

Then, we will just call those functions in our seed.ts to create whatever we needed

async function main() {
  const users: User[] = []
  const posts: Post[] = []
  // creating 2 users
  for (let i = 0; i < 2; i++) {
    const user = await prisma.user.create({ data: fakeUser() })
    // attaching a profile picture to the user
    await prisma.image.create({ data: fakeImage(user.id, 'user') })
    users.push(user)

    // each user has 3 posts
    for (let j = 0; j < 3; j++) {
      const post = await prisma.post.create({ data: fakePost(user) })
      posts.push(post)

      // each post has 3 images
      for (let k = 0; k < 3; k++) {
        await prisma.image.create({ data: fakeImage(post.id, 'post', k) })
      }
    }

    // each user has 2 stories
    for (let j = 0; j < 2; j++) {
      const story = await prisma.story.create({ data: fakeStory(user) })

      // each story has 1 image
      await prisma.image.create({ data: fakeImage(story.id, 'story') })
    }
  }

  // let's make first 2 users like each other
  await prisma.userFollower.create({
    data: fakeUserFollower(users[0], users[1]),
  })
  await prisma.userFollower.create({
    data: fakeUserFollower(users[1], users[0]),
  })

  //let's make the second user likes every post of first user
  for (let i = 0; i < 3; i++) {
    await prisma.postLike.create({ data: fakePostLike(users[1], posts[i]) })
  }
}

Here's the full codes, including the calling part which I copied from the Prisma documentation:

import { Post, PrismaClient, User } from '@prisma/client'
import {
  fakeImage,
  fakePost,
  fakePostLike,
  fakeStory,
  fakeUser,
  fakeUserFollower,
} from './factories'
const prisma = new PrismaClient()

async function main() {
  const users: User[] = []
  const posts: Post[] = []
  // creating 2 users
  for (let i = 0; i < 2; i++) {
    const user = await prisma.user.create({ data: fakeUser() })
    // attaching a profile picture to the user
    await prisma.image.create({ data: fakeImage(user.id, 'user') })

    // each user has 3 posts
    for (let j = 0; j < 3; j++) {
      const post = await prisma.post.create({ data: fakePost(user) })
      posts.push(post)

      // each post has 3 images
      for (let k = 0; k < 3; k++) {
        await prisma.image.create({ data: fakeImage(post.id, 'post', k) })
      }
    }

    // each user has 2 stories
    for (let j = 0; j < 2; j++) {
      const story = await prisma.story.create({ data: fakeStory(user) })

      // each story has 1 image
      await prisma.image.create({ data: fakeImage(story.id, 'story') })
    }
  }

  // let's make first 2 users like each other
  await prisma.userFollower.create({
    data: fakeUserFollower(users[0], users[1]),
  })
  await prisma.userFollower.create({
    data: fakeUserFollower(users[1], users[0]),
  })

  //let's make the second user likes every post of first user
  for (let i = 0; i < 3; i++) {
    await prisma.postLike.create({ data: fakePostLike(users[1], posts[i]) })
  }
}

// copied from https://www.prisma.io/docs/guides/database/seed-database
main()
  .then(async () => {
    await prisma.$disconnect()
  })
  .catch(async (e) => {
    console.error(e)
    await prisma.$disconnect()
    process.exit(1)
  })

Finally, we add the script for seeding in our package.json (I prefer to call it from yarn)

"scripts": {
  ...
  "seed": "ts-node prisma/seed.ts"
}

Wait... it's a bit suspicious there. We have never installed ts-node! Let's install it

yarn add -D typescript ts-node @types/node

and we can now run the seeding

yarn seed

If you're like me, and you encountered this error:

SyntaxError: Cannot use import statement outside a module

You can update the seed command from package.json to fix it, source

"scripts": {
  ...
  "seed": "ts-node --compiler-options {\"module\":\"CommonJS\"} prisma/seed.ts"
  // for Windows
  "seed": "set TS_NODE_COMPILER_OPTIONS={\"module\":\"commonjs\"} && ts-node prisma/seed.ts"
}

and run yarn seed again, now you should finally see some data in the tables

You can also use Prisma's own Prisma Studio to browse the database

npx prisma studio

You can now browse the record easily using Prisma Studio if you don't want to use external apps like DBeaver for it

Summary

Phew, that was a long way to get our database live.

So to review, we have created a Prisma Schema, connected a local database the Prisma and seeded it.

In the next article, we will have a first look at writing APIs with Next.js!

Complete codes for this part can be found on my GitHub