Make the move from Sqlite3 to Postgres in Rails 6
Tags: ruby, rails, postgres, tutorial
Published: 2020-08-27 22:25:49

If you created a rails project without specifying a database, chances are rails defaulted to a sqlite3 database. While this is a great start for development, it can lead to substantial roadblocks as you encounter the need to deploy to services with ephemeral storage or want to use more advanced database features such as jsonb and array datatypes. 

There are plenty of relational database options out there, but the most widely used and, in my opinion, the most versatile option is [Postgresql](https://www.postgresql.org/). In this article I'll break down the process of installing postgres, modifying db configs, creating your database, and finally importing your existing database.

##Install Postgres
If you are using a mac, the easiest way to get started with postgres is to follow the install instructions at [https://postgresapp.com/](https://postgresapp.com/).

For Debian based linux users, install is even easier. Just run:
```bash
sudo apt-get install postgresql
```
Check to make sure postgres is running:
```bash
sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Thu 2020-08-27 16:20:11 CDT; 50min ago
    Process: 1845 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 1845 (code=exited, status=0/SUCCESS)

Aug 27 16:20:11 ra systemd[1]: Starting PostgreSQL RDBMS...
Aug 27 16:20:11 ra systemd[1]: Finished PostgreSQL RDBMS.
```
Since this command returned an `Active` status, we are good to go!

##Update Gemfile

Remove the `sqlite3` gem from your project:
```bash
bundle remove sqlite3
```

Now add and install the `postgresql` gem:
```bash
bundle add pg --install
```

##Update Database Config
Replace the current contents of `config/database.yml` with the following config. Make sure you replace `<app_name>` with the actual name of your application.

```yaml
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 } %>

development:
  <<: *default
  database: <app_name>_development

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: <app_name>_test

production:
  <<: *default
  database: <app_name>_production
  username: pixel_place
  password: <%= ENV['PIXEL_PLACE_DATABASE_PASSWORD'] %>
```

##Setup The New Database
Now that postgres has been added to your project and configured, a few changes need to be made to your migrations. If you have any columns of the type `t.string` in your migrations, it is highly recommended that you replace that type with `t.text`. 

The main reason for this stems from the way ActiveRecord casts types between different databases. The type `t.string` will cast to a `VARCHAR(255)` in postgresql, limiting any existing string data to only 255 characters. Sqlite3 on the other hand is much more lenient with storing character data so your data may have been saved as text to begin with. To save ourselves any headaches further down the line, defaulting to the `t.text` type is a safe bet.

```ruby
# db/migrate/01_create_users.rb
class CreateUsers < ActiveRecord::Migration[6.0]
  def change
    create_table :users do |t|
      t.text :first_name
      t.text :last_name
      t.text :username

      t.timestamps
    end
  end
end
```

You are are now ready to run a few rake commands:
```bash
rake db:setup

# or

rake db:create db:migrate db:seed
```

At this point you should have a working postgresql database. Be sure to run `rails s` and navigate to [http://localhost:3000](http://localhost:3000) to make sure your application is running without error.

## Bonus: Import an existing sqlite3 database into postgres
If you have a populated database that needs to be saved, the process of importing data into postgresql is made trivial with a tool called [pgloader](https://github.com/dimitri/pgloader).

First, install pgloader on your system:
```bash
# linux
apt-get install pgloader

# mac
brew install pgloader
```
Then use pgloader to import your sqlite3 db file:
```bash
pgloader ./db/db_name.sqlite3 postgresql:///db_name
```