Data Persistence and Database Integration

Introduction

Most useful backend applications must preserve information beyond the lifetime of a single HTTP request. User accounts, products, orders, payments, messages and audit records therefore require data persistence: the reliable storage and retrieval of data in a database or another durable medium.

In a Node.js and Express application, database integration is not simply a matter of opening a connection and issuing queries. Professional development requires decisions about data modelling, validation, consistency, performance, security, migrations and error handling. This chapter introduces both relational and NoSQL approaches and shows how ORM and ODM libraries can provide structured access to them.

The examples use modern JavaScript syntax. Relational examples use PostgreSQL with Prisma concepts, while NoSQL examples use MongoDB with Mongoose concepts. The same principles apply to other database technologies.

Learning Objectives

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

• Explain persistence, relational databases, NoSQL databases, ORM and ODM tools.
• Design tables, documents, relationships, constraints and indexes.
• Configure database access safely in an Express application.
• Implement CRUD operations using an ORM or ODM.
• Apply validation at request, model and database levels.
• Explain transactions, isolation, migrations and connection pooling.
• Select relational or NoSQL storage according to application requirements.
• Identify performance, security, governance and reliability risks.

1. The Persistence Layer

A typical backend has several layers:

1. Route layer receives an HTTP request and selects an endpoint.
2. Controller layer translates the request into an application operation.
3. Service layer applies business rules and coordinates work.
4. Persistence layer reads and writes durable data.
5. Database stores data and enforces some integrity rules.

Separating these layers improves testability and reduces coupling. A controller should not contain a long SQL statement, and a database model should not decide HTTP status codes.

A database connection is a scarce resource. Node.js applications commonly use a connection pool rather than opening a new physical connection for every request. A pool maintains a controlled number of connections, assigns one to a query, and returns it when the query completes. Pool size must reflect database capacity, expected concurrency and deployment topology; creating excessive connections can overload the database.

Sensitive connection information belongs in environment variables, such as DATABASE_URL, not in source code. In production, secrets should be supplied through a secret-management system and rotated periodically.

2. Relational Database Concepts

A relational database stores data in tables consisting of rows and columns. A table normally represents an entity or relationship. For example, an online store might contain customers, products, orders and order_items.

A primary key uniquely identifies a row. A foreign key references a key in another table and expresses a relationship. A one-to-many relationship can be represented by placing the parent key in the child table. Thus, an order may contain customer_id, while one customer may have many orders.

A many-to-many relationship is represented by a junction table. For example, course_students(course_id, student_id) connects courses and students. The pair of foreign keys is often a composite primary key.

Normalisation

Normalisation is a design discipline that reduces duplicated data and update anomalies. In a simplified design:

• First normal form requires atomic, indivisible values and no repeating groups.
• Second normal form requires first normal form and removal of dependencies on only part of a composite key.
• Third normal form requires second normal form and removal of non-key attributes that depend on other non-key attributes.

Normalisation is not an absolute command to split every table. Read-heavy systems may deliberately use denormalisation to reduce joins, but duplicated values then require a clear update strategy.

Constraints

Important relational constraints include:

Constraints should be enforced by the database even when application validation also exists. Application validation gives users useful messages; database constraints protect integrity when data enters through another service, a script or a race condition.

3. NoSQL and Document Databases

NoSQL is a broad category including document, key-value, wide-column and graph databases. A document database stores records such as JSON-like documents. A customer document might contain an address object and an array of preferences.

Document databases are useful when:

• records have naturally hierarchical structures;
• the application commonly reads an aggregate as one unit;
• the schema evolves frequently;
• horizontal distribution and high write throughput are important.

They are less suitable when an application requires many complex joins, strict cross-record constraints or sophisticated financial transactions.

Document design normally follows access patterns. Embedding stores related data inside a document. It can make a read efficient, but documents may grow excessively or create duplicated information. Referencing stores an identifier for another document. It reduces duplication but requires additional queries or aggregation.

There is no universal superiority between relational and document databases. The correct choice depends on consistency requirements, query patterns, data relationships, operational skills, scale and governance.

4. Schema and Model Design

A schema is a formal description of allowed data structures and constraints. A model is an application-level representation used to create, query and update records. In an ORM, models usually represent relational tables; in an ODM, models represent document collections.

A useful design process is:

1. Identify business entities and important events.
2. Identify attributes and their types.
3. Identify relationships and cardinalities.
4. Identify invariants, such as “an order must have at least one item.”
5. Identify common read and write operations.
6. Select keys and indexes.
7. Decide which rules belong in the application and which belong in the database.
8. Test the design with realistic data and queries.

Avoid storing derived values unless there is a clear performance reason. If an order total is stored, the application must define whether it is a historical snapshot or a value recalculated from current prices.

Example relational schema

CREATE TABLE customers (
  id BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

The database generates an identifier and timestamp, requires an email and name, prevents duplicate emails, and prevents an order from referring to a nonexistent customer.

5. CRUD Through an ORM

CRUD means Create, Read, Update and Delete. An ORM maps application objects to relational records and generates SQL. It can provide type checking, parameterisation, relation handling and migration support, although developers still need to understand SQL and query performance.

An illustrative Prisma-style model is:

model Customer {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  createdAt DateTime @default(now())
  orders    Order[]
}

model Order {
  id         Int      @id @default(autoincrement())
  customerId Int
  status     String
  createdAt  DateTime @default(now())
  customer   Customer @relation(fields: [customerId], references: [id])
  @@index([customerId])
}

A service function could perform a create operation:

export async function createCustomer(data, prisma) {
  return prisma.customer.create({
    data: {
      email: data.email.trim().toLowerCase(),
      name: data.name.trim()
    },
    select: { id: true, email: true, name: true, createdAt: true }
  });
}

The select clause avoids returning unnecessary fields. In real systems, passwords, tokens and internal metadata should never be returned by default.

A filtered, paginated read might be written as:

const customers = await prisma.customer.findMany({
  where: { name: { contains: search, mode: 'insensitive' } },
  orderBy: { createdAt: 'desc' },
  skip: offset,
  take: limit,
  select: { id: true, email: true, name: true }
});

Offset pagination is simple but may become inefficient for very large offsets. Cursor pagination, based on a stable indexed key, is often more reliable for continuously changing datasets.

Updates should target a specific record and should not silently overwrite unrelated fields. Deletes require careful policy decisions. A hard delete removes the record; a soft delete marks it as deleted, often with deleted_at. Soft deletion supports recovery and auditing but complicates every query and does not automatically satisfy retention laws.

6. CRUD Through an ODM

Mongoose provides schemas, models, casting, validation and middleware for MongoDB. An illustrative schema is:

import mongoose from 'mongoose';

const orderSchema = new mongoose.Schema({
  customerId: { type: mongoose.Schema.Types.ObjectId, required: true, index: true },
  status: {
    type: String,
    enum: ['pending', 'paid', 'cancelled'],
    default: 'pending'
  },
  items: [{
    productId: { type: mongoose.Schema.Types.ObjectId, required: true },
    quantity: { type: Number, required: true, min: 1 },
    unitPrice: { type: Number, required: true, min: 0 }
  }]
}, { timestamps: true });

const Order = mongoose.model('Order', orderSchema);

The model can be queried with Order.find, inserted with Order.create, modified with findByIdAndUpdate, and removed with deleteOne. Queries should use projections, filters and limits rather than returning an unbounded collection.

ODM validation is valuable, but it does not replace application authorisation or all database guarantees. For example, validating that a quantity is positive does not prove that the authenticated user owns the order.

7. Validation and Error Handling

Validation occurs at several boundaries:

• Transport validation: checks that the HTTP body has the expected shape.
• Business validation: checks rules such as stock availability or permitted status transitions.
• Persistence validation: checks types, constraints and relationships.

A schema-validation library such as Zod or Joi can reject malformed input before a service function runs. Never trust client-provided identifiers, prices, roles or ownership claims.

Errors should be translated consistently. Typical responses include:

The server should log technical details while returning a safe public message. SQL statements, credentials and personal data must not be written indiscriminately to logs.

8. Indexing and Query Performance

An index is an auxiliary data structure that helps the database locate rows or documents without scanning the entire collection. Indexes improve selected reads but consume storage and slow inserts, updates and deletes because the index must also be maintained.

Create indexes from measured query patterns. Common examples include indexes on unique email addresses, foreign keys used in joins, and fields frequently used in filtering and ordering.

A composite index on (customer_id, created_at) can support queries filtering by customer and ordering by creation time. Column order matters: an index is most useful when its leading columns match the query’s filtering or ordering pattern. Use EXPLAIN or the database’s query-plan tools to verify actual behaviour.

Do not index every field. Low-selectivity fields, such as a Boolean with nearly equal values, may provide little benefit unless combined with other columns or supported by a partial index. Index design must be revisited as data volume and workload change.

9. Transactions and Consistency

A transaction groups operations into one logical unit. Its commonly described properties are ACID:

• Atomicity: all operations commit or none do.
• Consistency: committed data satisfies defined rules.
• Isolation: concurrent transactions do not produce prohibited intermediate effects.
• Durability: committed data survives normal failure.

Consider transferring 100 units from account A to account B. The debit and credit must succeed together. If the debit succeeds but the credit fails, the system has created or destroyed value. A transaction prevents this partial result.

await prisma.$transaction(async (tx) => {
  const account = await tx.account.findUnique({ where: { id: sourceId } });
  if (!account || account.balance < 100) throw new Error('Insufficient funds');

  await tx.account.update({
    where: { id: sourceId },
    data: { balance: { decrement: 100 } }
  });
  await tx.account.update({
    where: { id: destinationId },
    data: { balance: { increment: 100 } }
  });
});

The amount and currency are omitted here for brevity; production financial systems should use integer minor units or an exact decimal type rather than binary floating-point arithmetic.

Isolation levels determine how concurrent transactions interact. Stronger isolation can reduce anomalies but may increase locking, waiting or serialization failures. Transactions should be short, avoid network calls inside their boundaries, and be retried only when the database documents that a failure is transient and the operation is safe to retry.

10. Migrations and Seed Data

A migration is a versioned, repeatable change to database structure or data. Examples include adding a column, creating an index or replacing a nullable field with a required field. Migrations provide a history that can be applied consistently across development, testing and production.

A safe production migration often follows an expand-and-contract sequence:

1. Add the new structure without breaking old code.
2. Deploy code that can use both old and new structures.
3. Backfill data in controlled batches.
4. Switch reads and writes to the new structure.
5. Remove obsolete structures in a later migration.

Large migrations should be monitored and tested against a production-sized copy. A migration is not the same as a seed script: seed data creates predictable development or test records, whereas migrations change the schema or required production data.

11. Applied Case: Course Registration Service

This is an illustrative case. A college is building an Express service through which authenticated students register for courses. Each course has a capacity, and a student must not register twice for the same course.

Problem

A naïve implementation checks available places, then inserts a registration. Under concurrent requests, two transactions may both observe one remaining place and both register, exceeding capacity.

Analysis

The system needs a relational database because registration has strong relationships and constraints: students, courses and registrations. A unique constraint on (student_id, course_id) prevents duplicate registration. Capacity requires a transaction and a concurrency strategy.

Approach

The design includes:

CREATE TABLE registrations (
  student_id BIGINT NOT NULL REFERENCES students(id),
  course_id BIGINT NOT NULL REFERENCES courses(id),
  registered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (student_id, course_id)
);

The service starts a transaction, locks or safely updates the course row, verifies capacity, inserts the registration, increments the enrolled count, and commits. If the unique constraint fails, the service returns 409 Conflict. If capacity is unavailable, it returns a domain-specific error. The route validates identifiers and authorises the student before invoking the service.

Outcome

The design prevents duplicate registrations, preserves course capacity under concurrent requests and provides a clear audit timestamp. Automated tests should include simultaneous registration attempts, rollback after simulated failure, and attempts by unauthorised users.

Learning Points

Business rules that involve multiple records should not rely on a sequence of unprotected application checks. Constraints, transactions and appropriate locking must work together.

12. Security, Governance and Contemporary Practice

Database security includes least-privilege accounts, encrypted connections, secure secret handling, parameterised queries and restricted network access. ORMs reduce injection risk when used correctly, but unsafe raw-query APIs can reintroduce it. Never construct SQL or query operators directly from untrusted input.

Personal data requires governance. Teams should identify what data is collected, why it is retained, who may access it and when it should be deleted. Audit trails should record important changes without exposing unnecessary personal information. Backups must be encrypted, tested and subject to retention rules.

Modern systems increasingly use managed databases, read replicas, change-data-capture pipelines and observability platforms. AI-assisted coding can generate models or queries, but generated code must be reviewed for injection vulnerabilities, incorrect joins, missing constraints, privacy violations and inefficient query plans. AI does not replace schema reasoning or production testing.

Challenges and Limitations

Database integration introduces several trade-offs:

• Relational schemas provide strong integrity but can require planned migrations.
• Document databases provide flexibility but may duplicate data and complicate cross-document consistency.
• ORMs improve productivity but can hide inefficient queries or produce unexpected joins.
• Indexes accelerate some queries while increasing write cost.
• Transactions protect invariants but can reduce throughput when held too long.
• Soft deletion supports recovery but complicates uniqueness, reporting and privacy deletion.
• Distributed databases improve scale and availability but introduce replication lag and more complex consistency choices.

Professional developers therefore measure real workloads, inspect query plans, test failure modes and document design decisions.

Best-Practice Checklist

• Keep database credentials outside source control.
• Use migrations rather than manually editing production schemas.
• Validate input at the API boundary and enforce critical rules in the database.
• Use parameterised queries or safe ORM APIs.
• Select only required fields and paginate collection endpoints.
• Add indexes based on measured access patterns.
• Use transactions for multi-step invariants.
• Keep transactions short and retry only safe transient failures.
• Return consistent errors without leaking internal details.
• Test concurrency, rollback, backups and restoration.
• Monitor latency, error rates, connection usage and slow queries.

Chapter Summary

Data persistence enables an Express application to retain and protect information across requests and deployments. Relational databases organise data into related tables and provide powerful constraints, joins and transactions. NoSQL document databases organise data around aggregates and access patterns, offering flexibility and useful distribution characteristics. Neither approach is universally best.

Effective integration requires deliberate schema and model design, layered validation, secure connection management and appropriate ORM or ODM use. CRUD operations should be bounded, authorised and efficient. Indexes must be selected from real query patterns and verified with query plans. Transactions protect multi-step business invariants, while migrations provide controlled evolution of database structure. Security, privacy, observability, backup and recovery are essential parts of professional database engineering.

Key Terms

Persistence: Durable storage of data beyond a process or request lifetime.

Relational database: A database that stores structured data in related tables.

NoSQL: A broad family of non-relational database technologies.

Primary key: A value or set of values that uniquely identifies a record.

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

Constraint: A database rule that restricts invalid data.

Normalisation: Organising data to reduce duplication and anomalies.

Denormalisation: Deliberate duplication used to improve particular access patterns.

Document database: A database that stores JSON-like records or documents.

ORM: Object-relational mapping library for accessing relational data through application models.

ODM: Object-document mapping library for accessing document databases.

CRUD: Create, Read, Update and Delete operations.

Index: A data structure that accelerates selected searches.

Transaction: A logical unit of database work that commits or rolls back as a whole.

ACID: Atomicity, Consistency, Isolation and Durability properties.

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

Connection pool: A managed set of reusable database connections.

Soft delete: Marking a record as deleted instead of physically removing it.

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

Review Questions

Short Answer Questions

1. What is the role of a foreign key?
2. State the four ACID properties.
3. Why should database credentials not be stored in source code?
4. What is the difference between an ORM and an ODM?
5. Give one advantage and one risk of embedding data in a document database.

Long Answer Questions

1. Explain how a relational schema for customers and orders should represent their relationship and integrity rules.
2. Discuss the advantages and limitations of using an ORM in an Express application.
3. Explain how indexes work and why indexing every field is not good practice.
4. Describe a safe expand-and-contract migration strategy.
5. Compare relational and document databases for an e-commerce application.

Analytical / Application Questions

1. Design a persistence strategy for a course-registration service in which students cannot register twice and course capacity must never be exceeded. Identify tables, constraints and transaction steps.
2. An endpoint that lists customer orders becomes slow as the database grows. Propose a systematic investigation involving query plans, indexes, selected fields and pagination.
3. An application stores customer addresses both in a customer table and in order documents. Analyse the benefits and risks of this design, and recommend when historical address snapshots are justified.

Multiple Choice Questions with Answers

Question 1

Which database feature uniquely identifies each row in a relational table?

A. Foreign key

B. Primary key

C. Index hint

D. Migration

Correct Answer: B. Primary key

Question 2

Which operation best represents a many-to-many relationship in a relational design?

A. Adding a junction table

B. Adding a second primary key to each row

C. Embedding every record in one column

D. Removing all foreign keys

Correct Answer: A. Adding a junction table

Question 3

What is the main purpose of a database transaction?

A. To format JSON responses

B. To guarantee that related operations are treated as one logical unit

C. To replace all indexes

D. To encrypt HTTP headers

Correct Answer: B. To guarantee that related operations are treated as one logical unit

Question 4

Which practice most directly reduces SQL injection risk?

A. Using longer table names

B. Using parameterised queries

C. Adding more application routes

D. Increasing connection-pool size

Correct Answer: B. Using parameterised queries

Question 5

What is a likely cost of adding many indexes?

A. Writes may become slower and storage usage may increase

B. Foreign keys stop working

C. Transactions become impossible

D. All queries return uncommitted data

Correct Answer: A. Writes may become slower and storage usage may increase

Question 6

Which constraint prevents two students from registering for the same course twice?

A. A composite unique or primary key on student_id and course_id

B. A default timestamp

C. A nullable name column

D. A document projection

Correct Answer: A. A composite unique or primary key on student_id and course_id

Question 7

What is the safest general approach for a large breaking schema change?

A. Delete the database and recreate it during peak traffic

B. Use an expand-and-contract migration

C. Edit production tables manually without a record

D. Disable all constraints permanently

Correct Answer: B. Use an expand-and-contract migration

Question 8

Which statement best describes an ODM?

A. A tool that maps application objects to document-database records

B. A protocol for encrypting passwords

C. A network load balancer

D. A database backup format

Correct Answer: A. A tool that maps application objects to document-database records

Question 9

Why should collection endpoints usually use pagination?

A. To prevent an unbounded response and reduce memory and query cost

B. To disable validation

C. To remove the need for authentication

D. To guarantee that records never change

Correct Answer: A. To prevent an unbounded response and reduce memory and query cost

Question 10

In the course-registration case, why is checking capacity outside a transaction unsafe?

A. It prevents JSON parsing

B. Concurrent requests may both observe the same remaining place

C. It makes indexes impossible

D. It automatically deletes the course

Correct Answer: B. Concurrent requests may both observe the same remaining place