Testing Knex Components: From Unit to E2E Tests

Testing Knex Components: From Unit to E2E Tests

Knex.js is one of the most popular SQL query builders for Node.js, acting as a layer above raw SQL while remaining flexible enough to support complex queries, migrations, and transactions. But like any data-access tool, Knex code can become a source of subtle bugs if left untested. A misplaced where clause, a forgotten join, or a transaction that never rolls back can quietly corrupt data over time. This tutorial walks through a complete testing strategy for Knex-based components, from pure unit tests to full end-to-end (E2E) flows.

Why Testing Knex Components Matters

Knex sits between your application logic and your database. That means bugs in Knex code often manifest as incorrect data rather than crashes — and incorrect data is far harder to detect. A solid test suite gives you confidence that:

• Queries return the expected rows for known inputs.
• Migrations produce the schema you think they do.
• Transactions roll back correctly on failure.
• Seed data is deterministic and reproducible.
• Refactors of query logic don't silently change behavior.

Without tests, you're left manually poking at a database every time you change a query — which is slow, error-prone, and rarely thorough.

The Testing Pyramid for Knex

Not every test needs a real database. A practical Knex testing strategy follows the classic pyramid:

• Unit tests — verify query construction logic without touching a database.
• Integration tests — run real queries against a disposable database (often an in-memory SQLite or a Dockerized Postgres).
• E2E tests — exercise entire application flows that depend on Knex, including API endpoints, services, and the database together.

Each layer catches different classes of bugs, and together they form a safety net that is both fast and thorough.

Setting Up the Project

For this tutorial, assume a typical Node.js project with Knex installed alongside a test runner. We'll use Jest, but the patterns apply equally to Mocha, Vitest, or Node's built-in test runner.

npm install knex jest supertest --save-dev
npm install pg sqlite3

A simple Knex configuration file might look like this:

// knexfile.js
module.exports = {
  development: {
    client: 'pg',
    connection: process.env.DATABASE_URL,
    migrations: { directory: './migrations' },
    seeds: { directory: './seeds' },
  },
  test: {
    client: 'sqlite3',
    connection: ':memory:',
    useNullAsDefault: true,
    migrations: { directory: './migrations' },
    seeds: { directory: './seeds' },
  },
};

Using SQLite in-memory for tests keeps the suite fast and isolated, while still exercising real SQL execution. If your production database is Postgres and you rely on Postgres-specific features (JSONB, arrays, RETURNING), consider spinning up a Postgres container via docker-compose for integration tests instead.

Unit Testing Query Builders

Unit tests focus on the shape of the query Knex produces, not the data it returns. Knex query builders are chainable objects that expose a .toSQL() method, which is perfect for assertions.

Suppose you have a repository module that builds queries:

// repositories/userRepository.js
function findActiveUsers(knex) {
  return knex('users')
    .where('active', true)
    .orderBy('created_at', 'desc');
}

function searchUsers(knex, { name, role }) {
  let query = knex('users').where('active', true);
  if (name) query = query.andWhere('name', 'ilike', `%${name}%`);
  if (role) query = query.andWhere('role', role);
  return query;
}

module.exports = { findActiveUsers, searchUsers };

You can test these without a database by inspecting the generated SQL:

// __tests__/userRepository.unit.test.js
const { findActiveUsers, searchUsers } = require('../repositories/userRepository');

function makeKnexStub() {
  const query = {
    where: jest.fn().mockReturnThis(),
    andWhere: jest.fn().mockReturnThis(),
    orderBy: jest.fn().mockReturnThis(),
    toSQL: jest.fn(),
  };
  const knex = jest.fn(() => query);
  knex.query = query;
  return { knex, query };
}

describe('userRepository unit tests', () => {
  test('findActiveUsers filters active and orders by created_at', () => {
    const { knex, query } = makeKnexStub();
    findActiveUsers(knex);

    expect(knex).toHaveBeenCalledWith('users');
    expect(query.where).toHaveBeenCalledWith('active', true);
    expect(query.orderBy).toHaveBeenCalledWith('created_at', 'desc');
  });

  test('searchUsers applies name and role filters when provided', () => {
    const { knex, query } = makeKnexStub();
    searchUsers(knex, { name: 'alice', role: 'admin' });

    expect(query.where).toHaveBeenCalledWith('active', true);
    expect(query.andWhere).toHaveBeenCalledWith('name', 'ilike', '%alice%');
    expect(query.andWhere).toHaveBeenCalledWith('role', 'admin');
  });

  test('searchUsers skips filters when values are missing', () => {
    const { knex, query } = makeKnexStub();
    searchUsers(knex, {});

    expect(query.andWhere).not.toHaveBeenCalled();
  });
});

This approach is fast and isolates the query-building logic. The downside is that it tests implementation details (which methods were called) rather than behavior (what SQL is produced). A more robust alternative is to assert against .toSQL().toNative() or .toString():

const knex = require('knex')(require('../knexfile').test);

test('findActiveUsers produces expected SQL', () => {
  const sql = findActiveUsers(knex).toSQL();
  expect(sql.sql).toContain('select * from "users"');
  expect(sql.sql).toContain('"active" = ?');
  expect(sql.bindings).toContain(true);
});

This still doesn't hit a database, but it verifies the actual generated SQL rather than the chain of method calls. It's a good middle ground.

Integration Testing with a Real Database

Unit tests can't catch issues like wrong column names, missing indexes, or type mismatches. Integration tests run real queries against a real (but disposable) database.

Setting Up and Tearing Down

A common pattern is to run migrations before each test file and truncate tables between tests:

// __tests__/setup.js
const knex = require('knex')(require('../knexfile').test);

async function resetDatabase() {
  const tables = await knex('sqlite_master')
    .where('type', 'table')
    .whereNotIn('name', ['sqlite_sequence', 'knex_migrations', 'knex_migrations_lock'])
    .pluck('name');

  for (const table of tables) {
    await knex.raw(`DELETE FROM "${table}"`);
  }
}

beforeAll(async () => {
  await knex.migrate.latest();
});

afterAll(async () => {
  await knex.destroy();
});

beforeEach(async () => {
  await resetDatabase();
});

module.exports = { knex, resetDatabase };

Writing an Integration Test

Now you can test that queries actually return the right data:

// __tests__/userRepository.integration.test.js
const { knex } = require('./setup');
const { findActiveUsers, searchUsers } = require('../repositories/userRepository');

describe('userRepository integration tests', () => {
  beforeEach(async () => {
    await knex('users').insert([
      { id: 1, name: 'Alice', role: 'admin', active: true, created_at: '2024-01-01' },
      { id: 2, name: 'Bob', role: 'user', active: false, created_at: '2024-01-02' },
      { id: 3, name: 'Carol', role: 'admin', active: true, created_at: '2024-01-03' },
    ]);
  });

  test('findActiveUsers returns only active users, newest first', async () => {
    const users = await findActiveUsers(knex);
    expect(users).toHaveLength(2);
    expect(users[0].name).toBe('Carol');
    expect(users[1].name).toBe('Alice');
  });

  test('searchUsers filters by role', async () => {
    const users = await searchUsers(knex, { role: 'admin' });
    expect(users).toHaveLength(2);
    expect(users.every(u => u.role === 'admin')).toBe(true);
  });

  test('searchUsers filters by name and role together', async () => {
    const users = await searchUsers(knex, { name: 'carol', role: 'admin' });
    expect(users).toHaveLength(1);
    expect(users[0].name).toBe('Carol');
  });
});

Notice how these tests verify behavior end-to-end at the data layer: insert known data, run the query, assert the result. This catches bugs that unit tests miss, such as a where clause that references the wrong column.

Testing Transactions

Transactions are a common source of bugs. A good integration test verifies that a failed transaction rolls back correctly:

// services/transferService.js
async function transferCredits(knex, fromId, toId, amount) {
  return knex.transaction(async (trx) => {
    const from = await trx('accounts').where({ id: fromId }).first();
    if (from.balance < amount) {
      throw new Error('Insufficient funds');
    }
    await trx('accounts').where({ id: fromId }).decrement('balance', amount);
    await trx('accounts').where({ id: toId }).increment('balance', amount);
  });
}

module.exports = { transferCredits };

// __tests__/transferService.test.js
const { knex } = require('./setup');
const { transferCredits } = require('../services/transferService');

describe('transferCredits', () => {
  beforeEach(async () => {
    await knex('accounts').insert([
      { id: 1, balance: 100 },
      { id: 2, balance: 50 },
    ]);
  });

  test('transfers credits between accounts', async () => {
    await transferCredits(knex, 1, 2, 30);
    const accounts = await knex('accounts').orderBy('id');
    expect(accounts[0].balance).toBe(70);
    expect(accounts[1].balance).toBe(80);
  });

  test('rolls back on insufficient funds', async () => {
    await expect(transferCredits(knex, 1, 2, 500)).rejects.toThrow('Insufficient funds');
    const accounts = await knex('accounts').orderBy('id');
    expect(accounts[0].balance).toBe(100);
    expect(accounts[1].balance).toBe(50);
  });
});

The rollback test is critical: without it, a missing transaction wrapper would silently leave the database in an inconsistent state.

Testing Migrations and Seeds

Migrations are code, and they deserve tests. A useful integration test runs migrations forward and backward, then asserts the schema is in the expected state:

// __tests__/migrations.test.js
const { knex } = require('./setup');

describe('migrations', () => {
  test('latest migration creates users table with expected columns', async () => {
    await knex.migrate.latest();
    const columns = await knex('users').columnInfo();
    expect(columns).toHaveProperty('id');
    expect(columns).toHaveProperty('name');
    expect(columns).toHaveProperty('email');
    expect(columns).toHaveProperty('active');
    expect(columns).toHaveProperty('created_at');
  });

  test('rollback drops the users table', async () => {
    await knex.migrate.latest();
    await knex.migrate.rollback();
    const tables = await knex('sqlite_master').where('type', 'table').pluck('name');
    expect(tables).not.toContain('users');
  });
});

Seed tests verify that seed scripts produce deterministic data:

// __tests__/seeds.test.js
const { knex } = require('./setup');

describe('seeds', () => {
  beforeEach(async () => {
    await knex.migrate.latest();
    await knex.seed.run();
  });

  test('seed creates an admin user', async () => {
    const admin = await knex('users').where({ email: 'admin@example.com' }).first();
    expect(admin).toBeTruthy();
    expect(admin.role).toBe('admin');
  });
});

End-to-End Tests

E2E tests exercise the entire stack: HTTP request, application logic, Knex queries, and the database. They are slower but catch integration issues that lower-level tests miss.

Assume an Express app:

// app.js
const express = require('express');
const { findActiveUsers } = require('./repositories/userRepository');

function createApp(knex) {
  const app = express();
  app.use(express.json());

  app.get('/users', async (req, res) => {
    const users = await findActiveUsers(knex);
    res.json(users);
  });

  app.post('/users', async (req, res) => {
    const [user] = await knex('users')
      .insert({ name: req.body.name, role: req.body.role || 'user', active: true })
      .returning('*');
    res.status(201).json(user);
  });

  return app;
}

module.exports = { createApp };

An E2E test uses supertest to hit the app while a real database runs underneath:

// __tests__/app.e2e.test.js
const request = require('supertest');
const knex = require('knex')(require('../knexfile').test);
const { createApp } = require('../app');

const app = createApp(knex);

beforeAll(async () => {
  await knex.migrate.latest();
});

afterAll(async () => {
  await knex.destroy();
});

beforeEach(async () => {
  await knex('users').del();
});

describe('GET /users', () => {
  test('returns active users as JSON', async () => {
    await knex('users').insert([
      { name: 'Alice', role: 'admin', active: true },
      { name: 'Bob', role: 'user', active: false },
    ]);

    const res = await request(app).get('/users');

    expect(res.status).toBe(200);
    expect(res.body).toHaveLength(1);
    expect(res.body[0].name).toBe('Alice');
  });
});

describe('POST /users', () => {
  test('creates a new user and returns it', async () => {
    const res = await request(app)
      .post('/users')
      .send({ name: 'Dave', role: 'user' });

    expect(res.status).toBe(201);
    expect(res.body.name).toBe('Dave');
    expect(res.body.active).toBe(true);

    const stored = await knex('users').where({ name: 'Dave' });
    expect(stored).toHaveLength(1);
  });
});

This test verifies the full request-response cycle, including how the app interacts with Knex and the database. If a migration is missing a column, the POST will fail; if the query logic is wrong, the GET will return unexpected data.

Best Practices

• Use a disposable database for tests. Never run integration or E2E tests against a shared development database. In-memory SQLite or a throwaway Docker container keeps tests isolated and repeatable.
• Reset state between tests. Truncate or delete data in beforeEach so tests don't depend on execution order.
• Prefer behavior over implementation. Assert on returned rows and generated SQL, not on which Knex methods were called.
• Test transaction rollbacks explicitly. A transaction that doesn't roll back is a silent data-corruption bug.
• Keep unit tests fast and numerous, integration tests moderate, E2E tests few. The pyramid keeps your suite responsive.
• Match the test database to production when it matters. If you rely on Postgres-specific features, test against Postgres. SQLite is fine for most query logic but will not catch dialect-specific issues.
• Seed with factories, not hardcoded fixtures. Libraries like @faker-js/faker or custom factories make seed data flexible and reduce brittle coupling between tests.
• Run migrations as part of the test setup. This ensures your tests always run against the latest schema and catches migration bugs early.
• Avoid mocking Knex in integration tests. Mocking defeats the purpose: you want to catch real SQL issues, not pretend they don't exist.

Conclusion

Testing Knex components effectively means meeting the database at multiple levels. Unit tests verify that your query builders produce the right SQL without the overhead of a database connection. Integration tests run those queries against a real, disposable database to catch schema mismatches, transaction bugs, and data-shape issues. E2E tests tie everything together, ensuring that HTTP requests flow correctly through your services and into the database and back. By layering these tests and following best practices around isolation, state reset, and behavior-focused assertions, you build a safety net that lets you refactor query logic with confidence and ship data-access code that behaves the way you expect — every time.