Migrating a Rails App from MySQL to SQLite database

This article shows the steps to migrate your database from MySQL to SQLite.


Pre-requites

  • You need to have administrative access to the target MySQL database.
  • You have SQLite3 installed on the target machine.

Steps

1) Create a dump of your MySQL database.

$ mysqldump -u USER -p -h HOST > ~/dump.sql

2) Clone the mysql2sqlite tool from Github.

$ git clone https://github.com/mysql2sqlite/mysql2sqlite.git

3) Use the mysql2sqlite tool to import data from your dump file into a fresh SQLite database file.

$ ./mysql2sqlite ./dump.sql | sqlite3 mydb.sqlite3

4) Update the Gemfile in your Rails App.

# gem 'mysql2', '~> 0.5'
gem 'sqlite3', '>= 2.1'

5) Run bundle install.

6) Update the database configuration.

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

development:
  <<: *default
  database: storage/mydb.sqlite3

7) Copy the SQLite file into the storage directory.

8) That's all. You can now test your app.


AI Summary
gpt-4o-2024-08-06 2024-12-01 14:43:33
This article provides a step-by-step guide for migrating a Rails app from a MySQL to a SQLite database. It covers prerequisites, creating a MySQL database dump, using the mysql2sqlite tool, updating the Rails app's Gemfile, running bundle install, and updating the database configuration before testing the app.
Chrome On-device AI 2025-01-20 09:34:00

Share Article