Metadata-Version: 2.4
Name: compare-schema
Version: 0.1.1
Summary: Compare schema and data between databases
Author-email: Gu Park <doctorgu@kakao.com>
License-Expression: MIT
Project-URL: homepage, https://github.com/doctorgu/compare_schema
Project-URL: repository, https://github.com/doctorgu/compare_schema
Project-URL: documentation, https://github.com/doctorgu/compare_schema
Keywords: compare schema,database comparison,schema diff,mysql,sqlite
Requires-Python: >=3.13
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: sqlite3-client>=1.2.0
Requires-Dist: mysqlclient-client>=1.2.0
Requires-Dist: pydantic>=2.0
Requires-Dist: pydantic-settings>=2.0
Requires-Dist: xlsxwriter>=3.0
Requires-Dist: tabulate>=0.9
Requires-Dist: types-tabulate>=0.10
Requires-Dist: requests>=2.28
Requires-Dist: PyYAML>=6.0
Requires-Dist: SQLAlchemy>=2.0
Requires-Dist: PyMySQL>=1.0
Requires-Dist: python-dotenv>=1.0.0
Requires-Dist: ruff
Provides-Extra: test
Requires-Dist: pytest; extra == "test"
Requires-Dist: pytest-cov; extra == "test"
Requires-Dist: pytest-env; extra == "test"
Requires-Dist: pytest-mock; extra == "test"
Requires-Dist: pytest-asyncio; extra == "test"
Requires-Dist: build; extra == "test"
Dynamic: license-file

# compare-schema — Database Schema and Data Comparison Tool

[English](README.md) | [한국어](README_KO.md)

A database schema and data comparison tool across different environments (e.g. dev, stg, prd).

> [!NOTE]
> Currently, only **MySQL** schema and data are supported. The comparison metadata and version history are stored locally in an embedded SQLite database.

## Features

- **Schema Comparison**: Compare tables, views, columns, indices, and foreign key references across databases.
- **Data Comparison**: Compare key-based data records across environments.
- **Database History Tracking**: Track changes to the same database over time by comparing versions.
- **Multiple Output Formats**: Export comparison reports to Markdown (`.md`) or Excel (`.xlsx`).
- **History & Versioning**: Tracks versions in SQLite to detect schema drift over time.
- **Notifications**: Discord webhook notification support for schema and data changes.
- **CLI & Module Support**: Run via `compare-schema` CLI command or `python -m src.main`.

## Installation

```bash
pip install compare-schema
```

## Quick Start

### 1. Configuration (`config/config.yaml`)

Create a configuration YAML file:

```yaml
output_type: md # md or xlsx
output_dir: ./compare_schema/result
prev_version: ""

data_tables:
  - table: users
    columns:
      - name
      - email
      - nick_name
    key_columns:
      - id

exclude_tables: []
exclude_columns: []
db_host: 127.0.0.1
envs:
  - name: dev
    port: 3306
    db_names: [shop, logs]
  - name: stg
    port: 3306
    db_names: [shop]
  - name: prd
    port: 3306
    db_names: [shop]

compare_list:
  # Compare between different databases or environments
  - a: dev.shop
    b: dev.logs
  - a: dev.shop
    b: stg.shop
  - a: stg.shop
    b: prd.shop
  # If a and b is same, compares between recent version and new version (shows history of that DB)
  - a: dev.shop
    b: dev.shop
  - a: stg.shop
    b: stg.shop
  - a: prd.shop
    b: prd.shop
```

### 2. Version Comparison & History

The tool supports two comparison modes based on the entries defined in `compare_list` (`{env}.{database}`):

#### A. Cross-Database Comparison (`a != b`)

Compares schemas and data between two distinct environments or databases (e.g., `dev.shop` vs `stg.shop`):

- **Schema Differences**:
  - `schema_not_exists`: Identifies missing tables, views, columns, indices, and foreign keys present in one database but absent in the other.
  - `schema_diff`: Identifies structural discrepancies in matching objects:
    - **Columns**: Data types, nullability (`is_nullable`), default values, character length, numeric precision/scale, and column comments.
    - **Indices**: Index types (BTREE, etc.), uniqueness (`is_unique`), and indexed column combinations.
    - **Foreign Keys**: Target referenced tables and columns.
- **Data Differences**:
  - Compares rows in tables specified in `data_tables`.
  - Matches rows between databases using the primary/unique keys configured in `key_columns`.
  - `data_not_exists`: Rows present in one database but missing in the other.
  - `data_diff`: Rows that exist in both databases but have differing values in the monitored `columns`.
- **Output**: Exported to `{version}_compare.md` (Markdown) or the `compare` sheets in Excel.

#### B. Database History & Drift Tracking (`a == b`)

When `a` and `b` reference the same database (e.g., `a: dev.shop, b: dev.shop`), the tool compares the new `version` (timestamped as `YYYYMMDDHHmm`) against `prev_version`:

- **Change Categories**:
  - `schema_added` / `data_added`: Newly created tables, views, columns, indices, foreign keys, or inserted rows.
  - `schema_removed` / `data_removed`: Dropped schema objects or deleted rows.
  - `schema_changed` / `data_changed`: Modified column types/properties or updated data row values over time.
- **Output**: Exported to `{version}_log.md` (Markdown) or the `log` sheets in Excel.

---

### Configuration Reference (`config.yaml`)

| Parameter | Type | Default | Description |
| :--- | :--- | :--- | :--- |
| `output_type` | `Literal["xlsx", "md"]` | Required | Output format. `"md"` generates Markdown files; `"xlsx"` generates an Excel workbook. |
| `output_dir` | `str` | Required | Directory path where output reports are saved. |
| `prev_version` | `str` | `""` | Previous version (`YYYYMMDDHHmm`) to compare against. If empty (`""`), automatically uses the latest version recorded in SQLite. |
| `process_when_changed` | `bool` | Required | If `true`, stops execution without generating reports if no schema or data changes are detected against `prev_version`. If `false`, generates reports on every run. |
| `data_tables` | `list[DataTableConfig]` | `[]` | List of tables and columns to compare data for. |
| `data_tables[].table` | `str` | Required | Table name in MySQL. |
| `data_tables[].columns` | `list[str]` | Required | Columns to compare for value differences. |
| `data_tables[].key_columns` | `list[str]` | Required | Key columns (e.g., primary key) used to match corresponding rows across databases. |
| `exclude_tables` | `list[str]` | Required | Table names to exclude from cross-environment schema comparison (`a != b`), such as migration tables (e.g., `["alembic_version"]`). *(Not excluded during same-database history tracking `a == b`)*. |
| `exclude_columns` | `list[str]` | Required | Column names to ignore during cross-environment column comparisons. |
| `db_host` | `str` | Required | Hostname or IP address for MySQL database connections. |
| `envs` | `list[EnvConfig]` | Required | List of environments and databases to connect to and extract. |
| `envs[].name` | `str` | Required | Environment identifier (e.g., `dev`, `stg`, `prd`). |
| `envs[].port` | `int` | Required | MySQL port number for this environment. |
| `envs[].db_names` | `list[str]` | Required | List of database names to extract from this environment. |
| `compare_list` | `list[EnvCompareConfig]` | Required | Target database pairs to compare (e.g., `a: dev.shop`, `b: stg.shop`). |

---

### How It Works

1. **Extraction & Snapshotting**:
   - Connects to MySQL instances defined in `envs` using credentials from `.env`.
   - Extracts schema definitions (`information_schema.TABLES`, `COLUMNS`, `STATISTICS`, `KEY_COLUMN_USAGE`) and records from `data_tables`.
   - Saves a point-in-time snapshot into an embedded SQLite database (`db_client/schema_data.sqlite3`), tagged with the current version timestamp (`YYYYMMDDHHmm`).
2. **Change Detection**:
   - Queries SQLite to check whether any schema or data differences exist between the current version and `prev_version`.
   - If `process_when_changed: true` and no changes are found, execution halts early.
3. **Comparison Execution**:
   - Compares all configured pairs in `compare_list` using parameterized queries with `#foreach` list filtering.
4. **Report Export & Alerts**:
   - Writes Markdown or Excel reports to `data/`.
   - If schema or data changes are detected in log mode (`is_log: true`), an alert is automatically posted to Discord via `DISCORD_WEB_HOOK_URL`.
   - Any runtime errors or exceptions are formatted and sent to Discord as alerts.

### 3. Environment Variables (`.env`)

Set database credentials in `.env`:

```env
DB_USERNAME=your_username
DB_PASSWORD=your_password
DISCORD_WEB_HOOK_URL=https://discord.com/api/webhooks/...
```

### 4. Run Comparison

```bash
# Run via CLI
compare-schema --config_path config/config.yaml

# Or run as Python module
python -m src.main --config_path config/config.yaml
```

## License

MIT License. See [LICENSE](LICENSE) for details.
