SurrealDB MCP Server

# Progress: SurrealDB MCP Server (Phase 3 - Testing & Finalization) ## What Works * **Phase 1 Complete:** Successfully connected to the target SurrealDB instance (`ws://localhost:8000`, NS: `n8n`, DB: `AIWorld`) using root credentials via the `surrealdb` Node.js SDK (`test-connection.js`). * **MCP Server Scaffolding:** Created TypeScript project structure in the current directory. * **Dependencies:** Installed core MCP/TypeScript dependencies and `surrealdb`. * **Basic Connection:** Integrated connection logic into `src/index.ts`. * **First Tool (`query`):** Implemented the basic `query` tool in `src/index.ts`. * **Build:** Successfully compiled TypeScript to JavaScript (`npm run build`). * **Configuration:** Re-added server entry to MCP host settings (`cline_mcp_settings.json`). * **Startup Test:** Verified server starts and connects to DB when run via MCP host. * **Tool Test (`query`):** Successfully executed the `query` tool (`INFO FOR DB;`) via MCP client. * **Credential Refactoring:** Successfully updated `src/index.ts` to use environment variables (SURREALDB_URL, _NS, _DB, _USER, _PASS) provided by the MCP host configuration. Tested successfully. * **Second Tool (`select`):** Implemented the `select` tool in `src/index.ts`. Required using `new RecordId(table, idPart)` for `db.select()` when selecting a specific record, rather than just the `table:id` string. Tested successfully for both table-wide and specific ID selection. * **Third Tool (`create`):** Implemented the `create` tool in `src/index.ts` using `db.create()`. Tested successfully by creating a record in `mcp_test_data`. * **Fourth Tool (`update`):** Implemented the `update` tool in `src/index.ts`. Similar to `select`, required using `new RecordId(table, idPart)` for `db.update()` instead of the `table:id` string shown in documentation examples. Tested successfully by updating the test record. * **Fifth Tool (`delete`):** Implemented the `delete` tool in `src/index.ts`. Assumed and confirmed need for `new RecordId(table, idPart)` for `db.delete()`. Added handling for empty results when record not found. Tested successfully by deleting the test record and confirming non-existence. * **Sixth Tool (`merge`):** Implemented the `merge` tool in `src/index.ts` using `db.merge()`. Confirmed need for `RecordId`. Tested successfully. * **Seventh Tool (`patch`):** Implemented the `patch` tool in `src/index.ts` using `db.patch()`. Confirmed need for `RecordId`. Tested successfully with multiple patch operations. * **Eighth Tool (`upsert`):** Implemented the `upsert` tool in `src/index.ts` using `db.upsert()`. Confirmed need for `RecordId`. Tested successfully for both create and update scenarios. * **Ninth Tool (`insert`):** Implemented the `insert` tool in `src/index.ts` using `db.insert()`. Tested successfully for inserting multiple records. * **Tenth Tool (`insertRelation`):** Implemented the `insertRelation` tool in `src/index.ts` using `db.insertRelation()`. Corrected initial implementation based on documentation. Tested successfully. * **Improved Error Logging:** Updated `catch` blocks to log full error objects for better debugging. * **Project Publishing Preparation:** * Updated `package.json` with metadata, `bin`, `files`, `engines` fields. * Created comprehensive `README.md` with installation and configuration instructions. * Added SurrealDB logo to README. * Created `LICENSE` file (MIT). * Created `CONTRIBUTING.md` with guidelines for contributors. * Created `llms-install.md` for automated installation. * Added dev dependencies and configuration files (`.eslintrc.cjs`, `.prettierrc.json`). * Implemented `dev`, `lint`, `format` scripts in `package.json`. * **Code Quality Improvements:** * Fixed code duplication in `src/index.ts`. * Decided on input validation approach (relying on SurrealDB's validation). * Updated ESLint configuration to use the new ESLint v9.0.0+ format (`eslint.config.js`). * Fixed linting issues (removed unused imports, added appropriate ESLint disable comments). * Ran formatter and linter to ensure code quality. * **Post-Change Testing:** Manually tested all core tools (`query`, `create`, `select`, `update`, `patch`, `delete`) after code changes to verify continued functionality. * **GitHub Publication:** * Published repository to GitHub at https://github.com/nsxdavid/surrealdb-mcp-server * Updated `package.json` with correct repository information (author, URLs) * **npm Publication:** * Created `.npmignore` file to control which files are published to npm * Verified build process works correctly * Published to npm registry as versions 0.1.0 through 0.1.8 * Improved README with clearer installation and usage instructions * Fixed JSON examples in README to remove comments * Added note clarifying that no manual installation is required when using npx * **Additional Platform Support:** * Added support for additional AI assistants (Roo Code, Windsurf, Cursor) * Added n8n integration documentation * **MCP Protocol Improvements:** * Added support for the 'prompts' capability in v0.1.8 * Implemented handler for 'ListPromptsRequestSchema' * Added missing MCP protocol handlers * **Logging Improvements:** * Redirected logs to stderr for better debugging * **GitHub Releases:** * Created GitHub releases for versions v0.1.2, v0.1.3, v0.1.5, v0.1.6, and v0.1.8 * Added detailed release notes for each version * Memory Bank core files are initialized and being updated. ## What's Left to Build (Phase 3 - Testing & Future Improvements) 1. **✅ Create GitHub Releases:** GitHub releases have been created for versions v0.1.2, v0.1.3, v0.1.5, v0.1.6, and v0.1.8. 2. **Implement Schema Inspection Prompts:** Enhance the server with prompts that provide database structure information: * **Phase 1 Prompts:** * `database_schema` - Provides complete database schema via `INFO FOR DB` * `table_schema` - Returns schema for a specific table via `INFO FOR TABLE <table>` * `list_tables` - Lists all tables in the database * **Implementation Approach:** * Enhance the `ListPromptsRequestSchema` handler to return the available prompts * Implement the `GetPromptRequestSchema` handler to generate prompt content * Execute appropriate SurrealQL queries and return raw results * Format results as context-rich messages for AI assistants 3. **(Optional) Comprehensive Tests:** Implement automated tests covering all tools and edge cases. 4. **(Optional) Test Script:** Add a `test` script to `package.json` once a testing framework is chosen. 5. **(Optional) Improve Dev Script:** Consider switching from `ts-node` to `tsx` for potentially better performance. 6. **(Optional) Implement More Tools:** Consider adding tools for other specific database operations if needed. 7. **(Optional) Update Documentation:** Ensure documentation reflects the latest changes, particularly the new 'prompts' capability added in v0.1.8 and the schema inspection prompts. 8. **(Optional) Expanded Prompts (Phase 2):** Consider implementing additional prompts for: * Query assistance (templates, explanations) * Database statistics * Graph relationship visualization * Data modeling suggestions ## Current Status * Phase 1 (Connection Verification) is complete. * Phase 2 (Robustness Improvements) is complete. * Phase 3 (Finalization & Publication) is largely complete: * Project structure and documentation are complete * GitHub repository is published with releases up to v0.1.8 * Package is published to npm with versions up to 0.1.8 * Support for multiple AI assistant platforms has been added * MCP protocol capabilities have been expanded to include 'prompts' * Server provides a comprehensive baseline of common SurrealDB operations and is well-documented for potential contributors. * Project is published, well-maintained, and ready for use. * Current focus is on implementing schema inspection prompts to leverage the 'prompts' capability added in v0.1.8. * Future work includes optional improvements like testing and additional tools/prompts. ## Known Issues * Initial confusion regarding `signin` parameter names (`user`/`pass` vs. `username`/`password`) in the SDK vs. documentation/errors. Resolved by using `username`/`password` within the `connect` options `auth` object. * Initial confusion regarding the timing/necessity of `db.use()`. Resolved by providing NS/DB directly in `connect` options. * `db.select()`, `db.update()`, and `db.delete()` require a `RecordId` instance (not a string) when targeting a specific record ID, despite some documentation examples showing strings. Passing the `table:id` string resulted in unexpected behavior (`[]` return or incorrect results). * Handlers for `select` and `delete` need to check for empty/null results when a record is not found to avoid client-side validation errors. ## Future Tool Implementation Analysis Based on the [JavaScript SDK Methods documentation](https://surrealdb.com/docs/sdk/javascript/methods): **Legend:** * `[x]` - Completed & Implemented as MCP Tool * `[ ]` - To Do (Recommended Next) * `[?]` - Might Do (Lower Priority / Optional) * `[-]` - Won't Do (Internal/Admin/Auth/Live/Params) **Checklist:** **Initialization methods** * `[-]` `db.connect(url, options)` * `[-]` `db.close()` * `[-]` `db.use(namespace,database)` * `[-]` `db.let(key,val)` * `[-]` `db.unset(key)` **Query methods** * `[x]` `db.query<T>(sql,vars)` * `[x]` `db.select<T>(thing)` * `[-]` `db.live<T>(table, callback,diff)` * `[-]` `db.subscribeLive<T>(queryUuid,callback)` * `[-]` `db.kill(queryUuid)` **Export and Import methods** * `[-]` `db.export()` * `[-]` `db.import()` **Mutation methods** * `[x]` `db.create<T,U>(thing,data)` * `[x]` `db.insert<T,U>(thing,data)` - *For bulk inserts.* * `[x]` `db.insertRelation<T,U>(thing,data)` - *For graph relations.* * `[x]` `db.update<T,U>(thing,data)` * `[x]` `db.upsert<T,U>(thing,data)` * `[x]` `db.merge<T,U>(thing,data)` * `[x]` `db.patch<T,U>(thing,data)` * `[x]` `db.delete<T,U>(thing)` **Authentication methods** * `[-]` `db.signup(vars)` * `[-]` `db.signin(vars)` * `[-]` `db.invalidate()` * `[-]` `db.authenticate(token)` * `[-]` `db.info<T>()`