Skip to main content

Query Builders: Knex.js

In the previous article, you wrote raw SQL strings inside TypeScript. It works, but it has pain points: typos in column names aren't caught until runtime, building dynamic queries (optional filters, variable sort order) gets messy, and there's no built-in way to version your schema changes.

Knex.js is a query builder — a layer above the raw pg driver that lets you compose SQL using a JavaScript API. It generates the same parameterized SQL pg would run, but provides:

  • A chainable API for composing queries
  • A migration system to version schema changes
  • A seed system to populate development data
  • Multi-database support (same API works for PostgreSQL, MySQL, SQLite)

Knex is not an ORM. It doesn't define models, doesn't manage relationships, and doesn't hide the fact that you're working with SQL. That transparency is a feature — you stay in control of what queries run.

Quick Reference​

Install:

npm install knex pg
npm install --save-dev @types/knex
npx knex init  # generate knexfile.ts

Query:

const products = await knex("products")
  .select("id", "name", "price")
  .where("category", "Electronics")
  .where("price", "<", 100)
  .orderBy("price", "asc");

Insert with returning:

const [product] = await knex("products")
  .insert({
    name: "New Item",
    price: 49.99,
    category: "Accessories",
    stock_quantity: 10,
  })
  .returning("*");

Migration:

npx knex migrate:make create_products_table
npx knex migrate:latest
npx knex migrate:rollback

Gotchas:

  • ⚠️ Knex is NOT an ORM — it doesn't know about your table relationships
  • ⚠️ .where('column', value) is an equality check; .where('column', '>', value) is a comparison
  • ⚠️ Always await Knex queries — they return promises, not results
  • ⚠️ knex.raw() still requires parameterized input: knex.raw('WHERE id = ?', [id])

Version Information​

Tested with:

  • knex: ^3.1.0
  • pg: ^8.11.0
  • Node.js: v18.x, v20.x, v22.x
  • TypeScript: 5.x

Last verified: May 2025

What You Need to Know First​

Required reading:

What We'll Cover in This Article​

By the end of this guide, you'll understand:

  • How to install and configure Knex with PostgreSQL
  • How to write CRUD queries using the Knex query builder API
  • How to build dynamic queries with optional conditions
  • How to create and run database migrations
  • How to write seed files for development data
  • How to implement transactions with Knex
  • When Knex is the right tool — and when to use raw SQL instead

What We'll Explain Along the Way​

We'll introduce these concepts with full explanations:

  • What a migration is and why you need schema versioning
  • How Knex tracks which migrations have been run
  • The knexfile.ts configuration and environment-specific settings
  • When to use knex.raw() for complex queries

Part 1: Installation and Configuration​

npm install knex pg
npm install --save-dev @types/pg

Note: Knex ships with its own TypeScript types (@types/knex is not needed for Knex v3+).

knexfile.ts — The Configuration File​

Create knexfile.ts in your project root:

// knexfile.ts
import type { Knex } from "knex";

const config: { [key: string]: Knex.Config } = {
  development: {
    client: "postgresql",
    connection: process.env.DATABASE_URL,
    pool: {
      min: 2,
      max: 10,
    },
    migrations: {
      directory: "./migrations",
      extension: "ts",
    },
    seeds: {
      directory: "./seeds",
      extension: "ts",
    },
  },

  production: {
    client: "postgresql",
    connection: {
      connectionString: process.env.DATABASE_URL,
      ssl: { rejectUnauthorized: false }, // required by most cloud PostgreSQL providers
    },
    pool: {
      min: 2,
      max: 20,
    },
    migrations: {
      directory: "./migrations",
    },
  },
};

export default config;

The Knex Instance — Your Database Client​

Create a shared Knex instance, just like you created a Pool in the previous article:

// src/db.ts
import "dotenv/config";
import knex from "knex";
import config from "../knexfile.js";

const env = process.env.NODE_ENV ?? "development";

export const db = knex(config[env]);

Import db wherever you need to run queries. Create it once, use it everywhere.

Part 2: Querying with Knex​

The Knex API is chainable — you start with a table, add conditions, and eventually await the result:

SELECT​

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

// Select all columns
const products = await db("products");

// Select specific columns
const products = await db("products").select("id", "name", "price");

// With WHERE
const electronics = await db("products")
  .select("id", "name", "price")
  .where("category", "Electronics"); // equality: WHERE category = 'Electronics'

// Multiple conditions (AND by default)
const affordableElectronics = await db("products")
  .select("id", "name", "price")
  .where("category", "Electronics")
  .where("price", "<", 100) // range: WHERE price < 100
  .where("stock_quantity", ">", 0);

// OR conditions
const results = await db("products")
  .where("category", "Electronics")
  .orWhere("category", "Accessories");

// ORDER BY
const sorted = await db("products")
  .select("id", "name", "price")
  .orderBy("price", "asc");

// LIMIT and OFFSET
const page2 = await db("products")
  .select("*")
  .orderBy("name")
  .limit(10)
  .offset(10);

// Single row
const product = await db("products").where("id", 3).first(); // Returns first row object, or undefined — not an array

What Knex Actually Generates​

Knex isn't magic — it generates SQL you could have written yourself. You can log the generated SQL:

// Log the SQL before executing
const query = db("products")
  .select("id", "name", "price")
  .where("category", "Electronics")
  .where("price", "<", 100);

console.log(query.toSQL().toNative());
// {
//   sql: 'select "id", "name", "price" from "products"
//         where "category" = $1 and "price" < $2',
//   bindings: ['Electronics', 100]
// }

Notice: Knex generates a parameterized query. The values are always in bindings, never concatenated into the SQL string.

JOIN​

// INNER JOIN
const orderSummaries = await db("orders")
  .select("users.name AS customer", "orders.id AS order_id", "orders.status")
  .join("users", "orders.user_id", "=", "users.id")
  .orderBy("orders.id");

// LEFT JOIN
const usersWithOrders = await db("users")
  .select("users.name", db.raw("COUNT(orders.id) AS order_count"))
  .leftJoin("orders", "orders.user_id", "users.id")
  .groupBy("users.id", "users.name")
  .orderBy("order_count", "desc");

Dynamic Queries — The Knex Advantage​

This is where Knex pulls ahead of raw SQL strings. Building a query with optional filters in raw SQL requires messy string manipulation:

// ✅ Dynamic query with Knex — clean and readable
async function searchProducts(filters: {
  category?: string;
  minPrice?: number;
  maxPrice?: number;
  inStockOnly?: boolean;
  sortBy?: "price" | "name";
  sortDir?: "asc" | "desc";
  page?: number;
  pageSize?: number;
}) {
  const {
    category,
    minPrice,
    maxPrice,
    inStockOnly,
    sortBy = "name",
    sortDir = "asc",
    page = 1,
    pageSize = 20,
  } = filters;

  const query = db("products").select(
    "id",
    "name",
    "price",
    "stock_quantity",
    "category",
  );

  // Each condition is only added if the filter was provided
  if (category) {
    query.where("category", category);
  }
  if (minPrice !== undefined) {
    query.where("price", ">=", minPrice);
  }
  if (maxPrice !== undefined) {
    query.where("price", "<=", maxPrice);
  }
  if (inStockOnly) {
    query.where("stock_quantity", ">", 0);
  }

  query.orderBy(sortBy, sortDir);
  query.limit(pageSize).offset((page - 1) * pageSize);

  return query; // await happens here
}

// Usage
const results = await searchProducts({
  category: "Electronics",
  inStockOnly: true,
});

Doing this with raw SQL would require building a conditions array and joining it — more code, more error-prone. Knex's chainable API handles it cleanly.

INSERT​

// Single insert — returns number of affected rows by default in Knex
await db("products").insert({
  name: "Standing Desk Mat",
  description: "Anti-fatigue mat, 90x60cm",
  price: 79.99,
  stock_quantity: 50,
  category: "Accessories",
});

// With RETURNING (PostgreSQL-specific)
const [newProduct] = await db("products")
  .insert({
    name: "Standing Desk Mat",
    price: 79.99,
    category: "Accessories",
    stock_quantity: 50,
  })
  .returning("*");

console.log(newProduct.id); // The generated ID

// Multi-row insert
await db("products").insert([
  {
    name: "Item A",
    price: 19.99,
    category: "Accessories",
    stock_quantity: 100,
  },
  { name: "Item B", price: 29.99, category: "Electronics", stock_quantity: 50 },
]);

UPDATE​

// Update with returning
const [updated] = await db("products")
  .where("id", 3)
  .update({ price: 44.99 })
  .returning(["id", "name", "price"]);

// Update multiple columns
await db("products")
  .where("category", "Electronics")
  .update({
    price: db.raw("price * 0.9"), // 10% discount
  });

DELETE​

const deletedCount = await db("products").where("stock_quantity", 0).delete();

console.log(`Deleted ${deletedCount} out-of-stock products`);

Part 3: Migrations — Versioning Your Schema​

A migration is a versioned, reversible schema change. Instead of manually running ALTER TABLE commands (which don't get tracked anywhere), migrations are committed to source control and run in order on every environment.

Knex tracks which migrations have been run in a knex_migrations table it creates automatically.

Creating a Migration​

npx knex migrate:make create_shopflow_schema --knexfile=knexfile.ts

This creates a timestamped file like migrations/20240315143022_create_shopflow_schema.ts.

Each migration has an up function (apply the change) and a down function (reverse it):

// migrations/20240315143022_create_shopflow_schema.ts
import type { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  // Create users
  await knex.schema.createTable("users", (table) => {
    table.increments("id").primary(); // SERIAL PRIMARY KEY
    table.text("name").notNullable();
    table.text("email").notNullable().unique();
    table
      .timestamp("created_at", { useTz: true })
      .notNullable()
      .defaultTo(knex.fn.now());
  });

  // Create products
  await knex.schema.createTable("products", (table) => {
    table.increments("id").primary();
    table.text("name").notNullable();
    table.text("description"); // nullable — no .notNullable()
    table.decimal("price", 10, 2).notNullable().checkPositive();
    table.integer("stock_quantity").notNullable().defaultTo(0);
    table.text("category").notNullable();
    table
      .timestamp("created_at", { useTz: true })
      .notNullable()
      .defaultTo(knex.fn.now());
  });

  // Create orders
  await knex.schema.createTable("orders", (table) => {
    table.increments("id").primary();
    table
      .integer("user_id")
      .notNullable()
      .references("id")
      .inTable("users")
      .onDelete("RESTRICT");
    table.text("status").notNullable().defaultTo("pending");
    table
      .timestamp("created_at", { useTz: true })
      .notNullable()
      .defaultTo(knex.fn.now());
  });

  // Create order_items
  await knex.schema.createTable("order_items", (table) => {
    table.increments("id").primary();
    table
      .integer("order_id")
      .notNullable()
      .references("id")
      .inTable("orders")
      .onDelete("CASCADE");
    table
      .integer("product_id")
      .notNullable()
      .references("id")
      .inTable("products")
      .onDelete("RESTRICT");
    table.integer("quantity").notNullable();
    table.decimal("price_at_purchase", 10, 2).notNullable();
    table.unique(["order_id", "product_id"]);
  });

  // Add indexes
  await knex.schema.table("orders", (table) => {
    table.index("user_id", "idx_orders_user_id");
    table.index("status", "idx_orders_status");
  });

  await knex.schema.table("order_items", (table) => {
    table.index("order_id", "idx_order_items_order_id");
    table.index("product_id", "idx_order_items_product_id");
  });
}

export async function down(knex: Knex): Promise<void> {
  // Drop in reverse order (children before parents)
  await knex.schema.dropTableIfExists("order_items");
  await knex.schema.dropTableIfExists("orders");
  await knex.schema.dropTableIfExists("products");
  await knex.schema.dropTableIfExists("users");
}

Running Migrations​

# Apply all pending migrations
npx knex migrate:latest --knexfile=knexfile.ts

# Roll back the most recent migration batch
npx knex migrate:rollback --knexfile=knexfile.ts

# Roll back all migrations
npx knex migrate:rollback --all --knexfile=knexfile.ts

# Check migration status
npx knex migrate:status --knexfile=knexfile.ts

Adding Columns — Incremental Migrations​

Schema changes after the initial migration are separate files, never edits to the original migration:

npx knex migrate:make add_sku_to_products --knexfile=knexfile.ts
// migrations/20240320091500_add_sku_to_products.ts
import type { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  await knex.schema.alterTable("products", (table) => {
    table.text("sku").unique().nullable();
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.alterTable("products", (table) => {
    table.dropColumn("sku");
  });
}

Part 4: Seeds — Development Data​

Seeds populate your database with sample data for development and testing. Unlike migrations, seeds are idempotent — you can run them multiple times without duplicating data (when written correctly).

npx knex seed:make 001_users --knexfile=knexfile.ts
// seeds/001_users.ts
import type { Knex } from "knex";

export async function seed(knex: Knex): Promise<void> {
  // Delete existing data first (in child-to-parent order)
  await knex("order_items").del();
  await knex("orders").del();
  await knex("products").del();
  await knex("users").del();

  // Insert users
  await knex("users").insert([
    {
      name: "Aisha Mwangi",
      email: "aisha@example.com",
      created_at: "2024-01-15 09:23:00+00",
    },
    {
      name: "Marco Rossi",
      email: "marco@example.com",
      created_at: "2024-01-16 14:05:00+00",
    },
    {
      name: "Priya Sharma",
      email: "priya@example.com",
      created_at: "2024-01-17 11:30:00+00",
    },
    {
      name: "Carlos Rivera",
      email: "carlos@example.com",
      created_at: "2024-02-01 08:00:00+00",
    },
    {
      name: "Lin Wei",
      email: "lin@example.com",
      created_at: "2024-02-14 16:45:00+00",
    },
  ]);

  // Insert products
  await knex("products").insert([
    {
      name: "Wireless Keyboard",
      price: 89.99,
      stock_quantity: 45,
      category: "Electronics",
    },
    {
      name: "Mechanical Mouse",
      price: 64.99,
      stock_quantity: 30,
      category: "Electronics",
    },
    {
      name: "Laptop Stand",
      price: 49.99,
      stock_quantity: 0,
      category: "Accessories",
    },
    {
      name: "USB-C Hub",
      price: 39.99,
      stock_quantity: 120,
      category: "Electronics",
    },
  ]);
}
# Run all seeds
npx knex seed:run --knexfile=knexfile.ts

# Run specific seed
npx knex seed:run --specific=001_users.ts --knexfile=knexfile.ts

Part 5: Transactions with Knex​

async function placeOrder(
  userId: number,
  items: Array<{ productId: number; quantity: number; price: number }>,
): Promise<number> {
  return db.transaction(async (trx) => {
    // trx is a transaction-scoped Knex instance
    // All queries on trx are part of the same transaction

    // Insert order
    const [order] = await trx("orders")
      .insert({ user_id: userId, status: "pending" })
      .returning("id");

    const orderId = order.id;

    // Insert items and update stock
    for (const item of items) {
      await trx("order_items").insert({
        order_id: orderId,
        product_id: item.productId,
        quantity: item.quantity,
        price_at_purchase: item.price,
      });

      const updated = await trx("products")
        .where("id", item.productId)
        .where("stock_quantity", ">=", item.quantity)
        .decrement("stock_quantity", item.quantity);

      if (updated === 0) {
        // Throwing inside db.transaction() automatically triggers ROLLBACK
        throw new Error(`Insufficient stock for product ${item.productId}`);
      }
    }

    // If we reach here without throwing, Knex commits the transaction
    return orderId;
  });
}

db.transaction(async (trx) => { ... }) handles BEGIN, COMMIT, and ROLLBACK automatically:

  • If the callback resolves successfully → COMMIT
  • If the callback throws → ROLLBACK

You don't need try/catch inside the callback for the transaction itself — throwing is the signal to roll back.

Part 6: When to Use Raw SQL​

Knex's API covers most queries. But complex SQL — window functions, CTEs, complex aggregations — is sometimes easier to write as raw SQL:

// knex.raw() for complex queries
const result = await db.raw(`
  WITH order_totals AS (
    SELECT
      order_id,
      SUM(price_at_purchase * quantity) AS total
    FROM order_items
    GROUP BY order_id
  )
  SELECT
    u.name,
    COUNT(o.id)  AS order_count,
    SUM(ot.total) AS lifetime_value
  FROM users u
  JOIN orders o ON o.user_id = u.id
  JOIN order_totals ot ON ot.order_id = o.id
  GROUP BY u.id, u.name
  ORDER BY lifetime_value DESC
`);

const rows = result.rows;

// knex.raw() with parameters (use ? placeholders, not $1 — Knex handles the mapping)
const { rows } = await db.raw(
  "SELECT * FROM products WHERE category = ? AND price < ?",
  ["Electronics", 100],
);

knex.raw() still goes through pg — it's still parameterized, still safe. The ? placeholders are Knex's syntax; they get mapped to $1, $2, etc. before hitting PostgreSQL.

Common Misconceptions​

❌ Misconception: Knex is an ORM​

Reality: Knex is a query builder and migration tool. It has no concept of models, relationships, or the active record/data mapper patterns that define ORMs. It generates SQL and gives you the raw row objects back.

Practical implication: If you want to fetch a user's orders from Knex, you write a query. There's no user.orders() method — you join, or make a second query, manually.

❌ Misconception: .where('column', value) handles all comparison types​

Reality: .where('column', value) generates WHERE column = value (equality only). For other comparisons:

.where('price', '>', 50)      // WHERE price > 50
.where('price', '>=', 50)     // WHERE price >= 50
.whereBetween('price', [40, 80])  // WHERE price BETWEEN 40 AND 80
.whereIn('category', ['Electronics', 'Accessories'])  // WHERE category IN (...)
.whereNull('description')     // WHERE description IS NULL
.whereNotNull('description')  // WHERE description IS NOT NULL

❌ Misconception: Knex migrations auto-detect schema differences​

Reality: Knex migrations are explicit — you write the up and down functions manually. Knex doesn't compare your current schema to the desired state and generate SQL automatically (that's closer to what Prisma does). You write every createTable, addColumn, and createIndex yourself.

Troubleshooting Common Issues​

Problem: Migration file already exists​

Symptoms: npx knex migrate:make fails with this error.

Cause: Two migrations were created in the same second (timestamp collision). Rare, but possible.

Solution: Add a suffix to distinguish them, or wait a second between migrate:make calls.

Problem: Migration ran but down doesn't restore the original state​

Symptoms: After migrate:rollback, the database isn't what you expected.

Cause: The down function is incomplete or out of order.

Prevention:

// Always test rollback during development
npx knex migrate:rollback && npx knex migrate:latest

Problem: knex.raw() query returns unexpected results​

Symptoms: The raw query looks correct but returns wrong data.

Cause: Knex wraps raw() results differently from regular queries — the result is the full pg result object.

// Access rows correctly from raw()
const result = await db.raw("SELECT * FROM products");
const rows = result.rows; // NOT result.rows[0]

Check Your Understanding​

Quick Quiz​

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

    Show Answer

    Knex is a query builder — it helps you write SQL using a JavaScript API and generates parameterized queries, but it has no knowledge of your table relationships, doesn't define models, and returns raw row objects. An ORM like Prisma defines models with relationships, generates a type-safe client, and handles relation loading (eager loading, lazy loading) automatically. Knex gives you more control; Prisma gives you more abstraction.

  2. Why should you never edit an existing migration file once it's been run?

    Show Answer

    Knex tracks which migrations have been run by their filename and checksum. Editing a migration that's already run means other developers (and production) won't re-run it — they'll have a different schema than what the file describes. Always create a new migration for any schema change, even small ones.

  3. How does db.transaction() handle rollback?

    Show Answer

    If the async callback passed to db.transaction() throws an error (or returns a rejected promise), Knex automatically runs ROLLBACK before re-throwing the error. If the callback resolves successfully, Knex runs COMMIT. You don't need to manually call ROLLBACK — just throw an error to abort.

Hands-On Challenge​

Task: Write a Knex migration and a query function.

Migration: Add a discount_percent column to products (nullable, DECIMAL(5,2), with a CHECK that it's between 0 and 100).

Query function: getDiscountedProducts() — returns all products with a non-null discount_percent, including a computed discounted_price column, sorted by discount percentage descending.

Show Solution
// migrations/TIMESTAMP_add_discount_to_products.ts
import type { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  await knex.schema.alterTable("products", (table) => {
    table.decimal("discount_percent", 5, 2).nullable();
  });

  // CHECK constraint via raw (Knex schema builder doesn't expose named checks easily)
  await knex.raw(`
    ALTER TABLE products
    ADD CONSTRAINT check_discount_range
    CHECK (discount_percent IS NULL OR (discount_percent >= 0 AND discount_percent <= 100))
  `);
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.alterTable("products", (table) => {
    table.dropColumn("discount_percent");
  });
}
// Query function
interface DiscountedProduct {
  id: number;
  name: string;
  price: string;
  discount_percent: string;
  discounted_price: string;
}

async function getDiscountedProducts(): Promise<DiscountedProduct[]> {
  const rows = await db("products")
    .select(
      "id",
      "name",
      "price",
      "discount_percent",
      db.raw(
        "ROUND(price * (1 - discount_percent / 100), 2) AS discounted_price",
      ),
    )
    .whereNotNull("discount_percent")
    .orderBy("discount_percent", "desc");

  return rows as DiscountedProduct[];
}

Summary: Key Takeaways​

  • Knex is a query builder and migration tool — not an ORM; it generates SQL but doesn't know about relationships
  • Install knex and pg; configure via knexfile.ts with environment-specific settings
  • The chainable API builds queries: db('table').select(...).where(...).orderBy(...)
  • .toSQL().toNative() shows the generated SQL — useful for debugging
  • Knex excels at dynamic queries — adding conditions based on optional input is clean and readable
  • RETURNING works on insert().returning(...), update().returning(...), and delete().returning(...)
  • Migrations version your schema changes: create with migrate:make, apply with migrate:latest, reverse with migrate:rollback
  • Never edit an existing migration — always create a new one for schema changes
  • Seeds populate development data; run with seed:run
  • db.transaction(async (trx) => { ... }) — throw to rollback, return to commit
  • Use knex.raw() for complex SQL that the builder can't express cleanly

What's Next?​

You've seen raw pg (full control, verbose) and Knex (SQL-transparent, with migrations). The next step up the abstraction ladder is a full ORM.

The next step is Drizzle ORM: SQL Without the Magic — a TypeScript-first ORM that keeps SQL front-and-center while giving you end-to-end type safety, schema ownership, and a migration workflow that generates real SQL files you can inspect. We'll rebuild the ShopFlow data layer with Drizzle and compare it directly to what you just built with Knex.