DEV Community

Cover image for How to create tables on Heroku Postgresql
Daniel Mabadeje
Daniel Mabadeje

Posted on

How to create tables on Heroku Postgresql

So you want to deploy this your idea or project for demo on heroku. Probably, you are deploying from Github, then it’s time for you to migrate or create postgres databases and tables, but then you get confused especially when it’s not a laravel application where you can use the console to migrate tables. so you check the documentation and you get even more confused. this article is for you.
I encountered this some time ago and I felt bad so I decided to do an article on this.
Here we are going to cover everything concerning database and table creation starting from :

  1. Adding heroku-postgres as an add-on
  2. Attaching it as a database
  3. Getting configuration
  4. Creating table using dataclips

In this article I believe you have a heroku account and you have add your project there so we move over to adding heroku-postgres as an add-on

Adding Heroku-Postgres as an add-on

On the tabs menu you will see the resources link, when clicked should take you to something like this

https://dashboard.heroku.com/apps/{your_app}/resources
you will see something like this..
Alt Text
so click on find more add-ons and search for Heroku postgres you will see this
Alt Text
click on heroku postgres which will take you to this page..
Alt Text
click on install heroku postgres and add it to your project.
now we move over to attaching it as a database

Attaching as a Database

if you check your resources page you will see that heroku-postgres has been installed and you will see a button that says Attach As Database, click on it which will give you your configuration details. Once this is done, check your settings page and click reveal config var you will a url added there. It contains your database name, password, database host, database name. those are the credentials you add to your app to connect to database.

Creating Tables Using Dataclips

If you want to know about dataclips you could check here.
So “Heroku Dataclips enable you to create SQL queries for your Heroku Postgres databases and share the results with colleagues, third-party tools, and the public” — heroku.
so see them as saved queries or as endpoints or as shortcodes (for wordpress people) where you write your SQL queries and they are saved and can be ran.
So we going to use this to create a users table with a dataclip
Note: Datclips by default allow users to read only i.e you can write queries to select data from database but you can’t manipulate it.
for you to alter this setting, you need to write this

set transaction read write; 
Enter fullscreen mode Exit fullscreen mode

before writing your queries.
Now back to creating our table.
so when we select new dataclip it takes us to a page we can write our queries
we select the project we want the query to execute in and also give a title to our dataclip. then we write our query.

CREATE TABLE user( user_id serial PRIMARY KEY, username VARCHAR ( 50 ) UNIQUE NOT NULL, password VARCHAR ( 50 ) NOT NULL, email VARCHAR ( 255 ) UNIQUE NOT NULL, created_on TIMESTAMP NOT NULL, last_login TIMESTAMP );
Enter fullscreen mode Exit fullscreen mode

once this written click on save and run.
Voila!!
You have created your first table on Heroku..
Congrats!!
If you like this article, feel free to check my profile for more amazing content
also check me on github as:

https://github.com/DanielMabadeje
Thanks

Top comments (3)

Collapse
 
_johlym profile image
Johnathan Lyman

Tables created using Dataclips do not persist; this should not be recommended as a means for creating a table within Heroku Postgres. Instead, create tables the correct way using the Heroku CLI:

$ heroku pg:psql -a appname
Enter fullscreen mode Exit fullscreen mode
Collapse
 
danielmabadeje profile image
Daniel Mabadeje

Very nice contribution Johnathan
But this article was not focused on the CLI aspect
It was actually focused on finding an alternative to Heroku CLI

Amyways I appreciate your contribution

Collapse
 
nelwhix profile image
Nelwhix

Johnathan, please can you explain how to create a table using CLI indepth