{"course":{"slug":"building-apps-with-ai","title":"Building Apps with AI","url":"https://addaly.com/learn/building-apps-with-ai","level":"beginner","track":"build"},"lesson":{"slug":"reading-the-query-behind-the-page","title":"Reading the query behind the page","url":"https://addaly.com/learn/building-apps-with-ai/reading-the-query-behind-the-page","position":24,"of":87,"minutes":9,"answer":"Find the WHERE clause, confirm the user's identity in it came from the session, and check for a LIMIT and for queries running inside a loop.","keyIdea":"Find the WHERE clause, confirm the user's identity in it came from the session, and check for a LIMIT and for queries running inside a loop."},"body":{"format":"markdown","text":"## Every page is a question asked of the database\n\nThe screen is the answer. The query is the question, and it is where the two failures that matter most both live: the wrong rows, and somebody else's rows.\n\nYou do not need to write SQL fluently. You need to read one query and say what it will return.\n\n## The shape of a select\n\n```sql\nSELECT id, starts_at, status\nFROM bookings\nWHERE customer_id = 71\n  AND status = 'confirmed'\nORDER BY starts_at DESC\nLIMIT 20;\n```\n\nRead it in the order the database does. `FROM` names the table. `WHERE` decides which rows survive. `ORDER BY` sorts them. `LIMIT` cuts the list. `SELECT` picks which columns come back.\n\nThe `WHERE` clause is the whole security story. Remove `customer_id = 71` and this returns every confirmed booking belonging to everybody.\n\n## Joins, briefly\n\nA booking holds a `customer_id`, not a customer name. Getting the name means joining:\n\n```sql\nSELECT b.starts_at, c.name\nFROM bookings b\nJOIN customers c ON c.id = b.customer_id\nWHERE b.stylist_id = 4;\n```\n\n`JOIN` keeps rows that match on both sides. `LEFT JOIN` keeps every row from the left table even when the right has no match, filling the missing columns with NULL. That difference produces a real bug: a plain `JOIN` between bookings and customers silently drops every booking whose customer was deleted, and the count on your dashboard is wrong.\n\n## NULL is not empty\n\nNULL means unknown, and it does not behave like a value.\n\n```sql\nWHERE cancelled_at != '2026-01-01'   -- excludes rows where cancelled_at IS NULL\nWHERE phone = NULL                    -- never true, for any row\nWHERE phone IS NULL                   -- the correct form\n```\n\nThe first line is the trap. A filter meant to exclude one date also silently excludes every row where the column was never set. This is exactly the class of bug that hides a subset of rows from a list without any error appearing anywhere, and it is common in generated queries.\n\n## Reading an ORM instead\n\nMost generated projects use a query builder, which is the same thing with different punctuation:\n\n```js\nconst rows = await db\n  .select()\n  .from(bookings)\n  .where(and(\n    eq(bookings.customerId, session.userId),\n    eq(bookings.status, 'confirmed')\n  ))\n  .orderBy(desc(bookings.startsAt))\n  .limit(20)\n```\n\nSame five clauses. `eq` is `=`, `and` is `AND`, `desc` is `DESC`. The important habit transfers exactly: find the `where`, and check that the user's identity in it came from the session and not from the request.\n\nIf you cannot tell what SQL a builder produces, most of them will print it. In Drizzle, `.toSQL()`. In Prisma, set the query log. Seeing the real statement once removes a lot of uncertainty.\n\n## Three things to check in any query you review\n\n**Is it scoped to a person?** Any query returning user data needs a condition tying it to the logged-in identity, taken from the session on the server. This is the single most common serious hole in AI-built apps and it has a whole lesson later.\n\n**Is there a `LIMIT`?** A list page with no limit works on your 40 test rows and takes eleven seconds when a real account has 90,000.\n\n**Is it inside a loop?** Fetching a list and then querying once per item is the N+1 problem: 1 query becomes 201. It is invisible locally and it is the most common reason a generated page is slow.\n\n## Look at the data yourself\n\nThe fastest way to check a page is to ask the database the same question directly.\n\n```bash\npsql $DATABASE_URL          # Postgres, free, comes with it\nsqlite3 app.db              # SQLite, one command\n```\n\nOr use a free graphical client: **DBeaver**, **pgAdmin**, or the viewer built into Neon and Supabase. Type the query, count the rows, compare with the screen. When the two disagree, you have located the problem to one side of the line, and that is most of the work.\n\nThis single habit, looking at the actual rows, changes your relationship with an app you did not write more than any other in this block. The screen can be wrong in a hundred ways. The table cannot argue with you.\n\n> Figure: The query inside the loop. Line chart of Database round trips against Bookings shown on the page: One query per row (10, 11) (50, 51) (200, 201) (500, 501); One query with a join (10, 1) (50, 1) (200, 1) (500, 1). Fetching a list and then querying once per item turns one round trip into one per row. At forty rows nothing is visible; at five hundred the page takes seconds. Count the lines in the query log."},"check":{"question":"A dashboard's booking count is lower than the number of rows in the table. The query uses `JOIN customers`. What should you check first?","options":[{"text":"Whether the count is being cached from an earlier request.","correct":false,"tempting":"Reaches for an infrastructure explanation when the query itself contains a mechanism that drops rows."},{"text":"Whether a LIMIT clause is capping the result.","correct":false,"tempting":"Would cap a list of rows, but aggregate counts are computed before any limit applies in the shape described."},{"text":"Whether some bookings reference customers that no longer exist, which a plain JOIN excludes.","correct":true},{"text":"Whether the status filter is excluding cancelled bookings.","correct":false,"tempting":"A reasonable filter to verify, but it would be a deliberate exclusion rather than the silent one the join creates."}]},"prev":{"slug":"enough-javascript-to-review","title":"Enough JavaScript to review a change","url":"https://addaly.com/learn/building-apps-with-ai/enough-javascript-to-review"},"next":{"slug":"devtools-as-an-x-ray","title":"The browser devtools as an x-ray","url":"https://addaly.com/learn/building-apps-with-ai/devtools-as-an-x-ray"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Reading the query behind the page', in Building Apps with AI. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/building-apps-with-ai/reading-the-query-behind-the-page (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Reading the query behind the page","courseTitle":"Building Apps with AI","module":{"index":3,"slug":"reading-and-checking","title":"Reading and checking what came back"},"answer":null,"keyIdea":"Find the WHERE clause, confirm the user’s identity in it came from the session, and check for a LIMIT and for queries running inside a loop.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"line","title":"The query inside the loop","series":[{"label":"One query per row","points":[[10,11],[50,51],[200,201],[500,501]]},{"label":"One query with a join","points":[[10,1],[50,1],[200,1],[500,1]]}],"xLabel":"Bookings shown on the page","yLabel":"Database round trips","caption":"Fetching a list and then querying once per item turns one round trip into one per row. At forty rows nothing is visible; at five hundred the page takes seconds. Count the lines in the query log."},"alt":"Figure: The query inside the loop. Line chart of Database round trips against Bookings shown on the page: One query per row (10, 11) (50, 51) (200, 201) (500, 501); One query with a join (10, 1) (50, 1) (200, 1) (500, 1). Fetching a list and then querying once per item turns one round trip into one per row. At forty rows nothing is visible; at five hundred the page takes seconds. Count the lines in the query log."}},"blocks":[{"t":"h2","id":"every-page-is-a-question-asked-of-the-database","runs":[{"text":"Every page is a question asked of the database"}]},{"t":"p","runs":[{"text":"The screen is the answer. The query is the question, and it is where the two failures that matter most both live: the wrong rows, and somebody else’s rows."}]},{"t":"p","runs":[{"text":"You do not need to write SQL fluently. You need to read one query and say what it will return."}]},{"t":"h2","id":"the-shape-of-a-select","runs":[{"text":"The shape of a select"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT id, starts_at, status\nFROM bookings\nWHERE customer_id = 71\n  AND status = 'confirmed'\nORDER BY starts_at DESC\nLIMIT 20;"},{"t":"p","runs":[{"text":"Read it in the order the database does. "},{"text":"FROM","code":true},{"text":" names the table. "},{"text":"WHERE","code":true},{"text":" decides which rows survive. "},{"text":"ORDER BY","code":true},{"text":" sorts them. "},{"text":"LIMIT","code":true},{"text":" cuts the list. "},{"text":"SELECT","code":true},{"text":" picks which columns come back."}]},{"t":"p","runs":[{"text":"The "},{"text":"WHERE","code":true},{"text":" clause is the whole security story. Remove "},{"text":"customer_id = 71","code":true},{"text":" and this returns every confirmed booking belonging to everybody."}]},{"t":"h2","id":"joins-briefly","runs":[{"text":"Joins, briefly"}]},{"t":"p","runs":[{"text":"A booking holds a "},{"text":"customer_id","code":true},{"text":", not a customer name. Getting the name means joining:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT b.starts_at, c.name\nFROM bookings b\nJOIN customers c ON c.id = b.customer_id\nWHERE b.stylist_id = 4;"},{"t":"p","runs":[{"text":"JOIN","code":true},{"text":" keeps rows that match on both sides. "},{"text":"LEFT JOIN","code":true},{"text":" keeps every row from the left table even when the right has no match, filling the missing columns with NULL. That difference produces a real bug: a plain "},{"text":"JOIN","code":true},{"text":" between bookings and customers silently drops every booking whose customer was deleted, and the count on your dashboard is wrong."}]},{"t":"h2","id":"null-is-not-empty","runs":[{"text":"NULL is not empty"}]},{"t":"p","runs":[{"text":"NULL means unknown, and it does not behave like a value."}]},{"t":"code","lang":"sql","label":"SQL","text":"WHERE cancelled_at != '2026-01-01'   -- excludes rows where cancelled_at IS NULL\nWHERE phone = NULL                    -- never true, for any row\nWHERE phone IS NULL                   -- the correct form"},{"t":"p","runs":[{"text":"The first line is the trap. A filter meant to exclude one date also silently excludes every row where the column was never set. This is exactly the class of bug that hides a subset of rows from a list without any error appearing anywhere, and it is common in generated queries."}]},{"t":"h2","id":"reading-an-orm-instead","runs":[{"text":"Reading an ORM instead"}]},{"t":"p","runs":[{"text":"Most generated projects use a query builder, which is the same thing with different punctuation:"}]},{"t":"code","lang":"js","label":"JavaScript","text":"const rows = await db\n  .select()\n  .from(bookings)\n  .where(and(\n    eq(bookings.customerId, session.userId),\n    eq(bookings.status, 'confirmed')\n  ))\n  .orderBy(desc(bookings.startsAt))\n  .limit(20)"},{"t":"p","runs":[{"text":"Same five clauses. "},{"text":"eq","code":true},{"text":" is "},{"text":"=","code":true},{"text":", "},{"text":"and","code":true},{"text":" is "},{"text":"AND","code":true},{"text":", "},{"text":"desc","code":true},{"text":" is "},{"text":"DESC","code":true},{"text":". The important habit transfers exactly: find the "},{"text":"where","code":true},{"text":", and check that the user’s identity in it came from the session and not from the request."}]},{"t":"p","runs":[{"text":"If you cannot tell what SQL a builder produces, most of them will print it. In Drizzle, "},{"text":".toSQL()","code":true},{"text":". In Prisma, set the query log. Seeing the real statement once removes a lot of uncertainty."}]},{"t":"h2","id":"three-things-to-check-in-any-query-you-review","runs":[{"text":"Three things to check in any query you review"}]},{"t":"p","runs":[{"text":"Is it scoped to a person?","b":true},{"text":" Any query returning user data needs a condition tying it to the logged-in identity, taken from the session on the server. This is the single most common serious hole in AI-built apps and it has a whole lesson later."}]},{"t":"p","runs":[{"text":"Is there a ","b":true},{"text":"LIMIT","b":true,"code":true},{"text":"?","b":true},{"text":" A list page with no limit works on your 40 test rows and takes eleven seconds when a real account has 90,000."}]},{"t":"p","runs":[{"text":"Is it inside a loop?","b":true},{"text":" Fetching a list and then querying once per item is the N+1 problem: 1 query becomes 201. It is invisible locally and it is the most common reason a generated page is slow."}]},{"t":"h2","id":"look-at-the-data-yourself","runs":[{"text":"Look at the data yourself"}]},{"t":"p","runs":[{"text":"The fastest way to check a page is to ask the database the same question directly."}]},{"t":"code","lang":"bash","label":"Bash","text":"psql $DATABASE_URL          # Postgres, free, comes with it\nsqlite3 app.db              # SQLite, one command"},{"t":"p","runs":[{"text":"Or use a free graphical client: "},{"text":"DBeaver","b":true},{"text":", "},{"text":"pgAdmin","b":true},{"text":", or the viewer built into Neon and Supabase. Type the query, count the rows, compare with the screen. When the two disagree, you have located the problem to one side of the line, and that is most of the work."}]},{"t":"p","runs":[{"text":"This single habit, looking at the actual rows, changes your relationship with an app you did not write more than any other in this block. The screen can be wrong in a hundred ways. The table cannot argue with you."}]}],"check":{"question":[{"text":"A dashboard’s booking count is lower than the number of rows in the table. The query uses "},{"text":"JOIN customers","code":true},{"text":". What should you check first?"}],"options":[{"text":[{"text":"Whether the count is being cached from an earlier request."}],"correct":false,"tempting":[{"text":"Reaches for an infrastructure explanation when the query itself contains a mechanism that drops rows."}]},{"text":[{"text":"Whether a LIMIT clause is capping the result."}],"correct":false,"tempting":[{"text":"Would cap a list of rows, but aggregate counts are computed before any limit applies in the shape described."}]},{"text":[{"text":"Whether some bookings reference customers that no longer exist, which a plain JOIN excludes."}],"correct":true,"tempting":null},{"text":[{"text":"Whether the status filter is excluding cancelled bookings."}],"correct":false,"tempting":[{"text":"A reasonable filter to verify, but it would be a deliberate exclusion rather than the silent one the join creates."}]}],"order":[0,1,3,2],"why":null},"prev":{"slug":"enough-javascript-to-review","title":"Enough JavaScript to review a change","minutes":9},"next":{"slug":"devtools-as-an-x-ray","title":"The browser devtools as an x-ray","minutes":8}}}