ORMs & Query Builders

In Connecting to MySQL you wrote SQL by hand with mysql2. That is a good foundation, and many fast, reliable apps work that way. But when you join a team, you will often find one of two extra layers on top of the database driver: a query builder or an ORM. Job descriptions mention them by name (Prisma, Sequelize, TypeORM, Drizzle, Knex), so you need to know what they are, what they are good at and where they hurt.

This lesson runs real code with Knex, a popular query builder, against an in-memory SQLite database (so you need no server), then shows how the same ideas look in Drizzle and Prisma. Everything you learn applies to MySQL and PostgreSQL too.

Three ways to talk to a database

ORM means Object-Relational Mapper: it maps rows in relational tables to objects in your code. For MongoDB the equivalent is called an ODM (Object-Document Mapper), and Mongoose, from the MongoDB lesson, is one.

None of these replaces knowing SQL. When a page is slow, you will read the SQL that the library produced, so keep your SQL skills sharp.

A query builder: Knex

A query builder lets you build SQL with method calls. Each call adds a piece, and values always travel as bindings (placeholders), so SQL injection is prevented by default. You can see the SQL without even connecting:

import knex from "knex";

// No connection needed just to build SQL
const db = knex({ client: "mysql2" });

const query = db("orders")
  .select("id", "customer", "total")
  .where("city", "Pune")
  .andWhere("total", ">=", 500)
  .orderBy("total", "desc")
  .limit(10);

console.log(query.toString());
console.log(query.toSQL().sql);
console.log(query.toSQL().bindings);

select `id`, `customer`, `total` from `orders` where `city` = 'Pune' and `total` >= 500 order by `total` desc limit 10
select `id`, `customer`, `total` from `orders` where `city` = ? and `total` >= ? order by `total` desc limit ?
[ 'Pune', 500, 10 ]

toString() shows the SQL with values filled in, for reading only. What is really sent is the second line plus the bindings, exactly like the safe placeholders you wrote by hand. The big win appears with optional filters: a product search where the user may or may not choose a city, a price range and a sort order. Adding .where() calls inside if statements is far cleaner than gluing SQL strings together.

Tables, inserts and joins

In a real app you create one shared Knex instance, just like a connection pool:

// db.js: one shared Knex instance for the whole app
import knex from "knex";

export const db = knex({
  client: "better-sqlite3",          // for MySQL: client "mysql2" and a connection object
  connection: { filename: ":memory:" },
  useNullAsDefault: true,            // needed for SQLite only
});

To try it: npm install knex better-sqlite3. For MySQL you would install mysql2 instead and pass { host, user, password, database } as connection; the queries below stay the same.

import { db } from "./db.js";

await db.schema.createTable("customers", (t) => {
  t.increments("id");
  t.string("name", 60).notNullable();
  t.string("city", 40).notNullable();
});
await db.schema.createTable("orders", (t) => {
  t.increments("id");
  t.integer("customer_id").notNullable().references("id").inTable("customers");
  t.integer("total").notNullable();
  t.timestamp("created_at").defaultTo(db.fn.now());
});

await db("customers").insert([
  { name: "Priya", city: "Jaipur" },
  { name: "Rohan", city: "Rohtak" },
  { name: "Isha", city: "Pune" },
]);
await db("orders").insert([
  { customer_id: 1, total: 1299 },
  { customer_id: 1, total: 450 },
  { customer_id: 3, total: 2100 },
]);

const rows = await db("orders as o")
  .join("customers as c", "c.id", "o.customer_id")
  .select("o.id", "c.name", "c.city", "o.total")
  .where("o.total", ">", 400)
  .orderBy("o.id");
console.table(rows);

const perCustomer = await db("orders")
  .select("customer_id")
  .count({ orders: "*" })
  .sum({ spent: "total" })
  .groupBy("customer_id");
console.log(perCustomer);

await db.destroy();

┌─────────┬────┬─────────┬──────────┬───────┐
│ (index) │ id │ name    │ city     │ total │
├─────────┼────┼─────────┼──────────┼───────┤
│ 0       │ 1  │ 'Priya' │ 'Jaipur' │ 1299  │
│ 1       │ 2  │ 'Priya' │ 'Jaipur' │ 450   │
│ 2       │ 3  │ 'Isha'  │ 'Pune'   │ 2100  │
└─────────┴────┴─────────┴──────────┴───────┘
[
  { customer_id: 1, orders: 2, spent: 1749 },
  { customer_id: 3, orders: 1, spent: 2100 }
]

db.destroy() closes the connections so a script can exit; a server calls it only during shutdown.

Migrations

Creating tables inside app code is fine for a demo. Real teams use migrations: numbered files that each make one change to the database structure (add a table, add a column) and can be undone. Every developer and every server runs the same files in the same order, so all databases match. With Knex:

npx knex init                         # creates knexfile.js with connection settings
npx knex migrate:make create_orders   # creates migrations/<timestamp>_create_orders.js
npx knex migrate:latest               # runs every migration not yet applied
npx knex migrate:rollback             # undoes the last batch

Each migration file exports an up function (make the change) and a down function (undo it). Commit migrations to Git with the code that needs them. Every ORM below has the same idea under its own commands.

Transactions

A transaction groups several queries so that either all of them happen or none do. Placing an order must insert the order and reduce stock; if stock is short, neither change should stay. In Knex, db.transaction() commits when your function finishes and rolls back if it throws:

import { db } from "./db.js";

await db.schema.createTable("products", (t) => {
  t.string("sku").primary();
  t.integer("stock").notNullable();
});
await db.schema.createTable("orders", (t) => {
  t.increments("id");
  t.string("sku").notNullable();
  t.integer("qty").notNullable();
});
await db("products").insert({ sku: "BAT-KASHMIR", stock: 3 });

async function placeOrder(sku, qty) {
  return db.transaction(async (trx) => {
    const [id] = await trx("orders").insert({ sku, qty });
    const updated = await trx("products")
      .where("sku", sku)
      .andWhere("stock", ">=", qty)
      .decrement("stock", qty);
    if (updated === 0) throw new Error(`Not enough stock for ${sku}`);
    return id;
  }); // commit if the function finishes, rollback if it throws
}

console.log("order", await placeOrder("BAT-KASHMIR", 2));
try {
  await placeOrder("BAT-KASHMIR", 5);
} catch (err) {
  console.log(err.message);
}
console.log(await db("products").first());
console.log("orders saved:", (await db("orders").count({ n: "*" }))[0].n);
await db.destroy();

order 1
Not enough stock for BAT-KASHMIR
{ sku: 'BAT-KASHMIR', stock: 1 }
orders saved: 1

The second order was inserted, then rolled back, so only one order exists. Inside the transaction, every query must use trx, not db; a query on db would run outside the transaction. The stock check is inside the UPDATE itself (stock >= qty), so two customers buying the last bat at the same moment cannot both succeed. (On SQLite and MySQL, insert returns the new ID in an array; PostgreSQL needs .returning("id").)

The N+1 problem

The most common performance bug with any database library is the N+1 problem: one query to get a list, then one extra query for each item in the list. ORMs make it easy to write by accident because loading a related object looks like reading a property. Knex can count the queries it sends:

let queries = 0;
db.on("query", () => queries++);

// N+1: one query for orders, then one more for EACH order's customer
const orders = await db("orders").select("id", "customer_id", "total");
for (const o of orders) {
  o.customer = (await db("customers").where("id", o.customer_id).first()).name;
}
console.log(`N+1 version: ${orders.length} orders, ${queries} queries`);

// Better: one query with a JOIN
queries = 0;
const joined = await db("orders as o")
  .join("customers as c", "c.id", "o.customer_id")
  .select("o.id", "o.total", "c.name as customer");
console.log(`JOIN version: ${joined.length} orders, ${queries} query`);
console.log(joined[0], joined[0].customer === orders[0].customer);

N+1 version: 50 orders, 51 queries
JOIN version: 50 orders, 1 query
{ id: 1, total: 100, customer: 'Aarav' } true

Same answer, 51 round trips against 1. On a real network, each round trip costs a few milliseconds, so a page with 500 orders becomes seconds slower. Fix it with a join, or with an ORM's "include"/"eager loading" option, or with one whereIn("id", ids) query for all related rows.

ORMs: Drizzle and Prisma

Drizzle describes tables in JavaScript or TypeScript and gives you SQL-like methods with full type checking. Its query style stays close to SQL. This runs on SQLite (npm install drizzle-orm better-sqlite3):

import Database from "better-sqlite3";
import { drizzle } from "drizzle-orm/better-sqlite3";
import { sqliteTable, integer, text } from "drizzle-orm/sqlite-core";
import { eq, gt, and } from "drizzle-orm";

// schema.js: tables described in JavaScript
const customers = sqliteTable("customers", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name").notNull(),
  city: text("city").notNull(),
});

const sqlite = new Database(":memory:");
sqlite.exec("CREATE TABLE customers (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, city TEXT NOT NULL)");
const db = drizzle(sqlite);

await db.insert(customers).values([
  { name: "Meera", city: "Kochi" },
  { name: "Farhan", city: "Lucknow" },
  { name: "Ananya", city: "Kochi" },
]);

const fromKochi = await db.select().from(customers)
  .where(and(eq(customers.city, "Kochi"), gt(customers.id, 1)));
console.log(fromKochi);

[ { id: 3, name: 'Ananya', city: 'Kochi' } ]

For MySQL you would import mysqlTable from drizzle-orm/mysql-core instead; its drizzle-kit tool creates migrations from the table definitions.

Prisma takes a different route. You describe models in a separate schema.prisma file, run a command that generates a client, and then query with plain objects, for example prisma.order.findMany({ where: { total: { gt: 400 } }, include: { customer: true } }). The include option loads related rows without an N+1 loop. Prisma is very popular with TypeScript teams; its setup steps changed in recent major versions, so follow the official getting-started guide for the version you install.

Common mistakes

• Learning an ORM instead of SQL. You still need SQL to design tables, add indexes and read slow queries.
• N+1 loops. Watch for await inside a loop that runs a query; use joins, eager loading or whereIn.
• Using db instead of trx inside a transaction. That query is not part of the transaction.
• Changing tables by hand on the server. Use migrations so every environment matches.
• Using raw-string helpers with user input. Knex's db.raw() and similar features accept placeholders; never build them with template strings.

Key points

• Raw SQL drivers, query builders and ORMs all sit on top of SQL; pick by project size and team habits.
• Knex builds SQL from method chains and always sends values as bindings, which prevents SQL injection.
• Migrations version your database structure; run them the same way everywhere.
• db.transaction() commits on success and rolls back on any thrown error; use trx for every query inside.
• The N+1 problem multiplies queries; count them and fix with joins or eager loading.

function searchCustomers({ city, nameStartsWith, limit = 20 } = {}) {
  const q = db("customers").select("id", "name", "city");
  if (city) q.where("city", city);
  if (nameStartsWith) q.whereLike("name", `${nameStartsWith}%`);
  return q.orderBy("name").limit(limit);
}

console.log(searchCustomers({ city: "Kochi" }).toString());
console.log(searchCustomers({ nameStartsWith: "A", limit: 5 }).toString());

select `id`, `name`, `city` from `customers` where `city` = 'Kochi' order by `name` asc limit 20
select `id`, `name`, `city` from `customers` where `name` like 'A%' order by `name` asc limit 5