Prisma for beginners
Tags: webdev, node, database, postgres
Published: 2021-08-28 18:31:01

#### About Prisma

> Prisma is an ORM with an introspect and migrations feature that reflects changes across SQL definitions and GraphQL Types.

When you're working with GraphQL , it can become tedious to make changes to your Type Schema and subsequently make those very same changes to your Database Schema. Therefore it becomes a necessity to find a tool that can persist these changes.

However , Prisma needn't always be used with GraphQL. It's a great tool on its own even if you're building standard REST APIs.

**Prerequisites**

- PostGRES Installed locally (psql)
- NodeJS Installed
- npm Installed


#### Setting up Prisma


Initialise a Node Project using `npm`.

```
npm init -y
```

Install the Prisma CLI Tool that is used to generate the `schema.prisma`
file.

```
npm install @prisma/cli --save-dev
```

Install the Prisma Client that is used to interface with the Database

```
npm install @prisma/client
```

Let's generate a Prisma project next.

```
npx prisma init
```

This generates a _prisma_ folder containing the `schema.prisma` file.

Prisma differs in the sense that it offers its very own `schema.prisma` file with a specialised syntax for defining your schema. However , we'll not be editing this schema file now. We'll generate a schema using SQL first.

We've also generated a `.env` file. You can edit the `DATABASE_URL` variable to connect to your PostGRES Database.

```
#.env

DATABASE_URL="postgresql://username:password@localhost:5432/dbname?schema=public"
```

Replace `username` , `password` with your PostGRES credentials and replace `dbname` with any name you wish to give to your Database. (Database does not have to be created yet) I will name this Database as 'test'.

## Migrations and Introspection


> Prisma offers two approaches to persist changes across Type Schema and Database Schema. 

1. Introspect (SQL → Data Model)
2. Migration (Data Model → SQL)

#### Migration

Let's generate a Data Model in our `schema.prisma` file first , using SQL. 
Login to psql. Create the Database first.

```SQL
-- Create a Database
CREATE DATABASE test
``` 

Connect to the database using `\c test`. Proceed to execute the following SQL commands to generate our `users` and `posts` table along with our custom user type.


```SQL
-- Create a custom type
CREATE TYPE "user_role_enum" AS ENUM ('user', 'admin', 'superadmin'); 

-- Create a table
CREATE TABLE "users"(
    "id" BIGSERIAL PRIMARY KEY NOT NULL,
    "name" VARCHAR(255) NOT NULL,
    "email" VARCHAR(255) UNIQUE NOT NULL,
    "role" user_role_enum NOT NULL DEFAULT('user')
);

-- Create a table
CREATE TABLE "posts"(
    "id" BIGSERIAL PRIMARY KEY NOT NULL,
    "title" VARCHAR(255) NOT NULL,
    "body" TEXT,
    "userId" INTEGER NOT NULL,
    FOREIGN KEY ("userId") REFERENCES "users"("id")
);

```

#### Generating Data Models using Introspect

Once the table has been succesfully created , run the introspect command to generate Prisma Models.

```
npx prisma introspect
```

This will generate an equivalent Data Model in the schema.prisma file.

```
//schema.prisma
model posts {
  id     Int     @id @default(autoincrement())
  title  String
  body   String?
  userId Int
  users  users   @relation(fields: [userId], references: [id])
}

model users {
  id    Int            @id @default(autoincrement())
  name  String
  email String         @unique
  role  user_role_enum @default(user)
  posts posts[]
}

enum user_role_enum {
  user
  admin
  superadmin
}
```

As you can see , with the `introspect` feature , we've managed to convert our SQL commands into an equivalent Data Model.

Prisma also provides a GUI tool called _prisma studio_ to view and interact with our Database.

```
npx prisma studio
```

This will open the GUI tool in your browser.

## Migrations in Prisma

We've seen how we can convert our SQL commands to Data Models. Now let's look at how we can do the opposite. Delete your Database.

```SQL
DROP DATABASE test;
```

This would have deleted the Database. Enter `\l` into psql to check if it has been successfully deleted.

Migrations are used to generate SQL commands for given Prisma Data Models. Describe your model in the `schema.prisma` file as necessary.

```Javascript

model User {
  @@map(name: "users")
  id Int @default(autoincrement()) @id
  uuid String @default(uuid()) @unique
  email String @unique
  name String?
  role UserRole @default(USER)
  createdAt DateTime @default(now()) @map("created_at") 
  updatedAt DateTime @updatedAt @map("updated_at")
  posts Post[]
}

model Post {
  @@map(name: "posts")
  id Int @default(autoincrement()) @id
  uuid String @default(uuid()) @unique
  title String
  body String?
  user User @relation(fields: [userId], references: [id])
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  userId Int
}

enum UserRole {
  USER
  ADMIN
  SUPER_ADMIN
}

```
You can read more about the Data Model's syntax [here](https://www.prisma.io/docs/concepts/components/prisma-schema/data-model#models-in-prisma-client) 

The Model above is a slightly modified version of the model we generated previously.
Let's generate a table and its equivalent SQL commands next.

Run the following command to generate a Prisma migration.

```
npx prisma migrate dev --preview-feature 
```

You can give your migration any name you wish to. This will generate a `migrations` folder with all the necessary documentation and commands that were run in sequence to generate the SQL tables.

*Migration can create a Database if the Database doesn't exist already*

We can check if our tables were successfully created using `psql`.

Therefore we've looked at the two main approaches to persisting changes in schema.
Finally , Let's look at the _Prisma Client_.

## Prisma Client

Run the following command to generate a Prisma Client

```
npx prisma generate
```

The Prisma Client helps us interact with our PostGRES Database. 

```
const { PrismaClient } = require("@prisma/client");

const prisma = new PrismaClient();

async function main() {
	const users = await prisma.users.findMany();
	console.log(users);
}

main();
```

We can send queries to perform `CRUD` operations on our Database. Read more about these operations  [here](https://www.prisma.io/docs/concepts/components/prisma-client/crud).

**Note:** Don't forget to generate the Prisma Client every time an introspection or migration is performed.