Knex TypeScript: Strongly Typed Applications

Introduction to Knex with TypeScript

Knex.js is one of the most popular SQL query builders in the Node.js ecosystem, providing a clean, chainable interface for constructing database queries. When combined with TypeScript, Knex becomes an even more powerful tool, allowing developers to build strongly typed applications that catch errors at compile time rather than runtime. This tutorial covers everything you need to know to leverage the full power of typed database operations.

What Is Strongly Typed Knex?

🚀 Deploy your AI agent in 10 minutes

Managed Hermes hosting. Zero DevOps. 100M tokens/mo included.

Try it free →

By default, Knex queries return any types or loosely typed results. A strongly typed Knex setup means you define explicit TypeScript interfaces for your database tables, then parameterize Knex queries with generic type parameters so that every result—whether a single row, an array of rows, or a count—carries precise type information. This eliminates guesswork, reduces bugs, and provides excellent autocompletion in modern IDEs.

Why Strong Typing Matters for Database Queries

Compile-time safety: Mismatched column names or incorrect types are caught before you deploy
Autocompletion: Your editor knows exactly what properties exist on query results
Refactoring confidence: Rename a column in your schema and TypeScript flags every affected query
Self-documenting code: Interfaces serve as living documentation of your database shape
Team collaboration: New team members can understand data shapes instantly without digging through SQL files

Setting Up a Typed Knex Project

Start by installing the required dependencies. You need knex, the appropriate database driver, TypeScript, and the Node.js type definitions.

npm install knex pg typescript @types/node
npm install --save-dev ts-node

Create a tsconfig.json file configured for strict type checking:

{
  "compilerOptions": {
    "target": "ES2020",
    "module": "commonjs",
    "strict": true,
    "esModuleInterop": true,
    "skipLibCheck": true,
    "forceConsistentCasingInFileNames": true,
    "outDir": "./dist",
    "rootDir": "./src",
    "declaration": true,
    "declarationMap": true,
    "sourceMap": true
  },
  "include": ["src/**/*"]
}

Step 1: Define Your Table Types

The foundation of typed Knex is a set of interfaces that mirror your database tables. Create a dedicated file for your schema types. Here's an example for a typical e-commerce application:

// src/types/database.ts

export interface User {
  id: number;
  email: string;
  name: string;
  created_at: Date;
  updated_at: Date;
}

export interface Product {
  id: number;
  name: string;
  description: string | null;
  price: number;
  stock_quantity: number;
  category_id: number;
  created_at: Date;
}

export interface Order {
  id: number;
  user_id: number;
  total_amount: number;
  status: 'pending' | 'confirmed' | 'shipped' | 'delivered';
  created_at: Date;
}

export interface OrderItem {
  id: number;
  order_id: number;
  product_id: number;
  quantity: number;
  unit_price: number;
}

// Composite types for joined queries
export interface OrderWithUser extends Order {
  user_email: string;
  user_name: string;
}

export interface OrderWithItems extends Order {
  items: OrderItem[];
}

Using Type-Only Imports for Clean Boundaries

TypeScript 4.5+ supports import type syntax, which guarantees that type definitions are erased at compile time and never affect runtime behavior. Use this consistently for your database types:

import type { User, Product, Order } from '../types/database';

Step 2: Create a Typed Knex Instance

Configure Knex with your database connection and wrap it with proper typing. There are multiple approaches to parameterize Knex generics.

Approach A: Generic Table Parameter on Each Query

This is the most straightforward method. You pass the table type directly to each query method:

// src/db/connection.ts
import knex, { Knex } from 'knex';
import type { User, Product, Order, OrderItem } from '../types/database';

const config: Knex.Config = {
  client: 'pg',
  connection: {
    host: process.env.DB_HOST || 'localhost',
    port: Number(process.env.DB_PORT) || 5432,
    database: process.env.DB_NAME || 'myapp',
    user: process.env.DB_USER || 'postgres',
    password: process.env.DB_PASSWORD || 'postgres',
  },
  pool: { min: 2, max: 10 },
};

export const db = knex(config);

// Typed query helper functions
export async function findUserById(id: number): Promise {
  return db('users')
    .select('*')
    .where('id', id)
    .first();
}

export async function findUsersByEmail(email: string): Promise {
  return db('users')
    .select('*')
    .where('email', email);
}

export async function createUser(data: Omit): Promise {
  const [user] = await db('users')
    .insert(data)
    .returning('*');
  return user;
}

export async function updateUserName(id: number, name: string): Promise {
  const [user] = await db('users')
    .where('id', id)
    .update({ name, updated_at: new Date() })
    .returning('*');
  return user;
}

Approach B: Extending Knex with a Custom Typed Wrapper

For larger applications, wrap Knex in a class that bakes in table types. This centralizes type safety and reduces repetition:

// src/db/typed-client.ts
import knex, { Knex } from 'knex';
import type { User, Product, Order, OrderItem, OrderWithUser } from '../types/database';

export class TypedKnexClient {
  private knex: Knex;

  constructor(config: Knex.Config) {
    this.knex = knex(config);
  }

  // Generic table accessor
  table(tableName: string) {
    return this.knex(tableName);
  }

  // Specific table accessors with baked-in types
  get users() {
    return this.knex('users');
  }

  get products() {
    return this.knex('products');
  }

  get orders() {
    return this.knex('orders');
  }

  get orderItems() {
    return this.knex('order_items');
  }

  // Raw access for complex cases
  get raw() {
    return this.knex;
  }

  // Transaction support with typed callback
  async transaction(
    callback: (trx: Knex.Transaction) => Promise
  ): Promise {
    return this.knex.transaction(callback);
  }

  async destroy(): Promise {
    await this.knex.destroy();
  }
}

const client = new TypedKnexClient({
  client: 'pg',
  connection: process.env.DATABASE_URL,
});

export default client;

Using the typed client becomes remarkably clean:

// src/services/user-service.ts
import db from '../db/typed-client';
import type { User } from '../types/database';

export async function getActiveUsers(): Promise {
  return db.users
    .select('*')
    .where('status', 'active')
    .orderBy('created_at', 'desc');
}

export async function getUserWithOrderCount(userId: number): Promise {
  const result = await db.users
    .leftJoin('orders', 'users.id', 'orders.user_id')
    .select('users.*')
    .count('orders.id as order_count')
    .where('users.id', userId)
    .groupBy('users.id')
    .first();
  
  return result as User & { order_count: number };
}

Step 3: Typed Inserts, Updates, and Deletes

Mutation queries benefit enormously from typing. TypeScript ensures you pass the correct shape for inserts and receive properly typed results when using returning.

// Typed insert with partial data
type ProductInsert = Omit;

async function createProduct(data: ProductInsert): Promise {
  const [product] = await db.products
    .insert(data)
    .returning('*');
  return product;
}

// Typed batch insert
async function createProductsBatch(items: ProductInsert[]): Promise {
  return db.products
    .insert(items)
    .returning('*');
}

// Typed update with partial fields
type ProductUpdate = Partial>;

async function updateProduct(id: number, changes: ProductUpdate): Promise {
  const [product] = await db.products
    .where('id', id)
    .update(changes)
    .returning('*');
  return product;
}

// Typed delete returning the deleted row
async function deleteProduct(id: number): Promise {
  const [product] = await db.products
    .where('id', id)
    .del()
    .returning('*');
  return product;
}

Step 4: Handling Joined Queries with Precision

Joined queries produce composite results that don't match any single table type. Define explicit result types for joins and use them in your queries:

// src/types/database.ts — additional join types

export interface OrderWithUserAndItems {
  order_id: number;
  order_status: 'pending' | 'confirmed' | 'shipped' | 'delivered';
  order_total: number;
  order_created: Date;
  user_id: number;
  user_email: string;
  user_name: string;
  item_id: number;
  product_id: number;
  quantity: number;
  unit_price: number;
  product_name: string;
}

// src/services/order-service.ts
import db from '../db/typed-client';
import type { OrderWithUserAndItems } from '../types/database';

async function getOrderDetails(orderId: number): Promise {
  return db.raw
    .select(
      'orders.id as order_id',
      'orders.status as order_status',
      'orders.total_amount as order_total',
      'orders.created_at as order_created',
      'users.id as user_id',
      'users.email as user_email',
      'users.name as user_name',
      'order_items.id as item_id',
      'order_items.product_id',
      'order_items.quantity',
      'order_items.unit_price',
      'products.name as product_name'
    )
    .from('orders')
    .join('users', 'orders.user_id', 'users.id')
    .join('order_items', 'orders.id', 'order_items.order_id')
    .join('products', 'order_items.product_id', 'products.id')
    .where('orders.id', orderId)
    .then(rows => rows as OrderWithUserAndItems[]);
}

Step 5: Type-Safe Aggregation and Analytics Queries

Aggregation queries return counts, sums, and grouped data. Define result types explicitly so TypeScript knows exactly what you're working with:

// Analytics result types
interface SalesByCategory {
  category_id: number;
  total_sales: number;
  order_count: number;
}

interface DailyRevenue {
  date: string;
  revenue: number;
  order_count: number;
}

interface TopCustomer {
  user_id: number;
  user_email: string;
  user_name: string;
  total_spent: number;
  order_count: number;
}

async function getSalesByCategory(): Promise {
  return db.raw
    .select('products.category_id')
    .sum('order_items.unit_price * order_items.quantity as total_sales')
    .countDistinct('orders.id as order_count')
    .from('order_items')
    .join('products', 'order_items.product_id', 'products.id')
    .join('orders', 'order_items.order_id', 'orders.id')
    .where('orders.status', 'delivered')
    .groupBy('products.category_id')
    .orderBy('total_sales', 'desc')
    .then(rows => rows as SalesByCategory[]);
}

async function getDailyRevenue(days: number): Promise {
  return db.raw
    .select(knex.raw("DATE(orders.created_at) as date"))
    .sum('orders.total_amount as revenue')
    .count('orders.id as order_count')
    .from('orders')
    .where('orders.created_at', '>', knex.raw(`NOW() - INTERVAL '${days} days'`))
    .groupByRaw("DATE(orders.created_at)")
    .orderBy('date', 'desc')
    .then(rows => rows as DailyRevenue[]);
}

async function getTopCustomers(limit: number = 10): Promise {
  return db.raw
    .select(
      'users.id as user_id',
      'users.email as user_email',
      'users.name as user_name'
    )
    .sum('orders.total_amount as total_spent')
    .count('orders.id as order_count')
    .from('users')
    .join('orders', 'users.id', 'orders.user_id')
    .where('orders.status', 'delivered')
    .groupBy('users.id', 'users.email', 'users.name')
    .orderBy('total_spent', 'desc')
    .limit(limit)
    .then(rows => rows as TopCustomer[]);
}

Step 6: Strongly Typed Transactions

Transactions require careful typing because multiple tables may be involved. TypeScript helps ensure consistency across all operations within a transaction block:

async function placeOrder(
  userId: number,
  items: Array<{ product_id: number; quantity: number }>
): Promise {
  return db.transaction(async (trx) => {
    // Verify user exists
    const user = await trx('users')
      .select('id')
      .where('id', userId)
      .first();
    
    if (!user) {
      throw new Error(`User ${userId} not found`);
    }

    // Calculate total and verify stock
    let totalAmount = 0;
    const orderItems: Array> = [];

    for (const item of items) {
      const product = await trx('products')
        .select('id', 'price', 'stock_quantity')
        .where('id', item.product_id)
        .first();

      if (!product) {
        throw new Error(`Product ${item.product_id} not found`);
      }
      if (product.stock_quantity < item.quantity) {
        throw new Error(`Insufficient stock for product ${item.product_id}`);
      }

      totalAmount += product.price * item.quantity;
      orderItems.push({
        product_id: item.product_id,
        quantity: item.quantity,
        unit_price: product.price,
      });

      // Decrement stock
      await trx('products')
        .where('id', item.product_id)
        .decrement('stock_quantity', item.quantity);
    }

    // Create order
    const [order] = await trx('orders')
      .insert({
        user_id: userId,
        total_amount: totalAmount,
        status: 'pending',
      })
      .returning('*');

    // Create order items
    const itemsWithOrderId = orderItems.map(item => ({
      ...item,
      order_id: order.id,
    }));

    await trx('order_items')
      .insert(itemsWithOrderId);

    return order;
  });
}

Notice how each query within the transaction uses generic type parameters like trx<User>, trx<Product>, and trx<Order>. The transaction callback is fully typed, and the compiler validates every column reference and returned shape.

Step 7: Type-Safe Migrations

Migrations benefit from typing as well. Define your schema shape in TypeScript and use Knex's schema builder with type awareness. While the schema builder itself isn't fully generic, you can create helper utilities that reference your type definitions:

// src/migrations/001_create_users.ts
import { Knex } from 'knex';
import type { User } from '../types/database';

// A typed schema helper that references your table types
const UserColumns: Record = {
  id: 'id',
  email: 'email',
  name: 'name',
  created_at: 'created_at',
  updated_at: 'updated_at',
};

export async function up(knex: Knex): Promise {
  await knex.schema.createTable('users', (table) => {
    table.bigIncrements(UserColumns.id).primary();
    table.string(UserColumns.email, 255).notNullable().unique();
    table.string(UserColumns.name, 255).notNullable();
    table.timestamp(UserColumns.created_at).defaultTo(knex.fn.now()).notNullable();
    table.timestamp(UserColumns.updated_at).defaultTo(knex.fn.now()).notNullable();
  });

  // Create an update trigger for updated_at
  await knex.raw(`
    CREATE OR REPLACE FUNCTION update_updated_at_column()
    RETURNS TRIGGER AS $$
    BEGIN
      NEW.updated_at = NOW();
      RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;
  `);

  await knex.raw(`
    CREATE TRIGGER trigger_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();
  `);
}

export async function down(knex: Knex): Promise {
  await knex.schema.dropTableIfExists('users');
}

Step 8: Building a Repository Pattern with Full Typing

The repository pattern pairs beautifully with typed Knex. Create a generic base repository and extend it for each table:

// src/repositories/base-repository.ts
import { Knex } from 'knex';
import type { TypedKnexClient } from '../db/typed-client';

export abstract class BaseRepository> {
  protected db: TypedKnexClient;
  protected abstract tableName: string;

  constructor(db: TypedKnexClient) {
    this.db = db;
  }

  async findById(id: number): Promise {
    return this.db.table(this.tableName)
      .select('*')
      .where('id', id)
      .first();
  }

  async findAll(limit?: number, offset?: number): Promise {
    let query = this.db.table(this.tableName).select('*');
    if (limit !== undefined) query = query.limit(limit);
    if (offset !== undefined) query = query.offset(offset);
    return query;
  }

  async create(data: Omit): Promise {
    const [record] = await this.db.table(this.tableName)
      .insert(data)
      .returning('*');
    return record;
  }

  async update(id: number, data: Partial>): Promise {
    const [record] = await this.db.table(this.tableName)
      .where('id', id)
      .update(data)
      .returning('*');
    return record;
  }

  async delete(id: number): Promise {
    const [record] = await this.db.table(this.tableName)
      .where('id', id)
      .del()
      .returning('*');
    return record;
  }

  async count(): Promise {
    const result = await this.db.table(this.tableName)
      .count('* as count')
      .first();
    return Number(result?.count) || 0;
  }
}

Now create concrete repositories that inherit all typed methods automatically:

// src/repositories/user-repository.ts
import { BaseRepository } from './base-repository';
import type { User } from '../types/database';
import type { TypedKnexClient } from '../db/typed-client';

export class UserRepository extends BaseRepository {
  protected tableName = 'users';

  constructor(db: TypedKnexClient) {
    super(db);
  }

  // Custom typed queries beyond the base repository
  async findByEmail(email: string): Promise {
    return this.db.users
      .select('*')
      .where('email', email)
      .first();
  }

  async findActive(): Promise {
    return this.db.users
      .select('*')
      .where('status', 'active')
      .orderBy('created_at', 'desc');
  }

  async searchByName(term: string): Promise {
    return this.db.users
      .select('*')
      .where('name', 'ilike', `%${term}%`)
      .orderBy('name');
  }

  async getUsersWithStats(): Promise<(User & { order_count: number; total_spent: number })[]> {
    return this.db.users
      .select('users.*')
      .leftJoin('orders', 'users.id', 'orders.user_id')
      .count('orders.id as order_count')
      .sum('orders.total_amount as total_spent')
      .groupBy('users.id')
      .orderBy('total_spent', 'desc')
      .then(rows => rows as (User & { order_count: number; total_spent: number })[]);
  }
}

The repository pattern with TypeScript generics gives you incredible leverage—every method on UserRepository returns properly typed results without any additional effort per query.

Advanced Pattern: Conditional Query Builder Types

When building dynamic queries with conditional where clauses, maintain type safety by composing the query step by step:

interface ProductFilters {
  categoryId?: number;
  minPrice?: number;
  maxPrice?: number;
  inStock?: boolean;
  searchTerm?: string;
}

interface PaginationParams {
  page: number;
  pageSize: number;
}

interface PaginatedResult {
  data: T[];
  total: number;
  page: number;
  pageSize: number;
  totalPages: number;
}

async function searchProducts(
  filters: ProductFilters,
  pagination: PaginationParams
): Promise> {
  const { categoryId, minPrice, maxPrice, inStock, searchTerm } = filters;
  const { page, pageSize } = pagination;

  // Build query conditionally while maintaining type
  let query = db.products.select('*');

  if (categoryId !== undefined) {
    query = query.where('category_id', categoryId);
  }
  if (minPrice !== undefined) {
    query = query.where('price', '>=', minPrice);
  }
  if (maxPrice !== undefined) {
    query = query.where('price', '<=', maxPrice);
  }
  if (inStock === true) {
    query = query.where('stock_quantity', '>', 0);
  }
  if (searchTerm) {
    query = query.where('name', 'ilike', `%${searchTerm}%`);
  }

  // Clone the query for counting total results
  const countQuery = query.clone();
  const [{ count }] = await countQuery.count('* as count');
  const total = Number(count);

  // Apply pagination to the data query
  const data = await query
    .orderBy('created_at', 'desc')
    .limit(pageSize)
    .offset((page - 1) * pageSize);

  return {
    data,
    total,
    page,
    pageSize,
    totalPages: Math.ceil(total / pageSize),
  };
}

Handling JSON and Enum Columns with TypeScript

PostgreSQL supports JSON columns and custom enum types. TypeScript lets you type these precisely:

// Define structured JSON types
interface ProductMetadata {
  tags: string[];
  seo_title: string;
  seo_description: string;
  dimensions: {
    width: number;
    height: number;
    depth: number;
    unit: 'cm' | 'in';
  };
}

interface ProductWithMetadata extends Product {
  metadata: ProductMetadata;
}

// Enum as TypeScript union type (mirrors PostgreSQL enum)
type ProductStatus = 'draft' | 'published' | 'archived';

interface ProductWithStatus extends Product {
  status: ProductStatus;
  metadata: ProductMetadata;
}

async function getPublishedProducts(): Promise {
  return db.raw
    .select('*')
    .from('products')
    .where('status', 'published')
    .then(rows => rows as ProductWithStatus[]);
}

// When inserting JSON, TypeScript validates the shape
async function createProductWithMetadata(
  data: ProductInsert,
  metadata: ProductMetadata
): Promise {
  const [product] = await db.products
    .insert({
      ...data,
      metadata: JSON.stringify(metadata), // Knex handles JSON serialization
    })
    .returning('*');
  
  return {
    ...product,
    metadata: typeof product.metadata === 'string' 
      ? JSON.parse(product.metadata) 
      : product.metadata,
  } as ProductWithMetadata;
}

Best Practices for Strongly Typed Knex Applications

1. Keep Types in Sync with Your Schema

Your TypeScript interfaces must reflect the actual database schema. Consider using tools like pg-structure or schemats to auto-generate TypeScript types from a live database. Run these generators as part of your CI pipeline to detect drift early.

// Example using schemats (run as a script)
// npx schemats generate --host localhost --database myapp --user postgres --password postgres --output src/types/generated

2. Use Strict Null Checks

Enable strictNullChecks in your tsconfig.json. Mark nullable columns with | null in your interfaces. When using .first(), always account for the possibility of undefined:

const user = await db.users.where('id', id).first();
// TypeScript knows user is User | undefined — handle it
if (!user) {
  throw new NotFoundError(`User ${id} not found`);
}
// From here, user is narrowed to User

3. Prefer Explicit Return Types on Query Functions

Always annotate the return type of query functions. This catches mismatches early and serves as documentation:

// Good — explicit return type
async function getUserById(id: number): Promise {
  return db.users.where('id', id).first();
}

// Avoid — implicit return type relies on inference which can mask errors
async function getUserById(id: number) {
  return db.users.where('id', id).first();
}

4. Use Omit and Pick for Insert and Update Types

Derive insert and update types from your base table interface using utility types. This ensures consistency and avoids manual duplication:

type UserInsert = Omit;
type UserUpdate = Partial>;

5. Centralize Query Logic in Repositories or Services

Don't scatter raw Knex queries throughout your codebase. Encapsulate them in repository classes or service modules. This makes it trivial to find and update queries when the schema changes.

6. Leverage TypeScript's Discriminated Unions for Status Columns

For status columns, use string literal unions rather than plain string. TypeScript will exhaustively check switch statements and if-else chains:

type OrderStatus = 'pending' | 'confirmed' | 'shipped' | 'delivered' | 'cancelled';

function getNextStatus(current: OrderStatus): OrderStatus | null {
  switch (current) {
    case 'pending':
      return 'confirmed';
    case 'confirmed':
      return 'shipped';
    case 'shipped':
      return 'delivered';
    case 'delivered':
      return null;
    case 'cancelled':
      return null;
    // TypeScript exhaustiveness check — no default needed
  }
}

7. Test Your Typed Queries

Write unit tests that verify not just runtime behavior but also type correctness. Use a test database or mock Knex to validate that your queries produce the expected shapes:

// Example test using an in-memory SQLite for speed
import knex from 'knex';
import type { User } from '../types/database';

describe('UserRepository', () => {
  let db: ReturnType;

  beforeAll(() => {
    db = knex({
      client: 'sqlite3',
      connection: ':memory:',
      useNullAsDefault: true,
    });
  });

  it('should return typed user on findById', async () => {
    await db.schema.createTable('users', (table) => {
      table.increments('id');
      table.string('email');
      table.string('name');
      table.timestamp('created_at');
      table.timestamp('updated_at');
    });

    await db('users').insert({
      email: 'test@example.com',
      name: 'Test User',
      created_at: new Date(),
      updated_at: new Date(),
    });

    const user = await db('users').where('id', 1).first();
    
    // TypeScript validates these assertions
    expect(user).toBeDefined();
    expect(user!.email).toBe('test@example.com');
    expect(typeof user!.id).toBe('number');
  });
});

8. Document Type Mappings for Team Alignment

Maintain a living document or a generated reference that maps TypeScript interfaces to database tables. This helps designers, backend developers, and data analysts stay aligned on the data model.

Common Pitfalls and How to Avoid Them

Pitfall 1: Type Widening from Knex Chain Methods

Knex methods like .modify() or dynamic .where() calls can sometimes lose type information. When this happens, explicitly annotate the intermediate variable:

// Type narrowing can be lost in complex chains — use explicit annotation
const query: Knex.QueryBuilder = db.users.select('*');
if (filterActive) {
  query.where('status', 'active');
}
const results: User[] = await query;

Pitfall 2: Assuming Returning Always Works

The .returning() clause is not supported by all database drivers (notably MySQL). If you're using MySQL, you'll need a different pattern for retrieving inserted rows. Always check your driver's compatibility and consider using .insert() followed by a separate .select() as a fallback.

Pitfall 3: Forgetting That Count Results Are Strings

Knex's .count() method often returns the count as a string due to driver behavior. Always coerce counts to numbers:

const result = await db.users.count('* as count').first();
const count = Number(result?.count ?? 0);

Conclusion

Combining Knex with TypeScript transforms your data access layer from a source of subtle runtime errors into a compile-time verified, self-documenting system. By defining precise interfaces for your tables, parameterizing Knex queries with generics, and organizing query logic into typed repositories, you gain complete confidence that every database interaction is correct. The initial investment in setting up types pays dividends throughout the entire development lifecycle—from faster feature development with IDE autocompletion, to safer refactoring, to onboarding new team members who can understand the data model instantly. Start with explicit table interfaces, adopt the repository pattern, enforce strict null checks, and keep your types synchronized with your schema. Your future self—and your teammates—will thank you for building a strongly typed foundation that scales gracefully with your application.