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.