SQL in Node.js

Setting Up a Database Connection

The most common packages for MySQL connections in Node.js is mysql2. Here’s how to set it up:

// util/database.js
const mysql = require('mysql2');

// Create a connection pool
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'node_shop',
  password: 'your_password'
});

// Export promise wrapper
module.exports = pool.promise();

Using a connection pool is more efficient than new connection for each query, especially for web applications handling multiple concurrent requests.

Executing Basic Queries

With our connection established, we can execute SQL queries directly:

// models/product.js
const db = require('../util/database');

class Product {
  constructor(id, title, price, description, imageUrl) {
    this.id = id;
    this.title = title;
    this.price = price;
    this.description = description;
    this.imageUrl = imageUrl;
  }

  save() {
    if (this.id) {
      // Update existing product
      return db.execute(
        'UPDATE products SET title = ?, price = ?, description = ?, imageUrl = ? WHERE id = ?',
        [this.title, this.price, this.description, this.imageUrl, this.id]
      );
    } else {
      // Create new product
      return db.execute(
        'INSERT INTO products (title, price, description, imageUrl) VALUES (?, ?, ?, ?)',
        [this.title, this.price, this.description, this.imageUrl]
      );
    }
  }

  static fetchAll() {
    return db.execute('SELECT * FROM products');
  }

  static findById(id) {
    return db.execute('SELECT * FROM products WHERE id = ?', [id]);
  }

  static deleteById(id) {
    return db.execute('DELETE FROM products WHERE id = ?', [id]);
  }
}

module.exports = Product;

Handling Relationships Manually

One area where raw SQL requires more work is relationships. For our e-commerce example, Fetching a cart with products would look like:

// models/cart.js
const db = require('../util/database');

class Cart {
  static getCart(userId) {
    return db.execute(
      `SELECT c.id, p.id AS productId, p.title, p.price, p.description, p.imageUrl, ci.quantity 
       FROM carts c 
       JOIN cart_items ci ON c.id = ci.cartId 
       JOIN products p ON ci.productId = p.id 
       WHERE c.userId = ?`,
      [userId]
    ).then(([rows]) => {
      // Process the results into a cart object with products
      const cart = { id: rows[0]?.id, products: [] };
      rows.forEach(row => {
        if (row.id) {
          cart.products.push({
            id: row.productId,
            title: row.title,
            price: row.price,
            description: row.description,
            imageUrl: row.imageUrl,
            quantity: row.quantity
          });
        }
      });
      return cart;
    });
  }

  static addProduct(cartId, productId, quantity = 1) {
    // First check if product is already in cart
    return db.execute(
      'SELECT * FROM cart_items WHERE cartId = ? AND productId = ?',
      [cartId, productId]
    ).then(([rows]) => {
      if (rows.length > 0) {
        // Update quantity if product exists
        return db.execute(
          'UPDATE cart_items SET quantity = quantity + ? WHERE cartId = ? AND productId = ?',
          [quantity, cartId, productId]
        );
      } else {
        // Add new item if product doesn't exist in cart
        return db.execute(
          'INSERT INTO cart_items (cartId, productId, quantity) VALUES (?, ?, ?)',
          [cartId, productId, quantity]
        );
      }
    });
  }
}

module.exports = Cart;

The order creation process would look like this with raw SQL:

exports.postOrder = (req, res, next) => {
  const userId = req.user.id;
  let cartProducts;
  let cartId;
  
  // Get user's cart and products
  db.execute('SELECT id FROM carts WHERE userId = ?', [userId])
    .then(([cartRows]) => {
      cartId = cartRows[0].id;
      return db.execute(
        `SELECT p.*, ci.quantity 
         FROM products p 
         JOIN cart_items ci ON p.id = ci.productId 
         WHERE ci.cartId = ?`,
        [cartId]
      );
    })
    .then(([productRows]) => {
      cartProducts = productRows;
      // Create new order
      return db.execute(
        'INSERT INTO orders (userId) VALUES (?)',
        [userId]
      );
    })
    .then(([orderResult]) => {
      const orderId = orderResult.insertId;
      // Create order items
      const orderItems = cartProducts.map(product => {
        return db.execute(
          'INSERT INTO order_items (orderId, productId, quantity) VALUES (?, ?, ?)',
          [orderId, product.id, product.quantity]
        );
      });
      return Promise.all(orderItems);
    })
    .then(() => {
      // Clear the cart
      return db.execute(
        'DELETE FROM cart_items WHERE cartId = ?',
        [cartId]
      );
    })
    .then(() => {
      res.redirect('/orders');
    })
    .catch(err => console.log(err));
};

Benefits of Using Raw SQL

1. Full SQL Control: Access to database-specific features and optimizations
2. Performance: Potentially better performance for complex queries that you can optimize manually
3. Transparency: Clear visibility into exactly what SQL is being executed
4. Learning: Deeper understanding of SQL fundamentals
5. Debugging: Easier to troubleshoot specific database issues

When to Choose Raw SQL Over an ORM

Raw SQL might be preferable when:

• You need very specific query optimizations
• Your application has complex reporting needs
• You’re working with legacy databases
• Your team has strong SQL expertise
• The project is simple enough that an ORM adds unnecessary complexity