[2][DEV NOTE] Integrate prisma and postgres database

This is dev note for this project: turbo - nextjs - prisma - postgres blog

[1][DEV NOTE] Initial turbo project and add tailwindcss library

• Add prisma by add new database package
• Docker postgres database
• Import and use database package inside web app

1. Environment

In this project, I will use postgres database. I used docker to install postgres database on my local machine as follow:
Create docker-compose.yml in the root folder:

# Use postgres/example user/password credentials
version: "3.1"

services:
  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_PASSWORD: example
    ports:
      - 5432:5432

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

It's including postgres database and adminer.

In the next sections, I mainly follow this tutorial: Using Prisma with Turborepo

So that you can skip this part :]]]

Create database package

Create packages/database/package.json:

{
  "name": "database",
  "version": "0.0.0",
  "dependencies": {
    "@prisma/client": "latest"
  },
  "devDependencies": {
    "prisma": "latest"
  },
  "scripts": {
    "db:generate": "prisma generate",
    "db:push": "prisma db push --skip-generate"
  },
  "main": "./index.ts",
  "types": "./index.ts"
}

Create packages/database/index.ts:

export * from "@prisma/client";

Create packages/database/prisma/schema.prisma:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

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

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

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

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

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

Create packages/database/.gitignore:

node_modules
# Keep environment variables out of version control
.env

Important: You need to update the DATABASE_URL variable in your .env file example.

Run prisma init

cd to database package

cd packages/database

After that, it will generate these files:

packages/database/.gitignore
packages/database/prisma/schema.prisma
packages/database/.env

Add database's scripts to turbo.json

{
  "$schema": "https://turbo.build/schema.json",
  "globalDependencies": ["**/.env.*local"],
  "pipeline": {
    "build": {
      "dependsOn": ["^db:generate", "^build"],
      "outputs": [".next/**", "!.next/cache/**"]
    },
    "lint": {},
    "dev": {
      "dependsOn": ["^db:generate"],
      "cache": false,
      "persistent": true
    },
    "db:generate": {
      "cache": false
    },
    "db:push": {
      "cache": false
    },
    "db:migrate": {
      "cache": false
    }
  }
}

Then we can migrate the database as follow

turbo db:migrate -- --name init

After run this command line, prisma will generate a migration.sql file and create a database in the postgres.

Install packages/database into web app

Like part 1, we can install packages/database into web app by:

pnpm add --save-dev database@workspace --fitler web

At this time, we installed prisma and configured database packaged successfully.
Now we continue to next part: Read/Write data with prisma

Finally

Now, you can import and use Prisma in the web app:

Example:

import { PrismaClient } from "database";

export default async function Page() {
  const prisma = new PrismaClient();

  const allPosts = await prisma.post.findMany();

  return (
    <div className="h-full flex justify-center items-center">
      <h1 className="text-5xl font-extrabold text-transparent bg-clip-text bg-gradient-to-br from-pink-400 to-red-600">
        CODE FOR STARTUP.
      </h1>

      {allPosts.map((post) => (
        <div key={post.id}>{post.title}</div>
      ))}
    </div>
  );
}

Next part, I will implement the creating and listing posts with Prisma.

Reference

[1] Mainly on this tutorial: Using Prisma with Turborepo
[2] Prisma getting started