starred/bambuddy-maziggy-1

Fork 0

mirror of https://github.com/maziggy/bambuddy.git synced 2026-05-09 08:25:54 +02:00

[PR #432] [CLOSED] [Feature] - Adding MySql support #1045

New issue

Closed

opened 2026-05-07 00:15:40 +02:00 by BreizhHardware · 0 comments

BreizhHardware commented

2026-05-07 00:15:40 +02:00

Owner

📋 Pull Request Information

Original PR: https://github.com/maziggy/bambuddy/pull/432
Author: @wreuel
Created: 2/18/2026
Status: ❌ Closed

Base: 0.2.1b3 ← Head: feature/addingMySQLSupport

📝 Commits (10+)

392c192 Adding support to mysql
52b4263 Adding migration
a3feadf Fixing unit tests
12d3067 Merge branch '0.2.1b' into feature/addingMySQLSupport
412e354 Merge branch '0.2.1b' into feature/addingMySQLSupport
a954f0e Merge branch '0.2.1' into feature/addingMySQLSupport
6f5504b Merge branch '0.2.1b2' into feature/addingMySQLSupport
76c80ab Adding the migrations
2e03253 Merge branch '0.2.1b2' into feature/addingMySQLSupport
2d90006 Merge branch '0.2.1b3' into feature/addingMySQLSupport

📊 Changes

16 files changed (+1767 additions, -80 deletions)

View changed files

📝 .env.example (+11 -0)
➕ backend/alembic.ini (+37 -0)
➕ backend/alembic/env.py (+107 -0)
➕ backend/alembic/script.py.mako (+26 -0)
➕ backend/alembic/versions/0001_initial_schema.py (+815 -0)
➕ backend/alembic/versions/0002_add_virtual_printers_and_spool_core_weight_catalog_id.py (+55 -0)
📝 backend/app/api/routes/archives.py (+34 -14)
📝 backend/app/api/routes/auth.py (+6 -21)
📝 backend/app/api/routes/settings.py (+2 -6)
📝 backend/app/core/config.py (+40 -3)
📝 backend/app/core/database.py (+116 -28)
📝 backend/app/services/email_service.py (+2 -8)
📝 backend/tests/conftest.py (+1 -0)
➕ backend/tests/unit/test_mysql_compat.py (+442 -0)
➕ docker-compose.mysql.yml (+71 -0)
📝 requirements.txt (+2 -0)

📄 Description

Description

Adds optional MySQL/MariaDB support as an alternative database backend to SQLite. This is aimed at users running larger farms or multi-instance setups where SQLite's single-writer concurrency becomes a bottleneck.

Introduces DB_TYPE=mysql environment variable to switch from SQLite to MySQL/MariaDB
Adds Alembic migration framework for MySQL schema management (SQLite continues using inline migrations)
Includes a ready-to-use docker-compose.mysql.yml with MariaDB 11
All existing SQLite deployments are unaffected — SQLite remains the default

Fixes #130

Type of Change

Bug fix (non-breaking change that fixes an issue)
New feature (non-breaking change that adds functionality)
Breaking change (fix or feature that would cause existing functionality to change)
Documentation update
Code refactoring
Performance improvement
Test addition or update

Changes Made

Database layer

backend/app/core/config.py — New db_type, db_host, db_port, db_name, db_user, db_password settings with mysql+aiomysql URL builder
backend/app/core/database.py — Dual-path init: create_all() + inline migrations for SQLite, Alembic upgrade head for MySQL. MySQL-specific connection pooling (pool_recycle, pool_pre_ping)
backend/alembic/ — Full Alembic setup with async engine support
- env.py registers all models with Base.metadata
- 0001_initial_schema.py — Baseline migration matching the current SQLite schema (50+ tables)
- 0002_add_virtual_printers_and_spool_core_weight_catalog_id.py — Catches up with model changes from 0.2.1b2 merge

Docker

docker-compose.mysql.yml — Standalone compose file with Bambuddy + MariaDB 11, health checks, named volumes

Tests

backend/tests/unit/test_mysql_compat.py — 442-line test suite validating MySQL schema compatibility

Environment variables

Variable	Default	Description
`DB_TYPE`	`sqlite`	`sqlite` or `mysql`
`DB_HOST`	`localhost`	MySQL host
`DB_PORT`	`3306`	MySQL port
`DB_NAME`	`bambuddy`	Database name
`DB_USER`	—	Required for MySQL
`DB_PASSWORD`	—	Required for MySQL

How to add new Alembic migrations

When contributing model changes that add/remove/alter tables or columns, a new Alembic migration is needed for MySQL support. Here's how:

Prerequisites

A running MySQL/MariaDB instance with the current schema applied
Environment configured with DB_TYPE=mysql and valid credentials
Alembic installed (pip install alembic)

Option A: Auto-generate (recommended)

Alembic can diff your models against the live database and generate the migration automatically:

# From the project root
alembic -c backend/alembic.ini revision --autogenerate -m "short description of changes"

This creates a new file in backend/alembic/versions/ with the detected upgrade() and downgrade() operations.

Important: If you added a new model file, you must first import it in backend/alembic/env.py so Alembic can see it:
from backend.app.models import (  # noqa: E402, F401
    ...
    your_new_model,
)

Option B: Manual migration

If you prefer to write the migration by hand (or don't have a live MySQL instance):

alembic -c backend/alembic.ini revision -m "short description of changes"

Then edit the generated file and add your op.create_table(), op.add_column(), etc.

Applying migrations

# Apply all pending migrations
alembic -c backend/alembic.ini upgrade head

# Rollback last migration
alembic -c backend/alembic.ini downgrade -1

Quick reference (Alembic)

Alembic (Python)
`alembic revision --autogenerate -m "Name"`
`alembic upgrade head`
Delete the file + `alembic downgrade -1`
`alembic downgrade <revision>`

Screenshots

Testing

I have tested this on my local machine
I have tested with my printer model:

Checklist

My code follows the project's coding style
I have commented my code where necessary
I have updated the documentation (if needed)
My changes generate no new warnings
I have tested my changes thoroughly

Additional Notes

Important notes

SQLite users are not affected — SQLite continues using inline migrations in database.py
Always include a downgrade() — Every migration must be reversible
Naming convention — Files are prefixed with a sequential number: 0001_, 0002_, 0003_, etc.
down_revision chain — Each migration must point to the previous one (e.g., down_revision = "0002")

_{🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.}

## 📋 Pull Request Information **Original PR:** https://github.com/maziggy/bambuddy/pull/432 **Author:** [@wreuel](https://github.com/wreuel) **Created:** 2/18/2026 **Status:** ❌ Closed **Base:** `0.2.1b3` ← **Head:** `feature/addingMySQLSupport` --- ### 📝 Commits (10+) - [`392c192`](https://github.com/maziggy/bambuddy/commit/392c192bd70abba45445944568d9647fc0feb035) Adding support to mysql - [`52b4263`](https://github.com/maziggy/bambuddy/commit/52b4263f7a4cfdbfc50e997a075edc661b4edde0) Adding migration - [`a3feadf`](https://github.com/maziggy/bambuddy/commit/a3feadf5c94390522ed9e16257324c8524d0d35d) Fixing unit tests - [`12d3067`](https://github.com/maziggy/bambuddy/commit/12d306703e9121d17bb28b95e21f4156b3f35bfd) Merge branch '0.2.1b' into feature/addingMySQLSupport - [`412e354`](https://github.com/maziggy/bambuddy/commit/412e354748c69a7fb32bb75abb56527fc14008ce) Merge branch '0.2.1b' into feature/addingMySQLSupport - [`a954f0e`](https://github.com/maziggy/bambuddy/commit/a954f0ea033f07c24df12c5155e091bf5702b311) Merge branch '0.2.1' into feature/addingMySQLSupport - [`6f5504b`](https://github.com/maziggy/bambuddy/commit/6f5504bd3d0ad690c2d22c8fc30066bad821d906) Merge branch '0.2.1b2' into feature/addingMySQLSupport - [`76c80ab`](https://github.com/maziggy/bambuddy/commit/76c80ab480b4d0a1cc87d61d596f3441cd4bcc64) Adding the migrations - [`2e03253`](https://github.com/maziggy/bambuddy/commit/2e03253ecb1e8f729e7547000ffbb7261f491baf) Merge branch '0.2.1b2' into feature/addingMySQLSupport - [`2d90006`](https://github.com/maziggy/bambuddy/commit/2d90006c8f359b6a11a5449fe3c9c3160582253d) Merge branch '0.2.1b3' into feature/addingMySQLSupport ### 📊 Changes **16 files changed** (+1767 additions, -80 deletions) <details> <summary>View changed files</summary> 📝 `.env.example` (+11 -0) ➕ `backend/alembic.ini` (+37 -0) ➕ `backend/alembic/env.py` (+107 -0) ➕ `backend/alembic/script.py.mako` (+26 -0) ➕ `backend/alembic/versions/0001_initial_schema.py` (+815 -0) ➕ `backend/alembic/versions/0002_add_virtual_printers_and_spool_core_weight_catalog_id.py` (+55 -0) 📝 `backend/app/api/routes/archives.py` (+34 -14) 📝 `backend/app/api/routes/auth.py` (+6 -21) 📝 `backend/app/api/routes/settings.py` (+2 -6) 📝 `backend/app/core/config.py` (+40 -3) 📝 `backend/app/core/database.py` (+116 -28) 📝 `backend/app/services/email_service.py` (+2 -8) 📝 `backend/tests/conftest.py` (+1 -0) ➕ `backend/tests/unit/test_mysql_compat.py` (+442 -0) ➕ `docker-compose.mysql.yml` (+71 -0) 📝 `requirements.txt` (+2 -0) </details> ### 📄 Description ## Description Adds optional MySQL/MariaDB support as an alternative database backend to SQLite. This is aimed at users running larger farms or multi-instance setups where SQLite's single-writer concurrency becomes a bottleneck. - Introduces `DB_TYPE=mysql` environment variable to switch from SQLite to MySQL/MariaDB - Adds Alembic migration framework for MySQL schema management (SQLite continues using inline migrations) - Includes a ready-to-use `docker-compose.mysql.yml` with MariaDB 11 - All existing SQLite deployments are **unaffected** — SQLite remains the default ## Related Issue Fixes #130 ## Type of Change  - [ ] Bug fix (non-breaking change that fixes an issue) - [ ] New feature (non-breaking change that adds functionality) - [X] Breaking change (fix or feature that would cause existing functionality to change) - [ ] Documentation update - [ ] Code refactoring - [ ] Performance improvement - [ ] Test addition or update ## Changes Made ### Database layer - **`backend/app/core/config.py`** — New `db_type`, `db_host`, `db_port`, `db_name`, `db_user`, `db_password` settings with `mysql+aiomysql` URL builder - **`backend/app/core/database.py`** — Dual-path init: `create_all()` + inline migrations for SQLite, Alembic `upgrade head` for MySQL. MySQL-specific connection pooling (`pool_recycle`, `pool_pre_ping`) - **`backend/alembic/`** — Full Alembic setup with async engine support - `env.py` registers all models with `Base.metadata` - `0001_initial_schema.py` — Baseline migration matching the current SQLite schema (50+ tables) - `0002_add_virtual_printers_and_spool_core_weight_catalog_id.py` — Catches up with model changes from `0.2.1b2` merge ### Docker - **`docker-compose.mysql.yml`** — Standalone compose file with Bambuddy + MariaDB 11, health checks, named volumes ### Tests - **`backend/tests/unit/test_mysql_compat.py`** — 442-line test suite validating MySQL schema compatibility ## Environment variables | Variable | Default | Description | |----------|---------|-------------| | `DB_TYPE` | `sqlite` | `sqlite` or `mysql` | | `DB_HOST` | `localhost` | MySQL host | | `DB_PORT` | `3306` | MySQL port | | `DB_NAME` | `bambuddy` | Database name | | `DB_USER` | — | Required for MySQL | | `DB_PASSWORD` | — | Required for MySQL | ## How to add new Alembic migrations When contributing model changes that add/remove/alter tables or columns, a new Alembic migration is needed for MySQL support. Here's how: ### Prerequisites 1. A running MySQL/MariaDB instance with the current schema applied 2. Environment configured with `DB_TYPE=mysql` and valid credentials 3. Alembic installed (`pip install alembic`) ### Option A: Auto-generate (recommended) Alembic can diff your models against the live database and generate the migration automatically: ```bash # From the project root alembic -c backend/alembic.ini revision --autogenerate -m "short description of changes" ``` This creates a new file in `backend/alembic/versions/` with the detected `upgrade()` and `downgrade()` operations. > **Important:** If you added a **new model file**, you must first import it in [`backend/alembic/env.py`](backend/alembic/env.py) so Alembic can see it: > ```python > from backend.app.models import ( # noqa: E402, F401 > ... > your_new_model, > ) > ``` ### Option B: Manual migration If you prefer to write the migration by hand (or don't have a live MySQL instance): ```bash alembic -c backend/alembic.ini revision -m "short description of changes" ``` Then edit the generated file and add your `op.create_table()`, `op.add_column()`, etc. ### Applying migrations ```bash # Apply all pending migrations alembic -c backend/alembic.ini upgrade head # Rollback last migration alembic -c backend/alembic.ini downgrade -1 ``` ### Quick reference (Alembic) | Alembic (Python) | |---| | `alembic revision --autogenerate -m "Name"` | | `alembic upgrade head` | | Delete the file + `alembic downgrade -1` | | `alembic downgrade <revision>` | ## Screenshots  ## Testing  - [ ] I have tested this on my local machine - [ ] I have tested with my printer model:  ## Checklist - [ ] My code follows the project's coding style - [ ] I have commented my code where necessary - [ ] I have updated the documentation (if needed) - [ ] My changes generate no new warnings - [ ] I have tested my changes thoroughly ## Additional Notes  ### Important notes - **SQLite users are not affected** — SQLite continues using inline migrations in `database.py` - **Always include a `downgrade()`** — Every migration must be reversible - **Naming convention** — Files are prefixed with a sequential number: `0001_`, `0002_`, `0003_`, etc. - **`down_revision` chain** — Each migration must point to the previous one (e.g., `down_revision = "0002"`) --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>