DEV Community

Cover image for How to create tables on Heroku Postgresql

How to create tables on Heroku Postgresql

danielmabadeje profile image Daniel Mabadeje ・3 min read

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{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.
You have created your first table on Heroku..
If you like this article, feel free to check my profile for more amazing content
also check me on github as:

Discussion (0)

Editor guide