SQL vs SQL Builder vs ORM

Introduction

Developing websites inevitably involves interacting with relational databases through three methods:

• SQL
• SQL Builder
• Object-Relational Mapping (ORM)

These three have different usage methods, advantages, disadvantages, and suitable scenarios. Understanding their differences can help us make more suitable choices for project needs.

SQL

SQL is the language for communicating with relational databases. Although there are some syntactical differences between different databases, they are relatively minor. To achieve the best flexibility and performance, SQL can be used, but this extreme flexibility may hide the risk of SQL Injection.

// ❌ Users may add malicious input leading to malicious SQL commands being executed.

const username = req.body.username;
const password = req.body.password;

const query = `SELECT * FROM users WHERE username = '${username}' AND password = '${password}'`;

db.query(query, (err, results) => {
  if (results.length > 0) {
    // Login successful
  } else {
    // Login failed
  }
});

// ✅ Use Prepared Statement to securely handle user input and prevent SQL injection.
const username = req.body.username;
const password = req.body.password;

const query = 'SELECT * FROM users WHERE username = ? AND password = ?';

db.query(query, [username, password], (err, results) => {
  // Securely handle the query
});

SQL Builder

A solution between flexibility and security, which constructs SQL through an abstract function combination, thus avoiding SQL Injection issues.

// Drizzle ORM
import { eq, and } from 'drizzle-orm';
import { db } from './db';
import { users } from './schema';

const username = req.body.username;
const password = req.body.password;

const result = await db
  .select()
  .from(users)
  .where(and(
    eq(users.username, username),
    eq(users.password, password)
  ));

if (result.length > 0) {
  // Login successful
} else {
  // Login failed
}

ORM

Provides a way to map database tables to Classes in code and rows to Object instances. Typically, ORM also handles differences between different databases and SQL Injection issues.

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

const username = req.body.username;
const password = req.body.password;

const user = await prisma.user.findFirst({
  where: {
    username,
    password,
  },
});

if (user) {
  // Login successful
} else {
  // Login failed
}

My Learning Experience

In the Node.js ecosystem, I used MySQL2🔗 to communicate with the local database, but the lack of TypeScript types for the returned SQL data is a major issue. This issue is usually addressed through ORM frameworks that generate TypeScript types via predefined Schemas.

I found that transitioning between ORMs is troublesome and challenging, as one must understand the abstractions of the ORM while also being able to grasp the underlying SQL when necessary, which can be quite frustrating. Therefore, I prefer to use SQL Builder, which has minimal abstraction, to familiarize myself with SQL syntax and enjoy the convenience it offers. In the TypeScript ecosystem, I recommend Drizzle ORM🔗 for a comprehensive SQL Builder ~ ORM experience.

Summary

Further Reading

• Raw SQL, SQL Query Builder, or ORM? - ArjanCodes🔗
• Comparing Prisma ORM - Prisma/docs🔗