Raw queries when needed in Express - Mini Project: Build & Apply

Raw Queries When Needed in Express

📖 Scenario: You are building a simple Express app that needs to fetch user data from a database. Sometimes, you want to use raw SQL queries directly for more control.

🎯 Goal: Create an Express app that connects to a database and uses a raw SQL query to get users with age over a certain limit.

📋 What You'll Learn

Create a database connection variable called db

Create a variable ageLimit set to 30

Write a raw SQL query using db.query to select users older than ageLimit

Send the query result as JSON in the Express route handler

💡 Why This Matters

🌍 Real World

Raw SQL queries are useful when you need precise control over database operations beyond what an ORM offers. This is common in real-world apps that need optimized or complex queries.

💼 Career

Many backend developer jobs require knowledge of how to safely run raw queries in Express apps to handle special cases or performance tuning.

1

Set up database connection

Create a variable called db that requires the mysql2/promise module and connects to a database with host 'localhost', user 'root', password 'password', and database 'testdb'.

Express

# Your code here

Need a hint?

Use mysql2/promise to create a connection pool with the given credentials.

2

Add age limit variable

Create a variable called ageLimit and set it to 30.

Express

const mysql = require('mysql2/promise');
const db = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'testdb'
});

// Your code here

Need a hint?

Just create a constant named ageLimit and assign it the number 30.

3

Write raw SQL query in Express route

Create an Express app with express(). Add a GET route at '/users' that uses await db.query with the raw SQL 'SELECT * FROM users WHERE age > ?' and passes ageLimit as a parameter. Store the result in a variable called rows.

Express

const mysql = require('mysql2/promise');
const db = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'testdb'
});

const ageLimit = 30;

// Your code here

Need a hint?

Use db.query with a parameterized query to safely insert ageLimit.

4

Start the Express server

Add app.listen on port 3000 with a callback that logs 'Server running on port 3000'.

Express

const mysql = require('mysql2/promise');
const db = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'testdb'
});

const ageLimit = 30;

const express = require('express');
const app = express();

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

// Your code here

Need a hint?

Use app.listen to start the server on port 3000 and log a message.