feathers-objection
Advanced tools
Feathers database adapter for Objection.js, an ORM based on KnexJS SQL query builder for Postgres, Redshift, MSSQL, MySQL, MariaDB, SQLite3, and Oracle.
npm install --save feathers-objection
npm install --save objection
npm install --save knex
Then add one of the following:
npm install --save pg
npm install --save sqlite3
npm install --save mysql
npm install --save mysql2
npm install --save oracle
npm install --save mssql
If you want to use a MariaDB instance, you can use the mysql driver.
Use feathers generate service command to generate a new Objection service.
Please refer to the Feathers database adapter documentation for more details or directly at:
Refer to the official Objection.js documention.
It works like the Knex service adapter, except it has all the benefits of the Objection ORM.
config/defaults.json
{
"mysql": {
"client": "mysql2",
"connection": {
"host": "mysql.example.com",
"user": "root",
"password": "secret",
"database": "example"
}
}
}
objection.js
const { Model } = require('objection');
module.exports = function(app) {
const { client, connection } = app.get('mysql');
const knex = require('knex')({ client, connection, useNullAsDefault: false });
Model.knex(knex);
app.set('knex', knex);
};
model (required) - The Objection model definition.
id (optional, default: model.idColumn or 'id') - The name of the id field property. Use
array of strings for composite primary keys.
events (optional) - List of
custom service events
sent by this service.
paginate (optional) - Pagination object
containing a default and max page size.
multi (optional) - Allow create with arrays and update and remove
with id null to change multiple items. Can be true for all methods or an
array of allowed methods (e.g. [ 'remove', 'create' ]).
whitelist (optional) - List of additional query operators to allow (e.g.
[ '$eager', '$joinRelation' ]).
schema (optional) - Database schema to use with all the service queries (e.g.
public). See withSchema documentation.
Starting at version 2.0.0 feathers-objection converts queries securely. If you
want to support additional Objection operators, the whitelist service option
can contain an array of additional allowed operators. By default, supported
operators are:
'$eq',
'$ne',
'$gte',
'$gt',
'$lte',
'$lt',
'$in',
'$nin',
'$like',
'$notLike',
'$ilike',
'$notILike',
'$or',
'$and',
'$sort',
'$not'
Eager queries is one way of solving the SQL database relational model in Feathers services, instead of relying on hooks.
Note that all this eager related options are optional.
allowedEager - relation expression to limit the allowed eager queries in
the service. Defaults to '[]', meaning no eager queries allowed. See
allowGraph
documentation.
eagerOptions - options object to use with $eager and $joinEager query operators.
See GraphOptions
documentation.
eagerFilters - option to impose compulsory eager filter. It takes an
object or array of objects with the following properties:
expression - the relation expression that the filter will be applied.filter - the filter function. It uses
modifyGraph
internally.$modify - modifiers allow you to easily reuse snippets of query logic. you can pass arguments and use
multiple modifiers. value can be one of the following:
modifier1,modifier2['modifier1', arg1, arg2] or [['modifier1', 'modifier2'], arg1, arg2]true when it has no arguments.
e.g. { modifier1: [arg1, arg2], modifier2: [arg3, arg4], modifier3: true }See modify documentation.
$eager - eager load relations defined in models' relationMappings getter methods. See
withGraphFetched documentation.
$joinRelation - filter based on a relation's field. use with $eager to also fetch the relation. See
joinRelated
documentation.
$leftJoinRelation - filter based on a relation's field using LEFT JOIN. use with $eager to also fetch the relation. See
leftJoinRelated
documentation.
$joinEager - filter based on a relation's field. See
withGraphJoined
documentation.
$modifyEager - filter relation based on a relation's field. does not support JSON fields.
e.g. companies.find({ query: { $eager: 'employees', $modifyEager: { employees: { name: 'John' } } } })
$mergeEager - merge an eager expression to $eager,
e.g. companies.find({ query: { $eager: 'employees', $mergeEager: 'ceos' } })
$allowRefs - allow the usage of ref keyword to reference another field. Reference a relation's field using $joinEager or $joinRelation,
e.g. companies.find({ query: { name: 'ref(size)', $allowRefs: true } }), employees.find({ query: { $joinEager: 'company', 'company.name': 'ref(employees.name)', $allowRefs: true } }). See
ref documentation.
$select - add SELECT statement with given array of column names, e.g. ['name', 'ref(jsonb:a)', 'ref(jsonb:a) as a']. See
select
and FieldExpression documentation.
$sort - add an order by clause to the query, e.g. query: { $sort: { a: 1, 'b.c': -1, 'ref(jsonb:a)': 1 } }. See
FieldExpression documentation.
$noSelect - skips SELECT queries in create, patch & remove requests. response data will be based on the input data.
$null - filter based on if a column is NULL with REST support, e.g. companies.find({ query: { ceo: { $null: false } } }), companies.find({ query: { ceo: { $null: 'false' } } })
$not - filter based on if a query is NOT true. It can be used with an object $not: { name: { $in: ['craig', 'tim'] } } or array $not: [ { $id: 1 }, { $id: 2 } ]
$between - filter based on if a column value is between range of values
$notBetween - filter based on if a column value is not between range of values
$like - filter column value based on a LIKE pattern
$notLike - filter column value based on a NOT LIKE pattern
$regexp - filter column value based on a REGEXP pattern
$notRegexp - filter column value based on a NOT REGEXP pattern
$ilike - (Postgres) filter column value based on a case-insensitive LIKE pattern
$notILike - (Postgres) filter column value based on a case-insensitive NOT LIKE pattern
$iRegexp - (Postgres) filter column value based on a case-insensitive REGEXP pattern
$notIRegexp - (Postgres) filter column value based on a case-insensitive NOT REGEXP pattern
$containsKey (Postgres) - filter based on if a column contains a key
$any (Postgres) - filter based on if a column contains any key from array of strings
$all (Postgres) - filter based on if a column contains all keys from array of strings
$contains (Postgres) - filter based on if a column contains all values from array of values
$contained (Postgres) - filter based on if a column is contained within a serialized object
transaction - A transaction object. See
transaction
documentation.
atomic - when true ensure that multi create or graph insert/upsert success or fail all at once. Under the hood, automatically create a transaction and commit on success or rollback on partial or total failure. Ignored if you added your own transaction object in params.
mergeAllowEager - Will merge the given expression to the existing expression from the allowEager service option.
See allowGraph
documentation.
eagerOptions - Options object to use with $eager and $joinEager query operators.
merges on top of the eagerOptions service option.
See GraphOptions
documentation.
schema - Database schema to use with the query (e.g. public)
See withSchema
documentation.
modifierFiltersResults - when false the total count of a find() query is calculated from the original result set, ignoring the count of any $modify query.
The default behaviour is to apply the count of the modifier to the result total, assuming that the modifier may influence the result total by filtering the result set. This can be used to workaround issues with groupBy and the result count. See this issue for a detailed explanation.
Composite primary keys can be passed as the id argument using the following
methods:
idSeparator property (order matter,
recommended for REST)When calling a service method with the id argument, all primary keys are
required to be passed.
idSeparator - (optional) separator char to separate composite primary
keys in the id argument of get/patch/update/remove external service calls.
Defaults to ','.app.use('/user-todos', service({
id: ['userId', 'todoId'],
idSeparator: ','
})
app.service('/user-todos').get('1,2')
app.service('/user-todos').get([1, 2])
app.service('/user-todos').get({ userId: 1, todoId: 2 })
JSON column will be automatically converted from and to JS object/array and will be saved as text in unsupported databases. it must be defined in the model class.
Query against a JSON column in PostgresSQL:
app.service('companies').find({
query: {
obj: { stringField: 'string' }
}
});
app.service('companies').find({
query: {
obj: { numberField: 1.5 }
}
});
app.service('companies').find({
query: {
obj: { numberField: { $gt: 1.5 } }
}
});
app.service('companies').find({
query: {
obj: { 'objectField.object': 'string in obj.objectField.object' }
}
});
app.service('companies').find({
query: {
obj: { 'arrayField(0).object': 'string in obj.arrayField[0].object' }
}
});
app.service('companies').find({
query: {
arr: { '(0).objectField.object': 'string in arr[0].objectField.object' }
}
});
app.service('companies').find({
query: {
obj: { "(field.WithDot)": 'string' }
}
});
Arbitrary relation graphs can be upserted (insert + update + delete) using the
upsertGraph method. See
examples for a better explanation.
Runs on update and patch service methods when id is set. When the data object also contains id, then both must be the same or an error is thrown.
allowedUpsert - relation expression to allow relations to be upserted
along with update. Defaults to null, meaning relations will not be
automatically upserted unless specified here. See
allowGraph
documentation.upsertGraphOptions - See
upsertGraphOptions
documentation.createUseUpsertGraph - If set to true, Graph Upsert will also be used
for .create(data, params) method instead of Graph Insert.app.use('/companies', service({
model: Company,
allowedEager: 'clients',
allowedUpsert: 'clients'
})
app.service('/companies').update(1, {
name: 'New Name',
clients: [{
id: 100,
name: 'Existing Client'
}, {
name: 'New Client'
}]
})
In the example above, we are updating the name of an existing company, along with adding a new client which is a relationship for companies. The client without the ID would be inserted and related. The client with the ID will just be updated (if there are any changes at all).
mergeAllowUpsert - Merge given expression into allowedUpsert.mergeUpsertGraphOptions - Merge given options into upsertGraphOptions.Arbitrary relation graphs can be inserted using the insertGraph method. Provides the ability to relate the inserted object with its associations.
Runs on the .create(data, params) service method.
allowedInsert - relation expression to allow relations to be created
along with insert. Defaults to null, meaning relations will not be
automatically created unless specified here. See
allowGraph
documentation.insertGraphOptions - See
insertGraphOptions
documentation.mergeAllowInsert - Merge given expression into allowedInsert.mergeInsertGraphOptions - Merge given options into insertGraphOptions.Create a transaction
object and pass it to series of service calls using the transaction params operator.
Commit the transaction by calling await transaction.trx.commit().
Rollback by calling await transaction.trx.rollback().
users.service.js
const createService = require('feathers-objection');
const createModel = require('../../models/users.model');
const hooks = require('./users.hooks');
module.exports = function(app) {
const Model = createModel(app);
const paginate = app.get('paginate');
const options = {
model: Model,
paginate,
whitelist: ['$eager', '$joinRelation'],
allowedEager: 'todos'
};
app.use('/users', createService(options));
const service = app.service('users');
service.hooks(hooks);
};
todos.service.js
const createService = require('feathers-objection');
const createModel = require('../../models/todos.model');
const hooks = require('./todos.hooks');
module.exports = function(app) {
const Model = createModel(app);
const paginate = app.get('paginate');
const options = {
model: Model,
paginate,
whitelist: ['$eager', '$joinRelation'],
allowedEager: '[user, subtask]',
eagerFilters: [
{
expression: 'subtask',
filter: function(builder) {
builder.where('archived', true);
}
}
]
};
app.use('/todos', createService(options));
const service = app.service('todos');
service.hooks(hooks);
};
Use eager queries as follows:
// Get all todos and their unfinished tasks
app.service('/todos').find({
query: {
$eager: 'subtask(unDone)'
}
});
// Get all todos of an active user with firstName 'John'
app.service('/todos').find({
query: {
'user.firstName': 'John',
$eager: 'user(active)',
$joinRelation: 'user(active)'
}
});
See this article for more information.
Objection requires you to define Models with JSON Schema format for your tables:
users.model.js
const { Model } = require('objection');
class User extends Model {
static get tableName() {
return 'user';
}
static get jsonSchema() {
return {
type: 'object',
required: ['firstName', 'lastName'],
properties: {
id: { type: 'integer' },
firstName: { type: 'string', maxLength: 45 },
lastName: { type: 'string', maxLength: 45 },
status: {
type: 'string',
enum: ['active', 'disabled'],
default: 'active'
},
address: {
type: 'object',
properties: {
street: { type: 'string' },
city: { type: 'string' },
zipCode: { type: 'string' }
}
},
list: {
type: 'array',
maxItems: 3,
items: { type: 'string' }
}
}
};
}
static get relationMappings() {
const Todo = require('./todos.model')();
return {
todos: {
relation: Model.HasManyRelation,
modelClass: Todo,
join: {
from: 'user.id',
to: 'todo.userId'
}
}
};
}
static get modifiers() {
return {
active: builder => {
builder.where('status', 'active');
}
};
}
$beforeInsert() {
this.createdAt = this.updatedAt = new Date().toISOString();
}
$beforeUpdate() {
this.updatedAt = new Date().toISOString();
}
}
module.exports = function(app) {
if (app) {
const db = app.get('knex');
db.schema
.hasTable('user')
.then(exists => {
if (!exists) {
db.schema
.createTable('user', table => {
table.increments('id');
table.string('firstName', 45);
table.string('lastName', 45);
table.enum('status', ['active', 'disabled']).defaultTo('active');
table.timestamp('createdAt');
table.timestamp('updatedAt');
})
.then(() => console.log('Created user table'))
.catch(e => console.error('Error creating user table', e));
}
})
.catch(e => console.error('Error creating user table', e));
}
return User;
};
module.exports = User;
todos.model.js
const { Model } = require('objection');
class Todo extends Model {
static setup(app) {
this.app = app;
}
static get tableName() {
return 'todo';
}
static get jsonSchema() {
return {
type: 'object',
required: ['userId', 'text'],
properties: {
id: { type: 'integer' },
userId: { type: 'integer' },
text: { type: 'string', maxLength: 500 },
complete: { type: 'boolean', default: false },
dueDate: { type: 'string', format: 'date-time' }
}
};
}
static get relationMappings() {
const User = require('./users.model')();
return {
user: {
relation: Model.BelongsToOneRelation,
modelClass: User,
join: {
from: 'todo.userId',
to: 'user.id'
}
}
};
}
static get modifiers() {
const knex = this.app.get('knex');
return {
unDone: function(builder) {
builder.where('complete', false);
},
overdue: builder => {
builder
.where('complete', false)
.where('dueDate', '<', knex.fn.now());
}
};
}
$beforeInsert() {
this.createdAt = this.updatedAt = new Date().toISOString();
}
$beforeUpdate() {
this.updatedAt = new Date().toISOString();
}
}
module.exports = function(app) {
if (app) {
Todo.setup(app);
const db = app.get('knex');
db.schema
.hasTable('todo')
.then(exists => {
if (!exists) {
db.schema
.createTable('todo', table => {
table.increments('id');
table.integer('userId');
table.string('text', 500);
table.boolean('complete');
table.timestamp('dueDate');
table.timestamp('createdAt');
table.timestamp('updatedAt');
})
.then(() => console.log('Created todo table'))
.catch(e => console.error('Error creating todo table', e));
}
})
.catch(e => console.error('Error creating todo table', e));
}
return Todo;
};
Here's a complete example of a Feathers server with a todos SQLite service:
$ npm install @feathersjs/feathers @feathersjs/express body-parser feathers-objection objection knex sqlite3
app.js
const feathers = require('@feathersjs/feathers');
const express = require('@feathersjs/express');
const rest = require('@feathersjs/express/rest');
const errorHandler = require('@feathersjs/express/errors');
const bodyParser = require('body-parser');
const createService = require('feathers-objection');
const { Model } = require('objection');
const knex = require('knex')({
client: 'sqlite3',
connection: {
filename: './db.sqlite'
},
useNullAsDefault: false
});
// Bind Objection.js
Model.knex(knex);
// Clean up our data. This is optional and is here
// because of our integration tests
knex.schema.dropTableIfExists('todo').then(function() {
console.log('Dropped todo table');
// Initialize your table
return knex.schema.createTable('todo', function(table) {
console.log('Creating todo table');
table.increments('id');
table.string('text');
table.boolean('complete');
table.timestamp('createdAt');
table.timestamp('updatedAt');
});
});
// Create a feathers instance.
const app = express(feathers())
// Enable REST services
.configure(rest())
// Turn on JSON parser for REST services
.use(bodyParser.json())
// Turn on URL-encoded parser for REST services
.use(bodyParser.urlencoded({ extended: true }));
// Create an Objection Model
class Todo extends Model {
static get tableName() {
return 'todo';
}
static get jsonSchema() {
return {
type: 'object',
required: ['text'],
properties: {
id: { type: 'integer' },
text: { type: 'string' },
complete: { type: 'boolean', default: false }
}
};
}
$beforeInsert() {
this.createdAt = this.updatedAt = new Date().toISOString();
}
$beforeUpdate() {
this.updatedAt = new Date().toISOString();
}
}
// Create Objection Feathers service with a default page size of 2 items
// and a maximum size of 4
app.use(
'/todos',
createService({
model: Todo,
id: 'id',
paginate: {
default: 2,
max: 4
}
})
);
// Handle Errors
app.use(errorHandler());
// Start the server
module.exports = app.listen(3030);
console.log('Feathers Todo Objection service running on 127.0.0.1:3030');
Run the example app with npm run example and go to
localhost:3030/todos.
You should see an empty array. That's because you don't have any Todos yet, but you now have full CRUD for your new todos service!
Knex Migration CLI can be used to manage DB migrations and to seed a table with mock data.
As of version 4.8.0, feathers-objection only throws Feathers Errors
with the message.
On the server, the original error can be retrieved through a secure symbol via error[require('feathers-objection').ERROR].
const { ERROR } = require('feathers-objection');
try {
await objectionService.doSomething();
} catch (error) {
// error is a FeathersError with just the message
// Safely retrieve the original error
const originalError = error[ERROR];
}
As of version 7.0.0, feathers-objection has normalized errors accross all databases supported by Objection, and makes a best-effort attempt to provide reasonable error messages that can be returned directly to the client.
If these error messages do not work for your needs, the original error is still available using the symbol described above.
feathers-objection v2feathers-objection 2.0.0 comes with important security and usability updates
Important: For general migration information to the new database adapter functionality see
crow.docs.feathersjs.com/migrating.html#database-adapters
The following breaking changes have been introduced:
multi option)whitelist
option)feathers-objection v5feathers-objection 5.0.0 comes with usability updates and was migrated to use Objection v2
Important: For general migration information to Objection v2 see
https://vincit.github.io/objection.js/release-notes/migration.html
The following breaking changes have been introduced:
$pick query operator was removednamedEagerFilters service option was removed. use Model's modifiers insteadnamedFilters property was renamed to modifiersfeathers-objection v6feathers-objection 6.0.0 comes with usability and security updates
$not operator is now available. It can be used with an object $not: { name: { $in: ["craig", "tim"] } } or array $not: [ { $id: 1 }, { $id: 2 } ]$eager is no longer needed with upsert operationsThe following breaking changes have been introduced:
id fields in the data object will match the id argument$noSelect now always return the input data$select is now honored with upsert methodspatch method now enforce params.query with upsertget & update methods are called with different values in id & params.query.idfeathers-objection v7feathers-objection 7.0.0 comes with improved error handling.
The following breaking changes have been introduced:
UniqueViolationError -> Feathers' Conflict error typeCopyright © 2020
Licensed under the MIT license.
v7.6.0 (2023-04-02)
FAQs
A service plugin for ObjectionJS an ORM based on KnexJS
The npm package feathers-objection receives a total of 612 weekly downloads. As such, feathers-objection popularity was classified as not popular.
We found that feathers-objection demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 4 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Research
Security News
Malicious Koishi plugin silently exfiltrates messages with hex strings to a hardcoded QQ account, exposing secrets in chatbots across platforms.
Research
Security News
Malicious PyPI checkers validate stolen emails against TikTok and Instagram APIs, enabling targeted account attacks and dark web credential sales.
Security News
The Node.js TSC opted not to endorse a feature bounty program, citing concerns about incentives, governance, and project neutrality.