Knex.js Documentation

Knex supports several ways to bind parameters in knex.raw() to ensure values are properly escaped:

Positional Bindings

• ?: Interpreted as a value.
• ??: Interpreted as an identifier (e.g., table or column names).

Named Bindings

• :name: Interpreted as a value.
• :name:: Interpreted as an identifier.

Named bindings are processed as long as the provided value is not undefined.

Single Bindings

If you only have one binding, you can pass the value directly as the second argument to .raw() instead of an array.

Array Bindings

Knex does not have a unified syntax for array bindings (like IN (?)). You must manually generate the correct number of placeholders in your SQL string.

// Positional: Identifier and Value
knex('users').where(knex.raw('?? = ?', ['user.name', 1]))

// Named: Identifier and Value
const raw = ':name: = :thisGuy or :name: = :otherGuy or :name: = :undefinedBinding'
knex('users').where(knex.raw(raw, {
  name: 'users.name',
  thisGuy: 'Bob',
  otherGuy: 'Jay',
  undefinedBinding: undefined
}))

// Single binding shortcut
knex.raw('LOWER("login") = ?', 'knex')

// Manual Array binding for IN clauses
const myArray = [1, 2, 3]
knex.raw('select * from users where id in (' + myArray.map(_ => '?').join(',') + ')', [...myArray]);

A transactionProvider is a factory that creates a reusable transaction instance. The transaction does not actually start until the provider is called for the first time. Subsequent calls to the provider return the same transaction instance, allowing you to group multiple operations into a single unit of work across different parts of your code.

// Does not start a transaction yet
const trxProvider = knex.transactionProvider();

const books = [
  {title: 'Canterbury Tales'},
  {title: 'Moby Dick'},
  {title: 'Hamlet'}
];

// Starts a transaction
const trx = await trxProvider();
const ids = await trx('catalogues')
  .insert({name: 'Old Books'}, 'id')
books.forEach((book) => book.catalogue_id = ids[0]);
await trx('books').insert(books);

// Reuses same transaction
const sameTrx = await trxProvider();
const ids2 = await sameTrx('catalogues')
  .insert({name: 'New Books'}, 'id')
books.forEach((book) => book.catalogue_id = ids2[0]);
await sameTrx('books').insert(books);

To reduce boilerplate and enable automatic type inference across your application, you can augment the Tables interface in the 'knex/types/tables' module. This allows you to define your table structures once and have Knex automatically apply them to queries.

For advanced use cases, you can use Knex.CompositeTableType to specify different interfaces for the base model (used for select, where, etc.), the 'insert' type (used for .insert()), and the 'update' type (used for .update()).

import { Knex } from 'knex';

declare module 'knex/types/tables' {
  interface User {
    id: number;
    name: string;
    created_at: string;
    updated_at: string;
  }
  
  interface Tables {
    // Basic typing: maps 'users' table to the User interface
    users: User;

    // Advanced typing: uses CompositeTableType for different operations
    users_composite: Knex.CompositeTableType<
      User, // Base type (select, where, etc.)
      Pick<User, 'name'> & Partial<Pick<User, 'created_at' | 'updated_at'>>, // Insert type
      Partial<Omit<User, 'id'>> // Update type
    >;
  }
}

You can add custom methods to Knex's internal builders using the .extend() method. This allows you to create reusable, domain-specific logic for schema manipulation, table definitions, view creation, or column configuration.

Available builders to extend:

• knex.SchemaBuilder: For schema-level operations.
• knex.TableBuilder: For operations within a table definition.
• knex.ViewBuilder: For view-specific operations.
• knex.ColumnBuilder: For column-specific operations.

Each extension function should typically return this to allow for method chaining.

knex.SchemaBuilder.extend("functionName", function() {
    console.log('Custom Schema Builder Function');
    return this;
});

knex.TableBuilder.extend("functionName", function() {
    console.log('Custom Table Builder Function');
    return this;
});

knex.ViewBuilder.extend("functionName", function() {
    console.log('Custom View Builder Function');
    return this;
});

knex.ColumnBuilder.extend("functionName", function() {
    console.log('Custom Column Builder Function');
    return this;
});