Setting up a PostgreSQL database with Prisma

Create a project

Either create a barebones project such as with the following:

$ mkdir prisma_project

Or create a project using your favorite framework and then cd into the project directory.

$ cd prisma_project

Install Prisma CLI

$ npm install prisma --save-dev

Initialize Prisma Project

$ npx prisma init

Connect your database

Initialization will create a prisma directory with a schema.prisma file inside of it and it will also create a .env file in the root directory of your project. The .env file will look like this:

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

• Replace johndoe with your user, try postgres if you're unsure.

• Replace randompassword with your password. If you don't know the password, try the password for your OS user.

• Replace mydb with the name that you want to give your database

Create a model

In the schema.prisma file in your prisma directory, create a database model. Here's an example:

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

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

model User {
    id String @id @default(uuid())
    username String @unique
    passwordHash String
    userAuthToken String @unique
    createdAt DateTime @default(now())
    updatedAt DateTime @updatedAt
    role Roles @relation(fields: [roleId], references: [id])
    roleId Int
}

model Roles {
    id Int @id @default(autoincrement())
    name String @unique
    User User[]
}

Map the database model to the database

In the project directory, type the following:

$ npx prisma migrate dev --name init

View database in Prisma Studio

To open the Prisma GUI, use the following command:

$ npx prisma studio