SQLite VS PostgreSQL

I was taught SQL by using SQLite. SQLite is absolutely useful in plenty of situations. It works great as a DB solution for devices (cellphones, game consoles, other embedded devices), data analysis, and even for caching enterprise data. It does not work great as a database solution for high traffic websites that follow the client/server model as data can become corrupted from two clients attempting to alter the same piece of data at the same time. It also happens that in order to deploy a Rails application to Heroku one needs to use a client/server database. So I chose Postgres. It is open source, scalable, and who doesn't love that little elephant? For more information on when to use SQLite versus a client/server database system check out this page sqlite.org/whentouse.

PostgreSQL

Much different from SQLite, as we already know, Postgres has added layers of security/administration. This means there are users who have different permissions to access/create/drop databases. After postgres is installed, the super user postgres is created. This user has all the permissions and we will use this user to create other users.

# installing postgres on ubuntu
$ sudo apt install postgresql postgresql-contrib libpq-dev
# creating a new super user named sam (the -P option will prompt us to create a password for the newly created user)
$ sudo -u postgres createuser -s sam -P

Now we can migrate our Rails app to Postgres!

Rails And RDBMS

By default Ruby on Rails will include SQLite as the database of choice for Active Record. You can add an option to the new rails app command to use postgres:

$ rails new awesomeapp -d=postgresql

But if you already have a rails app you're wanting to deploy on Heroku like me this doesn't help. We will need to manually change the database. The first step will be to add the pg gem to the Gemfile. Easy!

# Gemfile
gem 'pg'

Don't forget to run the bundle install command after you've changed the Gemfile. Now comes the tricky part. Updating database.yml. I copy and pasted the database.yml file from a rails app I created using the -d=postgresql option and changed the names of databases to reflect the current app. Now that that is over run these commands:

$ rails db:setup
$ rails db:migrate

Boom! You are now using Postgres!