EP 2: How to Add a Postgres Database and Generate Models in a Redwood App

Cover Image Credits: Photo by Markus Spiske

Quick Summary

In our previous post, we learned how to bootstrap a redwood app from scratch. Continuing from there, in this tutorial we are going to learn how to add a Postgres database using Heroku and Generate models for our journaling application using Prisma and Redwood commands.

Goal

At the end of this tutorial, you will learn how to provision a Heroku Postgres Database, connect the database to our Redwood app, generate GraphQL schemas, and the logic needed to perform CRUD operations for all our models.

Prerequisites

This tutorial assumes that you have a basic understanding of Prisma ORM.

Creating our Postgres Database on Heroku

Heroku is a Platform-as-a-service tool for building and managing your application infrastructure in the cloud. You can host your apps on Heroku and get a live URL to access them.

Heroku also has other tools like Heroku Postgres which is a cloud-managed Postgres database service. This is where we'll create our database for this app.

If you don't already have an account on Heroku, head on to Heroku Website and create one. Then log in to your account and you'll be taken to a dashboard that looks something like this:

In Heroku, your created applications will appear within the section with the red box. Next we will create an application on Heroku to host our database.

Creating a new application on Heroku

Follow the steps below to create a new application on Heroku.

Step 1: From your Heroku dashboard, click on New button and select Create new app from the options
Step 2: Enter a name for your app, pick your region and click on Create app
Step 3: You will be redirected to the Deploy tab for your newly created application.

Adding a Postgres Database to our Heroku App

Step 4: Click on the Resources tab
Step 5: Find the Add-ons field, search for and select Heroku Postgres
Step 6: Click Submit Order Form on the modal that appears and a database will be created for you. 🎉

Connecting Postgres Database to our Redwood App

To use our database in our Redwood app we have to locate and get the URL to our newly created Heroku Postgres database.

Getting our DATABASE URL from our Heroku App

Step 7: From our application dashboard (Overview) tab, click on our newly created database and you'll be taken to the datastore screen for this database.
Step 8: On the datastore screen, click the Settings tab
Step 9: Click the View Credentials button to expose our database configurations
Step 10: Locate the URI Label and copy the URL in front of it as our database URL

Adding our DATABASE URL to the environment variable in our Redwood App

At the root of our thankful project, you'll find a .env file. Copy the URL From Step 10 above and paste it as the value for the DATABASE_URL variable inside our .env file.

Generating GraphQL Schemas

Now, we are going to create and generate our database tables which we will persist to the Postgres database we just created.

Personally, before I start writing my database tables, I use a tool called DB Diagram to design and understand my database relationships visually.

As we are building a journaling application, we reckon we will need the following features:

user account creation
notes creation where users can be mentioned inside of notes, plus the ability to update, query, and delete notes.

So with this in mind, we came up with the following database model using DB diagram:

A straightforward explanation for the above model goes like this:

users table (user creates account)
notes table (user creates a note and becomes the author of that note)
mentions table (user can mention another user when creating a note)

Since a user can be mentioned in multiple notes by different authors, and we can have multiple mentioned users in a single note, then it makes sense to have a mentions table to connect mentioned users to the notes they are mentioned in.

For more on table relationships, refer to the resources section in this post.

Replacing Redwood default DB engine with Postgres

To locate our schema file, from inside the api directory in our root project, open up the db directory and you'll find a schema.prisma file. The content of our default schema.prisma file looks like this:

Change the datasource db provider from sqlite to postgresql

If you look closely, you'll see that the datasource db url already reads the database URL from our environment file with the DATABASE_URL key. Since we already updated this to our Heroku Postgres URL, we are good to go.

Adding User, Mention and Note models

In our schema.prisma file above we have a default UserExample model, replace the default model code in our file with the code below:

// api/db/schema.prisma

model User {
  id        String    @id @default(uuid())
  firstName String
  lastName  String
  email     String    @unique
  username  String    @unique
  image     String
  password  String
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  notes     Note[]
  mentions  Mention[]

  @@map("users")
}

model Note {
  id        String    @id @default(uuid())
  content   String
  author    User      @relation(fields: [authorId], references: [id])
  authorId  String
  mentions  Mention[]
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt

  @@unique([authorId])
  @@map("notes")
}

model Mention {
  id                String   @id @default(uuid())
  mentionedUser     User     @relation(fields: [mentionedUsername], references: [username])
  mentionedUsername String
  note              Note     @relation(fields: [noteId], references: [id])
  noteId            String
  createdAt         DateTime @default(now())

  @@unique([mentionedUsername, noteId])
  @@map("mentions")
}

The code above creates User, Note, and Mention models for our database schema. To understand how we created the schema above please refer to the Prisma Schema Docs.

Generating Database Migrations and Models for our Redwood App

Now we have to migrate our database models so they can be in sync with Prisma and write to our Heroku Postgres database.

Run the command below to migrate our database:

yarn rw prisma migrate dev

Note: You are likely to run into an error like this:

If you do, do not fret. I will walk you through resolving it.

If you encounter the error above, refer to the section directly below on how to fix it, otherwise continue to the next section.

Resolve Prisma Migration Error

In Prisma, when we run development-focused commands like prisma migrate dev, Prisma uses a second temporary database called shadow database to monitor and detect problems.

The shadow database is not required when running production commands like prisma migrate deploy.

Another caveat is that since we are using a cloud database like Heroku Postgres we will have to create this shadow database manually. What better way to do that than to use our existing application created through Steps 1 through 3 above.

Please run through the database creation steps from Step 4 through 10 to create a second database on our existing Heroku application and get the URL for this database ready for use.

Once you have successfully created the shadow database, follow the steps below to add it to our Redwood app:

Add this line shadowDatabaseUrl = env("SHADOW_DATABASE_URL") inside your schema.prisma file just below the url = env("DATABASE_URL") line
Go to your .env file and add the variable SHADOW_DATABASE_URL to it
Copy the URL for your shadow database from the second Heroku Postgres database dashboard and paste it as the value of the SHADOW_DATABASE_URL variable.
Now re-run the command
```
yarn rw prisma migrate dev
```
Put in the name of your migration and prisma will perform its magic and create migrations for your database.

Viewing our database in Heroku Dashboard

To confirm that our models were successfully migrated and the tables are created successfully in our Heroku Postgres database we need to take a look at our database inside our Heroku application.

From our database datastore, click on Dataclips tab
Click on Create Dataclip button
Locate the Schema Explorer at the right of the screen. If you see your tables listed there, congratulations, your migration was successful. 🎉

Congratulations! You have successfully added a Postgres database to your Redwood app and created your tables. 🥳

For a job well done, you deserve a ☕.

Conclusion

Summary

In this article we learned:

how to create a cloud Postgres database on Heroku
adding our cloud database to our Redwood app
resolving Prisma migrate error with our cloud database
performing database migration using the yarn rw prisma dev command, and
viewing our migration status on Heroku.

Note: For a more concise way of managing your SQL database, I recommend using a more compact database management system like DBeaver or Redwood's recommended Beekeeper Studio and TablePlus.

Resources

GitHub Repo

Thankful App

EP 2: How to Add a Postgres Database and Generate Models in a Redwood App

Quick Summary

Goal

Prerequisites