Transitioning from SQLite to Postgres in Rails
Tags: rails, postgres
Published: 2020-01-02 21:07:46

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](https://www.heroku.com/). 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](https://devcenter.heroku.com/articles/sqlite3). 

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](https://www.postgresql.org/download/macosx/).

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 
``` ruby 
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 
```ruby
gem 'sqlite' 
```
to 
``` ruby
gem 'pg' 
```

then run 
``` bundle install```

After you will want to access your config/database.yml file.
change the lines from  
``` ruby 
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 
```

to 

``` ruby
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
```

Next run 
``` rails db:create && rails db:migrate ```
And your app will now be using postgres as the database. 

**Hosting on Heroku**
Once you are ready to host your finished application on Heroku, you can follow these steps:

You will need to login to your existing Heroku account, or you can create one [here](https://signup.heroku.com/)
run the command 

```heroku login ```

this will prompt you for your info.

After log in, run the command

``` heroku create ```

and a new Heroku app with a random name will be created.

If you would like to rename the application you can run 

```heroku apps:rename appName```

run 

``` git add . ``` 

to add all the changes and

```git commit -m "commit message"```

to commit the changes. 

Lastly run 

```git push heroku master```

and you are done!