Setting up PostgreSQL for Prisma v2

This story is about setting up a local database, PostgreSQL which is needed when working with Prisma v2.

Image from prisma.io

As Prisma announced version 2 release, I’ve also managed to migrate our hackatalk-server which used sequelize previously.

I’ve managed to create another graphql server with Prisma v2 and I’ve planned to change previous hackatalk-mobile repository monolithically and changed to hackatalk. This repository now has two projects which are client made by react-native and the server which is a graphql server powered by Prisma.

Image from https://github.com/dooboolab/hackatalk

To contribute to our server project, you can read the CONTRIBUTING guide that’s provided for only the server. However, these days, many people still ask me how to install the PostgreSQL so that we can run the server project locally. Therefore, I’d like to share how to do that now.

The conservative method to install PostgreSQL

Followed by the article provided by digitalocean, “How to install and use postgresql on Ubuntu 20.04”, I’ll summarize how to install PostgreSQL via terminal.

1. Write the below commands in your terminal.

// Ubuntu
$ sudo apt update
$ sudo apt install postgresql postgresql-contrib
// Mac
$ brew install postgresql
$ postgres -D /usr/local/var/postgres  // <= start service manually

Alternatively, you can install Postgres with docker and kitematic.

Add postgres password in POSTGRES_PASSWORD field in Settings and that will be your postgres account’s password when you installed this through kitematic.

Check the IP and Port to connect to your PostgreSQL installed via kitematic

Also, set POSTGRES_PASSWORD for database password in Environment Variables.

2. Access Postgres

$ sudo -u postgres psql

3. Creating a new role

$ sudo -u postgres createuser --interactive
// Typing above command will ask you additional information to write

4. Create a new database

$ sudo -u postgres createdb hackatalk
// To create database via terminal. You can either do below.

Now you have variables to setup PostgreSQL in Prisma2

Followed by our contributing guide, copy dotenv/dev.env to dotenv/.env . We’d like to run locally with manually created PostgreSQL user.

cp dotenv/dev.env dotenv/.env

Then create a database as you like and grant privileges.

CREATE DATABASE hackatalk;
CREATE ROLE postgres WITH LOGIN NOSUPERUSER INHERIT CREATEDB NOCREATEROLE NOREPLICATION PASSWORD 'dooboolab0!';
GRANT CONNECT ON DATABASE postgres TO hackatalk;
GRANT ALL PRIVILEGES ON DATABASE postgres TO hackatalk;

Then your DATABASE_URL should be the following.

DATABASE_URL="postgresql://hackatalk:dooboolab0!@localhost:5432/postgres?schema=hackatalk"

Migrate your database.

npx dotenv -e ./dotenv/.env -- prisma migrate up --experimental

The database has been migrated and installed necessary tables

You are all set! Let’s run yarn local in server directory.

Run server on localhost:40000

Try signup mutation

You can see the result by running yarn studio which is running prisma studio --experimental .

You can see the inserted data 🙌

Please join us and work with us in HackaTalk. We want to communicate with more people and grow together (not individually 🙏).

Thanks for taking the time to setup Prisma with PostgreSQL.