When working on a Rails project, there might be scenarios where you want to switch your database from PostgreSQL to SQLite.

SQLite is lightweight, requires no server setup, and is a great choice for development or small-scale projects. This guide walks you through the process step-by-step.

Introduction

Changing your database in a Rails project can seem daunting, but Rails’ flexibility makes it a straightforward task. By following these steps, you'll seamlessly transition from PostgreSQL to SQLite.

Update the Gemfile

First, replace the PostgreSQL gem (pg) with the SQLite gem (sqlite3) in your Gemfile. Here’s how:

# Remove or comment out:
# gem 'pg'

# Add:
gem 'sqlite3', '~> 1.4'

Once updated, run the following command to install the new dependencies:

bundle install

Output

Update the database configuration

Next, update your config/database.yml file to configure SQLite as your database adapter. Here's an example configuration:

default: &default
  adapter: sqlite3
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

development:
  <<: *default
  database: db/development.sqlite3

test:
  <<: *default
  database: db/test.sqlite3

production:
  <<: *default
  database: db/production.sqlite3

This configuration defines SQLite as the adapter for all environments (development, test, and production).

Update dependencies

Ensure that your application code doesn’t use PostgreSQL-specific features (like uuid, JSONB fields, or specific SQL queries). Refactor where necessary.

SQLite has different capabilities compared to PostgreSQL. You’ll need to ensure your application doesn’t rely on PostgreSQL-specific features such as:

• UUIDs: Replace UUID columns with standard IDs or compatible alternatives.
• JSONB fields: Use regular text columns or serialize data into a string format.
• Custom PostgreSQL Queries: Rewrite queries to be compatible with SQLite.

Carefully review your application code and migrations for any PostgreSQL-specific features and refactor as needed.

Run dependencies

After updating the Gemfile, ensure all the required gems are installed by running:

bundle install

Output

Re-create the structure

rails db:create
rails db:migrate

Output

Test your application

With everything set up, start your Rails server:

rails server

Test your application thoroughly to ensure everything works as expected. Be mindful of any features or queries that may behave differently in SQLite compared to PostgreSQL.

Conclusion

Switching your Rails project from PostgreSQL to SQLite can simplify your setup for development or small-scale applications.

SQLite’s lightweight and serverless design makes it ideal for quick iterations and testing. While PostgreSQL excels in production environments, SQLite shines when simplicity and portability are key.

Done

Let's network

Github
LinkedIn
Dev.to