Node.js MySQL Where

In Node.js, when working with MySQL, the WHERE clause is the primary tool used to filter records. Without it, a SELECT statement would return every single row in a table, which is rarely what you want in a production application. By using WHERE, you can pinpoint specific data—such as finding a user by their ID, fetching orders placed within the last 24 hours, or searching for products within a certain price range.

Key Features of Node.js MySQL Where

1. Precise Filtering: Narrow down results to only the data you actually need, reducing memory usage in your Node.js app.
2. Logical Logic (AND/OR): Chain multiple requirements together to create sophisticated search queries.
3. Flexible Comparisons: Go beyond simple equality with operators like "greater than" (>), "less than" (<), and "not equal" (!=).
4. Pattern Matching: Find partial matches (like searching for a username) using the LIKE operator and wildcards.
5. Security via Placeholders: Protect your database from malicious attacks by using ? placeholders (Prepared Statements) instead of concatenating strings.

Step 1 Prerequisites

Before querying your database, ensure you have the mysql2 driver installed. While the original mysql package exists, mysql2 is faster and offers better support for modern JavaScript features.

npm install mysql2

Step 2 Basic Query with WHERE Clause

The simplest way to use WHERE is to match a column against a single value. In the example below, we look for a specific user. Notice the use of the ? symbol; this is a placeholder that the library will safely swap with our variable.

Example Code

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         
  password: 'your_password',         
  database: 'testdb'     
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Find a user named 'John Doe'
  const selectQuery = 'SELECT id, name, email FROM users WHERE name = ?';
  const values = ['John Doe'];

  connection.query(selectQuery, values, (err, results) => {
    if (err) {
      console.error('Error fetching records:', err.stack);
      return;
    }
    console.log('User found:', results);
  });

  connection.end();
});

Output:

Filtered records: [
  { id: 1, name: 'John Doe', email: '[email protected]' }
]

Step 3 Using Multiple Conditions with AND/OR

In real-world apps, you often need to check multiple things at once. For example, a login system needs to check if the username AND the password are correct. Conversely, you might want to find users who live in New York OR London.

Example Code with AND

The AND operator requires all conditions to be met.

// Select records where both name and email match exactly
const selectQuery = 'SELECT * FROM users WHERE name = ? AND email = ?';
const values = ['John Doe', '[email protected]'];

connection.query(selectQuery, values, (err, results) => {
  if (err) throw err;
  console.log('Matched both criteria:', results);
});

Example Code with OR

The OR operator returns a result if any of the conditions are met.

// Select records where either the name matches OR the email matches
const selectQuery = 'SELECT * FROM users WHERE name = ? OR email = ?';
const values = ['John Doe', '[email protected]'];

connection.query(selectQuery, values, (err, results) => {
  if (err) throw err;
  console.log('Matched at least one criteria:', results);
});

Step 4 Using Comparison Operators

Filtering isn't limited to "equals." You can use standard mathematical operators to filter numerical data, dates, or even alphabetical ranges.

• > and < : Greater than / Less than.
• >= and <= : Greater than or equal / Less than or equal.
• <> or != : Not equal to.
• BETWEEN : Useful for ranges (e.g., prices between 10 and 50).

Example Code with Greater Than

// Fetching users over a certain age (e.g., for age-restricted content)
const minAge = 25;
const selectQuery = 'SELECT name, age FROM users WHERE age > ?';

connection.query(selectQuery, [minAge], (err, results) => {
  if (err) throw err;
  console.log('Users older than 25:', results);
});

Step 5 Using LIKE for Pattern Matching

The LIKE operator is used when you don't know the exact value. It uses two main wildcards:

• % : Represents zero, one, or multiple characters.
• _ : Represents a single character.

Example Code with LIKE

// Search for any user whose name starts with "John"
// 'John%' will match 'John', 'John Doe', and 'Johnny'
const searchQuery = 'John%';
const selectQuery = 'SELECT * FROM users WHERE name LIKE ?';

connection.query(selectQuery, [searchQuery], (err, results) => {
  if (err) throw err;
  console.log('Search Results:', results);
});

Step 6 Using Prepared Statements

While connection.query() is common, the mysql2 library provides a more robust connection.execute() method for prepared statements. This is slightly more efficient if you run the same query multiple times with different values, as the database "pre-compiles" the SQL logic.

Example Code with Prepared Statements

const mysql = require('mysql2');
const connection = mysql.createConnection({ host: 'localhost', user: 'root', database: 'testdb' });

// connection.execute is the preferred way for prepared statements
const selectQuery = 'SELECT * FROM users WHERE id = ?';
const userId = 1;

connection.execute(selectQuery, [userId], (err, results) => {
  if (err) {
    console.error(err);
    return;
  }
  console.log('User found via prepared statement:', results);
});

Summary

Mastering the WHERE clause is essential for any Node.js developer working with databases. By combining comparison operators, logical AND/OR conditions, and LIKE patterns, you can build complex data-driven features. Always prioritize security by using prepared statements and placeholders to keep your user data safe from injection attacks.