How to Generate Null, Empty, and Missing Database Values

How to Generate Null, Empty, and Missing Database Values

When a test suite exercises a data‑driven application, the absence of a value is often more revealing than its presence. Nulls, empty strings, and completely missing rows expose constraints, default‑value logic, and error‑handling paths that happy‑path data never touches. Yet many teams still treat “missing data” as an after‑thought, copying a handful of NULL literals into a seed script and calling it a day.

Below is a practical, step‑by‑step framework for producing null, empty, and missing values in a repeatable, version‑controlled way. The guide assumes you already have a test‑data pipeline (SQL scripts, a data‑factory library, or a commercial generator) and focuses on the decision points that turn a generic pipeline into one that reliably exercises the “nothing there” scenarios.

---

1. Understand the Three Flavors of “Nothing”

Flavor	SQL Representation	Typical Business Meaning	Why It Breaks Things
NULL	NULL	“Value unknown / not applicable”	Violates NOT NULL constraints, triggers IS NULL branches, changes aggregate results (COUNT(col) vs COUNT(*)).
Empty string	'' (or ''::text, ''::varchar)	“Explicitly provided but blank”	Passes NOT NULL checks, fails length validators, may be trimmed unexpectedly, influences LIKE '' matches.
Missing row / FK	No row in parent table, or FK column set to NULL	“Entity never created / relationship optional”	Triggers foreign‑key violations, outer‑join logic, cascade‑delete paths, “orphan” cleanup jobs.

Rule of thumb: Treat each flavor as a first‑class test datum with its own generation rule, not as a side‑effect of “leave the column out”.

---

2. Prerequisites – What You Need Before You Generate

Item	Why It Matters	Quick Verification
Schema contracts (DDL, CHECK, NOT NULL, DEFAULT)	Generation must respect or intentionally violate constraints.	SELECT column_name, is_nullable, column_default FROM information_schema.columns WHERE table_name = '…';
Data‑generation sandbox (dedicated schema, Docker Compose, CI ephemeral DB)	Prevents polluting shared environments.	CREATE SCHEMA test_gen; SET search_path = test_gen;
Version‑controlled seed scripts (SQL, YAML, or code)	Guarantees reproducibility across runs and teammates.	git diff HEAD~1 -- sql/seed/
Metadata catalogue (column‑level “nullability”, “emptiability”, “optional‑FK”)	Drives automated rule selection.	Export from DBT docs, OpenAPI spec, or a simple CSV.
Validation harness (assertion library, DBT tests, Great Expectations)	Confirms the generated “nothing” actually lands in the DB.	dbt test --select tag:null_checks

If any of these are missing, stop and add them first. The cost of a flaky “null test” is usually a hidden production bug.

---

3. Decision Matrix – Choosing a Generation Strategy

Strategy	When It Shines	Tooling Fit	Maintenance Cost
Explicit NULL literals in seed SQL	Small, static datasets; one‑off migration tests.	Plain SQL, Flyway, Liquibase	Low – but manual updates for every schema change.
Data‑factory functions (factory_boy, FactoryBot, go-faker)	Object‑oriented test code; need programmatic variation per test case.	Language‑specific libraries	Medium – factories must be kept in sync with schema.
Declarative YAML/JSON + templating (dbt seed, jinja2)	Large, evolving schemas; want a single source of truth.	dbt, custom Jinja templates	Medium‑High – template logic can become complex.
Specialised test‑data generator (e.g., QA3 free test data generator)	Teams that want a UI‑driven, schema‑aware “null/empty/missing” toggle without writing code.	Web UI + API (/tools/test-data-generator)	Low – configuration only; generator handles constraint awareness.
Database‑level DEFAULT + INSERT … DEFAULT VALUES	Testing default‑value logic, not the absence itself.	Pure SQL	Low – but only covers defaults, not explicit null/empty.

Pick one primary strategy and keep a secondary fallback for edge cases (e.g., a hand‑crafted SQL script for a legacy table that the generator cannot reverse‑engineer).

---

4. Worked Example – Generating All Three Flavors for a customers Table

Assume the following (simplified) DDL:

CREATE TABLE customers (
    id            BIGSERIAL PRIMARY KEY,
    email         VARCHAR(255) NOT NULL,
    phone         VARCHAR(30),                -- nullable
    nickname      VARCHAR(50) DEFAULT '',     -- empty string default
    referral_id   BIGINT REFERENCES customers(id) ON DELETE SET NULL
);

4.1. Define the “nullability profile” (metadata)

Column	Nullable?	Empty‑String Allowed?	Optional FK?
email	NO	NO	–
phone	YES	YES (treated as empty)	–
nickname	NO (default '')	YES (explicit)	–
referral_id	YES	N/A	YES

Store this as customers_nullability.yaml – the generator reads it and knows which flavors are legal.

4.2. Choose the generator (QA3 free test data generator)

1. Open /tools/test-data-generator.
2. Paste the DDL or upload the YAML profile.
3. Enable the three toggles: Null, Empty, Missing FK.
4. Set row count = 100 (or whatever your test suite needs).
5. Click Generate → download customers_seed.sql.

The produced script contains rows like:

INSERT INTO customers (email, phone, nickname, referral_id) VALUES
('alice@example.com', NULL, '', NULL),                     -- null phone, empty nickname, missing referral
('bob@example.com', '', 'bobthebuilder', 1),               -- empty phone, explicit nickname, FK present
('carol@example.com', '555-0101', NULL, NULL),             -- null nickname (default), missing referral
('dave@example.com', NULL, NULL, 2);                       -- null phone, null nickname, FK to row 2

Note: The generator respects NOT NULL on email and never emits NULL there. It also knows that nickname has a default empty string, so it can emit NULL or '' depending on the toggle.

4.3. Load into the sandbox

psql -d test_db -f customers_seed.sql

4.4. Validate the “nothing” landed

-- 1. Null phone count
SELECT count(*) FROM customers WHERE phone IS NULL;          -- expect >0


-- 2. Empty phone count
SELECT count(*) FROM customers WHERE phone = '';             -- expect >0


-- 3. Empty nickname (explicit)
SELECT count(*) FROM customers WHERE nickname = '';          -- expect >0


-- 4. Missing referral (FK null)
SELECT count(*) FROM customers WHERE referral_id IS NULL;    -- expect >0


-- 5. Orphan referral (FK points to non‑existent id) – should be 0 if generator respects FK
SELECT count(*) FROM customers c
LEFT JOIN customers r ON c.referral_id = r.id
WHERE c.referral_id IS NOT NULL AND r.id IS NULL;

Add these checks to your CI pipeline (e.g., as dbt test models) so a future schema change that makes phone NOT NULL will immediately surface.

---

5. Implementation Choices – Code‑Level Patterns

Below are three concrete patterns you can copy‑paste into your own test‑data library. They illustrate the same logical decisions the UI generator makes, but in code.

5.1. Factory‑Boy (Python) – Null / Empty / Missing FK

import factory
from factory.alchemy import SQLAlchemyModelFactory
from models import Customer, Session


class CustomerFactory(SQLAlchemyModelFactory):
    class Meta:
        model = Customer
        sqlalchemy_session = Session
        sqlalchemy_session_persistence = "commit"


# Required, never null
    email = factory.Sequence(lambda n: f"user{n}@example.com")


# Nullable phone – 30% null, 20% empty, 50% realistic
    phone = factory.LazyAttribute(
        lambda _: factory.random.choices(
            [None, "", factory.Faker("phone_number")],
            weights=[0.3, 0.2, 0.5]
        )[0]
    )


# Nickname – default empty string in DB, but we want explicit '' 25% of the time
    nickname = factory.LazyAttribute(
        lambda _: factory.random.choices(
            ["", factory.Faker("user_name")],
            weights=[0.25, 0.75]
        )[0]
    )


# Optional self‑referential FK – 40% missing, 60% point to existing row
    referral = factory.Maybe(
        factory.random.random() < 0.4,
        yes_declaration=factory.SubFactory("tests.factories.CustomerFactory"),
        no_declaration=None,
    )

Why it works: LazyAttribute lets you inject a distribution rather than a single value. The Maybe wrapper cleanly expresses “missing FK”.

5.2. dbt Seed + Jinja – Declarative Null/Empty Matrix

seeds/customers.yml

version: 2
seeds:
  - name: customers
    config:
      column_types:
        phone: varchar(30)
        nickname: varchar(50)
        referral_id: bigint
    rows:
      - email: "alice@example.com"
        phone: null
        nickname: ""
        referral_id: null
      - email: "bob@example.com"
        phone: ""
        nickname: "bobthebuilder"
        referral_id: 1
      - email: "carol@example.com"
        phone: "555-0101"
        nickname: null   # will become default ''
        referral_id: null
      - email: "dave@example.com"
        phone: null
        nickname: null
        referral_id: 2

Run dbt seed --select customers. The YAML is version‑controlled, reviewable, and the null/"" literals are explicit.

5.3. Go‑Faker + Custom “Missing” Helper

package testdata


import (
    "github.com/bxcodec/faker/v3"
    "math/rand"
)


type Customer struct {
    Email      string `faker:"email"`
    Phone      *string
    Nickname   string
    ReferralID *int64
}


func NewCustomer(existingIDs []int64) Customer {
    c := Customer{}
    faker.FakeData(&c)


// Phone: 30% nil, 20% empty, 50% faker
    switch r := rand.Float64(); {
    case r < 0.3:
        c.Phone = nil
    case r < 0.5:
        empty := ""
        c.Phone = &empty
    default:
        p := faker.PhoneNumber()
        c.Phone = &p
    }


// Nickname: 25% explicit empty
    if rand.Float64() < 0.25 {
        c.Nickname = ""
    }


// Referral: 40% missing, else pick random existing
    if rand.Float64() < 0.4 || len(existingIDs) == 0 {
        c.ReferralID = nil
    } else {
        id := existingIDs[rand.Intn(len(existingIDs))]
        c.ReferralID = &id
    }
    return c
}

Key point: Use pointers (*string, *int64) to differentiate NULL from empty string and missing FK.

---

6. Validation – Proving the “Nothing” Is Really There

Validation Layer	Technique	Example Assertion
Schema‑level	CHECK constraints, NOT NULL	ALTER TABLE customers ADD CONSTRAINT chk_phone_not_null CHECK (phone IS NOT NULL); – should fail for generated rows.
Data‑quality tests	dbt singular_test, Great Expectations expect_column_values_to_be_null	expect_column_values_to_be_null(column="phone", mostly=0.25)
Application‑level	Integration test that calls the service endpoint	GET /customers/123 → assert phone omitted from JSON when DB value is NULL.
Mutation testing	Flip a generated NULL to a real value and ensure a test fails	Use mutmut or pitest on the test suite.
Coverage of “missing” paths	Code‑coverage tool + custom marker	@pytest.mark.missing_fk on tests that exercise referral_id IS NULL.

Automation tip: Add a CI job named null‑empty‑missing‑validation that runs the data‑quality suite after every seed load. If the job passes, you have high confidence that the test data truly exercises the three flavors.

---

7. Common Failure Modes & How to Avoid Them

Failure Mode	Symptom	Root Cause	Mitigation
Accidental NOT NULL violation	Seed script aborts with ERROR: null value in column "email" violates not-null constraint	Generator ignored column‑level is_nullable = NO.	Keep a nullability catalogue (see §2) and feed it to the generator; add a pre‑flight SQL check: SELECT column_name FROM information_schema.columns WHERE table_name='customers' AND is_nullable='NO' AND column_default IS NULL;
Empty string treated as NULL by ORM	Application receives null for nickname even though DB stores ''.	ORM mapping uses @Column(nullable=false) + @Convert(emptyStringAsNull=true).	Explicitly test the ORM mapping; configure emptyStringAsNull=false or handle both cases in code.
Missing FK not enforced	Orphan referral_id values appear after seed.	Generator allowed any integer for FK without checking existence.	Enable FK‑aware generation (most UI generators have a “respect foreign keys” toggle). In code, pull existing IDs first (see Go example).
Default value masks explicit NULL	nickname column shows '' even when seed had NULL.	Column defined with DEFAULT ''; INSERT … (nickname) VALUES (NULL) still writes NULL, but INSERT … DEFAULT VALUES writes ''.	Be explicit: always list columns in INSERT; never rely on DEFAULT VALUES for nullable columns you want to test.
Test data drift	After a migration, old seed files still contain NULL for a column that became NOT NULL.	Seed files not regenerated after schema change.	Add a migration hook that runs the generator automatically (e.g., flyway callback afterMigrate).
Performance blow‑up	Generating 10 M rows with per‑row random choices takes >30 min.	Row‑by‑row procedural logic in SQL.

| Use set‑based generation: INSERT … SELECT … FROM generate_series(1, 10000000) ... with CASE WHEN random() < 0.3 THEN NULL …. |.

---

8. Scaling the Approach – From One Table to an Entire Schema

1. Catalogue every column (run a script that outputs table, column, is_nullable, default, fk_target).
2. Tag columns with a policy: NULL_ALLOW_NULL, ALLOW_EMPTY, ALLOW_MISSING_FK.
3. Feed the catalogue to your generator (UI or code).
4. Generate a single seed file per schema (or per bounded context).
5. **Run a global validation suite