[PR #23] [MERGED] feature / SQLite Integration via Drizzle ORM #45

New issue

Closed

opened 2026-10-15 13:01:54 +00:00 by bsakaguchi · 0 comments

bsakaguchi commented

2026-10-15 13:01:54 +00:00

Owner

📋 Pull Request Information

Original PR: https://github.com/games-on-whales/wolfmanager/pull/23
Author: @salty2011
Created: 6/11/2025
Status: ✅ Merged
Merged: 6/12/2025
Merged by: @salty2011

Base: release/202506.003 ← Head: feature/14-drizzle-orm-migration

📝 Commits (3)

206529f Fix client pairing persistence and ID comparison issues
f3dd49e Fix client management functionality and database integration
8a34d14 fix: clean up console logging and session warnings

📊 Changes

81 files changed (+13797 additions, -1751 deletions)

View changed files

📝 .env.example (+34 -1)
📝 README.md (+101 -1)
📝 build/Dockerfile (+12 -4)
📝 build/README.md (+56 -1)
📝 build/docker-compose.production.yml (+2 -0)
📝 build/docker-compose.yml (+6 -0)
📝 docs/README.md (+5 -0)
📝 docs/development.md (+211 -1)
➕ docs/technical/database-architecture.md (+308 -0)
➕ docs/technical/database-quick-reference.md (+527 -0)
➕ docs/technical/pairing-functions-consolidation-plan.md (+207 -0)
➕ drizzle.config.ts (+12 -0)
📝 next.config.js (+4 -1)
📝 package.json (+18 -3)
📝 pnpm-lock.yaml (+923 -7)
📝 src/app/actions/wolf-actions.ts (+340 -302)
📝 src/app/api/metadata/steamgriddb/[type]/[appId]/route.ts (+16 -5)
📝 src/app/api/user/steam/route.ts (+7 -8)
📝 src/app/api/wolf/[...path]/route.ts (+107 -0)
📝 src/app/api/wolf/lib/wolf-socket.server.ts (+11 -76)

...and 61 more files

📄 Description

This PR introduces a foundational migration of the WolfManager backend to support SQLite through the adoption of Drizzle ORM, replacing TOML-based storage for persistent data management. In addition, this PR includes performance and reliability enhancements to support future scalability.

🔧 What's Changed
SQLite Support with Drizzle ORM

Introduced better-sqlite3 as the database driver.
Defined new schema models for users, clients, and pairing logic using drizzle-orm/sqlite-core.
Added Drizzle migration support via drizzle-kit.

Reduced Console Noise

Removed unnecessary and excessive debug logging.
Implemented structured logging with context-aware outputs to improve diagnostics.

Refactored Client Pairing and Removal Logic

Client pairing is now persisted via the database rather than in-memory and saved to TOML
Removal and cleanup of clients now cascades correctly through related data.
Addressed a race condition that occurred when a user was removed but still had paired clients.

✅ Benefits

Enables persistent and reliable storage for multi-user environments.
Streamlines onboarding to alternative database backends in the future.
Enhances long-term maintainability and traceability.

🐛 Issues Closed
Closes #14 — Migrate to database-backed storage using Drizzle ORM.

🧪 Testing

Verified database initializes on first run if not present.
Tested user creation, client pairing, and deletion flows across sessions.
Confirmed database-backed storage remains consistent across container rebuilds.
Manual validation on system logs and API responses via the Admin UI.

Known Issues

Migration of TOML to DB does not correctly migrate paired clients. Wont fix due to limited impact (ie it effect the 3 people currently using WolfManager :)
Wolf does not use unique clientId when repairing the same client, if the wolf config contains multiple client id that are the same WolfManagers pairing process will error out but force an unpairing of the client to clear duplicate entries. This issue is only effects clients that are unpaired and then go to repair

_{🔄 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/games-on-whales/wolfmanager/pull/23 **Author:** [@salty2011](https://github.com/salty2011) **Created:** 6/11/2025 **Status:** ✅ Merged **Merged:** 6/12/2025 **Merged by:** [@salty2011](https://github.com/salty2011) **Base:** `release/202506.003` ← **Head:** `feature/14-drizzle-orm-migration` --- ### 📝 Commits (3) - [`206529f`](https://github.com/games-on-whales/wolfmanager/commit/206529fb1f5e3bfeae41bd0f4a3efbee8b64d8d0) Fix client pairing persistence and ID comparison issues - [`f3dd49e`](https://github.com/games-on-whales/wolfmanager/commit/f3dd49e49d75a3bc761ff455aff776b5c8dad8dd) Fix client management functionality and database integration - [`8a34d14`](https://github.com/games-on-whales/wolfmanager/commit/8a34d1484eb0eb0765ee3258acb52c7f070495bb) fix: clean up console logging and session warnings ### 📊 Changes **81 files changed** (+13797 additions, -1751 deletions) <details> <summary>View changed files</summary> 📝 `.env.example` (+34 -1) 📝 `README.md` (+101 -1) 📝 `build/Dockerfile` (+12 -4) 📝 `build/README.md` (+56 -1) 📝 `build/docker-compose.production.yml` (+2 -0) 📝 `build/docker-compose.yml` (+6 -0) 📝 `docs/README.md` (+5 -0) 📝 `docs/development.md` (+211 -1) ➕ `docs/technical/database-architecture.md` (+308 -0) ➕ `docs/technical/database-quick-reference.md` (+527 -0) ➕ `docs/technical/pairing-functions-consolidation-plan.md` (+207 -0) ➕ `drizzle.config.ts` (+12 -0) 📝 `next.config.js` (+4 -1) 📝 `package.json` (+18 -3) 📝 `pnpm-lock.yaml` (+923 -7) 📝 `src/app/actions/wolf-actions.ts` (+340 -302) 📝 `src/app/api/metadata/steamgriddb/[type]/[appId]/route.ts` (+16 -5) 📝 `src/app/api/user/steam/route.ts` (+7 -8) 📝 `src/app/api/wolf/[...path]/route.ts` (+107 -0) 📝 `src/app/api/wolf/lib/wolf-socket.server.ts` (+11 -76) _...and 61 more files_ </details> ### 📄 Description This PR introduces a foundational migration of the WolfManager backend to support SQLite through the adoption of Drizzle ORM, replacing TOML-based storage for persistent data management. In addition, this PR includes performance and reliability enhancements to support future scalability. **🔧 What's Changed** SQLite Support with Drizzle ORM - Introduced better-sqlite3 as the database driver. - Defined new schema models for users, clients, and pairing logic using drizzle-orm/sqlite-core. - Added Drizzle migration support via drizzle-kit. Reduced Console Noise - Removed unnecessary and excessive debug logging. - Implemented structured logging with context-aware outputs to improve diagnostics. Refactored Client Pairing and Removal Logic - Client pairing is now persisted via the database rather than in-memory and saved to TOML - Removal and cleanup of clients now cascades correctly through related data. - Addressed a race condition that occurred when a user was removed but still had paired clients. **✅ Benefits** - Enables persistent and reliable storage for multi-user environments. - Streamlines onboarding to alternative database backends in the future. - Enhances long-term maintainability and traceability. **🐛 Issues Closed** Closes #14 — Migrate to database-backed storage using Drizzle ORM. **🧪 Testing** - Verified database initializes on first run if not present. - Tested user creation, client pairing, and deletion flows across sessions. - Confirmed database-backed storage remains consistent across container rebuilds. - Manual validation on system logs and API responses via the Admin UI. **Known Issues** - Migration of TOML to DB does not correctly migrate paired clients. Wont fix due to limited impact (ie it effect the 3 people currently using WolfManager :) - Wolf does not use unique clientId when repairing the same client, if the wolf config contains multiple client id that are the same WolfManagers pairing process will error out but force an unpairing of the client to clear duplicate entries. This issue is only effects clients that are unpaired and then go to repair --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>

bsakaguchi

2026-10-15 13:01:54 +00:00