Experimenting with Ruby, Sinatra and PostgreSQL: a Message Board App

Although it’s 2018, and the first version of Ruby was released in 1995, 23 years ago, nowadays it seems Ruby has a reasonably large number of employment opportunities. The latest version of Ruby is 2.5.3 (it was released on October 18, 2018). In the present article I create a simple Message Board App using Ruby and PostgreSQL.

Photo by Jason Leung on Unsplash

I’ve found the following book very helpful for understanding the basics of the language. I will use Sinatra framework in this article.

Learn Enough Ruby to Be Dangerous

Keywords: Ruby, Embedded Ruby, HTML, CSS, SQL, erb, Sinatra, PostgreSQL, Linux, Ubuntu, rerun, Message Board, yield, HTTP, regular expressions, regex

DISCLAIMER: THE VIEWS AND OPINIONS EXPRESSED IN THIS ARTICLE ARE THOSE OF THE AUTHOR AND DO NOT REFLECT THE OFFICIAL POLICY OR POSITION OF THE EMPLOYER OF THE AUTHOR. THE ARTICLE IS NOT ENDORSED BY, DIRECTLY AFFILIATED WITH, MAINTAINED, AUTHORIZED, OR SPONSORED BY ANY CORPORATION OR ORGANIZATION. THE INFORMATION CONTAINED ON THIS ARTICLE IS INTENDED SOLELY TO PROVIDE GENERAL GUIDANCE ON MATTERS OF INTEREST FOR THE PERSONAL USE OF THE READER, WHO ACCEPTS FULL RESPONSIBILITY FOR ITS USE. ALTHOUGH THE AUTHOR HAS MADE EVERY EFFORT TO ENSURE THAT THE INFORMATION IN THIS ARTICLE WAS CORRECT AT THE TIME OF THE WRITING, THE AUTHOR DOES NOT ASSUME AND HEREBY DISCLAIM ANY LIABILITY TO ANY PARTY FOR ANY LOSS, DAMAGE, OR DISRUPTION CAUSED BY ERRORS OR OMISSIONS, WHETHER SUCH ERRORS OR OMISSIONS RESULT FROM NEGLIGENCE, ACCIDENT, OR ANY OTHER CAUSE.

Table of Content

The app will be built step by step. The article is divided into the following sections:

• Prerequisites
• Preparation
• Message Board Web App
• 1st step: List hard-coded values
• 2nd step: Read messages from the db
• 3rd step: Enable posting of messages and save them in the database
• Useful links for further reading
• Enhancing the Hello World example
• Closing words
• References

Prerequisites

For this article I will use the following programming languages and technology:

• HTML
• CSS
• Ruby
• SQL
• Sinatra (Ruby library)
• PostgreSQL
• Linux

Preparation

This step is about:

• installing Sinatra for Ruby
• testing Ruby with a simple hello world app
• starting PostgreSQL server
• installing rerun for Ruby (rerun is a Ruby library)

Installing Sinatra

I assume that your system already contains Ruby. I use Ubuntu 14.04.5 LTS (trusty) and my version of Ruby is

ruby 2.4.0p0 (2016-12-24 revision 57164) [x86_64-linux]

I use Cloud 9 environment.

For these examples we will also need Sinatra. Sinatra is a Domain Specific Language for quickly creating web applications in Ruby with minimal effort. To check if Sinatra is already installed, you can use

gem list sinatra

It should return something like this, containing the version of Sinatra installed in your system:

*** LOCAL GEMS ***
sinatra (2.0.4)

If Sinatra is not installed, you should install it:

gem install sinatra

To try if everything works, create the following Ruby file. Ruby source code has the extension .rb.

Run this file with Ruby:

ruby ruby-hello-world-sinatra.rb

In my system the command returns the following. This will start a web server and run the Ruby script.

nlharri:~/workspace/ruby $ ruby ruby-hello-world-sinatra.rb 
[2018-11-21 09:53:58] INFO  WEBrick 1.3.1
[2018-11-21 09:53:58] INFO  ruby 2.4.0 (2016-12-24) [x86_64-linux]
== Sinatra (v2.0.4) has taken the stage on 8080 for development with backup from WEBrick
[2018-11-21 09:53:58] INFO  WEBrick::HTTPServer#start: pid=2433 port=8080

Open your web browser and go to http://localhost:8080. You need to see a hello world text in the browser.

To stop the server, press CTRL+C in the terminal. I will get back to the hello world example later.

Start PostgreSQL server

I will use PostgreSQL version 9.3.18. If PostgreSQL server is not starting automatically, and you would like to start it, you can use

sudo update-rc.d postgresql enable

If you would like to start the PostgreSQL server, you can use

sudo service postgresql start

By default, PostgreSQL sets up the user and database “postgres” upon a new installation. We interact with the postgres database software through an interface called “psql.”

You can login with user “postgres” to database “postgres” with the following command:

sudo -u postgres psql postgres

(You can exit with CTRL+D.)

Install rerun

We will install rerun, which will make it possible to make changes to the code and see the results without restarting the server.

For this, we prepare a Gemfile in the project.

touch Gemfile

And the Gemfile should contain the following:

source 'https://rubygems.org'
gem 'sinatra', '2.0.4'
gem 'rerun',   '0.13.0'

We need to install these packages (we already installed sinatra earlier).

bundle install

(If the system does not contain bundler, you need to install it with sudo gem install bundler)

Now we need to start the server, but for this we use the following command:

bundle exec rerun ruby-hello-world-sinatra.rb

Earlier we needed to quit and restart the server in order to see changes to the app, but thanks to the rerun the app is updated automatically, after we refresh the browser by hand.

Message Board Web App

What will the app know? The app will show the already posted messages, and the nickname of the person who posted the message. It will allow to post new messages. The data will be stored in a database, and the app will read the entries from the database, and save the new posts there.

The app is created step by step.

1st step: List hard-coded values

The first version of the app will show some hard-coded messages without using the database. (Later we will enhance the app to read up messages from the database.) In the first version we will also not able to post messages.

Design

I tried to define a nice-looking but simple design with CSS. I used Bubbly example which was very helpful for me to define the design.

Bubbly - CSS speech bubbles made easy

The basic layout and design looks like the following.

I also put this to codepen.io.

Basic Design of the Message Board App

Implementation with Embedded Ruby

We will hard-code values of the messages data, and use templating of Sinatra to show the data. The data is passed to the erb, for which this stackoverflow post was very useful to understand.

Here you can check the coding. The l_main.erb and the v_message.erb should be in the views folder so that Sinatra will find them.

With :locals we can pass data to the erb.

2nd step: Read messages from the db

In this step we enhance the app to read the messages from the db. For this we need to define a database and a table, and put some entries to it.

Photo by Nikolay Tarashchenko on Unsplash

Define database and table

For the next version of the app we need a database to store the data.

For this step the following tutorials were very useful.

How To Create, Remove & Manage Tables in PostgreSQL on a Cloud Server | DigitalOcean

PostgreSQL Ruby tutorial

In this step we will:

• create a new user messageboarduser in the system for accessing the database
• create a new user messageboarduser in PostgreSQL
• define database messageboard
• define database table messageboardmessages

Create a new user in the system

We create a new user with the following command

sudo adduser messageboarduser

You need to provide a password. (I used messageboarduser also for the password, but of course you should not do the same in a productive environment.)

Create a new user and database in PostgreSQL

Log into the default PostgreSQL user (called “postgres”) to create a database and assign it to the new user:

sudo su - postgres
psql

Create a new user that matches the system user you created. Then create a database managed by that user:

CREATE USER messageboarduser WITH PASSWORD 'messageboarduser';
CREATE DATABASE messageboard OWNER messageboarduser;

Exit out of the interface with the following command:

\q

Exit out of the default “postgres” user account and log into the user you created with the following commands:

exit
sudo su - messageboarduser

Sign into the database you created with the following command:

psql messageboard

Define database table for the app

We will define the messageboardmessages db table with the following command:

CREATE TABLE messageboardmessages (
 message_id varchar(36) PRIMARY KEY,
 nickname varchar(30) NOT NULL,
 message varchar(200) NOT NULL,
 timestamp varchar(50) NOT NULL
);

We can see the new table by typing “\d” into the prompt:

\d

The result:

The following command prints the definition of the table

\d messageboardmessages

The result:

Add entries to the table

We will add one sample entry to the newly defined table. (Here the value of message_id is a unique identifier which I generated manually. We will get back to unique identifier generation later.)

INSERT INTO messageboardmessages(message_id, nickname, message) 
VALUES ('201519e8-f3e7-4fcf-9349-3f81b94e908c', 'PostgreSQLUser', 'Hello from PostgreSQL', '2018-11-30 10:48:19 +0000');

Let’s query the data to see if it is really there:

SELECT * FROM messageboardmessages;

The result:

So we have the entry there.

Check adjust PostgreSQL config file (optional)

It might happen that when working on a Linux machine of the Ubuntu flavor and setting up a PostgreSQL 9.3 database, you run into the error ‘PG::ConnectionBad: FATAL: Peer authentication failed for user’ when trying to connect to a database from a web application. The most common fix for this error in a development or staging environment is to loosen the local permissions up a bit. For this you need to follow the following tutorial.

PostgreSQL Database: PG::ConnectionBad: FATAL: Peer authentication failed for user

• locate the file /etc/postgresql/9.3/main/pg_hba.conf and open it using sudo (sudo nano /etc/postgresql/9.3/main/pg_hba.conf)
• scroll down through the file (almost to the bottom) until you find the section that starts with #Database administrative login by Unix domain socket
• directly below that you will find local all all peer or local all postgres peer — change it to local all all trust
• save and close the file
• restart the PostgreSQL server: sudo service postgresql restart

Enhance the app with reading from the db

For this we need the pg Gem. Let’s enhance the Gemfile with this. So the Gemfile looks like this:

source 'https://rubygems.org'
gem 'sinatra', '2.0.4'
gem 'rerun',   '0.13.0'
gem 'pg',      '1.1.3'

Install the Gem:

bundle install

We need to include pg in the app.rb to use it.

require 'pg'

We will also need to enhance the get '/' do part to query the data from the database. The final version of app.rb can be seen here:

After this modification, when the server is restarted and the page is reloaded, you should see the following:

3rd step: Enable posting of messages and save them in the database

This step is about:

• generating unique identifiers
• validate input
• generate time stamp
• writing data to the database

Generating unique identifiers

Enhance the Gemfile with uuidtools:

source 'https://rubygems.org'
gem 'sinatra', '2.0.4'
gem 'rerun',   '0.13.0'
gem 'pg', '1.1.3'
gem 'uuidtools', '2.1.5'

This uuidtools package can be used to generate a new unique identifier for the new message entry.

The main app.rb will be enhanced with the generation of the new identifier.

Validation of input to avoid SQL injection

We need to avoid SQL injection, therefore we allow only a limited set of characters to be used in the input fields. For this we will use a regular expression check, similar to the following:

if ( input =~ /^[a-zA-Z0-9 ]*$/ ) 
 puts "input is OK!" 
end

This will allow using only characters of the English alphabet, numbers, and space.

This is just an example for the app, and in a productive environment you should use more robust techniques to avoid SQL injection!

Regular expression tester for Ruby:

Rubular: a Ruby regular expression editor and tester

A good guide to regular expressions can be found here:

Ruby Regular Expressions

Guides about SQL injections regarding Ruby can be found here:

Preventing SQL injections in Ruby (and other vulnerabilities) - Sqreen Blog | Modern Application…

Fixing SQL Injection Vulnerabilities in Ruby/Rails

Rails SQL Injection Examples

Generating time stamp

For the timestamp generation we will use default feature of Ruby:

timestamp = Time.now

Note that in this example we use the time of the server. This will be saved in the database, and not the time on the clients’ computer.

Writing the data to the database

Database insertion will be done with the following implementation:

# connect to the database
connection = PG.connect :dbname => 'messageboard', :user => 'messageboarduser', :password => 'messageboarduser'
  
# generate new UUID
val_uuid = UUIDTools::UUID.random_create.to_s
  
# insert data into the database
timestamp = Time.now
connection.exec "INSERT INTO messageboardmessages(message_id, nickname, message, timestamp) VALUES ('#{val_uuid}', '#{params[:nickname]}', '#{params[:message]}', '#{timestamp}');"

Summary of the 3rd step

We needed to modify:

• Gemfile
• app.rb
• main.css
• l_main.erb
• v_message.erb

In this example implementation there are many repetitions. According to the DRY principle we should eliminate repetitions. I need to refactor this code later. In this example I just would like to show a quick example of using Ruby and Sinatra.

The modified implementation looks like the following:

Useful links for further reading

PostgreSQL: Linux downloads (Ubuntu)

Apt - PostgreSQL wiki

Ruby PostgreSQL tutorials

Enhancing the Hello World example

Photo by rawpixel on Unsplash

Now we will enhance the Hello World example. The following shows using the yield statement.

The tag function will wrap the provided text with the provided HTML tag name. With Sinatra, we provided this markup text to the output when the root of the website is accessed by the client.

We created another function, day_of_the_week, to get the name of the day of the week. For this we use the built-in date library of Ruby.

Closing words

I hope you have learned something new today. Thank you for reading this article.

The Message Board App can be accessed in my Github repo.

nlharri/RubyMessageBoard

References

Learn Enough to Be Dangerous

Ruby Programming Language

PostgreSQL: The world's most advanced open source database

sinatra/sinatra