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:
| Situation | What the DB does | Why the test fails |
|---|---|---|
DEFAULT CURRENT_TIMESTAMP on a created_at column | Inserts the wall‑clock time of the statement | Test asserts a fixed timestamp |
AUTO_INCREMENT / IDENTITY primary key | Increments a hidden counter per table | Test expects id = 1 on every run |
SEQUENCE with CACHE 20 | Pre‑allocates a block of numbers; gaps appear after a crash or rollback | Test checks for continuous IDs |
DEFAULT uuid_generate_v4() | Generates a random UUID | Test 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
| Item | Reason |
|---|---|
| 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 helper | A 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 integration | The 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
| Feature | Test Goal | Recommended 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 key | Ensure 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_TIMESTAMP | Verify 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 handling | Detect 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
- Capture the DDL – Export
CREATE TABLEstatements (including sequences) from source control. - Spin up a clean schema – Use a migration tool (Flyway, Liquibase, or plain
psql -f schema.sql) in a temporary database. - Define a test data spec – A JSON/YAML file that describes the rows you need, omitting columns that have defaults.
- Generate rows – Feed the spec to a data‑generation utility (the QA3 generator or a custom script) which issues
INSERT … RETURNING *statements. - Collect generated values – The
RETURNINGclause gives you the exact defaults, sequence values, and timestamps the DB assigned. - Assert against the contract – Write assertions that only reference the rules (uniqueness, monotonicity, format).
- 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 fullDROP SCHEMAbetween 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
| Test | Contract Verified | Technique |
|---|---|---|
test_identity_column_increments | id is unique, monotonic, non‑null | INSERT … RETURNING + ordering |
test_explicit_sequence_default | Sequence advances exactly once per row, column receives the same value | Direct SELECT on sequence metadata (last_value, is_called) |
test_timestamp_default_is_statement_time | now() reflects statement start (not transaction start) | SET LOCAL transaction.timestamp (Postgres) |
test_uuid_default_format_and_uniqueness | UUID column yields valid v4 UUIDs, no collisions | Bulk 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 READorSERIALIZABLEavoids phantom reads when checking sequence state. - Transaction rollback is guaranteed (no stray
COMMITin test code). - Deterministic time is set for any
DEFAULT now()/CURRENT_TIMESTAMPcolumns. - 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 COLUMNperformed in a test is rolled back; otherwise, drop the column in afinallyblock.
If any item fails, the test suite is not trustworthy for defaults/sequences.
Common Failure Modes & How to Detect Them
| Failure Mode | Symptom | Detection Strategy |
|---|---|---|
| Sequence cache loss after crash | After 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 IDENTITY | Subsequent 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.