Knex SQL Adapter - Feathers

The @feathersjs/knex adapter provides SQL database integration through Knex.js, supporting PostgreSQL, MySQL, SQLite, MSSQL, Oracle, and other SQL databases with transactions, joins, and advanced querying.

Installation

Install dependencies

Install the Knex adapter, Knex.js, and your database driver:

PostgreSQL
MySQL
SQLite
MSSQL

npm install @feathersjs/knex knex pg

npm install @feathersjs/knex knex mysql2

npm install @feathersjs/knex knex sqlite3

npm install @feathersjs/knex knex mssql

Configure Knex

Create a Knex instance:

import knex from 'knex'

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

Create a service

Create a service using the Knex adapter:

import { KnexService } from '@feathersjs/knex'

class UserService extends KnexService {
  // Custom methods here
}

app.use('users', new UserService({
  Model: db,
  name: 'users',
  paginate: {
    default: 10,
    max: 50
  }
}))

Configuration

Service Options

The Knex adapter requires these options:

interface KnexAdapterOptions {
  Model: Knex                    // Knex instance
  name: string                   // Table name
  id?: string                    // Primary key (default: 'id')
  schema?: string                // Database schema
  paginate?: PaginationOptions   // Pagination settings
  multi?: boolean | string[]     // Allow multi operations
  events?: string[]              // Custom events
  extendedOperators?: object     // Custom query operators
}

Example Configuration

import { KnexService } from '@feathersjs/knex'

app.use('messages', new KnexService({
  Model: db,
  name: 'messages'
}))

Querying

Basic Queries

The Knex adapter supports all standard Feathers query operators:

// Find all users
const users = await app.service('users').find({
  query: {}
})

// Find with filters
const activeUsers = await app.service('users').find({
  query: {
    status: 'active',
    age: { $gte: 18 }
  }
})

SQL-Specific Operators

The Knex adapter includes SQL-specific operators:

// Case-sensitive pattern matching
const users = await app.service('users').find({
  query: {
    name: { $like: '%John%' }
  }
})

// NOT LIKE
const users = await app.service('users').find({
  query: {
    email: { $notlike: '%spam.com' }
  }
})

Raw Knex Queries

Pass custom Knex query builders:

import { KnexAdapterParams } from '@feathersjs/knex'

// Use custom Knex query
const params: KnexAdapterParams = {
  knex: db('users')
    .select('users.*', 'profiles.bio')
    .leftJoin('profiles', 'users.id', 'profiles.userId')
    .where('users.status', 'active')
}

const results = await app.service('users').find(params)

Data Manipulation

Create

const user = await app.service('users').create({
  name: 'Alice',
  email: 'alice@example.com',
  age: 25
})

Update

// Replace entire row (except ID)
const updated = await app.service('users').update(
  1,
  {
    name: 'Alice Updated',
    email: 'alice.new@example.com',
    age: 26
  }
)

Patch

// Partial update
const patched = await app.service('users').patch(
  1,
  { status: 'verified' }
)

Remove

const removed = await app.service('users').remove(1)

Transactions

The Knex adapter provides powerful transaction support:

Using Transaction Hooks

import { transaction } from '@feathersjs/knex'

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

// Transaction automatically managed
const account = await app.service('accounts').create({
  balance: 1000
})

Transaction Context

Transactions maintain context across hooks:

app.service('transfers').hooks({
  before: {
    create: [
      transaction.start(),
      async (context) => {
        const { fromAccount, toAccount, amount } = context.data
        
        // Deduct from source account (uses transaction)
        await app.service('accounts').patch(
          fromAccount,
          { balance: db.raw('balance - ?', [amount]) },
          context.params  // Transaction passed automatically
        )
        
        // Add to destination account (uses same transaction)
        await app.service('accounts').patch(
          toAccount,
          { balance: db.raw('balance + ?', [amount]) },
          context.params
        )
        
        return context
      }
    ]
  },
  after: {
    create: [transaction.end()]
  },
  error: {
    create: [transaction.rollback()]
  }
})

Advanced Features

Table Schemas

Create and manage database schemas:

// Create table
await db.schema.createTable('users', (table) => {
  table.increments('id').primary()
  table.string('email').unique().notNullable()
  table.string('name').notNullable()
  table.integer('age')
  table.string('status').defaultTo('active')
  table.timestamps(true, true)
})

// Add indexes
await db.schema.alterTable('users', (table) => {
  table.index('email')
  table.index(['status', 'createdAt'])
})

Joins and Relations

Perform joins using custom Knex queries:

const usersWithProfiles = await app.service('users').find({
  knex: db('users')
    .select(
      'users.id',
      'users.name',
      'users.email',
      'profiles.bio',
      'profiles.avatar'
    )
    .leftJoin('profiles', 'users.id', 'profiles.userId')
    .where('users.status', 'active')
})

Field Selection

// Select specific fields
const users = await app.service('users').find({
  query: {
    $select: ['id', 'name', 'email'],
    status: 'active'
  }
})

// The adapter automatically includes the ID field

Type Safety

Full TypeScript support with generics:

import { KnexService, KnexAdapterParams } from '@feathersjs/knex'
import type { Params } from '@feathersjs/feathers'
import type { Knex } from 'knex'

interface User {
  id: number
  email: string
  name: string
  age: number
  status: 'active' | 'inactive'
  createdAt: Date
  updatedAt: Date
}

interface UserData {
  email: string
  name: string
  age: number
  status?: 'active' | 'inactive'
}

interface UserParams extends KnexAdapterParams {
  user?: User
}

class UserService extends KnexService<User, UserData, UserParams> {
  async find(params?: UserParams) {
    // Fully typed
    return super.find(params)
  }
}

const db: Knex = knex({ /* config */ })

app.use('users', new UserService({
  Model: db,
  name: 'users'
}))

// Type-safe usage
const users: User[] = await app.service('users').find()
const user: User = await app.service('users').get(1)

Error Handling

The adapter converts SQL errors to Feathers errors:

import { errorHandler } from '@feathersjs/knex'

try {
  await app.service('users').create({
    email: 'duplicate@example.com'
  })
} catch (error) {
  // SQL errors are converted:
  // Unique constraint -> Conflict (409)
  // Foreign key -> BadRequest (400)
  // Not found -> NotFound (404)
}

Connection Pooling

Configure connection pooling for better performance:

const db = knex({
  client: 'pg',
  connection: process.env.DATABASE_URL,
  pool: {
    min: 2,
    max: 10,
    createTimeoutMillis: 3000,
    acquireTimeoutMillis: 30000,
    idleTimeoutMillis: 30000,
    reapIntervalMillis: 1000,
    createRetryIntervalMillis: 100
  }
})

// Clean up on shutdown
process.on('SIGTERM', async () => {
  await db.destroy()
})

Next Steps

MongoDB Adapter

Learn about the MongoDB adapter

Common Patterns

Explore adapter patterns

Hooks

Add hooks to your services

Validation

Validate your data with schemas

Documentation Index

​Installation

​Configuration

​Service Options

​Example Configuration

​Querying

​Basic Queries

​SQL-Specific Operators

​Raw Knex Queries

​Data Manipulation

​Create

​Update

​Patch

​Remove

​Transactions

​Using Transaction Hooks

​Transaction Context

​Advanced Features

​Table Schemas

​Joins and Relations

​Field Selection

​Type Safety

​Error Handling

​Connection Pooling

​Next Steps