DEV Community

Nikolas Burk for Prisma

Posted on • Updated on

How to set up a free PostgreSQL database on Heroku

⚠️ Update: Heroku does not offer hosting free PostgreSQL database any more. Instead, you can set up a PostgreSQL database for free with Supabase.

Heroku offers a free plan for hosting PostgreSQL databases. This can be handy if you're getting started with a new project or "just quickly need a hosted database" for experimentation or prototyping.

This guide explains how to quickly setup a free PostgreSQL database on Heroku. You can learn more in the Heroku Postgres documentation.

TLDR

In order to create a new database on Heroku, you first need to create an app in your personal dashboard. Then you can attach a Heroku Postgres instance to it as a resource.

Step 1: Log into Heroku

Navigate your browser to https://id.heroku.com/login and log in. If you don't have an account yet, you can sign up via the Sign Up button below the login form:

Step 2: Create a new Heroku app

Once you see your personal app dashboard, you can create a new app. If your dashboard is currently empty, you can click the Create new app button. Otherwise click the New button in the top-right corner and select Create new app:

Since you only need the app to get access to your database, the App name doesn't really matter and you can choose whatever you want. In the screenshot below, get-my-db is used as an App name:

If you want, you can also change the Region so that the database is hosted closer to where you are located.

Once you've provided the App name, you can click the Create app button.

Step 3: Add a PostreSQL database

To attach a PostgreSQL database to the app you just created, you need to navigate to the Resources tab in the header of your newly created app's dahsboard. Then type Heroku Postgres into the Add-ons search field.

When shown, select the suggested Heroku Postgres add-on from the dropdown:

The next popup asks you to choose a pricing plan for the database. Select the Hobby Dev - Free plan and click Provision:

Congratulations, you now created a free PostgreSQL database 🎊

Step 4: Access the database credentials (and connection URL)

To find the credentials and the connection URL for the PostgreSQL database, you need to navigate to the Resources tab in your app's dashboard again and select the Heroku Postgres resource:

This brings you to the configuration screen of the your PostgreSQL database:

Now select the Settings tab in the header of that screen:

Here, you can click the View Credentials button to see the credentials of your PostgreSQL database:

You can use these credentials to connect to your PostgreSQL database from any PostgreSQL client, e.g. psql (CLI), TablePlus or Postico (GUIs).

For further reading, you can check out the Heroku Postgres documentation.

If you want to use this database in a Node.js or TypeScript application, be sure to check out Prisma as an alternative to traditional ORMs. Get started with Prisma here.

Top comments (13)

Collapse
 
rodbove profile image
Rodrigo Mello

Nice! I was looking for a free hosting so I could demo an integration to a customer on my job and that will come in handy, thanks for the post!

Note for anyone also using Postgresql on Heroku, SSL is enabled by default so if you get an error regarding that when trying to connect through your application, remember to check how to disable that option when connecting. In my case using Node.js the adding the following property on my connection options resolved:
ssl: { rejectUnauthorized: false }

Collapse
 
masudndatsu profile image
Masud-Ndatsu

Thanks a lot man

Collapse
 
adimaralimuddin profile image
adimaralimuddin

thank you one million!

Collapse
 
reneibarrad profile image
Isra´Ibarra

THE CREDENTIALS RESET BY CERTAIN TIME

Collapse
 
brian1150 profile image
Brian-1150

Thank you @nikolasburk for the helpful article!

Collapse
 
cindyguan28 profile image
Xin Guan

Hi, thank you for the tutorial. Heroku doesn't offer free plan any more. Do you have other suggestion for hobby developer?

Collapse
 
nikolasburk profile image
Nikolas Burk

Hey there! Absolutely, the top of the page now links to another article that explains how to set up a free PostgreSQL database on Supabase.

Collapse
 
blazestudios23 profile image
Andrew Obrigewitsch

When I try to connect to a free account with Prisma I get the following errors:

Prisma Migrate could not create the shadow database. Please make sure the database user has permission to create databases. More info: pris.ly/d/migrate-shadow. Original error:
Database error
Error querying the database: db error: ERROR: permission denied to create database
0: sql_migration_connector::flavour::postgres::sql_schema_from_migration_history
at migration-engine/connectors/sql-migration-connector/src/flavour/postgres.rs:279
1: sql_migration_connector::sql_database_migration_inferrer::calculate_drift
at migration-engine/connectors/sql-migration-connector/src/sql_database_migration_inferrer.rs:57
2: migration_core::api::DevDiagnostic
at migration-engine/core/src/api.rs:95

Collapse
 
nikolasburk profile image
Nikolas Burk

Hey Andrew 👋 thanks for the comment! This is likely because Heroku doesn't provide enough access privileges on the PostgreSQL DB server to create the shadow database which is needed during the migration.

You can resolve this by creating a second database by following the instructions in the article once more, and then add the URL of the second DB as the value for the shadowDatabaseURL field on the datasource block as explained here:

datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}
Enter fullscreen mode Exit fullscreen mode
Collapse
 
jeanphilippe_boudreault_ profile image
Jean-Philippe Boudreault

There are better options that Heroku. As Isra mentioned Heroku changes the credentials periodically. ElephantSQL has a free tier that can do the trick.

Collapse
 
vkostunica profile image
vkostunica

I get same shadow error on ElephantSQL. Is there free postgres cloud hosting that does not need two database urls?

Error: P3014

Prisma Migrate could not create the shadow database. Please make sure the database user has permission to create databases. Read more about the shadow database (and workarounds) at https://pris.ly/d/migrate-shadow

Original error:
db error: ERROR: permission denied to create database
   0: migration_core::state::DevDiagnostic
             at migration-engine/core/src/state.rs:250
Enter fullscreen mode Exit fullscreen mode
Collapse
 
felipelx profile image
Felipe Lisboa

very good explanation and very useful! thanks to share!

Collapse
 
quocthinhle profile image
Le Quoc Thinh

Thank you, didnt expect it this easy.