AliyunDMS Plugin

Author: aliyundms
Version: 0.0.8 Type: Tool Provider Plugin for Dify

Overview

AliyunDMS Plugin is a powerful Dify plugin developed by Alibaba Cloud Data Mangement Service that makes it easy for users to set up Data+AI workflows. This plugin provides comprehensive database management and natural language processing capabilities, enabling users to interact with databases using natural language queries and execute SQL operations securely.

Features

Core Tools

1. NL2SQL (Natural Language to SQL)

   • Convert natural language queries to SQL statements
   • Support for multiple LLM models including Qwen series and custom models
   • Enterprise edition with advanced features
   • Domain knowledge integration for better accuracy
   • Configurable reasoning levels (Standard, High, Expert)

2. SQL Executor

   • Execute SQL queries across multiple database types
   • Support for MySQL, PostgreSQL, SQLServer, Oracle, ClickHouse, and MongoDB
   • Built-in security controls to block risky SQL operations
   • Configurable output formats (Markdown, JSON)
   • Row limit controls for result sets
   • Decimal precision control (0-15 decimal places) for Markdown output

3. Advanced SQL Executor

   • Enhanced SQL execution capabilities
   • Additional security features
   • Enterprise-grade performance optimizations
   • Decimal precision control (0-15 decimal places) for Markdown output

4. Get MSchema

   • Retrieve database schema information (tables, columns, types, comments)
   • Support domain knowledge integration
   • Automatic metadata extraction for NL2SQL optimization
   • Works with both direct connections and DMS-managed databases

5. Data Visualizer

   • Create charts and visualizations from query results
   • Support for pie charts, line charts, bar charts, and mixed visualizations

6. Data2SQL

   • Convert structured data to SQL operations
   • Batch processing capabilities

7. LLM Reranker

   • Re-rank and improve query results using LLM
   • Enhanced result relevance

Supported Database Types

• MySQL: Via mysql+pymysql:// connection strings
• PostgreSQL: Via postgresql+psycopg2:// connection strings
• SQL Server: Via mssql+pymssql:// connection strings
• Oracle: Via oracle+oracledb:// connection strings
• ClickHouse: Via clickhouse+native:// connection strings
• MongoDB: Via mongodb:// connection strings

Installation

Prerequisites

• Python 3.12+
• Dify platform access
• Database credentials (for direct connections) or DMS-managed databases

Setup

1. Install dependencies:

   pip install -r requirements.txt
   

2. Configure the plugin:

   • For basic features: No API key required
   • For enterprise features: Obtain DMS API Key from AliyunDMS

3. Deploy to Dify:

   dify-plugin plugin package ./
   

Usage

NL2SQL Tool

Convert natural language questions to SQL queries:

# Example parameters
query: "Show me all users who registered in the last 30 days"
dms_db_info: '{"dialect": "mysql", "database": "users_db"}'
model: "dms-ai/xiyan-sql-32b"
level: "base"

SQL Executor Tool

Execute SQL queries safely:

# Example parameters
sql: "SELECT * FROM users WHERE created_at > '2024-01-01'"
db_connection_url: "mysql+pymysql://user:pass@host:3306/database"
output_format: "markdown"
block_risky_sql: true
max_limit_rows: 100
decimal_precision: 3  # Number of decimal places (0-15), only affects Markdown output

Decimal Precision Control:

• Range: 0-15 decimal places
• Default: 3
• Set to 0 to display integers only
• Only affects Markdown format output

Security Features

Risky SQL Detection

The plugin automatically detects and blocks potentially dangerous SQL operations including:

• CREATE, DROP, DELETE, TRUNCATE
• ALTER, UPDATE, INSERT, GRANT
• And other high-risk operations

API Key Management

• Optional DMS API Key for enterprise features
• Secure credential storage and validation
• API key validation with format checking

Configuration

Environment Variables

Create a .env file based on .env.example:

# For local debugging
INSTALL_METHOD=remote
REMOTE_INSTALL_HOST=https://debug.dify.ai
REMOTE_INSTALL_PORT=5003
REMOTE_INSTALL_KEY=your_debug_key

Plugin Configuration

Configure tools in provider/aliyundms.yaml:

• Enable/disable specific tools
• Set API key requirements
• Configure tool-specific parameters

Development

Project Structure

aliyundms/
├── main.py                 # Plugin entry point
├── manifest.yaml           # Plugin manifest
├── provider/
│   ├── aliyundms.yaml      # Provider configuration
│   └── aliyundms.py        # Provider implementation
├── tools/                  # Tool implementations
│   ├── nl2sql.py           # Natural language to SQL
│   ├── sql_executor.py     # SQL execution engine
│   ├── data_visualizer.py  # Data visualization
│   └── ...
├── scripts/               # Utility scripts
├── tests/                 # Test files
└── requirements.txt       # Python dependencies

Key Dependencies

• dify_plugin==0.4.1 - Dify plugin framework
• pandas~=2.2.3 - Data manipulation
• sqlalchemy~=2.0.41 - Database ORM
• sqlglot~=26.11.1 - SQL parsing and transformation
• Database drivers for MySQL, PostgreSQL, SQL Server, Oracle, ClickHouse, MongoDB
• alibabacloud_dms_enterprise20181101 - AliyunDMS SDK

Enterprise Features

Advanced NL2SQL Capabilities

• Adaptive Model Selection: Automatically chooses the best model for your query
• Multiple Model Support: Qwen-Max, Qwen-Plus, Qwen-Coder-Plus, DMS-AI/XiYan-SQL-32B
• Custom Model Integration: Support for privately deployed models
• Domain Knowledge: Incorporate business-specific knowledge for better SQL generation
• Multi-level Reasoning: Standard, High, and Expert reasoning levels

DMS Integration

• Passwordless Connections: Use DMS-managed databases without credentials
• Enterprise Security: Advanced security controls and monitoring
• Performance Optimization: Optimized for large-scale enterprise deployments

Testing

Run tests using:

python -m pytest tests/

Deployment

Package the Plugin

dify-plugin plugin package ./

This creates a plugin.difypkg file ready for deployment to Dify.

Publish to Marketplace

1. Ensure all documentation is complete
2. Update privacy policy in PRIVACY.md
3. Test thoroughly in development environment
4. Submit the packaged plugin to Dify Marketplace

Support

For support and documentation:

• Dify Plugin Documentation
• AliyunDMS Documentation
• NL2SQL Setup Guide

Privacy Policy

Please refer to PRIVACY.md for the plugin's privacy policy.

License

This plugin is developed and maintained by AliyunDMS. Please refer to the terms of service and licensing agreements for usage rights and restrictions.

Version History

v0.0.8 (Current)

• Added decimal precision control for SQL Executor and Advanced SQL Executor
  • Configurable decimal places (0-15) for Markdown output
  • Default precision: 3 decimal places
  • Useful for financial data, statistics, and custom formatting needs
• Added Get MSchema tool
  • Retrieve comprehensive database schema information
  • Support for domain knowledge integration
  • Automatic metadata extraction for improved NL2SQL accuracy

v0.0.7

• Enhanced NL2SQL capabilities with multiple model support
• Improved security features for SQL execution
• Added data visualization tools
• Enterprise edition features with DMS API integration
• Multi-language support (English, Chinese, Portuguese, Japanese)

---

Note: This plugin requires a Dify platform instance to function properly. Basic features work without API keys, while enterprise features require DMS API Key authentication.