Metadata-Version: 2.4
Name: pg-data-yaml
Version: 0.1.0
Summary: Yaml data converter between DB and Repo
Home-page: https://github.com/andruche/pg-data-yaml
Author: Andrey Chernyakov
License: BSD
Project-URL: Documentation, https://github.com/andruche/pg-data-yaml/blob/master/README.md
Project-URL: Bug Tracker, https://github.com/andruche/pg-data-yaml/issues
Keywords: postgres,postgresql,yaml,reference-data
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3.8
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Requires-Python: >=3.8
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: pyyaml
Requires-Dist: asyncpg<0.31.0,>=0.27.0
Provides-Extra: dev
Requires-Dist: flake8<6,>=5; extra == "dev"
Requires-Dist: pytest<9,>=8; extra == "dev"
Requires-Dist: pytest-cov; extra == "dev"
Requires-Dist: pytest-flake8; extra == "dev"
Requires-Dist: pytest-asyncio; extra == "dev"
Requires-Dist: pytest-sugar; extra == "dev"
Dynamic: author
Dynamic: classifier
Dynamic: description
Dynamic: description-content-type
Dynamic: home-page
Dynamic: keywords
Dynamic: license
Dynamic: license-file
Dynamic: project-url
Dynamic: provides-extra
Dynamic: requires-dist
Dynamic: requires-python
Dynamic: summary

## pg-data-yaml — Yaml interface for reference tables in PostgreSQL

Export, diff and sync rows of reference tables in PostgreSQL to YAML files in a repository.

## installation

```
pip install pg-data-yaml
```

## which tables are included

Table selection is configured with `--comment-label` or `--table-list-predicate`. One of these options is **required** for `export`, `diff` and `sync` (mutually exclusive).

### `--comment-label LABEL`

Include tables whose comment contains `LABEL`. Any label can be used, for example `global directory` or `env directory`.

Optional clause in parentheses after the label is parsed from the comment (see [marking tables](#marking-tables) below).

```
pg_data_yaml export -d mydb --out-dir /tmp/refs --comment-label "global directory"
```

### `--table-list-predicate PREDICATE`

Include tables matching a SQL predicate. Table comments are ignored; export always uses `select * from <schema>.<table> order by <primary key columns>`.

The predicate is inserted into a query against `pg_catalog.pg_class` and `pg_catalog.pg_namespace`. Use `n.nspname` for schema name and `c.relname` for table name.

Example — tables without tenant columns:

```
pg_data_yaml export -d mydb --out-dir /tmp/refs --table-list-predicate "
not exists (
    select 1
      from information_schema.columns col
     where col.table_schema = n.nspname
       and col.table_name = c.relname
       and col.column_name in ('app_id', 'customer_id', 'customerid')
)"
```

Tables must have a primary key. Tables without a PK are skipped with a warning.

## marking tables

When using `--comment-label`, add a comment on the table (via `COMMENT ON TABLE`):

```
synchronized directory
```

With a custom label:

```
global directory
```

Optional clause in parentheses:

- if it contains the word `select` — a full export query, for example:

```
synchronized directory(select id, name from my_schema.my_table order by name)
```

- otherwise — a `WHERE` filter inserted into the default template:

```
synchronized directory(not is_deleted)
```

→ `select * from <schema>.<table> where not is_deleted order by <primary key columns>`

If parentheses are omitted, export uses:

```
select * from <schema>.<table> order by <primary key columns>
```

Exported file layout: `<out-dir>/<schema>/<table>.yaml`

Each file is a YAML list of row mappings. Row and field order match the query result.

## usage

### export

```
usage: pg_data_yaml export [--help] [-d DBNAME] [-h HOST] [-p PORT] [-U USER] [-W PASSWORD]
                             (--comment-label LABEL | --table-list-predicate PREDICATE)
                             --out-dir OUT_DIR [--clean] [--rows-limit ROWS]

options:
  --help                show this help message and exit
  -d DBNAME, --dbname DBNAME
                        database name to connect to
  -h HOST, --host HOST  database server host or socket directory
  -p PORT, --port PORT  database server port
  -U USER, --user USER  database user name
  -W PASSWORD, --password PASSWORD
                        database user password
  --comment-label LABEL
                        include tables whose comment contains LABEL
  --table-list-predicate PREDICATE
                        include tables matching SQL PREDICATE (comments ignored)
  --out-dir OUT_DIR     directory for exporting files
  --clean               clean out_dir if not empty (env variable DATA_DIRECTORY_AUTOCLEAN=true)
  --rows-limit ROWS     skip tables with more than ROWS rows (default: 50000)
```

### diff

```
usage: pg_data_yaml diff [--help] [-d DBNAME] [-h HOST] [-p PORT] [-U USER] [-W PASSWORD]
                           (--comment-label LABEL | --table-list-predicate PREDICATE)
                           --source SOURCE

options:
  --help                show this help message and exit
  -d DBNAME, --dbname DBNAME
  -h HOST, --host HOST  database server host or socket directory
  -p PORT, --port PORT  database server port
  -U USER, --user USER  database user name
  -W PASSWORD, --password PASSWORD
                        database user password
  --comment-label LABEL
                        include tables whose comment contains LABEL
  --table-list-predicate PREDICATE
                        include tables matching SQL PREDICATE (comments ignored)
  --source SOURCE       directory or yaml file to compare with the database
```

### sync

```
usage: pg_data_yaml sync [--help] [-d DBNAME] [-h HOST] [-p PORT] [-U USER] [-W PASSWORD]
                           (--comment-label LABEL | --table-list-predicate PREDICATE)
                           --source SOURCE [--dry-run] [--echo-queries] [-y]
                           [--quiet] [--session-replication-role ROLE]

options:
  --help                show this help message and exit
  -d DBNAME, --dbname DBNAME
  -h HOST, --host HOST  database server host or socket directory
  -p PORT, --port PORT  database server port
  -U USER, --user USER  database user name
  -W PASSWORD, --password PASSWORD
                        database user password
  --comment-label LABEL
                        include tables whose comment contains LABEL
  --table-list-predicate PREDICATE
                        include tables matching SQL PREDICATE (comments ignored)
  --source SOURCE       directory or yaml file to sync to the database
  --dry-run             test run without real changes
  --echo-queries        echo commands sent to server
  -y, --yes             do not ask confirm
  --quiet               do not show yaml diff before applying changes
  --session-replication-role ROLE
                        set session_replication_role locally in transaction before DML
```

### merge-envs

Move table files that are identical in all given environment directories into a shared base directory and remove them from the environment directories.

```
usage: pg_data_yaml merge-envs [--help] --source ENV_DIR [--source ENV_DIR ...] --out-dir OUT_DIR [--dry-run]

options:
  --help                show this help message and exit
  --source ENV_DIR      environment directory (repeat for each env)
  --out-dir OUT_DIR     base directory for common table files
  --dry-run             show actions without changing files
```

Example layout after merge:

```
refs/
  base/public/countries.yaml   # identical in all envs
  dev/public/special.yaml      # env-specific
  prod/public/special.yaml
```

## examples

Comment label for synchronized reference data:

```
$ pg_data_yaml export -d my_database -h 127.0.0.1 -p 5432 -U postgres \
    --out-dir /tmp/refs/ --comment-label "synchronized directory"
```

Comment label for per-environment data:

```
$ pg_data_yaml export -d my_database --out-dir /tmp/refs/base --comment-label "global directory"
$ pg_data_yaml export -d my_database --out-dir /tmp/refs/dev --comment-label "env directory"
```

Predicate-based selection:

```
$ pg_data_yaml export -d my_database --out-dir /tmp/refs/ --table-list-predicate "
not exists (
    select 1 from information_schema.columns col
     where col.table_schema = n.nspname
       and col.table_name = c.relname
       and col.column_name in ('app_id', 'customer_id', 'customerid')
)"
```

Diff and sync use the same table selection options as export:

```
$ pg_data_yaml merge-envs --source /tmp/refs/dev --source /tmp/refs/prod --out-dir /tmp/refs/base
$ pg_data_yaml diff -d my_database -h 127.0.0.1 -p 5432 -U postgres --source /tmp/refs/ --comment-label "global directory"
$ pg_data_yaml sync -d my_database -h 127.0.0.1 -p 5432 -U postgres \
    --source /tmp/refs/ --comment-label "synchronized directory"
$ pg_data_yaml diff -d my_database -h 127.0.0.1 -p 5432 -U postgres --source /tmp/refs/public/countries.yaml
```

When syncing a directory, only tables that are both in the selected set and have a yaml file in `--source` are compared. A warning is printed and the table is skipped when the file exists but the table is not in the selection, or when the table is in the selection but the yaml file is missing. When syncing a single file, only that table is compared and updated if it is in the selection. Each table is synced in its own transaction, so a failed table does not leave other tables half-updated.
