Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 37 ยท Projects and Practice

Practice Exercises: Migrations, Transactions and Queries

Six hands-on exercises on migrations, transactions, lost updates, N+1 queries, keyset pagination and idempotency, each with a starter and a solution.

Reading about transactions and migrations is not the same as writing them. These six exercises each give you a runnable starter. Edit the code, press Run and read the PASS or FAIL line. The solutions are at the end of the lesson; try each exercise before you look.

The exercises use node:sqlite as a stand-in for the database layer, because TypeORM and Sequelize are not available in the lesson runner. The skill being practiced is the same: the SQL, the transaction boundaries and the reasoning about what can go wrong. The final section tells you how to repeat each exercise with the real libraries on your own machine.

Exercise 1: write a migration pair

Add a bio column to users in the up step and remove it in down. Add an index on users(email) in up as well, and drop it in down.

Node.js Exercise 1: up and down

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Exercise 2: make a transfer atomic

transfer below can leave money in limbo. Wrap it so a failure restores both balances, and make the second call (which overdraws) leave the balances unchanged.

Node.js Exercise 2: atomic transfer

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Exercise 3: fix the lost update

Two sales read the stock before either writes. Change sell so six concurrent sales of a stock of 3 sell exactly three, with no read-then-write.

Node.js Exercise 3: no lost updates

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Exercise 4: remove the N+1

listWithAuthors runs a query per post. Rewrite it to use at most two queries, then check the counter.

Node.js Exercise 4: at most two queries

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Exercise 5: keyset pagination

Implement nextPage(afterId, size) returning ids in descending order after the cursor, plus a nextCursor (or null on the last page). Walk all pages and check you see each id once.

Node.js Exercise 5: cursor pages

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Exercise 6: idempotent payments

A client may send the same payment twice with the same key. Make pay apply each key only once and return 'duplicate' the second time, while keeping the balance change and the payment record in one transaction.

Node.js Exercise 6: idempotency key

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Solutions

Try first. When you are ready, compare.

Exercise 1

JavaScript
up(db) {
  db.exec('alter table users add column bio text');
  db.exec('create index idx_users_email on users(email)');
},
down(db) {
  db.exec('drop index idx_users_email');
  db.exec('alter table users drop column bio');
},

Undo in the reverse order of up. Dropping the column first would fail in databases where an index still depends on the table layout.

Exercise 2

JavaScript
function transfer(from, to, amount) {
  db.exec('begin');
  try {
    db.prepare('update accounts set balance = balance + ? where id = ?').run(amount, to);
    db.prepare('update accounts set balance = balance - ? where id = ?').run(amount, from);
    db.exec('commit');
  } catch (e) {
    db.exec('rollback');
    throw e;
  }
}

Exercise 3

JavaScript
async function sell() {
  await tick();
  return db.prepare('update stock set qty = qty - 1 where qty >= 1').run().changes === 1;
}

Exercise 4

JavaScript
function listWithAuthors() {
  const posts = q('select * from posts order by id');
  const ids = [...new Set(posts.map((p) => p.author_id))];
  const users = q(`select id, name from users where id in (${ids.map(() => '?').join(',')})`, ...ids);
  const name = new Map(users.map((u) => [u.id, u.name]));
  return posts.map((p) => ({ title: p.title, author: name.get(p.author_id) }));
}

A single join also passes and uses one query.

Exercise 5

JavaScript
function nextPage(afterId, size) {
  const rows = afterId
    ? db.prepare('select id from posts where id < ? order by id desc limit ?').all(afterId, size)
    : db.prepare('select id from posts order by id desc limit ?').all(size);
  const ids = rows.map((r) => r.id);
  return { ids, nextCursor: ids.length === size ? ids.at(-1) : null };
}

With 11 rows and pages of 4, the last page holds 3 ids, so its cursor is null. If the total were an exact multiple of the page size, one extra empty page would be requested; fetching size + 1 rows avoids that.

Exercise 6

JavaScript
function pay(amount, key) {
  db.exec('begin');
  try {
    db.prepare('insert into payments(amount, key) values (?, ?)').run(amount, key);
    db.prepare('update wallet set balance = balance + ?').run(amount);
    db.exec('commit');
    return 'applied';
  } catch (e) {
    db.exec('rollback');
    if (/UNIQUE/.test(e.message)) return 'duplicate';
    throw e;
  }
}

The unique on key in the schema is what makes this reliable: the database constraint, not application code, is what stops a duplicate even when two requests arrive at the same moment.

Repeating them with the real libraries

On your own machine, with npm install typeorm sequelize better-sqlite3 sqlite3 (or pg), redo the exercises:

  1. Write the migration pair as a TypeORM MigrationInterface and as a Sequelize up/down file, run both directions with the CLI (TypeORM, Sequelize).
  2. Use dataSource.transaction and sequelize.transaction (ORM transactions).
  3. Use decrement or a conditional update (locking).
  4. Replace the loop with relations / include and compare the logged SQL (N+1).
  5. Express the cursor with LessThan(afterId) / Op.lt (pagination).
  6. Add a unique index on key and catch QueryFailedError / UniqueConstraintError.

Check your understanding

Quick check ยท 3 questions

of 3 answered You scored / 3 ยท perfect!

  1. 1. In a migration, in what order should down undo the steps of up?

    Later changes may depend on earlier ones, so they are removed first.
  2. 2. What makes idempotency reliable under concurrent retries?

    A check-then-insert has a race; the unique constraint is enforced atomically.
  3. 3. How many queries are needed to load 12 posts with their authors without N+1?

    One join, or one query for posts and one batched IN query for authors.

Summary

  • Migrations pair up with a down that reverses it in the opposite order.
  • Wrap multi-step writes in a transaction; use atomic conditional updates for counters.
  • Replace per-row queries with a join or a batched IN query.
  • Keyset cursors give stable pages; unique constraints make idempotency real.

Next: Back to the TypeORM and Sequelize track