Converting a Database from Sqlite to Postgres for Heroku

Photo by Campaign Creators on Unsplash

A few weeks ago I had written an application to be an information sharing hub for climbers, here it is. The database requirements were minimal and Sqlite worked perfectly well for it’s purpose.

The only issue I encountered was showing my app to non-technical folks. As it stood, one would have to view the code on my computer or download the code and run a few arbitrary commands to get a working version on their own system. Of course there are other options for hosting an sqlite site, here is a good article giving options if you’re interested.

However I already had an account setup on Heroku and I had heard the process for switching from sqlite to Heroku was relatively simple. That’s true, it is simple, but information on how to do so seemed to be scattered so I am now condensing my experience into an easy to follow guide if you find yourself in my shoes.

Keep in mind that my app was written in rails and so some points may differ slightly but the idea is the same as databases are language agnostic, generally speaking for our intents and purposes here.

Step 1:

If you haven’t, install postgres. Here is the site for that download.

Before going any further I was sure to rollback my current migrations as well. It is possible to complete all the steps without doing so because we’re not changing anything in our database, we’re switching to a whole new database that doesn’t exist yet.

Step 2:

Change your database.yml file and gemfile.

I’m going to avoid getting into the ins and outs of each line of code since that’s probably not why you’re here.

However I would advise that you at least read through the old code and try to grasp a base understanding of what is happening. For example; you have different databases for testing, development, or production environments and the path is also specified in each block.

Here is my code with the database set to sqlite:

And below is my code altered and ready to establish a postgres database with the same schema.

Make sure that your username is set how you want it to be, I am using ‘postgres’ as a catchall but it wouldn’t be wrong to use ‘postgres’ either. You will often see the username as the app name for example in my beta-climbers app the username would be beta_climbers. That is what rails will set the username as default if you initialize the backend to work with postgres.

If this app is going to be hosting actual client and is not just for fun, as mine is, then you will want to hide the password in an environment variable. We’re not going to get into that here. Otherwise you can just set the password to ‘password’ or leave it blank.

The last bit of code you will need to adjust will be your gemfile. Comment out or delete gem 'sqlite3'and replace it with gem 'pg'. Now you’re all set to move to the next step.

Step 3:

Create the database.

First try running rails db:create. If that works then you can move to the next step. Likely it didn’t work, and that’s okay.

The most common error I’ve seen at this point is

Fatal: role "username" does not exist 

Here is a great stackoverflow answer to help with that. If you’re in a hurry, this is the command I used that created the username is

createuser -s -r postgres

Make sure you are swapping the ‘postgres’ above with your username in the database.yml file.

Know that there is no silver bullet for this step. There are many variables going into the username that you use in your database.yml file. Like whether you used homebrew to install postgres or where your postgres files are located in your machine. Here is another great resource for setting up postgres, this time with homebrew.

Feel confident that the answer is out there and you will find it but it will take a little digging.

Assuming your user is created, let’s run rails db:create again. Everything should be working fine.

Step 4:

Migrate and Test.

Assuming your migrations are how you want them run: rails db:migrate.

Now make sure that all of your features are still working.

Step 5(bonus):

Moving your app to Heroku.

We have Heroku to thank for making this step the easiest of all.

I am assuming you have a heroku account and have downloaded the command line tools for heroku. If not here are the steps from Heroku.

a)

From the root directory of your application run heroku create. Your app will be assigned a generic name.

Let’s quickly rename our app to it’s proper name with heroku apps:rename beta-climbers.

b)

Now we can push the branch, master in my case, containing our production code to Heroku, for the world to see with git push heroku master.

…

…

This can take a few minutes…

…

c)

Follow the link provided and test the features of your app to make sure everything is working.

You might run into an error and upon further inspection you find it’s a 500 status code. In our case the fix for this is one final command, Again from the root directory, run heroku run rails db:migrate.

Note: You also want to run heroku run rails db:seed if you have seed data.

Fin

At this point you app should be working. If it is not remember, the answer is out there. You can be 99.9999% sure your problem has been encountered before.

I am also willing to help with issues that you have run into, assuming I have some time, shoot me an email hyrum.butler3@gmail.com, or find me on github.