customable/claude-mem

Fork 0

feat: Multi-Database Support (SQLite + PostgreSQL) #262

New issue

Closed

opened 2026-01-25 11:17:51 +00:00 by jack · 2 comments

jack commented

2026-01-25 11:17:51 +00:00

Owner

Problem

better-sqlite3 erfordert native Compilation, was bei der NPM-Distribution Probleme verursachen kann:

User brauchen Build-Tools (Python, C++ Compiler)
Plattform-spezifische Binaries
Mögliche Kompatibilitätsprobleme

Außerdem ist SQLite nicht für Multi-User/Remote-Szenarien geeignet.

Lösung

Hybrid-Ansatz: SQLite als Default, PostgreSQL als Option für Power-User/SaaS.

# Default (SQLite)
claude-mem-backend start

# Mit PostgreSQL
claude-mem-backend start --db postgres://localhost/claudemem

Aufgaben

Phase 1: SQLite-Probleme lösen

prebuild-install für prebuilt binaries konfigurieren
Fallback-Doku für User ohne Build-Tools
CI: Prebuild für alle Plattformen (Linux, macOS, Windows)

Phase 2: PostgreSQL-Support

MikroORM-Config für PostgreSQL erweitern
Connection-String aus Environment/CLI-Flag
Migrations für beide DBs testen
Dokumentation für PostgreSQL-Setup

Phase 3: Optional - libSQL evaluieren

libSQL als modernere SQLite-Alternative testen
Performance-Vergleich
Embedded vs. Remote Modus

Akzeptanzkriterien

pnpm install funktioniert ohne Build-Tools (prebuilt binaries)
PostgreSQL kann via DATABASE_URL Environment-Variable aktiviert werden
Alle Tests laufen mit SQLite UND PostgreSQL
Dokumentation für beide Datenbank-Optionen

Technische Details

MikroORM unterstützt bereits beide Datenbanken. Änderungen hauptsächlich in:

packages/database/src/mikro-orm.config.ts
packages/shared/src/settings.ts
CI/CD für Prebuild

Verwandte Issues

#261 - NPM-Package Distribution
#197 - Database Schema Redesign

## Problem `better-sqlite3` erfordert native Compilation, was bei der NPM-Distribution Probleme verursachen kann: - User brauchen Build-Tools (Python, C++ Compiler) - Plattform-spezifische Binaries - Mögliche Kompatibilitätsprobleme Außerdem ist SQLite nicht für Multi-User/Remote-Szenarien geeignet. ## Lösung Hybrid-Ansatz: SQLite als Default, PostgreSQL als Option für Power-User/SaaS. ```bash # Default (SQLite) claude-mem-backend start # Mit PostgreSQL claude-mem-backend start --db postgres://localhost/claudemem ``` ## Aufgaben ### Phase 1: SQLite-Probleme lösen - [ ] `prebuild-install` für prebuilt binaries konfigurieren - [ ] Fallback-Doku für User ohne Build-Tools - [ ] CI: Prebuild für alle Plattformen (Linux, macOS, Windows) ### Phase 2: PostgreSQL-Support - [ ] MikroORM-Config für PostgreSQL erweitern - [ ] Connection-String aus Environment/CLI-Flag - [ ] Migrations für beide DBs testen - [ ] Dokumentation für PostgreSQL-Setup ### Phase 3: Optional - libSQL evaluieren - [ ] libSQL als modernere SQLite-Alternative testen - [ ] Performance-Vergleich - [ ] Embedded vs. Remote Modus ## Akzeptanzkriterien - [ ] `pnpm install` funktioniert ohne Build-Tools (prebuilt binaries) - [ ] PostgreSQL kann via `DATABASE_URL` Environment-Variable aktiviert werden - [ ] Alle Tests laufen mit SQLite UND PostgreSQL - [ ] Dokumentation für beide Datenbank-Optionen ## Technische Details MikroORM unterstützt bereits beide Datenbanken. Änderungen hauptsächlich in: - `packages/database/src/mikro-orm.config.ts` - `packages/shared/src/settings.ts` - CI/CD für Prebuild ## Verwandte Issues - #261 - NPM-Package Distribution - #197 - Database Schema Redesign

jack added the

labels

2026-01-25 11:17:53 +00:00

jack referenced this issue

2026-01-25 13:01:00 +00:00

MikroORM Best Practices - Entity Relations & Unit of Work Refactoring #267

jack added a new dependency

2026-01-25 13:01:12 +00:00

#267 MikroORM Best Practices - Entity Relations & Unit of Work Refactoring

jack referenced this issue

2026-01-25 16:30:28 +00:00

MikroORM Best Practices - Entity Relations & Unit of Work Refactoring #267

jack removed a dependency

2026-01-25 16:39:13 +00:00

#267 MikroORM Best Practices - Entity Relations & Unit of Work Refactoring

jack changed title from ~~discussion: SQLite vs. Alternative Datenbanken~~ to feat: Multi-Database Support (SQLite + PostgreSQL)

2026-01-25 18:40:07 +00:00

jack added

and removed

labels

2026-01-25 18:40:10 +00:00

jack commented

2026-01-25 18:59:53 +00:00

Author

Owner

Progress Update: Phase 2 - PostgreSQL CLI Support

Added PostgreSQL connection string support to the CLI:

# SQLite (default, unchanged)
claude-mem-backend start --db ~/.claude-mem/custom.db

# PostgreSQL connection string (new)
claude-mem-backend start --db postgres://user:pass@localhost:5432/claudemem

The connection string is automatically parsed and mapped to settings:

DATABASE_TYPE → postgres
DATABASE_HOST → from URL
DATABASE_PORT → from URL (default: 5432)
DATABASE_USER → from URL
DATABASE_PASSWORD → from URL
DATABASE_NAME → from URL path

Existing Support

The backend already supported PostgreSQL through MikroORM and settings:

CLAUDE_MEM_DATABASE_TYPE=postgres
CLAUDE_MEM_DATABASE_HOST, CLAUDE_MEM_DATABASE_PORT, etc.
Settings file: DATABASE_TYPE: 'postgres'

Remaining Tasks

Phase 1: prebuild-install for better-sqlite3 (CI configuration)
Phase 2: PostgreSQL connection via CLI flag
Phase 3: libSQL evaluation (optional)

## Progress Update: Phase 2 - PostgreSQL CLI Support Added PostgreSQL connection string support to the CLI: ```bash # SQLite (default, unchanged) claude-mem-backend start --db ~/.claude-mem/custom.db # PostgreSQL connection string (new) claude-mem-backend start --db postgres://user:pass@localhost:5432/claudemem ``` The connection string is automatically parsed and mapped to settings: - `DATABASE_TYPE` → `postgres` - `DATABASE_HOST` → from URL - `DATABASE_PORT` → from URL (default: 5432) - `DATABASE_USER` → from URL - `DATABASE_PASSWORD` → from URL - `DATABASE_NAME` → from URL path ### Existing Support The backend already supported PostgreSQL through MikroORM and settings: - `CLAUDE_MEM_DATABASE_TYPE=postgres` - `CLAUDE_MEM_DATABASE_HOST`, `CLAUDE_MEM_DATABASE_PORT`, etc. - Settings file: `DATABASE_TYPE: 'postgres'` ### Remaining Tasks - [ ] **Phase 1**: prebuild-install for better-sqlite3 (CI configuration) - [x] **Phase 2**: PostgreSQL connection via CLI flag - [ ] **Phase 3**: libSQL evaluation (optional)

jack commented

2026-01-25 19:08:27 +00:00

Author

Owner

Implementation Complete

Phase 1: SQLite Prebuilt Support

✅ better-sqlite3 already includes prebuild-install (v11.10.0)
✅ Documentation for build tools fallback added in docs/DATABASE.md
Note: Custom CI prebuilds not needed since better-sqlite3 provides prebuilts for common platforms

Phase 2: PostgreSQL Support

✅ MikroORM config already supports PostgreSQL (mikro-orm.config.ts)
✅ CLI --db flag for PostgreSQL connection strings
✅ DATABASE_URL environment variable parsing in settings.ts
✅ Migration tool supports PostgreSQL as target
✅ Comprehensive documentation in docs/DATABASE.md

Changes Made

packages/shared/src/settings.ts
- Added parseDatabaseUrl() method for DATABASE_URL parsing
- Supports postgres:// and postgresql:// connection strings
packages/backend/src/cli.ts
- Added parseDatabaseOption() for --db flag
- Migration import supports --target with PostgreSQL
docs/DATABASE.md (new)
- SQLite setup and native compilation notes
- PostgreSQL installation and configuration
- Data migration instructions
- Environment variables reference
- Troubleshooting guide
README.md
- Updated to reference DATABASE.md
- Added PostgreSQL usage examples
- Updated architecture diagram

Usage

# SQLite (default)
claude-mem-backend start

# PostgreSQL via CLI flag
claude-mem-backend start --db postgres://user:pass@localhost:5432/claudemem

# PostgreSQL via environment variable
export DATABASE_URL=postgres://user:pass@localhost:5432/claudemem
claude-mem-backend start

# Migrate to PostgreSQL
claude-mem-backend migrate import legacy.db --target postgres://user:pass@localhost:5432/claudemem

Commits

f68783d feat(backend): add PostgreSQL connection string support to CLI (Issue #262)
b69f7bb feat(backend): support PostgreSQL as migration target (Issue #198, #262)
31e0317 docs: add multi-database documentation and DATABASE_URL support (Issue #262)

## Implementation Complete ### Phase 1: SQLite Prebuilt Support - ✅ `better-sqlite3` already includes `prebuild-install` (v11.10.0) - ✅ Documentation for build tools fallback added in `docs/DATABASE.md` - Note: Custom CI prebuilds not needed since better-sqlite3 provides prebuilts for common platforms ### Phase 2: PostgreSQL Support - ✅ MikroORM config already supports PostgreSQL (`mikro-orm.config.ts`) - ✅ CLI `--db` flag for PostgreSQL connection strings - ✅ `DATABASE_URL` environment variable parsing in `settings.ts` - ✅ Migration tool supports PostgreSQL as target - ✅ Comprehensive documentation in `docs/DATABASE.md` ### Changes Made 1. **packages/shared/src/settings.ts** - Added `parseDatabaseUrl()` method for DATABASE_URL parsing - Supports `postgres://` and `postgresql://` connection strings 2. **packages/backend/src/cli.ts** - Added `parseDatabaseOption()` for `--db` flag - Migration import supports `--target` with PostgreSQL 3. **docs/DATABASE.md** (new) - SQLite setup and native compilation notes - PostgreSQL installation and configuration - Data migration instructions - Environment variables reference - Troubleshooting guide 4. **README.md** - Updated to reference DATABASE.md - Added PostgreSQL usage examples - Updated architecture diagram ### Usage ```bash # SQLite (default) claude-mem-backend start # PostgreSQL via CLI flag claude-mem-backend start --db postgres://user:pass@localhost:5432/claudemem # PostgreSQL via environment variable export DATABASE_URL=postgres://user:pass@localhost:5432/claudemem claude-mem-backend start # Migrate to PostgreSQL claude-mem-backend migrate import legacy.db --target postgres://user:pass@localhost:5432/claudemem ``` ### Commits - `f68783d` feat(backend): add PostgreSQL connection string support to CLI (Issue #262) - `b69f7bb` feat(backend): support PostgreSQL as migration target (Issue #198, #262) - `31e0317` docs: add multi-database documentation and DATABASE_URL support (Issue #262)

jack closed this issue

2026-01-25 19:08:31 +00:00