Skip to content

Sanitization Strategies

Jump to bottom
Arael Espinosa edited this page Apr 7, 2026 · 3 revisions

Sanitization Strategies

DBMigrator supports seven sanitization strategies. Each strategy generates a SQL UPDATE statement applied to the target database after the dump is restored.

Sanitization runs per-table, not per-row — it uses a single UPDATE statement, which is efficient even on large tables.

All-or-Nothing Execution

All sanitization rules execute inside a single database transaction. If any rule fails (e.g., a column doesn't exist, a type mismatch, or a NOT NULL violation), the entire transaction is rolled back and the target database is left unchanged. There is no partial sanitization state.

In --dry-run mode the transaction is always rolled back after printing the SQL, so no data is modified.

static

Replaces all values in the column with a fixed string.

- name: "password_hash"
  strategy: "static"
  value: "$2b$10$REDACTED_HASH_VALUE_HERE"

Use when: you want all rows to share a known test value (e.g., a universal test password).

null_value

Sets the column to NULL.

- name: "avatar_url"
  strategy: "null_value"

Requirements: the column must be nullable. The validate command will report an error if you apply this to a NOT NULL column.

Use when: the data is sensitive but optional and not needed in development.

template

Generates values from a template string using column references. References are resolved as SQL concatenation at query time.

- name: "email"
  strategy: "template"
  value: "user_{id}@redacted.local"

The {id} placeholder is replaced by the actual id column value per row, producing unique values like user_42@redacted.local.

You can combine multiple column references:

value: "{first_name}_{last_name}_{id}@test.local"

Use when: you need deterministic, unique values that still look realistic and traceable (e.g., emails that include the original ID).

faker

Generates realistic but fake data using the Bogus library. A new value is generated per row.

- name: "first_name"
  strategy: "faker"
  faker: "first_name"

- name: "email"
  strategy: "faker"
  faker: "email"

See Faker Methods Reference for all 23+ available methods.

Use when: you need realistic-looking data in the target environment (e.g., for UI testing or demos).

Note: By default faker values differ on every run. Set faker_seed in your config to a fixed integer to make output reproducible across runs. Referential integrity across tables is still not preserved regardless of seed — use hash when FK consistency matters.

hash

Replaces the value with its SHA256 hex digest. The same input always produces the same output.

- name: "national_id"
  strategy: "hash"

- name: "user_ref"
  strategy: "hash"

Use when: you need to pseudonymize data while preserving uniqueness and foreign key relationships across tables.

partial_mask

Keeps a configurable number of leading and/or trailing characters visible and replaces the middle with a mask character (default *). The total string length is preserved.

# Keep last 4 digits: "+1 555 123 4567" → "***********4567"
- name: "phone"
  strategy: "partial_mask"
  keep_last: 4

# Keep first 2 and last 4: "john.doe@example.com" → "jo**************com"
- name: "email"
  strategy: "partial_mask"
  keep_first: 2
  keep_last: 3
  mask_char: "*"   # optional, default: *
Field Required Description
keep_first One of the two must be set Number of leading characters to keep visible
keep_last One of the two must be set Number of trailing characters to keep visible
mask_char No Single character used to fill the masked portion. Default: *

The expression is generated as pure SQL (LEFT + REPEAT + RIGHT) — values are never read into application memory.

Use when: you need to comply with PII masking policies while keeping enough context for support or debugging (e.g., last 4 digits of a phone number).

random_from

Picks a random value from a user-defined list on each row. Useful for categorical fields like status, role, country_code, or plan.

- name: "status"
  strategy: "random_from"
  values: ["active", "inactive", "pending"]

- name: "country_code"
  strategy: "random_from"
  values: ["US", "ES", "MX", "AR", "CO"]
Field Required Description
values Yes List of at least 2 strings to pick from

The selection is done in SQL using ARRAY[...][floor(random() * N)::int + 1] — no data is read into the application.

Use when: a column holds a known set of categorical values and you want the sanitized data to look realistic within that domain.

Row Filtering with where

All strategies support an optional where clause that limits which rows are updated:

- table: "users"
  where: "role != 'admin'"
  columns:
    - name: "email"
      strategy: "faker"
      faker: "email"

This is useful to preserve certain rows (e.g., admin accounts, seed data) while redacting the rest.

Strategy Comparison

Strategy Uniqueness Referential Integrity Realistic Deterministic
static No No No Yes
null_value No No N/A Yes
template Yes (if using ID) Yes Partial Yes
faker Mostly No Yes With faker_seed
hash Yes Yes No Yes
partial_mask Preserves original Preserves original Yes Yes
random_from No No Yes (categorical) No

Clone this wiki locally