Skip to content
Tutorials Center
My learning

Topics

Lesson 39 of 60 ยท Manual Testing in Practice

API and Database Checks by Hand

Check an API by hand: HTTP methods, status codes and JSON bodies. Then verify what was stored with SQL. Includes a mock API and a data reconciliation stand-in.

A screen shows only part of what a system does. Behind the buttons sit APIs that carry requests and answers between components, and databases that keep the truth. A tester who can send a request by hand and look inside the database can answer questions the UI hides: did the server really reject that bad input, was the order saved once or twice, does the stored value match what the screen shows?

You do not need to be a developer for this. You need to read a request, understand a status code, follow a bit of JSON, and write a simple SELECT query. This lesson gives you those skills and two stand-ins that run in your browser. Automating API checks properly is the topic of the Postman API testing track; here we focus on the manual mindset.

Check below the surface

Request Method, address, headers, body.
Response Status code, headers, JSON body.
Database What was actually stored.
Compare Does screen, API and data agree?
1 / 2

What a request looks like

An HTTP request has four parts: a method (what to do), a URL (to what), headers (extra information such as the content type or an access token) and an optional body. The server replies with a status code, headers and usually a body, most often JSON.

The common methods and what to expect from them:

Method Meaning Typical success code
GET Read something; must not change data 200 OK
POST Create something 201 Created
PUT / PATCH Replace or partly update something 200 OK or 204 No Content
DELETE Remove something 204 No Content (or 200)

Status codes come in families, and the first digit tells you the story:

  • 2xx success: 200 OK, 201 Created, 204 No Content.
  • 3xx redirection: the thing moved.
  • 4xx the client did something wrong: 400 Bad Request (invalid input), 401 Unauthorized (not signed in), 403 Forbidden (signed in but not allowed), 404 Not Found, 405 Method Not Allowed, 409 Conflict, 422 Unprocessable Content (valid format, invalid meaning), 429 Too Many Requests.
  • 5xx the server failed: 500 Internal Server Error, 502/503/504 gateway or availability problems.

A 5xx response to bad user input is almost always a defect: the server should answer a bad request with a 4xx, not crash. This is one of the highest-value things you can check by hand.

Real tools for sending requests include curl on the command line, a browser's developer tools (Network tab) and API clients such as Postman. A request with curl looks like this (read-only; it needs a real server):

Terminal
curl -i -X POST https://api.example.test/users \
  -H "Content-Type: application/json" \
  -d '{"name": "Grace", "email": "[email protected]"}'

The -i flag prints the status line and headers along with the body. Never test against a production system without permission, and never put real customer data in your examples.

Reading JSON

Most API bodies are JSON: objects in curly braces, lists in square brackets, and values that are strings, numbers, true/false or null.

JSON
{
  "id": 2,
  "name": "Grace",
  "email": "[email protected]",
  "roles": ["customer"],
  "address": null
}

When you read a response, check:

  • Structure: all expected fields are present, and no unexpected ones (passwords, internal ids, other users' data).
  • Types: "id": "2" versus "id": 2 can break clients. A price as a string, or a date in an inconsistent format, are classic defects.
  • Values: null where there should be data, empty lists, and rounding of money.
  • Consistency: the same field is named the same way in every endpoint.

An API you can poke at

A real API needs a server. The stand-in below is a tiny in-memory "users API" written as one function, so you can see how a handler decides on a status code. It is not a real network service, but its behavior matches what you would test: valid reads, a missing user, creation, invalid input, repeated delete, a wrong method and an unknown path.

JS Calling a mock users API

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Read the output as a tester. The second DELETE of user 2 returns 404 because the user is already gone, which is reasonable (some APIs return 204 again, to make deletes "idempotent"; both are defensible, but they should be documented and consistent). The POST without an email returns 400 with a helpful message. Try adding checks of your own: a name of 10,000 characters, a duplicate email, a negative id. Each one is an input from Equivalence Partitioning and Boundary Value Analysis applied to an API.

What to check on every endpoint

  • Happy path: correct status, body and headers.
  • Validation: missing, wrong-type, too-long and malformed fields give 4xx errors with clear messages, never 500.
  • Authentication and authorization: no token gives 401; a valid token for the wrong user gives 403, and you cannot read or change another user's data by changing an id in the URL.
  • Not found and repeated actions: unknown ids, deleting twice, creating twice.
  • Side effects: a GET changes nothing; a POST creates exactly one record; a failed request leaves nothing half-done.
  • Limits: pagination, large payloads, and rate limiting (429).
  • Consistency with the UI: what the page shows matches the response.

Verifying data with SQL

The database is the place where the final answer lives. After an action in the UI or API, you often want to confirm what was stored. For that you only need a few read-only queries.

SQL
-- Was the order saved once, with the right total?
SELECT id, customer_id, total_cents, status
FROM orders
WHERE customer_id = 7
ORDER BY id;

-- Are there duplicates that should not exist?
SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Orders without a matching customer (orphans)?
SELECT o.id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

If these look unfamiliar, the SQL track's lessons on SELECT, GROUP BY and LEFT JOIN cover them in depth, with queries you can run.

Rules of the road for testers working with a database:

  • Use a test database. Do not run queries against production unless your role and process explicitly allow it.
  • Prefer read-only access. A SELECT is safe; an UPDATE or DELETE without a precise WHERE is how data gets destroyed. If you must change test data, double-check the filter first.
  • Remember privacy: customer data in a test database should be masked (see Test Data and Test Environments).
  • Check more than "a row exists": values, types, rounding, time stamps (in which zone?), foreign keys, and that nothing was left behind after a failed request.

Database testing

Checking a value in the database after a form submit is the start. Database testing is a discipline in its own right: it verifies the data layer's structure, its rules and its behaviour under change and load. It usually combines grey-box knowledge (you see the schema) with SQL.

What to test

Structural testing (the schema):

  • Tables, columns, data types, lengths and defaults match the design.
  • Constraints exist and work: primary keys, foreign keys, unique, not-null, check constraints.
  • Indexes exist for the queries that need them.
  • Views, stored procedures, functions and triggers are present, with the expected parameters.
  • Migrations apply on a clean database and on a copy of production, and can be rolled back.

Functional testing (the data behaves):

  • CRUD operations from the application write, read, update and delete the right rows, and only those rows.
  • Business rules in the database (a trigger that updates stock, a stored procedure computing interest) give correct results, including boundary values.
  • Data mapping: what the screen shows equals what is stored, with correct formatting, rounding and time zones.
  • Referential integrity: deleting a parent row either cascades, is refused, or sets child keys null, as designed. No orphan rows.
  • Duplicates and nulls: unique rules hold; optional fields handle empty values.
  • Transactions (see ACID below), including what happens when a step fails half way.
  • Data migration and ETL: counts, sums and spot-checked rows match between source and target; nothing is truncated or mis-encoded.

Non-functional testing:

  • Performance: slow queries, missing indexes, behaviour with large volumes.
  • Security: least-privilege accounts, SQL injection resistance, encrypted sensitive data, no real personal data in test copies (masking).
  • Concurrency: parallel updates, locking, deadlocks (Concurrency and Stability Testing).
  • Backup and recovery: a restore actually works (Reliability and Recovery).

ACID in one minute

A reliable transaction is ACID:

  • Atomicity: all steps happen, or none do.
  • Consistency: the database moves from one valid state to another; constraints are never left violated.
  • Isolation: concurrent transactions do not see each other's half-finished work.
  • Durability: once committed, the result survives a crash.

Test atomicity by forcing a failure in the middle of a multi-step operation (such as transferring money: subtract from A, add to B) and confirm that neither side changed. Test isolation with two simultaneous sessions.

JS Test atomicity and a foreign-key rule

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Checking data with SQL

The SQL lessons in the SQL tutorial help here. Useful checks:

SQL
-- orphan rows: orders whose customer does not exist
SELECT o.id FROM orders o LEFT JOIN customers c ON c.id = o.customer_id WHERE c.id IS NULL;

-- duplicates that a unique rule should prevent
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

-- migration check: row counts and sums must match source and target
SELECT COUNT(*), SUM(amount) FROM payments;

Always run such queries against a test copy, never against production, and use read-only accounts where possible.

API testing in more depth: status codes, authentication and contracts

Status codes you must know

The status code is the first thing to assert. Check not just that a code exists but that it is the right one for the situation.

Range Meaning Common codes and when to expect them
1xx Informational Rare in everyday testing
2xx Success 200 OK (read or update done), 201 Created (new resource, with a Location header), 202 Accepted (queued for later), 204 No Content (success, empty body, typical for DELETE)
3xx Redirection 301/308 permanent move, 302/307 temporary, 304 Not Modified (cache still valid)
4xx The client made a mistake 400 Bad Request (malformed input), 401 Unauthorized (not authenticated, or the credentials are wrong), 403 Forbidden (authenticated but not allowed), 404 Not Found, 405 Method Not Allowed, 409 Conflict (duplicate, or edit conflict), 415 Unsupported Media Type, 422 Unprocessable Content (valid syntax, invalid meaning), 429 Too Many Requests (rate limit)
5xx The server failed 500 Internal Server Error, 502/503/504 gateway, unavailable, timeout

Typical bugs this finds: a validation error that returns 500 instead of 400 or 422 (the server crashed instead of rejecting), a missing resource that returns 200 with an empty body, a forbidden action that returns 404 or 200, and an error body that leaks a stack trace. Also test methods (GET, POST, PUT, PATCH, DELETE) on each URL, idempotency (repeating a PUT or DELETE has the same effect; a retried POST should not create two orders), and headers such as Content-Type, cache headers and CORS.

Authentication and authorization

  • Authentication answers "who are you?" and authorization "what may you do?". Test both, separately.
  • Common schemes: Basic (username and password in a header, only over HTTPS), API keys, Bearer tokens such as JWT (a signed token carrying claims and an expiry), OAuth 2.0 (delegated access with scopes) and session cookies.
  • Test cases: no token (expect 401), expired token, tampered token, token for another user, a token without the required scope or role (expect 403), and whether logout or password change revokes tokens.
  • Test object-level authorization: user A requests /orders/123 that belongs to user B. This is the most common serious API flaw, and an authentication check alone never finds it (see Security Testing Basics).
  • Never log or paste real secrets into bug reports.
JS Check status codes against expectations

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Contract testing

An API is a promise between a provider (the service) and its consumers (a web app, a mobile app, other services). The promise is the contract: URLs, methods, request fields, response fields, types, status codes and errors. A change that breaks it, such as renaming a field, breaks consumers even though the provider's own tests pass.

Contract testing checks that both sides keep the promise, without running the whole system:

  • Schema-based: an OpenAPI (Swagger) or JSON Schema description is the contract; tests validate real responses against it, and requests against it too.
  • Consumer-driven contracts: each consumer records what it actually needs (fields and types it reads); the provider's pipeline replays those expectations and fails if a change would break any consumer. Pact is the best-known tool.
  • Provider verification and mock servers: consumers test against a mock generated from the contract while the provider verifies it can satisfy it.

Contract tests are faster and more stable than end-to-end tests and tell you which team broke which promise. They sit between unit tests and full end-to-end tests in the test pyramid, and they pair with component testing.

JS Validate a response against a contract

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The second response would pass a test that only checks "status is 200", yet it breaks every consumer reading name or comparing id as a number. That is the gap contract testing closes.

Comparing screen, API and data

The most useful comparison of all is reconciliation: the same facts, seen from three places, must agree. The second stand-in compares a few rows as the "database" holds them with the JSON an API returns. Note how money is stored as integer cents in one place and a decimal in the other, a common source of rounding bugs, and how one order's status disagrees.

JS Reconcile API output with database rows

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The check found a real-looking defect: order 3 is cancelled in the database but reported as paid by the API. Without looking underneath, a tester who only saw the order page might have noticed nothing. When you report it, include the request, the response and the query with its result, as described in Writing Excellent Bug Reports. The Math.round(item.total * 100) conversion is also a hint: comparing decimals directly can fail because of floating-point rounding, so convert to a common unit first.

Do not paste real tokens, passwords or personal data into bug reports, screenshots or chat. Copy requests carefully and replace secrets with placeholders.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. You send an invalid email to a sign-up endpoint and receive 500 Internal Server Error. What is your assessment?

    5xx means the server failed. Bad client input should be rejected with a 4xx status and a helpful message.
  2. 2. A signed-in user changes the id in a URL and sees another user's profile. Which check failed?

    Authentication proves who you are; authorization decides what you may access. Seeing someone else's data is a serious authorization defect.
  3. 3. Which status code best fits creating a new resource successfully?

    201 Created is the usual response for a successful POST that created a resource, although 200 is sometimes used. 404 and 301 mean other things.
  4. 4. Which SQL statement is safest to run while checking test data?

    A SELECT only reads. Statements that change or remove data can destroy test data and should be used only with great care.
  5. 5. The API shows total 10.99 while the database holds 1099. What is the right comparison?

    10.99 and 1099 cents are the same amount. Converting to one unit avoids false alarms and floating-point comparison problems.

Summary

  • A request has a method, URL, headers and body; a response has a status code, headers and (usually) JSON.
  • Learn the families: 2xx success, 4xx client errors, 5xx server errors. A 5xx caused by bad input is a defect.
  • Check each endpoint for the happy path, validation, authentication and authorization, repeats, side effects and limits.
  • Read JSON for structure, types, values and consistency.
  • Use read-only SQL on a test database to confirm what was really stored, and look for duplicates and orphans.
  • Reconcile screen, API and database; convert to common units before comparing.

Next up: Mobile App Testing Basics.