Raw queries when needed in Express - Step-by-Step Execution

Concept Flow - Raw queries when needed

Receive HTTP request

↓

Check if raw query needed?

No→Use ORM methods

Yes↓

Write raw SQL query string

↓

Send query to database

↓

Receive raw results

↓

Send response to client

This flow shows how Express handles a request that needs a raw SQL query instead of ORM methods.

Execution Sample

Express

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

This code runs a raw SQL query to get users older than 30 and sends the results as JSON.

Execution Table

Step	Action	Query String	Parameters	Result	Response Sent
1	Receive GET /users request
2	Decide to run raw query	SELECT * FROM users WHERE age > $1	[30]
3	Send query to database	SELECT * FROM users WHERE age > $1	[30]	Rows with age > 30
4	Receive query results			Rows with age > 30
5	Send JSON response			Rows with age > 30	JSON array of users

💡 Response sent to client with raw query results

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
req	HTTP request object	HTTP request object	HTTP request object	HTTP request object	HTTP request object
queryString		SELECT * FROM users WHERE age > $1	SELECT * FROM users WHERE age > $1	SELECT * FROM users WHERE age > $1	SELECT * FROM users WHERE age > $1
params		[30]	[30]	[30]	[30]
result				Rows with age > 30	Rows with age > 30
res	HTTP response object	HTTP response object	HTTP response object	HTTP response object	Sent JSON response

Key Moments - 3 Insights

Why do we use raw queries instead of ORM methods sometimes?

Raw queries let us write custom SQL for complex or optimized operations that ORM methods can't easily do, as shown in step 2 of the execution_table.

How do parameters like [30] protect the query?

Parameters prevent SQL injection by separating data from code, as seen in the 'Parameters' column in steps 2 and 3.

What happens if the raw query returns no rows?

The result will be an empty array, and the response will send an empty JSON array, similar to step 5 but with no user data.

Visual Quiz - 3 Questions

Look at the execution_table, what is the query string sent to the database at step 3?

A SELECT * FROM users WHERE age > $1

B SELECT * FROM users

C INSERT INTO users VALUES ($1)

D UPDATE users SET age = $1

💡 Hint

Check the 'Query String' column at step 3 in the execution_table.

At which step does the server send the JSON response back to the client?

A Step 2

B Step 3

C Step 5

D Step 4

💡 Hint

Look at the 'Response Sent' column in the execution_table.

If the parameter changed from [30] to [50], how would the result change?

A It would return users younger than 30

B It would return users older than 50 instead of 30

C It would return all users regardless of age

D It would cause a syntax error

💡 Hint

Refer to the 'Parameters' column and how it affects the query in the execution_table.

Concept Snapshot

Raw queries in Express let you write SQL directly for complex database tasks.
Use parameterized queries to keep data safe from injection.
Send raw SQL with db.query(sql, params) and handle results manually.
Useful when ORM methods are too limited or slow.
Always send the query results back as a response to the client.

Full Transcript

This visual execution shows how Express handles raw SQL queries when needed. First, the server receives a request. It decides to run a raw SQL query instead of using ORM methods. The raw SQL string and parameters are prepared. The query is sent to the database. The database returns matching rows. Finally, the server sends these rows as a JSON response to the client. Variables like the query string, parameters, and results change step by step. Beginners often wonder why raw queries are used, how parameters protect queries, and what happens if no data is returned. The quiz questions help check understanding of query strings, response timing, and parameter effects.

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