# Exwiw Export What I Want (Exwiw) is a Ruby gem that allows you to export records from a database to a dump file(to specifically, the full list of INSERT sql) on the specified conditions. ## When to use Most of case in developing a software, There is no better choice than the same data in production. You might make well-crafted data, but it's very very hard to maintain. If you find the way to maintain the data for develoment env, then exwiw might be a solution for that. - Export the full database and mask data and import to another database. - Setup some system to replicate and mask data in real-time to another database. You want to export only the data you want to export. ## Features - Export the full list of INSERT sql for the specified conditions. - Provide serveral masking options for sensitive columns. - Provide config generator for ActiveRecord. ## Installation ```bash bundle add exwiw ``` Most of cases, you want to add 'require: false' to the Gemfile. If bundler is not being used to manage dependencies, install the gem by executing: ```bash gem install exwiw ``` ## Supported Databases - mysql - postgresql - sqlite - mongodb (see [MongoDB support](docs/mongodb.md)) For MySQL, exwiw connects through whichever of the `mysql2` or `trilogy` gem is available (preferring `mysql2`), so an app on either driver works without any extra setup. There is no separate `trilogy` adapter name — pass `--adapter=mysql` either way. Set `EXWIW_MYSQL_DRIVER=trilogy` (or `mysql2`) to force a specific driver. This is useful when the `mysql2` gem is linked against a `libmysqlclient` that can no longer load the server's auth plugin — e.g. a MySQL 9.x client drops the `mysql_native_password` plugin and raises `Authentication plugin 'mysql_native_password' cannot be loaded` on connect. The pure-Ruby `trilogy` driver implements that auth handshake itself and sidesteps the issue. ## Usage exwiw has two subcommands: - `export` (default) — generate INSERT/COPY SQL files. If the subcommand is omitted, `export` is assumed. - `explain` — print each query `export` would run together with its `EXPLAIN` output. SQL adapters compile the SELECT without executing it; mongodb runs the server's explain (defaulting to the execution-free `queryPlanner`). ### `exwiw export` ```bash # dump & masking all records from database to dump.sql based on schema.json # pass database password as an environment variable 'DATABASE_PASSWORD' exwiw \ --adapter=mysql \ --host=localhost \ --port=3306 \ --user=reader \ --database=app_production \ --schema-dir=exwiw/schema \ --target-table=shops \ --ids=1 \ # comma separated ids --output-dir=dump \ --log-level=info ``` By default `--ids` are matched against the target table's primary key. If the target table declares a per-table `scope_column`, exwiw runs in [scope-column mode](#scope-column-mode) instead — `--ids` are then values of that shared column, and the table is scoped like any other rather than anchored by primary key. When `--target-table` and `--ids` are omitted, exwiw dumps all tables defined in `--schema-dir`: ```bash # dump all tables exwiw \ --adapter=postgresql \ --host=localhost \ --port=5432 \ --user=reader \ --database=app_production \ --schema-dir=exwiw/schema \ --output-dir=dump ``` This command will generate sql files in the `dump` directory. The output dir is emptied before each export so it never mixes files from a previous run (defaulting to `dump/` when `--output-dir` is omitted). When run interactively (stdin is a tty) and the dir already contains files, exwiw asks for confirmation before removing them; in non-interactive contexts (CI, pipes) it proceeds without prompting. - `dump/insert-000-schema.sql` — idempotent `CREATE TABLE IF NOT EXISTS ...` for every table in scope. Apply this first to provision an empty database. - `dump/insert-{idx}-{table_name}.sql` - `dump/delete-{idx}-{table_name}.sql` idx means the order of the dump. bigger idx might depend on smaller idx, so you should import the dump in order. `insert-000-schema.sql` is generated by shelling out to the database client tools (`mysqldump` for `mysql`, `pg_dump` for `postgresql`, and the sqlite3 driver for `sqlite`), so the corresponding client must be available on PATH when running exwiw. For `mysql`, set `EXWIW_MYSQLDUMP` to point at a specific `mysqldump` binary when the one on PATH is incompatible with the server (e.g. a MySQL 9.x `mysqldump` cannot load `mysql_native_password` against a server still using that auth plugin — `EXWIW_MYSQLDUMP=/path/to/mysql@8.0/bin/mysqldump`). The output is post-processed to make it idempotent: `CREATE TABLE IF NOT EXISTS`, `CREATE INDEX IF NOT EXISTS` (where the engine supports it), and PostgreSQL's `ALTER TABLE ... ADD CONSTRAINT` statements are wrapped in `DO $$ ... EXCEPTION WHEN duplicate_object`. For `mysql`, the source server's `DEFINER=user@host` stamp on views and triggers is stripped too, so restoring into a managed MySQL instance (which usually can't grant the privilege to recreate someone else's `DEFINER`) does not fail. For `postgresql`, the extensions a managed platform installs to run the source instance itself are treated as out of target and left out of the dump entirely — currently `google_vacuum_mgmt` (Cloud SQL / AlloyDB adaptive autovacuum), `google_columnar_engine` and `google_db_advisor` (AlloyDB). They serve the source instance's operation (vacuum tuning, the in-memory columnar cache, index advice), hold no application data, are referenced by nothing in the application's own schema, and ship only with the managed platform, so a restore target outside it can never create them. Their schemas are dropped via `pg_dump --exclude-schema` and their `CREATE EXTENSION` / `COMMENT ON EXTENSION` statements — which are not schema-qualified, so no `pg_dump` filter reaches them — are removed from the output; whatever was excluded is named in the run's log. The list is exact names, not a `google_*` prefix match: those prefixes are not reserved, so a prefix rule would also drop a schema an application legitimately owns (`google_calendar` for a Google Calendar integration) together with its tables. Every other extension is kept and wrapped in the usual warn-and-skip `DO` block, including two kinds that are also managed-platform-only: - a third-party extension pulled in as a dependency of an excluded one (`google_db_advisor` requires `hypopg`), since that one *is* installable on a plain PostgreSQL, and - an application-facing platform extension (`google_ml_integration`, `alloydb_scann`, `alloydb_ai_nl`), which the application's own SQL and DDL can name (a ScaNN index is `USING scann`) — removing its `CREATE` would strand whatever refers to it, so it warns and skips instead. you need to delete the records before importing the dump, `delete-{idx}-{table_name}.sql` will help you to do that. This sql will delete "all" related records to the extract targets. idx meaning is the same as insert sql. ### `exwiw explain` Print the query each `export` would run together with its `EXPLAIN` output, to stdout. For the SQL adapters (`mysql`, `postgresql`, `sqlite`) this is the compiled SELECT plus its `EXPLAIN` (estimate-only; `EXPLAIN QUERY PLAN` on SQLite) — no SELECT is executed. For `mongodb` it is the `find` description plus the server's explain document as JSON. ```bash # preview the queries exwiw would run, without executing the SELECTs exwiw explain \ --adapter=postgresql \ --host=localhost --port=5432 --user=reader \ --database=app_production \ --schema-dir=exwiw/schema \ --target-table=shops --ids=1 ``` The `--output-dir`, `--output-format`, `--insert-only`, and `--after-insert-hook` options are dump-specific and rejected when used with `explain`. MongoDB-specific explain behavior — the configurable verbosity (`queryPlanner` / `executionStats` / `allPlansExecution`) and how scoped collections are shown — is described in [MongoDB support](docs/mongodb.md#exwiw-explain-verbosity). ### How each table is narrowed — the six scoping paths Only the dump target itself is filtered by `--ids` directly. Every *other* table must be **scoped** — narrowed to just the rows related to the target — some other way, and a table that cannot be scoped at all is dumped in full (or, in scope-column mode, aborts the run). exwiw resolves each table through the **first** of these six paths that applies: | # | Path | When it applies | Resulting query shape | |---|------|-----------------|-----------------------| | 1 | **Direct filter** | The table is the `--target-table` itself; or, in [scope-column mode](#scope-column-mode), it declares a `scope_column` | `WHERE pk IN (ids)` / `WHERE scope_column IN (ids)` | | 2 | **`belongs_to` join walk** | The table reaches the target (or a scope-column table) by following its `belongs_to` edges | `WHERE fk IN (ids)` for a single hop; a chain of `JOIN`s for longer paths | | 3 | **Referenced-by (automatic reverse)** | No `belongs_to` path of its own, but **exactly one** already-constrained table points at it by foreign key | Constrained to the ids that referencer's own query selects | | 4 | **`reverse_scope` (declared reverse)** | Referenced by **many** scoped tables — typically a global-identity table like `users` — and the referencers are enumerated in its config | Constrained to the `UNION` of the enumerated referencers' ids | | 5 | **Scoped-parent cascade** | No path or referencer, but a `belongs_to` parent is itself scoped (by any path above) | Constrained to the parent's in-scope primary keys; cascades over multiple hops | | 6 | **Full dump** | Nothing relates the table to the target | All rows. In scope-column mode this **aborts** unless the table opts in with `scope_exempt: true` | How the paths behave and interact: 1. **Direct filter.** In the default single-target mode the target is anchored on its primary key (or a custom field via the mongodb-only `--ids-field`). In [scope-column mode](#scope-column-mode) there is no single anchor: every table that declares a `scope_column` is filtered on that column directly. 2. **`belongs_to` join walk** — the "normal join" path. exwiw BFS-walks `belongs_to` edges to the nearest terminus (the target table, or a directly scoped table in scope-column mode) and compiles the shortest path into `INNER JOIN`s. A [polymorphic `belongs_to`](#polymorphic-belongs_to) hop additionally pins the type column; in scope-column mode a polymorphic hop is resolved for **every** concrete arm and the arms are `UNION`ed (see [Every arm is extracted](#every-arm-is-extracted-scope-column-mode)). 3. **Referenced-by** handles a table with no outgoing path that is pointed *at* by a constrained child — `active_storage_blobs`, referenced by `active_storage_attachments.blob_id`, is the canonical case (see [ActiveStorage](#activestorage-has_one_attached--has_many_attached)). It is automatic but deliberately narrow: it requires a single, non-polymorphic referencer. With two or more referencers it steps aside (path 6) unless you declare `reverse_scope`. 4. **[`reverse_scope`](#reverse-scope-for-multi-referencer-tables-reverse_scope)** is the declared, multi-referencer form of path 3: the config enumerates which referencers' (already scoped) queries feed the id set. Unscoped arms are skipped with a warning rather than widening the dump. 5. **Scoped-parent cascade** rescues satellites: a table whose only link is a `belongs_to` toward a hub that is itself scoped (e.g. via referenced-by or `reverse_scope`) is constrained to that parent's in-scope ids. The cascade recurses hop by hop (each level requires a single unambiguous scopable parent) and stops on `belongs_to` cycles. 6. **Full dump** is the fallback for a genuinely unrelated table — intended for reference/master data. Single-target mode dumps it in full (with a warning when an ambiguous cascade was the reason); scope-column mode refuses to run instead, unless the table is explicitly marked [`scope_exempt: true`](#scope_exempt-intentional-full-dump) (Rails-managed tables are exempt automatically). Paths 3–5 all materialize their id set once and probe it via a `JOIN` on a `SELECT DISTINCT` derived table rather than `IN (subquery)` — see [Why a JOIN, not `IN (subquery)`](#why-a-join-not-in-subquery). Scope-column mode classifies every table up front with these same paths (`:direct` / `:via_path` / `:referenced_by` / `:via_scoped_parent` / `:exempt` / `:unscopable` in `QueryAstBuilder#scope_category`) and aborts before extracting anything if any table lands on `:unscopable`. The MongoDB adapter follows the same model, except id sets are captured at runtime while parent collections stream instead of being expressed as SQL subqueries — see [MongoDB support](docs/mongodb.md). ### Scope-column mode The default `--target-table` extraction assumes the schema converges on a single root: every table is reached by walking `belongs_to` toward that one table. Some schemas are not shaped that way — many independent top-level tables each carry the *same* scope/tenant column (e.g. `tenant_id`, `business_entity_id`), and a foreign key that **cannot be joined** (most importantly a cross-database `belongs_to`, whose join is impossible but whose FK column is still filterable) is not reached at all. Choosing one table as `--target-table` would leave the others unrelated to it, and an unrelated table is dumped in full — a problem if it holds personal data. Scope-column mode handles this shape: instead of anchoring on one table's primary key, **every table is filtered by a shared column** whose values are `--ids`. Declare that column per table in the schema config with `scope_column:`: ```json { "name": "shops", "primary_key": "id", "scope_column": "business_entity_id", "columns": [{ "name": "id" }, { "name": "name" }, { "name": "business_entity_id" }] } ``` Then name any scoped table as `--target-table` and pass the scope values as `--ids`: ```bash exwiw \ --adapter=postgresql \ --host=localhost --port=5432 --user=reader \ --database=app_production \ --schema-dir=exwiw/schema \ --target-table=shops --ids=42,43 \ --output-dir=dump ``` Because `shops` declares a `scope_column`, exwiw switches to scope-column mode: the `--ids` (`42,43`) are **`business_entity_id` values, not shop primary keys**, and `shops` itself is scoped by `business_entity_id IN (42,43)` like every other scoped table — it is *not* used as a primary-key anchor. (A table that declares a `scope_column` therefore can no longer be single-extracted by primary key.) Each table is resolved as follows: - **Declares the scope column** (`scope_column:`, or carries the global column of the deprecated `--scope-column` flag) → `WHERE scope_column IN (ids)`. - **Does not, but `belongs_to` reaches a table that does** → exwiw joins up to the nearest such table and applies the scope filter there (the same join machinery the single-target mode uses). - **`belongs_to` a parent that is itself scoped but carries no scope column of its own** → exwiw constrains this table to the parent's in-scope ids by joining it to the parent's scoped query, materialized as a derived table (`JOIN (SELECT DISTINCT parent.pk … FROM ) … ON fk = …`). This covers a *hub* table that has no scope column and is scoped only because an extractable child references it (see referenced-by below): the hub's other `belongs_to` children ride along to just the in-scope rows instead of being dumped in full. The parent itself may be scoped the same way, so this **cascades across multiple hops** (each a single unambiguous scopable parent) and the derived-table JOINs nest correspondingly; the recursion terminates on a genuine `belongs_to` cycle (a table already on the path is left `:unscopable` rather than looped on). (See [Why a JOIN, not `IN (subquery)`](#why-a-join-not-in-subquery) for the materialization rationale.) - **Cannot be scoped at all** (no scope column and no path to one) → exwiw **aborts** and lists the offending tables, so an unscoped table is never silently dumped in full. For each, either declare a `scope_column`, add a `belongs_to` path, set `ignore: true` to skip it, or mark it `scope_exempt: true` (below) to export it in full. > **Note — referenced-by is preferred over the hub cascade.** A table that is > *both* `belongs_to` a scoped hub *and* referenced-by a constrained child is > scoped to the (narrower) referenced-by id-set, not the hub cascade, so the hub's > other children the child does not reference are dropped (under-scoping). To force > the broader hub cascade, set `ignore: true` on the child's `belongs_to` edge that > points at this table. Scope-column mode is SQL-only (mysql / postgresql / sqlite). It works with `exwiw explain` too, which is the recommended way to preview the queries before exporting. #### Cross-database foreign keys The motivating case for declaring a `scope_column` is a foreign key that cannot be joined: when a `belongs_to` target lives in a different database (see the cross-database `belongs_to` note under the generator), that join is impossible, but the foreign-key *column* is still present and can be filtered directly. Declaring `scope_column: ""` on the owning table scopes it by the column value, with no join — `schema:generate` points this out in the ignored relation's `comment`. #### `scope_exempt` (intentional full dump) A genuine reference/master table (no personal data) that has no scope linkage can opt out of the strict check and be exported in full: ```json { "name": "countries", "primary_key": "id", "scope_exempt": true, "columns": [{ "name": "id" }, { "name": "code" }] } ``` Rails-managed tables (`schema_migrations`, `ar_internal_metadata`) are treated as exempt automatically. #### Per-table `scope_column` and the value space Scope-column mode assumes a single shared **value** space — the same `--ids` apply to every scoped table. Each table names its own column, so a table that stores that same value under a differently named column simply declares that name: ```json { "name": "legacy_orders", "primary_key": "id", "scope_column": "legacy_tenant_id", "columns": [{ "name": "id" }, { "name": "legacy_tenant_id" }] } ``` Both `scope_exempt` and `scope_column` are user-maintained and preserved across `schema:generate` regeneration (the generators never emit them). #### Deprecated: the `--scope-column` flag Before per-table declarations, scope-column mode was selected with a global `--scope-column=COLUMN` flag (every table filtered by that one column, `--ids` its values, no `--target-table`). The flag still works — SQL-only and mutually exclusive with `--target-table` — but is **deprecated** and emits a warning; prefer declaring a per-table `scope_column` and running with `--target-table`. A per-table `scope_column` takes precedence over the flag for any table that sets both. ### Config file (`exwiw.yml`) Options you would otherwise repeat on every run can be kept in a YAML config file. Pass it with `--config=PATH`; when `--config` is omitted, exwiw automatically loads `exwiw.yml` (or `exwiw.yaml`) from the current directory if present. **Options passed on the CLI always take precedence over the config file** — the config only fills in options you did not pass. This lets you commit the stable settings (which schema to read, output format, ...) while still varying the environment-specific connection details per invocation. ```yaml # exwiw.yml — keep at the project root, alongside exwiw/schema/ adapter: postgresql schema_dir: exwiw/schema output_dir: dump output_format: insert # insert | copy insert_only: false after_insert_hook: hooks/seed.rb log_level: info # debug | info # target_table / ids / ids_field / scope_column may also be set here # mongodb_query_timeout_ms: 30000 # global query timeout (mongodb only) ``` With the file above, only the connection details need to be supplied on the CLI: ```bash DATABASE_PASSWORD=... exwiw \ --host=localhost --port=5432 --user=reader --database=app_production \ --target-table=shops --ids=1 ``` Notes: - **Database connection settings stay on the CLI/environment.** `host`, `port`, `user`, `database`, `uri`, and `password` are **rejected** in the config file (exwiw exits with an error). `adapter` is the one connection-related key that *is* allowed in the file. - **Relative paths in the config (`schema_dir`, `output_dir`, `after_insert_hook`) are resolved relative to the config file's own directory**, not the current working directory. So with the config at the project root, `schema_dir: exwiw/schema` reads naturally, and an absolute `--config=/path/to/exwiw.yml` works no matter where you run from. (CLI path flags remain relative to the current directory — each source resolves relative to where it is written.) Absolute paths are used as-is. - Unknown keys are rejected so a typo surfaces immediately. - Export-only keys (`output_dir`, `output_format`, `insert_only`, `after_insert_hook`) are ignored when running `explain`, so a single config file can be shared by both subcommands. - `explain_verbosity` sets the mongodb `explain` verbosity (`queryPlanner` | `executionStats` | `allPlansExecution`, default `queryPlanner`); the `EXWIW_MONGODB_EXPLAIN_VERBOSITY` env var overrides it. Ignored by the SQL adapters and by `export`. See [MongoDB support](docs/mongodb.md#exwiw-explain-verbosity). - `mongodb_query_timeout_ms` sets the global, server-enforced query timeout (mongodb only); the `--mongodb-query-timeout-ms` CLI flag overrides it. Ignored by the SQL adapters. See [MongoDB support](docs/mongodb.md). ### Generator The config generator is provided as a Rake task. ```bash # generate table schema under exwiw/schema/ bundle exec rake exwiw:schema:generate ``` The output directory is resolved in this order: 1. the `EXWIW_SCHEMA_DIR_PATH` environment variable, if set; 2. otherwise `schema_dir` from the config file (`exwiw.yml` / `exwiw.yaml` in the current directory), so the generator and the `exwiw` CLI share one location without repeating the path; 3. otherwise the `exwiw/schema` default. ```sh EXWIW_SCHEMA_DIR_PATH=custom_directory bundle exec rake exwiw:schema:generate ``` As with the CLI, a relative `schema_dir` in the config file is resolved relative to the config file's own directory. #### Safe mode (masking new columns by default) A migration that adds a column would otherwise leave `schema:generate` emitting it unmasked, so it starts being exported the moment the config is regenerated — before anyone has judged whether it holds personal data. So `schema:generate` runs in **safe mode by default**: every column the config does not have yet is emitted **masked** and flagged [`needs_mask_decision: true`](#needs_mask_decision). Columns already in the config keep whatever they say — the merge that preserves `replace_with` / `comment` / `ignore` preserves a resolved decision too — so in practice this marks exactly the columns a migration just added. ```bash bundle exec rake exwiw:schema:generate # safe mode EXWIW_NEW_COLUMNS=plain bundle exec rake exwiw:schema:generate # opt out ``` Opting out is for the **first-time bootstrap** of a config, where every column of every table is new and safe mode would flag the whole thing at once. Use it nowhere else: a column committed under `plain` carries no flag, so nothing afterwards can tell it apart from one whose masking was decided. A column that has a **default of its own** is masked with that default: it is a value the column provably holds, and it is what the application treats as neutral, so masking a `default: true` flag does not quietly turn the feature off for every row in the dump. A default the database computes (`now()`) is not a constant and does not count, and neither does a JSON object — `{...}` in a mask is a column placeholder, so those fall back to `{}`. Otherwise the mask depends on the column type: `masked-{primary key}` for text (with `@example.com` appended when the column name mentions mail, so it stays a valid address), `0` for numbers, `false` for booleans, a fixed date/timestamp, and `{}` for JSON. Text always takes the template rather than its default, since the mask has to vary per row. Three kinds of column are flagged but deliberately **not** masked: - **The primary key, and the foreign keys/types the `belongs_tos` join on.** Masking them would break the joins and leave the dump referencing rows that were never exported. - **Types no constant safely fits** — `uuid`, `binary`, enums, array columns (which report their member type, so a scalar default would not fit), and text columns too short to hold the masked value. An invalid default would fail the restore the dump feeds, which is worse than exporting the column while the flag keeps the change from being merged. - **Columns covered by a unique index**, unless the mask varies per row (the text masks do, via the primary key). A constant would collapse every row onto one value and break the restore with a duplicate key. Safe mode is ActiveRecord-only for now: `schema:generate_mongoid` does not flag new fields yet, though the `needs_mask_decision` key itself is understood on a MongoDB field. #### Tidying stale config (`schema:tidy`) `schema:generate` adds and updates config files for the tables it finds, but it never deletes the config file of a table that has been dropped from the application. To reconcile the existing config against the current schema, run: ```bash bundle exec rake exwiw:schema:tidy ``` `schema:tidy` compares the config files already on disk with the **live database** (read through the database connection, not the models) and removes only what no longer exists there: - a config file whose table has been dropped from the database is **deleted**, and - columns recorded in a surviving table's config that the table no longer has are **dropped** from that file. Because it reads the database directly, a table that still exists in the database but has lost (or never had) an ActiveRecord model is **kept** — only a table that is genuinely gone is removed. (This is the deliberate counterpart to `generate`, which is model-driven and only ever adds what the models know about.) It respects `EXWIW_SCHEMA_DIR_PATH` and the per-database subdirectory layout in the same way as `schema:generate`. Unlike `generate`, `tidy` never adds or regenerates entries — every surviving table/column (including hand-edited `comment` / `ignore` / `replace_with`) is left untouched, so it is safe to run on a customized config. The task prints which tables and columns it removed (or that the config was already tidy). Stale `belongs_tos` are not pruned by `tidy`; rerun `schema:generate` to refresh those. #### Checking the config against the schema `schema:check` reports how the committed config differs from what the application would generate now — without writing anything, so it can run on a working tree it must not modify: ```bash bundle exec rake exwiw:schema:check ``` It regenerates into a throwaway copy of the config directory (safe mode + `tidy`) and prints the comparison as JSON, then exits non-zero when anything needs attention: ```json { "added_tables": [], "added_columns": ["users.contact_email"], "removed_tables": [], "removed_columns": [], "changed_tables": ["users"], "needs_mask_decision": ["orders.memo"] } ``` `added_*` / `removed_*` / `changed_tables` mean the config no longer matches the schema — run `schema:generate` and `schema:tidy` to reconcile it. `needs_mask_decision` lists the columns whose masking nobody has decided on yet (see [the flag](#needs_mask_decision)). The exit code makes it usable as a CI check that keeps a schema change from being merged until both are resolved; the JSON is stable and sorted, so it can be posted as-is. In a multi-database app each entry is prefixed with its database (`primary/users.email`), so the same table name in two databases stays distinct. Set `EXWIW_SCHEMA_CHECK_OUTPUT=` to have the same JSON written to a file, which spares a caller from assuming stdout carries nothing else (application boot is free to print). Like safe mode, this is ActiveRecord-only — it regenerates through `SchemaGenerator`, so a Mongoid config directory is not supported yet. #### Multiple databases If the application uses Rails' multiple-database support (`connects_to`), `schema:generate` buckets models by the database they connect to and writes each database's config files into its own subdirectory of the output directory, named after the database config name (`primary`, `analytics`, ...): ``` exwiw/schema/ primary/ shops.json users.json schema_migrations.json analytics/ analytics_events.json ``` Each database keeps its own Rails migration history, so a `schema_migrations` (and `ar_internal_metadata`) entry is emitted under every database that contains one — the example above shows `primary/schema_migrations.json` and would also produce `analytics/schema_migrations.json` when the analytics database has its own migration table. Single-database applications are unaffected and continue to write files flat into the output directory. A `belongs_to` whose target model lives in a *different* database (e.g. a `primary` model referencing an `analytics` one) cannot be joined: each database is exported on its own connection and into its own subdirectory, so the target table is absent from the directory this config is loaded with. `schema:generate` detects such a relation (by comparing the owning and target models' database config names) and emits it with `ignore: true` and `ignore_type: "cross_database"`, recording why in the `comment`; the relation is then dropped from extraction at load time, while the foreign-key column itself is still exported as a plain column. Polymorphic associations are handled per target, so only the targets that cross a database boundary are ignored. The task also prints a summary of every cross-database `belongs_to` it ignored. **To extract across such a boundary, declare `scope_column: ""` on the owning table (see [scope-column mode](#scope-column-mode)) so its rows are filtered by the foreign-key value directly** — there is no join, so the cross-database boundary is not a problem there. **Limitations** - The rails-managed table *names* are resolved from the global `ActiveRecord::Base.schema_migrations_table_name` / `internal_metadata_table_name` accessors, which are shared across all connections. A per-database override of these names is not detected, so such a table will be missing from that database's generated configs. #### Mongoid applications For MongoDB applications backed by [Mongoid](https://www.mongodb.com/docs/mongoid/), a separate rake task introspects Mongoid document models and emits `MongodbCollectionConfig` files: ```bash bundle exec rake exwiw:schema:generate_mongoid ``` What it derives from each model (fields, `belongs_tos`, `embedded_in`, STI handling), how to annotate constructs exwiw cannot represent with `ignore` / `ignore_type`, and the `EXWIW_SKIP_UNSUPPORTED=1` bootstrap flag are all documented in [MongoDB support](docs/mongodb.md#generating-config-from-mongoid-models). ### Configuration This is an example of the one table schema: ```json { "name": "users", "primary_key": "id", "filter": "users.id > 0", "bulk_insert_chunk_size": 1000, "belongs_tos": [{ "table_name": "companies", "foreign_key": "company_id" }], "columns": [{ "name": "id" }, { "name": "email", "replace_with": "user{id}@example.com" }, { "name": "company_id" }] } ``` `--schema-dir` will use all json files in the specified directory. #### Unknown keys are rejected Loading a table/collection config with a key that no declared attribute accepts is an **error** (`Exwiw::UnknownConfigKeyError`, an `ArgumentError` subclass) naming the key, the table/collection, the offending file, and the allowed keys. This also applies to the nested `belongs_tos` / `columns` / `fields` / `reverse_scope` / `embedded_in` / `replace_with_fake_data` entries. Previously such keys were silently dropped, which turned a typo (`reverse_scop`) — or a key another adapter supports but this one does not (e.g. `raw_sql` on a MongoDB field) — into a silent no-op: the config loaded, the dump ran, and the requested masking/scoping simply never happened. For free-form annotations, use the `comment` key — it is a declared, documentation-only attribute on table/collection configs and on their `belongs_tos` / `columns` / `fields` entries, so it always passes (see [Ignore / annotate a column or `belongs_to`](#ignore--annotate-a-column-or-belongs_to)). ### Output format By default, exwiw generates `INSERT` statements. For PostgreSQL, you can pass `--output-format=copy` to generate `COPY FROM stdin` format instead, which is significantly faster for bulk loading. The generated file uses tab-separated values with PostgreSQL's text-format escaping (`\N` for NULL, `\\` for backslash, etc.). Import with `psql`: ```bash psql -d app_dev -f dump/insert-001-shops.sql ``` `--output-format=copy` is only supported with the `postgresql` adapter. ### Skip DELETE SQL output By default, exwiw generates `delete-*.sql` files alongside the `insert-*.sql` files so that an existing dataset can be cleared before re-inserting. Pass `--insert-only` when you only need the insert files. ### After-insert hook `--after-insert-hook=PATH` runs a post-processing hook **after** all per-table insert/delete files have been written. The hook can be either a Ruby file (`.rb`) or any executable script (e.g. `.sh`). **Ruby hook (`.rb`)**: provides a tiny DSL with these builtins: - `cli_options` — Hash of all parsed CLI options (e.g. `cli_options.fetch(:ids)` returns the `--ids` array). - `insert_sql(template)` — appends an ERB-rendered string to a buffer. After the hook finishes, the buffer is concatenated and written to `insert-{N+1}-after_insert.{ext}` where `{N+1}` is one past the last per-table insert file. For the MongoDB adapter the equivalent alias `insert_jsonl(template)` is available; output goes to `insert-{N+1}-after_insert.jsonl`. Multiple `insert_sql` calls in a single hook are joined with `"\n"` into the same file. If no `insert_sql` call is made, no file is created. - `insert_jsonl(collection, template)` — **MongoDB adapter only**. SQL statements name their table in-band, but JSONL documents do not — the import convention derives the target collection from the filename — so the two-argument form writes the ERB-rendered extended-JSON lines to the named collection's own `insert-NNN-.jsonl` file, importable with the same `mongoimport --collection ` convention as the per-collection dump files. Multiple calls targeting the same collection are appended (joined with `"\n"`) into that collection's file; distinct collections get one file each, numbered sequentially after the last per-collection dump file (the collection-less `after_insert` buffer, when also used, keeps `{N+1}` and the collection files follow it). Calling this form with a SQL adapter raises an error. Example `hooks/seed_default_users.rb`: ```ruby insert_sql <<~SQL -- seed default users for tenants <%= cli_options.fetch(:ids).join(',') %> <%- cli_options.fetch(:ids).each do |tenant_id| -%> INSERT INTO users (tenant_id, email) VALUES (<%= tenant_id %>, 'default@example.com'); <%- end -%> SQL ``` MongoDB example seeding two collections (`insert-{N+1}-users.jsonl` and `insert-{N+2}-posts.jsonl`): ```ruby insert_jsonl 'users', <<~JSONL <%- cli_options.fetch(:ids).each do |shop_id| -%> {"shop_id":{"$oid":"<%= shop_id %>"},"email":"default@example.com"} <%- end -%> JSONL insert_jsonl 'posts', '{"title":"welcome"}' ``` **Shell hook**: anything other than `.rb` is exec'd as a child process. It is a pure side-effect hook — exwiw does not capture its stdout. The hook receives these env vars and inherits `DATABASE_PASSWORD` from the parent: - `EXWIW_OUTPUT_DIR`, `EXWIW_SCHEMA_DIR` - `EXWIW_DATABASE_ADAPTER`, `EXWIW_DATABASE_HOST`, `EXWIW_DATABASE_PORT`, `EXWIW_DATABASE_USER`, `EXWIW_DATABASE_NAME` - `EXWIW_TARGET_TABLE`, `EXWIW_IDS` (comma-separated), `EXWIW_OUTPUT_FORMAT` A non-zero exit code from the shell hook aborts exwiw. Note: Ruby hooks are evaluated via `instance_eval` inside the exwiw process — only pass paths you trust. ### Ignore a table Set `"ignore": true` on a table's config JSON to exclude it from data extraction. The table's DDL is still emitted into `insert-000-schema.{sql,js}` so the schema stays consistent, but no `insert-*` / `delete-*` files are generated for it and the table is never queried. ```json { "name": "audit_logs", "primary_key": "id", "ignore": true, "belongs_tos": [], "columns": [{ "name": "id" }] } ``` Constraints: - If another non-ignored table has a `belongs_to` entry pointing at an ignored table, exwiw raises `ArgumentError` on load. Remove the `belongs_to` entry on the referencing table, or unset `ignore` on the referenced table. - Specifying an ignored table as `--target-table` raises `ArgumentError`. - `ignore: true` is preserved by `exwiw:schema:generate` regenerations (the receiver value wins over the auto-generated config). ### Ignore / annotate a column or `belongs_to` Individual `columns` (SQL) / `fields` (MongoDB) and `belongs_tos` entries accept two optional, **user-owned** keys: - `comment` — a free-form note. Purely informational; exwiw never reads it. - `ignore: true` — drops that entry from extraction. An ignored column/field is excluded from the `SELECT` and the generated `INSERT` (the column still exists in the target schema, since the DDL comes from the source database — exwiw just does not copy its data). An ignored `belongs_to` is removed from dependency ordering and query building, so the relation is not traversed. ```json { "name": "users", "primary_key": "id", "belongs_tos": [ { "table_name": "companies", "foreign_key": "company_id" }, { "table_name": "audit_logs", "foreign_key": "log_id", "ignore": true, "comment": "huge table, not needed for this export" } ], "columns": [ { "name": "id" }, { "name": "secret_token", "ignore": true, "comment": "do not copy credentials" } ] } ``` The ignored entries are removed only at runtime, right after the config is loaded from file; the JSON on disk keeps them. Both `comment` and `ignore` are **preserved across `exwiw:schema:generate` / `exwiw:mongoid:schema:generate` regenerations** (the hand-edited value wins over the auto-generated config), just like `replace_with`. This applies to the MongoDB `MongodbCollectionConfig` (`fields` / `belongs_tos`) as well. ### `needs_mask_decision` A column/field may also carry `needs_mask_decision: true`, marking a column whose masking nobody has decided on yet: ```json { "name": "contact_email", "replace_with": "masked-{id}@example.com", "needs_mask_decision": true } ``` Extraction ignores the key entirely — what the column exports is whatever `replace_with` / `ignore` say. It exists so the decision can be tracked and required: `schema:generate`'s [safe mode](#safe-mode-masking-new-columns-by-default) attaches it to every newly discovered column together with a default mask, and [`schema:check`](#checking-the-config-against-the-schema) reports the columns that still carry it, so CI can keep a pull request red until each one is resolved. Resolving it means removing the key — after keeping the mask (ideally recording why in `comment`), replacing it with a real masking rule, dropping `replace_with` to export the raw value, or setting `ignore: true`. Like `comment` / `ignore`, the on-disk state wins over regeneration: once removed, `schema:generate` does not bring it back. ### Polymorphic `belongs_to` A Rails polymorphic association (`belongs_to :reviewable, polymorphic: true`) does not point at a single table — the target row is selected at runtime by a type column. exwiw models this as **one `belongs_to` entry per concrete target table**, each carrying two extra fields: - `foreign_type` — the type column on *this* table (e.g. `reviewable_type`). - `type_value` — the value stored in that column for this target (e.g. `"Product"`), i.e. the target model's `polymorphic_name`. ```json { "name": "reviews", "primary_key": "id", "belongs_tos": [ { "table_name": "products", "foreign_key": "reviewable_id", "foreign_type": "reviewable_type", "type_value": "Product" }, { "table_name": "shops", "foreign_key": "reviewable_id", "foreign_type": "reviewable_type", "type_value": "Shop" } ], "columns": [{ "name": "id" }, { "name": "reviewable_type" }, { "name": "reviewable_id" }] } ``` `exwiw:schema:generate` expands a polymorphic `belongs_to` automatically: it finds every model that registers the association as a target via `has_many` / `has_one ..., as: :reviewable` and emits one entry per target table (ordered by table name so the output is stable across Ruby versions). A plain (non-polymorphic) `belongs_to` simply omits `foreign_type` / `type_value`. At dump time, when a polymorphic `belongs_to` lies on the path to the dump target, exwiw constrains **both** the foreign key and the type column, so only rows of the matching type are extracted. For example, dumping `products` pulls only reviews whose `reviewable_type = 'Product'`: ```sql SELECT reviews.* FROM reviews WHERE reviews.reviewable_id IN (/* products subquery */) AND reviews.reviewable_type = 'Product' ``` The same type filter is applied on the join path — and in the matching `delete-*.sql` bulk-delete subquery — when the polymorphic table is an intermediate hop rather than the directly-dumped table. #### Every arm is extracted (scope-column mode) A polymorphic `belongs_to` is several `belongs_to` entries — one per concrete target — that a row selects between via its type column. A single JOIN can only follow **one** of them, so a join table reached through such a hop would come out holding only the rows of that one `type_value`. In [scope-column mode](#scope-column-mode) exwiw therefore resolves **every** arm of the group and constrains the table to the union of the ids the arms keep: ```sql SELECT comments.* FROM comments JOIN ( SELECT DISTINCT exwiw_scope_src_0.id AS exwiw_scope_id FROM ( SELECT comments.id FROM comments JOIN posts ON comments.commentable_id = posts.id AND comments.commentable_type = 'Post' JOIN shops ON posts.shop_id = shops.id AND shops.tenant_id = 't1' UNION SELECT comments.id FROM comments JOIN pages ON comments.commentable_id = pages.id AND comments.commentable_type = 'Page' JOIN shops ON pages.shop_id = shops.id AND shops.tenant_id = 't1' ) AS exwiw_scope_src_0 ) AS exwiw_scope_ids_0 ON comments.id = exwiw_scope_ids_0.exwiw_scope_id ``` `UNION`, not `OR`, because each arm joins a *different* table: OR-ing them in one `WHERE` would need outer joins, whereas each arm is a self-contained query of exactly the shape a single-arm table already produces. It rides on the existing scope id-set machinery, so the id set is materialized once (see [Why a JOIN, not `IN (subquery)`](#why-a-join-not-in-subquery)) and, on mysql, into a session `TEMPORARY TABLE`. Notes: - **Only arms that reach the scope are included.** An arm whose target has no scope of its own is dropped, never widened — an unscoped arm would pull in every tenant's rows. An arm marked `"ignore": true` is dropped as usual, before any of this. - An arm's target does not need a `belongs_to` path to a scoped table: if it is scoped by other means (referenced-by, [`reverse_scope`](#reverse-scope-for-multi-referencer-tables-reverse_scope), or the parent cascade) the arm probes that query's ids instead, still pinned by the type column. - An arm whose target is scoped **through this same table** (e.g. `active_storage_blobs`, narrowed by referenced-by from `active_storage_attachments`, appearing as an `ActiveStorage::Blob` arm of those same attachments) is dropped: adopting it would make the two tables scope each other and leave the referenced table short of rows the join table kept — a dangling foreign key on import. - Nothing changes when there is a single arm, or when the walk leaves through a non-polymorphic `belongs_to`: the plain single-JOIN SQL is emitted, byte for byte as before. - This applies to the scope-column mode walk. The single `--target-table` mode still follows one path per table. ### ActiveStorage (`has_one_attached` / `has_many_attached`) ActiveStorage is handled automatically — no ActiveStorage-specific configuration is required. The `has_one_attached` / `has_many_attached` macros don't add a column to the owning model; they generate ordinary associations that exwiw already understands: - **`active_storage_attachments`** is the polymorphic join row (`belongs_to :record, polymorphic: true` + `belongs_to :blob`). `exwiw:schema:generate` expands the polymorphic `record` into one `belongs_to` per model that declared `has_*_attached` (found via the generated `has_* ..., as: :record` reflections), exactly like any other [polymorphic `belongs_to`](#polymorphic-belongs_to). So only the attachments whose owner is among the dumped rows are extracted. In scope-column mode every owner type that reaches the scope is extracted (see [Every arm is extracted](#every-arm-is-extracted-scope-column-mode)); before that, only the single owner type the walk happened to settle on came out. - **`active_storage_blobs`** has no `belongs_to` of its own (attachments point *at* it), so it has no path to the dump target. exwiw narrows it via **reverse / "referenced_by" extraction**: a parent table referenced by exactly one constrained, non-polymorphic child is constrained to just the referenced ids instead of dumping every row. The id set is materialized once and joined back (see [Why a JOIN, not `IN (subquery)`](#why-a-join-not-in-subquery)): ```sql SELECT active_storage_blobs.* FROM active_storage_blobs JOIN ( SELECT DISTINCT exwiw_scope_src_0.blob_id AS exwiw_scope_id FROM ( SELECT active_storage_attachments.blob_id FROM active_storage_attachments WHERE active_storage_attachments.record_id IN (/* owner subquery */) AND active_storage_attachments.record_type = '...' ) AS exwiw_scope_src_0 ) AS exwiw_scope_ids_0 ON active_storage_blobs.id = exwiw_scope_ids_0.exwiw_scope_id ``` `active_storage_variant_records` also references blobs, but since it has no path of its own to the dump target it doesn't constrain anything and is ignored as a referencer — blobs stays narrowed to the attachment-referenced ids. (A parent referenced by *multiple* constrained children currently falls back to dumping all of its rows.) - **`active_storage_variant_records`** holds derivative variant-tracking rows that ActiveStorage regenerates lazily, and it too has no path to the dump target — left alone it would land in the "no relation → dump all" branch and, worse, its `blob_id` could point at blobs outside the narrowed set above (a foreign-key violation on import). `exwiw:schema:generate` therefore emits it with **`ignore: true`** (and drops it from the attachments `record` polymorphic expansion so nothing carries a dangling reference to it), so its data is skipped while the DDL is still written. Remove `ignore` from the generated config if you really need to export it. ### Reverse scope for multi-referencer tables (`reverse_scope`) The automatic reverse extraction above narrows a table referenced by **exactly one** constrained child. A table referenced by **two or more** constrained children falls back to dumping every row — fine for `active_storage_blobs`, but a problem for a **global-identity table** such as `users`: it carries no scope/tenant column and has no `belongs_to` of its own, yet dozens of scoped tables point *at* it. Dumping it (and everything that hangs off it) in full pulls in every tenant's identities. `reverse_scope` opts such a table into **multi-referencer** reverse scoping: you enumerate the referencers whose own (already scoped) extraction queries should be `UNION`'d into the id set the table is constrained to. It is a user-owned key (never emitted by `schema:generate`, preserved across regeneration like `scope_exempt`/`scope_column`): ```json { "name": "users", "primary_key": "id", "reverse_scope": { "via": [ { "table": "customers", "column": "user_id" }, { "table": "staff", "column": "user_id" }, { "table": "business_entity_customers", "column": "kantan_yoyaku_user_id" } ] }, "columns": [{ "name": "id" }, { "name": "name" }] } ``` produces (each arm reuses that referencer's own scope, so a per-tenant run keeps only that tenant's ids; the `UNION` id set is materialized once and joined back — see [Why a JOIN, not `IN (subquery)`](#why-a-join-not-in-subquery)): ```sql SELECT users.* FROM users JOIN ( SELECT DISTINCT exwiw_scope_src_0.user_id AS exwiw_scope_id FROM ( SELECT customers.user_id FROM customers WHERE AND customers.user_id IS NOT NULL UNION SELECT staff.user_id FROM staff WHERE AND staff.user_id IS NOT NULL UNION SELECT business_entity_customers.kantan_yoyaku_user_id FROM business_entity_customers WHERE <…' scope> AND business_entity_customers.kantan_yoyaku_user_id IS NOT NULL ) AS exwiw_scope_src_0 ) AS exwiw_scope_ids_0 ON users.id = exwiw_scope_ids_0.exwiw_scope_id ``` Notes: - **`column` is explicit**, so a *non-default* foreign key (e.g. `kantan_yoyaku_user_id`, or `organization_admins.id` which itself references `users.id`) is honored, and even a column with no declared `belongs_to` edge can be enumerated. - **Only scoped referencers belong in `via`.** Each arm's query must come out constrained; an unconstrained referencer (e.g. a `scope_exempt` table, or one with no path to a scope) would project *every* id and union the whole table back — so such an arm is **skipped with a warning** rather than silently widening the dump. An unknown table is likewise skipped with a warning. If no arm survives, the table stays unscopable and (in [scope-column mode](#scope-column-mode)) the run aborts via `validate_scope!`. - **NULLs are excluded** per arm (`IS NOT NULL`). - **Satellites need no config.** A table that `belongs_to` the reverse-scoped table (e.g. `end_users.id → users.id`, or `identities.user_id → users.id`) tightens to the kept ids automatically through the normal cascade — only the reverse-scoped table itself declares `reverse_scope`. The cascade is **multi-hop**, so a table several `belongs_to` hops below the reverse-scoped table (e.g. `end_user_profiles → end_users → users`) also tightens automatically, with no config of its own. - Works in both single-target and scope-column mode. In single-target mode there is no scope-column pre-flight (`validate_scope!`), so a satellite the cascade cannot resolve to a single scopable parent (e.g. it `belongs_to` two scopable hubs) is dumped in full with a warning rather than aborting. Polymorphic foreign keys are not eligible as anchors (the named `column` is always a concrete column). - **The MongoDB adapter supports `reverse_scope` too** — same config shape and semantics, but the id set is captured at runtime instead of being emitted as a `UNION` subquery. See [`reverse_scope` on collections](docs/mongodb.md#reverse_scope-on-collections) under MongoDB support. ### Why a JOIN, not `IN (subquery)` Every scope id-set above — the multi-referencer `reverse_scope` `UNION`, the single-referencer reverse extraction, and the multi-hop forward cascade — is emitted as a `JOIN` to a `SELECT DISTINCT` derived table rather than ` IN ()`: ```sql … JOIN (SELECT DISTINCT src. AS exwiw_scope_id FROM () AS src) AS ids ON .= ids.exwiw_scope_id ``` Both forms select the **same rows** — the `DISTINCT` dedups, so the join never fans out — but the query plans differ sharply on a large table. As `IN (… UNION …)`, MySQL cannot turn a `UNION` subquery into a materialized semi-join and falls back to its IN-to-`EXISTS` rewrite: a **correlated `DEPENDENT SUBQUERY`** re-evaluated for every outer row, i.e. a full scan of the (potentially huge) outer table multiplied by the cost of the union. The derived-table form forces the engine to evaluate the id set **once** (the `DISTINCT` makes the derived table non-mergeable, hence materialized) and then probe the outer table by its primary key. On a global-identity table such as `users` this is the difference between a full table scan and an index lookup; the cascade nests the same way, so each level is materialized once instead of being re-evaluated by the level above. All three SQL adapters (mysql / postgresql / sqlite) emit this shape. PostgreSQL additionally reconciles a `uuid`/`varchar` type mismatch by casting the join key and the projected id to `text`, exactly as the old `IN` form did. ### Rails-managed tables (special `type` values) Some tables are owned by Rails itself rather than the application — they have no ActiveRecord model and Rails reserves the right to evolve their column shape between versions (e.g. `schema_migrations`, `ar_internal_metadata`). exwiw treats them as a distinct category via the `type` field on a table config: - `type: "rails_managed_schema_migrations"` — Rails' migration history table (`ActiveRecord::Base.schema_migrations_table_name`). - `type: "rails_managed_internal_metadata"` — Rails' internal metadata table (`ActiveRecord::Base.internal_metadata_table_name`). `exwiw:schema:generate` emits these entries automatically when the corresponding tables exist on the connection — they are NOT pulled from `ActiveRecord::Base.descendants` because they have no model class. A rails-managed entry has a minimal shape (no `primary_key`, no `belongs_tos`, no `columns`): ```json { "name": "schema_migrations", "type": "rails_managed_schema_migrations", "comment": "Managed internally by Rails. Tracks applied schema migrations." } ``` Behavior at dump time: - Extraction uses `SELECT *` so the dump is robust against Rails-side column additions. - `INSERT` statements omit the column list (`INSERT INTO schema_migrations VALUES (...)`). For PostgreSQL `--output-format=copy`, the `COPY` header similarly omits the column list (`COPY schema_migrations FROM stdin;`). - No `delete-*.sql` file is generated for rails-managed tables, to avoid wiping migration history on the import target. Constraints: - Defining `primary_key`, `columns`, or `belongs_tos` on a rails-managed entry is rejected with `ArgumentError` on load. - A rails-managed table cannot be used as `--target-table`. - In multi-database setups, the rails-managed entry is emitted under whichever database's connection actually contains the table (see [Multiple databases](#multiple-databases)). The table name itself is still derived from the global `ActiveRecord::Base.schema_migrations_table_name` / `internal_metadata_table_name` (prefix/suffix) accessors. ### Composite primary keys (unsupported) exwiw does not yet support tables with a composite primary key. When `exwiw:schema:generate` encounters a model whose `primary_key` is an array, it still emits a config entry so the table is not silently dropped, but marks it `ignore: true`, tags it `type: "unsupported_composite_primary_key"`, and records the key columns in a `comment`: ```json { "name": "composite_pk_records", "type": "unsupported_composite_primary_key", "ignore": true, "comment": "exwiw does not support composite primary keys (organization_id, location_id); data extraction is skipped.", "belongs_tos": [], "columns": [{ "name": "organization_id" }, { "name": "location_id" }, { "name": "name" }] } ``` Unlike rails-managed entries, `columns` and `belongs_tos` are retained so the entry is ready to wire up once composite-key support lands. The `type` is purely a marker — `ignore: true` is what actually excludes the table from extraction, so removing `ignore` (and supplying a workable `primary_key`) lets you opt the table back in manually. ### Bulk insert chunk size `bulk_insert_chunk_size` splits the generated `INSERT` statement into multiple statements, each containing at most the specified number of rows. This is useful when the number of records per table is large enough to hit limits like MySQL's `max_allowed_packet`. If omitted, the adapter default applies: 10,000 rows per statement for the SQL adapters (1,000 documents per chunk for MongoDB). Tables at or below the chunk size still produce a single `INSERT` statement. To force a single statement regardless of table size, set a value larger than the table's row count. ### Batched extraction (`batch_scope`) A scoped table is normally extracted with one query, whose scope filter sits on the table it joins up to: ```sql SELECT activities.* FROM activities JOIN customers ON activities.customer_id = customers.id AND customers.tenant_id IN ('t1') ``` That is index-driven while the scope keeps few `customers`. Past some number of them the planner's estimate of "probe the foreign-key index once per customer" exceeds its estimate of "scan the table once", and it switches to a **sequential scan of the whole table** — for a result set that is a small fraction of it. On a table of hundreds of millions of rows the scan then exceeds the server's `statement_timeout`, or simply runs for hours. Note that no `filter` on the extracted table fixes this: a predicate that reduces the *output* does not reduce the *work* once the plan is a scan (it may not even change the plan). `batch_scope` removes the choice instead of arguing with the estimate. It names the scoped table this one reaches — the **batch table** — and exwiw resolves that table's in-scope primary keys once, then extracts one `size`-sized slice of those ids at a time: ```json { "name": "activities", "primary_key": "id", "batch_scope": { "table": "customers", "size": 1000 }, "belongs_tos": [{ "table_name": "customers", "foreign_key": "customer_id" }], "columns": [{ "name": "id" }, { "name": "customer_id" }] } ``` Each batch runs with that slice's ids in place of the scope filter: ```sql SELECT activities.* FROM activities JOIN customers ON activities.customer_id = customers.id AND customers.id IN (/* 1000 ids */) ``` An explicit id list of that size is exactly estimated and selective, so the foreign-key index is unambiguously the cheapest plan for every batch, and total work is proportional to the rows the table actually keeps rather than to the table's size. - **The dumped rows are the same as the unbatched query's** (in batch-by-batch order). The slices partition the id set — every id is in exactly one batch — so no row is dropped or emitted twice. The ids are sorted (in exwiw, not with `ORDER BY` — the id-set query stays cheap on the source DB) before slicing, so batch composition, and the dump, is reproducible run to run. - **`size` defaults to 1000** ids per batch. - The batch table's ids come from **its own extraction query**, so it is narrowed by exactly the filter it would carry in the unbatched query. They are held in memory for the extraction: one scope's worth of primary keys, orders of magnitude smaller than the table being batched. - The batch table may be **any number of hops up** the path — a table two hops below it (`activity_orders → activities → customers`) names `customers` too, and the batch ids are applied where the path meets the scope, bounding the whole join chain. - A table that **carries the scope column itself** batches by naming itself; each batch then filters `WHERE IN ()` directly. Note that the id-set query is then the same scope predicate over the same table, so this shape only avoids the scan when the scope column is indexed (ideally index-only) — the join shape above is the one that genuinely removes the planner's choice. - `delete-*.sql` is unaffected (it is generated from the unbatched query). - `bulk_insert_chunk_size` is independent: batches are query boundaries, chunks are `INSERT` statement boundaries. - With `--output-format=copy`, batching bounds each query's cost but not memory: COPY builds the whole table's body in memory, so all batches' rows are resident at once. Use the default INSERT format (which streams) when the kept rows themselves are huge. **Supported shapes.** A batch key only splits an extraction correctly when *every* row the table keeps is selected through the batch table's scope filter — otherwise a route the batch key does not constrain would keep the same rows in every batch, and the dump would repeat them (a primary-key conflict on import). So `batch_scope` requires [scope-column mode](#scope-column-mode) and one of: - the table is **directly scoped** (`scope_column`) and names itself, or - the table reaches the scope through a **single `belongs_to` join path** (path 2 in [the six scoping paths](#how-each-table-is-narrowed--the-six-scoping-paths)) whose scoped terminus is the named table. Every other shape — polymorphic arm `UNION`s, `reverse_scope`, referenced-by, the parent cascade, `scope_exempt` (on the batched table *or* the batch table, whose id set would then not be scoped), and single `--target-table` mode — is **rejected with an explanation** rather than silently mis-sliced, before any output is written. (In single-target mode the extraction is already anchored on a caller-supplied id list, so batching it means running exwiw once per slice of `--ids`.) `exwiw explain` prints the id-set query and its `EXPLAIN` after a batched table's own query, since that query is the part of a batched export the table's query does not show. It cannot show a batch's literal id list — `explain` resolves no ids, because it executes no extraction SELECT. Like `scope_column` / `scope_exempt` / `reverse_scope`, `batch_scope` is user-maintained: never emitted by `schema:generate`, and preserved across regeneration. ### Filter Some case, you don't need full records related to target. e.g. dump user access logs only for the last year. `filter` is here for that. Be careful to use this option, as it will be: - injected as it is in table condition(e.g. WHERE on mysql), so you are recommended to clearify table name of column to avoid ambiguity. - injected to every where / join clause, so it affects to all tables depends on filterted target-table. it results to data inconsistency. - a way to reduce the rows returned, which is **not** necessarily a way to reduce the work: on a large table the engine may keep (or switch to) a full scan and evaluate the filter per row. See [batched extraction](#batched-extraction-batch_scope) when the goal is to bound how much of the table is read. ### Masking `exwiw` provides several options for masking value. #### `replace_with` It will replace the value with the specified string, and you can use the column name with `{}` to replace the value with the column value. For example, Let assume we have the record which id is 1, then "user{id}@example.com" will be replaced with "user1@example.com". `replace_with` **preserves NULL**: a source value that is `NULL` (or, for MongoDB, an absent field) is left as-is instead of being replaced by the masked literal, so the "not set" signal survives into the dump. Only true `NULL`/absent is preserved — an empty string is a real value and is still masked. Because of this you do not need to hand-write a `raw_sql` `CASE WHEN ... IS NOT NULL ...` to keep NULLs. A **non-String** value (number or boolean) is used verbatim instead of being rendered as a template, so a column that is not text keeps its type: ```jsonc { "name": "score", "replace_with": 0 } // integer column -> SELECT emits the literal 0 { "name": "active", "replace_with": false } // boolean column { "name": "email", "replace_with": "masked-{id}@example.com" } // template, as above ``` The SQL adapters emit it as a typed literal (not concatenated into text) and the MongoDB adapter assigns it as-is, so the field keeps its BSON type. NULL preservation applies to both forms. In the String form, a `{...}` placeholder must name a column: an empty brace pair (`{}`) names nothing, so it is emitted literally — which is what makes `"replace_with": "{}"` a usable empty-JSON mask, on every adapter. #### `raw_sql` It will used instead of the original value. For example, `"raw_sql": "CONCAT('user', shops.id, '@example.com')"` is equivalent to `"replace_with": "user{id}@example.com"`. This is useful when you want to transform with functions provided by the database. Notice that you are recommended to clearify table name of column to avoid ambiguity. If it used with `replace_with`, `replace_with` will be ignored. #### `map` The value is evaluated as Ruby code once (per table, at dump time), must yield a `Proc`, and the proc is called for every fetched row. Its return value replaces the column value in the dump: ```jsonc { "name": "email", "map": "proc { |r| 'user' + r['id'].to_s + '@example.com' }" } ``` which is equivalent to `"replace_with": "user{id}@example.com"`. - `r['column_name']` reads any column of the current row — the value as fetched from the database (i.e. after SQL-side masking such as another column's `replace_with`, before Ruby-side transforms). `r` is only valid inside the call; do not retain it. - Return a `String`, `Numeric`, or `nil`. Unlike `replace_with` there is **no automatic NULL preservation** — the proc receives `nil` and decides. - `map` is exclusive with the other masking keys on the same column (`raw_sql` / `replace_with` / `replace_with_fake_data`). - SQL adapters only. On the MongoDB adapter the key is rejected on load, like `raw_sql` (see [Unknown keys are rejected](#unknown-keys-are-rejected)). Because the transform runs in the exwiw process, it is invisible to `explain`. **Security note**: `map` executes arbitrary Ruby from the schema config. Treat config files with the same trust as your Gemfile — only load trusted configs. This is the most powerful option, but it runs per row in the exwiw process rather than in the database. The measured dispatch cost is small, though (~0.6–0.8µs/row plus whatever the proc body does — see [`docs/row-transform-masking-notes.md`](docs/row-transform-masking-notes.md)). Prefer `replace_with`/`raw_sql` when they can express the transform; reach for `map` when they cannot. #### `replace_with_fake_data` Replaces the value with realistic-looking fake data generated by the [faker](https://github.com/faker-ruby/faker) gem, picked **deterministically** from the value of a seed column — the same seed value always maps to the same fake value, across tables, runs, and adapters: ```jsonc { "name": "name", "replace_with_fake_data": { "seed": "users.id", "type": "human_name" } } ``` - `seed` names a column of the same table, bare (`"id"`) or table-qualified (`"users.id"`). The seed value is hashed (SHA-256, after `to_s` normalization, so sqlite's integer `123` and postgres/mysql's string `"123"` agree) and the hash picks the fake value. Use a stable identifier (integer or string primary key) as the seed; float/decimal/binary columns are discouraged because their text forms differ per adapter. A `NULL` seed value hashes `""` (still deterministic). - Like `replace_with`, it **preserves NULL** in the target column. - `locale` (optional) sets the locale used to build the candidate values, e.g. `{ "seed": "id", "type": "human_name", "locale": "ja" }` produces Japanese names. - Supported `type`s: | type | example output (en) | example output (`locale: ja`) | |------|----------------|----------------| | `human_name` | `Adrianna Kilback` | `山田 太郎` | | `first_name` | `Adrianna` | `太郎` | | `last_name` | `Kilback` | `山田` | | `human_name_kana` | — (ja only) | `ヤマダ タロウ` | | `first_name_kana` | — (ja only) | `タロウ` | | `last_name_kana` | — (ja only) | `ヤマダ` | | `phone_number` | `(555) 123-4567` | | | `address` | `282 Kevin Brook, Imogeneborough, CA 58517` | | | `company_name` | `Hirthe-Ritchie` | | | `email` | `cliff.fay.9d6b804eff5a3f57@example.com` | | | `username` | `cliff.fay_9d6b804eff5a3f57` | | - **Coherent identity across the name family.** The person-family types (`human_name`, `first_name`, `last_name` and their `*_kana` counterparts) all draw from a single shared pool of people per locale, keyed by the same seed — so for one seed value the last name, first name, full name, and every kana reading belong to the **same person**: `human_name` always equals `last_name` + `first_name`, and `human_name_kana` matches `human_name`. Full names are ordered per locale (`姓 名` for `ja`, `First Last` otherwise). - **Kana (`*_kana`) types require `locale: ja`.** faker's `ja` locale ships kanji names with no reading, so exwiw bundles its own paired (kanji, katakana) dataset for `ja`; this is what lets a fake person carry a kana reading that actually matches its kanji. Requesting a `*_kana` type with any other locale raises a clear error at build time. - Values are drawn from a pre-generated pool per (type, locale), so distinct seeds can share a fake value. The name family uses one shared **person** pool of 20,000 identities — for `ja` these are 20,000 *distinct* people enumerated from the bundled (kanji, kana) dataset (142 surnames × 142 given names); the other types use an independent 10,000-candidate pool. The uniqueness-sensitive types (`email`, `username`) additionally embed a 64-bit hex token derived from the seed hash, so they stay collision-free under a unique index even at millions of rows (collision probability at 5M distinct seeds ≈ 7e-7) and always use the `example.com` domain. - **Determinism caveat**: values are stable for a given locale plus the version of the value source — the faker gem for the non-`ja` name family and the independent types, and exwiw's bundled dataset for the `ja` name family. Upgrading that source (or changing `locale`) regenerates the pool and maps seeds to different values. The seed→value mapping itself never changes within one version. - The faker gem is **not** a runtime dependency of exwiw — add `gem "faker"` to your Gemfile to use this mode (exwiw raises a clear error otherwise). A config that uses **only** `ja` person types needs no faker (that pool is built entirely from the bundled dataset); faker is required for every other type and locale. - Exclusive with the other masking keys on the same column, and invisible to `explain`. Also supported by the MongoDB adapter on a `MongodbField` (seed names a field of the collection, or `_id`), where it is applied document-side after `replace_with` — see [MongoDB support](docs/mongodb.md#masking). **Performance**: this is a per-row Ruby transform, measured at ~1.5–1.6µs/row per fake column (so ≈ +8s per 5M rows per column; ~+40% against a local sqlite fetch — the worst case — and proportionally less against a network database, where the fetch dominates). Values are drawn from a pool pre-generated once, not by calling faker per row (which would be ~20× slower). Memory is unaffected: the transform streams with the dump. See [`docs/row-transform-masking-notes.md`](docs/row-transform-masking-notes.md) for the benchmark, and `script/bench_row_transform.rb` to measure on your data. ### MongoDB exwiw can export MongoDB databases too (`--adapter=mongodb`): JSONL output importable with `mongoimport`, schema/index DDL for `mongosh`, masking inside embedded documents, `reverse_scope` on collections, Mongoid-based config generation, a server-enforced query timeout, and parallel dump workers. Everything MongoDB-specific is documented in [docs/mongodb.md](docs/mongodb.md). ## How it works - Load the table information from the specified config file. - Calculate the dependency between tables. - Generate the full list of INSERT sql based on the specified conditions. - If the processing table has no relation with target tables, then dump all records. - If the processing table has relation with target tables, then dump the records which are related to the target tables. - Generate the full list of DELETE sql based on the specified conditions. - If the processing table has no relation with target tables, then delete all records. - If the processing table has relation with target tables, then delete the records which are related to the target tables. ## Development After checking out the repo, run `bin/setup` to install dependencies. Then, run `rake spec` to run the tests. You can also run `bin/console` for an interactive prompt that will allow you to experiment. To install this gem onto your local machine, run `bundle exec rake install`. To release a new version: 1. Run the **Release PR** workflow from the Actions tab with the new version number (e.g. `0.2.3`). This creates a PR that bumps `version.rb` and `CHANGELOG.md`. 2. Merge the PR. The **Release** workflow runs automatically, creating a git tag and publishing the gem to [rubygems.org](https://rubygems.org). ## Contributing Bug reports and pull requests are welcome on GitHub at https://github.com/heyinc/exwiw. ## License The gem is available as open source under the terms of the [MIT License](https://opensource.org/licenses/MIT).