Skip to content
academia.sh
tr
Log in

Course Intermediate

The Data Access Layer and Business Logic

By the end of this course

Start course

01

Data Access Approaches

The trade-off between a direct query and a mapper, the points where the object model and the relational model do not line up, the repository pattern that hides the persistence detail, composing queries, the saturation behavior of a connection pool, and running schema migrations forward and backward.

  1. 01 Direct Query and Mapper Building the same list two ways: hand-written SQL versus a small mapper that generates SQL from a declared mapping, counting the generated queries, and the control-versus-repetition trade-off between the two approaches.
  2. 02 Object–Relational Impedance Mismatch The four points where the object model and the relational model do not line up: identity, inheritance, relation direction, and granularity. Each mismatch is measured and shown together with the mapping decision that closes it.
  3. 03 Repository Pattern The repository pattern, which gathers the persistence detail behind a single interface: testing business rules with a fake repository and no database, applying the same contract to two implementations, and measuring the persistence leak per module.
  4. 04 Query Objects and Specifications Turning a criterion into an object: each specification produces its own SQL fragment and bound values, combines with and-or-not, the same criterion works as an in-memory predicate, and identifiers are limited with an allowlist.
  5. 05 Connection Pool Managing a connection as an expensive resource: measuring the opening cost, a fixed-size pool and queue, measuring the relationship between pool size and wait time with parallel requests, the queue timeout at saturation, and carrying over session state.
  6. 06 Schema Migrations Splitting a schema change into versioned steps: forward and backward migration scripts, the version table, the atomic rollback of a failed step, and changing a column without an outage through an expand-write-contract scheme.

02

Transactions and Consistency

Where to draw the boundary of a unit of work, the effect isolation levels have as seen from the application, how optimistic and pessimistic locking resolve conflicts, the cost of atomicity across services, and the immutable transaction design that makes retrying safe.

  1. 01 Transaction Boundaries Where to draw the boundary of a unit of work: the half-finished state that unbounded writes leave behind, why opening a transaction per repository fails to compose, a unit of work that keeps the transaction boundary in one place, the savepoint used at nested boundaries, and the effect boundary width has on wait time.
  2. 02 Application Impact of Isolation Levels How read anomalies look from the application: measuring nonrepeatable reads and phantom rows by running the same query twice, an invariant between two aggregate queries violated across thousands of samples, and the cost of isolation.
  3. 03 Optimistic and Pessimistic Locking Two solutions to a concurrent update conflict: optimistic locking, which detects the conflict at write time with a version column; pessimistic locking, which makes the decision under a lock; the fact that an upgrade conflict cannot be resolved by waiting; and a comparison of the two approaches' attempt count and duration.
  4. 04 Distributed Transaction Problem Atomicity's failure to cross a service boundary: writes to two separate databases left half-finished, unwinding with compensating steps, the fact that compensation can fail too, and the outbox pattern, where the status change and the message are written in the same transaction.
  5. 05 Idempotent Transactions Making retries safe: measuring how three write shapes behave under repetition, making a retry idempotent through assignment, derivation, and conflict behavior, storing an idempotency key together with the response in the same transaction, and fixing the outbox consumer.

03

Performance Problems

Measuring and resolving the N+1 problem that multiplies the query count, the cost of over-fetching in bytes transferred, batch insert and update patterns, diagnosing a slow query seen from the application by reading its plan, and the cost of deep pagination.

  1. 01 N+1 Query Problem Query count growing with record count: measurement with a counting wrapper, comparing one-by-one loading against batch fetch and join, a scaling table, the effect of round-trip cost, and writing the query budget as a test.
  2. 02 Over-Fetching Measuring transferred data in bytes: the difference between fetching every column and fetching only the needed ones, the contribution of row limiting, whether aggregation belongs in the application or the database, and star selection silently dropping data on column collision.
  3. 03 Batch Operations Scaling the write path: writing the same two thousand rows four ways and comparing durations, the decisive share of the transaction boundary, how batch size relates to statement count and query length, and measuring statement count in a batch update.
  4. 04 Reading Query Plans Diagnosing a slow query from the application: a wrapper that times every query and logs the one crossing a threshold together with its plan, the same query's plan without and with an index, a condition's phrasing changing the plan, and grouping slow queries by query fingerprint.
  5. 05 Pagination Cost Two problems of deep pagination: duration growing with the offset value, the constant cost of keyset pagination, records skipped when the result set changes, and the interface constraints each approach brings.

04

Business Logic Placement

The responsibility split between the presentation, application, domain and infrastructure layers, data transfer objects that separate the outer contract from the inner model, the boundary between input validation and a domain rule, domain events that couple side effects loosely, and a persistence-ignorant domain model.

  1. 01 Layer Responsibilities The separation of the presentation, application, domain and infrastructure layers: auditing the dependency direction rule through the import graph, counting the findings in the violating version, and inverting the dependency by moving the contract into the domain layer.
  2. 02 Data Transfer Objects Separating the outer contract from the inner model: producing two different views from the same domain object, testing that the outer contract does not change when a field is added to the inner model, counting the fields returning the domain object directly leaks, and mass assignment in the incoming direction.
  3. 03 Validation Layers Separating input validation from the domain rule: the different responses the same body produces in two layers, counting how many queries each layer's decision needs, the domain rule's dependence on data state, and the database constraint that gives the final guarantee.
  4. 04 Domain Events Loosely coupling side effects through event publication: attaching more than one listener to the same publish call, counting the wrong notifications an event published inside a transaction produces after a rollback, and bringing that count to zero with the outbox pattern.
  5. 05 Persistence-Ignorant Domain Model Writing business rules as pure functions: the decision equality of a database-free version and a database-coupled version, the duration ratio across a 64-state scan, and counting the persistence trace left in the domain module.

Start typing to search.

↑↓ ↵ Esc navigate · open · close