Senior Frontend Interview Guide, Part 16: Relational and NoSQL Databases

This article is Part 16 of 18 in The Senior Frontend Engineer Interview Guide, and includes 18 interview questions with model answers.

Frontend engineers rarely administer databases, but enterprise roles increasingly expect “full-stack awareness”: understanding how data is stored determines what APIs can efficiently provide, why some screens are slow, how pagination and sorting should work, and what consistency guarantees the UI can rely on. In fintech especially, the difference between a strongly consistent ledger in a relational database and an eventually consistent document store has direct consequences for what you can show users. This chapter covers relational fundamentals and SQL, transactions and isolation, indexing and performance, NoSQL families, consistency models, caching, and browser-side storage.

Relational Databases

Relational database management systems (RDBMS) such as PostgreSQL, Oracle, Microsoft SQL Server, MySQL, and IBM Db2 store data in tables of rows and columns with a fixed schema. Relationships are expressed with keys:

• Primary key: uniquely identifies a row (accounts.id).
• Foreign key: references a primary key in another table (transactions.account_id -> accounts.id), with the database enforcing referential integrity.
• Unique constraints, check constraints, and NOT NULL enforce data rules at the database level.

Banks rely heavily on relational databases (Oracle, Db2, SQL Server, and increasingly PostgreSQL) for core ledgers because of strong consistency, transactions, and mature tooling.

A Simple Banking Schema

CREATE TABLE customers (
  id            UUID PRIMARY KEY,
  full_name     TEXT NOT NULL,
  email         TEXT UNIQUE NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE accounts (
  id            UUID PRIMARY KEY,
  customer_id   UUID NOT NULL REFERENCES customers(id),
  type          TEXT NOT NULL CHECK (type IN ('checking', 'savings', 'credit')),
  currency      CHAR(3) NOT NULL,
  nickname      TEXT,
  opened_at     DATE NOT NULL
);

CREATE TABLE ledger_entries (
  id            BIGSERIAL PRIMARY KEY,
  account_id    UUID NOT NULL REFERENCES accounts(id),
  amount_minor  BIGINT NOT NULL,              -- signed: credits positive, debits negative
  description   TEXT NOT NULL,
  status        TEXT NOT NULL CHECK (status IN ('pending', 'posted', 'reversed')),
  posted_at     TIMESTAMPTZ,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_entries_account_created ON ledger_entries (account_id, created_at DESC, id DESC);

Money is stored as integer minor units (BIGINT) or NUMERIC(19,4), never FLOAT.

Normalization

Normalization organizes data to reduce redundancy and anomalies:

• First normal form (1NF): atomic values, no repeating groups.
• Second normal form (2NF): no partial dependency on part of a composite key.
• Third normal form (3NF): no transitive dependencies; non-key columns depend only on the key.

Normalized schemas avoid update anomalies (changing a customer’s email in one place). Denormalization deliberately duplicates data for read performance, such as storing a running balance or caching a payee name on a transaction for fast statement rendering.

SQL Essentials

You should be able to read and write basic queries and recognize inefficient ones.

-- Latest 50 posted transactions for an account
SELECT id, description, amount_minor, posted_at
FROM ledger_entries
WHERE account_id = $1 AND status = 'posted'
ORDER BY posted_at DESC, id DESC
LIMIT 50;

-- Balance per account for a customer
SELECT a.id, a.nickname, COALESCE(SUM(e.amount_minor), 0) AS balance_minor
FROM accounts a
LEFT JOIN ledger_entries e ON e.account_id = a.id AND e.status = 'posted'
WHERE a.customer_id = $1
GROUP BY a.id, a.nickname;

-- Monthly spending by category in 2026
SELECT date_trunc('month', posted_at) AS month, category, SUM(-amount_minor) AS spent_minor
FROM ledger_entries
WHERE account_id = $1 AND amount_minor < 0 AND posted_at >= '2026-01-01'
GROUP BY 1, 2
ORDER BY 1, 3 DESC;

-- Running balance with a window function
SELECT id, posted_at, amount_minor,
       SUM(amount_minor) OVER (ORDER BY posted_at, id) AS running_balance_minor
FROM ledger_entries
WHERE account_id = $1 AND status = 'posted';

Joins

Join	Returns
INNER JOIN	Rows with matches in both tables
LEFT JOIN	All rows from the left table, matched rows from the right (NULL if none)
RIGHT JOIN	All rows from the right table, matched from the left
FULL OUTER JOIN	All rows from both, matched where possible
CROSS JOIN	Cartesian product

Parameterized Queries

The $1 placeholders above are parameters. Building SQL by concatenating user input enables SQL injection; parameterized queries (prepared statements, or ORMs like JPA and Prisma) prevent it. Even though frontend engineers do not write backend SQL often, this is a standard security question.

The N+1 Query Problem

Loading a list of 50 transactions and then issuing one query per transaction to load its payee results in 51 queries. ORMs make this easy to do accidentally (lazy loading). Fixes: joins, batch loading (WHERE id IN (...)), or JPA fetch joins and entity graphs. The frontend version of the same problem is calling an API per list item; the fix is a batched or aggregated endpoint (a BFF).

Transactions and ACID

A transaction groups operations into a single unit of work with ACID properties:

• Atomicity: all operations succeed or none do. A transfer debits one account and credits another together.
• Consistency: transactions move the database from one valid state to another, respecting constraints.
• Isolation: concurrent transactions do not interfere in unexpected ways (degree depends on isolation level).
• Durability: once committed, changes survive crashes.

BEGIN;
INSERT INTO ledger_entries (account_id, amount_minor, description, status, posted_at)
VALUES ($from, -25000, 'Transfer to Savings', 'posted', now());
INSERT INTO ledger_entries (account_id, amount_minor, description, status, posted_at)
VALUES ($to, 25000, 'Transfer from Checking', 'posted', now());
COMMIT;

Isolation Levels

Level	Prevents	Allows
Read Uncommitted	Nothing significant	Dirty reads
Read Committed (PostgreSQL, Oracle, SQL Server default)	Dirty reads	Non-repeatable reads, phantoms
Repeatable Read (MySQL InnoDB default)	Dirty and non-repeatable reads	Phantoms (in the SQL standard; PostgreSQL prevents them too)
Serializable	All of the above anomalies	Nothing; may abort transactions that must be retried

Anomalies: a dirty read sees uncommitted data; a non-repeatable read gets different values reading the same row twice; a phantom read sees new rows appear in a repeated range query; a lost update happens when two transactions read and then overwrite each other’s changes.

To prevent lost updates (two users editing the same payee, or two concurrent withdrawals), systems use pessimistic locking (SELECT ... FOR UPDATE), optimistic locking (a version column checked on update, as in Chapter 15), atomic updates (UPDATE accounts SET balance = balance - 100 WHERE id = $1 AND balance >= 100), or serializable isolation.

Indexes and Query Performance

An index is a data structure (most commonly a B-tree) that lets the database find rows without scanning the whole table, at the cost of extra storage and slower writes.

• Composite indexes follow the leftmost-prefix rule: an index on (account_id, created_at) supports filtering by account_id, or by account_id and created_at, but not efficiently by created_at alone.
• Covering indexes contain all columns a query needs, avoiding table lookups.
• Partial indexes index only rows matching a condition (WHERE status = 'pending').
• Other index types: hash, GIN (full-text search and JSONB in PostgreSQL), GiST, BRIN for very large time-ordered tables.
• Use EXPLAIN ANALYZE to see the query plan: sequential scans on large tables, high row estimates, and sorts spilling to disk indicate missing indexes or poor queries.

Pagination and the Database

This is where database knowledge directly shapes frontend features.

Offset pagination:

SELECT * FROM ledger_entries WHERE account_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 5000;

The database must read and discard 5,000 rows to return 50, getting slower for later pages, and concurrent inserts shift rows between pages.

Keyset (cursor) pagination:

SELECT * FROM ledger_entries
WHERE account_id = $1
  AND (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

It uses the index to jump directly to the next page, performs consistently regardless of depth, and is stable under inserts. The API encodes (created_at, id) of the last row into an opaque cursor. The trade-off is no “jump to page 37”, which is rarely needed for transaction histories. The deterministic tie-breaker (id) ensures rows with the same timestamp are not skipped or duplicated.

Sorting and filtering in the UI should map to indexed columns; offering sort by any column on a table with millions of rows requires either appropriate indexes or a search engine.

NoSQL Databases

“NoSQL” covers non-relational databases designed for flexible schemas, horizontal scaling, or specific data access patterns.

Type	Examples	Data model	Typical uses
Document	MongoDB, Couchbase, Amazon DocumentDB, Azure Cosmos DB	JSON-like documents	User profiles, product catalogs, content, flexible configuration
Key-value	Redis, Amazon DynamoDB, Memcached	Key to value	Caching, sessions, rate limiting, feature flags, idempotency keys
Wide-column	Apache Cassandra, ScyllaDB, HBase, Bigtable	Rows with dynamic columns, partitioned	Time-series, event logs, high write throughput
Graph	Neo4j, Amazon Neptune	Nodes and edges	Fraud rings, relationship analysis, recommendations
Search	Elasticsearch, OpenSearch	Inverted indexes over documents	Full-text search, log analytics, transaction search
Time-series	InfluxDB, TimescaleDB	Time-stamped measurements	Metrics, market data

Document Databases (MongoDB)

// A customer preferences document
{
  _id: ObjectId("..."),
  customerId: "c-123",
  locale: "es-US",
  notifications: {
    email: true,
    sms: false,
    push: { enabled: true, quietHours: { start: "22:00", end: "07:00" } }
  },
  dashboardLayout: [
    { widget: "balances", position: 1 },
    { widget: "spending", position: 2 }
  ],
  updatedAt: ISODate("2026-03-14T15:09:26Z")
}

db.preferences.updateOne(
  { customerId: "c-123" },
  { $set: { "notifications.sms": true, updatedAt: new Date() } }
);
db.transactions.find({ accountId: "a-1", amountMinor: { $lt: 0 } })
  .sort({ postedAt: -1 }).limit(50);
db.transactions.aggregate([
  { $match: { accountId: "a-1", postedAt: { $gte: ISODate("2026-01-01") } } },
  { $group: { _id: "$category", spent: { $sum: "$amountMinor" } } },
  { $sort: { spent: 1 } }
]);

Document databases map naturally to JSON and to how frontends think about data. Schema design is driven by access patterns: data read together is stored together (embedding), and data that grows unbounded or is shared is referenced. MongoDB supports multi-document ACID transactions, secondary indexes, and schema validation.

Key-Value Stores (Redis and DynamoDB)

Redis is an in-memory data store used for caching, session storage, rate limiting (counters with expiry), distributed locks, leaderboards, pub/sub, and storing idempotency keys with a time-to-live. DynamoDB is a managed key-value and document database that scales horizontally, designed around partition keys and sort keys with single-table design patterns, offering single-digit millisecond latency at any scale.

When to Use Which

• Relational: core financial records, ledgers, anything requiring complex queries, joins, strong consistency, and transactions across entities.
• Document: flexible or evolving schemas, aggregates read as a unit (preferences, onboarding applications in progress, content).
• Key-value: caching, sessions, high-throughput simple lookups.
• Search engine: full-text search over transactions and documents, faceted filtering.
• Graph: fraud detection across networks of accounts and devices.

Most enterprises practice polyglot persistence: different stores for different needs, with a relational system of record and specialized stores for caching, search, and analytics.

Consistency and Distributed Data

CAP and PACELC

The CAP theorem states that in the presence of a network partition, a distributed system must choose between consistency (every read sees the latest write) and availability (every request gets a response). PACELC extends it: if there is a Partition, choose Availability or Consistency; Else, choose Latency or Consistency.

Eventual Consistency

In eventually consistent systems (many NoSQL databases by default, read replicas, caches, search indexes, and event-driven microservices), updates propagate over time, so a read immediately after a write may return old data.

Why this matters to the frontend:

• After a user creates a payee, the payee list (served from a replica or search index) may not include it yet. Solutions: use the write response to update the client cache directly, read from the primary for the user’s own recent writes (“read-your-writes” consistency), or show the new item optimistically as pending.
• Transaction search powered by Elasticsearch may lag behind the ledger by seconds; label search results and ensure authoritative balances come from the system of record.
• Balances computed asynchronously should display an “as of” timestamp.

Change Data Capture and Events

Systems often publish database changes as events (Debezium reading the transaction log into Kafka) to update caches, search indexes, and other services. The outbox pattern writes an event to an outbox table in the same transaction as the business change, guaranteeing that events are published if and only if the change commits. These pipelines are how real-time updates eventually reach the UI via WebSockets or SSE.

Caching Layers

Layer	Example	Frontend concern
Browser HTTP cache	Cache-Control, ETag	Sensitive data uses no-store
Client data cache	TanStack Query	staleTime, invalidation after mutations
CDN / edge	CloudFront, Akamai	Static assets; rarely personalized API responses
Application cache	Redis	Stale reads after writes; cache invalidation
Database buffer cache	Built in	Transparent

Cache invalidation strategies include time-to-live expiry, write-through (update cache on writes), cache-aside (load on miss, invalidate on write), and event-driven invalidation. Every cache introduces potential staleness, which the UI must tolerate or work around.

ORMs and Data Access from the Frontend’s Perspective

On the backend, ORMs (Hibernate/JPA in Java, Prisma or TypeORM in Node.js) map tables to objects. Frontend engineers writing BFFs in Node.js might use Prisma:

const entries = await prisma.ledgerEntry.findMany({
  where: { accountId, status: 'posted' },
  orderBy: [{ postedAt: 'desc' }, { id: 'desc' }],
  take: 50,
  cursor: cursor ? { id: cursor } : undefined,
  skip: cursor ? 1 : 0,
  select: { id: true, description: true, amountMinor: true, postedAt: true },
});

Selecting only needed columns, avoiding N+1 queries, and using cursor pagination apply regardless of the tool.

Browser-Side Storage

Frontend engineers also choose storage in the browser:

Storage	Capacity	API	Persistence	Use	Security notes
Cookies	About 4 KB each	Headers / document.cookie	Configurable	Session identifiers (HttpOnly)	Sent with requests; use Secure, HttpOnly, SameSite
localStorage	About 5 MB	Synchronous key-value strings	Until cleared	Non-sensitive preferences	Readable by any script (XSS)
sessionStorage	About 5 MB	Synchronous, per tab	Until tab closes	Per-tab UI state, PKCE verifier	Readable by any script
IndexedDB	Large (quota-based)	Asynchronous, transactional object store	Until cleared or evicted	Offline data, large caches	Readable by scripts; encrypt sensitive data or avoid storing it
Cache Storage	Large	Service worker caches	Until cleared	Offline assets	Be careful with authenticated responses
Memory	N/A	JS variables	Until reload	Tokens, sensitive data	Smallest exposure

IndexedDB is a real transactional database in the browser, useful for offline-capable apps (field agent tools, document capture) and for caching large datasets. Libraries like Dexie make it pleasant. In banking apps, avoid persisting sensitive data at rest in the browser, and clear storage on logout.

Interview Questions and Answers

Q1. What is the difference between SQL and NoSQL databases?

SQL databases use structured tables with fixed schemas, relationships via keys, a declarative query language with joins, and strong ACID transactions, typically scaling vertically or with replication and sharding. NoSQL databases use flexible models (document, key-value, wide-column, graph), are designed around access patterns, often scale horizontally, and may offer tunable or eventual consistency. Choose based on data shape, query needs, consistency requirements, and scale.

Q2. What does ACID mean?

Atomicity (all or nothing), Consistency (constraints preserved), Isolation (concurrent transactions behave predictably according to the isolation level), and Durability (committed data survives failures). It is why relational databases are used for financial ledgers.

Q3. Explain database indexes and their trade-offs.

Indexes are auxiliary structures, usually B-trees, that speed up lookups, range scans, and sorting on indexed columns. They consume storage and slow down writes because each insert or update must maintain them. Composite indexes follow the leftmost-prefix rule. Use query plans to decide which indexes are needed.

Q4. Why is cursor pagination better than offset pagination for transaction history?

Offset pagination reads and discards all preceding rows, so deep pages are slow, and new transactions shift rows between pages, causing duplicates or gaps. Cursor (keyset) pagination seeks directly to the position after the last seen row using an index, has constant performance, and is stable under inserts. It requires a deterministic sort with a unique tie-breaker.

Q5. What is the N+1 query problem?

Fetching a list with one query and then running an additional query for each item, resulting in N+1 queries. It is fixed with joins, batch loading, or eager fetching. The frontend equivalent is making one API call per list item; it is fixed with batched or aggregated endpoints.

Q6. What is SQL injection, and how is it prevented?

An attack where user input is concatenated into SQL and changes the query’s meaning, potentially exposing or modifying data. Prevent it with parameterized queries or prepared statements, ORMs that parameterize by default, least-privilege database accounts, and input validation as defense in depth.

Q7. What is eventual consistency, and how does it affect the UI?

A consistency model where replicas converge to the same state over time, so reads shortly after writes may return stale data. The UI can update its cache from the write response, read the user’s own writes from the primary, show pending states, display “as of” timestamps, and avoid presenting search indexes or replicas as authoritative for balances.

Q8. Explain the CAP theorem.

In a distributed system experiencing a network partition, you must choose between consistency and availability. CP systems refuse some requests to stay consistent; AP systems answer with possibly stale data. Without partitions, systems trade latency against consistency (PACELC).

Q9. When would you use MongoDB instead of PostgreSQL?

When data is naturally document-shaped and read as a unit, the schema evolves rapidly, and access patterns are well known, such as user preferences, content, or in-progress applications with varying fields. PostgreSQL is the better default for relational data with complex queries and transactions, and its JSONB support covers many semi-structured needs.

Q10. What is Redis used for in a banking platform?

Caching frequently read data, session storage, rate limiting and velocity checks, storing idempotency keys with expiry, distributed locks, OTP attempt counters, and pub/sub for real-time notifications.

Q11. How do transactions prevent problems in a funds transfer?

The debit and credit are performed in one transaction, so either both are committed or neither is, preventing money from disappearing or being created. Isolation and locking prevent concurrent withdrawals from overdrawing an account, and constraints ensure valid data.

Q12. What isolation anomalies should you know?

Dirty reads (reading uncommitted data), non-repeatable reads (a row changes between reads), phantom reads (new rows appear in repeated range queries), and lost updates (concurrent read-modify-write overwrites). Higher isolation levels, locking, or optimistic concurrency prevent them.

Q13. What is normalization, and when would you denormalize?

Organizing tables to eliminate redundancy and update anomalies (1NF, 2NF, 3NF). Denormalize deliberately for read performance or to preserve historical values, such as storing the payee name at the time of a transaction or maintaining a computed balance, while keeping a normalized source of truth.

Q14. How should money be stored in a database?

As exact numeric types: integer minor units (BIGINT) or fixed-precision decimals (NUMERIC(19,4) or DECIMAL), with a separate currency code column. Never as floating-point types.

Q15. What browser storage would you use for an offline-capable app, and what are the security considerations?

IndexedDB for structured data and Cache Storage via a service worker for assets. Avoid storing sensitive financial data or tokens; if unavoidable, minimize, encrypt with keys not stored alongside the data, set expiry, and clear on logout. Remember that any XSS can read browser storage.

Q16. What is the outbox pattern?

Writing a business change and a corresponding event record to an outbox table in the same database transaction, then publishing outbox records to a message broker asynchronously. It guarantees that events are published if and only if the transaction commits, avoiding inconsistency between the database and downstream consumers such as notification and real-time UI services.

Q17. How does database design influence the sorting and filtering options in a UI?

Efficient sorting and filtering require indexes matching the query patterns. Offering arbitrary sort columns or free-text search over millions of rows needs specific indexes or a search engine. Frontend and backend should agree on supported filters and sorts based on what the data layer can serve efficiently.

Q18. What is sharding?

Splitting data across multiple database instances by a shard key (such as customer ID) to scale writes and storage horizontally. Queries that include the shard key are efficient; cross-shard queries and transactions are complex. NoSQL systems like DynamoDB and Cassandra partition automatically by key.

Key Takeaways

• Relational databases provide schemas, joins, constraints, and ACID transactions, which suit financial ledgers.
• Indexes and keyset pagination determine whether list screens scale.
• NoSQL families serve specific access patterns: documents, key-value caches, wide-column, graph, and search.
• Eventual consistency and caches cause stale reads; design the UI to handle them.
• Choose browser storage with security in mind and never persist sensitive data unnecessarily.

---

← Part 15: Java and Spring Boot for Frontend Engineers · All chapters · Part 17: AI Functions in Frontend Applications →

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Comment *

Name *

Email *

Website

Δ