How To Use PostgreSQL With Ruby On Rails Application

Even though the default database SQLite3 works great with Ruby on Rails, we shouldn't be using it in some cases. For instance, if your Rails application is being concurrently accessed by large number of users, SQLite is not recommended. We should try more robust databases like MySQL or PostgreSQL, which provides scalability, concurrency, centralization, and control. We already knew how to use MySQL with Rails application. Today, we will see how to use PostgreSQL with Ruby on Rails application in Ubuntu Linux.

Prerequisites

I assume that you already have installed the latest Ruby on Rails on your Linux system. If not, refer the following guide.

• How To Install Ruby On Rails In Linux

1. Install PostgreSQL

To install PostgreSQL in Debian, Ubuntu and its derivatives, run:

$ sudo apt install postgresql postgresql-contrib libpq-dev

This command will install PostgreSQL along with all required dependencies. Here, the libpq-dev is a PostgreSQL library that allows the client programs to pass queries to the PostgreSQL backend server and to receive the results of these queries. This library is required to connect to PostgreSQL when you setup a Rails application.

Make sure postgresql service is started and enabled on startup:

$ sudo systemctl start postgresql

$ sudo systemctl enable postgresql

To view the current status of postgresql service, run:

$ systemctl status postgresql

1.1. Create new database role

We need a dedicated database user (role) to create and configure databases or our Rails application.

To create a new database role in PostgreSQL, run the following command from your Terminal:

$ sudo -u postgres createuser -s ostechnix -P

Here,

• sudo -u postgres - allows you to run the command from the postgres account.
• createuser -s ostechnix - create a new role named ostechnix with super user privileges.
• -P - prompts you to enter the password for the new role.

2. Use PostgreSQL with Ruby on Rails application in Linux

To create a new Rails application called ostechnixapp and use PostgreSQL as its default database, run:

$ rails new ostechnixapp -d postgresql

This will create a Rails application called ostechnixapp in a directory named ostechnixapp and install the gem dependencies that are already mentioned in Gemfile using bundle install.

After creating the ostechnixapp application, switch to its directory:

$ cd ostechnixapp/

Edit the application's database configuration file:

$ vi config/database.yml

Under the default section, add the PosgreSQL database role and its password which we created in the previous step:

[...]
default: &default
   adapter: postgresql
   encoding: unicode
   # For details on connection pooling, see Rails configuration guide
   # https://guides.rubyonrails.org/configuring.html#database-pooling
   pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
   username: ostechnix
   password: Password123#@!
[...]

Replace the username and its password with your own. Press ESC and type :wq to save and close the file.

Now create the new database for your Rails application using command:

$ rake db:create

This will create two databases with your Rails application's name. For example, if the application name is ostechnixapp, then it will create ostechnixapp_development and ostechnixapp_test.

Created database 'ostechnixapp_development'
Created database 'ostechnixapp_test'

You can verify it if the databases are successfully created from PosgreSQL as well.

Login to PosgreSQL:

$ sudo -u postgres psql

In the postgresql prompt, run \l to list the available databases:

postgres=# \l

Sample output:

                                       List of databases
            Name           |   Owner   | Encoding | Collate |  Ctype  |   Access privileges   
 --------------------------+-----------+----------+---------+---------+-----------------------
  ostechnixapp_development | ostechnix | UTF8     | C.UTF-8 | C.UTF-8 | 
  ostechnixapp_test        | ostechnix | UTF8     | C.UTF-8 | C.UTF-8 | 
  postgres                 | postgres  | UTF8     | C.UTF-8 | C.UTF-8 | 
  template0                | postgres  | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
                           |           |          |         |         | postgres=CTc/postgres
  template1                | postgres  | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
                           |           |          |         |         | postgres=CTc/postgres
 (5 rows)
 postgres=# 

Exit from the PostgreSQL prompt by entering \q.

postgres=# \q

Now start your Rails web server using command:

$ rails server -b 0.0.0.0

This will start your Rails application on default port 3000. Here, we passed the argument -b 0.0.0.0 in order to access the Rails application from any machine on the local area network.

Open your web browser and access your Rails test page by navigating to the URL: http://ip-address:3000.

If you want to use different port, pass -p flag along with the port number like below:

$ rails server -b 0.0.0.0 -p 8080

Now, the Rails application can be accessed from this URL: http://ip-address:8080.

3. Delete Rails application

If you have used the default database which is SQLite, you can delete the Rails application by simply deleting its app directory.

$ rm -fr ostechnixapp

If you've used PostgreSQL, first you need to drop the related databases of your application using command:

$ rake db:drop

And then delete the application directory:

$ rm -fr ostechnixapp

Conclusion

Now you know how to create a Rails application with PostgreSQL as the database. Like I mentioned already, if your application is being actively used by large number of users, you need to use PostgreSQL or MySQL. This can also be useful when you want to scale the database in future, manage your database from a remove system.

Related read:

• Configure Database Connection Using Environment Variable In Rails