feat: migrate from SQLite to MySQL (external server) #10

New Issue

martin · 2026-06-17T08:42:58Z

martin commented

2026-06-17 08:42:58 +00:00

Goal

Replace the local SQLite file with a MySQL database running on an external server. All existing Alembic migration history ports cleanly — nothing in the migrations is SQLite-specific.

Code changes

1. Add PyMySQL driver (`requirements.txt`)

PyMySQL==1.1.1

Pure Python — no OS packages needed in the Docker image.

2. Make `render_as_batch` conditional (`alembic/env.py`)

render_as_batch=True is a SQLite workaround (it re-creates tables to emulate ALTER TABLE). It must be False on MySQL or Alembic will do unnecessary full-table copies on every schema migration.

is_sqlite = settings.database_url.startswith("sqlite")
context.configure(
    connection=connection,
    target_metadata=target_metadata,
    render_as_batch=is_sqlite,
)

Apply the same change in run_migrations_offline().

3. Update `.env`

DATABASE_URL=mysql+pymysql://flipventory:PASSWORD@db.host.example:3306/flipventory?charset=utf8mb4

External server setup

Run once on the MySQL server:

CREATE DATABASE flipventory
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

CREATE USER 'flipventory'@'%' IDENTIFIED BY 'strong-password-here';
GRANT ALL PRIVILEGES ON flipventory.* TO 'flipventory'@'%';
FLUSH PRIVILEGES;

utf8mb4 is required — MySQL's legacy utf8 is 3-byte and silently drops emoji and other 4-byte characters.

Applying migrations to the new database

docker compose run --rm app alembic upgrade head

This creates all tables fresh. The existing five migration files run cleanly on MySQL.

Data migration (if live data exists in SQLite)

Write a one-off script that:

Opens both engines simultaneously (SQLite source, MySQL target)
Reads each table in FK-safe order: users → storage_bins → items → audit_log
Bulk-inserts with INSERT IGNORE to be idempotent
Verifies row counts match before and after

Script lives in scripts/migrate_sqlite_to_mysql.py and is deleted after use.

Testing checklist

alembic upgrade head completes without errors on MySQL
Register a user, create a bin, add items — all CRUD paths work
Profit calculation, audit log, and admin pages render correctly
alembic downgrade -1 / upgrade head round-trips cleanly
Container restart does not lose data

## Goal Replace the local SQLite file with a MySQL database running on an external server. All existing Alembic migration history ports cleanly — nothing in the migrations is SQLite-specific. ## Code changes ### 1. Add PyMySQL driver (`requirements.txt`) ``` PyMySQL==1.1.1 ``` Pure Python — no OS packages needed in the Docker image. ### 2. Make `render_as_batch` conditional (`alembic/env.py`) `render_as_batch=True` is a SQLite workaround (it re-creates tables to emulate ALTER TABLE). It must be `False` on MySQL or Alembic will do unnecessary full-table copies on every schema migration. ```python is_sqlite = settings.database_url.startswith("sqlite") context.configure( connection=connection, target_metadata=target_metadata, render_as_batch=is_sqlite, ) ``` Apply the same change in `run_migrations_offline()`. ### 3. Update `.env` ``` DATABASE_URL=mysql+pymysql://flipventory:PASSWORD@db.host.example:3306/flipventory?charset=utf8mb4 ``` ## External server setup Run once on the MySQL server: ```sql CREATE DATABASE flipventory CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE USER 'flipventory'@'%' IDENTIFIED BY 'strong-password-here'; GRANT ALL PRIVILEGES ON flipventory.* TO 'flipventory'@'%'; FLUSH PRIVILEGES; ``` `utf8mb4` is required — MySQL's legacy `utf8` is 3-byte and silently drops emoji and other 4-byte characters. ## Applying migrations to the new database ```bash docker compose run --rm app alembic upgrade head ``` This creates all tables fresh. The existing five migration files run cleanly on MySQL. ## Data migration (if live data exists in SQLite) Write a one-off script that: 1. Opens both engines simultaneously (SQLite source, MySQL target) 2. Reads each table in FK-safe order: `users` → `storage_bins` → `items` → `audit_log` 3. Bulk-inserts with `INSERT IGNORE` to be idempotent 4. Verifies row counts match before and after Script lives in `scripts/migrate_sqlite_to_mysql.py` and is deleted after use. ## Testing checklist - [ ] `alembic upgrade head` completes without errors on MySQL - [ ] Register a user, create a bin, add items — all CRUD paths work - [ ] Profit calculation, audit log, and admin pages render correctly - [ ] `alembic downgrade -1` / `upgrade head` round-trips cleanly - [ ] Container restart does not lose data

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: martin/flipventory_v2#10

feat: migrate from SQLite to MySQL (external server) #10