Quality is not optional. It's our standard. Free QA tools for testers and developers.

How to Test Database Defaults, Sequences, and Auto-Increment IDs

Why Defaults, Sequences, and Auto‑Increment Columns Break Tests

When a test suite runs against a real database, the implicit behaviour of the engine often becomes the hidden source of flaky failures.
A column defined as DEFAULT now(), a SERIAL/IDENTITY column, or an Oracle‑style SEQUENCE all generate values outside the test code. If the test assumes a specific value—or assumes that the value will never change—any of the following can turn a green build red:

SituationWhat the DB doesWhy the test fails
DEFAULT CURRENT_TIMESTAMP on a created_at columnInserts the wall‑clock time of the statementTest asserts a fixed timestamp
AUTO_INCREMENT / IDENTITY primary keyIncrements a hidden counter per tableTest expects id = 1 on every run
SEQUENCE with CACHE 20Pre‑allocates a block of numbers; gaps appear after a crash or rollbackTest checks for continuous IDs
DEFAULT uuid_generate_v4()Generates a random UUIDTest compares the column to a known constant

The problem is not the feature itself—it’s the assumption that the generated value is deterministic or that the test can ignore it. A reliable test suite must explicitly verify the contract between the application and the database defaults, sequences, and auto‑increment mechanisms.


Prerequisites: What You Need Before Writing the Tests

ItemReason
Schema definition (DDL)You must know the exact DEFAULT, IDENTITY, SEQUENCE, and CACHE clauses.
Isolated test database (per‑run or per‑test)Guarantees that sequence state from one test does not leak into another.
Transaction control (savepoints or full rollback)Allows you to inspect generated values without persisting them.
Deterministic time source (e.g., clock_timestamp() vs now() in Postgres)Lets you freeze time for DEFAULT now() columns.
Test data generation helperA small utility or a tool such as QA3’s free test data generator can spin up rows with known defaults, making the test set‑up concise.
CI/CD integrationThe test must run in the same environment (same DB version, same extensions) as production.

If any of these are missing, you’ll spend more time debugging the test harness than the actual logic.


Decision Criteria: When to Test What

FeatureTest GoalRecommended Technique
Column DEFAULT (scalar, function, expression)Verify the default is applied only when the column is omitted.Insert a row without the column, read back the value, assert equality to the expected result of the default expression.
IDENTITY / AUTO_INCREMENT primary keyEnsure a unique, monotonically increasing value is produced for each insert.Insert two rows in the same transaction, capture both IDs, assert id2 > id1.
SEQUENCE (explicit NEXTVAL or DEFAULT nextval('seq'))Confirm the sequence advances correctly, respects CACHE, CYCLE, MINVALUE, MAXVALUE.Call nextval directly, then insert a row that uses the sequence as default; compare the two values.
DEFAULT uuid_generate_v4() / gen_random_uuid()Validate format (RFC‑4122) and uniqueness across inserts.Insert N rows, collect UUIDs, assert all are valid v4 UUIDs and pairwise distinct.
DEFAULT now() / CURRENT_TIMESTAMPVerify the timestamp reflects the statement start time (or transaction start, depending on DB).Freeze the clock (e.g., SET TIME ZONE 'UTC'; SELECT set_config('transaction.timestamp', '2024-01-01 00:00:00', false); in Postgres) and assert the column equals the frozen value.
Concurrency / Gap handlingDetect lost updates, duplicate keys, or unexpected gaps after rollback.Run parallel inserts in separate transactions, then inspect the sequence state (last_value, is_called).

Rule of thumb: Test the contract, not the implementation. If the schema says “id BIGSERIAL PRIMARY KEY”, the contract is “every inserted row gets a unique, non‑null, ever‑increasing id”. The test should not care whether the engine uses a sequence object, an identity column, or a hidden table.


End‑to‑End Workflow

  1. Capture the DDL – Export CREATE TABLE statements (including sequences) from source control.
  2. Spin up a clean schema – Use a migration tool (Flyway, Liquibase, or plain psql -f schema.sql) in a temporary database.
  3. Define a test data spec – A JSON/YAML file that describes the rows you need, omitting columns that have defaults.
  4. Generate rows – Feed the spec to a data‑generation utility (the QA3 generator or a custom script) which issues INSERT … RETURNING * statements.
  5. Collect generated values – The RETURNING clause gives you the exact defaults, sequence values, and timestamps the DB assigned.
  6. Assert against the contract – Write assertions that only reference the rules (uniqueness, monotonicity, format).
  7. Rollback / drop – End the transaction or tear down the temporary schema so the next test starts from a known state.

The workflow is deliberately declarative: the test describes what it expects, the generator handles how to get the DB to produce it.


Worked Example: PostgreSQL + Python (pytest + SQLAlchemy)

Below is a complete, runnable example that demonstrates the workflow for three typical cases:

  • created_at TIMESTAMPTZ DEFAULT now()
  • id BIGSERIAL PRIMARY KEY (identity)
  • event_id BIGINT DEFAULT nextval('event_seq') (explicit sequence)

Note – The code uses a transaction‑per‑test pattern (session.begin_nested() + rollback) so the database stays clean without a full DROP SCHEMA between tests.



# conftest.py


import os
import pytest
from sqlalchemy import create_engine, event
from sqlalchemy.orm import sessionmaker, Session


# ----------------------------------------------------------------------


# 1. Engine & session factory – points at a dedicated test DB


# ----------------------------------------------------------------------


TEST_DB_URL = os.getenv(
    "TEST_DB_URL",
    "postgresql+psycopg2://qa_user:qa_pass@localhost:5432/qa_test"
)


engine = create_engine(TEST_DB_URL, isolation_level="REPEATABLE READ")
TestingSession = sessionmaker(bind=engine, class_=Session, expire_on_commit=False)


# ----------------------------------------------------------------------


# 2. Schema creation – runs once per test session


# ----------------------------------------------------------------------


DDL = """
CREATE SEQUENCE IF NOT EXISTS event_seq START WITH 1 INCREMENT BY 1 NO CYCLE CACHE 20;


CREATE TABLE IF NOT EXISTS events (
    id           BIGSERIAL PRIMARY KEY,
    event_id     BIGINT NOT NULL DEFAULT nextval('event_seq'),
    payload      JSONB NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
"""


@pytest.fixture(scope="session", autouse=True)
def create_schema():
    with engine.begin() as conn:
        conn.exec_driver_sql(DDL)
    yield
    # teardown – optional, keep schema for inspection
    with engine.begin() as conn:
        conn.exec_driver_sql("DROP TABLE IF EXISTS events; DROP SEQUENCE IF EXISTS event_seq;")


# ----------------------------------------------------------------------


# 3. Per‑test transactional session (rolls back automatically)


# ----------------------------------------------------------------------


@pytest.fixture
def db_session() -> Session:
    connection = engine.connect()
    transaction = connection.begin()
    session = TestingSession(bind=connection)


# Nested transaction allows us to rollback the test work only
    nested = connection.begin_nested()


@event.listens_for(session, "after_transaction_end")
    def restart_savepoint(session_, transaction_):
        if transaction_.nested and not transaction_._parent.nested:
            session_.expire_all()
            connection.begin_nested()


yield session


session.close()
    transaction.rollback()
    connection.close()

Test Module



# test_defaults_sequences.py


import re
import uuid
from datetime import datetime, timezone
from sqlalchemy import text
from sqlalchemy.dialects.postgresql import UUID


UUID_V4_REGEX = re.compile(
    r'^[0-9a-f]{8}-[0-9a-f]{4}-4[0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$',
    re.IGNORECASE
)


def test_identity_column_increments(db_session):
    """BIGSERIAL / IDENTITY must produce a strictly increasing id."""
    # Insert two rows without providing `id`
    db_session.execute(text("INSERT INTO events (payload) VALUES ('{}'), ('{}')"))
    db_session.flush()                     # forces RETURNING evaluation
    rows = db_session.execute(text("SELECT id FROM events ORDER BY id")).fetchall()


assert len(rows) == 2
    assert rows[0][0] < rows[1][0]         # monotonicity
    assert rows[0][0] > 0                  # non‑null, positive


def test_explicit_sequence_default(db_session):
    """`event_id` uses `nextval('event_seq')`; verify the sequence contract."""
    # First, read the sequence state directly
    seq_before = db_session.execute(text("SELECT last_value, is_called FROM event_seq")).fetchone()
    # Insert a row – the DB will call nextval for us
    db_session.execute(text("INSERT INTO events (payload) VALUES ('{}')"))
    db_session.flush()


# Retrieve the generated event_id
    generated = db_session.execute(text("SELECT event_id FROM events ORDER BY id DESC LIMIT 1")).scalar()


seq_after = db_session.execute(text("SELECT last_value, is_called FROM event_seq")).fetchone()


# The sequence must have advanced exactly once
    assert seq_after[0] == seq_before[0] + 1
    assert seq_after[1] is True               # is_called flips to true after first use
    assert generated == seq_after[0]           # the column received the same value


def test_timestamp_default_is_statement_time(db_session):
    """`created_at DEFAULT now()` should reflect the statement start time."""
    # Freeze the transaction timestamp (Postgres specific)
    freeze_ts = "2024-06-15 12:34:56+00"
    db_session.execute(text("SET LOCAL transaction.timestamp = :ts"), {"ts": freeze_ts})


db_session.execute(text("INSERT INTO events (payload) VALUES ('{}')"))
    db_session.flush()


created = db_session.execute(
        text("SELECT created_at FROM events ORDER BY id DESC LIMIT 1")
    ).scalar()


# The column must match the frozen timestamp (truncated to microseconds)
    assert created.replace(tzinfo=timezone.utc) == datetime.fromisoformat(freeze_ts)


def test_uuid_default_format_and_uniqueness(db_session):
    """If a column uses `gen_random_uuid()`, verify RFC‑4122 v4 format and uniqueness."""
    # Add a UUID column for this test only (ALTER TABLE in a transaction)
    db_session.execute(text("ALTER TABLE events ADD COLUMN IF NOT EXISTS trace_id UUID DEFAULT gen_random_uuid()"))
    db_session.flush()


# Insert 50 rows, collect UUIDs
    db_session.execute(text("INSERT INTO events (payload) SELECT '{}' FROM generate_series(1, 50)"))
    db_session.flush()


uuids = [row[0] for row in db_session.execute(text("SELECT trace_id FROM events")).fetchall()]


# All must be valid v4 UUIDs
    assert all(UUID_V4_REGEX.match(str(u)) for u in uuids)
    # All must be distinct
    assert len(set(uuids)) == len(uuids)

What the Example Shows

TestContract VerifiedTechnique
test_identity_column_incrementsid is unique, monotonic, non‑nullINSERT … RETURNING + ordering
test_explicit_sequence_defaultSequence advances exactly once per row, column receives the same valueDirect SELECT on sequence metadata (last_value, is_called)
test_timestamp_default_is_statement_timenow() reflects statement start (not transaction start)SET LOCAL transaction.timestamp (Postgres)
test_uuid_default_format_and_uniquenessUUID column yields valid v4 UUIDs, no collisionsBulk insert + regex + set cardinality

All tests run inside a single transaction that is rolled back, so the sequence state, table data, and even the ALTER TABLE are invisible to subsequent tests. This pattern works on any DB that supports savepoints (PostgreSQL, SQL Server, Oracle, MySQL 8+).


Validation Checklist – Run This Before Merging

  • Schema version used by the test matches the migration applied in CI.
  • Isolation level is at least READ COMMITTED; REPEATABLE READ or SERIALIZABLE avoids phantom reads when checking sequence state.
  • Transaction rollback is guaranteed (no stray COMMIT in test code).
  • Deterministic time is set for any DEFAULT now() / CURRENT_TIMESTAMP columns.
  • Sequence metadata (last_value, is_called, cache_size) is inspected after each insert, not assumed.
  • Concurrency test (optional) – fire 10 parallel inserts in separate connections, then assert no duplicate keys and that the sequence advanced by exactly 10.
  • Cache‑gap awareness – if the sequence uses CACHE > 1, the test must not assert “no gaps”. Gaps are legal.
  • UUID format – validate with a strict regex or a library (uuid.UUID(..., version=4)).
  • Cleanup – any ALTER TABLE … ADD COLUMN performed in a test is rolled back; otherwise, drop the column in a finally block.

If any item fails, the test suite is not trustworthy for defaults/sequences.


Common Failure Modes & How to Detect Them

Failure ModeSymptomDetection Strategy
Sequence cache loss after crashAfter a hard kill, the next nextval jumps by CACHE size.Simulate pg_ctl stop -m immediate (or SHUTDOWN ABORT on Oracle) in a test container, then insert a row and compare the jump.
Identity column reset by TRUNCATE … RESTART IDENTITYSubsequent inserts start at 1 again, breaking foreign‑key expectations.Include a test that runs TRUNCATE events RESTART IDENTITY; then inserts a row; assert the new id == 1.
DEFAULT now() evaluated at transaction start (Postgres now() vs statement_timestamp())All rows in a multi‑statement transaction share the same timestamp.Insert two rows in the same transaction without freezing time; assert the timestamps differ (they

Read more

GraphQL Test Data Generation for Queries and Mutations

A practical guide to “GraphQL Test Data Generation for Queries and Mutations,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.

Postman Test Data Generation with CSV and JSON Files

A practical guide to “Postman Test Data Generation with CSV and JSON Files,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.

Generate API Test Data Directly from an OpenAPI Specification

A practical guide to “Generate API Test Data Directly from an OpenAPI Specification,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.