Express Query Building

Introduction

When building web applications with Express.js, one of the most common tasks is interacting with a database. Whether you're storing user information, blog posts, product inventories, or application data, you'll need a way to efficiently query and manipulate your database. Express Query Building refers to the techniques and tools used to construct database queries within an Express application.

Query building is essential because it:

Creates an abstraction layer between your application code and raw database queries
Helps prevent SQL injection attacks
Makes your code more maintainable and readable
Provides a consistent interface regardless of the database you're using

In this guide, we'll explore different approaches to query building in Express applications, from raw queries to query builders and ORMs (Object-Relational Mappers).

Prerequisites

Before diving into query building, you should have:

Basic knowledge of Express.js
Understanding of JavaScript and Node.js
A database system installed (like MySQL, PostgreSQL, MongoDB)
Express and appropriate database drivers installed in your project

Basic Query Building Approaches

1. Raw Queries

The most straightforward way to interact with a database is through raw queries. Here's an example using MySQL:

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

app.get('/users', (req, res) => {
  connection.query('SELECT * FROM users', (error, results) => {
    if (error) throw error;
    res.json(results);
  });
});

While this approach is simple, it has several drawbacks:

Prone to SQL injection if not properly sanitized
Mixes business logic with database queries
Can become hard to maintain as queries grow in complexity
Database-specific syntax ties your application to a particular database system

2. Parameterized Queries

A better approach is to use parameterized queries to prevent SQL injection:

javascript
app.get('/users/:id', (req, res) => {
  const userId = req.params.id;
  
  connection.query(
    'SELECT * FROM users WHERE id = ?', 
    [userId],
    (error, results) => {
      if (error) throw error;
      res.json(results[0] || { message: 'User not found' });
    }
  );
});

This approach is more secure but still has the other limitations of raw queries.

Query Builders

Query builders provide a cleaner, more abstract syntax for building SQL queries programmatically. Let's look at a popular option - Knex.js.

Using Knex.js

Knex.js is a flexible SQL query builder that works with various databases.

First, install Knex and your database driver:

bash
npm install knex mysql

Then configure Knex in your application:

javascript
const knex = require('knex')({
  client: 'mysql',
  connection: {
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'myapp'
  }
});

Now you can build queries using a chainable API:

javascript
app.get('/users', async (req, res) => {
  try {
    // Simple query
    const users = await knex('users').select('*');
    res.json(users);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.get('/users/search', async (req, res) => {
  try {
    // More complex query with filtering
    const { name, email, limit = 10, offset = 0 } = req.query;
    
    const query = knex('users').select('id', 'name', 'email', 'created_at');
    
    if (name) {
      query.where('name', 'like', `%${name}%`);
    }
    
    if (email) {
      query.where('email', 'like', `%${email}%`);
    }
    
    const users = await query.limit(limit).offset(offset);
    res.json(users);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

Benefits of Knex.js

Chainable query builder API
SQL injection protection
Transaction support
Migrations and seeding
Multi-database support

ORM Approaches

Object-Relational Mappers (ORMs) take database abstraction a step further by mapping database tables to JavaScript classes.

Using Sequelize

Sequelize is a popular ORM for Node.js.

First, install Sequelize:

bash
npm install sequelize mysql2

Set up your models:

javascript
const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize('myapp', 'root', 'password', {
  host: 'localhost',
  dialect: 'mysql'
});

// Define a model
const User = sequelize.define('User', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.STRING,
    allowNull: false
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true,
    validate: {
      isEmail: true
    }
  },
  password: {
    type: DataTypes.STRING,
    allowNull: false
  }
}, {
  // Options
  tableName: 'users',
  timestamps: true
});

// Sync model with database
sequelize.sync();

module.exports = { User };

Now you can use the model to query the database:

javascript
const { User } = require('./models');

app.get('/users', async (req, res) => {
  try {
    const users = await User.findAll({
      attributes: ['id', 'name', 'email', 'createdAt']
    });
    res.json(users);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.get('/users/:id', async (req, res) => {
  try {
    const user = await User.findByPk(req.params.id);
    if (user) {
      res.json(user);
    } else {
      res.status(404).json({ message: 'User not found' });
    }
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

Benefits of Sequelize

Models represent your database structure
Data validation built-in
Relationships between models
Hooks for processing data
Database-agnostic code

MongoDB Query Building with Mongoose

If you're using MongoDB, Mongoose is the most popular ODM (Object Document Mapper):

bash
npm install mongoose

Set up connection and define a schema:

javascript
const mongoose = require('mongoose');

// Connect to MongoDB
mongoose.connect('mongodb://localhost:27017/myapp', {
  useNewUrlParser: true,
  useUnifiedTopology: true
})
.then(() => console.log('MongoDB connected'))
.catch(err => console.error('MongoDB connection error:', err));

// Define schema
const userSchema = new mongoose.Schema({
  name: { type: String, required: true },
  email: { type: String, required: true, unique: true },
  password: { type: String, required: true },
  createdAt: { type: Date, default: Date.now }
});

// Create model
const User = mongoose.model('User', userSchema);

module.exports = { User };

Use the model to query MongoDB:

javascript
const { User } = require('./models');

app.get('/users', async (req, res) => {
  try {
    const users = await User.find()
      .select('name email createdAt')
      .sort({ createdAt: -1 });
    res.json(users);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.get('/users/search', async (req, res) => {
  try {
    const { name, email } = req.query;
    const query = {};
    
    if (name) {
      query.name = { $regex: name, $options: 'i' };
    }
    
    if (email) {
      query.email = { $regex: email, $options: 'i' };
    }
    
    const users = await User.find(query).select('name email createdAt');
    res.json(users);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

Practical Example: Building a Blog API

Let's create a simple blog API with Express and Knex, featuring posts and comments.

First, set up the database tables:

javascript
// migrations/create_tables.js
exports.up = function(knex) {
  return knex.schema
    .createTable('posts', function(table) {
      table.increments('id');
      table.string('title').notNullable();
      table.text('content').notNullable();
      table.integer('user_id').notNullable().references('id').inTable('users');
      table.timestamps(true, true);
    })
    .createTable('comments', function(table) {
      table.increments('id');
      table.text('content').notNullable();
      table.integer('post_id').notNullable().references('id').inTable('posts');
      table.integer('user_id').notNullable().references('id').inTable('users');
      table.timestamps(true, true);
    });
};

exports.down = function(knex) {
  return knex.schema
    .dropTable('comments')
    .dropTable('posts');
};

Now, let's implement the routes:

javascript
// routes/posts.js
const express = require('express');
const router = express.Router();
const knex = require('../database');

// Get all posts
router.get('/', async (req, res) => {
  try {
    const posts = await knex('posts')
      .join('users', 'posts.user_id', '=', 'users.id')
      .select(
        'posts.id', 
        'posts.title', 
        'posts.content',
        'posts.created_at',
        'users.name as author'
      )
      .orderBy('posts.created_at', 'desc');
      
    res.json(posts);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Get single post with comments
router.get('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    
    // Get post details
    const post = await knex('posts')
      .join('users', 'posts.user_id', '=', 'users.id')
      .select(
        'posts.id', 
        'posts.title', 
        'posts.content',
        'posts.created_at',
        'users.name as author'
      )
      .where('posts.id', id)
      .first();
      
    if (!post) {
      return res.status(404).json({ message: 'Post not found' });
    }
    
    // Get comments for this post
    const comments = await knex('comments')
      .join('users', 'comments.user_id', '=', 'users.id')
      .select(
        'comments.id',
        'comments.content',
        'comments.created_at',
        'users.name as author'
      )
      .where('comments.post_id', id)
      .orderBy('comments.created_at', 'asc');
    
    // Combine post and comments
    post.comments = comments;
    
    res.json(post);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Create new post
router.post('/', async (req, res) => {
  try {
    const { title, content, userId } = req.body;
    
    if (!title || !content || !userId) {
      return res.status(400).json({ message: 'Title, content and userId are required' });
    }
    
    const [postId] = await knex('posts').insert({
      title,
      content,
      user_id: userId
    });
    
    const post = await knex('posts').where('id', postId).first();
    res.status(201).json(post);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Add other methods (update, delete) here

module.exports = router;

Best Practices for Express Query Building

Use parameterized queries or query builders to prevent SQL injection
Separate database logic from route handlers to improve code organization
Handle errors properly and provide meaningful error messages
Use transactions for operations that involve multiple queries
Implement pagination for large result sets
Index your database properly for frequently accessed columns
Cache query results when appropriate for better performance
Validate input data before using it in queries
Use connection pooling for better performance
Log slow queries for optimization

Summary

Express Query Building offers multiple approaches to interact with databases in your Express applications:

Raw queries: Simple but potentially insecure and hard to maintain
Query builders (like Knex.js): Provide a clean API for constructing SQL queries
ORMs (like Sequelize): Map database tables to JavaScript classes for an object-oriented approach
ODMs (like Mongoose): Similar to ORMs but designed for document databases like MongoDB

The right approach depends on your project's complexity, team familiarity, and specific requirements. For smaller projects or teams familiar with SQL, query builders may be sufficient. For larger projects with complex data relationships, an ORM might be a better choice.

Additional Resources

Exercises

Create a RESTful API for a to-do application using Express and the query builder of your choice.
Implement pagination for a resource that could have many records (e.g., products or articles).
Build a search API endpoint that allows filtering by multiple criteria.
Implement a relationship between two entities (e.g., orders and products) and create endpoints to retrieve related data.
Add validation to your API endpoints to ensure data integrity before inserting into the database.

With these techniques and practices, you'll be able to build efficient, secure, and maintainable database queries in your Express applications!

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Prerequisites​

Basic Query Building Approaches​

1. Raw Queries​

2. Parameterized Queries​

Query Builders​

Using Knex.js​

Benefits of Knex.js​

ORM Approaches​

Using Sequelize​

Benefits of Sequelize​

MongoDB Query Building with Mongoose​

Practical Example: Building a Blog API​

Best Practices for Express Query Building​

Summary​

Additional Resources​

Exercises​