# 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