NestJS Knex Example: Step-by-Step Guide to Building Scalable SQL Application

Introduction to SQL with NestJS

In a backend ecosystem dominated by ORMs like TypeORM and Sequelize, it's easy to overlook the power of raw SQL and lightweight query builders. NestJS—a framework known for its structured architecture—works well with both.

But sometimes, developers want more control, faster performance and less abstraction. That’s where Knex.js comes in. Unlike heavy ORMs, Knex gives you the freedom to write raw, optimized SQL while still offering helpful utilities like migrations and query builders.

What is Knex.js?

Knex.js is a SQL query builder for Node.js that supports several relational databases like:

• PostgreSQL

• MySQL / MariaDB

• SQLite3

• MSSQL

It provides a clean, chainable syntax for writing SQL queries in JavaScript. While it’s not an ORM, Knex allows you to manage migrations, transactions and connections—making it ideal for projects that need SQL control without ORM overhead.

Benefits of Using Knex with NestJS

Here’s why developers often pair Knex with NestJS instead of an ORM:

Setting Up a New NestJS Project

To get started, create a new NestJS project:

npm i -g @nestjs/cli
nest new nest-knex-app
cd nest-knex-app

Choose any package manager (npm/yarn) and clear out any boilerplate modules if necessary.

Installing Knex and Required Dependencies

Let’s install Knex and a SQL driver. For this example, we'll use PostgreSQL:

npm install knex pg

If you're using another DB:

• MySQL: npm install mysql2

• SQLite: npm install sqlite3

To enable migrations and CLI support, you can also install Knex globally:

npm install -g knex

Configuring Knex in a NestJS App

Create a knexfile.js in your project root:

module.exports = {
  development: {
    client: 'pg',
    connection: {
      host: 'localhost',
      user: 'your_user',
      password: 'your_password',
      database: 'your_db'
    },
    migrations: {
      directory: './migrations'
    }
  }
};

Add this to your package.json scripts:

"scripts": {
  "migrate": "knex migrate:latest --knexfile knexfile.js",
  "rollback": "knex migrate:rollback --knexfile knexfile.js"
}

Creating and Running Migrations

Create a migration to define your database schema:

knex migrate:make create_users_table --knexfile knexfile.js

CopyEdit

knex migrate:make create_users_table --knexfile knexfile.js

Edit the file in migrations/:

exports.up = function(knex) {
  return knex.schema.createTable('users', (table) => {
    table.increments('id');
    table.string('email').notNullable().unique();
    table.string('name');
    table.timestamps(true, true);
  });
};

exports.down = function(knex) {
  return knex.schema.dropTable('users');
};

Run the migration:

npm run migrate

Building a KnexService in NestJS

Create a new service that wraps Knex:

nest g service knex

In knex.service.ts:

import { Injectable, OnModuleInit } from '@nestjs/common';
import knex from 'knex';
import * as config from '../../knexfile';

@Injectable()
export class KnexService implements OnModuleInit {
  private db;

  onModuleInit() {
    this.db = knex(config.development);
  }

  get connection() {
    return this.db;
  }
}

Now you can inject KnexService into any module and use .connection to access the Knex instance.

Writing Basic SQL Queries Using Knex

Here’s how to use Knex in a controller or service:

const users = await this.knexService.connection('users')
  .select('*')
  .where({ email: 'test@example.com' });

await this.knexService.connection('users').insert({
  email: 'newuser@example.com',
  name: 'New User'
});

You can chain queries for sorting, filtering and pagination.

Knex with DTOs and Validation in NestJS

Use DTOs for type safety and validation:

export class CreateUserDto {
  @IsEmail()
  email: string;

  @IsOptional()
  @IsString()
  name?: string;
}

Combine DTOs with your controller logic for cleaner and safer inputs.

Knex Query Examples by Use Case

Now let’s explore practical, real-world examples using Knex with NestJS:

1. Pagination

const users = await this.knexService.connection('users')
  .select('*')
  .limit(10)
  .offset(20); // For page 3 (assuming 10 per page)

2. Search and Filtering

const results = await this.knexService.connection('users')
  .where('name', 'ilike', `%john%`)
  .andWhere('email', 'like', '%@gmail.com');

3. Joins

const posts = await this.knexService.connection('posts')
  .join('users', 'users.id', 'posts.user_id')
  .select('posts.*', 'users.name as authorName');

4. Transactions

await this.knexService.connection.transaction(async trx => {
  await trx('users').insert({ email: 'new@example.com' });
  await trx('logs').insert({ action: 'User created' });
});

These examples show how Knex handles complex SQL tasks with clean, readable syntax.

Error Handling with Knex in NestJS

SQL can fail for various reasons—bad data, missing tables or constraint violations. NestJS makes it easy to manage these errors centrally.

Basic Try-Catch

try {
  await this.knexService.connection('users').insert({ email: 'a@b.com' });
} catch (error) {
  throw new InternalServerErrorException(error.message);
}

Global Exception Filters

For consistent error handling, use NestJS filters:

@Catch(QueryFailedError)
export class DatabaseExceptionFilter implements ExceptionFilter {
  catch(exception: QueryFailedError, host: ArgumentsHost) {
    const ctx = host.switchToHttp();
    const response = ctx.getResponse<Response>();
    response.status(500).json({ message: exception.message });
  }
}

Testing Knex Queries in NestJS

To write tests, isolate logic in services and mock the Knex connection.

Mock KnexService

const mockKnexService = {
  connection: jest.fn().mockReturnValue({
    select: jest.fn().mockReturnValue([{ id: 1, email: 'test@example.com' }]),
  }),
};

Unit Test Example

it('should fetch users', async () => {
  const users = await service.getUsers();
  expect(users).toHaveLength(1);
});

Make sure you use test databases or mocking for safe and repeatable tests.

Switching Between Development and Production Databases

Use environment variables to control which Knex config gets loaded:

const config = require('./knexfile')[process.env.NODE_ENV || 'development'];

Maintain separate .env files for:

• Development

• Testing

• Production

This ensures proper isolation of data environments and reduces risk of destructive migrations.

Real-World Use Cases of Knex with NestJS

Here’s where this combo shines:

Knex is especially useful when performance and transparency are top priorities.

Common Pitfalls and Troubleshooting Tips

⚠️ Common Mistakes:

• Forgetting to run migrations.

• Misconfigured database credentials.

• Query returning undefined due to missing await.

✅ Tips:

• Always wrap DB calls in try/catch.

• Log Knex queries during development using .debug().

• Use tools like PostgreSQL EXPLAIN to analyze slow queries.

FAQs About NestJS Knex Example

1. Is Knex.js an ORM?

No. Knex is a query builder—it helps write SQL with JS syntax but doesn’t manage models or relationships like an ORM.

2. Can I use Knex with TypeScript?

Absolutely. Knex has full TypeScript support and you can define your own types or interfaces for result sets.

3. Can I use raw SQL in Knex?

Yes, Knex supports raw queries:

await knex.raw('SELECT * FROM users WHERE id = ?', [1]);

4. Is Knex faster than TypeORM?

In many cases, yes. Knex is lightweight and doesn’t carry the overhead of models, decorators or auto-relations.

5. Can I switch to another database easily?

Yes, Knex supports multiple SQL dialects. You just need to change the client and connection config.

6. Should I use Knex for all NestJS projects?

Not necessarily. Use it when you need SQL-level control or want to avoid ORM abstraction for performance or complexity reasons.

Conclusion: Building Powerful SQL Apps with NestJS + Knex

If you're aiming to build structured, scalable SQL applications with complete control, the combination of NestJS + Knex is a game changer.

NestJS provides a clean, modular architecture ideal for enterprise applications, while Knex gives you the flexibility to write efficient, optimized SQL queries without the heaviness of an ORM.

Whether you're building a microservice, dashboard, reporting tool or a production-grade SaaS product—this stack enables rapid development, strict typing and full performance visibility.

Start small, follow the best practices and you’ll unlock the full potential of relational databases in a modern Node.js application.