Full-Stack Application Integration

Introduction

A relational database becomes useful to an application only when the application can communicate with it reliably, securely, and efficiently. In a full-stack system, the database is connected to server-side code through a driver or database client. The server then exposes business operations through services and APIs consumed by browser, mobile, or other client applications.

This integration layer is more than a sequence of SQL statements. It must manage connections, transactions, validation, authorization, errors, schema evolution, performance, testing, and deployment. Poor integration can produce SQL injection, lost updates, inconsistent data, connection exhaustion, slow pages caused by N+1 queries, and deployments that cannot be reproduced.

This chapter examines the principal patterns used to connect MySQL and PostgreSQL databases to server-side applications. Examples use SQL-like syntax and JavaScript-style pseudocode, but the principles also apply to Python, Java, C#, PHP, Go, and other platforms.

Learning Objectives

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

• Explain the roles of drivers, database clients, repositories, services, and APIs.
• Use parameterized queries and prepared statements to prevent injection.
• Design connection pools and transaction boundaries.
• Apply repository and ORM patterns appropriately.
• Manage migrations and seed data safely.
• Diagnose and prevent N+1 query problems.
• Test database-dependent code and integrate those tests into CI/CD.
• Evaluate the security, reliability, and maintainability of an integration design.

1. The Application–Database Boundary

A typical full-stack request follows this path:

1. A client sends an HTTP request.
2. A route handler authenticates and validates the request.
3. A service applies business rules.
4. A repository or data-access component executes SQL through a driver or ORM.
5. The database returns rows or an error.
6. The service maps the result to an application model.
7. The API returns an appropriate response.

The layers have different responsibilities. A route handler should not normally contain complex SQL. A repository should not decide whether a user is entitled to perform a business operation. A database constraint should protect data integrity even when an application bug bypasses application validation.

A useful separation is:

This separation is not absolute. Small applications may combine layers, while larger systems may add domain, caching, messaging, or authorization components. The important principle is to make responsibilities explicit and testable.

2. Drivers, Clients, and Prepared Statements

A database driver implements the database protocol and exposes an application-language interface. PostgreSQL drivers commonly use the PostgreSQL wire protocol and positional parameters such as $1, $2; MySQL drivers may use ? placeholders. A client library often adds conveniences such as pooling, transaction helpers, type conversion, and error classification.

A parameterized query keeps SQL structure separate from values:

const result = await db.query(
  'SELECT id, email, display_name FROM users WHERE email = $1',
  [email]
);

The database or driver sends the statement and its values separately. A malicious value such as x' OR '1'='1 is treated as data rather than executable SQL.

By contrast, string concatenation is unsafe:

// Unsafe: never construct SQL this way.
const sql = "SELECT * FROM users WHERE email = '" + email + "'";

Prepared statements may be explicitly prepared and reused:

PREPARE find_user(text) AS
SELECT id, email FROM users WHERE email = $1;

EXECUTE find_user('[email protected]');

The exact API differs between MySQL and PostgreSQL. Parameterization protects values, but it does not automatically parameterize identifiers such as table names, column names, or sort directions. Dynamic identifiers should be selected from a fixed allow-list:

const allowedSorts = { newest: 'created_at DESC', name: 'display_name ASC' };
const orderBy = allowedSorts[sort] ?? allowedSorts.newest;
const sql = `SELECT id, display_name FROM users ORDER BY ${orderBy}`;

The interpolated text is safe here because it comes only from a controlled mapping. User input must never be inserted directly into SQL syntax.

Other injection surfaces include dynamic filters, reporting expressions, shell commands, HTML, and logs. Parameterized SQL solves SQL value injection, not every application security problem. Input validation, authorization, output encoding, and least-privilege database accounts remain necessary.

3. Connection Management and Pooling

Opening a database connection involves network communication, authentication, session initialization, and resource allocation. Creating a new connection for every request is therefore expensive and can overwhelm the database. A connection pool maintains a bounded set of reusable connections.

A normal pooled operation is:

1. Request a connection from the pool.
2. Execute one or more statements.
3. Commit or roll back if a transaction is used.
4. Release the connection in a finally block.

const connection = await pool.connect();
try {
  await connection.query('BEGIN');
  const account = await connection.query(
    'SELECT balance FROM accounts WHERE id = $1 FOR UPDATE',
    [accountId]
  );
  // Business operation and updates occur here.
  await connection.query('COMMIT');
} catch (error) {
  await connection.query('ROLLBACK');
  throw error;
} finally {
  connection.release();
}

A pool should have a maximum size, acquisition timeout, idle timeout, and health or error handling policy. A larger pool is not automatically faster. If many application processes each create large pools, the combined number of connections may exceed the database capacity. Pool sizing must consider database limits, workload, query duration, number of application instances, and available CPU and memory.

Applications should use separate configuration for development, testing, and production. Credentials belong in a secret manager or protected environment variables, not source control. Production connections generally require encryption in transit, certificate verification, and restricted network access.

Long transactions are harmful because they retain locks, delay vacuum or cleanup work, and increase the probability of conflicts. A transaction should include the smallest complete unit of business work, not unrelated remote API calls or slow user interactions.

4. Transactions and Concurrency

A transaction groups operations into an atomic unit. The commonly used ACID properties are:

• Atomicity: all operations commit, or none do.
• Consistency: constraints and declared rules remain satisfied.
• Isolation: concurrent transactions observe results according to the selected isolation level.
• Durability: committed changes survive normal failures according to the database's durability guarantees.

Consider transferring 100 monetary units from account A to account B:

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 10 AND balance >= 100;

-- Application verifies that exactly one row was updated.

UPDATE accounts
SET balance = balance + 100
WHERE id = 20;

INSERT INTO transfers(from_account, to_account, amount)
VALUES (10, 20, 100);

COMMIT;

The balance check and update should be performed atomically. An application that first reads a balance and later updates it without protection may permit two concurrent requests to spend the same funds. Techniques include row locks such as SELECT ... FOR UPDATE, optimistic version columns, and carefully designed conditional updates.

Isolation levels represent trade-offs between concurrency and consistency. PostgreSQL and MySQL support several standard concepts, although implementation details differ. READ COMMITTED commonly prevents dirty reads while allowing a later statement to see newly committed data. REPEATABLE READ provides a stable transaction view in many situations. SERIALIZABLE offers the strongest general isolation but may cause serialization failures that the application must retry. The exact behavior must be verified against the selected database engine and version.

Deadlocks can occur when transactions lock resources in different orders. Applications should acquire related locks consistently, keep transactions short, detect deadlock errors, and retry safe operations with a bounded backoff. Retrying is unsafe when an operation has external side effects unless idempotency has been designed.

5. Repositories and Service Design

A repository presents a focused data-access interface. For example:

class UserRepository {
  constructor(pool) { this.pool = pool; }

  async findById(id) {
    const result = await this.pool.query(
      'SELECT id, email, display_name FROM users WHERE id = $1',
      [id]
    );
    return result.rows[0] ?? null;
  }

  async create(user) {
    const result = await this.pool.query(
      `INSERT INTO users (email, display_name)
       VALUES ($1, $2)
       RETURNING id, email, display_name`,
      [user.email, user.displayName]
    );
    return result.rows[0];
  }
}

Repositories centralize SQL, parameter mapping, and database-specific details. They also provide a seam for unit tests and make query review easier. A repository should not become a generic abstraction that hides every database capability. Complex reporting queries, locking, and database-specific features may be better represented explicitly.

A service coordinates repositories and applies business rules:

async function enrollStudent(studentId, courseId, repositories) {
  return repositories.transaction(async (tx) => {
    const course = await tx.courses.findByIdForUpdate(courseId);
    if (!course || course.capacity <= course.enrolledCount) {
      throw new Error('Course is full');
    }
    await tx.enrollments.insert(studentId, courseId);
    await tx.courses.incrementEnrollment(courseId);
  });
}

The transaction boundary belongs around the complete invariant: the enrollment and the capacity update must succeed together. The database should additionally enforce uniqueness, for example with a unique constraint on (student_id, course_id).

6. Object–Relational Mappers and Query Builders

An ORM maps tables and relationships to programming-language objects. It can provide migrations, validation hooks, change tracking, eager loading, and parameterized queries. A query builder provides composable SQL without requiring every statement to be handwritten.

ORMs improve productivity for conventional CRUD operations, but abstraction has costs:

• Generated SQL may be inefficient or difficult to inspect.
• Relationship loading can create N+1 queries.
• Database-specific capabilities may be obscured.
• Implicit transactions or cascades may surprise developers.
• Schema changes can be unsafe if generated without review.

A mature team treats ORM-generated SQL as production SQL: it is inspected with logs or an execution plan, measured, and reviewed. Raw SQL is appropriate for complex analytical queries, vendor-specific features, bulk operations, or performance-critical paths. Using an ORM and SQL together is often a sound choice when boundaries are clear.

7. The N+1 Query Problem

N+1 occurs when an application performs one query to obtain a collection and then one additional query for each item. For 1,000 parent rows, the application may issue 1,001 queries rather than one or two. Network latency, parsing, connection use, and database work can make this severely slower.

Problematic pattern:

const posts = await postRepository.findRecent();
for (const post of posts) {
  post.author = await userRepository.findById(post.authorId);
}

Possible solutions include a join, eager loading, batching, or a data-loader pattern:

SELECT p.id, p.title, u.id AS author_id, u.display_name
FROM posts AS p
JOIN users AS u ON u.id = p.author_id
WHERE p.created_at >= $1
ORDER BY p.created_at DESC;

The correct solution depends on cardinality and response requirements. Eagerly loading every relationship can create an overly large Cartesian result, so query shape should match the endpoint. Logging query counts in tests and examining EXPLAIN or EXPLAIN ANALYZE output are practical prevention techniques. EXPLAIN ANALYZE executes the query in PostgreSQL and should be used cautiously for statements with side effects; use a transaction rollback or a non-mutating test environment when appropriate.

8. Migrations and Seed Data

A migration is a versioned, ordered change to database structure or database-controlled data. Examples include creating a table, adding an index, or introducing a nullable column before application code begins writing it.

A reliable migration system records applied versions in a metadata table and executes pending migrations in order. Each migration should be reviewed for locking behavior, duration, reversibility, and compatibility with currently deployed application versions.

A safer production pattern for a large table is often:

1. Add a new nullable column.
2. Deploy code that can read both old and new forms.
3. Backfill in controlled batches.
4. Begin writing the new column.
5. Validate and enforce the constraint.
6. Remove obsolete columns only after all old code is gone.

This is known as an expand-and-contract approach. A migration that adds a non-null column with an expensive default may lock or rewrite a large table, depending on the database and version.

Seed data is controlled initial data used for development, demonstrations, or required reference values. Seed scripts should be deterministic and idempotent where possible. They should not insert fake production users with known passwords or overwrite production data. Test fixtures are different: they are scenario-specific data created and destroyed by tests.

Migration files should be committed with application code. The deployment system should apply migrations as a controlled release step, with backups, monitoring, and a rollback or forward-fix plan. Rolling back schema changes is not always safe once new application code has written data in the new format.

9. API Integration and Error Handling

An API should expose domain operations rather than merely mirror tables. For example, POST /enrollments expresses an operation, while a client should not be allowed to arbitrarily update an enrollment row by submitting database columns.

Input processing normally includes parsing, schema validation, authentication, authorization, and business validation. Database constraints remain the final integrity barrier. API responses should avoid leaking SQL text, stack traces, credentials, or internal identifiers unnecessarily.

Errors should be classified. A unique-constraint violation may become an HTTP 409 Conflict; invalid input may become 400 or 422; missing authorization may become 401 or 403; an unexpected database outage generally becomes 500 or 503. The mapping depends on the API contract. Logs should include a correlation identifier and safe contextual data, while sensitive values must be redacted.

Pagination is an integration concern. Offset pagination is simple but can become slow and unstable when rows are inserted or deleted. Keyset pagination uses a stable ordered key:

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT $3;

The (created_at, id) pair provides a deterministic continuation point when an index supports the ordering. The application must validate the limit and cap it to prevent excessive responses.

10. Testing Database-Dependent Code

Database integration tests execute real queries against a real database engine or a highly compatible controlled environment. They detect incorrect SQL, missing constraints, transaction mistakes, type differences, and migration failures that mocks cannot detect.

A useful testing strategy has several levels:

• Unit tests: test pure business logic with dependencies replaced by fakes or mocks.
• Repository integration tests: run repository queries against a disposable database.
• Service integration tests: verify transactions and interactions among repositories.
• API tests: send HTTP requests and verify status, data, authorization, and errors.
• End-to-end tests: exercise a deployed-like stack, used selectively because they are slower.

Each test should isolate its data. Options include a transaction rolled back after each test, truncation with controlled ordering, a fresh schema, or a disposable database container. Rollback-based isolation is fast but may not work when application code opens separate connections or uses asynchronous jobs outside the test transaction.

Mocks should not assert only that a particular SQL string was called; such tests can pass while the real query is invalid. They are valuable for service-level behavior, but repository behavior needs real database tests.

A CI pipeline commonly performs the following sequence:

1. Start a supported MySQL or PostgreSQL service.
2. Create a test database and restricted test credentials.
3. Apply migrations from an empty schema.
4. Load deterministic seed or fixture data.
5. Run unit, integration, and API tests.
6. Collect coverage, logs, and query diagnostics.
7. Destroy the database and publish results.

The CI database version should match production closely. Tests should not depend on execution order, developer-local state, current time without control, or network services that are unavailable in CI. Secrets used in CI must be short-lived and protected. Production data must not be copied into ordinary test environments without authorization, masking, and governance controls.

11. Illustrative Case: Course Enrollment Service

Background

A university platform stores students, courses, and enrollments in PostgreSQL. The first implementation retrieves courses, then queries the instructor and enrollment count separately for every course. It also concatenates a user-supplied search term into SQL.

Problem

During registration, response times increase sharply. Security review identifies SQL injection risk. Concurrent requests occasionally enroll more students than a course capacity permits. Local development works, but CI does not reliably detect the problems because tests use mocks and an in-memory substitute database.

Analysis

The system has three independent defects. First, the query pattern is N+1. Second, string concatenation allows input to alter SQL syntax. Third, the capacity check and enrollment insert are not one atomic operation. The test environment also differs from PostgreSQL, so database behavior is not being tested.

Approach

The team makes the following changes:

1. A PostgreSQL driver with a bounded pool is configured through environment-based secrets.
2. Search uses a parameterized query and a validated allow-list for sort columns.
3. A repository uses a join to return courses and instructor summaries in one query.
4. Enrollment is implemented as a transaction. The course row is locked with FOR UPDATE; the service verifies capacity, inserts the enrollment, and increments the count.
5. A unique constraint on (student_id, course_id) prevents duplicate enrollment.
6. Migrations create the schema and constraints; deterministic seeds provide reference courses.
7. CI starts PostgreSQL, applies migrations, and runs repository, service, and API tests against it.
8. Query-count assertions and periodic execution-plan review detect regression.

Outcome

The endpoint performs a bounded number of queries rather than one query per course. Malicious search input is treated as data. Concurrent enrollment attempts serialize on the course row, and a duplicate request receives a controlled conflict response. CI now detects SQL and migration defects before deployment.

Learning Points

Performance, security, correctness, and testability are connected. A repository pattern alone does not solve concurrency; a prepared statement alone does not guarantee authorization; and a passing unit-test suite does not prove that production SQL works. Reliable integration requires coordinated design across code, schema, deployment, and operations.

12. Professional Practice, Governance, and Contemporary Trends

Database integration involves governance as well as engineering. Personal data should be collected only for legitimate purposes, retained no longer than necessary, and protected through access control, encryption, auditing, and appropriate deletion processes. Regulations such as the GDPR and sector-specific privacy rules may impose obligations concerning access, correction, breach response, and data minimization. Exact legal duties depend on jurisdiction and organizational context; engineers should involve security, privacy, and legal specialists.

Use separate database roles for application runtime, migrations, reporting, and administration. The runtime role should have only the privileges required by the application. Audit sensitive operations, rotate credentials, patch drivers and database servers, and monitor failed authentication, slow queries, pool exhaustion, deadlocks, and migration duration.

Observability should connect API requests to database activity through correlation IDs, structured logs, metrics, and traces. Do not log passwords, tokens, complete payment details, or unrestricted personal data. Query logs should be protected because SQL parameters can contain confidential information.

AI-assisted coding can generate repository methods, migrations, and tests, but generated SQL must be reviewed for injection safety, missing authorization, incorrect joins, transaction errors, and destructive migrations. AI output is not evidence that a query is correct or performant. Human review, automated tests, schema constraints, and execution-plan analysis remain necessary.

Challenges and Limitations

No integration pattern eliminates trade-offs. Connection pools can conceal leaks until production load. ORMs accelerate ordinary development but can hide expensive SQL. Transactions improve correctness but reduce concurrency when held too long. Migrations provide repeatability but require operational planning for large data sets. Integration tests provide realism but are slower and more environment-dependent than unit tests. Microservices may require separate databases, making cross-service transactions difficult and encouraging asynchronous workflows or sagas.

The appropriate design depends on workload, consistency requirements, team expertise, deployment topology, regulatory constraints, and the capabilities of MySQL or PostgreSQL. The best design is observable, secure by default, explicit about consistency, and tested against the database engine that will actually run it.

Chapter Summary

Full-stack application integration connects client-facing APIs, server-side services, repositories, drivers, and relational databases. Drivers and clients provide protocol communication, while connection pools reuse a bounded set of connections. Parameterized queries and prepared statements separate SQL structure from values and are essential defenses against SQL injection.

Transactions provide atomicity and coordinate concurrent operations, but transaction boundaries should be short and aligned with business invariants. Repositories centralize data access; services coordinate rules and transactions; ORMs and query builders improve productivity but must not replace SQL review and performance analysis. N+1 queries are avoided through joins, batching, or carefully controlled eager loading.

Migrations make schema evolution reproducible, while seed data and fixtures support development and testing. API layers should validate input, enforce authorization, map errors safely, and expose domain operations. Reliable CI/CD workflows use a database engine compatible with production, apply migrations, execute integration tests, and protect credentials and data. Security, privacy, least privilege, observability, and human review remain essential throughout the lifecycle.

Key Terms

Driver: Software that implements communication between an application and a database.

Connection pool: A bounded collection of reusable database connections.

Prepared statement: A SQL statement whose structure is separated from supplied parameter values.

Parameterized query: A query using placeholders rather than interpolated user input.

Transaction: A logical unit of database work committed or rolled back as a whole.

ACID: Atomicity, consistency, isolation, and durability properties of transactions.

Isolation level: A database setting that defines how concurrent transactions observe one another.

Deadlock: A condition in which transactions wait indefinitely for resources held by one another.

Repository: A component that encapsulates data-access operations.

Service layer: A component that applies business rules and coordinates operations.

ORM: Object–relational mapper that represents database structures through application objects.

N+1 query problem: Inefficient execution of one collection query followed by one query per item.

Migration: A versioned change to database schema or controlled database data.

Seed data: Initial deterministic data used to make an environment usable.

Fixture: Test-specific data prepared for a defined scenario.

Keyset pagination: Pagination based on a stable continuation key rather than an offset.

Least privilege: Granting only the database permissions necessary for a task.

Idempotency: The property that repeating an operation produces the same intended result.

CI/CD: Continuous integration and continuous delivery or deployment practices.

Execution plan: The database optimizer’s planned or observed method for executing a query.

Review Questions

Short Answer Questions

1. What is the difference between a database driver and a connection pool?
2. Why are parameterized queries safer than string-concatenated SQL?
3. State the four ACID properties.
4. What is the N+1 query problem?
5. Why should production migrations be reviewed for locking and duration?

Long Answer Questions

1. Explain how an HTTP request should move through API, service, repository, driver, and database layers. Discuss the responsibilities of each layer.
2. Describe how you would design a transaction for course enrollment when course capacity must not be exceeded.
3. Compare handwritten SQL, query builders, and ORMs, including their respective strengths and risks.
4. Design a CI workflow for a PostgreSQL-backed application that tests migrations, repositories, services, and APIs.
5. Explain the expand-and-contract approach to changing a large production table.

Analytical / Application Questions

1. An endpoint retrieves 500 posts and then queries each post’s author separately. Diagnose the performance issue and propose two alternative query designs.
2. Two users simultaneously attempt to purchase the last item in inventory. Explain how a non-transactional implementation can fail and design a database-safe solution.
3. A team proposes replacing PostgreSQL integration tests with mocks because the tests are slow. Evaluate this proposal and recommend a balanced testing strategy.

Multiple Choice Questions with Answers

Question 1

Which technique most directly prevents user-supplied values from changing SQL syntax?

A. String concatenation

B. Parameterized queries

C. Increasing pool size

D. Offset pagination

Correct Answer: B. Parameterized queries

Question 2

What should an application normally do after acquiring a pooled connection?

A. Keep it permanently

B. Release it only when the server shuts down

C. Release it in a finally or equivalent cleanup block

D. Share it globally without coordination

Correct Answer: C. Release it in a finally or equivalent cleanup block

Question 3

Which ACID property means that a transaction’s operations all succeed or none take effect?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: A. Atomicity

Question 4

A query loads 100 courses and then performs one instructor query for each course. What problem does this illustrate?

A. Dirty write

B. N+1 queries

C. Migration drift

D. Connection encryption

Correct Answer: B. N+1 queries

Question 5

Which constraint is most appropriate for preventing a student from enrolling in the same course twice?

A. A unique constraint on student_id and course_id

B. An index on course title only

C. A nullable display_name column

D. A foreign key from courses to instructors only

Correct Answer: A. A unique constraint on student_id and course_id

Question 6

Why should dynamic ORDER BY choices usually come from an allow-list?

A. Identifiers cannot safely be treated as arbitrary parameter values

B. Allow-lists increase transaction duration

C. They eliminate the need for authentication

D. They automatically create indexes

Correct Answer: A. Identifiers cannot safely be treated as arbitrary parameter values

Question 7

Which practice best tests whether repository SQL is valid for the selected database engine?

A. Testing only mocked method calls

B. Testing against a real compatible database

C. Testing only user-interface colors

D. Disabling database constraints

Correct Answer: B. Testing against a real compatible database

Question 8

What is the main purpose of a migration?

A. To version and reproduce schema changes

B. To replace API authentication

C. To increase every connection pool automatically

D. To serialize all HTTP responses

Correct Answer: A. To version and reproduce schema changes

Question 9

Which approach is commonly used to protect a capacity invariant during concurrent enrollment requests?

A. Reading capacity without any update protection

B. Using a transaction with suitable locking or conditional update

C. Caching capacity permanently in the browser

D. Removing the capacity column

Correct Answer: B. Using a transaction with suitable locking or conditional update

Question 10

What is a principal advantage of keyset pagination over large offsets?

A. It requires no ordering

B. It uses a stable continuation key and can avoid scanning skipped rows

C. It disables indexes

D. It returns every row in one response

Correct Answer: B. It uses a stable continuation key and can avoid scanning skipped rows