Full-Stack API, Database, and Data Testing

Introduction

Modern web applications are not tested adequately by checking whether a page renders. A request travels through several layers: a client constructs an HTTP or GraphQL request, authentication middleware identifies the caller, application services apply business rules, a database enforces constraints and persists data, and a response is serialized back to the client. A defect may therefore appear at a different layer from its cause. For example, a missing database constraint may eventually appear as an incorrect API response, while an API authorization defect may expose valid but unauthorized database records.

This chapter presents a systematic approach to testing REST and GraphQL interfaces, authentication and authorization, schemas, status codes, errors, idempotency, databases, transactions, migrations, data integrity, and cross-layer behavior. The emphasis is on representative, deterministic, privacy-preserving test data and on tests that provide evidence about observable system behavior rather than merely increasing code coverage.

Learning Objectives

By the end of this chapter, a student should be able to:

1. Distinguish unit, integration, contract, end-to-end, and data-layer tests.
2. Design tests for REST and GraphQL requests, schemas, status codes, errors, and idempotency.
3. Test authentication, authorization, token expiry, and object-level access control.
4. Verify database constraints, transactions, migrations, indexes, and data integrity.
5. Construct safe, representative test fixtures and control test data life cycles.
6. Diagnose failures that cross API, service, and database boundaries.
7. Evaluate reliability, maintainability, security, and governance implications of a test strategy.

1. A Layered Model of Full-Stack Testing

A useful model separates the system into five testable concerns:

A unit test isolates a small component, often replacing collaborators with test doubles. An integration test exercises a meaningful boundary, such as an application service with a real database. An end-to-end test uses the deployed application through its public interface. These categories are complementary. A unit test can identify a faulty branch quickly, but it cannot prove that an ORM mapping, database constraint, serialization rule, and authorization policy work together.

Testing should be organized around risk. A payment endpoint, for example, deserves stronger testing of authorization, idempotency, transaction boundaries, and audit records than a low-risk display preference. The objective is not to test every possible string or database row; it is to establish justified confidence in important behavior.

2. REST API Testing

REST testing examines resources, representations, HTTP methods, headers, status codes, and state transitions. A test should specify the initial state, request, expected protocol result, expected body, and expected persistent state.

2.1 Methods and status codes

The commonly used methods have different semantic expectations:

• GET retrieves a representation and should not intentionally change server state.
• POST commonly creates a subordinate resource or requests an operation. Repeating it may create multiple resources unless the API defines idempotency handling.
• PUT replaces a resource representation and is conventionally idempotent: repeating the same valid request should produce the same intended state.
• PATCH partially updates a resource. Idempotency depends on the operation; setting a field is usually idempotent, whereas “increment by one” is not.
• DELETE removes or marks a resource as deleted. Repeating it should be defined explicitly, often as the same final state even if the second response differs.

Status codes communicate the result but do not replace a useful response body. Typical assertions include:

A robust test does not assert only the status code. It checks content type, required and optional fields, field types, error structure, pagination metadata, caching headers where relevant, and whether the database changed appropriately.

2.2 Schema and contract testing

A schema defines the shape and constraints of messages. OpenAPI and JSON Schema can express required properties, formats, enumerations, bounds, and additional-property policies. Contract tests detect drift between an API implementation and its documented interface.

Schema tests should include valid boundary values and invalid values: omitted required fields, null values, wrong types, excessive lengths, unknown enumeration values, malformed identifiers, and unexpected properties. A schema is not a complete business specification. “Quantity is an integer greater than zero” may be expressible in JSON Schema, while “quantity cannot exceed available inventory” requires application behavior and often database coordination.

Consumer-driven contract testing is useful when one service depends on another. A consumer records the response shape and semantics it requires; the provider verifies that it continues to satisfy those expectations. This reduces integration surprises but must be supplemented by broader tests for workflows, security, and persistence.

3. GraphQL Testing

GraphQL exposes a schema rather than a collection of resource URLs. Clients select fields through queries and invoke state-changing mutations. Testing therefore focuses on schema validity, resolver behavior, authorization at field and object levels, nullability, error paths, and query complexity.

A GraphQL test should verify that:

1. Valid queries return the requested fields with the documented types.
2. Invalid fields, arguments, and variable types are rejected before inappropriate resolver execution.
3. Mutations enforce business rules and return the correct selection-set data.
4. Resolver errors appear in the errors structure and do not silently produce misleading data.
5. Non-null fields behave consistently: an error may propagate nullability upward according to the schema.
6. Authorization is applied to every relevant object and field, not merely to the top-level operation.
7. Pagination, filtering, sorting, and maximum query depth or cost are controlled.

GraphQL can make over-fetching less likely, but it can also enable expensive nested queries. Tests should include deeply nested requests, repeated aliases, large page sizes, and requests that could trigger N+1 database access. A data loader or batching mechanism may reduce repeated queries, but tests should verify both output correctness and acceptable query behavior.

4. Authentication, Authorization, and Security Tests

Authentication answers “Who is the caller?” Authorization answers “What may this caller do?” These must be tested separately. A valid token does not imply permission to access every resource.

A basic authentication test matrix includes:

Object-level authorization is especially important. A test that changes an identifier in the URL or request body must confirm that the server checks ownership or tenant scope rather than trusting the identifier. Tests should also cover mass assignment, where a client submits fields such as role, ownerId, or isAdmin that should be server-controlled.

Security tests must avoid real credentials and production personal data. Secrets belong in a secret manager or protected test configuration, not source control or logs. Error responses should provide clients with actionable categories without disclosing stack traces, SQL statements, token contents, or private records.

5. Errors, Invariants, and Idempotency

A high-quality API has predictable errors. A structured error commonly contains a stable code, human-readable message, and optional field or correlation information. Tests should verify that equivalent failures have consistent structure and that validation failures identify safe, relevant fields.

An invariant is a condition that must remain true. Examples include “an order belongs to exactly one customer,” “an email address is unique within a tenant,” and “a payment cannot be captured twice.” Invariants should be protected at the strongest practical layer. Application validation improves usability, but database constraints are essential protection against concurrent requests and defects in other code paths.

Idempotency concerns repeated requests. If an operation is idempotent, sending the same request multiple times should have the same intended effect as sending it once. For a create-payment endpoint, a client may supply an idempotency key. The server records the key and the resulting outcome, then returns the original outcome for a safely repeated request. Tests should cover:

• same key and same request: one effect and a repeatable response;
• same key and different request: explicit conflict;
• different keys: independent operations;
• timeout followed by retry: no duplicate business effect;
• concurrent requests with the same key: one committed result;
• expired keys: behavior defined by retention policy.

Idempotency is not achieved merely by checking a key in application memory. The key and relevant scope normally require a unique database constraint and a transactionally safe implementation.

6. Database Testing

Database tests evaluate both the stored state and the mechanisms that protect it.

6.1 Constraints and integrity

Important constraints include:

• NOT NULL, for mandatory values;
• UNIQUE, for identifiers or scoped business values;
• PRIMARY KEY, for row identity;
• FOREIGN KEY, for valid relationships;
• CHECK, for domain conditions such as non-negative quantities;
• appropriate data types, precision, collation, and character handling.

A test should attempt invalid operations and confirm both the failure and the absence of unintended partial changes. For example, inserting an order line for a nonexistent product should fail through a foreign-key constraint or a deliberate application error, and no orphan row should remain.

Application-level validation and database constraints are not substitutes. Application validation gives clear feedback before a write; database constraints provide concurrency-safe enforcement. The database should not be expected to encode every business workflow, but critical structural invariants should not exist only in application code.

6.2 Transactions and isolation

A transaction groups operations into an atomic unit. The familiar ACID properties are atomicity, consistency, isolation, and durability. A test for an order placement operation might verify that order creation, inventory reservation, and audit recording either all commit or all roll back.

Tests should deliberately inject a failure after an intermediate write. The expected result is that no partial state remains. Concurrency tests should also examine duplicate submissions, lost updates, and overselling. The exact behavior depends on the database engine and isolation level; therefore, tests must document the chosen semantics rather than assume that every system prevents every anomaly.

A practical pattern is to use a real database engine in integration tests, provision an isolated schema or container, and reset it deterministically. An in-memory substitute may be fast but can differ in SQL semantics, constraints, transaction behavior, indexing, and null handling.

6.3 Migrations

A migration changes the database schema in a controlled, versioned sequence. Migration tests should verify:

1. a new database can be created from the complete migration history;
2. an existing representative database upgrades successfully;
3. expected tables, columns, indexes, constraints, and defaults exist;
4. existing data is preserved or transformed according to an explicit rule;
5. application versions are compatible during rolling deployment when required;
6. rollback or forward-fix procedures are understood.

Destructive changes deserve particular caution. A safer deployment may add a nullable column, backfill it, deploy code that writes both representations, verify the backfill, and only later remove the old column. Tests should include large enough fixtures to reveal inefficient backfills and lock behavior.

6.4 Data integrity and reconciliation

Data integrity includes accuracy, completeness, consistency, and traceability. Tests can compare API-visible totals with database-derived totals, verify that foreign-key relationships are complete, and check that timestamps, currencies, and identifiers preserve their intended meaning. Reconciliation tests are valuable for reporting and financial workflows, but they must account for legitimate states such as pending or reversed transactions.

7. Test Data Engineering

Test data is part of test design. A useful fixture is representative of production structure without reproducing production identity. Data should be:

• deterministic, so failures can be reproduced;
• minimal, so the cause is visible;
• representative, including boundaries, optional fields, and realistic relationships;
• isolated, so tests do not depend on execution order;
• safe, with synthetic or irreversibly anonymized values;
• disposable, with a defined cleanup or expiration process.

Factories can generate related objects while allowing deliberate overrides. Builders make scenarios readable. Seed data may be suitable for local development, but tests should not silently depend on a large shared database. Each test should establish its own relevant preconditions.

Boundary data includes zero, one, maximum permitted values, empty collections, Unicode text, time-zone transitions, duplicate values, and missing optional fields. Randomized or property-based testing can explore broader input spaces, but failures require recorded seeds and stable shrinking or reduction procedures.

Parallel tests require isolation. Options include separate databases, schemas, tenant identifiers, transaction rollbacks, or unique resource namespaces. Cleanup must run even after failure, and test environments must not point accidentally to production.

8. Cross-Layer Testing Strategy

A cross-layer test traces one business behavior from request to persistence and back. Consider creating an account through an API. The test may verify authentication, request validation, password handling, uniqueness, database persistence, response serialization, and subsequent login behavior. This is more informative than separately asserting that a controller returns 201 and that a repository inserts a row.

A balanced strategy uses many fast unit tests, a smaller set of integration tests with real infrastructure, contract tests at service boundaries, and a focused set of end-to-end tests for high-value journeys. End-to-end tests should not attempt to cover every validation branch; lower-level tests can do that more efficiently.

Test failures should be classified by layer. Useful diagnostic evidence includes request correlation IDs, sanitized request and response metadata, database transaction logs in non-production environments, migration version, service version, and test-data identifiers. Logs must be designed so that diagnostic value does not become a privacy breach.

Worked Example: Testing an Order API

Assume an order service exposes POST /orders, accepts product lines, and reserves inventory. The business invariant is that a product cannot be reserved beyond available stock.

A representative scenario begins with a customer, a product with a stock quantity of 5, and no existing order. The client submits two units. The API test expects a successful creation response, an order identifier, and a total computed from server-controlled product pricing. A database assertion verifies one order, one order line, and stock reduced to 3.

The following negative tests are required:

1. quantity zero or negative: validation failure and unchanged stock;
2. quantity six: business failure and unchanged stock;
3. nonexistent product: failure and no order line;
4. altered unit price in the request: ignored or rejected, never trusted;
5. duplicate submission with the same idempotency key: one order and repeatable result;
6. database failure after order insertion but before stock update: transaction rollback;
7. customer token for another tenant: rejection with no cross-tenant disclosure.

A concurrency test starts two requests, each reserving four units from stock of five. The expected invariant is that at most one request reserves four units, or that the system applies a documented allocation policy. The test must use synchronization so that the requests overlap; sequential requests do not reveal a race condition. The result is assessed through committed database state, not merely response codes.

Illustrative Case Study: Campus Equipment Lending Service

Background. A university develops a service through which students reserve cameras and laptops. The service has a REST API for mobile clients and a GraphQL API for administrative dashboards. Equipment belongs to departments, and students may view only their own reservations.

Problem. During a pilot, two students occasionally reserved the same camera. An administrator also reported that a GraphQL query exposed another department’s equipment name. The original tests covered successful requests but used mocks for the database and a shared development dataset.

Analysis. Investigation identified three weaknesses. First, availability was checked in application code and then updated in a separate database operation. Second, the GraphQL resolver authorized the reservation object but not a nested equipment field. Third, tests did not isolate departments or run concurrent requests.

Approach. The team added a database-enforced reservation rule appropriate to the booking interval model, enclosed availability validation and reservation creation in one transaction, and added a concurrency test with two overlapping requests. Department scope became an explicit authorization condition in every repository query and resolver. The team replaced shared fixtures with synthetic factories, added schema contract tests, and introduced a migration test from the previous release.

Outcome. Sequential functional behavior remained unchanged, while concurrent tests now produced one successful reservation and one defined conflict. The GraphQL field test rejected cross-department data. Migration tests detected a missing index before release, and sanitized fixtures made local reproduction possible without exposing student information.

Learning points. The case demonstrates that a response-level test cannot prove a concurrency invariant, that authorization must be applied at the data-access boundary as well as the route boundary, and that realistic isolation is essential for discovering cross-layer defects.

9. Limitations, Governance, and Contemporary Practice

No test suite proves the absence of all defects. Test environments differ from production in scale, network behavior, data distribution, extensions, and operational configuration. Mocks can conceal integration failures, while end-to-end tests can be slow and brittle. Coverage percentage is therefore a diagnostic signal, not a quality guarantee.

API and database tests may process personal, educational, health, or payment-related data. Governance requires data minimization, access control, retention limits, auditability, encryption where appropriate, and compliance with applicable privacy and security requirements. Test reports and CI artifacts can leak secrets or personal data even when the application itself is secure.

AI-assisted tools can generate test cases, identify untested branches, synthesize synthetic data, and summarize failures. Human review remains necessary because generated tests may encode incorrect assumptions, reproduce sensitive data, miss authorization boundaries, or assert implementation details rather than behavior. AI output should be treated as a proposed test artifact subject to the same review, security, and licensing controls as other code.

Best practice is to make tests deterministic, independent, observable, and proportionate to risk; use real infrastructure where semantics matter; verify both response and state; enforce critical invariants in the database; test authorization negatively; and preserve reproducible evidence for every failure.

Chapter Summary

Full-stack testing follows behavior across API, application, database, and data boundaries. REST tests examine methods, schemas, headers, status codes, errors, and resource state; GraphQL tests additionally examine schema selection, resolver errors, nullability, authorization, and query complexity. Authentication and authorization require distinct tests, especially for object ownership and tenant isolation. Idempotency must be tested under retries and concurrency and usually requires durable uniqueness controls.

Database testing verifies constraints, transactions, isolation behavior, migrations, indexes, and integrity. Safe test-data engineering uses deterministic, synthetic, isolated, representative fixtures. Cross-layer tests validate complete business outcomes, while unit and contract tests provide speed and boundary confidence. No single metric or test level is sufficient: a risk-based combination gives stronger evidence while controlling cost, brittleness, privacy exposure, and maintenance effort.

Key Terms

API: A defined interface through which software components communicate.

Authentication: Verification of a caller’s identity.

Authorization: Determination of what an authenticated caller may access or perform.

Contract testing: Verification that an implementation conforms to an agreed interface.

Cross-layer test: A test that follows behavior across multiple application layers.

Database constraint: A database rule that restricts invalid stored state.

Fixture: Controlled data established for a test.

GraphQL resolver: Code that obtains or computes a field’s value.

Idempotency: Property whereby repetition of an operation has the same intended effect as one execution.

Integration test: Test of collaboration across a meaningful software or infrastructure boundary.

Invariant: A condition that must remain true throughout valid operation.

Migration: A versioned change to database structure or data.

Mock: A test double that simulates a collaborator and may verify interactions.

N+1 query problem: Excessive repeated database queries caused by nested data retrieval.

Schema: Formal description of valid data structure and constraints.

Status code: HTTP response code communicating the broad result of a request.

Test isolation: Prevention of interference between tests.

Transaction: Atomic unit of database work with defined consistency and durability behavior.

Review Questions

Short Answer Questions

1. How do authentication and authorization differ?
2. Why should critical invariants be protected by database constraints as well as application validation?
3. What information should a REST API test verify besides the HTTP status code?
4. Why can an in-memory database produce misleading integration-test results?
5. What makes test data safe and representative?

Long Answer Questions

1. Explain how to design a balanced test strategy using unit, integration, contract, and end-to-end tests.
2. Describe a comprehensive test plan for a GraphQL mutation that creates a reservation.
3. Explain how transactions and concurrency tests can expose overselling or duplicate-booking defects.
4. Discuss the role of migrations, backfills, and compatibility during a rolling deployment.
5. Evaluate the privacy, security, and governance risks of using production-derived data in test environments.

Analytical / Application Questions

1. An API returns 201 for two simultaneous payment requests carrying the same idempotency key, and two payment rows are stored. Identify the likely design weakness and propose a corrected test and implementation approach.
2. A GraphQL administrator query hides a reservation object but reveals its nested equipment name. Analyze the authorization failure and specify where authorization checks should be applied.
3. A migration succeeds on an empty database but fails on a populated staging database because an old column contains nulls. Design a phased migration and the tests needed before deployment.

Multiple Choice Questions with Answers

Question 1

Which test most directly verifies that an API implementation matches its documented response structure?

A. Contract test

B. Load test

C. Mutation test

D. Visual regression test

Correct Answer: A. Contract test

Question 2

What is the primary distinction between a 401 response and a 403 response?

A. 401 indicates malformed JSON, while 403 indicates a missing field

B. 401 concerns failed or absent authentication, while 403 concerns insufficient permission

C. 401 is used only for database errors, while 403 is used only for timeouts

D. 401 indicates success, while 403 indicates resource creation

Correct Answer: B. 401 concerns failed or absent authentication, while 403 concerns insufficient permission

Question 3

Which control most reliably prevents two concurrent requests from creating the same supposedly unique record?

A. A client-side validation message

B. A database uniqueness constraint used with appropriate transaction handling

C. A longer HTTP timeout

D. A GraphQL fragment

Correct Answer: B. A database uniqueness constraint used with appropriate transaction handling

Question 4

A test repeats the same payment request after a timeout and expects only one payment effect. Which property is being tested?

A. Pagination

B. Nullability

C. Idempotency

D. Serialization

Correct Answer: C. Idempotency

Question 5

Which test is most appropriate for detecting partial persistence after a failure between two database writes?

A. A transaction rollback integration test

B. A CSS snapshot test

C. A unit test of a password formatter

D. A browser compatibility test

Correct Answer: A. A transaction rollback integration test

Question 6

Why should GraphQL tests include deeply nested and high-volume queries?

A. To verify that HTML is rendered correctly

B. To detect query-complexity and excessive resolver/database work

C. To replace all authorization tests

D. To ensure every query returns a 500 response

Correct Answer: B. To detect query-complexity and excessive resolver/database work

Question 7

Which fixture characteristic most directly supports reproducibility?

A. Dependence on the order in which tests execute

B. Use of live production records

C. Deterministic values and controlled setup

D. Random deletion of records during execution

Correct Answer: C. Deterministic values and controlled setup

Question 8

What is the main purpose of a foreign-key constraint?

A. To enforce valid relationships between related rows

B. To encrypt database backups

C. To choose an HTTP status code

D. To limit GraphQL query depth

Correct Answer: A. To enforce valid relationships between related rows

Question 9

A PATCH operation sets a user's preferred language to 'en'. Repeating the identical request should normally produce which behavior?

A. The language should alternate between two values

B. The final intended state should remain the same

C. A new user should be created each time

D. The database must be deleted

Correct Answer: B. The final intended state should remain the same

Question 10

Which statement best describes test coverage percentage?

A. It proves that no production defects remain

B. It is a useful signal but does not establish behavioral or security completeness

C. It measures only database storage capacity

D. It replaces contract and integration tests

Correct Answer: B. It is a useful signal but does not establish behavioral or security completeness