SQL Explained Through JavaScript - Part 2

In the first part, we looked at how SQL concepts like GROUP BY, HAVING, and LEFT JOIN can be understood through equivalent JavaScript code.

Let's continue with the same approach and cover other examples.

Example: Limiting Comments per Hour with UPSERT and CASE WHEN

We want to track how many comments each user posts within a one-hour window, so we can reject comments once they exceed the hourly limit.

To track this, we keep a comment_limits table with one row per user. Each row stores the user's ID (user_id), comment count (comments_count), and the time their current window ends (expires_at).

Here's the SQL:

INSERT INTO comment_limits (user_id, comments_count, expires_at)
VALUES ($1, 1, now() + interval '1 hour')
ON CONFLICT (user_id) DO UPDATE
SET
  comments_count = CASE
    WHEN comment_limits.expires_at <= now() THEN 1
    ELSE comment_limits.comments_count + 1
  END,
  expires_at = CASE
    WHEN comment_limits.expires_at <= now() THEN now() + interval '1 hour'
    ELSE comment_limits.expires_at
  END
RETURNING comments_count;

Here's the equivalent JavaScript:

const HOUR = 60 * 60 * 1000;

// Let's assume this is the table data: one row per user
const table = [];

function recordComment(userId) {
  const now = Date.now();

  const row = table.find((row) => row.userId === userId);

  // SQL: INSERT ... VALUES ($1, 1, now() + interval '1 hour')
  // $1 is userId. No row yet, so create one.
  if (!row) {
    const newRow = {
      userId,
      commentsCount: 1,
      expiresAt: now + HOUR,
    };

    table.push(newRow);
    return newRow.commentsCount;
  }

  // SQL: ON CONFLICT (user_id) DO UPDATE
  // A row already exists, so update it.
  const expired = row.expiresAt <= now;

  // SQL: CASE WHEN expired THEN 1 ELSE comments_count + 1 END
  row.commentsCount = expired ? 1 : row.commentsCount + 1;

  // SQL: CASE WHEN expired THEN now() + interval '1 hour' ELSE expires_at END
  row.expiresAt = expired ? now + HOUR : row.expiresAt;

  // SQL: RETURNING comments_count
  return row.commentsCount;
}

The first comment starts a one-hour window with a count of 1. Each comment within that window adds 1. The first comment after the window ends starts a new window with a count of 1. The updated count is returned each time.

Why RETURNING comments_count?

RETURNING gives back the new comment count. That's the only value the caller needs to decide what to do next, and it comes from the same statement, so there's no second SELECT needed:

const count = recordComment(userId);

if (count > 5) {
  // too many comments this hour, reject
}

Using Two Counters

The query above has a drawback: it only counts per user. A spammer can register 100 accounts and post 5 comments from each, and no single account ever passes 5.

The fix is to keep two counters. When someone comments, we increase the counter for their user and the counter for their IP address.

Each counter catches the case the other one misses. So we block if either one is over the limit.

To store both kinds of counters in the same comment_limits table, we replace the user_id column with a key column. A key is a string that names one counter, like user:42 or ip:1.2.3.4. Here 42 is the user_id from the previous example. For example, after the first comment, the two counters might look like this:

We want to limit comments by both user and IP address, so switching accounts or IPs alone won't bypass the limit.

Here's the SQL:

INSERT INTO comment_limits (key, comments_count, expires_at)
SELECT key, 1, now() + interval '1 hour'
FROM unnest($1::text[]) AS key
ORDER BY key
ON CONFLICT (key) DO UPDATE
SET
  comments_count = CASE
    WHEN comment_limits.expires_at <= now() THEN 1
    ELSE comment_limits.comments_count + 1
  END,
  expires_at = CASE
    WHEN comment_limits.expires_at <= now() THEN now() + interval '1 hour'
    ELSE comment_limits.expires_at
  END
RETURNING comments_count;

Here's the equivalent JavaScript:

const HOUR = 60 * 60 * 1000;

// Table data: one row per user or IP key.
const table = [];

function recordComment(keys) {
  const now = Date.now();

  // SQL: SELECT key ... FROM unnest($1::text[]) AS key ORDER BY key
  return [...keys].sort().map((key) => {
    const row = table.find((row) => row.key === key);

    // SQL: INSERT ... SELECT key, 1, now() + interval '1 hour'
    // No row for this key yet, so create one.
    if (!row) {
      const newRow = {
        key,
        commentsCount: 1,
        expiresAt: now + HOUR,
      };

      table.push(newRow);
      return newRow.commentsCount;
    }

    // SQL: ON CONFLICT (key) DO UPDATE
    // A row already exists, so update it.
    const expired = row.expiresAt <= now;

    // SQL: CASE WHEN expired THEN 1 ELSE comments_count + 1 END
    row.commentsCount = expired ? 1 : row.commentsCount + 1;

    // SQL: CASE WHEN expired THEN now() + interval '1 hour' ELSE expires_at END
    row.expiresAt = expired ? now + HOUR : row.expiresAt;

    // SQL: RETURNING comments_count (one per key)
    return row.commentsCount;
  });
}

Each comment updates two counters: one for the user and one for the IP address. Both follow the same counting and reset rules as the earlier example.

Note: ORDER BY key helps prevent deadlocks by ensuring concurrent requests process keys in the same order. Without it, two requests could lock the same rows in opposite orders, each waiting for the other to release a lock. Sorting the keys avoids this particular situation, though it doesn't prevent every possible deadlock.

Using the result

The caller checks that every counter is under the limit:

const counts = recordComment([`user:${userId}`, `ip:${ip}`]);

if (!counts.every((count) => count <= 5)) {
  // either the user or the IP commented too much, reject
}