Knex Adapter

API reference for the Feathers Knex SQL database adapter

The Knex adapter provides a service interface for SQL databases using the Knex.js query builder. Supports PostgreSQL, MySQL, SQLite, MSSQL, and Oracle.

Installation

npm install @feathersjs/knex knex
# Plus your database driver
npm install pg        # for PostgreSQL
npm install mysql2    # for MySQL
npm install sqlite3   # for SQLite

KnexService

The main service class for SQL operations via Knex.

Constructor

import { KnexService } from '@feathersjs/knex'
import knex from 'knex'

const db = knex({ client: 'pg', connection: {...} })
const service = new KnexService<Result, Data, ServiceParams, PatchData>(options)

options · KnexAdapterOptions · required

Configuration options for the Knex adapter

Model · Knex · required

The Knex instance

name · string · required

The table name

id · string · default: "id"

Name of the id field property

schema · string

Database schema name (for PostgreSQL, MSSQL)

paginate · PaginationParams

Pagination settings with default and max page size

multi · boolean | string[]

Allow multiple updates. Can be true, false, or an array of method names

tableOptions · object

only · boolean

Use ONLY keyword for table queries (PostgreSQL)

extendedOperators · Record<string, string>

Custom query operators mapping. Example: { '$regexp': '~*' }

Service Methods

find

Retrieve multiple records from the table.

await service.find(params?)

params · KnexAdapterParams

query · AdapterQuery

Query filters including $limit, $skip, $sort, $select, and SQL operators

paginate · PaginationOptions | false

Override pagination settings

knex · Knex.QueryBuilder

Pre-configured Knex query builder to extend

transaction · KnexAdapterTransaction

Transaction object for transactional operations

result · Paginated<Result> | Result[]

Paginated results or array depending on settings

get

Retrieve a single record by id.

await service.get(id, params?)

id · Id · required

The record id to retrieve

result · Result

The found record

create

Create one or more new records.

await service.create(data, params?)

data · Data | Data[] · required

Record data to create. Arrays are processed sequentially

result · Result | Result[]

The created record(s)

update

Completely replace a record.

await service.update(id, data, params?)

id · Id · required

The record id to update

data · Data · required

Complete record data. Fields not provided will be set to null

result · Result

The updated record

patch

Partially update one or multiple records.

await service.patch(id, data, params?)

id · Id | null · required

The record id to patch, or null to patch multiple records matching the query

data · PatchData · required

Partial data to merge with existing record(s)

result · Result | Result[]

The patched record(s)

remove

Remove one or multiple records.

await service.remove(id, params?)

id · Id | null · required

The record id to remove, or null to remove multiple

result · Result | Result[]

The removed record(s)

KnexAdapter

The base adapter class that KnexService extends.

Properties

Model

Access the Knex instance.

const knex = service.Model

fullName

Get the full table name including schema.

const tableName = service.fullName // "myschema.mytable" or "mytable"

Methods

db

Get a Knex query builder for the table, with transaction support.

const queryBuilder = service.db(params?)

params · KnexAdapterParams

Parameters with optional transaction

result · Knex.QueryBuilder

Query builder for the configured table

createQuery

Create a Knex query from Feathers parameters.

const query = service.createQuery(params)

result · Knex.QueryBuilder

Configured query builder with all filters applied

knexify

Convert a Feathers query object to Knex where clauses.

service.knexify(queryBuilder, query)

queryBuilder · Knex.QueryBuilder · required

The Knex query builder to modify

query · Query · required

Feathers query object

Query Syntax

Standard Operators

// Comparison operators
await service.find({
  query: {
    age: { $gt: 18 },          // Greater than
    score: { $gte: 90 },       // Greater than or equal
    status: { $ne: 'deleted' }, // Not equal
    role: { $in: ['admin', 'moderator'] } // In array
  }
})

// Like operators (case-sensitive)
await service.find({
  query: {
    name: { $like: '%john%' },
    email: { $notlike: '%spam%' }
  }
})

// Case-insensitive like (PostgreSQL)
await service.find({
  query: {
    name: { $ilike: '%john%' }
  }
})

Logical Operators

// $or queries
await service.find({
  query: {
    $or: [
      { status: 'active' },
      { role: 'admin' }
    ]
  }
})

// $and queries
await service.find({
  query: {
    $and: [
      { age: { $gte: 18 } },
      { age: { $lt: 65 } }
    ]
  }
})

// Nested logical operators
await service.find({
  query: {
    status: 'active',
    $or: [
      { role: 'admin' },
      { $and: [
        { age: { $gte: 18 } },
        { verified: true }
      ]}
    ]
  }
})

Special Query Parameters

await service.find({
  query: {
    status: 'active',
    $select: ['id', 'name', 'email'],  // Select specific columns
    $sort: { createdAt: -1, name: 1 }, // Sort descending, then ascending
    $limit: 25,                         // Limit results
    $skip: 50                           // Offset for pagination
  }
})

Extended Operators

Add custom SQL operators:

const service = new KnexService({
  Model: knex,
  name: 'users',
  extendedOperators: {
    '$regexp': '~',      // PostgreSQL regex
    '$iregexp': '~*'     // PostgreSQL case-insensitive regex
  }
})

await service.find({
  query: {
    email: { $regexp: '^[a-z]+@example\\.com$' }
  }
})

Transactions

The Knex adapter supports database transactions via hooks.

Transaction Hooks

import { transaction } from '@feathersjs/knex'

app.service('users').hooks({
  before: {
    create: [transaction.start()],
    update: [transaction.start()],
    patch: [transaction.start()],
    remove: [transaction.start()]
  },
  after: {
    create: [transaction.end()],
    update: [transaction.end()],
    patch: [transaction.end()],
    remove: [transaction.end()]
  },
  error: {
    create: [transaction.rollback()],
    update: [transaction.rollback()],
    patch: [transaction.rollback()],
    remove: [transaction.rollback()]
  }
})

Manual Transactions

const trx = await knex.transaction()

try {
  await service.create(data1, {
    transaction: { trx }
  })
  
  await service.patch(id, data2, {
    transaction: { trx }
  })
  
  await trx.commit()
} catch (error) {
  await trx.rollback()
  throw error
}

Nested Transactions

Transaction hooks support nesting automatically:

// Parent transaction
await service.create(userData, params)
  // Creates child transaction when calling another service
  await otherService.create(relatedData, params)

Example

import { KnexService } from '@feathersjs/knex'
import knex from 'knex'

interface User {
  id?: number
  email: string
  name: string
  age: number
  createdAt?: Date
}

const db = knex({
  client: 'pg',
  connection: {
    host: 'localhost',
    port: 5432,
    user: 'postgres',
    password: 'password',
    database: 'myapp'
  }
})

class UserService extends KnexService<User> {
  async find(params: any) {
    // Add custom query logic
    params.query = params.query || {}
    params.query.deletedAt = null
    
    return super.find(params)
  }
}

const users = new UserService({
  Model: db,
  name: 'users',
  schema: 'public',
  paginate: {
    default: 20,
    max: 100
  }
})

// Create a user
const user = await users.create({
  email: '[e-mail]',
  name: 'Test User',
  age: 25
})

// Find with complex query
const results = await users.find({
  query: {
    $or: [
      { age: { $gte: 18 } },
      { role: 'admin' }
    ],
    status: 'active',
    $sort: { createdAt: -1 },
    $limit: 10
  }
})