MongoDB to SQLite Migration Tool

A standalone CLI utility to migrate data from a MongoDB database to SQLite format. Designed specifically for migrating Quilltap 2.7.x MongoDB databases to 2.8.x SQLite format.

Features

• Complete schema migration - Creates all Quilltap tables with proper indexes
• Data transformation - Automatically converts MongoDB documents to SQLite rows
• Dependency-aware - Migrates collections in priority order to maintain referential integrity
• Dry-run mode - Preview record counts before executing migration
• Verbose logging - Optional detailed output for debugging
• Batch processing - Efficiently handles large datasets
• Error handling - Graceful error reporting with detailed logs

Installation

Install globally or use with npx:

npm install -g @quilltap/mongodb-to-sqlite

Or use directly without installation:

npx @quilltap/mongodb-to-sqlite [options]

Usage

Basic Migration

mongodb-to-sqlite \
  --mongo-uri "mongodb://localhost:27017" \
  --output ./quilltap.db

With Authentication

mongodb-to-sqlite \
  --mongo-uri "mongodb://user:password@host:27017" \
  --db-name mydb \
  --output /path/to/quilltap.db

Dry Run (Preview Only)

mongodb-to-sqlite \
  --mongo-uri "mongodb://localhost:27017" \
  --db-name quilltap \
  --dry-run

Verbose Output

mongodb-to-sqlite \
  --mongo-uri "mongodb://localhost:27017" \
  --output ./quilltap.db \
  --verbose

Options

| Option | Short | Required | Default | Description | | -------- | ------- | ---------- | --------- | ------------- | | --mongo-uri | -m | Yes | - | MongoDB connection URI | | --output | -o | Yes* | - | Output SQLite database path (*not needed with --dry-run) | | --db-name | -d | No | quilltap | MongoDB database name to migrate | | --dry-run | - | No | false | Count records without migrating | | --verbose | -v | No | false | Enable detailed logging | | --help | -h | No | - | Show help message |

Migrated Collections

The tool migrates the following MongoDB collections to SQLite:

Core Identity & Session

• users - User accounts and authentication data
• accounts - OAuth/external provider accounts
• sessions - Active user sessions
• api_keys - API keys for integrations

Content & Characters

• characters - Character definitions and metadata
• chats - Chat/conversation records
• chat_messages - Individual messages (denormalized from array to rows)
• memories - Character memories and knowledge
• tags - User-defined tags

Configuration & Profiles

• connection_profiles - LLM provider configurations
• image_profiles - Image generation profiles
• embedding_profiles - Embedding model profiles
• roleplay_templates - Roleplay system prompts
• prompt_templates - Reusable prompt templates
• chat_settings - User chat preferences

Storage & Files

• files - File metadata
• folders - File organization
• mount_points - Storage backend configurations
• file_permissions - File access controls

Infrastructure

• background_jobs - Async job queue
• llm_logs - LLM request/response logs
• vector_indices - Embedding vector indices
• plugin_configs - Plugin configurations
• provider_models - Available LLM models
• projects - User projects

Synchronization

• sync_instances - Remote sync targets
• sync_mappings - Entity synchronization mappings
• sync_operations - Sync operation history
• user_sync_api_keys - Sync API credentials

Migrations

• migrations_state - Migration history
• migrations_metadata - Migration metadata

Data Transformations

The tool applies several automatic transformations:

1. MongoDB ObjectIds → Text IDs (uses existing id field)
2. Timestamps → ISO 8601 strings
3. Booleans → Integers (0/1)
4. Objects/Arrays → JSON strings
5. Missing timestamps → Auto-generated with sensible defaults
6. Chat message denormalization → Converts embedded message arrays to individual rows

Examples

Migrate Local Development Database

mongodb-to-sqlite \
  -m "mongodb://localhost:27017" \
  -d quilltap \
  -o ./quilltap-local.db \
  -v

Migrate from Remote MongoDB Atlas

mongodb-to-sqlite \
  -m "mongodb+srv://user:[email protected]/quilltap" \
  -o ~/data/quilltap.db

Preview Migration Impact

mongodb-to-sqlite \
  -m "mongodb://localhost:27017" \
  -d quilltap \
  --dry-run

Output:

Collection Record Counts:
==================================================
  users                                        12
  characters                                   45
  chats                                       230
  chat_messages                              5420
  memories                                    180
  ...
==================================================
  TOTAL                                      12547

Requirements

• Node.js 16+
• MongoDB 3.6+ (source database)
• Write access to output directory

Troubleshooting

Connection Issues

Problem: Cannot connect to MongoDB

Error: connect ECONNREFUSED 127.0.0.1:27017

Solution:

• Verify MongoDB is running
• Check the connection URI is correct
• Ensure network/firewall allows access

Permission Issues

Problem: Cannot write output file

Error: EACCES: permission denied, open '/path/to/quilltap.db'

Solution:

• Ensure output directory exists and is writable
• Run with appropriate permissions
• Check disk space availability

Migration Failures

Use --verbose flag to see detailed error information:

mongodb-to-sqlite -m "mongodb://localhost:27017" -o ./quilltap.db -v

Performance Notes

• Migrations are processed in batches (100 documents per batch)
• Large collections (>100k documents) may take several minutes
• Use --dry-run first to estimate time required
• Ensure sufficient disk space for SQLite database (typically 1.5x MongoDB size)

License

MIT

Support

For issues, questions, or contributions, visit the GitHub repository.