MCP libSQL by xexr

1# MCP libSQL by xexr
2 
3A Model Context Protocol (MCP) server for libSQL database operations, providing secure database access through Claude Desktop, Claude Code, Cursor, and other MCP-compatible clients.
4 
5Runs on Node, written in TypeScript
6 
7## 🔧 **Quick Start**
8 
91. **Install:**
10   ```bash
11   pnpm install -g @xexr/mcp-libsql
12   ```
13 
142. **Test locally:**
15   ```bash
16   mcp-libsql --url file:///tmp/test.db --log-mode console
17   ```
18 
193. **Configure Claude Desktop** with your Node.js path and database URL (see configuration examples below)
20 
21## 🚀 **Status**
22 
23✅ **Complete database management capabilities** - All 6 core tools implemented and tested  
24✅ **Comprehensive security validation** - 67 security tests covering all injection vectors  
25✅ **Extensive test coverage** - 244 total tests (177 unit + 67 security) with 100% pass rate  
26✅ **Production deployment verified** - Successfully working with MCP clients  
27✅ **Robust error handling** - Connection retry, graceful degradation, and audit logging  
28 
29## 🛠️ **Features**
30 
31### **Available Tools**
32- **read-query**: Execute SELECT queries with comprehensive security validation
33- **write-query**: INSERT/UPDATE/DELETE operations with transaction support
34- **create-table**: DDL operations for table creation with security measures
35- **alter-table**: Table structure modifications (ADD/RENAME/DROP operations)
36- **list-tables**: Database metadata browsing with filtering options
37- **describe-table**: Table schema inspection with multiple output formats
38 
39### **Security & Reliability**
40- **Multi-layer SQL injection prevention** with comprehensive security validation
41- **Connection pooling** with health monitoring and automatic retry logic  
42- **Transaction support** with automatic rollback on errors
43- **Comprehensive audit logging** for security compliance
44 
45> 🔐 **Security details:** See [docs/SECURITY.md](docs/SECURITY.md) for comprehensive security features and testing.
46 
47### **Developer Experience**
48- **Beautiful table formatting** with proper alignment and NULL handling
49- **Performance metrics** displayed for all operations
50- **Clear error messages** with actionable context
51- **Parameterized query support** for safe data handling
52- **Development mode** with enhanced logging and hot reload
53 
54## 📋 **Prerequisites**
55 
56- **Node.js** 20+ 
57- **pnpm** (or npm) package manager
58- **libSQL database** (file-based or remote)
59- **Claude Desktop** (for MCP integration)
60 
61### **Platform Requirements**
62- **macOS**: Native Node.js installation
63- **Linux**: Native Node.js installation  
64- **Windows**: Native Node.js installation or WSL2 with Node.js installation
65 
66## 🔧 **Installation**
67 
68```bash
69# Use your package manager of choice, e.g. npm, pnpm, bun etc
70 
71# Install globally
72pnpm install -g @xexr/mcp-libsql
73mcp-libsql -v # check version
74 
75# ...or build from the repository
76git clone https://github.com/Xexr/mcp-libsql.git
77cd mcp-libsql
78pnpm install # Install dependencies
79pnpm build # Build the project
80node dist/index.js -v  # check version
81```
82 
83## 🚀 **Usage**
84 
85### **Local Testing**
86 
87Global installation assumed below, replace "mcp-libsql" with "node dist/index.js" if using local build
88 
89```bash
90# Test with file database (default: file-only logging)
91mcp-libsql --url file:///tmp/test.db
92 
93# Test with HTTP database
94mcp-libsql --url http://127.0.0.1:8080
95 
96# Test with Turso database (environment variable, alternatively export the env var)
97LIBSQL_AUTH_TOKEN="your-token" mcp-libsql --url "libsql://your-db.turso.io"
98 
99# Test with Turso database (CLI parameter)
100mcp-libsql --url "libsql://your-db.turso.io" --auth-token "your-token"
101 
102# Development mode with console logging
103mcp-libsql --dev --log-mode console --url file:///tmp/test.db
104 
105# Test with different logging modes
106mcp-libsql --url --log-mode both file:///tmp/test.db
107```
108 
109### **Claude Desktop Integration**
110 
111Configure the MCP server in Claude Desktop based on your operating system:
112 
113#### **macOS Configuration**
114 
1151. **Create configuration file** at `~/Library/Application Support/Claude/claude_desktop_config.json`:
116 
117**Global install**
118```json
119 
120{
121  "mcpServers": {
122    "mcp-libsql": {
123      "command": "mcp-libsql",
124      "args": [
125        "--url",
126        "file:///Users/username/database.db"
127      ]
128    }
129  }
130}
131```
132 
133**Alternative configuration for local build installation:**
134```json
135{
136  "mcpServers": {
137    "mcp-libsql": {
138      "command": "node",
139      "args": [
140        "/Users/username/projects/mcp-libsql/dist/index.js",
141        "--url", 
142        "file:///Users/username/database.db"
143      ],
144    }
145  }
146}
147```
148 
149**Alternative configuration for global install using nvm lts for node**
150```json
151{
152  "mcpServers": {
153    "mcp-libsql": {
154      "command": "zsh",
155      "args": [
156        "-c",
157        "source ~/.nvm/nvm.sh && nvm use --lts > /dev/null && mcp-libsql --url file:///Users/username/database.db",
158      ],
159    }
160  }
161}
162```
163 
164**Important**: The global installation method is recommended as it handles PATH automatically.
165 
166#### **Linux Configuration**
167 
1681. **Create configuration file** at `~/.config/Claude/claude_desktop_config.json`:
169 
170**Global install**
171```json
172{
173  "mcpServers": {
174    "mcp-libsql": {
175      "command": "mcp-libsql",
176      "args": [
177        "--url",
178        "file:///home/username/database.db"
179      ]
180    }
181  }
182}
183```
184 
185**Alternative configuration for local build installation:**
186```json
187{
188  "mcpServers": {
189    "mcp-libsql": {
190      "command": "node",
191      "args": [
192        "/home/username/projects/mcp-libsql/dist/index.js",
193        "--url",
194        "file:///home/username/database.db"
195      ],
196    }
197  }
198}
199```
200 
201#### **Windows (WSL2) Configuration**
202 
2031. **Create configuration file** at `%APPDATA%\Claude\claude_desktop_config.json`:
204 
205**Global install**
206```json
207{
208  "mcpServers": {
209    "mcp-libsql": {
210      "command": "wsl.exe",
211      "args": [
212        "-e",
213        "bash",
214        "-c",
215        "mcp-libsql --url file:///home/username/database.db",
216      ]
217    }
218  }
219}
220```
221 
222**Alternative configuration for local build installation:**
223```json
224{
225  "mcpServers": {
226    "mcp-libsql": {
227      "command": "wsl.exe",
228      "args": [
229        "-e",
230        "bash",
231        "-c",
232        "/home/username/projects/mcp-libsql/dist/index.js --url file:///home/username/database.db",
233      ]
234    }
235  }
236}
237```
238 
239**Alternative configuration for global install using nvm for node**
240```json
241{
242  "mcpServers": {
243    "mcp-libsql": {
244      "command": "wsl.exe",
245      "args": [
246        "-e",
247        "bash",
248        "-c",
249        "source ~/.nvm/nvm.sh && mcp-libsql --url file:///home/username/database.db",
250      ]
251    }
252  }
253}
254```
255 
256**Important**: Use `wsl.exe -e` (not just `wsl.exe`) to ensure proper command handling and avoid issues with server command reception on Windows.
257 
258### **Database Authentication**
259 
260For Turso (and other credentialed) databases, you'll need an authentication token. There are two secure ways to provide it:
261 
262_Global installation shown below, adjust accordingly for your setup_
263 
264#### **Method 1: Environment Variable (Recommended)**
265 
266**Configure Claude Desktop with environment variable** (macOS/Linux example):
267```bash
268export LIBSQL_AUTH_TOKEN="your-turso-auth-token-here"
269```
270 
271```json
272{
273  "mcpServers": {
274    "mcp-libsql": {
275      "command": "mcp-libsql",
276      "args": [
277        "--url",
278        "libsql://your-database.turso.io"
279      ]
280    }
281  }
282}
283```
284 
285#### **Method 2: CLI Parameter**
286 
287```json
288{
289  "mcpServers": {
290    "mcp-libsql": {
291      "command": "mcp-libsql",
292      "args": [
293        "--url",
294        "libsql://your-database.turso.io",
295        "--auth-token",
296        "your-turso-auth-token-here"
297      ]
298    }
299  }
300}
301```
302 
303#### **Getting Your Turso Auth Token**
304 
3051. **Install Turso CLI:**
306   ```bash
307   curl -sSfL https://get.tur.so/install.sh | bash
308   ```
309 
3102. **Login to Turso:**
311   ```bash
312   turso auth login
313   ```
314 
3153. **Create an auth token:**
316   ```bash
317   turso auth token create --name "mcp-libsql"
318   ```
319 
3204. **Get your database URL:**
321   ```bash
322   turso db show your-database-name --url
323   ```
324 
325#### **Security Best Practices**
326 
327- **Environment variables are safer** than CLI parameters (tokens won't appear in process lists)
328- **MCP config files may contain tokens** - ensure they're not committed to version control
329- **Consider using external secret management** for production environments
330- **Use scoped tokens** with minimal required permissions
331- **Rotate tokens regularly** for enhanced security
332- **Monitor token usage** through Turso dashboard
333 
334#### **Example: Complete Turso Setup**
335 
3361. **Create and configure database:**
337   ```bash
338   # Create database
339   turso db create my-app-db
340   
341   # Get database URL
342   turso db show my-app-db --url
343   # Output: libsql://my-app-db-username.turso.io
344   
345   # Create auth token
346   turso auth token create --name "mcp-libsql-token"
347   # Output: your-long-auth-token-string
348   ```
349 
3502. **Configure Claude Desktop:**
351    ```bash
352    export LIBSQL_AUTH_TOKEN="your-turso-auth-token-here"
353    ```
354 
355    ```json
356    {
357      "mcpServers": {
358        "mcp-libsql": {
359          "command": "mcp-libsql",
360          "args": [
361            "--url",
362            "libsql://my-app-db-username.turso.io"
363          ]
364        }
365      }
366    }
367    ```
368 
3693. **Test the connection:**
370   ```bash
371   # Test locally first
372   mcp-libsql --url "libsql://my-app-db-username.turso.io" --log-mode console
373   ```
374 
375#### **Configuration Notes**
376 
377- **File paths**: Use absolute paths to avoid path resolution issues
378- **Database URLs**: 
379  - File databases: `file:///absolute/path/to/database.db`
380  - HTTP databases: `http://hostname:port`
381  - libSQL/Turso: `libsql://your-database.turso.io`
382- **Node.js path**: Use `which node` to find your Node.js installation path
383- **Working directory**: Set `cwd` to ensure relative paths work correctly
384- **Authentication**: For Turso databases, use environment variables for secure token handling
385- **Logging modes**: 
386  - Default `file` mode prevents JSON parsing errors in MCP protocol
387  - Use `--log-mode console` for development debugging
388  - Use `--log-mode both` for comprehensive logging
389  - Use `--log-mode none` to disable all logging
390 
3912. **Restart Claude Desktop** completely after updating the configuration
392 
3933. **Test the integration** by asking Claude to run SQL queries:
394   ```
395   Can you run this SQL query: SELECT 1 as test
396   ```
397 
398 
399 
400## 📋 **Available Tools**
401 
402- **read-query** - Execute SELECT queries with security validation
403- **write-query** - INSERT/UPDATE/DELETE with transaction support  
404- **create-table** - CREATE TABLE with DDL security
405- **alter-table** - Modify table structure (ADD/RENAME/DROP)
406- **list-tables** - Browse database metadata and objects
407- **describe-table** - Inspect table schema and structure
408 
409> 📖 **Detailed API documentation:** See [docs/API.md](docs/API.md) for complete input/output examples and parameters.
410 
411## 🧪 **Testing**
412 
413```bash
414# Run all tests
415pnpm test
416 
417# Run tests in watch mode
418pnpm test:watch
419 
420# Run tests with coverage
421pnpm test:coverage
422 
423# Run specific test file
424pnpm test security-verification
425 
426# Lint code
427pnpm lint
428 
429# Fix linting issues
430pnpm lint:fix
431 
432# Type check
433pnpm typecheck
434```
435 
436**Test Coverage**: 403 tests covering all functionality including edge cases, error scenarios, CLI arguments, authentication, and comprehensive security validation.
437 
438## ⚠️ **Common Issues**
439 
440### **1. Build Failures**
441```bash
442# Clean and rebuild
443rm -rf dist node_modules
444pnpm install && pnpm build
445```
446 
447### **2. Node.js Version Issues (macOS)**
448```
449SyntaxError: Unexpected token '??='
450```
451**Problem**: Claude Desktop may default to using an older Node.js version on your system which doesn't support the required feature set.
452 
453**Solution**: Use global installation and nvm node selection method shown above.
454 
455### **3. Server Won't Start**
456- For global installation: `pnpm install -g @xexr/mcp-libsql`
457- For local installation: Ensure `pnpm build` was run and `dist/index.js` exists
458- Test locally: `mcp-libsql --url file:///tmp/test.db`
459- Restart Claude Desktop after config changes
460 
461### **4. Tools Not Available**
462- Verify database URL is accessible
463- Check Claude Desktop logs for connection errors
464- Test with simple file database: `file:///tmp/test.db`
465 
466### **5. JSON Parsing Errors (Resolved)**
467```
468Expected ',' or ']' after array element in JSON
469```
470**Resolved**: This issue is caused by stdout console logging. The `--log-mode` option now defaults to `file` mode which prevents this issue. If you see these errors, ensure you're using the default `--log-mode file` or not specifying `--log-mode` at all. Note, the error is harmless, and the tool will still work with it if you wish to have console logging.
471 
472### **6. Database Connection Issues**
473```bash
474# Test database connectivity
475sqlite3 /tmp/test.db "SELECT 1"
476 
477# Fix permissions
478chmod 644 /path/to/database.db
479```
480 
481> 🔧 **Full troubleshooting guide:** See [docs/TROUBLESHOOTING.md](docs/TROUBLESHOOTING.md) for detailed solutions to all issues.
482 
483## 🏗️ **Architecture**
484 
485Built with TypeScript and modern Node.js patterns:
486- **Connection pooling** with health monitoring and retry logic
487- **Tool-based architecture** with consistent validation and error handling
488- **Security-first design** with multi-layer input validation
489- **Comprehensive testing** with 244 tests covering all scenarios
490 
491## 🤝 **Contributing**
492 
4931. Follow TypeScript strict mode and existing code patterns
4942. Write tests for new features  
4953. Maintain security measures
4964. Update documentation
497 
498**Development:** `pnpm dev` • **Build:** `pnpm build` • **Test:** `pnpm test`
499 
500## 📄 **License**
501 
502MIT License - see [LICENSE](LICENSE) file for details.
503 
504## 🔗 **Links**
505 
506- [Model Context Protocol](https://modelcontextprotocol.io/)
507- [MCP TypeScript SDK](https://github.com/modelcontextprotocol/typescript-sdk)
508- [libSQL Documentation](https://docs.libsql.org/)
509- [Claude Desktop](https://claude.ai/desktop)