Configuring a postgres database for your Rails app

Postgresql is an ACID-compliant object relational database managesment system. If you want to deploy your application via Heroku, you’ll have to use postgres, which is the only back-end database that platform supports. Unlike a lot of SQL databases, postgres has great built-in support for many unusual objects types like json, or currency. I’ve written this blog post to help other junior developers avoid some of the confusion I endured when transitioning from sqlite3 to postgresql.

Right now I’m using postgres in a React-Rails app that I’m building out for making and reviewing academic flashcards. If this sounds familiar, it’s because I’ve written a similar app in the past (and posted about it on this blog). My previous flashcards app used Rails as both the back-end and the view layer (along with some jQuery on the front-end for making fetch requests to the database). This time, I’m rewriting the application to use Rails 5 in api-only mode on the back-end and React on the front-end. Like many people, I’ve really come to like React and I think that React could provide the view layer with seamless card-to-card transitions and a nice level of interactivity. Lately I’ve been trying to work a lot on making my projects have a better end-user experience, ever since reading this blog post by Joel Spolsky where he mentioned that even the best tech gets overlooked by a typical end-user in favor of slightly less-good tech that feels nice and looks great.

At any rate, I’m reworking this old flashcards project and using postgres as my database because I think that while my old project was solidly designed, it doesn’t look awesome and it’s not hosted on the web. I firmly believe that it’s important for me to get comfortable with deploying projects to the web so that my code can be useful to end-users/I can get some DevOps experience, and for recruiters to weigh the strength and quality of my work through live code.

Getting started with postgres

To use pg in your own Rails project, first make sure it is installed. (The following instructions are for Mac users. I like to use Homebrew for this kind of stuff because it automatically manages needed dependencies. To check if you already have postgres installed on your local machine, enter this into your terminal:

postgres -V

If you do have postgres installed, the terminal will respond to your prompt with something like this:

postgres (PostgreSQL) 9.4.4

In which case, great, skip the following installation instructions and proceed directly to the discussion of Postgres’ permission based model in the next section. If you don’t already have postgres installed, however, enter this into your terminal:

brew install postgresql

If you run into any problems during the execution of this command, this gist about installing postgres via Homebrew might prove helpful to you. Hopefully however, Homebrew will work great for you (I usually find it to be fairly straightforward) and after some downtime you will have a working postgres install.

Postgres has a permission-based model

When I first switched from sqlite3 to Postgres, I was not aware that for security reasons, postgres has a permission-based model. This means that specific database instances are tied to particular users (called ‘roles’) who can have permissions that include creating a database, reading a database, writing to a database, or dropping a database. As I tried to understand my new postgres database, I kept getting these errors which boiled down to ‘role does not exist’. Of course I tried looking at the docs, but as is often the case for me StackOverflow helped me to surpass my initial hurdle of misunderstanding to become aware of the role-based permission model behind postgres. (I’m not knocking documentation here at all — often even the best written documentation is somewhat more opaque than a well-written SO answer to someone who is a complete novice to that framework or tool, in my opinion). StackOverflow led to me this fantastic codementor tutorial here, which is how I was able to solve my issue. Basically, I was attempting to migrate a database whose ownership was different than the postgres user I was currently logged in as. By editing my ./config/database.yml file to use the default postgres users for development (postgres comes installed with a default user role, whose username as well as password is ‘postgres’) I was able to successfully migrate my tables within my flashcard_api_development database. I also created a more secure role with its own username and password for my production and test environments. I’ll have to research more to see if I’ve configured my database exactly as I’d like it, but I am satisfied to at least know what’s going on now in terms of the postgres configuration options.

Hopefully this brief discussion of postgres’ role-based permission model and the codementor tutorial I’ve linked to can help others who are just now migrating to postgres.