# EP 2: How to Add a Postgres Database and Generate Models in a Redwood App **Cover Image Credits**: Photo by *[Markus Spiske](https://unsplash.com/@markusspiske)* ## Quick Summary In our **[previous post](https://blog.evansibok.com/ep-1-bootstrapping-a-redwoodjs-application)**, 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](https://www.prisma.io/). ## 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](https://www.heroku.com/) and create one. Then log in to your account and you'll be taken to a dashboard that looks something like this: ![heroku-dashboard.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1632940122652/Cx50vUhch.png) 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 ![new-app.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1632940148309/o0N7bmoWT.png) - __Step 2__: Enter a name for your app, pick your region and click on __`Create app`__ ![create-new-app.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1632940276262/Wbh3tOM9T.png) - __Step 3__: You will be redirected to the __`Deploy`__ tab for your newly created application. ![create-app-success.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1632940454328/URAfktAWL.png) ### Adding a Postgres Database to our Heroku App - __Step 4__: Click on the __`Resources`__ tab ![go-to-resources.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1632940476971/81fxDo8fG.png) - __Step 5__: Find the __`Add-ons`__ field, search for and select __`Heroku Postgres`__ ![select-postgres.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1632940494212/cnYEhltI8.png) - __Step 6__: Click __`Submit Order Form`__ on the modal that appears and a database will be created for you. 🎉 ![submit-order.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1632940532116/q0KlN4MyA.png) ![new-db.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1632940547068/vdGPTvM0c.png) ## 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. ![click-db-one.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1632940576586/_1jPIi5tm.png) - __Step 8__: On the datastore screen, click the __`Settings`__ tab ![click-settings.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1632940595183/SEYR5t8lM.png) - __Step 9__: Click the __`View Credentials`__ button to expose our database configurations ![datastore-db-one.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1632940623254/ADlYzNXW9.png) - __Step 10__: Locate the __`URI`__ Label and copy the URL in front of it as our database URL ![db-credentials.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1632940696397/jIlejRrJX.png) ### 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](https://dbdiagram.io/) 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: ![thankful-model.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1632940810977/EpYVbPGhK.png) 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: ![prisma-schema-file.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1632940877399/A3cmg0IpG.png) - 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](https://www.prisma.io/docs/concepts/components/prisma-schema). ### 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: ![migrate-error.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1632940964471/FsZXdxVtI.png) 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. ![migrations-success.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1632941103009/iHbG2ZWWd.png) ### 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-dataclips.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1632941174506/GT2LIJafN.png) - Click on __`Create Dataclip`__ button ![create-dataclips.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1632941191228/P3oRw4T7HS.png) - Locate the __`Schema Explorer`__ at the right of the screen. If you see your tables listed there, congratulations, your migration was successful. 🎉 ![schema-explorer.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1632941214941/XO82LqMEw.png) 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](https://dbeaver.io/) or Redwood's recommended [Beekeeper Studio](https://www.beekeeperstudio.io/) and [TablePlus](https://tableplus.com/). ### Resources - [SQL Table Relationships Explained](https://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561) - [Prisma Migrate Docs](https://www.prisma.io/docs/concepts/components/prisma-migrate) - [Heroku Website](https://www.heroku.com/) ### GitHub Repo [Thankful App](https://github.com/evansibok/thankful)