How to Use the db_query Plugin in Dify Chatflow Workflows: Complete Guide

The db_query plugin enables Dify chatflows to execute SQL SELECT statements against MySQL, PostgreSQL, Oracle, or MSSQL databases by adding a "Database Query" tool node that accepts connection parameters and returns results in markdown or JSON format.

The db_query plugin extends Dify's workflow capabilities by integrating direct database querying into chatflow automations. Developed by junjiem as an open-source extension, this tool allows developers to pull live data from relational databases and feed results directly into LLM prompts or conditional logic nodes. This guide explains how to configure and deploy the db_query plugin in your Dify chatflow workflows based on the actual implementation in the junjiem/dify-plugin-tools-dbquery repository.

Plugin Architecture and Core Components

Understanding the internal structure helps troubleshoot issues and optimize performance when integrating the db_query plugin into complex workflows.

Entry Point and Provider Registration

The plugin boots through db_query/main.py, which creates the Plugin instance and initializes the service. Tool discovery happens via db_query/provider/db_query.py, where the DbQueryProvider class registers the Database Query tool with Dify's tool registry, making it available in the workflow editor.

Query Execution Engine

The concrete implementation resides in db_query/tools/sql_query.py. When Dify invokes the tool, it calls SqlQueryTool._invoke(), which validates parameters and delegates database operations to db_query/tools/db_util.py. The DbUtil class builds SQLAlchemy engine URLs, executes queries using pandas, and converts DataFrame results to normalized dictionaries while handling date and UUID serialization automatically.

Pre-Authorization Variant

For production environments requiring credential security, db_query_pre_auth/tools/sql_query.py offers a hardened alternative. This variant stores credentials in the plugin's runtime settings rather than exposing them in workflow JSON payloads, reading connection details from self.runtime.credentials during execution.

Installation and Credential Configuration

Installing the Plugin

Install the db_query plugin via Dify's interface using the GitHub repository URL:

1. Navigate to Plugin → Install from GitHub.
2. Enter: https://github.com/junjiem/dify-plugin-tools-dbquery.

Runtime vs. Pre-Auth Credential Modes

The db_query plugin supports two authentication strategies that determine how database credentials reach the SQL engine:

• Runtime Mode: Credentials pass through each tool invocation's JSON parameters. Suitable for multi-tenant scenarios where connection details vary per execution or when querying different databases within the same workflow.
• Pre-Auth Mode: Credentials stored once in plugin settings. Configure db_type, db_host, db_port, db_username, db_password, db_name, and db_properties in the plugin configuration page, then reference only query_sql and output_format in workflow nodes.

Configuring Database Queries in Chatflows

Runtime Mode Parameters

When using runtime credentials, supply all connection details in the tool node's tool_parameters JSON:

{
  "db_type": "postgresql",
  "db_host": "db.example.com",
  "db_port": "5432",
  "db_username": "my_user",
  "db_password": "my_secret",
  "db_name": "sales_db",
  "db_properties": "",
  "query_sql": "SELECT order_id, total_amount FROM orders WHERE order_date >= '2024-01-01'",
  "output_format": "markdown"
}

Pre-Auth Mode Parameters

With stored credentials configured in the plugin settings, minimize the JSON to query-specific fields only:

{
  "query_sql": "SELECT * FROM customers LIMIT 5",
  "output_format": "json"
}

The plugin retrieves host, port, and authentication details from self.runtime.credentials as implemented in db_query_pre_auth/tools/sql_query.py.

Workflow Implementation Examples

Markdown Output for Human Review

Configure the tool node with output_format set to markdown (default) to receive GitHub-style tables suitable for direct user display:

| order_id | total_amount |
|----------|--------------|
| 101      | 1250.75      |
| 102      | 980.00       |

JSON Output for Programmatic Processing

Set output_format to json when feeding results into Parse JSON, LLM, or Condition nodes downstream:

{
  "records": [
    {"id": 1, "name": "Alice", "created_at": "2023-12-01 10:23:45"},
    {"id": 2, "name": "Bob", "created_at": "2023-12-04 14:12:09"}
  ]
}

Complete Chatflow YAML Definition

Define the workflow declaratively for version control:

nodes:
  - id: start
    type: start
    next: query
  - id: query
    type: tool
    tool_name: Database Query
    parameters: |
      {
        "db_type": "mysql",
        "db_host": "mysql.example.com",
        "db_port": "3306",
        "db_username": "admin",
        "db_password": "secret",
        "db_name": "inventory",
        "query_sql": "SELECT product_name, stock FROM products WHERE stock < 10",
        "output_format": "markdown"
      }
    next: reply
  - id: reply
    type: reply
    content: |
      Low stock items:
      {{ tool_output }}
    next: end
  - id: end
    type: end

The {{ tool_output }} variable receives the rendered markdown or JSON string yielded by SqlQueryTool._invoke().

Supported Databases and Connection Handling

The db_query plugin supports MySQL, PostgreSQL, Oracle (including Oracle-11g), and MSSQL through SQLAlchemy. The DbUtil class in db_query/tools/db_util.py handles connection pooling, URL construction for Oracle thin/thick modes, and automatic type conversion for dates and UUIDs to ensure JSON serializability.

Summary

• Install the db_query plugin from https://github.com/junjiem/dify-plugin-tools-dbquery via Dify's GitHub installer.
• Choose between Runtime Mode (credentials per request) or Pre-Auth Mode (stored credentials in db_query_pre_auth/tools/sql_query.py) depending on security requirements.
• Configure tool nodes with mandatory parameters: db_type, db_host, db_port, db_username, db_password, db_name, query_sql, and optional output_format.
• Use markdown output for human-readable tables or json for structured data processing in downstream nodes.
• The implementation in db_query/tools/sql_query.py handles validation, while db_query/tools/db_util.py manages SQLAlchemy connections and result normalization.

Frequently Asked Questions

What databases does the db_query plugin support?

The plugin supports MySQL, PostgreSQL, Oracle (including Oracle-11g), and Microsoft SQL Server (MSSQL). The DbUtil class generates appropriate SQLAlchemy connection strings for each database type, including specialized handling for Oracle thin and thick client modes to accommodate different deployment environments.

How do I secure database credentials in Dify chatflows?

Use the Pre-Auth Mode implemented in db_query_pre_auth/tools/sql_query.py. Configure credentials once in the plugin's settings page rather than passing them in workflow JSON. This stores sensitive data in self.runtime.credentials and keeps connection details out of chatflow logs, exports, and version control systems.

Can the db_query plugin execute INSERT, UPDATE, or DELETE statements?

No. The plugin is designed specifically for SELECT statements only. The SqlQueryTool._invoke() method constructs read-only SQLAlchemy sessions, and the tool is intended for data retrieval within chatflow workflows, not data modification. Attempting DML operations will result in SQL execution errors.

How does the plugin handle database connection errors?

The DbUtil class wraps SQLAlchemy operations in exception handling and returns error messages through Dify's message yield mechanism. If a connection fails, authentication fails, or the SQL syntax is invalid, the tool returns the error content in the specified output format (markdown or JSON), allowing you to route failures to alternative workflow branches using Condition nodes.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}