Raw queries when needed in Express - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of raw SQL query execution in Express

Consider this Express route using a raw SQL query with a database client. What will be the output sent to the client?

Express

app.get('/users', async (req, res) => {
  const result = await db.query('SELECT name FROM users WHERE id = $1', [1]);
  res.send(result.rows[0].name);
});

A "[object Object]"

B SyntaxError

C "John"

D undefined

💡 Hint

Think about how the query result is structured and how to access the first user's name.

The query returns an object with a 'rows' array. Accessing rows[0].name gets the first user's name, which is sent as a string.

❓ component_behavior

intermediate

Behavior of raw query with parameter injection

What happens if a raw SQL query in Express is constructed by concatenating user input directly, like this?

Express

app.get('/search', async (req, res) => {
  const term = req.query.term;
  const query = `SELECT * FROM products WHERE name LIKE '%${term}%'`;
  const result = await db.query(query);
  res.json(result.rows);
});

A It returns an empty array always.

B It safely escapes user input to prevent SQL injection.

C It throws a syntax error due to template literals.

D It works but is vulnerable to SQL injection attacks.

💡 Hint

Think about what happens when user input is directly inserted into SQL strings.

Directly inserting user input into SQL strings without escaping allows attackers to inject malicious SQL code.

📝 Syntax

advanced

Correct syntax for parameterized raw query in Express

Which option shows the correct way to write a parameterized raw SQL query using a PostgreSQL client in Express?

A db.query('SELECT * FROM users WHERE id = ?', [userId]);

B db.query('SELECT * FROM users WHERE id = $1', [userId]);

C db.query('SELECT * FROM users WHERE id = :userId', { userId });

D db.query('SELECT * FROM users WHERE id = $userId', [userId]);

💡 Hint

PostgreSQL uses $1, $2 placeholders for parameters.

PostgreSQL client libraries use $1, $2 placeholders with an array of values for parameters.

🔧 Debug

advanced

Debugging error from raw query in Express

This Express route throws an error when running a raw query. What is the cause?

Express

app.get('/orders', async (req, res) => {
  const result = await db.query('SELECT * FROM orders WHERE date = $1', [req.query.date]);
  res.json(result.rows);
});

A The parameter should be passed as an array, not a string.

B The SQL query syntax is invalid.

C The 'date' field does not exist in the 'orders' table.

D The route handler is missing async keyword.

💡 Hint

Check how parameters are passed to the query function.

The query method expects parameters as an array. Passing a string causes a runtime error.

🧠 Conceptual

expert

Why use raw queries in Express apps?

Which reason best explains when you should use raw SQL queries in an Express app instead of an ORM?

A When you need complex queries or performance optimizations not supported by the ORM.

B When you want to avoid writing any SQL and rely fully on JavaScript.

C When you want to automatically generate database schemas from models.

D When you want to ensure complete database abstraction without any SQL knowledge.

💡 Hint

Think about the trade-offs between ORMs and raw SQL.

Raw queries allow fine control and optimizations for complex or performance-critical database operations.

Practice

1. What is the main reason to use raw queries in Express applications?

easy

A. To write direct SQL commands when query builders are insufficient

B. To automatically generate SQL queries without any code

C. To avoid using any SQL and rely only on JavaScript

D. To speed up the server by skipping database calls

Solution

1. Step 1: Understand raw queries purpose

   Raw queries allow writing direct SQL commands for complex or special cases.

2. Step 2: Compare with other options

   Options A, B, and C are incorrect because raw queries do not skip database calls, auto-generate SQL, or avoid SQL.

3. Final Answer:

   To write direct SQL commands when query builders are insufficient -> Option A

4. Quick Check:

   Raw queries = direct SQL use [OK]

Hint: Raw queries = direct SQL for special cases [OK]

Common Mistakes:

• Thinking raw queries auto-generate SQL
• Believing raw queries avoid SQL entirely
• Assuming raw queries skip database calls

2. Which of the following is the correct way to use a raw query with parameters in Express using a SQL library?

easy

A. db.query('SELECT * FROM users WHERE id = ' + userId)

B. db.query('SELECT * FROM users WHERE id = ?', [userId])

C. db.query('SELECT * FROM users WHERE id = $userId')

D. db.query('SELECT * FROM users WHERE id = :userId')

Solution

1. Step 1: Identify parameterized query syntax

   Using '?' with an array of parameters is the standard safe way to pass values.

2. Step 2: Check other options

   db.query('SELECT * FROM users WHERE id = ' + userId) concatenates strings (unsafe). Options C and D use placeholders not standard in many Express SQL libraries.

3. Final Answer:

   db.query('SELECT * FROM users WHERE id = ?', [userId]) -> Option B

4. Quick Check:

   Use '?' + params array for safe raw queries [OK]

Hint: Use '?' with array for parameters in raw queries [OK]

Common Mistakes:

• Concatenating variables directly into query strings
• Using unsupported placeholder syntax
• Not passing parameters as an array

3. Given the code snippet:

const result = await db.query('SELECT * FROM products WHERE price > ?', [100]);
console.log(result.rows.length);

What will be logged if there are 5 products priced over 100?

medium

A. 5

B. undefined

C. 0

D. An error will occur

Solution

1. Step 1: Understand the query and parameters

   The raw query selects products with price greater than 100, passing 100 safely as a parameter.

2. Step 2: Interpret the result object

   Assuming the database library returns an object with a 'rows' array, its length will be the number of matching products, which is 5.

3. Final Answer:

   5 -> Option A

4. Quick Check:

   Rows length = number of matching products [OK]

Hint: Result rows length = count of matched records [OK]

Common Mistakes:

• Expecting result directly as array without .rows
• Confusing parameter placement
• Assuming query returns undefined or error

4. Identify the error in this raw query usage:

const userId = 10;
const result = await db.query('SELECT * FROM users WHERE id = ' + userId);

medium

A. The parameter should be passed as an object, not concatenated

B. The query syntax is invalid because of missing quotes

C. It risks SQL injection by concatenating variables directly

D. The query will always return zero results

Solution

1. Step 1: Analyze query construction

   The query concatenates userId directly into the string, which is unsafe and can cause SQL injection.

2. Step 2: Understand best practice

   Parameters should be passed separately using placeholders to avoid injection risks.

3. Final Answer:

   It risks SQL injection by concatenating variables directly -> Option C

4. Quick Check:

   Never concatenate variables directly in raw queries [OK]

Hint: Avoid string concatenation; use placeholders [OK]

Common Mistakes:

• Ignoring SQL injection risks
• Thinking concatenation is safe for numbers
• Not using parameterized queries

5. You need to update multiple user records with different emails using raw queries in Express. Which approach is best to safely perform this?

hard

A. Use raw queries without parameters for faster execution

B. Concatenate all updates into one big query string and run once

C. Update users one by one by concatenating values directly

D. Use a loop with parameterized raw queries for each user update

Solution

1. Step 1: Consider safety and flexibility

   Using a loop with parameterized queries ensures each update is safe and avoids SQL injection.

2. Step 2: Evaluate other options

   Concatenating all updates risks injection and syntax errors; skipping parameters is unsafe; direct concatenation is insecure.

3. Final Answer:

   Use a loop with parameterized raw queries for each user update -> Option D

4. Quick Check:

   Loop + parameters = safe batch updates [OK]

Hint: Loop with parameters for multiple safe updates [OK]

Common Mistakes:

• Trying to build one big concatenated query
• Ignoring parameterization for speed
• Concatenating user data directly