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. 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/.
For Debian based linux users, install is even easier. Just run:
sudo apt-get install postgresql
Check to make sure postgres is running:
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:
bundle remove sqlite3
Now add and install the postgresql
gem:
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.
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.
# 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:
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 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.
First, install pgloader on your system:
# linux
apt-get install pgloader
# mac
brew install pgloader
Then use pgloader to import your sqlite3 db file:
pgloader ./db/db_name.sqlite3 postgresql:///db_name
Top comments (1)
Thanks. The article was topnoch but the pgloader tip saved me a lot of work. Thanks a lot.