Before the Shadow Play

Category: Postgres

  • Necessary steps to get the GitLab Rails template app running on OS X

    The GitLab template is a great way to get started on a new Rails app – it sets up most of what you need simply by checking out the code, but there are a few things you need to do if you’re starting fresh on a
    new Mac.

    The template app assumes you’ll be using Postgres for your database. I recommend sticking with this; if you grow your app past the proof-of-concept step, you’re going to want it configured for a robust and responsive  database. I love SQlite, but it’s not that much harder to go ahead and get Postgres running, so let’s do that instead of downgrading the template.

    If you have Homebrew installed, skip down to the brew install postgres section. If you don’t, run the following command:

    /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

    Wait for a while for that to finish, and Homebrew will be installed. Once that’s done you can move on to installing and configuring Postgres:

    brew update
brew install postgres
initdb /usr/local/var/postgres -E utf8

    Now you need to configure your databases. The initdb above sets up your user ID as a root user to start, so you can now add the application’s user and databases. (Substitute an appropriate username for yourappdev).

    psql postgres
create role yourappdev with login password '...some password here...';
alter user yourappdev createdb;

    Now exit psql (^D will do) and log back in again as the yourappdev user to create the development and test databases for your Rails app. If you set a password, psql will prompt you for it. (If you forgot it, your root-level user created when you installed Postgres can still log in without a password at the moment.)

    psql postgres -U yourappdev
create database yourapp_development;
create database yourapp_test;

    Securing the production database

    You now need to create the database that you’ll run in production. Remember this is the one with the important user data in it that you don’t want to lose, so we want to create a whole separate username that will be the creator of the database and give it a good strong password that you record in your password manager. (If you don’t have a password manager, get one! It’s way safer than writing it down on a sticky and putting it in an envelope.)

    psql postgres
create role yourapp with login password 'a very strong password please';
alter user yourapp createdb;
^D
psql postgres -U yourapp
create database yourapp

    You’re now ready to work on your Rails app. When you want to run the production version, you’ll need to set the DATABASE_URL environment variable like this:

    DATABASE_URL=”postgres://yourapp:strongpassword@localhost/yourapp”

    Further deployment issues are beyond the scope of this post.

  • Postgres array_to_string() and array_agg() to decouple your interface

    Let’s say you’ve got a collection of job data for a set of users that looks like this, and you want to create a nice summary of it to be displayed, with a count of how many jobs are in each category for each user, all on one line.

     │id │ user_id │ job │ status    │
 ├───┼─────────┼─────┼───────────┤
 │ 1 │ 12      │ 1   │ Completed │
 │ 2 │ 12      │ 2   │ Cancelled │
 │ 3 │ 14      │ 3   │ Ready     │
 │ 5 │ 14      │ 4   │ Completed │
 │ 6 │ 14      │ 4   │ Completed │
 │ 7 │ 14      │ 4   │ Cancelled │
 ...

    Here’s  the report of summarized statuses of jobs for each user that you want.

    │ user_id │ summary                           │
├─────────┼───────────────────────────────────┤
│ 12      | 1 Cancelled, 1 Completed          │
│ 14      | 1 Cancelled, 2 Completed, 1 Ready │

    I’ll show you how it’s possible to provide this solely with a Postgres SELECT.

    (more…)