Padmy

CLI utility functions for PostgreSQL such as sampling and anonymization.

Installation

Just run

uv add padmy

1. Database Exploration

You can get information about a database by running

uvx padmy analyze --db test --schemas test

or using the docker image

 docker run -it \
   --network host \
   ghcr.io/soren/padmy:latest analyze --db test --schemas test

For instance, the following table definition will output:

CREATE TABLE table1
(
    id SERIAL PRIMARY KEY
);

CREATE TABLE table2
(
    id        SERIAL PRIMARY KEY,
    table1_id INT REFERENCES table1
);

CREATE TABLE table3
(
    id        SERIAL PRIMARY KEY,
    table1_id INT REFERENCES table1,
    table2_id INT REFERENCES table2
);

CREATE TABLE table4
(
    id        SERIAL PRIMARY KEY,
    table1_id INT REFERENCES table1
);
INSERT INTO table1(id)
SELECT generate_series(0, 10);

Default

Network Schema (if --show-graphs is specified)

2. Sampling

You can quickly sample (ie: take a subset) of a database by running

uvx padmy sample \
  --db test --to-db test-sampled \
  --sample 20 \
  --schemas public

This will sample the test database into a new test-sampled database, copy of the original one, keeping if possible (see: Annexe) 20% of the original database.

You can choose how to sample with more granularity by passing a configuration file. Here is an example:

# We want a default sampling size of 20% of each table count
sample: 20
# We want to sample `schema_1` and `schema_2`
schemas:
  - schema_1
  # We want a default size of 30% for the tables of this schema
  - name: schema_2
    sample: 30

tables:
  # We want a sample size of 10% for this table
  - schema: public
    table: table_3
    sample: 10

3. Anonymization

You can scrub PII from selected columns with padmy anonymize. Field types map to Faker generators (or NULL to blank the column). Available types:

NULL fields are applied in a single set-based UPDATE; Faker fields are generated in Python and pushed in chunked UPDATE ... FROM unnest(...) statements. A table can carry a where: SQL filter so the rows it excludes are left untouched (eg. internal accounts).

Example config:

tables:
  - schema: public
    table: users
    where: "email NOT LIKE '%@my-company.com'"   # optional: rows to leave as-is
    fields:
      - column: email
        type: EMAIL
        domain: example.com         # extra arg forwarded to faker.email
      - column: password_hash
        type: "NULL"                # quoted: YAML's bare NULL parses as null
      - column: first_name
        type: FIRST_NAME
      - column: birthdate
        type: DATE_OF_BIRTH
        minimum_age: 18
        maximum_age: 80

Run with:

uvx padmy anonymize --db test -f config.yml

4. Migration utils

Setting up

This library includes a migration utility to help you evolve your data model. In order to use it, start by setting up the migration table:

uvx padmy -vv migrate setup --db postgres

This will create the public.migration table that stores all the migration / rollback that will be applied.

Setting up the Schemas

Now that we are all setup, let's create our first sql file that will create the schema:

rm -rf /tmp/sql
mkdir /tmp/sql
uvx padmy -v migrate new-sql 1 --sql-dir /tmp/sql
tree /tmp/sql

Add CREATE SCHEMA general; to the file.

echo "CREATE SCHEMA general;" >> /tmp/sql/0001_new_file.sql
cat /tmp/sql/0001_new_file.sql

Then apply the modifications to the database:

uvx padmy -v migrate apply-sql --sql-dir /tmp/sql --db postgres 

Creating a first migration

Now, lets create our first migration:

migration_dir="/tmp/migrations"
rm -rf "$migration_dir"
mkdir "$migration_dir" # You can choose a different folder to store your migrations
uvx padmy -v migrate new --sql-dir "$migration_dir" --author padmy
tree "$migration_dir"

This will create 2 new files:

• up: {timestamp}-{migration_id}-up.sql that contains your migration to apply to the database.
• down: {timestamp}-{migration_id}-down.sql that contains the code to revert your changes.

Let's now modify the up.sql file with:

CREATE TABLE IF NOT EXISTS general.test
(
    id  int primary key,
    foo int
);

CREATE TABLE IF NOT EXISTS general.test2
(
    id  serial primary key,
    foo text
);

file="$migration_dir/$(ls "$migration_dir" | grep up.sql)"
printf "\n" >> "$file"
cat <<'SQL' >> "$file"
CREATE TABLE IF NOT EXISTS general.test (
    id  INT PRIMARY KEY,
    foo INT
);

CREATE TABLE IF NOT EXISTS general.test2 (
    id  SERIAL PRIMARY KEY,
    foo TEXT
);
SQL

and check that the migration is valid:

uvx padmy -v migrate verify --sql-dir /tmp/migrations --schemas general

Because we did not add anything to the down.sql file, the command returns an error. Let's modify it to make the command pass:

DROP table general.test;
DROP table general.test2;

uvx padmy -vv migrate verify --sql-dir /tmp/migrations

We are all good !

Optional: You can also verify that the order of the migration is correct by running:

uvx padmy -vv migrate verify-files --sql-dir /tmp/migrations --no-raise

5. Comparing databases schemas

You can compare two databases by running:

uvx padmy -vv schema-diff --db soren --schemas schema_1,schema_2

If differences are found, the command will output the differences between the two databases.

Known limitations

Exact sample size

Sometimes, we cannot guaranty that the sampled table will have the exact expected size.

For instance let's say we want 10% of table1 and 10% of table2, given the following table definitions:

CREATE TABLE table1
(
    id SERIAL PRIMARY KEY
);

CREATE TABLE table2
(
    id        SERIAL PRIMARY KEY,
    table1_id INT NOT NULL REFERENCES table1
);

INSERT INTO table1(id)
VALUES (1);

INSERT INTO table2(table1_id)
SELECT 1
FROM generate_series(1, 10);

In this case, it's not possible to have less that 100% of table 1 since it has only 1 key on which depend all the table1_id rows of table2.

Cyclic foreign keys

Cyclic foreign keys (table with a FK on another table that reference the previous one) are not supported. Here is an example.

CREATE TABLE table1
(
    id        SERIAL PRIMARY KEY,
    table2_id INT NOT NULL
);

CREATE TABLE table2
(
    id        SERIAL PRIMARY KEY,
    table1_id INT NOT NULL
);

ALTER TABLE table1
    ADD CONSTRAINT table1_table2_id_fk
        FOREIGN KEY (table2_id) REFERENCES table2;

ALTER TABLE table2
    ADD CONSTRAINT table2_table1_id_fk
        FOREIGN KEY (table1_id) REFERENCES table1;

You can display cycling dependencies in a database by running:

uvx padmy -vv analyze --db test --schemas test --show-graph

(Note:: you'll need to have installed the network extra )

Self referencing foreign keys

Foreign keys referencing another column in the same table are ignored.

CREATE TABLE table1
(
    id        SERIAL PRIMARY KEY,
    parent_id INT REFERENCES table1
);

Annexes

Showing Network in Jupyter

You can display the network visualization in Jupyter.

Start by launching a JupyterLab session with:

uv run --group notebook jupyter lab

Then create a new notebook and run the following code:

from dash import Dash
from padmy.sampling import network, viz, sampling
from padmy.utils import init_connection
import asyncpg

PG_URL = 'postgresql://postgres:postgres@localhost:5432/test'

app = Dash(__name__)

db = sampling.Database(name='test')

async with asyncpg.create_pool(PG_URL, init=init_connection) as pool:
    await db.explore(pool, ['public'])

g = network.convert_db(db)

app.layout = viz.get_layout(g,
                            style={'width': '100%', 'height': '800px'},
                            layout='klay')

app.run(jupyter_mode='jupyterlab')  # or jupyter_mode='inline'