Data Access, NoSQL, Caching, and Storage

Introduction

Backend applications rarely fail because they cannot display a web page. They fail because data is poorly modelled, inconsistently updated, unavailable under load, or stored in an unsuitable technology. Data access is therefore more than writing SQL or calling a database driver. It involves designing a boundary between application logic and persistence, selecting an appropriate data model, controlling consistency and performance, and protecting information throughout its life cycle.

This chapter examines repositories and data-access layers, relational and NoSQL databases, query builders and object-relational mappers (ORMs), caching, file and object storage, and criteria for selecting data technologies. The emphasis is practical: a certificate-level developer should be able to build a maintainable persistence layer, explain the principal trade-offs, and make a defensible technology choice.

Learning Objectives

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

• explain the role of repositories and data-access layers;
• distinguish database drivers, query builders, ORMs, and repositories;
• compare relational, document, key-value, wide-column, and graph models;
• design basic schemas and queries for common application requirements;
• apply caching strategies and reason about invalidation and consistency;
• distinguish database storage from file and object storage;
• evaluate technologies using consistency, scale, cost, operational complexity, and security;
• identify common failure modes and apply suitable best practices.

1. The Persistence Boundary

A backend normally contains at least three conceptually different concerns:

1. Domain or application logic: what the system means and what rules it enforces.
2. Data access: how records are created, read, changed, and deleted.
3. Infrastructure: database connections, credentials, drivers, queues, caches, and storage services.

Mixing these concerns makes change expensive. For example, if an HTTP controller constructs SQL strings directly, then business rules, request parsing, and database details become inseparable. A change from one database engine to another may require editing many controllers.

A data-access layer (DAL) provides a controlled boundary around persistence. It may expose operations such as findUserById, listOpenOrders, or savePayment, while hiding connection management and query details. A repository is a commonly used abstraction representing a collection of domain objects or a particular aggregate. It should express useful application operations rather than merely reproduce every database feature.

A useful layering arrangement is:

• Controller or route handler: validates input, authenticates the caller, and selects a use case.
• Service or use-case layer: applies business rules and coordinates operations.
• Repository or gateway: translates domain operations into database operations.
• Database adapter: uses a driver, query builder, or ORM.
• Database or storage service: persists the data.

The repository pattern is not a rule that every table needs a class. An abstraction is valuable when it reduces coupling, centralizes important queries, supports testing, or protects domain code from persistence details. An abstraction that merely renames SELECT as getAll can conceal rather than solve complexity.

Drivers, query builders, ORMs, and repositories

A database driver is a library that communicates with a particular database protocol. It provides low-level connection, parameter binding, transaction, and result-handling facilities.

A query builder constructs database queries through a programming-language API while usually preserving substantial control over SQL. It can reduce string-concatenation errors and provide composable filters, joins, and pagination.

An ORM maps database rows and relationships to programming-language objects. ORMs can accelerate development by handling common CRUD operations, validation integration, migrations, and relationship loading. However, they may generate inefficient queries, hide transaction boundaries, or encourage a model that does not fit the database.

A repository sits above one or more of these mechanisms. It should define a stable interface for the application. A repository implementation can use SQL, an ORM, a document client, or a test double.

Parameterization and transactions

User input must never be concatenated directly into a query. Parameterized queries send values separately from query structure and substantially reduce injection risk. Input validation remains necessary, but validation is not a replacement for parameterization.

A transaction groups operations so that the database treats them as one unit. The traditional ACID properties are:

• Atomicity: all operations in a transaction succeed, or none take effect.
• Consistency: declared database rules remain satisfied after commit.
• Isolation: concurrent transactions do not produce prohibited intermediate effects.
• Durability: committed data survives an appropriate system failure, subject to the database’s durability guarantees.

Transactions are especially important when a business operation changes multiple related records. The application must define the transaction boundary; it should not assume that two independent database calls are automatically atomic.

2. Relational Data Access

Relational databases represent data in tables composed of rows and columns. A row usually represents an entity or relationship occurrence, while a column represents an attribute. Relationships are represented using primary and foreign keys.

A primary key identifies a row. A foreign key refers to a key in another table and can enforce referential integrity. Normalization reduces duplicated facts by separating entities into related tables. Excessive normalization may require many joins, whereas carefully justified denormalization can improve read performance.

For example, an online shop might use:

• customers(customer_id, name, email);
• orders(order_id, customer_id, order_date, status);
• order_items(order_id, product_id, quantity, unit_price);
• products(product_id, name, current_price).

The price in order_items is intentionally a historical snapshot. If the product’s current price changes, an old invoice must not change. This illustrates that data modelling follows business meaning, not merely the current appearance of a user interface.

Indexes are auxiliary structures that allow a database to locate rows more efficiently. An index can improve reads but consumes storage and adds work during inserts, updates, and deletes. Index columns should reflect actual query patterns. A composite index on (customer_id, order_date) may support queries filtering by customer and ordering or filtering by date, but its usefulness depends on the database engine and query shape.

Query design and pagination

A query should select only the fields needed, filter in the database, and use a deterministic order when returning a page. Offset pagination, such as “skip 1,000 rows,” is simple but can become slow and unstable when records are inserted or deleted. Cursor or keyset pagination uses a value from the last result, such as an indexed creation time plus unique identifier. It is often more efficient for large, changing datasets.

The N+1 query problem occurs when an application first loads N parent records and then performs a separate query for each parent. A join, batch query, or deliberate eager-loading strategy can reduce round trips. Developers should inspect generated SQL rather than assume that an ORM has produced an efficient plan.

3. NoSQL Data Models

“NoSQL” describes a family of non-relational or relationally different approaches rather than one single technology. NoSQL systems often prioritize flexible schemas, horizontal scaling, high write throughput, or access patterns that do not fit normalized tables. They do not automatically eliminate transactions, structure, or the need for careful modelling.

Document databases

A document database stores records as documents, commonly represented using JSON-like structures. A document can contain nested objects and arrays. This is useful when an aggregate is normally read or written together, such as a product catalogue entry with specifications and images.

Advantages include flexible fields, natural representation of nested data, and convenient retrieval of an aggregate. Risks include duplicated data, documents that grow without bound, and difficult cross-document transactions. A document should not become an unstructured dumping ground. Required fields, allowed types, maximum sizes, and versioning should still be governed.

Embedding is appropriate when child data has a strong ownership relationship and is usually retrieved with its parent. Referencing is preferable when the child is shared, independently updated, or potentially very large.

Key-value stores

A key-value store maps a key to a value. The value may be opaque to the store or may support types such as strings, sets, lists, or counters. Key-value systems are effective for sessions, feature flags, rate-limit counters, ephemeral state, and cache entries. They are less suitable when the principal operation requires arbitrary filtering across many values.

Key design is fundamental. A key should be unique, stable, and structured enough to avoid collisions. Namespaces such as session:<id> or user:<id>:preferences can make operations and invalidation clearer.

Wide-column stores

Wide-column databases organize data by row keys and column families. They are designed for large distributed datasets and high-throughput access patterns. Data is often modelled from the query backward: the developer identifies the exact lookup and stores a representation optimized for it. Flexible ad hoc joins should not be assumed.

Graph databases

Graph databases represent nodes and relationships as first-class structures. They are useful for traversals such as “friends of friends,” dependency networks, fraud connections, and recommendation relationships. A graph database is not automatically better for every relationship; a conventional relational schema may be simpler for fixed joins and financial transactions.

Comparing models

The correct choice depends on workload rather than popularity. A single system may legitimately use several models, but each additional technology creates operational, monitoring, backup, and security responsibilities.

4. Consistency, Availability, and Scale

Consistency describes what a reader can observe after writes. Strong consistency generally means a successful read reflects the latest committed write according to the system’s contract. Eventual consistency means replicas may temporarily differ but converge if updates stop. Some systems provide tunable or session-level guarantees between these extremes.

The practical question is not “Is eventual consistency good?” but “Which data can tolerate stale values, for how long, and what user-visible behaviour is acceptable?” A social-media like count may tolerate a short delay; an account balance normally requires much stronger guarantees.

Distributed systems also face trade-offs among latency, availability, partition tolerance, and consistency. Network partitions are unavoidable possibilities in a distributed system. Consequently, design decisions should identify which guarantees are essential during communication failures rather than treating a slogan as a complete architecture.

Scale has several dimensions:

• Vertical scale increases resources on one machine.
• Horizontal scale adds machines or service instances.
• Read scale may use replicas, caches, or materialized views.
• Write scale may require partitioning, batching, queues, or workload redesign.
• Data volume scale concerns storage capacity and archival policy.

A database that is fast for one thousand records may need indexes, partitioning, or archival at one billion records. Capacity planning should be based on measured workload, not vendor claims alone.

5. Caching

A cache stores a reusable representation closer to the consumer than the authoritative data source. It may reduce latency, database load, and infrastructure cost. A cache is not automatically a source of truth. The design must specify what happens when an entry is absent, stale, corrupted, or unavailable.

Common caching locations include:

• browser and client caches;
• content delivery networks (CDNs);
• reverse proxies;
• application-process memory;
• distributed key-value caches;
• database buffer caches.

A cache-aside pattern is widely used: the application reads the cache; on a miss, it reads the database, stores the result with an expiration, and returns it. On a write, the application updates the database and then removes or refreshes the related cache entry. The order and failure handling matter. If cache invalidation happens before a database update that later fails, a subsequent read may repopulate an old value; if invalidation never occurs, stale data may persist.

Other patterns include:

• Read-through: a cache provider loads missing values from the source.
• Write-through: writes go through the cache to the backing store.
• Write-behind: the cache acknowledges first and writes later; this improves latency but increases durability risk.
• Refresh-ahead: frequently used entries are refreshed before expiration.

The basic hit ratio is:

The ratio is dimensionless and applies to a defined observation period and request population. It must be interpreted with latency and correctness; a high hit ratio is not useful if entries are wrong or expensive to generate.

A cache’s approximate storage requirement can be estimated as:

Here, N is the number of entries, each S term is a size in bytes, and the estimate assumes average entry sizes. Real systems add allocator, replication, compression, and eviction overhead.

Expiration and invalidation

Time to live (TTL) limits how long an entry remains eligible for use. TTL reduces permanent staleness but does not guarantee freshness during the TTL interval. Explicit invalidation is more immediate but requires the application to know every affected key. Versioned keys, such as product:<id>:v<version>, can avoid deleting many related entries, although old values still consume space until expiration.

Cache stampedes occur when many requests miss simultaneously and all perform an expensive origin operation. Mitigations include request coalescing, short randomized TTL variation, locks used carefully, prewarming, and stale-while-revalidate approaches. Caches also require protection against unbounded key cardinality, eviction storms, sensitive-data leakage, and denial-of-service through costly misses.

6. File and Object Storage

Databases are usually designed for structured records and transactional queries. Files such as photographs, videos, backups, and large documents are often better placed in file or object storage. Object storage typically stores a blob together with metadata under a key and provides APIs for upload, retrieval, lifecycle management, and access control.

A robust design stores the object in object storage and stores metadata in a database. Metadata may include owner, media type, size, checksum, storage key, upload state, and creation time. The database should not usually contain the entire large binary merely because it can.

A common upload sequence is:

1. authenticate the user and authorize the intended operation;
2. create a short-lived, scoped upload permission or presigned URL;
3. upload directly to object storage where appropriate;
4. verify size, media type, checksum, and malware status;
5. mark the database record as complete only after verification;
6. serve through controlled URLs or a CDN.

The system must handle partial uploads, duplicate submissions, abandoned objects, and the possibility that the database update and object upload do not complete together. A background reconciliation job can find orphaned objects and incomplete metadata.

Never trust a filename or client-supplied content type as proof of safety. Use authorization checks, size limits, content inspection, encryption, logging, retention rules, and privacy-aware access policies. Public URLs may expose information even when application pages are protected.

7. Selecting a Data Technology

Technology selection should begin with requirements and access patterns. A useful decision sequence is:

1. What are the critical entities and invariants?
2. Which operations must be atomic?
3. What are expected read and write rates, data size, and growth?
4. Which queries, joins, searches, and aggregations are required?
5. What degree of staleness is acceptable?
6. What availability and recovery objectives apply?
7. What are the budget, team skills, hosting, and compliance constraints?
8. Can one technology satisfy the requirements with less operational complexity?

Important evaluation dimensions include:

A relational database is often the safest default for transactional business data because it provides mature constraints, queries, and transactions. A document store may be appropriate for independently evolving aggregates. A key-value system is a strong choice for sessions or cache entries. Object storage is usually appropriate for large immutable blobs. These are starting points, not universal rules.

8. Worked Application: A Course Marketplace

Consider an illustrative course marketplace. Users browse courses, purchase enrolments, watch videos, and download certificates.

Requirements

Purchases must not be charged twice for one payment request. A user’s enrolment and payment record must be consistent. Course descriptions change occasionally but are read frequently. Video files are large and should not pass through the application server unnecessarily. Progress updates are frequent and need not appear instantly on every device.

Analysis

Purchases have strong integrity requirements and involve relationships among users, courses, orders, and payment attempts. A relational database is appropriate for these records. Course descriptions could remain relational because they require search, ownership, and reporting; a document cache can be added only if measurement justifies it. Videos belong in object storage. Frequently requested course summaries can use a cache-aside strategy.

Approach

The relational schema includes users, courses, orders, order_items, payments, and enrolments. A unique business key, such as an idempotency key for a payment request, prevents accidental duplicate processing. The order, payment state, and enrolment are updated within carefully designed transaction boundaries; interaction with an external payment provider requires an explicit state machine because the provider call cannot normally be rolled back by the local database.

Course pages use a repository method such as getCoursePage(courseId). The method returns only required fields and applies authorization where necessary. The application first checks course-page:<id>:<version> in the cache. A successful course update increments the version or invalidates the old entry. A short TTL limits the impact of missed invalidations.

Video metadata is held in the database, while video objects use generated storage keys unrelated to untrusted filenames. The application issues short-lived download permissions after checking enrolment. A worker records viewing progress in batches, reducing write pressure. The user interface may display slightly delayed progress, an explicitly accepted consistency trade-off.

Outcome and learning points

This design uses multiple technologies because their responsibilities differ, not because “polyglot persistence” is fashionable. The relational database protects business invariants; the cache improves read latency; object storage handles large binaries; background processing manages asynchronous work. Monitoring should include transaction failures, cache hit ratio, stale or missing objects, payment reconciliation, upload failures, and queue delay.

The case also demonstrates a limitation: a local transaction cannot make an external payment provider transactionally identical to the local database. Idempotency, reconciliation, durable state transitions, and compensating actions are required.

9. Challenges, Governance, and Best Practices

Reliability and operations

Backups are useful only when restoration is tested. Document retention periods, recovery objectives, replication, failover procedures, schema migration procedures, and ownership. Monitor latency, error rates, connection-pool exhaustion, slow queries, lock contention, storage growth, replication lag, cache evictions, and queue backlog.

Use migrations under version control. Avoid destructive changes without a compatibility plan. A common safe sequence is to add a new field, deploy code that can read both forms, backfill data, switch writes, and remove the old field only after dependent versions are gone.

Security and privacy

Apply least privilege to database and storage identities. Separate development, test, and production credentials. Encrypt data in transit and at rest where appropriate. Redact secrets and personal information from logs. Define retention and deletion policies, including backups and cached copies. Audit administrative access and investigate unusual download or query patterns.

Data minimization is both an ethical and operational practice: do not collect or retain information without a legitimate purpose. Privacy regulations may impose duties concerning access, correction, deletion, breach response, residency, and processor agreements. The exact requirements depend on jurisdiction and data type; developers should involve the responsible governance or legal function rather than assuming that a database setting alone establishes compliance.

Testing

Test repository behaviour with integration tests against a realistic database engine, not only mocks. Test transactions, constraints, migrations, pagination boundaries, duplicate requests, concurrent updates, cache misses, stale entries, failed uploads, and recovery paths. Load tests should use representative data distributions; a test containing only uniform small records may hide production problems.

AI-assisted development

AI coding tools can generate repository methods, schemas, or migration drafts, but generated code must be reviewed. Common risks include insecure query construction, accidental exposure of secrets in prompts, incorrect transaction assumptions, inefficient ORM queries, and invented library APIs. Developers remain responsible for testing, licensing review, privacy, and operational correctness.

Chapter Summary

Data access is a boundary between application behaviour and persistence technology. Drivers provide low-level communication; query builders provide controlled query construction; ORMs map objects and records; repositories express application-oriented persistence operations. Relational databases are strong for structured relationships, constraints, reporting, and transactions. Document, key-value, wide-column, and graph systems address different access patterns and scaling needs.

Caching can improve latency and reduce load, but it introduces staleness, invalidation, eviction, and stampede risks. TTL, explicit invalidation, versioned keys, and careful failure handling are essential. Large binary files are generally better suited to object storage, with authorization and metadata managed separately. Technology selection should weigh consistency, query shape, scale, cost, security, recovery, and team complexity. Sound systems use measurement, migrations, observability, tested backups, least privilege, and explicit business invariants rather than relying on fashionable technologies.

Key Terms

Repository: An application-facing abstraction for persistence operations.

Data-access layer: Code that separates application logic from storage mechanisms.

Database driver: A library that communicates with a database protocol.

Query builder: An API for constructing database queries programmatically.

ORM: Object-relational mapper that connects program objects with relational records.

Transaction: A group of operations treated as one unit by a database.

Primary key: A value that uniquely identifies a database row.

Foreign key: A reference from one table to a key in another table.

Index: An auxiliary structure that accelerates selected queries.

Document database: A database that stores nested, document-like records.

Key-value store: A system that retrieves values primarily by unique keys.

Wide-column database: A distributed database organized around partitioned rows and column families.

Graph database: A database that treats nodes and relationships as primary structures.

Consistency: The guarantee governing what values readers can observe after writes.

Cache: A temporary, reusable copy of data maintained for faster access.

TTL: Time to live; the period for which a cache entry remains eligible for use.

Cache stampede: Concentrated origin load caused by many simultaneous cache misses.

Object storage: Storage for blobs addressed by keys and accompanied by metadata.

Idempotency: The property that repeating an operation produces the same intended result rather than duplicate effects.

Migration: A controlled change to a database schema or stored data.

Review Questions

Short Answer Questions

1. What is the purpose of a repository in a backend application?
2. State two differences between a query builder and an ORM.
3. Give one suitable use for a key-value store and one unsuitable use.
4. Why should large video files usually be stored outside a relational database?
5. What is cache invalidation, and why is it difficult?

Long Answer Questions

1. Explain the roles of controllers, services, repositories, adapters, and databases in a layered backend.
2. Compare relational, document, key-value, wide-column, and graph databases, including a strength and limitation of each.
3. Explain the cache-aside pattern, including cache misses, writes, TTL, and stale data.
4. Describe how transactions, constraints, indexes, and migrations contribute to reliable relational data access.
5. Discuss the principal factors that should guide selection of a data technology for a new application.

Analytical / Application Questions

1. A service stores user sessions, financial transfers, product images, and recommendation relationships. Select an appropriate storage technology for each category and justify your choices.
2. An ORM-based endpoint performs 501 database queries when displaying 500 orders. Identify the likely problem and propose two solutions, including how you would verify improvement.
3. Design a reliable upload workflow for user documents. Explain authorization, object storage, metadata, validation, failure recovery, retention, and secure download.

Multiple Choice Questions with Answers

Question 1

Which component should normally contain application-oriented persistence operations such as finding a user by identifier?

A. Repository

B. CSS stylesheet

C. DNS resolver

D. Image compressor

Correct Answer: A. Repository

Question 2

What is the principal security benefit of a parameterized query?

A. It automatically creates a database backup

B. It separates query structure from supplied values

C. It guarantees that every query uses an index

D. It converts a relational database into a document database

Correct Answer: B. It separates query structure from supplied values

Question 3

Which data model is most naturally suited to direct lookups by a unique key, such as session retrieval?

A. Graph

B. Key-value

C. Wide-column reporting cube

D. Relational join table only

Correct Answer: B. Key-value

Question 4

In a cache-aside design, what normally happens after a cache miss?

A. The application reads the source, stores the result in the cache, and returns it

B. The cache permanently deletes the database record

C. The application rejects every request until the cache is rebuilt

D. The database is replaced by the cache

Correct Answer: A. The application reads the source, stores the result in the cache, and returns it

Question 5

A cache records 900 hits and 100 misses. What is its hit ratio?

A. 0.10

B. 0.50

C. 0.90

D. 9.00

Correct Answer: C. 0.90

Question 6

Which storage choice is generally most appropriate for large video objects?

A. Object storage

B. A browser cookie

C. A database index

D. A foreign-key constraint

Correct Answer: A. Object storage

Question 7

What problem occurs when an application loads 500 parent records and then performs one query for each parent?

A. Cache poisoning

B. N+1 queries

C. Referential integrity

D. Horizontal normalization

Correct Answer: B. N+1 queries

Question 8

Which property means that all operations in a transaction succeed or none take effect?

A. Atomicity

B. Availability

C. Indexing

D. Partitioning

Correct Answer: A. Atomicity

Question 9

Which statement about a cache TTL is correct?

A. It guarantees that data is never stale

B. It limits how long an entry remains eligible for use

C. It permanently synchronizes every database replica

D. It replaces authorization checks

Correct Answer: B. It limits how long an entry remains eligible for use

Question 10

Why might a relational database be selected for payment and enrolment records?

A. It cannot enforce constraints

B. It is designed only for storing images

C. It offers mature transactions and integrity constraints

D. It prevents all failures in external payment services

Correct Answer: C. It offers mature transactions and integrity constraints