mysql-to-sqlite

CI

A standalone, schema-aware copier that copies raw table data from MySQL to an existing SQLite database through Active Record, without loading an application's models or running callbacks.

The source and destination must have matching table columns and the same maximum schema_migrations.version. The importer refuses a non-empty destination by default, copies in batches, and validates row counts, foreign keys, and integer sequences.

Install

Requirements

  • Ruby 3.3 or newer
  • MySQL or MariaDB client libraries and development headers, required by mysql2
  • SQLite libraries and development headers, required by sqlite3

The package names vary by operating system. Install the development packages provided by your system package manager before installing the gem.

Install the command

Install the published gem for standalone use:

gem install mysql-to-sqlite

Confirm that the command is available:

mysql-to-sqlite --version
mysql-to-sqlite --help

Add it to an application

Add the gem to the application's development dependencies:

group :development do
  gem "mysql-to-sqlite"
end

Install the bundle and run the command through Bundler:

bundle install
bundle exec mysql-to-sqlite --help

Use

First create or migrate an empty SQLite database to exactly the schema version recorded in MySQL. Then run:

mysql-to-sqlite \
  --source 'mysql2://user:password@127.0.0.1/database' \
  --destination /absolute/path/to/database.sqlite3 \
  --yes

The URLs may instead be supplied as MYSQL_SOURCE_URL and SQLITE_DATABASE_PATH. Run mysql-to-sqlite --help for all options, or prefix the command with bundle exec when it is installed through an application's bundle.

--replace deletes records from matching destination tables before copying. --ignore-source-only skips tables found only in MySQL; use it only after inspecting why the schemas differ.

Stop all writes during the final import and keep verified backups. This tool copies database records only; application uploads and encryption keys must be preserved separately.

Test

The unit tests do not require a database server:

bundle exec rake test

The integration suite restores the SQL dumps under test/fixtures/mysql into a disposable MySQL database and imports them into temporary SQLite files. Start the provided MySQL 8.4 service and opt into those tests:

docker compose -f compose.test.yml up --detach --wait

MYSQL_TEST_URL='mysql2://root:test-password@127.0.0.1:3307/mysql_to_sqlite_test' \
  bundle exec rake test

docker compose -f compose.test.yml down

Set MYSQL_TEST_PORT on both Compose commands and use the same port in MYSQL_TEST_URL when port 3307 is unavailable.

The fixtures cover relationships, explicit primary keys and sequences, Unicode, binary and null values, replacement mode, source-only tables, schema mismatches, and migration-version mismatches. When MYSQL_TEST_URL is absent, integration cases are reported as skipped.