Metadata-Version: 2.4
Name: pg-data-yaml
Version: 0.0.1
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]

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)
```

### 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]

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
```

### 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, a missing yaml file for an included table is treated as an empty table (rows are deleted). When syncing a single file, only that table is compared and updated.
