Files
mytodos/migration-guide.md
ecdown ad1c093199
tests / ci (push) Canceled after 0s
moving to mysql
2026-09-24 21:13:19 -07:00

6.8 KiB

Production Migration Guide: SQLite → MySQL

This runbook moves the application's production database from SQLite to MySQL 8, preserving existing data. It mirrors the local cutover and relies on the db:transfer-sqlite command added in app/Console/Commands/TransferSqliteData.php.

Read the whole guide before starting. Every step assumes you can SSH into the production server and run commands from the app root.


0. Preconditions

  • MySQL 8 server reachable from the app server (5.7+ and MariaDB also work, but MySQL 8 is the tested target).
  • A database user with privileges to create the database and tables.
  • Put the app into read-only/maintenance mode during the cutover so no new writes land in SQLite while you migrate:
php artisan down
  • Record the size and location of the current database:
ls -lh database/database.sqlite

Note the path. If it is not database/database.sqlite, you will pass it to the transfer command with --source.


1. Back up the SQLite database

Keep the SQLite file intact all the way through. Copy it to a timestamped backup and move the original aside (it is the documented rollback path):

cp database/database.sqlite database/database.sqlite.bak-$(date +%Y%m%d%H%M%S)

The transfer command reads the file directly, so the original must still be at database/database.sqlite (or referenced via --source) during the cutover.


2. Create the MySQL database and user

Run from a MySQL client with admin access:

CREATE DATABASE mytodos
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE USER 'mytodos'@'%' IDENTIFIED BY '<strong-password>';
GRANT ALL PRIVILEGES ON mytodos.* TO 'mytodos'@'%';
FLUSH PRIVILEGES;

Use a user scoped to mytodos.* — do not grant global privileges. Restrict 'mytodos'@'localhost' instead of '%' if the app always connects via the local socket/host.


3. Deploy the MySQL-compatible code

The code changes from the local migration must be on the server:

git pull --ff-only origin main
composer install --no-dev --prefer-dist --optimize-autoloader
npm ci --no-audit --no-fund
npm run build

Relevant changes in this release:

  • config/database.php — MySQL default connection + sqlite_source connection.
  • app/Console/Commands/TransferSqliteData.php — the db:transfer-sqlite command.
  • phpunit.xml, .env.testing(.example), .github/workflows/tests.yml — test/CI only, no production impact.

4. Update the environment

Switch the application to MySQL, using the credentials from step 2:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=mytodos
DB_USERNAME=mytodos
DB_PASSWORD=<strong-password>
  • Keep the old database/database.sqlite file in place (see step 6).
  • If you previously ran php artisan config:cache, the config is cached and will not pick up the new .env until step 8. Run php artisan config:clear before the next config:cache, or rely on the cache rebuild in step 8.

5. Run the migrations against the empty MySQL database

The MySQL database is still empty. Apply the schema:

php artisan migrate --force

Do not seed yet — the data comes from SQLite in the next step.


6. Transfer the data

Run the transfer command. It copies the domain tables (users, folders, todos, passkeys, password_reset_tokens) from SQLite into MySQL, preserving IDs, then resets MySQL auto-increment counters.

If the SQLite file is at the default path:

php artisan db:transfer-sqlite --force

If it lives elsewhere:

php artisan db:transfer-sqlite --source=/var/www/app/database/database.sqlite --force

The --force flag is required because APP_ENV is production. The command:

  • aborts unless the destination connection is MySQL;
  • truncates the destination domain tables before inserting (it is idempotent);
  • skips framework/transient tables (cache, sessions, jobs, migrations, …).

Expected output:

Copying data from /var/www/app/database/database.sqlite into the 'mysql' connection.
  transferred N row(s) into users.
  transferred N row(s) into folders.
  ...
Data transfer complete.

7. Verify the transfer

Compare row counts and spot-check a sample:

php artisan tinker --execute '
  $source = DB::connection("sqlite_source");
  foreach (["users", "folders", "todos", "passkeys"] as $table) {
      echo $table.": ".$source->table($table)->count()." -> ".DB::table($table)->count().PHP_EOL;
  }
'

Manually confirm a few rows and the max IDs on MySQL:

php artisan tinker --execute '
  foreach (["users", "folders", "todos", "passkeys"] as $table) {
      echo $table." max id: ".DB::table($table)->max("id").PHP_EOL;
  }
'

Then re-create the admin/administrator account that BaseDatabaseSeeder owns:

php artisan db:seed --class=BaseDatabaseSeeder --force

8. Finalize

php artisan config:cache
php artisan route:cache
php artisan view:cache
php artisan queue:restart
php artisan up

Smoke-test from outside: log in, load the dashboard, run a search, toggle a status filter, and drag-reorder a todo.


9. Post-migration cleanup (optional, after a soak period)

Once you are confident MySQL is healthy:

  • Archive the SQLite backup off the server (or delete it) after you have confirmed there are no rollback triggers.
  • Consider deleting the one-time app/Console/Commands/TransferSqliteData.php command so it can never truncate production tables by accident.
  • Update deploy.sh's comments if they reference SQLite.

Rollback

  1. php artisan down.
  2. Point the app back at SQLite:
DB_CONNECTION=sqlite
# remove the DB_HOST/DB_PORT/DB_DATABASE/DB_USERNAME/DB_PASSWORD MySQL lines
  1. Replace the (unmigrated) original with your backup:
cp database/database.sqlite.bak-<timestamp> database/database.sqlite
  1. php artisan config:clear && php artisan up.

Note: any rows written into MySQL after step 5 are not on the SQLite backup and will be lost on rollback.


Production notes

  • Ordering matters: migrate → transfer → seed. Running db:seed before the transfer would create a conflicting admin user truncated by the transfer's destination-table wipe.
  • Timezone: SQLite stores Laravel timestamps as YYYY-MM-DD HH:MM:SS text. Those strings transfer verbatim and MySQL interprets them as-is; ensure the MySQL session timezone matches the app's app.timezone/APP_TIMEZONE if you observe hour shifts.
  • Search behavior: LIKE becomes case- and accent-insensitive under utf8mb4_unicode_ci — a deliberate, generally desirable change from SQLite's ASCII-only behavior.
  • Index/key limits: the schema requires MySQL 5.7.7+ / 8.0 (InnoDB 3072-byte key limit). Do not run this against MySQL 5.6.