Home

Awesome

feathers-objection

Build Status Coverage Status js-semistandard-style Dependency Status npm

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

Installation

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.

Feathers CLI

Use feathers generate service command to generate a new Objection service.

Documentation

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.

Initializing the Library

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);
};

Service Options

Default Query Operators

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

Eager queries is one way of solving the SQL database relational model in Feathers services, instead of relying on hooks.

Service Options

Note that all this eager related options are optional.

Query Operators

Params Operators

Composite primary keys

Composite primary keys can be passed as the id argument using the following methods:

When calling a service method with the id argument, all primary keys are required to be passed.

Service Options

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

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' }
  }
});

Graph upsert

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.

Service Options

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).

Params Operators

Graph insert

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.

Service Options

Params Operators

Transaction

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().

Service

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.

Models

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;
};

Complete Example

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!

DB migrations

Knex Migration CLI can be used to manage DB migrations and to seed a table with mock data.

Error handling

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.

Migrating to feathers-objection v2

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:

Migrating to feathers-objection v5

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:

Migrating to feathers-objection v6

feathers-objection 6.0.0 comes with usability and security updates

The following breaking changes have been introduced:

Migrating to feathers-objection v7

feathers-objection 7.0.0 comes with improved error handling.

The following breaking changes have been introduced:

License

Copyright © 2020

Licensed under the MIT license.