[2][DEV NOTE] Integrate prisma and postgres database
Tags: nextjs, prisma, typescript, postgres
Published: 2023-08-22 14:33:04

> This is dev note for this project: [turbo - nextjs - prisma - postgres blog
](https://github.com/CodeForStartup/nextjs-prisma-postgres)

[[1][DEV NOTE] Initial turbo project and add tailwindcss library](https://dev.to/codeforstartup/dev-note-initial-turbo-project-and-add-tailwindcss-library-4iae/edit)


- 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
](https://turbo.build/repo/docs/handbook/tools/prisma)

**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](https://turbo.build/repo/docs/handbook/tools/prisma)
[2] [Prisma getting started](https://www.prisma.io/docs/getting-started)