# Prisma with PostgreSQL

"Ever wondered about a world where complex SQL queries are no longer needed to interact with databases?

Enter Prisma. With Prisma, creating tables and adding references becomes a breeze. Say goodbye to tedious SQL syntax and hello to streamlined database management.

So, what are we waiting for? Let's harness the power of Prisma to make our projects more efficient and enjoyable to work on. Ready to dive in?"

The Github repo for this is provided at the end.

### Initialize Project

Initialize your project by creating a folder with a name of your choice. For example, let's call it 'prisma-with-psql'.

Next, initialize the `package.json` file and set up TypeScript for the project by running the following commands in your terminal:

```bash
npm init -y
npm install typescript ts-node @types/node --save-dev
npx tsc --init
```

After running these commands, open the `tsconfig.json` file and uncomment the 'rootDir' and 'outDir' options. Set them as follows:

```json
"rootDir": "./src",
"outDir": "./dist"
```

The 'rootDir' option specifies the directory where you'll write your TypeScript code, while 'outDir' specifies the directory where TypeScript will compile your code to JavaScript.

### Install Prisma

To use Prisma in our project, we first need to install it. Prisma can be installed with the following command:

`npm prisma install`

This command will install the Prisma CLI and make it available for use in our project.

### Initialize Prisma

Now that we have Prisma installed, let's initialize it for our project. Run the following command in your terminal:

`npx prisma init`

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714728445509/cdbb7ca5-572d-4485-a9ef-c0bad78f4cde.png align="center")

After executing the command successfully, a folder named `prisma` will be created in your project directory. Inside this folder, you'll find the `schema.prisma` file.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714728584616/10eff221-8299-437e-8006-86ad1e3400bf.png align="center")

The `schema.prisma` file is where we define our database tables (or models). It also allows us to choose between various databases such as PostgreSQL, MongoDB, or MySQL. For this project, we'll proceed with PostgreSQL.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714728856173/be8350c0-7dea-4f38-8ae3-a4624e0622da.png align="center")

Next, we need to obtain the `DATABASE_URL` for our PostgreSQL database. You can use services like [neon.tech](http://neon.tech), [https://aiven.io/postgresql](https://aiven.io/postgresql), [or set up a local instance](https://aiven.io/postgresql) using Docker.

Once you have the `DATABASE_URL`, paste it into the `.env` file in your project directory.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714729225129/174dedf7-3a3f-42b8-a5da-4999661ebe9f.png align="center")

### Define and Create Tables

Without Prisma, creating tables in PostgreSQL requires executing complex SQL queries. For example:

```sql
CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(50), rollno VARCHAR(20) UNIQUE );
```

However, Prisma simplifies this process significantly. Let's define a `User` model in our `schema.prisma` file:

```bash
model User {
  id    Int    @id @default(autoincrement())
  name  String
  email String @unique
}
```

Finally, `schema.prisma` would look like this:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714730483849/72d10187-4bbb-4be4-a7a4-38cb225c37b9.png align="center")

In this `User` model:

1. `id` is an integer type that auto-increments and serves as the primary key.
    
2. `name` is a string type
    
3. `email` is also a string type, and we've specified it as unique.
    

After defining the model, we need to run migrations to create the corresponding table in the database. Migration files also help to keep track of changes made in the database, from time to time.

To do this, execute the following command:

`npx prisma migrate dev --name "name_of_the_migration"`

Your output in the terminal would look like this:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714731689120/559f57dc-15f2-4bb2-aed8-d7b25ca1b861.png align="center")

This command generates a migration file within the `migrations` folder of the `prisma` directory. Inside this file, a `SQL` query is written to create the `User` table in the database.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714731818763/eacc4fea-8804-456b-927f-204aade7c419.png align="center")

`migration.sql` would look like this

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714731834142/6c43ed5e-9ac5-4eac-b263-4eb9585b6c86.png align="center")

Once the migration is completed, you can view the `User` table in your database management tool or by running the command:

`npx prisma studio`

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714732396255/0c2aee3c-b4d7-4fcf-a183-76a179e7e7ec.png align="center")

This command launches Prisma Studio, a graphical interface for interacting with your database directly from the browser.

With Prisma, defining and creating database tables becomes a seamless process, allowing for efficient database management and development.

### Generate Clients

To generate Clients in Prisma, run the command:

`npx prisma generate client`

This command generates client libraries based on the models defined in your `schema.prisma` file. These client libraries provide functions for creating, reading, updating, and deleting entries in your database.

After running the command, you'll find the generated client code in the 'node\_modules/@prisma/client' directory.

### Making entries in DB

We have generated clients, now its time to use these, and making entries in the DB.

Create a `index.ts` file in `src` folder.

In the 'index.ts' file, import the PrismaClient:

```typescript
import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();
```

Create an asynchronous function to make the entry. The `index.ts` file will be like this:

```typescript
import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();

interface UserEntry {
    name : string,
    email : string
}

async function makeEntry(newUser : UserEntry) {
    const output = await prisma.user.create({
        data : {
            name : newUser.name,
            email : newUser.email
        }
    })
    console.log(output)
}

makeEntry({name : "Krishan Kumar", email : "random@gmail.com"})
```

Now, compile this file by using `tsc -b` command. After compilation, a `dist` folder will be created, containing the 'index.js' file. Run this file by `node dist/index.js` command.

The output would look like this:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714733864573/c1678f84-0458-4834-8bd4-92930d1e8cd8.png align="center")

Entry has been made to the Table User. We can check it.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714733972794/ba8ac436-4b77-4a6f-8f2b-56604333d017.png align="center")

Now we can add more entries to the database.

### Find Records in DB

To find entries, Prisma provides various functions like findUnique, findMany, findFirst.

1. findMany: This function returns all the `User` records.
    
2. findUnique: This function returns the unique record that matches the data.
    
3. findFirst: This function returns the first record that matches the data.
    

Create an asynchronous function for finding records in the `index.ts` :

* FindMany:
    
    ```typescript
    async function findManyRecords() {
      const output = await prisma.user.findMany();
      console.log(output);
    }
    
    findManyRecords();
    ```
    
    Output would be:
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714748626664/27e6fbdd-1366-4e20-84ff-e14d3c8e943f.png align="center")
    
* FindFirst:
    
    ```typescript
    async function findFirstRecord(name: string) {
      const output = await prisma.user.findFirst({
        where: {
          name: name,
        },
      });
      console.log(output);
    }
    
    findFirstRecord("Krishan Kumar");
    ```
    
    Output would be:
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714748826818/94ce60db-f7a0-4242-96f4-6badf0dd8e18.png align="center")
    
* FindUnique:
    
    ```typescript
    async function findUniqueRecord(id : number) {
        const output = await prisma.user.findUnique({
            where : {
                id : id
            }
        })
        console.log(output);
    }
    
    findUniqueRecord(2)
    ```
    
    Output would be:
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714749252734/5d2cf506-a327-45d2-9077-39b8826d1680.png align="center")
    

### Update Records in DB

To update records, Prisma provides functions like update and updateMany.

1. update: This function will find a single user and update it based on the data given.
    
2. updateMany: This function will update all the users that match the data given.
    

Create an asynchronous function for updating the record in `index.ts` file:

```typescript
interface updateUser {
  name?: string;
  email?: string;
}

async function updateRecord(id: number, updateUser: updateUser) {
  const output = await prisma.user.update({
    where: {
      id: id,
    },
    data: updateUser,
  });
  console.log(output);
}

updateRecord(3, { name: "Mahadev" });
```

The record having Id=3 before updating:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714750082438/d8abddd9-1ad8-4242-b19a-848731ac8c0a.png align="center")

The record having Id=3 after updating:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714750141790/69cb7c61-84e9-4dcc-b255-988d7c3d5876.png align="center")

### Delete Records in DB:

To Delete records, Prisma provides functions like delete and deleteMany.

1. delete: This function will delete a single user that matches the data.
    
2. deleteMany: This function can delete multiple records that matches the data, also it can delete all the records.
    

Create an asynchronous function for deleting the record in `index.ts` file:

* delete:
    
    ```typescript
    async function deleteRecord(id: number) {
      const output = await prisma.user.delete({
        where: {
          id: id,
        },
      });
    
      console.log(output);
    }
    
    deleteRecord(5);
    ```
    
    Deleted Record:
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714750795794/42c308b8-3b6a-4f18-85b9-bc13e22fef59.png align="center")
    
    Records present in the DB now:
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714750868687/da52722c-255a-44dc-90fc-4424f1f4a955.png align="center")
    
* deleteMany:
    
    ```typescript
    async function deleteMany() {
      const output = await prisma.user.deleteMany({});
      console.log(output);
    }
    
    deleteMany();
    ```
    
    Output would be:
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714750995522/1420a968-e2f7-4be7-a131-970e95355a4a.png align="center")
    
    You can also provide data here, to delete multiple records.
    

There are many other functions, that Prisma provides. You should explore them.

Github Repo: [https://github.com/Crimson-03/prisma-with-psql](https://github.com/Crimson-03/prisma-with-psql)

I hope you learned something new. Thank you, and make sure to give feedback and like.