Raw queries when needed in Express

Introduction

Sometimes you need to write your own database commands directly to do things that tools can't do easily.

When you want to run a special database command not supported by your usual tools.

When you need to optimize a query for speed and want full control.

When you want to use database-specific features or functions.

When your usual query builder can't express the query you need.

When debugging or testing raw SQL commands quickly.

Syntax

Express

const result = await db.raw('YOUR SQL QUERY HERE', [optionalParams]);

Use db.raw() to run raw SQL queries in Express with a database library like Knex.

You can pass parameters safely to avoid SQL injection by using placeholders and an array of values.

Examples

This runs a raw SQL query to get users older than 18.

Express

const users = await db.raw('SELECT * FROM users WHERE age > ?', [18]);

This runs a raw SQL query to count all orders.

Express

const count = await db.raw('SELECT COUNT(*) FROM orders');

This updates prices for all products in the 'books' category by increasing them 10%.

Express

const result = await db.raw('UPDATE products SET price = price * 1.1 WHERE category = ?', ['books']);

Sample Program

This Express app uses a raw SQL query to get users aged 18 or older from an in-memory SQLite database and returns them as JSON.

Express

import express from 'express';
import knex from 'knex';

const app = express();

const db = knex({
  client: 'sqlite3',
  connection: {
    filename: ':memory:'
  },
  useNullAsDefault: true
});

// Setup a simple table and data
await db.schema.createTable('users', table => {
  table.increments('id');
  table.string('name');
  table.integer('age');
});

await db('users').insert([
  { name: 'Alice', age: 25 },
  { name: 'Bob', age: 17 },
  { name: 'Charlie', age: 30 }
]);

app.get('/adults', async (req, res) => {
  const adultsResult = await db.raw('SELECT name, age FROM users WHERE age >= ?', [18]);
  const adults = adultsResult.rows || adultsResult; // Adjust for different DB clients
  res.json(adults);
});

app.listen(3000, () => {
  console.log('Server running on http://localhost:3000');
});

Important Notes

Always use parameter placeholders (?) and pass values as an array to avoid SQL injection risks.

Raw queries bypass some safety and convenience features, so use them only when necessary.

Check your database library's documentation for exact syntax and return formats of raw queries.

Summary

Raw queries let you write direct SQL commands when needed.

Use placeholders and parameters to keep queries safe.

Good for special cases where normal query builders can't help.