Using PostgreSQL with Ruby

This part of the Bookshelf tutorial shows how the sample app stores its persistent data in a PostgreSQL database.

This sample uses a PostgreSQL server, running on a Google Compute Engine virtual machine instance, to store its persistent data. If you prefer to use a different PostgreSQL server, you can deploy this sample app to Cloud Platform and configure it to use any PostgreSQL server of your choice.

This page is part of a multi-page tutorial. To start from the beginning and see instructions for setting up, go to Ruby Bookshelf App.

Running PostgreSQL on Compute Engine

  1. Use the PostgreSQL image provided by Bitnami in the Cloud Launcher. Click Launch on Compute Engine. For Deployment name, enter library. It takes a few minutes to create the instance and deploy PostgreSQL.

  2. Note the IP address of your instance.

    Screen shot of VM instance

  3. Note the username and password. Also note the name of the virtual machine instance.

  4. Create a firewall rule to allow traffic to PostgreSQL:

    gcloud compute firewall-rules create default-allow-postgresql \
        --allow tcp:5432 \
        --source-ranges 0.0.0.0/0 \
        --target-tags google-cloud-marketplace \
        --description "Allow postgresql access to all IPs"
    

Configuring settings

  1. Go to the getting-started-ruby/2-postgresql directory, and copy the sample database.yml file:

    cp config/database.example.yml config/database.yml
    
  2. To configure your database, edit config/database.yml. Set the value of database to bookshelf. Replace the your-postgresql-* placeholders with the specific values for your PostgreSQL instance and database.

    For example, suppose your IPv4 address is 173.194.230.44, your username is postgres, and your password is secret123. Then the postgresql_settings section of your database.yml file would look like this:

    postgresql_settings: &postgresql_settings
      adapter: postgresql
      encoding: unicode
      pool: 5
      username: postgres
      password: secret123
      host: 173.194.230.44
      database: bookshelf
    

Installing dependencies

In to the 2-postgresql directory, and enter this command:

bundle install

Creating a database and tables

  1. To create the database and run migrations to create the required tables, enter these commands:

    rake db:create
    rake db:migrate
    

Running the app on your local machine

  1. Start a local web server:

    rails server
    
  2. In your web browser, enter this address:

    http://localhost:3000

Now you can browse the app's web pages and add, edit, and delete books.

Press Control+C to exit the local web server.

Deploying the app to the App Engine flexible environment

  1. Compile JavaScript assets for production:

    RAILS_ENV=production rake assets:precompile
    
  2. Deploy the sample app:

    gcloud app deploy
    
  3. In your web browser, enter this address. Replace [YOUR_PROJECT_ID] with your project ID:

    https://[YOUR_PROJECT_ID].appspot.com
    

If you update your app, you can deploy the updated version by entering the same command you used to deploy the app the first time. The new deployment creates a new version of your app and promotes it to the default version. The older versions of your app remain, as do their associated VM instances. Be aware that all of these app versions and VM instances are billable resources.

You can reduce costs by deleting the non-default versions of your app.

To delete an app version:

  1. In the Cloud Platform Console, go to the App Engine Versions page.

    Go to the Versions page

  2. Click the checkbox next to the non-default app version you want to delete.
  3. Click the Delete button at the top of the page to delete the app version.

For complete information about cleaning up billable resources, see the Cleaning up section in the final step of this tutorial.

Application structure

The following diagram shows the application's components and how they connect to each other.

Auth sample structure

Understanding the code

This section walks you through the application code and explains how it works.

Listing books

When you visit the app's home page, you are routed to the index action of the BooksController class. This is configured in config/routes.rb:

Rails.application.routes.draw do

  # Route root of application to BooksController#index action
  root "books#index"

  # Restful routes for BooksController
  resources :books

end

The BookController#index action fetches a list of books from the Cloud SQL database. The app lists at most 10 books on each web page, so the fetched list depends on which page the user is viewing. For example, suppose there are 26 books in the database, and the user is on the third page (/?page=3). In that case, params[:page] is equal to 3, which is assigned to the page_number variable. Then a list of 6 books, starting at offset 20, is fetched and assigned to @books.

class BooksController < ApplicationController

  PER_PAGE = 10

  def index
    page_number = params[:page] ? params[:page].to_i : 1
    book_offset = PER_PAGE * (page_number - 1)
    @books      = Book.limit(PER_PAGE).offset(book_offset)
    @next_page  = page_number + 1 if @books.count == PER_PAGE
  end

The Book class is a simple ActiveRecord model that represents an individual book in the books table:

class Book < ActiveRecord::Base
  validates :title, presence: true
end

In routes.rb, the resources :books call configures RESTful routes for creating, reading, updating, and deleting books that are routed to the corresponding actions in the BooksController class.

After BooksController.index fetches a list of books, the embedded Ruby code in books/index.html.erb renders the list:

<% @books.each do |book| %>
  <div class="media">
    <%= link_to book_path(book) do %>
      <div class="media-body">
        <h4><%= book.title %></h4>
        <p><%= book.author %></p>
      </div>
    <% end %>
  </div>
<% end %>

<% if @next_page %>
  <nav>
    <ul class="pager">
      <li><%= link_to "More", books_path(page: @next_page) %></li>
    </ul>
  </nav>
<% end %>

Displaying book details

When you click an individual book on the web page, the BookController#show action fetches the book, specified by its ID, from the books table:

{ % include "cloud/_docwidgets/_github_include.html" with project="getting-started-ruby" file="2-postgresql/app/controllers/books_controller.rb" region_tag="show" %}

Then the embedded Ruby code in show.html.erb displays the book's details:

<div class="media">
  <div class="media-body">
    <h4><%= @book.title %> | &nbsp; <small><%= @book.published_on %></small></h4>
    <h5>By <%= @book.author || "unknown" %></h5>
    <p><%= @book.description %></p>
  </div>
</div>

Creating books

When you click Add book on the web page, the BooksController#new action creates a new book. The embedded Ruby code in new.html.erb points to _form.html.erb, which displays the form for adding a new book:

<%= form_for @book do |f| %>
  <div class="form-group">
    <%= f.label :title %>
    <%= f.text_field :title %>
  </div>
  <div class="form-group">
    <%= f.label :author %>
    <%= f.text_field :author %>
  </div>
  <div class="form-group">
    <%= f.label :published_on, "Date Published" %>
    <%= f.date_field :published_on %>
  </div>
  <div class="form-group">
    <%= f.label :description %>
    <%= f.text_area :description %>
  </div>
  <button class="btn btn-success" type="submit">Save</button>
<% end %>

When you submit the form, the BooksController#create action saves the book in the database. If the new book is saved successfully, the book's page is displayed. Otherwise, the form is displayed again along with error messages. The book_params method uses strong parameters to specify which form fields are allowed. In this case, only book title, author, publication date, and description are allowed:

def create
  @book = Book.new book_params

  if @book.save
    flash[:success] = "Added Book"
    redirect_to book_path(@book)
  else
    render :new
  end
end

private

def book_params
  params.require(:book).permit(:title, :author, :published_on, :description)
end

Editing books

When you click Edit book on the web page, the BooksController#update action fetches the book from the database. The embedded Ruby code in edit.html.erb points to _form.html.erb, which displays the form for editing the book:

def update
  @book = Book.find params[:id]

  if @book.update book_params
    flash[:success] = "Updated Book"
    redirect_to book_path(@book)
  else
    render :edit
  end
end

When you submit the form, the BooksController#update action saves the book in the database. If the new book is saved successfully, the book's page is displayed. Otherwise, the form is displayed again along with error messages.

Deleting books

When you click Delete Book on the web page, the BooksController#destroy action deletes the book from the database and then displays the list of books:

def destroy
  @book = Book.find params[:id]
  @book.destroy
  redirect_to books_path
end

Send feedback about...