# pymysql vs psycopg2 vs pymssql: Database Driver Differences in the Dify DB-Query Plugin

> Explore pymysql vs psycopg2 vs pymssql. Understand the core differences in database drivers for Dify DB-Query plugin, covering implementation, performance, and dependencies.

- Repository: [Junjie.M/dify-plugin-tools-dbquery](https://github.com/junjiem/dify-plugin-tools-dbquery)
- Tags: deep-dive
- Published: 2026-03-05

---

**The Dify DB-Query plugin selects `pymysql` for MySQL (pure Python, easy deployment), `psycopg2` for PostgreSQL (C-extension with highest performance), and `pymssql` for SQL Server (FreeTDS wrapper), each differing in implementation language, performance characteristics, and platform dependencies.**

The `junjiem/dify-plugin-tools-dbquery` repository provides a unified SQL execution layer that abstracts database connectivity through SQLAlchemy. When you specify a `db_type` of `mysql`, `postgresql`, or `mssql`, the plugin internally maps your choice to a specific **DB-API driver**—`pymysql`, `psycopg2`, or `pymssql`—each engineered for different performance and deployment constraints.

## How the Plugin Selects Database Drivers

Driver selection happens inside the `DbUtil` class in [`db_query/tools/db_util.py`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/db_query/tools/db_util.py). The method `get_driver_name()` (lines 38‑47) returns a SQLAlchemy dialect string that pairs the database type with its corresponding driver:

```python
def get_driver_name(self):
    driver_name = self.db_type
    if self.db_type == 'mysql':
        driver_name = 'mysql+pymysql'
    elif self.db_type in {'oracle', 'oracle11g'}:
        driver_name = 'oracle+oracledb'
    elif self.db_type == 'postgresql':
        driver_name = 'postgresql+psycopg2'
    elif self.db_type == 'mssql':
        driver_name = 'mssql+pymssql'
    return driver_name

```

SQLAlchemy uses this string to import the correct **DB-API 2.0** implementation at runtime. The three drivers therefore differ not only in syntax but in how they translate Python calls into wire‑level database protocols.

## Driver Architecture and Performance Characteristics

### pymysql (MySQL) – Pure Python Implementation

**`pymysql`** is a pure‑Python MySQL client that implements the MySQL wire protocol entirely in Python code.

- **Deployment**: Zero compilation steps; `pip install pymysql` works on any platform that runs Python (Windows, Linux, macOS, ARM).
- **Performance**: Slower than C‑based alternatives because packet parsing and encryption happen in interpreted code. For typical BI queries returning thousands of rows, the difference is usually negligible, but high‑throughput OLTP workloads may notice latency.
- **Feature set**: Supports prepared statements, SSL, and most MySQL 8.0 authentication plugins. It satisfies the plugin’s requirement for standard `SELECT`, `INSERT`, `UPDATE`, and `DELETE` operations.

### psycopg2 (PostgreSQL) – C Extension with libpq

**`psycopg2`** is a C extension module that wraps the native PostgreSQL client library **`libpq`**.

- **Performance**: Offers the highest throughput of the three drivers because heavy lifting (serialization, network I/O, and type conversion) happens in compiled C code. Benchmarks often show **2‑5×** faster fetch speeds compared to pure‑Python drivers for large result sets.
- **Advanced features**: Exposes PostgreSQL‑specific capabilities the plugin could leverage in future versions:
  - **Server‑side cursors** (`named cursor`) for streaming huge tables without loading them entirely into memory.
  - **`COPY`** commands for bulk loading/unloading.
  - **Asynchronous notifications** (`LISTEN`/`NOTIFY`).
- **Installation**: Requires a C compiler and `libpq` headers, or you can install the pre‑built `psycopg2-binary` wheel. On minimal Alpine Linux containers, you must install `postgresql-dev` before pip.

### pymssql (SQL Server) – FreeTDS Wrapper

**`pymssql`** is a thin wrapper around the **FreeTDS** library, an open‑source implementation of the TDS (Tabular Data Stream) protocol used by Microsoft SQL Server and Sybase.

- **Cross‑platform connectivity**: Enables Linux and macOS hosts to connect to SQL Server without Microsoft’s proprietary ODBC drivers. It also works on Windows if FreeTDS is available.
- **Performance**: Generally slower than `pyodbc` paired with Microsoft’s native ODBC driver, because FreeTDS adds an extra translation layer and may not support the latest TDS protocol optimizations. For the plugin’s typical use case—ad‑hoc analytical queries—the difference is acceptable.
- **Feature limitations**: Lacks support for some SQL Server‑specific features such as **table‑valued parameters** and **MARS (Multiple Active Result Sets)** in older FreeTDS versions. The plugin currently uses simple query execution, so these gaps do not affect functionality.
- **Deployment**: On Linux/macOS you must install FreeTDS (`freetds-dev` or `freetds-bin`) before `pip install pymssql`. Windows wheels bundle FreeTDS, making installation easier on that platform.

## Practical Usage Examples

Below are complete, runnable snippets that demonstrate how the plugin instantiates each driver. All examples assume you have installed the required packages (`pymysql`, `psycopg2-binary`, `pymssql`) and that the target databases are reachable.

### MySQL with pymysql

```python
from db_query.tools.db_util import DbUtil

# db_type="mysql" triggers the mysql+pymysql dialect

client = DbUtil(
    db_type="mysql",
    username="app_user",
    password="secret",
    host="mysql.internal",
    port="3306",
    database="analytics"
)

result = client.run_query("SELECT COUNT(*) AS total FROM events")
print(result)  # [{'total': 15420}]

```

### PostgreSQL with psycopg2

```python
from db_query.tools.db_util import DbUtil

# db_type="postgresql" selects postgresql+psycopg2

client = DbUtil(
    db_type="postgresql",
    username="postgres",
    password="example",
    host="db.example.com",
    port="5432",
    database="sample_db"
)

# Returns list of dicts with column names as keys

records = client.run_query(
    "SELECT id, title FROM articles ORDER BY created_at DESC LIMIT 3"
)
print(records)

```

### SQL Server with pymssql

```python
from db_query.tools.db_util import DbUtil

# db_type="mssql" maps to mssql+pymssql

client = DbUtil(
    db_type="mssql",
    username="sa",
    password="YourPassword123",
    host="mssql.example.com",
    port="1433",
    database="production"
)

# T-SQL syntax uses TOP instead of LIMIT

rows = client.run_query(
    "SELECT TOP 10 EmployeeID, Name FROM Employees"
)
print(rows)

```

In each case, the **`DbUtil`** class delegates the heavy lifting to SQLAlchemy, which imports the specific driver module (`pymysql`, `psycopg2`, or `pymssql`) at runtime based on the dialect string returned by `get_driver_name()`.

## Installation and Deployment Considerations

Choosing the right driver affects your container size, build complexity, and runtime speed.

- **pymysql** – Ideal for minimal containers or environments where compilation tools are prohibited. Install with:
  ```bash
  pip install pymysql
  ```

- **psycopg2** – For production PostgreSQL workloads, prefer the pre‑built binary to avoid compiling C extensions:
  ```bash
  pip install psycopg2-binary
  ```

  If you build from source (e.g., on Alpine Linux), install `postgresql-dev` and a C compiler first.

- **pymssql** – Requires the FreeTDS library on Unix‑like systems:
  ```bash
  # Ubuntu/Debian

  sudo apt-get install freetds-dev
  
  # macOS

  brew install freetds
  
  pip install pymssql
  ```

  Windows users can install directly via pip because the wheel bundles FreeTDS.

## Summary

- **pymysql** is a pure‑Python MySQL driver that trades raw speed for portability and zero‑compilation installs.
- **psycopg2** is a C‑extension wrapper around PostgreSQL’s `libpq`, delivering the highest performance and advanced features like server‑side cursors and bulk `COPY`.
- **pymssql** leverages FreeTDS to connect Python to Microsoft SQL Server; it enables cross‑platform access but carries the extra dependency of the FreeTDS library and lacks some modern SQL Server features found in ODBC-based drivers.

All three drivers are abstracted behind the `DbUtil` class in [`db_query/tools/db_util.py`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/db_query/tools/db_util.py), letting you switch databases by simply changing the `db_type` parameter without rewriting query logic.

## Frequently Asked Questions

### Which driver offers the best performance for PostgreSQL in the Dify plugin?

**psycopg2** provides the best performance because it is a C extension that directly wraps the native PostgreSQL client library `libpq`. It minimizes Python interpreter overhead during result set serialization and supports server‑side cursors for streaming large tables without loading them entirely into memory.

### Can I use pymysql on Windows without installing a C compiler?

Yes. **pymysql** is implemented entirely in Python, so it does not require a C compiler or any external libraries. You can install it on Windows, Linux, or macOS using `pip install pymysql` and immediately connect to MySQL or MariaDB instances.

### Why does the plugin use pymssql instead of pyodbc for SQL Server?

The plugin selects **pymssql** because it bundles the FreeTDS library, enabling SQL Server connectivity on Linux and macOS hosts without installing Microsoft’s proprietary ODBC drivers. While `pyodbc` offers broader feature support (e.g., table‑valued parameters and MARS), `pymssql` satisfies the plugin’s requirement for simple query execution while maintaining cross‑platform portability.

### Are there security differences between these three drivers?

All three drivers support **SSL/TLS** encryption when configured on the server side, but their underlying implementations differ. `psycopg2` inherits PostgreSQL’s native SSL handling through `libpq`, `pymysql` implements SSL in pure Python (slightly higher CPU usage), and `pymssql` relies on FreeTDS’s encryption support, which may require explicit `tds version` configuration for modern TLS standards. Always verify that your connection strings include `sslmode=require` (PostgreSQL) or equivalent flags for production deployments.