Articles

Sequelize Interview Questions: Transactions, Associations, and Duplicate Joins

Practice Sequelize v6 transactions, association aliases, eager-loading filters, duplicate join counts, and parent-first pagination with worked row traces.

A Sequelize interview can expose a mismatch between three things: the transaction a query actually joins, the rows produced by SQL, and the model objects returned to JavaScript. A short include expression can change parent eligibility, inflate a count, or hide a pagination defect even when the returned objects look reasonable.

Version and evidence boundary: this article uses Sequelize v6 documentation and APIs. The official v7 documentation is labeled alpha; do not import its transaction-context defaults into a v6 answer. Linked documentation establishes official facts. Fixtures, failure traces, and recommendations are original exercises and inference, not candidate reports about an employer's interview process.

What makes a query part of a v6 transaction?

Official fact: Sequelize v6 managed transactions commit when the callback succeeds and roll back when it throws. Without configured automatic context propagation, queries must receive the transaction through their options.

Original review exercise: assume CLS is not enabled, and both models already exist.

await sequelize.transaction(async (t) => {
  await Order.create({ id: 17, status: 'pending' }, { transaction: t });
  await Audit.create({ orderId: 17, kind: 'created' });
  throw new Error('abort request');
});

The order insert is explicitly enrolled. The audit insert is not. A successful independent audit insert could survive the order rollback, although locks, constraints, or pool limits could instead make it fail or wait. The review finding is the missing transaction option, not a universal prediction of which statement completes first.

The repair passes { transaction: t } to the audit operation and any helper queries that belong to this unit of work. Calling a helper inside the callback does not prove that it forwards the option.

Inference: verify rollback from outside the rejected transaction. Seed an existing order or independent fixture, perform the deliberate failure, and assert every enrolled mutation reverted. A mock verifying that sequelize.transaction was called cannot establish database atomicity.

Why is catching an error inside the callback dangerous?

Question: “We catch the exception and return an error object. Will the transaction roll back?”

A managed transaction sees whether its callback resolves or rejects. Returning { ok: false } after swallowing an error can make the wrapper observe success, unless the database transaction itself is already unusable or another failure occurs.

Original trace: create an order, attempt a required audit operation, catch its application error, return an error object. The order can commit even though the API response describes failure. That inconsistency comes from the control flow, not from a broken rollback implementation.

Inference: let failures that invalidate the unit of work propagate. Catch outside the transaction to translate the error into an HTTP response. If part of the work is genuinely optional, define its separate recovery boundary rather than catching every exception in one broad block.

Await every operation that must finish before commit. An unawaited promise can execute after the callback resolves. Also distinguish database state from external effects: a message sent inside the callback cannot be recalled by SQL rollback. An outbox can record a notification obligation in the database transaction, with delivery handled afterward.

Can CLS replace explicit transaction options?

Official fact: v6 documents automatic propagation through explicitly configured CLS and Sequelize.useCLS. It is not safe to assume that a codebase enables it simply because it uses async functions. Configuration is part of the transaction contract.

Inference: in an interview, ask whether propagation is enabled and which library version the repository uses. Then trace deliberate overrides and helper behavior. “Every query in this callback uses t” is a conclusion that needs evidence.

Hooks are another hidden query path. Official fact: the hooks guide describes transaction information in hook options. A hook performing related database work should preserve the intended transaction option rather than independently query outside it.

Original example: an afterCreate hook creates a searchable metadata row. If it drops options.transaction, a later failure can leave metadata for an entity that rolled back. Check the hook's actual query options and final rows, not only whether the hook ran.

Where does an association's foreign key live?

Official fact: Sequelize's association guide distinguishes belongsTo, hasMany, and many-to-many associations. The source and target sides determine foreign-key placement, while aliases identify the association used for loading.

Original model pair:

User.hasMany(Task, { as: 'tasks', foreignKey: 'userId' });
Task.belongsTo(User, { as: 'owner', foreignKey: 'userId' });

The foreign key is on Task. Reading User.tasks and reading Task.owner are inverse navigation paths over that relationship. Specifying both sides consistently avoids accidentally creating or referring to different foreign-key fields.

Inference: inspect the database schema to confirm actual constraints. A model association and a deployed foreign-key constraint are different artifacts. The schema may lag behind definitions, or the application may intentionally operate without a database constraint.

If the include asks for alias owner while querying User, that is not the association named tasks. Aliases are not decorative response labels that can be freely interchanged. When multiple relationships point to the same model, state which relationship the query is loading.

Why does `include.where` remove users with no tasks?

Official fact: v6 eager-loading documentation shows that an include-level where normally makes the include required. Setting required: false preserves an outer join while applying that include filter in its join condition.

Original fixture: User A has three tasks: two open and one closed. User B has no tasks. The endpoint wants every user, attaching only open tasks.

const users = await User.findAll({
  include: [{
    model: Task,
    as: 'tasks',
    where: { state: 'open' },
    required: false,
  }],
});

The intended relational shape places the state predicate in ON:

SELECT u.id, t.id AS task_id
FROM users u
LEFT JOIN tasks t
  ON t.user_id = u.id AND t.state = 'open';

A's two open tasks remain. B contributes one null-extended row and should hydrate as a parent with no matching tasks. By contrast, moving t.state = 'open' into a top-level WHERE removes B because the null-extended row does not satisfy the equality.

Inference: ask whether the requirement means “all users, filtered children” or “only users having a matching child.” Those are different products. Flipping required to restore a missing user without checking the requirement can turn a correct eligibility filter into a bug.

Count the rows before counting the objects

Question: “There are two users, so why does our count say four?”

Use the same original fixture, but remove the task-state filter. A has three tasks; B has none. An unrestricted left join produces:

User	Task	Joined row
A	First task	1
A	Second task	2
A	Third task	3
B	NULL	4

The parent count is two. The joined-row count is four. The count of non-null task IDs is three. Each number answers a different question.

Official fact: the finder guide distinguishes model instances from raw results and describes findAndCountAll, including its changed count shape when grouping is used.

Inference: hydrated parent objects can consolidate associated child rows, so a two-element JavaScript array does not prove that SQL processed two rows. Raw output exposes a different shape. Always identify whether the observed duplication is in SQL, raw objects, hydrated parents, or the count query.

With two independent one-to-many includes, multiplication can be worse. Three tasks and four comments for A can produce twelve joined combinations for A. Deduplicating the parent in JavaScript does not recover the database work already performed.

Does `distinct: true` fix every count and pagination bug?

Official fact: the v6 Model API reference documents count options such as distinct and col. Distinct counting of the relevant parent key can address a multiplied parent count.

Inference: inspect the generated count SQL and the qualified key chosen for your actual aliases and dialect. Do not paste an option combination from another query and assume the result is correct. Grouped counts, required includes, composite identity, and nested associations can change the interpretation.

A distinct count also does not decide which parents enter a page or ensure that every selected parent has all requested children. These are separate correctness checks.

Original pagination failure: order the unrestricted joined fixture by user ID and task ID, then apply a SQL-level LIMIT 2 directly to the joined rows. Both rows belong to A. The result contains one parent with only part of its child set, rather than the first two complete parents.

Sequelize can generate subqueries for some pagination combinations. Do not assume that every limit is applied directly to the join, or that turning off subqueries is a harmless optimization. Examine the actual SQL for the exact options used.

How would you build a complete parent page?

Original parent-first plan: first select eligible parent IDs with a stable order and page boundary. Then load the requested associations for that bounded ID set. Preserve the original parent order in the final response.

Eligibility belongs in the first step. If the requirement is “users with an open task,” use an appropriate existence condition there. Filtering children only in the second step would page all users and then discover some are ineligible.

Inference: decide whether both steps require one consistent database view. Concurrent deletion or updates can otherwise change the association data between queries. A suitable transaction and isolation level can address a defined snapshot requirement, but the answer must match the database's actual behavior.

The eager-loading guide also documents separate: true for supported hasMany loading. It is an option to investigate, not a generic setting for every association type. Compare statement count, child limits, ordering, and the maximum response size before choosing it.

A useful verification fixture includes a parent with zero children, one with several, two parents sharing a sort value, and a page boundary through a high-fan-out parent. Assert parent IDs, complete child sets, count semantics, and stable ordering independently.

What evidence makes the answer reviewable?

For a transaction bug, show the options passed at every query path and the committed rows after deliberate failure. For an association bug, show the schema, aliases, and generated join predicate. For a count bug, state whether the endpoint wants parents, children, groups, or joined combinations.

Inference: enable SQL logging in a controlled test and compare the statement shape with the intended relational operation. Avoid putting sensitive values into production logs unnecessarily. A small fixture whose rows you can enumerate is often more informative than a large database where the only observation is “the total looks wrong.”

If a proposed fix changes count semantics, parent eligibility, or child completeness, document each change explicitly. An endpoint can return a plausible-looking page while still silently excluding users or understating available results.

Practice the relational and transaction boundaries

These are transferable practice exercises, not predictions of a Sequelize interview.

Question	What to explain
Reason About Composite Join Keys and Predicate Placement	Join identity and ON-versus-WHERE behavior.
Illustrate SQL Join Results with Duplicate Keys	Enumerate joined combinations before counting.
Write left-join queries with tricky filters	Preserve or deliberately exclude unmatched parents.
Explain Joins and Write Coupon SQL	Connect query syntax to actual result rows.
Design an In-Memory Transactional Key-Value Store	Trace rollback enrollment and control flow.

Practice the duplicate-key join exercise. Write the rows by hand, then distinguish the parent count, child count, and count the ORM should return.