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 adapterName of the id field property
Database schema name (for PostgreSQL, MSSQL)
Pagination settings with default and max page size
Allow multiple updates. Can be true, false, or an array of method names
Use ONLY keyword for table queries (PostgreSQL)
Custom query operators mapping. Example: { '$regexp': '~*' }
Service Methods
find
Retrieve multiple records from the table.
await service.find(params?)
Query filters including $limit, $skip, $sort, $select, and SQL operators
paginate
PaginationOptions | false
Override pagination settings
Pre-configured Knex query builder to extend
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?)
The record id to retrieve
create
Create one or more new records.
await service.create(data, params?)
Record data to create. Arrays are processed sequentially
update
Completely replace a record.
await service.update(id, data, params?)
Complete record data. Fields not provided will be set to null
patch
Partially update one or multiple records.
await service.patch(id, data, params?)
The record id to patch, or null to patch multiple records matching the query
Partial data to merge with existing record(s)
remove
Remove one or multiple records.
await service.remove(id, params?)
The record id to remove, or null to remove multiple
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
Get a Knex query builder for the table, with transaction support.
const queryBuilder = service.db(params?)
Parameters with optional transaction
Query builder for the configured table
createQuery
Create a Knex query from Feathers parameters.
const query = service.createQuery(params)
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 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: '[email protected]',
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
}
})