Transitioning from SQLite to Postgres in Rails

#rails #postgres

After spending some time creating a single page web application with a rails API and a vanilla JavaScript frontend, I decided I wanted to host the application on cloud platform such as Heroku. A cloud-based platform is a great way to host applications and make it easy for friends and potential employers to see what you have been working on. An important thing to keep in mind before hosting your rails app is what database are you working with.

Sqlite Vs. Postgres
The default rails database is operated through sqlite. sqlite is great for the novice developer because it can be run in local memory and stored in small files in your local working directory that are easily created and moved. Given how sqlite stores its files on disks, it is not intended to be used for production. On the Heroku website it explains that Heroku's stack is an ephemeral filesystem. You can do all the CRUD actions with this filesystem, but it clears its contents periodically. Meaning, if you were to use a sqlite database, the entire database would be deleted at least once every 24 hours. If you'd like to read more about how Heroku's filesystem is managed, you can click here.

Given the nature of the filesystem stack, Heroku recommends using the Postgres database.
Postgres is different than sqlite in how it stores files. Postgres databases are only portable once they are hosted on a server. Practically, this means that any changes you make to your database will not be persistent until the database is hosted on a server. This is a very useful feature during the development phase of your project. If you are working with one or more partners on your project and are using a host like github during development, you will likely run into merge conflicts with the database if you are using a locally stored database like sqlite. Because of this, Postgres is a better database system to use for applications intended for production.

How To Use Postgres With Rails
If you don't already have Postgres installed, you can download it here.

If you have Homebrew installed on your computer you can also use your computer's terminal to install postgres by typing the following command into the command line:

brew install postgresql

Once you have postgres installed, you can create a new rails app.
Rails has a built-in function to set the application's default database.
simply run

rails new -d postgresql

If you'd like, you can also set the name of the database and its associated username and password.
Before creating your new rails app
type the following commands into the command line.

su -postgres

create role <appName> with <createdb> login password <password1>

replace the words in <> with the names you'd like to fill in.
Afterwards, you can create your new rails app with the command listed above.

Change an existing sqlite database to postgres
Up until now, we have only discussed how to create a new rails app with a postgres database. However, if you want to transition from sqlite to postgres mid-development, you can follow these steps:

In your gemfile you will want to change the line

gem 'sqlite'

gem 'pg'

then run

bundle install

After you will want to access your config/database.yml file.
change the lines from

development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

test:
  adapter: sqlite3
  database: db/test.sqlite3
  pool: 5
  timeout: 5000

production:
  adapter: sqlite3
  database: db/production.sqlite3
  pool: 5
  timeout: 5000

development:
  adapter: postgresql
  database: my_database_development
  pool: 5
  timeout: 5000
test:
  adapter: postgresql
  database: my_database_test
  pool: 5
  timeout: 5000

production:
  adapter: postgresql
  database: my_database_production
  pool: 5
  timeout: 5000