HTML MASTER CLASS /// LEARN TAGS /// BUILD STRUCTURE /// SEMANTIC WEB /// HTML MASTER CLASS /// LEARN TAGS ///

API Payloads in SQL & Databases

Learn about API Payloads in this comprehensive SQL & Databases development tutorial. The API connection.

⚡ Total XP: 0|💻 sql XP: 0

Skill Matrix

UNLOCK NODES BY LEARNING NEW TAGS.

Select an unlocked node to view details root

🚀 LEVEL UP TO SENIOR:Unlock 500+ Advanced Practical Challenges & Exercises.

🎓 COURSERA PARTNER:Earn professional Google, Meta, and IBM certificates to supercharge your resume.

1Matching JSON

A good backend developer writes their SELECT clause to exactly match the JSON shape the Frontend requested. If the React frontend expects { "userId": 1, "userEmail": "a@b.com" }, write SELECT id AS "userId", email AS "userEmail". Don't fetch raw data and map it in Node; let the database do the mapping.

2Step-by-Step Breakdown

The Most Used Command. The SELECT statement is the absolute core of SQL. It is used to fetch data from a database. Every backend API GET request eventually translates to a SELECT query.

**Select Everything (*).** Using an asterisk (*) means 'return every single column from the table'. 'SELECT * FROM users;' fetches the id, name, email, password, etc.

**The Danger of SELECT *.** In production backend code, you should NEVER use 'SELECT *'. If a table has 50 columns and you only need the name, fetching all 50 wastes memory, CPU, and network bandwidth.

Selecting Specific Columns. Always specify exactly the columns you need, separated by commas. 'SELECT id, email FROM users;'. This is highly optimized.

Knowledge Check. Why is it considered a bad practice to use SELECT * in production Node.js applications?

→It wastes memory and bandwidth by fetching columns you don't need
→It causes syntax errors in modern SQL engines

Concatenation. You can combine columns directly in the SELECT clause. In Postgres, you use '||'. 'SELECT first_name || ' ' || last_name AS full_name FROM users;'.

Selecting Static Values. You can SELECT things that aren't even in the table. 'SELECT id, 'active' AS status FROM users;'. Every row will have a column called status with the word 'active'.

Formatting Output. If your database returns a raw date, you can use functions in the SELECT clause to format it for the frontend before it even hits your Node server.

The FROM Clause. SELECT defines the columns. FROM defines the source. You can even SELECT from a 'sub-query' (a virtual table created by another query).

Summary. SELECT specifies the exact shape of the data you want returned.

Level Up 🚀

Advanced cheat sheets, SEO tricks, and interview prep for this topic.

Browser Support

ChromeSupported

Fully supported.

FirefoxSupported

Fully supported.

SafariSupported

Fully supported.

EdgeSupported

Fully supported.

Accessibility (A11y)

1Name Columns to Match Accessible Table Headers, Not Just Frontend Convenience

When a SELECT clause aliases columns for the API response (e.g. SELECT id AS "userId"), keep the underlying data self-descriptive enough that a table built from it can generate meaningful <th> header text, rather than relying on ambiguous single-letter aliases that leave screen reader users guessing what a column represents.

SEO Implications

1
Over-Fetching with SELECT * Slows Server-Rendered Pages and Hurts Core Web Vitals
Using SELECT * to fetch far more columns than a page needs, including large text or binary columns, increases database and network time before the page can render, which directly impacts Time to First Byte and Largest Contentful Paint for server-rendered, database-backed pages.

Best Practices

Never Use SELECT * in Production Application Code

Explicitly naming only the columns you need reduces memory, CPU, and network usage, and protects your code from silently breaking or leaking new sensitive columns (like a newly added password_hash) that get added to the table later.

Alias Columns in SQL to Match the Exact Shape Your API Needs

Instead of fetching raw column names and remapping them in application code, use SELECT id AS "userId", email AS "userEmail" so the database does the shape transformation, reducing boilerplate mapping logic in your Node.js layer.

Frequent Bugs

THE BUG

An API response silently exposes a new column (like a password hash or internal flag) after a teammate adds it to the users table.

THE FIX

This happens because the endpoint used SELECT * FROM users, which automatically includes any newly added column. Always SELECT only the specific columns you intend to expose, so schema changes can't silently leak new sensitive data.

THE BUG

A query concatenating a full name (first_name || ' ' || last_name) returns NULL for some rows even though both name fields look populated in the raw table.

THE FIX

In most SQL engines, concatenating any value with NULL produces NULL for the entire expression — if either column is NULL for that row, the whole result becomes NULL. Use COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') to substitute empty strings for NULLs before concatenating.

Real-World Examples

Shaping a SELECT to Match the Frontend's Expected JSON

A React frontend expected a user object shaped as { userId, userEmail }, and the team wanted the database to produce that shape directly instead of remapping field names in the Node.js API layer.

SELECT id AS "userId", email AS "userEmail"
FROM users
WHERE id = $1;

Interview Prep

Pascual Vila

Frontend Instructor // Code Syllabus

Common Pitfalls & Errors

The Error //

Using SELECT * in production, silently exposing newly added sensitive columns

-- Wrong: exposes every column, including future ones
SELECT * FROM users WHERE id = $1;

-- Correct: explicit, safe column list
SELECT id, name, email FROM users WHERE id = $1;

The Solution //

SELECT * automatically includes any column added to the table later, including sensitive ones like a password hash or internal flag. Always name exactly the columns your endpoint intends to expose.

The Error //

String concatenation producing NULL because one of the concatenated columns is NULL

-- Wrong: entire result is NULL if last_name is NULL
SELECT first_name || ' ' || last_name AS full_name FROM users;

-- Correct: COALESCE guards against NULL
SELECT COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') AS full_name FROM users;

The Solution //

Concatenating any value with NULL yields NULL for the entire expression in standard SQL. Wrap nullable columns in COALESCE to substitute an empty string before concatenating.