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 selectCreate new app
from the optionsStep 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
tabStep 5: Find the
Add-ons
field, search for and selectHeroku 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
tabStep 9: Click the
View Credentials
button to expose our database configurationsStep 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
fromsqlite
topostgresql
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 yourschema.prisma
file just below theurl = env("DATABASE_URL")
line - Go to your
.env
file and add the variableSHADOW_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 theSHADOW_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
tabClick on
Create Dataclip
buttonLocate 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.