Application Connectivity and Data Access Layers

Introduction

A backend application becomes useful when it can reliably store, retrieve, modify, and protect data. Database integration is therefore more than creating a connection string. It involves selecting a driver, managing connections, translating application objects into database operations, validating input, handling failures, and organising code so that database concerns do not spread throughout the application.

This chapter examines connectivity and data-access techniques for both MySQL, a relational database management system, and MongoDB, a document database. The discussion uses Node.js-style examples because the same architectural ideas apply to other backend platforms such as Java, Python, C#, and PHP.

The central design principle is separation of concerns:

HTTP route/controller → service layer → repository/data-access layer → driver/ORM/ODM → database

A controller should interpret a request and formulate a response. A service should implement business rules. A repository should perform persistence operations. The database driver, ORM, or ODM should manage communication with the database.

Learning Objectives

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

1. Explain the roles of database drivers, connection pools, ORMs, ODMs, repositories, and services.
2. Configure MySQL and MongoDB connections safely.
3. Use prepared statements and parameterised queries to reduce injection risk.
4. Compare relational mapping with document mapping.
5. Design repository and service layers for maintainable applications.
6. Apply transactions, validation, error handling, and lifecycle management appropriately.
7. Evaluate practical trade-offs between direct drivers and abstraction libraries.

1. Database Connectivity as an Application Responsibility

A database connection is a communication channel between an application process and a database server. The application must identify the server, authenticate, negotiate a protocol, send commands, receive results, and release resources. A driver implements the database-specific protocol and exposes a programming interface.

A typical connection configuration contains:

Credentials should not be embedded in source code. Configuration should be supplied through environment variables or a managed secret store. A production application should also use separate credentials for development, testing, staging, and production.

Drivers

A driver is the lowest-level commonly used application interface. Examples include a MySQL driver and the official MongoDB Node.js driver. Drivers provide control and usually expose database-native features, but developers must write more persistence code themselves.

Connection Pools

Opening a network connection for every request is inefficient. A connection pool creates or retains a bounded collection of reusable connections. When an operation begins, it borrows an available connection; after completion, it returns the connection to the pool.

Pool size is not simply a performance contest. Excessive connections can exhaust database memory, operating-system file descriptors, or server limits. Pool capacity should be tested under realistic load and coordinated with the number of application instances.

A pool does not mean that all operations are automatically safe. A transaction must remain associated with one connection or session, and that resource must be released even when an exception occurs.

2. Configuration and Secure Startup

A robust application validates configuration during startup rather than discovering errors during the first user request. For example:

const required = ['MYSQL_HOST', 'MYSQL_USER', 'MYSQL_PASSWORD', 'MYSQL_DATABASE'];

for (const name of required) {
  if (!process.env[name]) {
    throw new Error(`Missing configuration: ${name}`);
  }
}

In real systems, configuration validation should also check types and permitted ranges. A port should be numeric, a pool limit should be positive, and a production deployment should reject disabled certificate verification.

A configuration object may be constructed as follows:

const mysqlConfig = {
  host: process.env.MYSQL_HOST,
  port: Number(process.env.MYSQL_PORT || 3306),
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DATABASE,
  waitForConnections: true,
  connectionLimit: Number(process.env.MYSQL_POOL_SIZE || 10),
  ssl: process.env.NODE_ENV === 'production' ? {} : undefined
};

MongoDB uses a URI, but the same principles apply:

const client = new MongoClient(process.env.MONGODB_URI, {
  maxPoolSize: 10,
  serverSelectionTimeoutMS: 5000
});
await client.connect();
const database = client.db(process.env.MONGODB_DATABASE);

Applications should log connection success and health status without logging passwords, complete connection URIs, access tokens, or personal data.

3. MySQL Access with Drivers and Prepared Statements

MySQL represents data in tables composed of rows and columns. Relationships are expressed through keys, and SQL provides a declarative language for querying and modifying data.

A parameterised query separates SQL structure from input values:

const [rows] = await pool.execute(
  'SELECT id, email, display_name FROM users WHERE email = ?',
  [email]
);

The question mark is a parameter placeholder. The driver sends the value separately or safely binds it according to its protocol. This is fundamentally safer than concatenation:

// Unsafe: never construct SQL this way with user input
const sql = `SELECT * FROM users WHERE email = '${email}'`;

Prepared statements protect values from being interpreted as SQL syntax. They do not automatically make table names, column names, or sort directions safe. Dynamic identifiers must be selected from a server-controlled allow-list.

const allowedSorts = { newest: 'created_at DESC', name: 'display_name ASC' };
const orderBy = allowedSorts[sort] || allowedSorts.newest;
const [rows] = await pool.query(`SELECT id, display_name FROM users ORDER BY ${orderBy}`);

Transactions

A transaction groups operations into an atomic unit. In a relational system, the commonly discussed ACID properties are atomicity, consistency, isolation, and durability. A transaction should be committed only when all required operations succeed; otherwise it should be rolled back.

const connection = await pool.getConnection();
try {
  await connection.beginTransaction();
  await connection.execute(
    'UPDATE accounts SET balance = balance - ? WHERE id = ?',
    [amount, senderId]
  );
  await connection.execute(
    'UPDATE accounts SET balance = balance + ? WHERE id = ?',
    [amount, receiverId]
  );
  await connection.commit();
} catch (error) {
  await connection.rollback();
  throw error;
} finally {
  connection.release();
}

The example assumes that the application has already validated a positive amount, both accounts exist, and the debit cannot produce an invalid balance. A real financial system also requires concurrency controls, audit records, idempotency, and carefully selected isolation behaviour.

4. MongoDB Access with the Native Driver

MongoDB stores BSON documents in collections. Documents can contain nested objects and arrays, which often makes them convenient for aggregate data that is read together.

const users = database.collection('users');

const user = await users.findOne(
  { email: email },
  { projection: { email: 1, displayName: 1 } }
);

MongoDB queries are represented as data structures rather than SQL strings. Nevertheless, user input must still be validated. An application should not blindly accept an arbitrary object as a filter, because an attacker may submit operators or query structures that were not intended by the API.

const user = await users.findOne({ email: String(email) });

For updates, explicitly construct the permitted fields:

const changes = {};
if (typeof displayName === 'string') changes.displayName = displayName.trim();

await users.updateOne(
  { _id: userId },
  { $set: changes }
);

MongoDB supports multi-document transactions when deployed in a suitable topology, but transactions add coordination cost. A document model should first be designed so that frequently atomic data can be stored together where that is appropriate.

5. ORM and ODM Abstractions

An object-relational mapper (ORM) maps application objects or classes to relational tables. It may provide models, relationships, migrations, query builders, validation, and transaction helpers. Examples in the JavaScript ecosystem include Prisma, Sequelize, and TypeORM.

An object-document mapper (ODM) provides analogous abstractions for document databases. It may define schemas, models, validation, middleware, and query methods. Mongoose is a widely used MongoDB ODM.

Abstraction has advantages:

• repetitive mapping code is reduced;
• common queries become easier to express;
• relationships and validation can be documented in model definitions;
• migrations and generated types may improve consistency;
• testing can be simplified when persistence interfaces are isolated.

It also has costs:

• complex generated queries may be difficult to inspect;
• abstraction can hide indexes, joins, or network calls;
• inefficient loading may produce the N+1 query problem;
• database-specific capabilities may be inaccessible or awkward;
• developers may misunderstand the database because the model resembles ordinary objects.

An ORM or ODM does not remove the need to understand SQL, indexes, query plans, transactions, document size, consistency, and failure modes.

6. Repository and Service-Layer Patterns

A repository presents persistence operations in domain-oriented terms. It may expose methods such as findUserByEmail, saveOrder, or listAvailableCourses rather than exposing database syntax to every controller.

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

  async findByEmail(email) {
    const [rows] = await this.pool.execute(
      'SELECT id, email, display_name FROM users WHERE email = ?',
      [email]
    );
    return rows[0] || null;
  }

  async create(user) {
    const [result] = await this.pool.execute(
      'INSERT INTO users (email, display_name) VALUES (?, ?)',
      [user.email, user.displayName]
    );
    return { id: result.insertId, ...user };
  }
}

A service coordinates business rules:

class RegistrationService {
  constructor(userRepository, passwordHasher) {
    this.users = userRepository;
    this.passwordHasher = passwordHasher;
  }

  async register(input) {
    const email = input.email.trim().toLowerCase();
    if (!email.includes('@')) throw new Error('Invalid email');

    if (await this.users.findByEmail(email)) {
      throw new Error('Email already registered');
    }

    const passwordHash = await this.passwordHasher.hash(input.password);
    return this.users.create({
      email,
      displayName: input.displayName.trim(),
      passwordHash
    });
  }
}

The service owns the rule that an email must be unique in the business process, while the database should also enforce uniqueness with a unique constraint. Application checks alone are vulnerable to race conditions.

Dependency injection makes these components easier to test. A service can receive a fake repository in a unit test, while integration tests use a real database or an isolated test database.

7. Relational and Document Data-Access Decisions

The choice between MySQL and MongoDB should follow data and workload requirements rather than fashion.

Embedding is useful when related data is normally read together, has bounded growth, and is updated as one unit. Referencing is preferable when related data is shared, independently updated, or unbounded.

For either database, indexes should be designed from actual query patterns. An index can accelerate reads but consumes storage and may slow writes. Query plans, latency percentiles, error rates, and connection-pool saturation should be observed in testing and production.

8. Error Handling, Validation, and Lifecycle Management

Database failures are not all equivalent. A duplicate-key error may be converted into a client-facing conflict response. A timeout may be retried selectively. Authentication failure usually indicates a deployment problem. A lost connection may require reconnection or process recovery.

Error handling should:

1. validate input before database access;
2. map known database errors to stable application errors;
3. avoid exposing SQL, stack traces, credentials, or internal topology;
4. log enough context for diagnosis without logging sensitive data;
5. preserve correlation identifiers for tracing;
6. release connections and sessions in finally blocks.

Retries require care. Retrying a read is usually safer than retrying a non-idempotent write. A retry may duplicate an order or payment unless the operation has an idempotency key and a uniqueness constraint. Exponential backoff and a maximum attempt count are preferable to immediate unlimited retries.

When an application shuts down, it should stop accepting new work, allow active requests to finish within a deadline, and close pools or clients. In a long-running Node.js process, the following pattern is typical:

process.on('SIGTERM', async () => {
  await server.close();
  await pool.end();
  await mongoClient.close();
  process.exit(0);
});

9. Applied Case: Course Enrolment Platform

Background

Consider an illustrative diploma-project platform that stores student accounts, course information, and enrolments. Student and course records require strong uniqueness and relationships. Course announcements contain flexible metadata and may evolve frequently.

Problem

The first implementation places SQL queries directly in route handlers. It creates a new connection for every request, concatenates a search term into SQL, and inserts enrolments without checking concurrent requests. Announcement data is stored in a rigid table even though different courses require different fields.

Analysis

The architecture has four major weaknesses: injection risk, connection overhead, poor separation of concerns, and a race condition that may allow duplicate enrolments. The data is also split according to implementation convenience rather than access patterns.

Approach and Solution

The team creates a MySQL pool for students, courses, and enrolments. Unique constraints are added to users.email and the pair (student_id, course_id). Repository methods use prepared statements. A service validates course availability and creates an enrolment. The database constraint remains the final protection against duplicates.

MongoDB is selected for course announcements because each announcement may contain optional attachments, links, tags, and structured content. The collection receives validation rules for required fields and indexes for course identifier and publication date.

The route now performs authentication and input parsing, then calls enrolmentService.enrol. The service calls repositories and translates expected conflicts into a stable application error. Monitoring records request duration, database duration, pool wait time, and failed operations.

Outcome

The design reduces duplicated persistence code, makes security review more manageable, and permits unit tests of enrolment rules without a live database. The system also becomes easier to evolve because relational integrity and flexible announcement content are handled by technologies suited to their respective access patterns.

Learning Points

1. Database constraints and application validation should reinforce one another.
2. A pool should be created once and reused, not recreated per request.
3. Repository boundaries clarify where persistence logic belongs.
4. A service layer expresses business rules and coordinates operations.
5. Polyglot persistence is justified by distinct requirements, not by using more technologies for its own sake.

10. Governance, Privacy, and Contemporary Practice

Database access exposes governance responsibilities. Applications should collect only necessary data, define retention periods, restrict access by role, and encrypt sensitive information in transit and at rest where appropriate. Passwords must be stored as slow, salted password hashes, never as reversible encryption or plaintext.

Audit trails are important for administrative changes, financial operations, and access to sensitive records. Logs should be protected because they may contain identifiers or query parameters. Backups require access controls, encryption, retention policies, and restoration tests.

Modern data-access libraries increasingly provide generated types, schema migration tools, automatic validation, observability hooks, and serverless-friendly connection strategies. AI coding tools can generate queries or repository code, but generated code must be reviewed for injection vulnerabilities, incorrect transaction boundaries, excessive data retrieval, and accidental exposure of secrets. AI assistance does not replace database testing or professional accountability.

Best-Practice Checklist

• Use environment-based configuration and a secret manager.
• Validate configuration at startup.
• Reuse bounded connection pools.
• Use prepared statements or safely parameterised driver methods.
• Allow-list dynamic identifiers such as sort columns.
• Enforce important invariants with database constraints.
• Keep transactions short and release resources reliably.
• Design indexes from measured query patterns.
• Separate controllers, services, repositories, and models.
• Test normal, invalid, concurrent, timeout, and duplicate-request cases.
• Monitor latency, errors, pool utilisation, and slow queries.
• Protect personal data, credentials, backups, and logs.
• Close clients gracefully during deployment and shutdown.

Chapter Summary

Application connectivity includes drivers, configuration, authentication, pooling, error handling, and lifecycle management. MySQL applications use SQL, relational constraints, prepared statements, and transactions to manage structured and related data. MongoDB applications use document queries, collection design, validation, indexes, and—when necessary—transactions for document-oriented workloads.

ORMs and ODMs reduce repetitive mapping work but do not eliminate the need for database knowledge. Repository layers isolate persistence operations, while service layers coordinate business rules and transactions. Secure systems parameterise input, enforce constraints, validate configuration, protect secrets, observe performance, and handle failures deliberately. The most effective database design aligns technology, data structure, consistency requirements, and access patterns with the needs of the application.

Key Terms

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

Connection pool: A reusable, bounded set of database connections.

Configuration management: The controlled provision and validation of application settings.

Prepared statement: A parameterised database command whose structure is separated from input values.

SQL injection: An attack in which input changes the intended meaning of a SQL command.

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

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

ORM: Object-relational mapper that connects application objects with relational data.

ODM: Object-document mapper for document databases.

Repository: A component that encapsulates persistence operations.

Service layer: A component that implements and coordinates business rules.

Parameterisation: Supplying values separately from a query or command structure.

Index: A data structure that can accelerate selected database queries.

Embedding: Storing related document data inside a parent document.

Referencing: Connecting documents or records through identifiers.

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

Connection leak: Failure to return or close a database resource.

Migration: A controlled change to database structure or constraints.

Schema validation: Rules that restrict the structure or content of stored data.

Polyglot persistence: Using different data-storage technologies for distinct requirements.

Review Questions

Short Answer Questions

1. What is the purpose of a database driver?
2. Why are connection pools used in backend applications?
3. How does a prepared statement reduce SQL injection risk?
4. Distinguish an ORM from an ODM.
5. What is the role of a repository?

Long Answer Questions

1. Explain how configuration, pooling, transactions, and graceful shutdown contribute to reliable database integration.
2. Compare MySQL and MongoDB with respect to schema, relationships, transactions, and common design risks.
3. Describe the responsibilities of controllers, services, repositories, and drivers in a layered backend.
4. Explain the benefits and limitations of using an ORM or ODM instead of a native driver.
5. Discuss how validation, database constraints, error mapping, logging, and monitoring contribute to secure data access.

Analytical / Application Questions

1. A developer creates a new MySQL connection for every HTTP request and concatenates a search term into SQL. Identify the risks and propose a corrected design.
2. A course platform stores unbounded activity history inside one MongoDB document. Analyse the likely problems and recommend an alternative modelling strategy.
3. Two simultaneous requests enrol the same student in the same course. Design application and database safeguards that prevent duplicate enrolments and explain the transaction or constraint decisions involved.

Multiple Choice Questions with Answers

Question 1

Which component normally implements the database-specific communication protocol?

A. Controller

B. Database driver

C. Service layer

D. HTML template

Correct Answer: B. Database driver

Question 2

What is the main purpose of a connection pool?

A. To store database backups

B. To validate passwords

C. To reuse a bounded set of database connections

D. To replace database indexes

Correct Answer: C. To reuse a bounded set of database connections

Question 3

Which practice best protects values in a MySQL query from SQL injection?

A. String concatenation

B. Parameterised queries

C. Disabling transactions

D. Increasing pool size

Correct Answer: B. Parameterised queries

Question 4

Which layer should primarily coordinate business rules such as checking course availability?

A. Service layer

B. Network driver

C. Connection pool

D. Database port

Correct Answer: A. Service layer

Question 5

What is a principal advantage of embedding in MongoDB?

A. It always eliminates indexes

B. It stores frequently co-read bounded data together

C. It guarantees unlimited document growth

D. It enforces all relational foreign keys

Correct Answer: B. It stores frequently co-read bounded data together

Question 6

Why should a database also enforce a unique email constraint when the service checks for an existing email?

A. To increase password length

B. To prevent race-condition duplicates

C. To remove the need for validation

D. To disable transactions

Correct Answer: B. To prevent race-condition duplicates

Question 7

Which resource-handling action is essential after using a pooled MySQL connection?

A. Release the connection

B. Print the password

C. Delete the database

D. Change the SQL dialect

Correct Answer: A. Release the connection

Question 8

Which statement about ORMs and ODMs is correct?

A. They eliminate the need to understand database behaviour

B. They are used only for backups

C. They provide abstractions for mapping application data to databases

D. They prevent every possible performance problem

Correct Answer: C. They provide abstractions for mapping application data to databases

Question 9

Which operation is generally safest to retry without additional design?

A. A non-idempotent payment write

B. An unprotected order insertion

C. A read operation

D. A password reset write

Correct Answer: C. A read operation

Question 10

Which item should not be written to ordinary application logs?

A. Correlation identifier

B. Operation duration

C. Database password

D. Error category

Correct Answer: C. Database password