API-Centred Database Integration

Introduction

Modern applications rarely allow a browser or mobile client to connect directly to a database. Instead, an application programming interface (API) provides a controlled boundary between clients, business logic, and persistent data. This boundary is responsible for authentication, validation, query construction, transaction management, serialization, error reporting, and protection against malicious or accidental misuse.

In a full-stack application, an API-centred design commonly follows this flow:

Client → HTTP/GraphQL API → routing and authentication → validation → service layer → repository/data-access layer → MySQL or MongoDB

The API must expose useful capabilities without exposing database structure or allowing clients to control unsafe operations. A well-designed service therefore treats database integration as more than issuing SQL or MongoDB queries. It establishes a stable contract between consumers and the application.

This chapter uses REST as the principal example and introduces GraphQL where its design differs. Examples use an illustrative online bookstore with users, books, and orders.

Learning Objectives

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

• design database-backed REST endpoints and explain equivalent GraphQL operations;
• validate request data at the API boundary and enforce business rules in the service layer;
• implement pagination, filtering, sorting, and controlled field selection;
• produce consistent HTTP responses and useful error representations;
• serialize database records without exposing sensitive or internal fields;
• distinguish authentication, authorization, validation, and persistence errors;
• prevent SQL injection, NoSQL injection, mass assignment, and over-fetching;
• design idempotent write operations and understand transaction boundaries;
• compare relational and document-oriented integration patterns;
• evaluate an API design through an applied case study.

1. The API as a Database Integration Boundary

An API contract describes what a client may request and what the server guarantees in response. The contract should be expressed in terms of business resources, not tables or collections. For example, /books is preferable to /tbl_book_records, because the former remains meaningful if the storage design changes.

A typical layered architecture contains the following components:

The separation is valuable because each layer has a distinct security and testing purpose. A controller should not construct SQL strings, and a repository should not decide whether a customer is allowed to cancel an order.

Resource-oriented REST

A REST API commonly uses HTTP methods as operation semantics:

Idempotency means that repeating the same request has the same intended effect as performing it once. GET, PUT, and DELETE are conventionally idempotent, although the exact server implementation must preserve that property. POST is not automatically idempotent; an idempotency key can make a particular create operation safely repeatable.

GraphQL perspective

GraphQL exposes a typed schema rather than a collection of URL endpoints. Queries retrieve data, mutations change data, and subscriptions support event-oriented updates. GraphQL can reduce under-fetching, but unrestricted client-selected fields can produce expensive queries. Depth limits, query-cost controls, authorization directives, and resolver batching are therefore important. GraphQL does not remove the need for validation, authorization, parameterized database access, or careful serialization.

2. Request Validation and Business Rules

Validation occurs at multiple levels. Transport validation checks whether the request is structurally acceptable: a quantity is an integer, an email has an acceptable format, and a date is parseable. Domain validation checks business meaning: a quantity must be positive, a book price cannot be negative, and an order cannot be cancelled after shipment. Database constraints provide a final integrity barrier but should not be the only user-facing validation mechanism.

A useful validation sequence is:

1. Parse the request according to its content type.
2. Reject unknown or forbidden fields when appropriate.
3. Validate types, lengths, formats, and ranges.
4. Normalize permitted values, such as trimming a name.
5. Authenticate the caller.
6. Authorize the requested operation and resource.
7. Apply domain rules in the service layer.
8. Persist using a transaction or atomic operation where required.

Validation should be allow-list based. For a book-creation endpoint, an explicit schema might permit only title, authorId, isbn, and price. It should not copy every property from the request body into a model.

Validation example

A request such as:

{
  "title": "Database Design",
  "authorId": 7,
  "price": 34.50,
  "isAdmin": true
}

must not silently assign isAdmin. The API should either reject unknown fields or ignore them according to a documented policy; rejecting them is generally safer because clients discover mistakes immediately.

Validation errors should identify fields without revealing internal implementation details. A useful response is:

{
  "error": {
    "code": "VALIDATION_FAILED",
    "message": "One or more fields are invalid.",
    "fields": {
      "price": "Must be greater than or equal to 0."
    },
    "requestId": "req-8f21"
  }
}

3. Safe Database Queries

SQL injection prevention

SQL injection occurs when untrusted input changes the meaning of a SQL statement. The primary defence is a prepared statement with bound parameters:

const sql = `SELECT id, title, price
             FROM books
             WHERE author_id = ? AND price <= ?`;
const [rows] = await connection.execute(sql, [authorId, maximumPrice]);

The values are transmitted separately from the SQL program. String concatenation is unsafe:

// Unsafe: do not use
const sql = "SELECT * FROM books WHERE title = '" + title + "'";

Parameters generally cannot represent SQL identifiers such as column names. Therefore, dynamic sorting must use an allow-list:

const sortColumns = { title: "title", price: "price", newest: "created_at" };
const column = sortColumns[requestedSort] ?? "created_at";
const direction = requestedDirection === "asc" ? "ASC" : "DESC";
const sql = `SELECT id, title, price FROM books ORDER BY ${column} ${direction}`;

Only server-defined values enter the identifier position.

NoSQL injection prevention

MongoDB queries are represented as objects. If a client can submit operators such as $where, $ne, or $gt in an uncontrolled manner, the query may have unintended meaning. Validate the type and shape of every filter, construct the query from permitted fields, and disable or reject operators that the endpoint does not explicitly support.

const filter = { status: "published" };
if (typeof request.query.authorId === "string") {
  filter.authorId = new ObjectId(request.query.authorId);
}
const books = await db.collection("books")
  .find(filter)
  .limit(20)
  .toArray();

Do not pass the complete query-string object directly to find(). In addition to injection, uncontrolled query objects can create expensive collection scans.

Mass assignment

Mass assignment occurs when a framework maps request properties directly to a persistent object. A malicious user might submit role: "admin", balance: 0, or approved: true. The remedy is explicit field selection and separate input/output models:

const update = {
  displayName: body.displayName,
  phone: body.phone
};
await userRepository.update(userId, update);

Authorization must still be enforced. A field being syntactically valid does not mean the caller is permitted to modify it.

4. Pagination, Filtering, and Sorting

Returning every database record is unsuitable for large datasets. Pagination controls response size and protects database resources. The common offset model uses limit and offset:

GET /books?limit=20&offset=40

The server should impose a maximum limit, such as 100, and normalize invalid values. Offset pagination is simple and supports jumping to a page, but deep offsets may become slow because the database still has to locate and skip many rows.

Cursor pagination uses a stable position, commonly a unique ordered key:

GET /books?limit=20&after=eyJjcmVhdGVkQXQiOiIyMDI1..."

A cursor should be opaque, signed or integrity-protected, and tied to the relevant sort order. A robust ordering uses a unique tie-breaker, for example created_at DESC, id DESC. Without a tie-breaker, records with equal timestamps may appear on multiple pages or be skipped.

A paginated response can be represented as:

{
  "data": [{"id": 42, "title": "Database Design"}],
  "page": {
    "limit": 20,
    "nextCursor": "opaque-token",
    "hasMore": true
  }
}

Filtering should expose a documented vocabulary, for example status=published, authorId=7, and priceMax=50. Do not expose arbitrary SQL expressions or unrestricted MongoDB operators. Sorting should have a default, a small allow-list, and deterministic tie-breaking.

Indexes must reflect common query patterns. A relational index on (status, created_at, id) may support filtering by status and ordered retrieval. MongoDB compound indexes should be designed with the query shape and equality/range/order pattern in mind. Indexes improve reads but consume storage and slow writes, so they require measurement rather than indiscriminate creation.

5. Serialization and Response Contracts

Database records often contain fields that must not leave the server: password hashes, password-reset tokens, internal audit data, supplier costs, and security flags. Serialization transforms an internal entity into a public representation.

function serializeUser(user) {
  return {
    id: user.id,
    displayName: user.displayName,
    email: user.email,
    createdAt: user.createdAt.toISOString()
  };
}

Serialization should also normalize dates, decimals, enumerations, nested relationships, and identifiers. It should avoid accidentally returning ORM metadata or MongoDB-specific internal fields unless they are part of the contract. Public representations should be versioned or evolved compatibly; removing or changing field meaning can break clients.

HTTP status codes communicate broad outcomes:

An API should use one documented error envelope. It should log detailed diagnostics internally while returning safe messages externally. A request or correlation ID allows operators to connect a client-visible error with server logs.

6. Transactions, Consistency, and Idempotency

A transaction groups operations so that they satisfy the database's atomicity and consistency guarantees. Consider placing an order: creating the order, creating order lines, and reducing inventory may need to succeed together. In MySQL, this can be implemented with START TRANSACTION, appropriate row locks, and COMMIT or ROLLBACK. MongoDB supports transactions across replica-set or sharded deployments, but they should not be used indiscriminately; document embedding and atomic single-document updates are often simpler.

The service layer should define the transaction boundary. It should not hold a transaction open while waiting for an external payment provider. Instead, payment workflows often use durable states such as pending, paid, and failed, together with retry and reconciliation mechanisms.

Idempotency is essential when clients retry after timeouts. A client can send an Idempotency-Key with a create request. The server stores the key, request fingerprint, resulting status, and response. A repeated key with the same request returns the stored result; the same key with different content produces a conflict. The key must be scoped appropriately, often to the authenticated client and operation.

For an order request, a relational implementation might enforce a unique constraint on (customer_id, idempotency_key). In MongoDB, a unique compound index can provide a similar guarantee. The persistence of the key and business result must be coordinated so that concurrent retries cannot create duplicates.

7. REST and GraphQL Design Considerations

REST makes caching, resource URLs, and HTTP semantics visible. It is often straightforward for public services and simple clients. GraphQL allows a client to request a tailored shape, which can reduce multiple round trips. However, GraphQL resolvers can accidentally create an N+1 query pattern: one query loads 100 orders and then performs 100 separate customer queries. DataLoader-style batching, carefully designed joins, or prefetching can address this problem.

Both styles require:

• authentication and authorization at resolver or endpoint level;
• input schemas and maximum sizes;
• query complexity and rate controls;
• safe data access and field-level serialization;
• observability of latency, errors, and database calls.

GraphQL errors are often returned alongside partial data, so clients must inspect both the data and errors members. REST commonly uses status codes and an error body. Neither approach is intrinsically more secure; security depends on implementation and governance.

8. Applied Case: The Bookstore Order API

Background

This illustrative case concerns ReadSpace, an online bookstore using MySQL for customers, orders, and inventory. Product search is later moved to MongoDB because descriptions and categories have variable structure. The company has experienced duplicate orders after mobile-network retries and has exposed internal user fields in an earlier API version.

Problem

The team must design:

• POST /orders for order creation;
• GET /books with search, filtering, sorting, and pagination;
• safe user updates;
• consistent errors and protection against injection and mass assignment.

Analysis

An order is a business operation rather than a simple row insertion. It requires authentication, ownership of the customer identity, validation of line items, inventory checks, a transaction, and retry safety. Book search is read-heavy and requires indexes and a bounded result set. The API must not trust client-supplied prices, totals, roles, or inventory values.

Approach and solution

The order request accepts only book identifiers and quantities:

{
  "items": [
    {"bookId": 18, "quantity": 2},
    {"bookId": 31, "quantity": 1}
  ]
}

The service performs the following steps:

1. Authenticate the caller and read the customer ID from the verified identity, not from the request body.
2. Validate that items is non-empty, quantities are positive integers, and the number of lines is bounded.
3. Begin a database transaction.
4. Lock or atomically reserve the relevant inventory rows.
5. Read authoritative prices from the database.
6. Calculate the server-side total and create the order and lines.
7. Store the idempotency key and commit.
8. Serialize the order without internal audit fields.

If inventory is unavailable, the service returns a conflict response rather than a generic database error. If a duplicate idempotency key is received, the original order response is returned. A failed transaction is rolled back.

For book search, the endpoint accepts only documented parameters:

GET /books?q=database&category=programming&sort=price_asc&limit=20&after=...

The repository builds a controlled filter object and uses a MongoDB text or search index according to the chosen deployment. The limit is capped, and the cursor contains the last ordered key. The response includes public book fields only.

For profile editing, the controller maps displayName and phone explicitly. It rejects role, verified, and passwordHash, even if submitted by the client. A separate authenticated workflow handles password changes.

Outcome

The design prevents duplicate order creation during retries, keeps prices authoritative, limits search cost, and removes sensitive fields from responses. Monitoring records latency, query duration, status distribution, validation failures, duplicate idempotency attempts, and transaction conflicts.

Learning points

The central lesson is that an API must control both the input path and the output path. Prepared statements do not solve authorization; serialization does not replace validation; and transactions alone do not solve client retries. Reliable database integration is a coordinated design problem.

9. Testing, Observability, and Operational Governance

API tests should include unit tests for validation and serialization, integration tests against a real or faithfully configured database, and contract tests for response shapes. Security tests should attempt SQL injection, NoSQL operator injection, mass assignment, unauthorized object access, oversized requests, and cursor tampering.

Observability should capture structured logs, metrics, and traces. Useful measures include request count, latency percentiles, error rate, database time, rows examined, cache behaviour, and transaction retries. Logs must not contain passwords, access tokens, or unnecessary personal data.

Database migrations should be versioned, reviewed, and reversible where practical. Deployments must consider compatibility between old clients and new schemas. A safe sequence is often to add a nullable field, deploy code that writes both representations, migrate existing data, and remove the old field only after dependent clients have moved.

Privacy and governance are also part of API design. Collect only necessary data, define retention periods, restrict administrative access, encrypt traffic, protect credentials, and provide audit records for sensitive actions. Data-protection obligations vary by jurisdiction, but the principles of purpose limitation, access control, minimization, and accountability are broadly applicable.

10. Contemporary Developments and Limitations

API gateways increasingly provide rate limiting, authentication integration, schema validation, and observability, but they cannot replace authorization in application code. Managed databases and serverless functions can accelerate deployment, yet connection pooling, cold starts, transaction scope, and cost controls remain important.

AI-assisted coding can generate repositories, validators, and tests, but generated code must be reviewed for unsafe interpolation, excessive privileges, missing authorization, and incorrect transaction assumptions. AI systems may also reproduce vulnerable patterns or expose sensitive data through prompts and logs. Human review, automated scanning, and tests remain necessary.

No single pagination strategy, database, or API style is universally best. Offset pagination is easy to understand; cursor pagination is often better for changing, large datasets. MySQL offers strong relational constraints and joins; MongoDB offers flexible documents and document-level atomicity. The correct choice depends on access patterns, consistency requirements, team capability, and operational constraints.

Best-Practice Checklist

• Define resources and operations as a documented contract.
• Authenticate before accessing protected data and authorize every object operation.
• Validate with allow-lists, bounded sizes, and explicit domain rules.
• Use prepared SQL statements and constructed MongoDB filters.
• Allow-list sortable columns and filter operators.
• Cap page sizes and use deterministic ordering.
• Separate input models, persistence models, and output serializers.
• Never trust client-supplied identity, price, role, total, or privilege fields.
• Use transactions for coordinated state changes and idempotency keys for retryable writes.
• Return consistent status codes and safe, traceable error bodies.
• Index measured query patterns and monitor database cost.
• Test security, concurrency, migration compatibility, and failure recovery.

Chapter Summary

An API-centred database integration places a controlled, documented boundary between clients and persistence systems. Effective services combine layered architecture, input validation, business-rule enforcement, authorization, parameterized queries, safe MongoDB query construction, explicit serialization, and consistent HTTP or GraphQL responses. Pagination, filtering, sorting, and indexing must be designed together to provide useful performance without allowing unbounded queries. Transactions protect coordinated changes, while idempotency keys protect retryable operations from duplication. Mass assignment and injection are prevented through allow-lists and separation of models. Finally, testing, observability, privacy, migration discipline, and human review of automated code are essential to operating a dependable database-backed API.

Key Terms

API: A defined interface through which software components communicate.

REST: An architectural style using resources and standard HTTP semantics.

GraphQL: A typed query language and runtime in which clients select response fields.

Endpoint: A network-accessible API operation.

Validation: Checking that input is structurally and semantically acceptable.

Authorization: Determining whether an authenticated actor may perform an operation.

Serialization: Converting internal data into a public representation.

SQL injection: Manipulation of SQL meaning through unsafe untrusted input.

NoSQL injection: Manipulation of a document query through uncontrolled operators or values.

Mass assignment: Unsafe automatic mapping of request fields to persistent attributes.

Parameterized query: A query whose values are bound separately from its program text.

Pagination: Dividing a large result set into bounded responses.

Cursor: An opaque position used to continue a stable ordered result set.

Idempotency: The property that repeating an operation has the same intended effect as performing it once.

Transaction: A group of database operations managed as a single unit of work.

Serializer: A component that selects and formats fields for an API response.

Allow-list: An explicit set of permitted values or fields.

Repository: A data-access component that isolates persistence operations.

ORM: A library that maps application objects to relational database structures.

Observability: The ability to understand system behaviour through logs, metrics, and traces.

Review Questions

Short Answer Questions

1. What is the purpose of separating a service layer from a repository layer?
2. Why should API sorting parameters be allow-listed?
3. Define mass assignment and state one prevention technique.
4. What information should a safe API error response contain?
5. Why is a unique tie-breaker useful in cursor pagination?

Long Answer Questions

1. Explain how validation, authorization, service rules, and database constraints work together in a create-order operation.
2. Compare offset pagination and cursor pagination, including their performance and consistency characteristics.
3. Describe how prepared statements prevent SQL injection and why they do not solve authorization problems.
4. Compare REST and GraphQL database integration with respect to response shaping, performance, and security controls.
5. Design an idempotent order-creation workflow and explain the role of transactions and unique constraints.

Analytical / Application Questions

1. An endpoint copies every JSON property from a profile-update request into a user record. Identify the security risks and redesign the input-processing flow.
2. A search endpoint accepts sort, filter, and limit values and becomes slow under malicious requests. Propose validation, indexing, pagination, and monitoring controls.
3. A customer receives two orders after a mobile timeout and retry. Analyse the likely design failure and specify a corrected request, persistence, and response strategy.

Multiple Choice Questions with Answers

Question 1

Which technique is the primary defence against SQL injection in a value predicate?

A. Concatenating escaped strings manually

B. Using prepared statements with bound parameters

C. Returning fewer columns

D. Adding a pagination cursor

Correct Answer: B. Using prepared statements with bound parameters

Question 2

Which field should normally be derived by the server when creating an order?

A. The client interface language

B. The requested book identifiers

C. The final order total

D. The requested quantities

Correct Answer: C. The final order total

Question 3

What is the main purpose of an allow-list for sorting?

A. To permit arbitrary SQL identifiers

B. To restrict sorting to safe, supported columns

C. To encrypt database records

D. To replace authentication

Correct Answer: B. To restrict sorting to safe, supported columns

Question 4

Which status code most commonly represents a successful resource creation?

A. 200 OK

B. 201 Created

C. 204 No Content

D. 404 Not Found

Correct Answer: B. 201 Created

Question 5

Which practice best prevents mass assignment?

A. Copying all request fields into the model

B. Accepting only explicitly permitted input fields

C. Disabling database indexes

D. Using larger page sizes

Correct Answer: B. Accepting only explicitly permitted input fields

Question 6

Why should cursor pagination use a unique tie-breaker?

A. To allow arbitrary operators in filters

B. To make ordering deterministic when primary sort values are equal

C. To remove the need for authorization

D. To convert REST into GraphQL

Correct Answer: B. To make ordering deterministic when primary sort values are equal

Question 7

What does an idempotency key help prevent?

A. Duplicate effects caused by safe retries

B. All forms of SQL syntax errors

C. Unauthorized field visibility in responses

D. Missing database indexes

Correct Answer: A. Duplicate effects caused by safe retries

Question 8

Which layer should commonly coordinate business rules such as whether an order may be cancelled?

A. Service layer

B. CSS layer

C. Network cable

D. Serializer only

Correct Answer: A. Service layer

Question 9

What is the safest general approach to a client-supplied MongoDB filter?

A. Pass the complete request query object directly to find()

B. Construct a filter from validated, permitted fields

C. Allow every operator beginning with $

D. Convert the filter into an HTML fragment

Correct Answer: B. Construct a filter from validated, permitted fields

Question 10

Which response practice reduces accidental exposure of password hashes and reset tokens?

A. Returning the raw database object

B. Using an explicit output serializer

C. Increasing transaction duration

D. Removing all authentication

Correct Answer: B. Using an explicit output serializer