DEV Community

How to set up a free PostgreSQL database on Heroku

Nikolas Burk on February 26, 2020

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

Read full post

Rodrigo Mello • Dec 15 '20

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 }

Masud-Ndatsu • Aug 13 '22

Thanks a lot man

Isra´Ibarra • Aug 6 '20

THE CREDENTIALS RESET BY CERTAIN TIME

adimaralimuddin • Jul 31 '22

thank you one million!

Brian-1150 • Aug 9 '22

Thank you @nikolasburk for the helpful article!

Xin Guan • Mar 8 '23

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

Nikolas Burk Prisma • Mar 14 '23

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.

Andrew Obrigewitsch • May 31 '21

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

Nikolas Burk Prisma • Jun 4 '21

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")
}

Jean-Philippe Boudreault • Mar 16 '22

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

vkostunica • Jun 21 '22

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

Le Quoc Thinh • Jul 23 '21

Thank you, didnt expect it this easy.

Felipe Lisboa • Nov 2 '21

very good explanation and very useful! thanks to share!