Knex as query builder alternative in Express - Step-by-Step Execution

Concept Flow - Knex as query builder alternative

Start Express app

↓

Import Knex

↓

Configure Knex with DB

↓

Build query using Knex methods

↓

Execute query

↓

Receive results

↓

Send response to client

This flow shows how an Express app uses Knex to build and run database queries step-by-step.

Execution Sample

Express

const knex = require('knex')({
  client: 'sqlite3',
  connection: { filename: './data.db' },
  useNullAsDefault: true
});

knex('users').select('*').where('age', '>', 18).then(console.log);

This code builds a query to select all users older than 18 and logs the results.

Execution Table

Step	Action	Knex Query State	SQL Generated	Result
1	Initialize Knex with config	knex instance ready	N/A	N/A
2	Build query: select('*')	select * from users	SELECT * FROM `users`	Query object created
3	Add where condition: age > 18	select * from users where age > 18	SELECT * FROM `users` WHERE `age` > 18	Query object updated
4	Execute query with .then()	Query sent to DB	Executed SQL	[{id:1, name:'Alice', age:20}, ...]
5	Log results	N/A	N/A	Output user list to console
6	Send response to client	N/A	N/A	Client receives user data

💡 Query completes after sending results to client and logging output

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
knex	undefined	knex instance	knex instance	knex instance	knex instance
query	undefined	select * from users	select * from users where age > 18	results array	results array

Key Moments - 2 Insights

Why does the SQL query not run immediately after building it with Knex methods?

Knex builds a query object step-by-step but only runs the SQL when .then() or await is called, as shown in execution_table step 4.

How does Knex help compared to writing raw SQL strings?

Knex lets you build queries with JavaScript methods, reducing syntax errors and improving readability, as seen in steps 2 and 3.

Visual Quiz - 3 Questions

Look at the execution table, what SQL is generated after adding the where condition?

A INSERT INTO `users` VALUES (...)

B SELECT * FROM `users`

C SELECT * FROM `users` WHERE `age` > 18

D DELETE FROM `users` WHERE `age` < 18

💡 Hint

Check the SQL Generated column at Step 3 in the execution table.

At which step does the query actually run against the database?

A Step 4

B Step 2

C Step 3

D Step 5

💡 Hint

Look for when the 'Query sent to DB' happens in the Action column.

If you remove the where condition, how does the query change in the execution table?

A Query will not run

B SQL changes to SELECT * FROM `users`

C SQL changes to SELECT * FROM `users` WHERE `age` > 18

D Results will be empty

💡 Hint

Compare Step 2 and Step 3 SQL Generated columns to see effect of where condition.

Concept Snapshot

Knex is a JavaScript query builder for SQL databases.
Use chained methods like select(), where() to build queries.
Queries run only when awaited or .then() is called.
Knex improves readability and safety over raw SQL strings.
Integrates easily with Express apps for database access.

Full Transcript

This visual execution shows how an Express app uses Knex as a query builder alternative. First, Knex is initialized with database config. Then, a query is built step-by-step using methods like select and where. The SQL query is generated internally but not run yet. When .then() is called, the query executes against the database and returns results. These results are logged and sent back to the client. Variables like the knex instance and query object change state as the code runs. Key points include that queries are built before execution and Knex helps avoid raw SQL errors. The quiz questions check understanding of when SQL runs and how query building affects the SQL generated.

Practice

1. What is the main advantage of using Knex as a query builder in an Express app?

easy

A. It allows writing database queries using JavaScript instead of raw SQL.

B. It automatically creates database tables without any code.

C. It replaces Express middleware for handling requests.

D. It compiles JavaScript into SQL code for faster execution.

Solution

1. Step 1: Understand Knex's purpose

   Knex is designed to let developers write database queries in JavaScript instead of SQL.

2. Step 2: Compare options

   Options B, C, and D describe features Knex does not provide. It does not create tables automatically, replace Express middleware, or compile JS into SQL.

3. Final Answer:

   It allows writing database queries using JavaScript instead of raw SQL. -> Option A

4. Quick Check:

   Knex = JS query builder [OK]

Hint: Knex lets you write queries in JS, not raw SQL [OK]

Common Mistakes:

• Thinking Knex creates tables automatically
• Confusing Knex with Express middleware
• Believing Knex compiles JS to SQL code

2. Which of the following is the correct way to select all rows from a table named users using Knex?

easy

A. knex.tables('users').select('*')

B. knex.from('users').selects()

C. knex.query('SELECT * FROM users')

D. knex.select('*').from('users')

Solution

1. Step 1: Review Knex select syntax

   The common pattern is knex.select('*').from('tableName') to get all rows.

2. Step 2: Check each option

   knex.select('*').from('users') matches the correct syntax. knex.from('users').selects() uses invalid selects() method. knex.tables('users').select('*') uses knex.tables which is not standard Knex syntax. knex.query('SELECT * FROM users') uses raw SQL string which is not the Knex query builder method.

3. Final Answer:

   knex.select('*').from('users') -> Option D

4. Quick Check:

   Correct select syntax = knex.select('*').from('users') [OK]

Hint: Use knex.select('*').from('table') to get all rows [OK]

Common Mistakes:

• Using knex.tables() instead of knex.select().from()
• Using selects() instead of select()
• Trying to pass raw SQL string to knex

3. What will the following Knex query return?

knex('products').where('price', '>', 100).select('id', 'name')

medium

A. All products with price less than or equal to 100, showing id and name.

B. All products with price greater than 100, showing all columns.

C. All products with price greater than 100, showing id and name.

D. Syntax error due to incorrect method chaining.

Solution

1. Step 1: Analyze the where clause

   The query filters products where price is greater than 100.

2. Step 2: Analyze the select clause

   It selects only the 'id' and 'name' columns to return.

3. Final Answer:

   All products with price greater than 100, showing id and name. -> Option C

4. Quick Check:

   where('price', '>', 100) + select('id', 'name') = All products with price greater than 100, showing id and name. [OK]

Hint: where filters rows; select chooses columns [OK]

Common Mistakes:

• Confusing > with < in where clause
• Assuming all columns are returned
• Thinking method chaining causes syntax error

4. Identify the error in this Knex query:

knex('orders').where('status' = 'pending').select()

medium

A. Missing table name in knex call.

B. Using '=' instead of ',' inside where method.

C. select() requires column names as arguments.

D. where method cannot be chained after knex call.

Solution

1. Step 1: Check where method syntax

   The where method expects arguments separated by commas, e.g., where('status', 'pending').

2. Step 2: Identify the error

   The query uses '=' inside where which is invalid syntax in JavaScript function calls.

3. Final Answer:

   Using '=' instead of ',' inside where method. -> Option B

4. Quick Check:

   where('status' = 'pending') is invalid [OK]

Hint: Use commas, not '=' inside where() arguments [OK]

Common Mistakes:

• Using '=' instead of ',' in method arguments
• Thinking select() must have columns
• Believing where cannot be chained

5. You want to update the email of a user with id = 5 using Knex. Which query correctly performs this update?

hard

A. knex('users').where('id', 5).update({ email: '[e-mail]' })

B. knex('users').set('email', '[e-mail]').where('id', 5)

C. knex.update('users').set('email', '[e-mail]').where('id', 5)

D. knex('users').update({ email: '[e-mail]' }).where('id' = 5)

Solution

1. Step 1: Recall correct update syntax

   Knex updates usually chain where() before update() to specify which rows to change.

2. Step 2: Evaluate each option

   knex('users').where('id', 5).update({ email: '[e-mail]' }) correctly chains where('id', 5) before update({ email: ... }). knex('users').update({ email: '[e-mail]' }).where('id' = 5) uses '=' instead of ',' causing syntax error. knex('users').set('email', '[e-mail]').where('id', 5) uses invalid set() method. knex.update('users').set('email', '[e-mail]').where('id', 5) uses invalid syntax.

3. Final Answer:

   knex('users').where('id', 5).update({ email: '[e-mail]' }) -> Option A

4. Quick Check:

   where() before update() is correct pattern [OK]

Hint: Chain where() before update() to target rows [OK]

Common Mistakes:

• Using '=' instead of ',' in where arguments
• Using non-existent set() method
• Using invalid knex.update('users') syntax