Integrating Prisma ORM with PostgreSQL and Express

In this blog, we will be working inside of a Node.js application. We will be learning how to integrate Prisma - an ORM for Node.js, along with Express and PostgreSQL.

Lets get everything set up before we move any further. Make sure you have these following packages installed on your computer before continuing.

1. Node — We will need Node.js installed on our computers so make sure you have that installed by running the following command.

node -v

If that comes back with a message showing the version of node you have installed, you will be good to go. If not, you will need to download and install Node.js here.

By downloading and installing Node.js, it will install npm alongside it, which is another package we will need for this project. You can check to make sure you have it installed with the following command.

npm -v

If for some reason you don’t have it installed, you can install npm globally with the following command.

npm install -g npm

2. PostgreSQL — We will be using a local Postgres database for this project, so you will need to download and install it here.

Once you have Node.js, npm, and PostgreSQL installed, we can continue on with the project.

Step 1) The first thing we will be doing is setting up our local Postgres database with the SQL shell that gets installed when you install PostgreSQL.

Go to the search bar on your computer and type in psql and run it. You should have a terminal pop up that looks like this.

SQL Shell (psql)

Keep pressing enter on the prompts until it asks you for your password.

Password prompt in psql

Once you enter your password, the shell will look like this.

psql running once password is entered

The next step is to create the actual database we will work with. Enter the following command in the shell and press enter, this will create a database called prisma.

Make sure you have a semicolon at the end of the command or it will not work. It took me a couple tries the first time I worked with Postgres because of this, lol.

CREATE DATABASE prisma;

Create database called prisma

Once you have ran that command, you should get a message back saying, “CREATE DATABASE”.

Run Create database command

Now, lets check to make sure our database was created. Run the following command in the psql shell.

\l

This will give you a list of all of your databases. You should see one called prisma.

Database “prisma” has been created

Our PostgreSQL database has now been created and is now ready to be used.

Step 2) We will now begin creating the actual Node.js app. I am using VSCode, but you can use whatever text editor or IDE you like.

Lets first create a folder for this project, you can call it whatever you want.

Navigate wherever you want your project to be located at on your computer and run the following command.

mkdir prisma-postgres

I am going to call it, prisma-postgres. Once you have created the folder, navigate inside of it in your terminal, I am going to be using the terminal inside of VSCode for the remainder of this blog.

The first thing we have to do is create the actual Node.js app, lets start by running the following command inside of our directory we just created.

npm init

It will give you a few prompts, just keep pressing enter for all of them. Once this is finished, you should see a package.json file in your directory.

We are now going to run the following command into the terminal to install express and node.

npm install express node

Now we can start working on our server.

Create a file in your main directory called index.js. We are going to set up a very basic express server for this project.

Express server setup

We are now going to install a dev dependency to run the server. Enter the following command into your terminal.

npm install nodemon -D

This makes it so we don’t have to restart the server every time we make changes to our app.

Lets go ahead and start our server up by running the following code in the terminal.

nodemon index.js

After running this command, we should see this output in the terminal. Our server is now running on localhost:3000

Terminal output after running nodemon index.js

Step 3) Setting up our database connection string.

We are now going to install another dependency so we can connect to our database. Run this command in the terminal.

npm install dotenv

Now create a file in your main directory called .env

We will use this file to connect to our database using our connection string.

We will use this formatted string to connect to our database

postgres://YourUserName:YourPassword@localhost:5432/prisma

Make sure to put your username and password in the string to be able to connect to your db.

Then, in our .env file, we will declare our DATABASE_URL variable and set it equal to our connection string like so.

Postgres connection string

Now all we have to do to finish setting up our env file and connection string is load it in our server. All we have to do is add two lines of code to our index.js to accomplish this.

Load environment variables into our server

As you can see, on line 2 we have required dotenv, and on line 5 we configured our env file. We have now loaded our .env file, and we should now be ready to connect to our database.

Step 3) Setting up Prisma.

We will start by installing the prisma client. Run the following command in the terminal.

npm install @prisma/client

We will now install prisma as a dev dependency.

npm install prisma -D

Now that we have prisma installed, we can now initialize it by running the follwing command in the terminal.

npx prisma init

This will generate a prisma folder with a file called prisma.schema inside of it. This file is where we will create our database models.

Lets create a user model with and id, name, email, and password in the prisma.schema file.

model User {
  id       Int @id @default(autoincrement())
  name     String
  email    String
  password String
}

Your schema file should now look like this.

User schema

Now that we have our model created in the schema file, we can now generate the prisma client by running the following command in the terminal. The prisma client is used to run queries on your db.

npx prisma generate

Now we will migrate our db which will create a sql file of our current schemas and sync our postgres db with any prisma schemas we have.

Run the following command in your terminal and enter any name you’d like when it prompts you for a migration name.

npx prisma migrate dev

Now we are ready to set up our prisma client to be used across our app. It is recommended by Prisma that we only create one instance of the prisma client and reuse it so we aren’t opening up a new db connection on every request to our database. You can read more about this here.

Create a folder in the main directory of our app called db. Inside of our db folder, create a file called prisma.js

We will use this file to create an instance of the prisma client, we will be able to reuse this instance across our whole app.

Put the following code in your prisma.js file.

Instantiate Prisma Client

Then we just have to import it wherever we want to use it. I am going to import it into the main server.

Import prisma client wherever you want to use it

Step 4) Adding data to the database.

If you have existing data that you want to add to your db, you can do so by seeding it, you can learn how to do this here. I am not going to be doing this today, I am just going to be adding a couple users through prisma studio.

In your terminal, type in the following command.

npx prisma studio

This will pull up your browser with a look into your database. Click on the User model and add a couple users. Here is the data I created. The ids will be automatically created so you don’t have to touch them. They will increment starting from 1.

Prisma studio database view

We now have data in our database and are now ready to make queries to our database using the prisma client.

Step 5) Making queries to our Postgres database.

In our main server, index.js, we are going to create a user and insert it into our database, then we will get all the users in our database and send them to the frontend to be rendered.

Inside of our main route inside of our server, we are going to insert the following code. This code is responsible for creating a user and inserting it into our database.

await prisma.user.create({
  data: {
    name: "John Doe",
    email: "jondoe@gmail.com",
    password: "123456"
  }
})

We are then going to insert the following code right under it, this will be responsible for getting all the users that we have stored in the database.

const users = await prisma.user.findMany()

We are then going to get the names of all of the users and store them in a list.

const names = users.map((user) => user.name)

After putting all of this in the main server, inside of our home route, our index.js should look like this.

Finished Express server

Make sure the application is running, if it’s not, input the following command into the terminal.

nodemon index.js

Now our server should be up and running at localhost:3000, or whatever port you set in your server.

When we navigate to our home page in the browser, your page should look something like this.

Final output

You can now see that we have 3 users in our database, this is because every time we navigate to our home page route, we create a new user.

Now go ahead, and refresh the page, you will see 4 users now. Ever time we refresh the page, another user will be created.

You wouldn’t create something like this in a real production application, but it does a good job at showing how these queries work.

This wraps up this blog for setting up Prisma with a PostgreSQL database in an Express Node.js application. I will be talking more about web development topics in my upcoming blogs, so be on the lookout. (: