README.md•14.2 kB
# tbls MCP Server
> ⚠️ **EXPERIMENTAL SOFTWARE**
> This application is experimental software with insufficient testing coverage (~66% currently). It is not suitable for production use. We recommend experimental use only for development and testing purposes. If you plan to use it in production, please conduct thorough testing beforehand.
A Model Context Protocol (MCP) server that provides access to database schema information generated by [tbls](https://github.com/k1LoW/tbls) and enables SQL query execution on MySQL and SQLite databases.
## Features
- **JSON Schema Support**: Primary support for tbls-generated JSON schema files with optimal performance
- **JSON-Only Support**: Uses JSON schema format from tbls for optimal performance
- **Multiple Resource Types**: Access schemas, tables, and index information through MCP resources
- **SQL Query Execution**: Execute SELECT queries on MySQL and SQLite databases with comprehensive security
- **Type Safety**: Full TypeScript implementation with zod validation
- **Error Handling**: Robust error handling using neverthrow Result types
- **MCP Compatible**: Works with Claude Desktop and other MCP clients
- **Flexible Configuration**: Support for both CLI arguments and configuration files
## MCP Client Configuration
To use this server with MCP clients, add the following configuration to your MCP client's configuration file.
### Example: Claude Desktop
Add to your Claude Desktop configuration file (`claude_desktop_config.json`):
```json
{
"mcpServers": {
"tbls": {
"command": "npx",
"args": [
"github:yhosok/tbls-mcp-server",
"--schema-source", "/path/to/your/tbls/schema.json",
"--database-url", "mysql://user:password@localhost:3306/database"
]
}
}
}
```
**Note**: You can create a `.tbls-mcp-server.json` configuration file to specify server options (see Configuration section below) and use just the command without arguments in your MCP client configuration.
## Installation
### Prerequisites
- Node.js 18 or higher
- [tbls](https://github.com/k1LoW/tbls) installed and configured
- Database access (MySQL or SQLite) - optional for SQL query features
### Via npx (Recommended for MCP)
```bash
npx github:yhosok/tbls-mcp-server --schema-source /path/to/tbls/schema.json
```
### Clone and Run Locally
```bash
git clone https://github.com/yhosok/tbls-mcp-server.git
cd tbls-mcp-server
npm install
npm run build
# Run the server
node dist/index.js --schema-source /path/to/tbls/schema.json
```
## Usage
### Basic Usage (Schema Information Only)
```bash
npx github:yhosok/tbls-mcp-server --schema-source /path/to/tbls/schema.json
```
### With Database Connection (Full Features)
```bash
npx github:yhosok/tbls-mcp-server \
--schema-source /path/to/tbls/schema.json \
--database-url mysql://user:pass@localhost:3306/mydb
```
### Using Configuration File
```bash
npx github:yhosok/tbls-mcp-server --config .tbls-mcp-server.json
```
## JSON Schema Sample
Example of a tbls-generated JSON schema file structure:
```json
{
"name": "myapp",
"type": "mysql",
"tables": [
{
"name": "users",
"type": "table",
"comment": "User accounts",
"columns": [
{
"name": "id",
"type": "int(11)",
"nullable": false,
"primary": true,
"comment": "Primary key"
},
{
"name": "email",
"type": "varchar(255)",
"nullable": false,
"unique": true,
"comment": "User email address"
}
],
"indexes": [
{
"name": "PRIMARY",
"columns": ["id"],
"unique": true
},
{
"name": "idx_email",
"columns": ["email"],
"unique": true
}
]
}
]
}
```
## Examples
### Setting up with tbls
First, install tbls by following the instructions at [https://github.com/k1LoW/tbls](https://github.com/k1LoW/tbls).
Then generate schema documentation:
```bash
# Generate schema documentation (default output: ./dbdoc)
tbls doc mysql://user:pass@localhost:3306/mydb
# Or generate JSON schema directly
tbls out -t json mysql://user:pass@localhost:3306/mydb -o ./custom/schema/path/schema.json
# Start MCP server
npx github:yhosok/tbls-mcp-server --schema-source ./dbdoc/schema.json
```
### Directory Structure
Expected tbls output structure:
```
./dbdoc/
├── schema.json # Complete schema information (required)
└── README.md # Human-readable overview (optional)
```
**Note**:
- Use `tbls out -t json` to generate the JSON schema file
- The default output file is `schema.json` in the specified directory
- The `--schema-source` option can point to either a JSON file or directory containing JSON files
### Using with Claude Desktop
1. **Configure Claude Desktop**:
```json
{
"mcpServers": {
"tbls": {
"command": "npx",
"args": [
"github:yhosok/tbls-mcp-server",
"--schema-source", "/Users/username/projects/myapp/dbdoc/schema.json",
"--database-url", "mysql://user:password@localhost:3306/myapp"
]
}
}
}
```
2. **Restart Claude Desktop** and the tbls server will be available
3. **Query your database schema**:
- "Show me all tables in the database"
- "What columns does the users table have?"
- "Show me the relationship between users and posts"
- "Execute: SELECT COUNT(*) FROM users WHERE active = true"
### Common SQL Queries
**Schema exploration**:
```sql
-- MySQL
SHOW TABLES;
SHOW COLUMNS FROM users;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'users';
-- SQLite
SELECT name FROM sqlite_master WHERE type='table';
PRAGMA table_info(users);
```
**Data analysis**:
```sql
-- User statistics
SELECT
COUNT(*) as total_users,
COUNT(CASE WHEN active = 1 THEN 1 END) as active_users,
COUNT(CASE WHEN created_at > DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as recent_users
FROM users;
-- Popular posts
SELECT p.title, p.created_at, COUNT(c.id) as comment_count
FROM posts p
LEFT JOIN comments c ON p.id = c.post_id
GROUP BY p.id
ORDER BY comment_count DESC
LIMIT 10;
```
## MCP Resources
The server exposes tbls-generated schema information through the following MCP resources:
<!-- AUTO-GENERATED:START - Do not modify this section manually -->
| URI Pattern | Description | Discovery Required |
|-------------|-------------|-------------------|
| `db://schemas` | Complete list of all available database schemas with metadata including schema names, table counts, and version information. | No |
| `db://schemas/{schemaName}` | Information about the {schemaName} schema. This URI redirects to db://schemas/{schemaName}/tables. | Yes |
| `db://schemas/{schemaName}/tables` | Comprehensive list of all tables within the {schemaName} schema, including table metadata, row counts, and basic structure information. | Yes |
| `db://schemas/{schemaName}/tables/{tableName}` | Complete detailed information about the {tableName} table including column definitions with data types, constraints, indexes, foreign key relationships, and table statistics. | Yes |
| `db://schemas/{schemaName}/tables/{tableName}/indexes` | Detailed index information for the {tableName} table including index names, types (primary, unique, regular), column compositions, and performance statistics. | Yes |
<!-- AUTO-GENERATED:END -->
## MCP Tools
The server provides SQL query execution capabilities when a database connection is configured:
### SQL Query Tool (`execute-sql`)
**Purpose**: Execute SELECT queries on connected MySQL or SQLite databases with comprehensive security features.
**Security Features**:
- ✅ **SELECT queries only** - INSERT, UPDATE, DELETE, DROP, etc. are blocked
- ✅ **Parameterized queries** prevent SQL injection attacks
- ✅ **Query timeout protection** prevents long-running queries
- ✅ **Multiple statement prevention** blocks compound SQL injection
- ✅ **Input sanitization** removes dangerous patterns
**Supported Databases**:
- MySQL (via connection string or individual parameters)
- SQLite (file path or :memory: database)
**Parameters**:
- `query` (required): SQL SELECT query to execute
- `parameters` (optional): Array of parameters for prepared statements
- `timeout` (optional): Query timeout in milliseconds (1000-300000, default: 30000)
**Usage Examples**:
```json
{
"query": "SELECT * FROM users WHERE active = ?",
"parameters": [true]
}
```
```json
{
"query": "SELECT u.name, COUNT(p.id) as post_count FROM users u LEFT JOIN posts p ON u.id = p.user_id GROUP BY u.id",
"parameters": []
}
```
```json
{
"query": "SHOW TABLES",
"parameters": [],
"timeout": 10000
}
```
**Response Format**:
```json
{
"rows": [
{"id": 1, "name": "John Doe", "email": "john@example.com"},
{"id": 2, "name": "Jane Smith", "email": "jane@example.com"}
],
"rowCount": 2,
"columns": [
{"name": "id", "type": "int"},
{"name": "name", "type": "varchar"},
{"name": "email", "type": "varchar"}
]
}
```
## Configuration
### Command Line Arguments
- `--schema-source <path>`: Path to tbls JSON schema file or directory (required)
- `--database-url <url>`: Database connection string (optional)
- `--log-level <level>`: Set logging level (debug, info, warn, error, default: info)
- `--config <path>`: Path to configuration file
- `--help`: Show help information
- `--version`: Show version information
### Environment Variables
- `TBLS_SCHEMA_SOURCE`: Path to tbls JSON schema file or directory
- `DATABASE_URL`: Database connection string (optional)
- `LOG_LEVEL`: Logging level (debug, info, warn, error)
### Configuration File
Create a `.tbls-mcp-server.json` file in your project root:
```json
{
"schemaSource": "/path/to/tbls/schema.json",
"logLevel": "info",
"database": {
"type": "mysql",
"connectionString": "mysql://username:password@localhost:3306/database_name"
}
}
```
#### Database Configuration Options
**MySQL:**
```json
{
"database": {
"type": "mysql",
"connectionString": "mysql://user:password@host:port/database"
}
}
```
**SQLite:**
```json
{
"database": {
"type": "sqlite",
"connectionString": "sqlite:///path/to/database.db"
}
}
```
#### Complete Configuration Examples
**Production Setup with JSON Schema:**
```json
{
"schemaSource": "/opt/app/schema/production.json",
"logLevel": "warn",
"database": {
"type": "mysql",
"connectionString": "mysql://readonly_user:password@db.company.com:3306/production_db"
}
}
```
**Development Setup with Local Files:**
```json
{
"schemaSource": "./dbdoc/schema.json",
"logLevel": "debug",
"database": {
"type": "sqlite",
"connectionString": "sqlite:///./dev.db"
}
}
```
## Troubleshooting
### Common Issues
**Server fails to start**:
- Verify Node.js version (18+ required)
- Check that the schema source file exists or directory contains tbls-generated files
- For JSON: Ensure the JSON file is valid and contains proper schema structure
- For directories: Ensure the directory contains proper .json files
- Ensure database connection string is valid (if using database features)
**No resources available**:
- Confirm tbls has generated JSON schema file in the specified location
- Check file permissions on the schema file/directory
- Enable debug logging: `--log-level debug`
**Database connection issues**:
- Test database connectivity outside of the MCP server
- Verify connection string format
- Check firewall and network access
- Ensure database user has appropriate permissions (SELECT at minimum)
**SQL queries fail**:
- Only SELECT statements are allowed
- Use parameterized queries with `?` placeholders
- Check query timeout settings
- Review query syntax for your database type
**Claude Desktop integration issues**:
- Restart Claude Desktop after configuration changes
- Check configuration file syntax (valid JSON)
- Verify file paths are absolute and accessible
- Check Claude Desktop logs for error messages
### Debug Mode
Enable debug logging to troubleshoot issues:
```bash
tbls-mcp-server --schema-source /path/to/schema.json --log-level debug
```
This will output detailed information about:
- Configuration loading and schema source resolution
- Resource discovery (JSON file vs directory detection)
- Database connections
- SQL query execution
- Error details and diagnostics
### Support
For issues and questions:
- Check the [GitHub Issues](https://github.com/yhosok/tbls-mcp-server/issues)
- Review [tbls documentation](https://github.com/k1LoW/tbls)
- Consult [MCP specification](https://modelcontextprotocol.io/)
## Development
### Prerequisites
- Node.js 18+
- npm or yarn
### Setup
```bash
git clone https://github.com/yhosok/tbls-mcp-server.git
cd tbls-mcp-server
npm install
```
### Development Commands
```bash
# Start development server
npm run dev
# Run tests
npm test
# Run tests with coverage
npm run test:coverage
# Build for production
npm run build
# Run linter
npm run lint
```
### Testing
The project uses Jest for testing with a focus on Test-Driven Development (TDD):
```bash
# Run all tests
npm test
# Run tests in watch mode
npm run test:watch
# Generate coverage report
npm run test:coverage
```
## Architecture
The server is built using:
- **TypeScript**: Type-safe implementation
- **@modelcontextprotocol/sdk**: Official MCP SDK
- **neverthrow**: Result types for error handling
- **zod**: Schema validation
- **mysql2**: MySQL database connectivity
- **sqlite3**: SQLite database connectivity
## Security
- Only SELECT statements are permitted for SQL execution
- Input validation using zod schemas
- SQL injection prevention through parameterized queries
- Connection string validation and sanitization
## Contributing
1. Fork the repository
2. Create a feature branch
3. Write tests for your changes
4. Implement your changes
5. Ensure all tests pass
6. Submit a pull request
## License
ISC License
## Related Projects
- [tbls](https://github.com/k1LoW/tbls) - Schema documentation tool
- [Model Context Protocol](https://modelcontextprotocol.io/) - Protocol specification