ORM (Sequelize) — Practical

Migration: create table inside transaction

migrations/20260514120000-create-users.ts

import { QueryInterface, DataTypes } from 'sequelize';

export = {
  async up(queryInterface: QueryInterface): Promise<void> {
    await queryInterface.sequelize.transaction(async (t) => {
      await queryInterface.createTable('users', {
        id:        { type: DataTypes.UUID, primaryKey: true, defaultValue: DataTypes.UUIDV4 },
        email:     { type: DataTypes.STRING(320), allowNull: false },
        createdAt: { type: DataTypes.DATE, allowNull: false },
        updatedAt: { type: DataTypes.DATE, allowNull: false },
        deletedAt: { type: DataTypes.DATE, allowNull: true },
      }, { transaction: t });

      await queryInterface.addIndex('users', ['email'], {
        unique: true, name: 'users_email_unique', transaction: t,
      });
    });
  },

  async down(queryInterface: QueryInterface): Promise<void> {
    await queryInterface.dropTable('users');
  },
};

Notes:

• Postgres runs DDL transactionally; MySQL does not (DDL auto-commits).
• Filename timestamp is the sort key, not git history — merge surprises possible.

Soft delete (paranoid)

import { Model, DataTypes, Sequelize, InferAttributes, InferCreationAttributes } from 'sequelize';

class Post extends Model<InferAttributes<Post>, InferCreationAttributes<Post>> {
  declare id: number;
  declare title: string;
  declare deletedAt: Date | null;
}

Post.init({
  id:    { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
  title: { type: DataTypes.STRING, allowNull: false },
  deletedAt: DataTypes.DATE,
}, { sequelize, modelName: 'Post', paranoid: true });

await Post.destroy({ where: { id: 1 } });            // UPDATE deletedAt = NOW()
await Post.destroy({ where: { id: 1 }, force: true }); // hard DELETE
await Post.findByPk(1);                              // null (filtered out)
await Post.findByPk(1, { paranoid: false });         // returns soft-deleted row
await Post.restore({ where: { id: 1 } });            // UPDATE deletedAt = NULL

Hooks — password hash + audit

import bcrypt from 'bcrypt';

User.beforeCreate(async (user, options) => {
  if (user.changed('password')) {
    user.password = await bcrypt.hash(user.password, 12);
  }
});

User.beforeBulkCreate(async (users, options) => {
  // bulkCreate skips per-row beforeCreate by default — hash here instead
  await Promise.all(users.map(async (u) => {
    u.password = await bcrypt.hash(u.password, 12);
  }));
});

User.afterUpdate(async (user, options) => {
  await AuditLog.create({
    userId:  user.id,
    changed: user.changed(),
    before:  user.previous(),
    actorId: options.context?.actorId,
  }, { transaction: options.transaction });  // critical: propagate tx
});

Eager loading — N+1 fix

// BAD: N+1
const users = await User.findAll();
for (const u of users) {
  u.posts = await u.getPosts();        // 1 query per user
}

// GOOD: eager load
const users = await User.findAll({
  include: [{
    model: Post,
    as: 'posts',
    attributes: ['id', 'title'],
    required: false,                   // LEFT JOIN — keep users with 0 posts
    where: { publishedAt: { [Op.ne]: null } },
  }],
});

// BETTER for multiple hasMany — avoid Cartesian explosion
const users = await User.findAll({
  include: [
    { model: Post,    as: 'posts',    separate: true, limit: 10, order: [['createdAt','DESC']] },
    { model: Comment, as: 'comments', separate: true, limit: 5  },
  ],
});

// Filter parents by child column WITHOUT forcing INNER JOIN
const users = await User.findAll({
  where: { '$posts.published$': true },
  include: [{ model: Post, as: 'posts', required: false }],
});

Transactions — transfer funds + CLS

import { Transaction } from 'sequelize';

await sequelize.transaction(
  { isolationLevel: Transaction.ISOLATION_LEVELS.SERIALIZABLE },
  async (t) => {
    const from = await Account.findByPk(fromId, { lock: t.LOCK.UPDATE, transaction: t });
    const to   = await Account.findByPk(toId,   { lock: t.LOCK.UPDATE, transaction: t });

    if (from.balance < amount) throw new Error('insufficient funds');

    await from.decrement('balance', { by: amount, transaction: t });
    await to.increment  ('balance', { by: amount, transaction: t });

    t.afterCommit(() => publishEvent('transfer.completed', { fromId, toId, amount }));
  },
);

// CLS — automatic transaction propagation
import cls from 'cls-hooked';
const ns = cls.createNamespace('app');
Sequelize.useCLS(ns);

await sequelize.transaction(async () => {
  await User.create({ email });        // auto-joins, no { transaction } needed
  await audit('user.created');         // any inner function's queries also join
});

Raw queries — window + recursive CTE

import { QueryTypes } from 'sequelize';

interface RankedPost { id: number; title: string; rank: number; }

// Window function — bind params
const top: RankedPost[] = await sequelize.query(
  `SELECT id, title,
          ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY likes DESC) AS rank
   FROM posts
   WHERE created_at >= $since`,
  {
    bind: { since: new Date('2026-01-01') },
    type: QueryTypes.SELECT,
  },
);

// Recursive CTE hydrated to model instances
const team = await sequelize.query<User>(
  `WITH RECURSIVE reports AS (
     SELECT * FROM users WHERE manager_id = $rootId
     UNION ALL
     SELECT u.* FROM users u JOIN reports r ON u.manager_id = r.id
   )
   SELECT * FROM reports`,
  { bind: { rootId }, model: User, mapToModel: true, type: QueryTypes.SELECT },
);
// team[0] is a User instance with .save(), .destroy(), associations.

Connection pool config

import { Sequelize } from 'sequelize';

export const sequelize = new Sequelize(process.env.DATABASE_URL!, {
  dialect: 'postgres',
  pool: {
    max: 10,        // per process; coordinate with replica count
    min: 2,         // keep 2 warm so cold requests don't pay handshake
    acquire: 30000, // fail fast — 30s queue beats stampeding the DB
    idle: 10000,
    evict: 1000,
    maxUses: 7500,  // recycle to rebalance across DB cluster nodes
  },
  retry: {
    max: 3,
    match: [/SequelizeConnectionError/, /SequelizeConnectionRefusedError/],
  },
  logging: process.env.NODE_ENV === 'production' ? false : console.log,
});

export async function poolHealth() {
  const start = Date.now();
  await sequelize.authenticate();
  return { ok: true, latencyMs: Date.now() - start };
}

Interview Q&A

Q: When would you use sync() in real code?

A: Local development against a throwaway DB, integration test suites that spin up a fresh schema per run (sync({ force: true }) in a beforeAll), and rapid prototyping. Never in CI promotion paths or production. The moment more than one engineer touches the schema, switch to migrations so changes become reviewable, ordered, and reversible.

Q: Walk me through a safe production migration workflow.

A: (1) Generate migration locally, write up/down, test against prod-shaped data. (2) Code review covers the SQL emitted — turn on logging: console.log while testing. (3) For destructive changes (drop column, rename column), use the expand/contract pattern: ship migration A that adds the new column + dual-writes, deploy app code that reads from new with fallback to old, ship migration B that backfills, ship migration C that drops the old column. (4) Run db:migrate from a one-shot job before the new app version starts serving traffic — never from app boot. (5) Confirm SequelizeMeta shows the new row; have a forward-fix migration ready rather than relying on down.

Q: User asks “delete my account permanently” (GDPR). How does paranoid interact?

A: Paranoid is the wrong tool for GDPR erasure — soft delete is data retention, not deletion. Implement an explicit “purge” path that calls .destroy({ force: true }) plus deletes related PII across all tables (use a transaction). Keep paranoid for accidental-delete recovery and audit, but provide the hard-delete pipeline for compliance requests, ideally with a verification step and a logged actor.

Q: Why might a unique constraint break after enabling paranoid?

A: Soft-deleted rows still occupy the unique index. If a user with email='x@y.com' is soft-deleted and tries to re-register, Postgres still sees the row and rejects the insert. Fix with a partial unique index — CREATE UNIQUE INDEX users_email_active ON users(email) WHERE deleted_at IS NULL. On MySQL, model the same idea with a generated column such as active_email = IF(deleted_at IS NULL, email, NULL) plus UNIQUE(active_email). Do not compose directly over nullable (email, deletedAt); multiple NULL values can bypass the intended uniqueness.

Q: I added User.beforeCreate to hash passwords, but during a CSV import using bulkCreate passwords are stored in plaintext. Why?

A: bulkCreate only fires bulk hooks; per-instance beforeCreate doesn’t run. Either add individualHooks: true to the bulkCreate call (fine for small batches), or — better — implement a beforeBulkCreate that iterates and hashes once, avoiding the per-row SELECT overhead individualHooks introduces.

Q: How would you build an audit log with hooks, and where does the approach break down?

A: Register afterCreate/afterUpdate/afterDestroy on every audited model, write to an audit_logs table inside options.transaction. Pass actor ID through CLS or as options.context. Breakdowns: (1) raw queries and queryInterface writes bypass hooks entirely — use DB triggers or a write-side proxy; (2) DB-level cascade deletes won’t fire afterDestroy; (3) bulk ops without individualHooks produce one log entry per N rows. For high-write systems, prefer a CDC/outbox pattern over hooks.

Q: Eager loading is meant to fix N+1 — when does it make things worse?

A: When you eager-load multiple hasMany associations in one query, the JOIN produces the Cartesian product of all child collections. A user with 100 posts and 50 comments becomes 5,000 rows on the wire per user. Use separate: true on each hasMany. Also consider whether you actually need both collections in this request.

Q: Explain required: true vs required: false and when filtering breaks intent.

A: required: true = INNER JOIN (drop parents with no matching child). required: false = LEFT OUTER JOIN (keep all parents). Trap: putting where inside an include auto-flips it to required: true, so User.findAll({ include: [{ model: Post, where: { published: true }}]}) returns only users who have published posts — not users-with-only-their-published-posts. To get the latter, use required: false explicitly, or push the filter to the top level via '$Post.published$'.

Q: Managed vs unmanaged transactions — which do you reach for?

A: Managed by default. The callback form makes commit/rollback impossible to forget, the lifetime matches a lexical scope, and exceptions auto-rollback. Unmanaged is for narrow cases: long-running sagas, two-phase commit coordination, test fixtures spanning multiple it() blocks. Whenever you write unmanaged, also write the try/finally to guarantee commit or rollback — leaked transactions hold connections open until acquire timeout.

Q: How do you prevent two concurrent withdraw requests from over-drawing an account?

A: Wrap in a transaction at READ_COMMITTED (default) and SELECT ... FOR UPDATE the account row inside the transaction. The second request blocks at lock acquisition until the first commits, then re-reads the new balance. Alternative: optimistic concurrency with a version column and WHERE version = ? on the update, retrying on zero-row updates. Pessimistic locking is simpler but serializes writers; optimistic scales better when conflicts are rare.

Q: When would you reach for a raw query over the ORM, and what do you give up?

A: Window functions, recursive CTEs, dialect-specific features (Postgres JSONB ops, MySQL JSON_TABLE), full-text search, geo, bulk ops where pulling rows into Node would be wasteful. Trade-offs: lose paranoid filter, lose hooks, lose default scopes, lose type-safety on the result, lose dialect portability. Mitigate with a hybrid pattern — write SQL, hydrate via mapToModel: true. Keep raw queries in repositories/sql/ so they’re reviewable.

Q: Why prefer bind over replacements?

A: replacements are interpolated into the SQL string by Sequelize before being sent to the DB — Sequelize escapes them, but escaping is a defense, not an architecture. bind parameters travel as a separate payload over the wire protocol; the SQL the database parses literally never contains user input, so the parser cannot misinterpret a value as code. It also enables the DB to cache the prepared plan. Use replacements only for things bind can’t do — substituting identifiers like table or column names.

Q: Production app starts throwing SequelizeConnectionAcquireTimeoutError under load. Walk me through the diagnosis.

A: First, check whether the DB itself is healthy and not at max_connections — if it is, you’re starving other clients. Second, look for leaked connections: unmanaged transactions without try/finally, code paths that throw between await sequelize.transaction() and t.commit()/rollback(). Third, audit transaction durations — a transaction that does HTTP I/O while holding a row lock will pin a connection for the whole call. Fourth, check whether pool.max × pod count exceeds DB capacity. Bumping pool.max is the last resort and only valid if the DB has headroom — otherwise put PgBouncer in front.

Q: How would you size pool.max for a Postgres-backed Node service on Kubernetes?

A: Start from the database’s max_connections (e.g. 100 on default Postgres). Reserve ~20% headroom for admin, replication, monitoring, migrations. Divide the rest by max replica count: 80 / 16 pods = 5 per pod, so pool.max: 5. Set min: 1 to keep one warm connection so the first request after scale-up doesn’t pay a handshake. Set acquire to a value smaller than your HTTP request timeout (e.g. 10–30s) so requests fail fast rather than pile up. If horizontal scaling is aggressive, put PgBouncer in transaction-pooling mode in front and use pool.max: 10 per pod against PgBouncer rather than directly against Postgres.

Gotchas roundup

• Model.update({...}, { where }) fires beforeBulkUpdate/afterBulkUpdate only — not beforeUpdate. Most password-hashing bugs trace to this.
• individualHooks: true is an O(n) memory hit. Don’t use on million-row updates.
• findAndCountAll + includes silently inflates count. Use distinct: true (col: 'id' for paranoid).
• limit + include on hasMany: Sequelize wraps in subquery to limit parents. Add subQuery: false only when you’re sure.
• include: { all: true, nested: true } is a debugging convenience; don’t ship it.
• acquire timeout = “queue full” — usually a leaked tx or pool.max too low. Find the leak first.
• Lambdas/serverless blow up naive pooling. Use RDS Proxy or PgBouncer, or min: 0, max: 1 per execution.
• Long transactions hold a connection for their entire duration. 30s tx × pool.max: 10 = 10 concurrent in-flight transactions globally.