zpaeng/db-gen-mcp

📈 Star History

📋 Table of Contents

• Features
• System Requirements
• Quick Start
• Installation and Configuration
• Available Tools
• Generated Java Code Structure
• Supported Database Types
• Examples
• Error Handling
• Development and Extension
• FAQ
• Contributing
• License
• Contact

✨ Features

• 🗄️ Multi-Database Support: MySQL, PostgreSQL, SQLite, SQL Server, Oracle
• 🔧 CRUD Operations: Complete Create, Read, Update, Delete functionality
• 📄 Pagination: Support for pagination and conditional queries
• 🏗️ Code Generation: Automatically generate Java Entity, Mapper, Service, Controller
• 🔌 MyBatis-Plus: Integrated MyBatis-Plus framework
• 🧪 Connection Testing: Database connection testing tools
• 📊 Metadata Analysis: Retrieve table structure and field information
• 🚀 MCP Protocol: Based on Model Context Protocol standard
• 🎯 Type Safety: Developed with TypeScript, providing complete type support
• 📝 Template Customization: Support for custom Handlebars code templates

📋 System Requirements

• Node.js: >= 18.0.0
• npm: >= 8.0.0
• TypeScript: >= 5.0.0
• Supported OS: Windows, macOS, Linux

Database Driver Requirements

Depending on the database type you use, additional system dependencies may be required:

• Oracle: Requires Oracle Instant Client
• SQL Server: May require additional configuration on Linux/macOS

🚀 Quick Start

1. Clone the Project

git clone https://github.com/zpaeng/db-gen-mcp.git
cd db-gen-mcp

2. Install Dependencies

npm install

3. Build the Project

npm run build

4. Start the Server

npm start

5. Configure MCP Client

Add the following configuration to your MCP client configuration file:

Roo Configuration (.roo/mcp.json):

{
  "mcpServers": {
    "database-generator": {
      "command": "node",
      "args": ["build/index.js"],
      "cwd": "."
    }
  }
}

Claude Desktop Configuration:

{
  "mcpServers": {
    "database-generator": {
      "command": "node",
      "args": ["path/to/db-gen-mcp/build/index.js"],
      "cwd": "path/to/db-gen-mcp"
    }
  }
}

📦 Installation and Configuration

Development Mode

# Watch for file changes and auto-recompile
npm run watch

# Clean build directory
npm run clean

# Rebuild
npm run rebuild

# Run in development mode
npm run dev

Production Deployment

# Build production version
npm run build

# Start server
npm start

🛠️ Available Tools

1. Test Database Connection

Test if the database connection is working properly.

{
  "tool": "test_connection",
  "config": {
    "type": "mysql",
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "password",
    "database": "test_db"
  }
}

2. Get Database Table List

Get all tables in the specified database.

{
  "tool": "list_tables",
  "config": {
    "type": "mysql",
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "password",
    "database": "test_db"
  }
}

3. Get Table Schema

Get detailed structure information of the specified table.

{
  "tool": "get_table_schema",
  "config": {
    "type": "mysql",
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "password",
    "database": "test_db"
  },
  "tableName": "users"
}

4. Execute Custom SQL Query

Execute custom SQL query statements.

{
  "tool": "execute_query",
  "config": {
    "type": "mysql",
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "password",
    "database": "test_db"
  },
  "query": "SELECT * FROM users WHERE age > ?",
  "params": [18]
}

5. CRUD Operations

Create Record

{
  "tool": "crud_create",
  "config": {
    "type": "mysql",
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "password",
    "database": "test_db"
  },
  "tableName": "users",
  "data": {
    "name": "John Doe",
    "email": "john@example.com",
    "age": 25
  }
}

Read Records

{
  "tool": "crud_read",
  "config": {
    "type": "mysql",
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "password",
    "database": "test_db"
  },
  "tableName": "users",
  "criteria": {
    "age": 25
  },
  "limit": 10,
  "offset": 0
}

Update Records

{
  "tool": "crud_update",
  "config": {
    "type": "mysql",
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "password",
    "database": "test_db"
  },
  "tableName": "users",
  "data": {
    "email": "newemail@example.com"
  },
  "criteria": {
    "id": 1
  }
}

Delete Records

{
  "tool": "crud_delete",
  "config": {
    "type": "mysql",
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "password",
    "database": "test_db"
  },
  "tableName": "users",
  "criteria": {
    "id": 1
  }
}

6. Pagination Query

Support for pagination and conditional queries.

{
  "tool": "paginate",
  "config": {
    "type": "mysql",
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "password",
    "database": "test_db"
  },
  "tableName": "users",
  "page": 1,
  "pageSize": 10,
  "criteria": {
    "status": "active"
  }
}

7. Generate Java Code

Automatically generate complete Java Spring Boot + MyBatis-Plus code.

{
  "tool": "generate_java_code",
  "dbConfig": {
    "type": "mysql",
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "password",
    "database": "test_db"
  },
  "codeGenConfig": {
    "packageName": "com.example.demo",
    "outputPath": "./generated",
    "author": "Developer",
    "enableSwagger": true
  },
  "tableName": "users"
}

📁 Generated Java Code Structure

The generated code will include the following files:

generated/
├── entity/
│   └── User.java              # Entity class
├── mapper/
│   └── UserMapper.java        # MyBatis-Plus Mapper interface
├── service/
│   ├── IUserService.java      # Service interface
│   └── impl/
│       └── UserServiceImpl.java # Service implementation
└── controller/
    └── UserController.java    # REST Controller

Example Generated Code

package com.example.demo.entity;

import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.annotation.TableField;
import java.io.Serializable;
import java.time.LocalDateTime;

/**
 * <p>
 * User table
 * </p>
 *
 * @author Developer
 * @since 2024-01-01
 */
@TableName("users")
public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId("id")
    private Long id;

    @TableField("name")
    private String name;

    @TableField("email")
    private String email;

    @TableField("age")
    private Integer age;

    @TableField("created_at")
    private LocalDateTime createdAt;

    // getter and setter methods...
}

package com.example.demo.mapper;

import com.example.demo.entity.User;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;

/**
 * <p>
 * User table Mapper interface
 * </p>
 *
 * @author Developer
 * @since 2024-01-01
 */
public interface UserMapper extends BaseMapper<User> {

}

package com.example.demo.service;

import com.example.demo.entity.User;
import com.baomidou.mybatisplus.extension.service.IService;

/**
 * <p>
 * User table service class
 * </p>
 *
 * @author Developer
 * @since 2024-01-01
 */
public interface IUserService extends IService<User> {

}

package com.example.demo.controller;

import org.springframework.web.bind.annotation.*;
import org.springframework.beans.factory.annotation.Autowired;
import com.example.demo.entity.User;
import com.example.demo.service.IUserService;

/**
 * <p>
 * User table front controller
 * </p>
 *
 * @author Developer
 * @since 2024-01-01
 */
@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private IUserService userService;

    @GetMapping("/{id}")
    public User getById(@PathVariable Long id) {
        return userService.getById(id);
    }

    @PostMapping
    public boolean save(@RequestBody User user) {
        return userService.save(user);
    }

    @PutMapping
    public boolean updateById(@RequestBody User user) {
        return userService.updateById(user);
    }

    @DeleteMapping("/{id}")
    public boolean removeById(@PathVariable Long id) {
        return userService.removeById(id);
    }
}

🗄️ Supported Database Types

📚 Examples

See for detailed usage examples and tutorials.

⚠️ Error Handling

The server returns detailed error information, including:

• Connection Failed: Database connection parameter errors, network issues
• SQL Syntax Error: Query statement format errors
• Permission Issues: Insufficient user permissions
• Data Type Error: Parameter type mismatch
• Constraint Violation: Primary key conflicts, foreign key constraints, etc.

Error Response Format

{
  "error": {
    "code": "CONNECTION_FAILED",
    "message": "Unable to connect to database",
    "details": "ECONNREFUSED 127.0.0.1:3306"
  }
}

🔧 Development and Extension

Adding New Database Adapters

1. Create a new adapter file in the src/database/adapters/ directory
2. Extend the BaseDatabaseAdapter class
3. Implement all abstract methods
4. Register the new adapter in DatabaseManager

// src/database/adapters/newdb.ts
import { BaseDatabaseAdapter } from './base';

export class NewDBAdapter extends BaseDatabaseAdapter {
  // Implement abstract methods
}

Custom Code Templates

1. Modify Handlebars templates in the src/generator/templates/ directory
2. Add new template variables
3. Update corresponding generator classes

{{!-- src/generator/templates/entity.hbs --}}
package {{packageName}}.entity;

/**
 * {{comment}}
 * @author {{author}}
 */
public class {{className}} {
    // Custom template content
}

Project Structure

src/
├── database/           # Database related
│   ├── adapters/      # Database adapters
│   ├── manager.ts     # Database manager
│   └── pool.ts        # Connection pool
├── generator/         # Code generator
│   ├── generators/    # Various type generators
│   └── templates/     # Handlebars templates
├── metadata/          # Metadata analysis
├── query/             # Query builder
├── types/             # Type definitions
└── utils/             # Utility functions

❓ FAQ

generated/
├── entity/
├── mapper/
├── service/
└── controller/

🤝 Contributing

We welcome all forms of contributions!

How to Contribute

1. Fork this repository
2. Create your feature branch (git checkout -b feature/AmazingFeature)
3. Commit your changes (git commit -m 'Add some AmazingFeature')
4. Push to the branch (git push origin feature/AmazingFeature)
5. Open a Pull Request

Contribution Guidelines

• Follow existing code style
• Add appropriate tests
• Update relevant documentation
• Ensure all tests pass

Development Environment Setup

# Clone repository
git clone https://github.com/zpaeng/db-gen-mcp.git
cd db-gen-mcp

# Install dependencies
npm install

# Start development mode
npm run dev

📄 License

This project is licensed under the .

📞 Contact

• GitHub Issues: Submit Issues
• Discussions: GitHub Discussions

🙏 Acknowledgments

Thanks to the following open source projects:

• Model Context Protocol - MCP protocol standard
• MyBatis-Plus - Excellent MyBatis enhancement tool
• Handlebars.js - Template engine
• TypeScript - Type-safe JavaScript