Repository files navigation

Important: This module is unmaintained since Objection.js is no longer being actively developed. SeeVincit/objection.js#2335

Feathers database adapter forObjection.js, an ORM based onKnexJS SQL query builder for Postgres, Redshift, MSSQL, MySQL,MariaDB, SQLite3, and Oracle.

npm install --save feathers-objectionnpm install --save objectionnpm install --save knex

Then add one of the following:

npm install --save pgnpm install --save sqlite3npm install --save mysqlnpm install --save mysql2npm install --save oraclenpm install --save mssql

If you want to use a MariaDB instance, you can use the mysql driver.

Usefeathers generate service command to generate a newObjection service.

Please refer to theFeathers database adapter documentationfor more details or directly at:

• Querying - Thecommon adapter querying mechanism
• Pagination and Sorting -How to use pagination and sorting for the database adapter
• Extending -How to extend a database adapter

Refer to the officialObjection.js documention.

It works like theKnex 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');constknex=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. Usearray of strings for composite primary keys.

• events (optional) - List ofcustom service eventssent by this service.

• paginate (optional) -Pagination objectcontaining adefault andmax page size.

• multi (optional) - Allowcreate with arrays andupdate andremovewithidnull to change multiple items. Can betrue for all methods or anarray 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). SeewithSchema documentation.

Starting at version 2.0.0feathers-objection converts queries securely. If youwant to support additional Objection operators, thewhitelist service optioncan contain an array of additional allowed operators. By default, supportedoperators 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 inFeathers services, instead of relying on hooks.

Note that all this eager related options are optional.

• allowedEager - relation expression to limit the allowed eager queries inthe service. Defaults to'[]', meaning no eager queries allowed. SeeallowGraphdocumentation.

• eagerOptions - options object to use with$eager and$joinEager query operators.SeeGraphOptionsdocumentation.

• eagerFilters - option to impose compulsory eager filter. It takes anobject or array of objects with the following properties:

  • expression - the relation expression that the filter will be applied.
  • filter - the filter function. It usesmodifyGraphinternally.

• $modify - modifiers allow you to easily reuse snippets of query logic. you can pass arguments and usemultiple modifiers. value can be one of the following:

  • String with comma-separated modifier names. e.g.modifier1,modifier2
  • Array or serialized array with modifier name or array of modifier names as the first item. The rest of the array items would be the modifier/s arguments.e.g.['modifier1', arg1, arg2] or[['modifier1', 'modifier2'], arg1, arg2]
  • Object or serialized object with modifiers as keys and their arguments as values. Set modifier's value totrue when it has no arguments.e.g.{ modifier1: [arg1, arg2], modifier2: [arg3, arg4], modifier3: true }

  Seemodify documentation.

• $eager - eager load relations defined in models'relationMappings getter methods. SeewithGraphFetched documentation.

• $joinRelation - filter based on a relation's field. use with$eager to also fetch the relation. SeejoinRelateddocumentation.

• $leftJoinRelation - filter based on a relation's field using LEFT JOIN. use with$eager to also fetch the relation. SeeleftJoinRelateddocumentation.

• $joinEager - filter based on a relation's field. SeewithGraphJoineddocumentation.

• $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 ofref 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 } }). Seeref documentation.

• $select - add SELECT statement with given array of column names, e.g.['name', 'ref(jsonb:a)', 'ref(jsonb:a) as a']. SeeselectandFieldExpression documentation.

• $sort - add an order by clause to the query, e.g.query: { $sort: { a: 1, 'b.c': -1, 'ref(jsonb:a)': 1 } }. SeeFieldExpression 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. Seetransactiondocumentation.

• atomic - whentrue 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 owntransaction object in params.

• mergeAllowEager - Will merge the given expression to the existing expression from theallowEager service option.SeeallowGraphdocumentation.

• eagerOptions - Options object to use with$eager and$joinEager query operators.merges on top of theeagerOptions service option.SeeGraphOptionsdocumentation.

• schema - Database schema to use with the query (e.g.public)SeewithSchemadocumentation.

• modifierFiltersResults - whenfalse thetotal count of afind() query is calculated from the original result set, ignoring thecount 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 withgroupBy and the result count. Seethis issue for a detailed explanation.

Composite primary keys can be passed as theid argument using the followingmethods:

• String with values separated by theidSeparator property (order matter,recommended for REST)
• JSON array (order matter, recommended for internal service calls)
• JSON object (more readable, recommended for internal service calls)

When calling a service method with theid argument, all primary keys arerequired to be passed.

• idSeparator - (optional) separator char to separate composite primarykeys in theid 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 willbe 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 theupsertGraph method. Seeexamples for a better explanation.

Runs onupdate andpatch service methods whenid is set. When thedata object also containsid, then both must be the same or an error is thrown.

• allowedUpsert - relation expression to allow relations to be upsertedalong with update. Defaults tonull, meaning relations will not beautomatically upserted unless specified here. SeeallowGraphdocumentation.
• upsertGraphOptions - SeeupsertGraphOptionsdocumentation.
• createUseUpsertGraph - If set totrue, Graph Upsert will also be usedfor.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, alongwith adding a new client which is a relationship for companies. The clientwithout the ID would be inserted and related. The client with the ID will justbe updated (if there are any changes at all).

• mergeAllowUpsert - Merge given expression intoallowedUpsert.
• mergeUpsertGraphOptions - Merge given options intoupsertGraphOptions.

Arbitrary relation graphs can be inserted using the insertGraph method. Providesthe 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 createdalong with insert. Defaults tonull, meaning relations will not beautomatically created unless specified here. SeeallowGraphdocumentation.
• insertGraphOptions - SeeinsertGraphOptionsdocumentation.

• mergeAllowInsert - Merge given expression intoallowedInsert.
• mergeInsertGraphOptions - Merge given options intoinsertGraphOptions.

Create a transactionobject and pass it to series of service calls using thetransaction params operator.
Commit the transaction by callingawait transaction.trx.commit().
Rollback by callingawait transaction.trx.rollback().

users.service.js

constcreateService=require('feathers-objection');constcreateModel=require('../../models/users.model');consthooks=require('./users.hooks');module.exports=function(app){constModel=createModel(app);constpaginate=app.get('paginate');constoptions={model:Model,    paginate,whitelist:['$eager','$joinRelation'],allowedEager:'todos'};app.use('/users',createService(options));constservice=app.service('users');service.hooks(hooks);};

todos.service.js

constcreateService=require('feathers-objection');constcreateModel=require('../../models/todos.model');consthooks=require('./todos.hooks');module.exports=function(app){constModel=createModel(app);constpaginate=app.get('paginate');constoptions={model:Model,    paginate,whitelist:['$eager','$joinRelation'],allowedEager:'[user, subtask]',eagerFilters:[{expression:'subtask',filter:function(builder){builder.where('archived',true);}}]};app.use('/todos',createService(options));constservice=app.service('todos');service.hooks(hooks);};

Use eager queries as follows:

// Get all todos and their unfinished tasksapp.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)'}});

Seethis articlefor more information.

Objection requires you to defineModels withJSON Schema format for your tables:

users.model.js

const{ Model}=require('objection');classUserextendsModel{staticgettableName(){return'user';}staticgetjsonSchema(){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'}}}};}staticgetrelationMappings(){constTodo=require('./todos.model')();return{todos:{relation:Model.HasManyRelation,modelClass:Todo,join:{from:'user.id',to:'todo.userId'}}};}staticgetmodifiers(){return{active:builder=>{builder.where('status','active');}};}$beforeInsert(){this.createdAt=this.updatedAt=newDate().toISOString();}$beforeUpdate(){this.updatedAt=newDate().toISOString();}}module.exports=function(app){if(app){constdb=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));}returnUser;};module.exports=User;

todos.model.js

const{ Model}=require('objection');classTodoextendsModel{staticsetup(app){this.app=app;}staticgettableName(){return'todo';}staticgetjsonSchema(){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'}}};}staticgetrelationMappings(){constUser=require('./users.model')();return{user:{relation:Model.BelongsToOneRelation,modelClass:User,join:{from:'todo.userId',to:'user.id'}}};}staticgetmodifiers(){constknex=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=newDate().toISOString();}$beforeUpdate(){this.updatedAt=newDate().toISOString();}}module.exports=function(app){if(app){Todo.setup(app);constdb=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));}returnTodo;};

Here's a complete example of a Feathers server with atodos SQLite service:

$ npm install @feathersjs/feathers @feathersjs/express body-parser feathers-objection objection knex sqlite3

app.js

constfeathers=require('@feathersjs/feathers');constexpress=require('@feathersjs/express');constrest=require('@feathersjs/express/rest');consterrorHandler=require('@feathersjs/express/errors');constbodyParser=require('body-parser');constcreateService=require('feathers-objection');const{ Model}=require('objection');constknex=require('knex')({client:'sqlite3',connection:{filename:'./db.sqlite'},useNullAsDefault:false});// Bind Objection.jsModel.knex(knex);// Clean up our data. This is optional and is here// because of our integration testsknex.schema.dropTableIfExists('todo').then(function(){console.log('Dropped todo table');// Initialize your tablereturnknex.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.constapp=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 ModelclassTodoextendsModel{staticgettableName(){return'todo';}staticgetjsonSchema(){return{type:'object',required:['text'],properties:{id:{type:'integer'},text:{type:'string'},complete:{type:'boolean',default:false}}};}$beforeInsert(){this.createdAt=this.updatedAt=newDate().toISOString();}$beforeUpdate(){this.updatedAt=newDate().toISOString();}}// Create Objection Feathers service with a default page size of 2 items// and a maximum size of 4app.use('/todos',createService({model:Todo,id:'id',paginate:{default:2,max:4}}));// Handle Errorsapp.use(errorHandler());// Start the servermodule.exports=app.listen(3030);console.log('Feathers Todo Objection service running on 127.0.0.1:3030');

Run theexample app withnpm run example and go tolocalhost:3030/todos.

You should see an empty array. That's because you don't have any Todos yet, butyou now have full CRUD for your new todos service!

Knex Migration CLI can be used to manage DB migrationsand toseed a table with mock data.

As of version 4.8.0,feathers-objection only throwsFeathers Errorswith the message.
On the server, the original error can be retrieved through a secure symbol viaerror[require('feathers-objection').ERROR].

const{ERROR}=require('feathers-objection');try{awaitobjectionService.doSomething();}catch(error){// error is a FeathersError with just the message// Safely retrieve the original errorconstoriginalError=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 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:

• All methods allow additional query parameters
• Multiple updates are disabled by default (see themulti option)
• Objection related operators are disabled by default (see thewhitelistoption)

feathers-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 removed
• namedEagerFilters service option was removed. use Model'smodifiers instead
• Model'snamedFilters property was renamed tomodifiers

feathers-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 operations

The following breaking changes have been introduced:

• Graph upsert now requires thatid fields in thedata object will match theid argument
• $noSelect now always return the input data
• $select is now honored with upsert methods
• patch method now enforceparams.query with upsert
• NotFound error will be thrown whenget &update methods are called with different values inid &params.query.id

feathers-objection 7.0.0 comes with improved error handling.

The following breaking changes have been introduced:

• All Databases will return the same types of errors based on the underlying Objection error
• SQL Driver error text is no longer used as the Feathers error message
• Objection errors are mapped more accurately to Feathers errors, e.g.
  • Objection'sUniqueViolationError -> Feathers'Conflict error type

Copyright © 2020

Licensed under theMIT license.