Export What I Want (exwiw) exports part of a database as SQL INSERT files: the rows related to the records you name, with sensitive columns masked. It is meant for building a development database that looks like production, without copying all of production or maintaining hand-made seed data.
Each table is described in a JSON schema config: its columns, how to mask them, and its belongs_to relations. Given a target table and ids, exwiw follows those relations to decide which rows of every other table to export. The schema config can be generated from ActiveRecord or Mongoid models, or from a live database.
bundle add exwiwYou usually want require: false on the Gemfile entry. Without bundler, run gem install exwiw.
- MySQL
- PostgreSQL
- SQLite
- MongoDB (see MongoDB support)
For MySQL, exwiw uses the mysql2 gem if it is available and trilogy otherwise; pass --adapter=mysql either way. Set EXWIW_MYSQL_DRIVER=trilogy (or mysql2) to choose one. trilogy helps when mysql2 is linked against a client library that cannot load the server's auth plugin, such as a MySQL 9.x client connecting to a server that uses mysql_native_password.
To connect to MySQL over TLS, pass --ssl-mode (disabled, preferred, required, verify_ca or verify_identity) and --ssl-ca=<CA file>. The server certificate is verified only with verify_ca or verify_identity; --ssl-ca is rejected without --ssl-mode. Both apply to the driver connection and to mysqldump. Without them, exwiw uses the client library's default, which may connect without TLS and then fail against a server with require_secure_transport=ON. MariaDB's client library and mysqldump do not have every mode, so with them verify_ca also checks the host name and preferred leaves the client default (mysqldump gets --ssl / --ssl-verify-server-cert in place of --ssl-mode).
exwiw has three subcommands:
export(the default): write the dump files.explain: print the queriesexportwould run, with theirEXPLAINoutput.schema generate|check|tidy --from-db: maintain the schema config from a live database. See Non-Rails applications.
# The database password is read from DATABASE_PASSWORD.
exwiw \
--adapter=mysql \
--host=localhost \
--port=3306 \
--user=reader \
--database=app_production \
--schema-dir=exwiw/schema \
--target-table=shops \
--ids=1,2 \
--output-dir=dumpThis exports the shops rows with id 1 and 2 and the rows of other tables related to them. --schema-dir reads every JSON file in the directory.
--target-table |
--ids |
What is exported |
|---|---|---|
| given | given | The target rows by primary key, and their related rows. If the table declares a scope_column, scope-column mode is used instead |
| omitted | given | Scope-column mode. An error if no table declares a scope_column, unless every exported table is scope_exempt or Rails-managed (SQL adapters only) |
| omitted | omitted | Every table in full |
--mode chooses how the target is read, and the options depend on it:
--mode |
--target-table |
--ids |
--[no-]recursive |
|---|---|---|---|
primary-key |
Required. The table to start from | Its primary keys | Allowed. --no-recursive extracts only the target table's rows, without their related rows (default --recursive) |
scope-column |
No meaning (it only matters when --mode is omitted) |
Values of the tables' scope_columns; every table is filtered by its own |
An error |
Without --mode, the target decides: scope-column if the --target-table declares a scope_column in the schema, or if there is no --target-table (with --ids); primary-key otherwise. So adding a scope_column to a schema changes what --target-table=shops --ids=1 means; pass --mode=primary-key to keep it. With neither --target-table nor --ids, every table is exported in full.
The output directory (dump/ by default) is emptied before each run. When it already has files and stdin is a terminal, exwiw asks before removing them.
The output files are:
insert-000-schema.sql:CREATE TABLE IF NOT EXISTS ...for every table. Run it first to create an empty database.insert-{idx}-{table}.sql: one per table. A file may depend on files with a smalleridx, so import them in order.
exwiw writes no DELETE statements. Import into an empty database, or clear the target's rows yourself.
insert-000-schema.sql is created with the database's own tools (mysqldump, pg_dump, or the sqlite3 driver), so mysqldump or pg_dump must be on PATH. Set EXWIW_MYSQLDUMP to use a specific mysqldump, for example an 8.0 one when a 9.x mysqldump cannot authenticate against the server.
The schema file is rewritten so that running it again is harmless (IF NOT EXISTS, and PostgreSQL constraints and triggers that are skipped if they already exist). For MySQL, DEFINER clauses are removed so that a managed MySQL instance accepts the views and triggers.
MySQL data files turn off foreign key checks (FOREIGN_KEY_CHECKS=0). PostgreSQL data files set session_replication_role = 'replica', which turns off both foreign key checks and triggers. This setting needs superuser (rds_superuser on RDS); without it a WARNING is printed and triggers fire. The setting stays on for the rest of the connection, so run SET session_replication_role = 'origin' if you keep using that connection. SQLite loads with its triggers active.
On PostgreSQL, extensions that only exist to run a managed instance (google_vacuum_mgmt, google_columnar_engine, google_db_advisor) are left out of the dump, because a database outside that platform cannot create them. Other extensions are kept, and are skipped with a warning when the target cannot create them.
Prints the query export would run for each table, with its EXPLAIN output. For the SQL adapters the SELECT is not executed. For MongoDB, see exwiw explain verbosity.
exwiw explain \
--adapter=postgresql \
--host=localhost --port=5432 --user=reader \
--database=app_production \
--schema-dir=exwiw/schema \
--target-table=shops --ids=1--output-dir, --output-format and --after-insert-hook cannot be used with explain.
Prints the UPDATE statements that mask the given rows of one table in place, using the masks of the schema config, the same ones export applies. For MongoDB it prints one line of canonical Extended JSON per batch instead, ["<collection>", <operations>], where the operations are what collection.bulk_write takes (see Applying the MongoDB output).
exwiw mask-update \
--adapter=postgresql \
--host=localhost --port=5432 --user=reader \
--database=app_production \
--schema-dir=exwiw/schema \
--target-table=users --ids=1,2 --mode=primary-key --no-recursive > mask.sql--ids are primary key values (a composite primary key is not supported), and --mode=primary-key is required so that a scope_column declared in the schema never changes what they mean. Only the target table's rows are masked, not the tables related to it; recursion is not supported yet, so --no-recursive is required (a later version will extend what is masked, and --no-recursive will keep this behavior). The statements go to stdout; a summary (the number of rows, the columns, primary key values that were not found) goes to stderr. --profile applies the overlay of a profile.
- exwiw only reads. The output has no header and no transaction control; review it and apply it yourself. Whether the declared masks are safe values for the database you apply it to is not checked: a mask written for a development copy is applied as written.
- Every masked value is computed when the statements are generated, by the same SELECT
exportruns, and written as a literal: one UPDATE per row on SQLite, and on PostgreSQL and MySQL one per batch of up to 1,000 rows, joining the table with the rows' values. Apply them soon after generating; a row changed in between is overwritten with a value computed from its old data. - Applying the same statements twice leaves the same values. Generating them again after masking reads the masked values, so a mask that reads the column (
{column}),maporreplace_with_fake_datacan give different values the second time, andreplace_with_randomalways does. - Masks are applied as written, whatever the column's name. A credential (a password digest, a token, ...) masked to a constant, a value derived from the primary key, or a fake or
mapvalue stays guessable, so mask it withunsetorreplace_with_random(for a column that cannot be NULL), and enable theguessable_credentialscheck to find the others. A random value is drawn when the statements are generated and written into them, so treat the output as a secret. - The literals assume MySQL is applied over a
utf8mb4connection withoutNO_BACKSLASH_ESCAPES, and PostgreSQL withstandard_conforming_stringson. - It is an error when the table is not declared or is
ignore: true, has no primary key or a composite one, declares no mask, or masks its primary key.
Each line is ["users", [{"updateOne": {"filter": {"_id": ...}, "update": {"$set": {...}}}}]] in canonical Extended JSON, so the BSON types of the key and the values are kept. The operations are the ones mongosh's bulkWrite takes. In mongosh, parse each line with EJSON.parse(line, { relaxed: false }) and save this as apply.js:
const fs = require("fs");
for (const line of fs.readFileSync(process.env.MASK_UPDATE_FILE, "utf8").split("\n").filter(Boolean)) {
const [name, operations] = EJSON.parse(line, { relaxed: false });
db.getCollection(name).bulkWrite(operations);
}MASK_UPDATE_FILE=mask.jsonl mongosh "$MONGODB_URI" --file apply.jsIn Ruby, restore a line with BSON::ExtJSON.parse_obj(JSON.parse(line)). The driver's bulk_write does not take those keys as they are, so rename them to update_one: with Symbol keys:
name, operations = BSON::ExtJSON.parse_obj(JSON.parse(line))
client[name].bulk_write(
operations.map { |op| { update_one: { filter: op["updateOne"]["filter"], update: op["updateOne"]["update"] } } }
)An application can mask its own rows through the connection it already holds. Include Exwiw::Maskable::ActiveRecordModel (Mongoid: Exwiw::Maskable::MongoidDocument) in the models, then call mask_with_exwiw! on a relation (criteria) or a record:
class ApplicationRecord < ActiveRecord::Base
primary_abstract_class
include Exwiw::Maskable::ActiveRecordModel
def self.exwiw_config_path = Rails.root.join("exwiw/exwiw.yml") # optional
end
User.where(organization_id: 1).mask_with_exwiw!
User.where(organization_id: 1).mask_with_exwiw!(batch: false) # all rows at once, in no transaction of its own
user.mask_with_exwiw!
User.where(id: ids).mask_with_exwiw!(dry_run: true).statements # the UPDATEs, not executed
User.where(organization_id: 1).mask_with_exwiw! { |summary, statements| audit(statements) } # told of each batch once done-
It applies the masks
mask-updatewould print, reading and updating through the model's connection, and returnsExwiw::Maskable::Result(summary,statements).touch:(defaulttrue) also setsupdated_atto the current time;dry_run: trueonly returns the statements (statementsis nil without it): SQL UPDATEs, or for MongoDBExwiw::Adapter::MongodbAdapter::MaskUpdateBulkWrites (collection_name,operations, and#to_extended_jsonfor the linemask-updateprints), andprofile:applies a profile. -
Given a block, it yields each batch's summary and statements once the batch is done (after its transaction; with or without
dry_run), and keeps none of them:statementsis nil andsummarytotals the batches. Use it to handle the statements of a large relation without holding them all in memory. Mongoid masks in one batch, so the block is called once. -
A relation with no condition of its own (
User,User.all,User.unscoped, or only the default scope) is refused, as it would mask every row. A default scope that embeds a changing value such as the current time is not recognized as the default scope, so the check does not catch it. Rows a default scope hides (e.g. soft-deleted ones) are not masked unless the relation includes them (with_deleted). -
With
batch:(defaulttrue), an ActiveRecord relation is masked in batches of 1,000 rows (in_batches, so the relation's order is ignored), each in its own transaction, which joins the caller's if one is open. A batch that fails changes none of its rows, but the batches before it stay masked; wrap the call in a transaction to make the whole call all-or-nothing. Withbatch: falseit opens no transaction and builds every statement before running any, so a mask that cannot be applied changes nothing. Mongoid opens none either: it runs in the session Mongoid has open, if any. The updates bypass callbacks, so nothing that runs on save (search indexes, events, caches) sees them. Mongoid reads from the primary, and an embedded document class is refused (mask the parent). -
Model.exwiw_maskable?(profile:as above) tells, from the schema alone and without reading the table, whethermask_with_exwiw!would mask the model's rows: it is false when the table is not declared, isignore: true, Rails-managed or keyed by a composite primary key, declares no mask, or masks or does not select its primary key (Mongoid: also for an embedded document class). A caller masking many models can skip the ones it would refuse, whilemask_with_exwiw!still raises for them:models.select(&:exwiw_maskable?).each { |model| model.where(user_id: id).mask_with_exwiw! }
-
The config file is
./exwiw.ymlunless the class (or a base class, or a concern'sclass_methods) definesexwiw_config_path. The schema directory isexwiw_schema_dirwhen the class defines it, e.g. per database withRails.root.join("exwiw/schema", connection_db_config.name); otherwise the config file'sschema_dir, elseschemanext to theexwiw_config_pathfile, elseexwiw/schema. A loaded schema is kept for 60 seconds.
Options can be kept in a YAML file passed with --config=PATH. Without --config, exwiw.yml (or exwiw.yaml) in the current directory is used if it exists. Options passed on the command line take precedence.
adapter: postgresql
schema_dir: exwiw/schema
output_dir: dump
output_format: insert # insert | copy
after_insert_hook: hooks/seed.rb
log_level: info # debug | info
# target_table, ids, ids_field and scope_column can also be set here.DATABASE_PASSWORD=... exwiw \
--host=localhost --port=5432 --user=reader --database=app_production \
--target-table=shops --ids=1- Connection settings (
host,port,user,database,uri,password) are rejected, so they stay out of a committed file.adapteris allowed. - Relative paths are resolved from the config file's directory, not the current directory.
- Unknown keys are rejected. Keys that only apply to
exportare ignored byexplain,mask-updateandschema, so all subcommands can share one file (ids_fieldis the exception:mask-updatepicks rows by primary key and rejects it). - MongoDB-only keys (
explain_verbosity,mongodb_query_timeout_ms,parallel_workers) are described in MongoDB support. profilesdeclares schema profiles; see Profile overlays.baseis reserved for the base config.checkselects the static checksschema checkruns; see Static checks. Onlyschema checkreads it.
For PostgreSQL, --output-format=copy writes COPY ... FROM stdin instead of INSERT, which loads much faster. Import it with psql -d app_dev -f dump/insert-001-shops.sql.
Each table has one JSON file:
{
"name": "users",
"primary_key": "id",
"belongs_tos": [{
"table_name": "companies",
"foreign_key": "company_id"
}],
"columns": [{
"name": "id"
}, {
"name": "email",
"replace_with": "user{id}@example.com"
}, {
"name": "company_id"
}]
}belongs_tos decides which rows are exported (see How each table is narrowed), and each column can be masked (see Masking). The other table-level keys are:
filter: an SQL condition added to the table's query, such as"access_logs.created_at > '2025-01-01'". It is added to every query that joins this table, so it also narrows the tables that depend on it, which can leave their foreign keys pointing at rows that were not exported. Qualify column names with the table name. A filter reduces the rows returned, not necessarily the rows read; see Batched extraction for that.bulk_insert_chunk_size: the maximum rows perINSERTstatement (10,000 by default), to stay under limits such as MySQL'smax_allowed_packet.ignore,comment: see below.
A config with a key exwiw does not know fails to load, with an error naming the key and the file. A typo such as reverse_scop would otherwise silently disable what it was meant to do. Use comment for notes.
"ignore": true on a table stops its data from being exported. Its CREATE TABLE is still written to insert-000-schema.sql.
- A table that has a
belongs_toto an ignored table fails to load. Remove thatbelongs_to, or ignore it too. - An ignored table cannot be the
--target-table.
Entries in columns and belongs_tos accept comment (a note exwiw never reads) and ignore: true. An ignored column is left out of the SELECT and the INSERT, so the restored rows get the column's database default, or NULL if it has none. On a NOT NULL column without a default, the INSERT can fail. An ignored belongs_to is not followed.
{
"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": "not needed in development" }
],
"columns": [
{ "name": "id" },
{ "name": "secret_token", "ignore": true, "comment": "do not copy credentials" }
]
}Regenerating the config (see Generating the schema config) keeps what you wrote by hand. A column already in the config is kept as it is (with its allow_unmasked and allow_guessable_credential), comment and ignore on a table or belongs_to are kept, and so are the keys the generators never write (filter, bulk_insert_chunk_size, scope_column, id_space, scope_exempt, reverse_scope, batch_scope).
Settings that only one kind of export needs, such as the exports for a development environment, can live in an overlay file next to the base config instead of in it. users.dev.json is merged into users.json when the dev profile is selected:
# exwiw.yml
profiles: [dev] # the profile names exwiw recognizes{
"name": "users",
"filter": "users.created_at > '2025-01-01'",
"columns": [
{ "name": "email", "replace_with": "user{id}@example.com" },
{ "name": "nickname", "raw_sql": null }
]
}exwiw export --profile=dev ... # without --profile, no profile applies
exwiw schema show --profile=dev --table=users # prints the merged config; needs no database- Only the names in
profilesare recognized: without them,users.dev.jsonis an ordinary config file. Profile names use lowercase letters, digits,_and-. A profile applies only when--profilenames it (export, explain,mask-updateandschema show), one per run; naming an undeclared profile is an error. - An overlay must have the
nameof a config in the same directory (with multiple databases, in that database's subdirectory), and each config can have one overlay per profile. - A key in the overlay replaces the base value; a nested object such as
batch_scopeis replaced as a whole.columns(fieldsfor MongoDB) are matched byname, andbelongs_tosbytable_name,foreign_key,foreign_typeandtype_value; the matched entry is merged key by key. An overlay can only change entries the base has, not add new ones. - Setting one of
replace_with,raw_sql,map,replace_with_fake_data,replace_with_random,unsetandallow_unmaskedon a column removes the others, so the overlay's mask is the one that applies, and"allow_unmasked": trueexports the column unmasked for that profile. To removeunsetorallow_unmaskedin an overlay, set it tonull.unset,allow_unmaskedandallow_guessable_credentialaccept onlytrue:falseis rejected, as it would count as setting the flag and remove the base's mask. nullremoves an optional key.name, required keys and lists cannot be set tonull.- The merged config is loaded and validated like any config; a profile that cannot be merged or produces an invalid config fails every command that reads it.
- The schema generators,
tidyandchecknever read or write overlays as base configs.tidyremoves overlay entries for tables, columns andbelongs_tosthat are gone from the base (and the overlay file when its table is gone).checkreports them asoverlay_stale(see Checking the config against the schema), and fails when an overlay does not merge.
Only the target table is filtered by --ids directly. exwiw narrows every other table to the rows related to the target, using the first of these rules that applies:
| # | Rule | When it applies | Result |
|---|---|---|---|
| 1 | Direct filter | The table is the --target-table, or declares a scope_column in scope-column mode |
WHERE pk IN (ids) or WHERE scope_column IN (ids) |
| 2 | belongs_to path |
Following belongs_to reaches a table of rule 1 |
Joined along the shortest path |
| 3 | Referenced by one table | No path, but exactly one narrowed table has a foreign key to it | Only the rows that table points at |
| 4 | reverse_scope |
Several narrowed tables point at it, and the config lists them | Only the rows the listed tables point at |
| 5 | Narrowed parent | No path, but a belongs_to parent is narrowed by a rule above |
Only the rows belonging to the parent's exported rows, over any number of hops |
| 6 | Full dump | Nothing relates the table to the target | All rows, meant for master data. Scope-column mode stops with an error instead, unless the table has scope_exempt: true |
- Rule 3 covers tables like
active_storage_blobs, which nothing in the table itself links to the target. It applies only when the referencingbelongs_tois not polymorphic. - Rule 5 applies only when exactly one parent is narrowed, and stops at a
belongs_tocycle. - If a table matches both rule 3 and rule 5, rule 3 wins and the result can miss rows that rule 5 would have kept; set
ignore: trueon the referencingbelongs_toto use rule 5. - Whichever rule narrows a table with a
belongs_toto itself, the ancestors of its rows are added (see Self-referencingbelongs_to). - MongoDB follows the same rules, collecting the ids while it reads the parent collections instead of using SQL subqueries. It does not add ancestors.
When a rule picks the full dump because the relation was ambiguous, exwiw logs a warning. exwiw explain is the easiest way to see what each table resolved to. Rules 3 to 5 appear in it as a JOIN on a SELECT DISTINCT subquery; docs/scope-id-set-join-notes.md explains why.
A polymorphic association (belongs_to :reviewable, polymorphic: true) is written as one belongs_to per target table, each with the type column (foreign_type) and the value it holds for that target (type_value):
{
"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 writes these entries from the models' has_many ..., as: declarations. A non-polymorphic belongs_to leaves out foreign_type and type_value.
Following such a belongs_to also checks the type column, so dumping products exports only the reviews with reviewable_type = 'Product'.
When a table's path to the target goes through a polymorphic belongs_to, exwiw follows every entry with the same foreign_type and exports the union of their rows. Dumping shops exports the shop's own reviews and the reviews of its products.
- An entry whose target table is not narrowed is skipped, so a polymorphic
belongs_tonever widens the dump. - If the shortest path leaves through a non-polymorphic
belongs_to, only that path is used. - In single-target mode, an entry is skipped when its table is narrowed only by rule 3. Those rows are exported to keep foreign keys valid, not because they own the rows that point at them. So dumping
productsstill exports only theProductreviews, even though the product's shop is exported too. An entry whose table has areverse_scopeis followed.
ActiveStorage needs no configuration:
active_storage_attachmentsis a polymorphicbelongs_to :record, so only the attachments of exported records are exported.active_storage_blobsis narrowed by rule 3 to the blobs those attachments point at.active_storage_variant_recordsis generated withignore: true, because ActiveStorage recreates its rows when needed and itsblob_idcould point at blobs that were not exported. Removeignoreif you need it.
A table such as users often has no belongs_to toward the target, while many narrowed tables point at it. Rule 3 does not apply when there is more than one, so it would be dumped in full, with every tenant's users. reverse_scope lists the tables and columns that point at it, and the table is narrowed to the values those tables export:
{
"name": "users",
"primary_key": "id",
"reverse_scope": {
"via": [
{ "table": "customers", "column": "user_id" },
{ "table": "staff", "column": "user_id" },
{ "table": "members", "column": "legacy_user_id" }
]
},
"columns": [{ "name": "id" }, { "name": "name" }]
}columnis given explicitly, so a column with a non-standard name, or one without abelongs_to, works.- List only narrowed tables. A table in
viathat is not narrowed would add every value, so it is skipped with a warning. - A table in
viamay itself be narrowed by its ownreverse_scope. - By default the values are matched against the primary key. Set
reverse_scope.columnto match another column, such as{ "column": "code", "via": [{ "table": "contracts", "column": "rate_code" }] }. - Tables that
belongs_tothe reverse-scoped table are narrowed by rule 5 and need no config.
When a table has a belongs_to to itself, such as categories.parent_id, narrowing it would drop the parents of the rows it keeps. exwiw also keeps every ancestor of those rows, up to the root. Declaring the belongs_to is enough.
- Ancestors are kept regardless of the table's
filterand scope, so foreign keys stay valid. If a tree spans tenants, the dump can include another tenant's ancestors. - Tables below the tree (
category_notes) also keep the rows of the ancestors, and tables the tree points at keep what the ancestors point at. An ancestor with many rows hanging off it brings them all; setignore: trueon thatbelongs_toto leave them out. - The walk stops at a
NULLor missing parent and at cycles in the data. Polymorphic self-references are not supported. - On MySQL this needs 8.0 or later, and a tree deeper than
cte_max_recursion_depth(1000 by default) fails. - Batched tables do not get ancestors added; exwiw warns about this. MongoDB does not add ancestors either.
Single-target mode assumes every table reaches one target table through belongs_to. In many multi-tenant schemas, tables instead each carry a tenant column (tenant_id) and are not all connected to one root. Picking one table as the target would dump the unrelated tables in full.
In scope-column mode, each table names the column that holds the tenant id, and --ids are values of that column:
{
"name": "shops",
"primary_key": "id",
"scope_column": "tenant_id",
"columns": [{ "name": "id" }, { "name": "name" }, { "name": "tenant_id" }]
}exwiw \
--adapter=postgresql \
--host=localhost --port=5432 --user=reader \
--database=app_production \
--schema-dir=exwiw/schema \
--ids=42,43 \
--output-dir=dumpHere 42,43 are tenant_id values, not shop ids. Passing --target-table=shops gives the same result.
Tables without a scope_column are narrowed by the rules above. A table that none of them can narrow stops the run before anything is exported, and the error lists those tables. For each, declare a scope_column, add a belongs_to, set ignore: true, or set scope_exempt: true.
When every table that is not ignore: true is scope_exempt or Rails-managed, there is nothing for --ids to narrow: no table needs to declare a scope_column, the tables are exported in full, and a warning is logged. So a run that passes the same --ids to several databases also works for one with no tenant data.
Scope-column mode is for the SQL adapters only. Use exwiw explain to check the queries first.
A belongs_to to a table in another database cannot be joined, so schema:generate writes it with ignore: true. The foreign key column is still there, so declaring scope_column: "<that foreign key>" on the table narrows it without a join.
A master table with no personal data and no relation to the tenant can be exported in full:
{ "name": "countries", "primary_key": "id", "scope_exempt": true, "columns": [{ "name": "id" }, { "name": "code" }] }schema_migrations and ar_internal_metadata are exempt automatically.
Each table names its own column, so tables that store the tenant id under different names work together. When one database has two groups of tables keyed by different kinds of id, give one group an id_space and pass its values separately:
{ "name": "tenants", "primary_key": "id", "scope_column": "id", "columns": [{ "name": "id" }] }
{ "name": "organizations", "primary_key": "id", "scope_column": "id", "id_space": "org", "columns": [{ "name": "id" }] }exwiw ... --ids=1,2 --ids=org=0b6f4c1e-0000-4000-8000-000000000001- Tables without
id_space, and--idswithout a prefix, use thedefaultID space. Write--ids=default=...when an id itself contains=. - A table without a scope column uses the ID space of the table it is narrowed through.
- The run stops before exporting when an ID space a table needs has no values, when values are given for an unused ID space, or when a table reaches tables of more than one ID space.
- Single-target mode and MongoDB support only the
defaultID space. - In the config file,
ids:takes a list, or a mapping such asids: { default: [1, 2], org: [...] }.
The older global --scope-column=COLUMN flag still works but is deprecated; declare scope_column per table instead.
A table with hundreds of millions of rows can be slow to export even when few of its rows are kept. Once the scope covers many parent rows, the database may decide that scanning the whole table is cheaper than using the foreign key index, and the query runs for hours or hits statement_timeout.
batch_scope avoids this by exporting the table in batches. exwiw first fetches the ids of a narrowed table on the path (the batch table), then runs one query per size ids, with the ids written into the query:
{
"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" }]
}SELECT activities.* FROM activities
JOIN customers ON activities.customer_id = customers.id
AND customers.id IN (/* 1000 ids */)- The exported rows are the same as without batching. The batch table's ids are sorted, so the output is the same on every run.
sizedefaults to 1000.- The batch table can be several hops up the path.
- A table with its own
scope_columncan name itself. This only helps when the scope column is indexed. batch_scoperequires scope-column mode, and a table narrowed by rule 1 or rule 2. Other cases are rejected before anything is written.- With
--output-format=copy, all batches are held in memory at once. Use the defaultINSERTformat for very large results. exwiw explainalso shows the query that fetches the batch table's ids.
Each column can be masked with one of the following keys. A column whose name looks sensitive but that is deliberately exported as it is can say so with "allow_unmasked": true (see Static checks).
Replaces the value with a string. {column} is replaced with that column's value, so for a row with id 1, "user{id}@example.com" becomes user1@example.com. {} is kept as is, so "replace_with": "{}" gives an empty JSON object.
A number or boolean is used as it is, so non-text columns keep their type:
NULL stays NULL (an empty string is still replaced).
An SQL expression used in place of the column, such as "CONCAT('user', shops.id, '@example.com')". Use it when a database function is needed. Qualify column names with the table name. replace_with is ignored when both are set. SQL adapters only.
Ruby code that returns a Proc. The proc is called with each row, and its return value (a String, a number or nil) replaces the column:
{ "name": "email", "map": "proc { |r| 'user' + r['id'].to_s + '@example.com' }" }r['column']reads any column of the row, after the database-side masking of other columns.NULLis not kept automatically; the proc receivesniland decides.- It runs in the exwiw process, so
explaindoes not show it. SQL adapters only. - Because the config runs arbitrary Ruby, only load configs you trust.
Prefer replace_with or raw_sql when they can do the job.
Replaces the value with realistic fake data from the faker gem. The value is chosen from a seed column, so the same seed always gives the same value, across tables, runs and adapters:
{ "name": "name", "replace_with_fake_data": { "seed": "users.id", "type": "human_name", "locale": "ja" } }| type | example (en) | example (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 |
seedis a column of the same table, with or without the table name. Use a stable id such as the primary key.- For one seed, the name types describe the same person:
human_nameislast_name+first_name, and the kana matches the kanji. - Different seeds can get the same name.
emailandusernameinclude a token from the seed, so they stay unique. - Values change when
locale, the faker version, or exwiw's bundled Japanese name list changes. NULLstaysNULL.- Add
gem "faker"to your Gemfile. A config that uses onlyjaname types does not need it. - It runs in the exwiw process, so
explaindoes not show it. The cost is small; seedocs/row-transform-masking-notes.md.
Replaces the value with random bytes as lowercase hex, different for every row. Use it for a credential (a token, a secret key) that must not be guessable but cannot be NULL, such as one under a unique index:
{ "name": "api_token", "replace_with_random": "hex:32" } // 64 hex characters- The format is
hex:<bytes>, with 16 to 1024 bytes. No other format is supported. NULLstaysNULL.- The value comes from Ruby's
SecureRandomin the exwiw process, soexplaindoes not show it.
Removes the value: the column is exported as NULL, and a MongoDB field is left out of the document ($unset in mask-update). Use it for a credential the copy does not need:
{ "name": "password_digest", "unset": true }It accepts only true; in an overlay, "unset": null removes it. Unlike the replace_with keys, which keep NULL as it is, a MongoDB field holding null is removed too, so every masked document ends up without the field: an application reading it sees the field as not set, and an ODM fills in the field's default (Mongoid's default:), which it does not for null.
Only one masking key can be set on a column.
In a Rails application, a rake task writes the schema config from the models:
bundle exec rake exwiw:schema:generateThe files go to EXWIW_SCHEMA_DIR_PATH if set, otherwise schema_dir from exwiw.yml, otherwise exwiw/schema. If the application has more than one source of models, such as ActiveRecord and Mongoid, give each its own directory; check and tidy treat files they do not recognize as stale.
A new column could hold personal data, so schema:generate writes every column that is not yet in the config as masked, and marks it with needs_mask_decision: true. Columns already in the config are left as they are.
The mask is the column's default value if it has a constant one, otherwise a value by type: masked-{primary key} for text (with @example.com when the name mentions mail), 0, false, a fixed date, or {} for JSON. Some columns are marked but left unmasked, because masking them would break the dump or the restore:
- the primary key and the columns
belongs_tojoins on - types no constant fits, such as
uuid, binary, enums, arrays, and text too short for the mask - columns under a unique index, unless the mask differs per row
For the first config of an application, where every column is new, run with EXWIW_NEW_COLUMNS=plain to turn safe mode off. Do not use it afterwards: those columns get no mark, so nothing tells them apart from reviewed ones.
needs_mask_decision: true marks a column whose masking nobody has decided yet. Export ignores it. schema:check reports these columns, so CI can block a pull request until each is decided. To decide, keep the mask (ideally with a comment saying why), change it, remove replace_with to export the real value, or set ignore: true, and then remove the key.
schema:generate never deletes anything. schema:tidy removes the config files of tables that no longer exist in the database, and the columns those tables no longer have. It reads the database, not the models, so a table without a model is kept. It does not touch anything else, and does not remove stale belongs_tos; run schema:generate for those. It also removes the profile overlay entries the tidied base no longer has.
bundle exec rake exwiw:schema:tidyschema:check reports how the config differs from what generate and tidy would produce, without writing anything. It exits non-zero when something needs attention, so it can run in CI:
bundle exec rake exwiw:schema:check{
"added_tables": [],
"added_columns": ["users.contact_email"],
"removed_tables": [],
"removed_columns": ["orders.legacy_flag"],
"changed_tables": ["orders", "users"],
"needs_mask_decision": ["orders.memo"],
"stale_tables": [],
"stale_columns": ["orders.legacy_flag"]
}-
added_*,removed_*andchanged_tables: runschema:generateandschema:tidy. -
needs_mask_decision: columns still waiting for a decision. -
stale_*: removed tables and columns that the config still exports, which would make the export fail. Used by--fail-on=stale(see below). -
overlay_stale: only whenprofilesare declared. Entries of profile overlays that reference what the regenerated base no longer has, such asdev:users.columns.legacy_flag. These are stale too: the overlay would no longer merge. An overlay that does not merge into the committed config makes the check fail with an error instead. -
integrity,guessable_credentials,unmasked_sensitive: findings of the static checks, one key per enabled check. Regenerating does not fix them.
With multiple databases each entry starts with the database name (primary/users.email). Set EXWIW_SCHEMA_CHECK_OUTPUT=<path> to also write the JSON to a file.
schema:check also judges the committed config itself, with no database rows. integrity applies to every use of exwiw and is on by default; the others are rules you opt into, like a linter's: guessable_credentials matters to mask-update (and mask_with_exwiw!), which applies masks as written, and unmasked_sensitive guesses from column names, so it reports false positives. Each check is enabled in the config file:
# exwiw.yml
profiles: [dev]
check:
integrity: {} # on by default; `false` turns it off
guessable_credentials: # off by default
patterns: ['(^|_)pin(_|$)'] # added to the built-in credential names
unmasked_sensitive: # off by default
patterns: ['(^|_)kana(_|$)'] # added to the built-in patterns
profiles: [base, dev] # which configs to judge- Each check takes
true(the same as{}), a mapping of options, orfalse. Withoutcheck, onlyintegrityruns. profilesnames the configs to judge:baseis the base config with no overlay, the others are declared profiles. By default a check judges the base config and every declared profile. A finding shows the profiles it occurs in:users.api_token [base, dev]: ....- Tables that
mask-updaterefuses are skipped:ignore: true, Rails-managed and composite primary key tables. A MongoDB embedded collection is judged through its parent, under the field's path (users.profile.addresses.zip_code). Ignored columns are not judged. - Each database directory is judged with its adapter. The rake task takes it from the Rails database configuration named like each database directory (the default connection's for a single database);
check_mongoiduses MongoDB, andexwiw schema checkits--adapter. An adapter that cannot be found fails the check. - An invalid
checksection fails the check (exwiw schema checkexits with the "could not run" status).
The checks:
integrity: every profile's config loads, and every table selects its primary key unmasked. A masked or unselected (ignored) primary key breaks the rows that reference the table, andmask-update, which addresses rows by it.guessable_credentials: a column (MongoDB: a field path) whose name looks like a credential, masked to anything butunsetorreplace_with_random, unless it setsallow_guessable_credential: true. Also anallow_guessable_credentialthat guards no such column. The built-in names arepassword,secret,token,credential,passcode,api_key,access_key,private_key(also without_) andjwk(case-insensitive, anywhere in the name), and cannot be removed;patternsadds regular expressions (case-insensitive) to them, whichunmasked_sensitivecounts too.digestandsaltare not among them: a guessable digest or salt matches no input, so masking one to a constant is safe.unmasked_sensitive: a column (MongoDB: the last segment of a field path) whose name matches a mask pattern but that declares no mask and no"allow_unmasked": true, in every table. The primary key andbelongs_toforeign keys are exempt, as exwiw never masks them. Also anallow_unmaskedon a column that is masked, exempt, or matches no pattern. The built-in patterns are case-insensitive and cannot be removed;patternsadds regular expressions (case-insensitive) to them:- the credential names above, with the
patternsofguessable_credentials;otp;encrypted,digest,salt(a real digest of a short secret, such as a PIN, can be brute-forced) email,e_mail;phone,mobile(as a whole name, ormobile_numberand the like),tel,fax;address;zip,postalbirth;first_name,last_name,middle_name,full_name,given_name,family_name(also without_)ssn,cvv,cvc;card_number
- the credential names above, with the
Both checks leave out a name ending in _at (a timestamp such as email_updated_at), even when it matches a pattern.
allow_unmasked: true is a decision to export real data, so leave a comment saying why.
With Rails' multiple databases, schema:generate writes each database's files into a subdirectory named after it (exwiw/schema/primary/, exwiw/schema/analytics/). Each database is exported by a separate run.
A belongs_to to a model in another database is written with ignore: true and ignore_type: "cross_database"; see Cross-database foreign keys.
schema_migrations and ar_internal_metadata get a config with a type such as rails_managed_schema_migrations and no columns. They are exported with SELECT * and INSERT without a column list, so new Rails versions do not break them. They cannot be the --target-table.
Composite primary keys are not supported. Such a table is generated with ignore: true and type: "unsupported_composite_primary_key".
bundle exec rake exwiw:schema:generate_mongoid
bundle exec rake exwiw:schema:tidy_mongoid
bundle exec rake exwiw:schema:check_mongoidThese work like the ActiveRecord tasks. See Generating config from Mongoid models.
For applications exwiw cannot load, the same three operations read the database instead of the models:
exwiw schema generate --from-db -a postgresql -h db.example.com -p 5432 -u app --database=app --schema-dir=exwiw/schema
exwiw schema check --from-db -a postgresql -h db.example.com -p 5432 -u app --database=app --schema-dir=exwiw/schema
exwiw schema tidy --from-db -a postgresql -h db.example.com -p 5432 -u app --database=app --schema-dir=exwiw/schema- MySQL and PostgreSQL only.
DATABASE_PASSWORDmay be empty, for CI databases without a password.- Safe mode and the
checkreport work as above, including the static checks, which judge the config with the--adapter's rules.checkexits 1 when the config needs attention, and with another status when it could not run. check --fail-on=stalefails only onstale_*, so it can run before an export without blocking on newly added columns or static check findings.- One run covers one database, and the files are written directly into the schema directory.
Foreign keys in the database become belongs_tos. A table without a primary key is generated with ignore: true and a comment; once you add a primary_key by hand, it is kept.
Regeneration only adds belongs_tos, because many relations exist only in application code. Add those by hand; they are kept from then on, and their foreign key columns are never masked. tidy removes a belongs_to whose table no longer exists.
--after-insert-hook=PATH runs a script after all data files are written, to add rows of your own.
A Ruby hook (.rb) can use:
cli_options: the parsed options, such ascli_options.fetch(:ids).ids_for(id_space = "default"): the ids of an ID space.insert_sql(template): renders an ERB template and writes it toinsert-{N+1}-after_insert.sql, after the last data file. Multiple calls go into the same file.insert_jsonl(collection, template): MongoDB only; see MongoDB support.
insert_sql <<~SQL
<%- cli_options.fetch(:ids).each do |tenant_id| -%>
INSERT INTO users (tenant_id, email) VALUES (<%= tenant_id %>, 'default@example.com');
<%- end -%>
SQLRuby hooks run inside the exwiw process, so only use hooks you trust.
Any other file is run as a command. Its output is not captured, and a non-zero exit stops exwiw. It gets DATABASE_PASSWORD and these environment variables:
EXWIW_OUTPUT_DIR,EXWIW_SCHEMA_DIREXWIW_DATABASE_ADAPTER,EXWIW_DATABASE_HOST,EXWIW_DATABASE_PORT,EXWIW_DATABASE_USER,EXWIW_DATABASE_NAMEEXWIW_TARGET_TABLE,EXWIW_IDS(comma-separated, thedefaultID space),EXWIW_OUTPUT_FORMATEXWIW_IDS_<ID_SPACE>for each ID space given values (--ids=org=...becomesEXWIW_IDS_ORG)
--adapter=mongodb exports JSON Lines for mongoimport. Setup, options and the differences from the SQL adapters are in docs/mongodb.md.
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:
- Run the Release PR workflow from the Actions tab with the new version number (e.g.
0.2.3). This creates a PR that bumpsversion.rbandCHANGELOG.md. - Merge the PR. The Release workflow runs automatically, creating a git tag and publishing the gem to rubygems.org.
To publish a prerelease, give a version in RubyGems' prerelease form, X.Y.Z.alphaN, X.Y.Z.betaN or X.Y.Z.rcN (e.g. 1.3.0.beta1). The flow is the same, except that CHANGELOG.md is left unchanged (its [Unreleased] items become the release notes and stay there for the final release) and the GitHub Release is marked as a pre-release. RubyGems installs a prerelease only with gem install exwiw --pre or an explicit version requirement.
Bug reports and pull requests are welcome on GitHub at https://github.com/heyinc/exwiw.
The gem is available as open source under the terms of the MIT License.
{ "name": "score", "replace_with": 0 } { "name": "active", "replace_with": false }