Stop 08 · ORMs & Query BuildersTopics · 6/10

Knex.js

In short

Not every project wants a full ORM. Knex is a query builder: you build SQL with chainable JavaScript methods, stay close to real SQL, and still get migrations.

A flexible SQL query builder (not a full ORM) that lets you construct SQL queries programmatically while staying close to raw SQL — a middle ground between hand-written SQL and a full ORM.

The blob snaps colourful toy bricks together into a long chain.

Think of it like this

Snap-together building blocks

Building blocks let you make anything one piece at a time, and you can always see each piece. Knex works like that: select, where, join and orderBy snap together into one SQL query. No magic objects, no hidden queries, just SQL built from blocks.

Watch it happen

Building one query

table

join

where

orderBy

toSQL

1/5db('orders') starts a query on the orders table.

The big ideas

What you need to know

Chainable builder

Each method adds a piece to the query. Await the chain and Knex runs it.

Bindings by default

Values become ? or $1 placeholders, so user input can't turn into SQL.

Migrations and seeds

knex migrate:make, migrate:latest and seed:run cover schema changes and test data.

No hidden magic

Results are plain objects. No models, no lazy loading, no surprise extra queries.

See it in code

Walk through the code

1import knex from "knex";2 3const db = knex({ client: "pg", connection: process.env.DATABASE_URL });4 5const bigOrders = await db("orders")6  .join("users", "users.id", "orders.user_id")7  .where("orders.status", "paid")8  .andWhere("orders.total", ">", 500)9  .select("orders.id", "users.name", "orders.total")10  .orderBy("orders.total", "desc")11  .limit(10);12 13console.log(db("orders").where("id", 42).toSQL().toNative());14// { sql: 'select * from "orders" where "id" = $1', bindings: [42] }

1/3Create one Knex instance for the whole app. client picks the SQL dialect.

Install knex plus a driver like pg. Knex is also the engine under Objection.js, if you later want models on top.

Save yourself the pain

Common mistakes

Oops

Using db.raw with values glued into the string.

Do this instead

Pass values as bindings, like db.raw('select * from users where id = ?', [id]), so they're handled safely.

Oops

Creating a new knex() instance for every request.

Do this instead

Each instance has its own connection pool. Create one at startup and share it.

Your mission

Rewrite with Knex

~25 min

Quick check

What's the main difference between Knex and an ORM like Prisma?

Go deeper

1Build the same query first in raw SQL, then with Knex's query builder.
2Learn when a query builder's extra control is worth it over a full ORM.
3Practice Knex migrations for schema changes.