How to Create and Connect an RDS Postgres Database to TablePlus, Terminal or Rails App

Motivation

I’m migrating my database from Heroku to RDS and I want to have the same comforts that I’m used to which is being able to supply a connection url to TablePlus or environment variables to configure my Rails web-app.

Create RDS Postgres Server

We’re going to create an RDS Postgres server for this example with the most inexpensive setting. Proceed to create a database

And select PostgreSQL and proceed to Next

Choose Dev/Test and proceed to Next

We’ll set our DB Instance class to db.t2.micro

I’m using the example credentials for awsuser and mypassword. I would suggest using an online password generator for both for production. Lets proceed to Next

Lets ensure Public Accessibility to Yes and we’ll let it create a new VPC Security Group

Name your database. I’ve named mine mydatabase

I’m going to turn off Backup by setting the retention period to zero. Since this is a test database.

We can go ahead and create our database

And then proceed to our DB instance details

Assemble Connection URL from Endpoint

Waiting for Endpoint

If we visit our DB Instance details and the database is yet to be provisioned the endpoint won’t be available. We just need to wait a while and refresh.

We know that its still being created because it will tell us in the DB Instance status is creating

When the Endpoint is Available

When the server is available then DB instance status will say just that

And then under Connect we should see our Endpoint and Port and under the Security group rules we can see that the inbound rules only allow our own IP address. If you need others machines to connect you’ll have to add them to your inbound rules.

Sanity Check with Telnet

To ensure everything is working as expected lets use telnet to see if we can connect to our RDS instance.

Assembling the Connection URL

This is how you make the connection URL

postgres://username:password@endpoint:port/database

So my in my case it will look like the following

postgres://awsuser:mypassword@fpsql-database.cglotc3n8hbn.us-east-1.rds.amazonaws.com:5432/mydatabase

Accessing our Database via TablePlus

In TablePlus we can create a new connection from URL and it should populate the Connection form. We can proceed to Test the connection and see everything light up green. Then we can continue to Connect

And we’ll create two tables and insert some data to ensure everything is working as expected.

WITH project AS (
  INSERT INTO projects (name) VALUES ('Science Fair - Volcano Study') RETURNING id
)
INSERT INTO tasks (project_id,name) 
        SELECT id, 'Why do volcanos erupt?'       FROM project
  UNION SELECT id, 'Why is lava red?'             FROM project
  UNION SELECT id, 'Where do volcanos come from?' FROM project;

Accessing our Database via Terminal

This is how we can connect via terminal

psql --host endpoint --port port --username username --dbname db

So in my case it will be

psql --host psql-database.cglotc3n8hbn.us-east-1.rds.amazonaws.com --port 5432 --username awsuser --dbname mydatabase

Accessing our Database via Rails

Configure database.yml to accept Connection URL

If we set the DATABASE_URL environment variable with our connection url Rails will automatically use this for the database and we can simplify our database.yml

development:
  adapter: postgresql
  encoding: unicode
test:
  adapter: postgresql
  encoding: unicode
production:
  adapter: postgresql
  encoding: unicode

Loading Env Vars in Development

In development we can use DotEnv gem to load our env vars for local development inside the .env file that will sit at the root of our Rails app.

DATABASE_URL=postgres://username:password@endpoint:port/database

Loading Env Vars Production

Most instance providers allow you set the env vars directly or they let you specify a startup script and you would just provide them in there.

Example of setting Env Vars in Opsworks