SQL traps · Chapter 12 of 16

Check, then insert

Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Time
  • 3 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL traps are free. This one comes with the all labs plan.

What you build: 3 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    repro.sql: the duplicate key, on purpose

    Write repro.sql. It runs in a fresh, empty database where hashtags.sql has just made the tables users, posts, hashtags and post_hashtags and the function tag_post(post_id, tag), and it plays two sessions with session lines, as in case 11. Add two members and a post by each. In session a, begin a transaction and tag the first post with a brand new hashtag, and do not commit yet. In session b, tag the second post with the same new hashtag: it waits for session a. Then commit session a, and end in session b with a SELECT that gives one value: how many rows post_hashtags has.

    The check runs your file twice, each time in a fresh database: beside the original hashtags.sql it must give 1 (session b's tag_post failed with a duplicate key), and beside a fixed hashtags.sql it must give 2. So the two calls really have to overlap: a file that only says SELECT 1 fails the second run. Run it with node run.mjs in the terminal: it prints each step and any error it raised, then your last SELECT.

  2. Task 2

    hashtags.sql: a new hashtag for two at once

    Fix tag_post in hashtags.sql so it gets the hashtag or makes it, safely. A tag nobody has used yet is made and its id returned. A tag that already exists is reused: no new row, and its id returned. Names are kept exactly as given, so Chai is a different tag from chai. When two or three sessions tag posts with the same new tag at the same moment, there is one hashtag row, every post is tagged, every call returns that one id, and no call fails.

    Keep the four tables as they are, with hashtags.name UNIQUE, and keep tag_post(post_id, tag) returning the tag's id. The check runs its own sessions, and catching the duplicate key error and returning NULL is as wrong as the bug: nothing fails, but a post is not tagged.

  3. Task 3

    hashtags.test.sql: a test that would have caught it

    Write a SQL test file, hashtags.test.sql, that would have caught this bug before members met it. Use the lab's ok(condition, name) and is(got, want, name): each call is one test, and an error in any step is a failed test. The check runs your file three times, each in a fresh database where a version of hashtags.sql has just made the tables: beside the original hashtags.sql at least one test must fail; beside the fixed hashtags.sql every test must pass and nothing may raise an error; beside a hotfix that catches the duplicate key and returns NULL at least one test must fail.

    So make two tag_post calls with the same new tag overlap with session lines, then test both: the tag is made once, and both posts are tagged. Run it with node run.mjs hashtags.test.sql.

What you will learn

  • repro.sql: the duplicate key, on purpose
  • hashtags.sql: a new hashtag for two at once
  • hashtags.test.sql: a test that would have caught it

More in Time

Build "Check, then insert" yourself

A year after Chai aur SQL, MingleSphere sends a Sunday digest, pays its creators and shows investors a dashboard, and things break the way they break in real teams. Anya, the lead backend engineer, hands you each ticket. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL traps are free. This one comes with the all labs plan.