{"_id":"@arnoldtkl/feathers-knex","_rev":"1-ee76e8c4f2576f7e7c6e8f8f999a2eac","name":"@arnoldtkl/feathers-knex","dist-tags":{"latest":"7.1.1"},"versions":{"7.1.1":{"name":"@arnoldtkl/feathers-knex","description":"A service plugin for KnexJS a query builder for PostgreSQL, MySQL and SQLite3","version":"7.1.1","homepage":"https://github.com/feathersjs-ecosystem/feathers-knex","keywords":["feathers","feathers-plugin"],"licenses":[{"type":"MIT","url":"https://github.com/feathersjs-ecosystem/feathers-knex/blob/master/LICENSE"}],"repository":{"type":"git","url":"git://github.com/feathersjs-ecosystem/feathers-knex.git"},"author":{"name":"Feathers contributors","email":"hello@feathersjs.com","url":"https://feathersjs.com"},"contributors":[],"bugs":{"url":"https://github.com/feathersjs-ecosystem/feathers-knex/issues"},"engines":{"node":">= 6"},"main":"lib/","types":"types","scripts":{"publish":"git push origin --tags && npm run changelog && git push origin","changelog":"github_changelog_generator && git add CHANGELOG.md && git commit -am \"Updating changelog\"","release:patch":"npm version patch && npm publish","release:minor":"npm version minor && npm publish","release:major":"npm version major && npm publish","lint":"semistandard --fix","dtslint":"dtslint types","mocha":"mocha test/","test":"npm run lint && npm run dtslint && npm run coverage","example":"babel-node example/app","coverage":"shx rm -rf *.sqlite && istanbul cover node_modules/mocha/bin/_mocha -- test/"},"semistandard":{"env":["mocha"]},"directories":{"lib":"lib"},"dependencies":{"@feathersjs/adapter-commons":"^4.5.2","@feathersjs/commons":"^4.5.3","@feathersjs/errors":"^4.5.3","debug":"^4.1.1","is-plain-object":"^3.0.1"},"devDependencies":{"@feathersjs/adapter-tests":"^4.5.2","@feathersjs/express":"^4.5.4","@feathersjs/feathers":"^4.5.3","body-parser":"^1.19.0","chai":"^4.2.0","chai-as-promised":"^7.1.1","dtslint":"^3.6.12","istanbul":"^1.1.0-alpha.1","knex":"^0.21.1","loud-rejection":"^2.2.0","mocha":"^8.0.1","semistandard":"^14.2.2","shx":"^0.3.2","sqlite3":"^5.0.0","typescript":"^3.9.6"},"gitHead":"339ff1c36395d6f56717a194e4b30add9acace8f","_id":"@arnoldtkl/feathers-knex@7.1.1","_nodeVersion":"12.11.1","_npmVersion":"6.14.7","dist":{"integrity":"sha512-EwKc6Kvfdoke8Vo3LVbpcsgxUL8NzhyCt82ztXpzzGj6f1F9YUUrlKP2bq7A3xhd7xxai6q/aylPZjhE3FaIZQ==","shasum":"58b81df21df0b20b8def2fc7f947dbd74458f5b2","tarball":"https://registry.npmjs.org/@arnoldtkl/feathers-knex/-/feathers-knex-7.1.1.tgz","fileCount":12,"unpackedSize":72005,"npm-signature":"-----BEGIN PGP SIGNATURE-----\r\nVersion: OpenPGP.js v3.0.13\r\nComment: https://openpgpjs.org\r\n\r\nwsFcBAEBCAAQBQJfcFFBCRA9TVsSAnZWagAAyjIP/jthSDZWe9+KRo2eZsk0\n8RFVo5b1srOJ/uhpDDCblJW9cOrqaeNR7HLShoFUFCl1Yir/MKt7+Qi0tCgv\n8h6nFl+hl/Aq3LtkfMiDN5rbZhSpqGWorJLhSB7ZcFgJQrms7vXubkoCm28D\n2soYjWJh+naijQLMXYR6fZq1w4jS+dmX7MggyIgJam7e+U9wH7xMo1rc7zZg\n4Hh4BfpsZGatgtoj+7EjSZspt2Lta/yzDZJ39QTI31mJNuPpqWyohOlcpY0o\n8wpCA27MSlJocrwzFqLmfqjJZ3IAgCWxTiecTkl6FOPwr2O/ajfHtMgl1V1n\nSdb1t8LyCegxSGz3DG0oXhD8MH+zfB0uQ8Bw9rE80g6qz6WA4hv/lBzeEp4G\nOD57DXKnSI+lU3n/yItwSg+I98C0CkPLraFG7oOnFceq87YiheQHAvSf0HhB\nitU+7Ad8l7o1VLkyQmFVoPeAKZ/KPh5IuJBSYEvb+fy1WUiKmCHLmlQ5n/Qw\nOPf3MJ5MOQTA3pgkAQF3C7x/ghzyPg0eYglGWfEFIaFh/RLObyIVvE4+rzth\n69fQlIHiX8t4ALFJnlixOLBNrBYYB/TxZzqgd4vX6/Bwkry8Y7cIoq18xRMR\nyF9vJbgQugXnhryS3YgjU/iMVH2eGXIMJJDtFY9IiLA1aAxsiNjHhyKhNTQu\nFZyQ\r\n=4hOQ\r\n-----END PGP SIGNATURE-----\r\n","signatures":[{"keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA","sig":"MEUCIFITOu3ShEaez8pj+76KaqlLtaIMIzHHtRMUSoO4/FdoAiEAhuJP7Omhh3gv9XD/6KSz75DGsCGdb6Xexh6B+B+j9xw="}]},"maintainers":[{"name":"arnoldtkl","email":"arnoldtkl@gmail.com"}],"_npmUser":{"name":"arnoldtkl","email":"arnoldtkl@gmail.com"},"_npmOperationalInternal":{"host":"s3://npm-registry-packages","tmp":"tmp/feathers-knex_7.1.1_1601196352763_0.6644571443375946"},"_hasShrinkwrap":false}},"time":{"created":"2020-09-27T08:45:52.727Z","7.1.1":"2020-09-27T08:45:52.852Z","modified":"2022-04-04T15:33:53.041Z"},"maintainers":[{"name":"arnoldtkl","email":"arnoldtkl@gmail.com"}],"description":"A service plugin for KnexJS a query builder for PostgreSQL, MySQL and SQLite3","homepage":"https://github.com/feathersjs-ecosystem/feathers-knex","keywords":["feathers","feathers-plugin"],"repository":{"type":"git","url":"git://github.com/feathersjs-ecosystem/feathers-knex.git"},"contributors":[],"author":{"name":"Feathers contributors","email":"hello@feathersjs.com","url":"https://feathersjs.com"},"bugs":{"url":"https://github.com/feathersjs-ecosystem/feathers-knex/issues"},"readme":"# feathers-knex\n\n[![Greenkeeper badge](https://badges.greenkeeper.io/feathersjs-ecosystem/feathers-knex.svg)](https://greenkeeper.io/)\n\n[![Build Status](https://travis-ci.org/feathersjs-ecosystem/feathers-knex.png?branch=master)](https://travis-ci.org/feathersjs-ecosystem/feathers-knex)\n[![Dependency Status](https://img.shields.io/david/feathersjs-ecosystem/feathers-knex.svg?style=flat-square)](https://david-dm.org/feathersjs-ecosystem/feathers-knex)\n[![Download Status](https://img.shields.io/npm/dm/feathers-knex.svg?style=flat-square)](https://www.npmjs.com/package/feathers-knex)\n\nA database adapter for [KnexJS](http://knexjs.org/), an SQL query builder for Postgres, MSSQL, MySQL, MariaDB, SQLite3, and Oracle.\n\n```bash\nnpm install --save mysql knex feathers-knex\n```\n\n> __Important:__ `feathers-knex` implements the [Feathers Common database adapter API](https://docs.feathersjs.com/api/databases/common.html) and [querying syntax](https://docs.feathersjs.com/api/databases/querying.html).\n\n<!-- -->\n\n> **Note:** You also need to [install the database driver](http://knexjs.org/#Installation-node) for the DB you want to use.\n\n## API\n\n### `service(options)`\n\nReturns a new service instance initialized with the given options.\n\n```js\nconst knex = require('knex');\nconst service = require('feathers-knex');\n\nconst db = knex({\n  client: 'sqlite3',\n  connection: {\n    filename: './db.sqlite'\n  }\n});\n\n// Create the schema\ndb.schema.createTable('messages', table => {\n  table.increments('id');\n  table.string('text');\n});\n\napp.use('/messages', service({\n  Model: db,\n  name: 'messages'\n}));\napp.use('/messages', service({ Model, name, id, events, paginate }));\n```\n\n__Options:__\n\n- `Model` (**required**) - The KnexJS database instance\n- `name` (**required**) - The name of the table\n- `schema` (*optional*) - The name of the schema table prefix (example: `schema.table`)\n- `id` (*optional*, default: `'id'`) - The name of the id field property.\n- `events` (*optional*) - A list of [custom service events](https://docs.feathersjs.com/api/events.html#custom-events) sent by this service\n- `paginate` (*optional*) - A [pagination object](https://docs.feathersjs.com/api/databases/common.html#pagination) containing a `default` and `max` page size\n- `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' ]`)\n- `whitelist` (*optional*) - A list of additional query parameters to allow (e..g `[ '$regex', '$geoNear' ]`). Default is the supported `operators`\n\n### `adapter.createQuery(query)`\n\nReturns a KnexJS query with the [common filter criteria](https://docs.feathersjs.com/api/databases/querying.html) (without pagination) applied.\n\n### params.knex\n\nWhen making a [service method](https://docs.feathersjs.com/api/services.html) call, `params` can contain an `knex` property which allows to modify the options used to run the KnexJS query. See [customizing the query](#customizing-the-query) for an example.\n\n\n## Example\n\nHere's a complete example of a Feathers server with a `messages` SQLite service. We are using the [Knex schema builder](http://knexjs.org/#Schema) and [SQLite](https://sqlite.org/) as the database.\n\n```\n$ npm install @feathersjs/feathers @feathersjs/errors @feathersjs/express @feathersjs/socketio feathers-knex knex sqlite3\n```\n\nIn `app.js`:\n\n```js\nconst feathers = require('@feathersjs/feathers');\nconst express = require('@feathersjs/express');\nconst socketio = require('@feathersjs/socketio');\n\nconst service = require('feathers-knex');\nconst knex = require('knex');\n\nconst db = knex({\n  client: 'sqlite3',\n  connection: {\n    filename: './db.sqlite'\n  }\n});\n\n// Create a feathers instance.\nconst app = express(feathers());\n// Turn on JSON parser for REST services\napp.use(express.json());\n// Turn on URL-encoded parser for REST services\napp.use(express.urlencoded({ extended: true }));\n// Enable REST services\napp.configure(express.rest());\n// Enable Socket.io services\napp.configure(socketio());\n// Create Knex Feathers service with a default page size of 2 items\n// and a maximum size of 4\napp.use('/messages', service({\n  Model: db,\n  name: 'messages',\n  paginate: {\n    default: 2,\n    max: 4\n  }\n}))\napp.use(express.errorHandler());\n\n// Clean up our data. This is optional and is here\n// because of our integration tests\ndb.schema.dropTableIfExists('messages').then(() => {\n  console.log('Dropped messages table');\n\n  // Initialize your table\n  return db.schema.createTable('messages', table => {\n    console.log('Creating messages table');\n    table.increments('id');\n    table.string('text');\n  });\n}).then(() => {\n  // Create a dummy Message\n  app.service('messages').create({\n    text: 'Message created on server'\n  }).then(message => console.log('Created message', message));\n});\n\n// Start the server.\nconst port = 3030;\n\napp.listen(port, () => {\n  console.log(`Feathers server listening on port ${port}`);\n});\n```\n\nRun the example with `node app` and go to [localhost:3030/messages](http://localhost:3030/messages).\n\n## Querying\n\nIn addition to the [common querying mechanism](https://docs.feathersjs.com/api/databases/querying.html), this adapter also supports:\n\n### $and\n\nFind all records that match all of the given criteria. The following query retrieves all messages that have foo and bar attributes as true.\n\n```js\napp.service('messages').find({\n  query: {\n    $and: [\n      {foo: true},\n      {bar: true}\n    ]\n  }\n});\n```\n\nThrough the REST API:\n\n```\n/messages?$and[][foo]=true&$and[][bar]=true\n```\n\n### $like\n\nFind all records where the value matches the given string pattern. The following query retrieves all messages that start with `Hello`:\n\n```js\napp.service('messages').find({\n  query: {\n    text: {\n      $like: 'Hello%'\n    }\n  }\n});\n```\n\nThrough the REST API:\n\n```\n/messages?text[$like]=Hello%\n```\n\n### $notlike\n\nThe opposite of `$like`; resulting in an SQL condition similar to this: `WHERE some_field NOT LIKE 'X'`\n\n```js\napp.service('messages').find({\n  query: {\n    text: {\n      $notlike: '%bar'\n    }\n  }\n});\n```\n\nThrough the REST API:\n\n```\n/messages?text[$notlike]=%bar\n```\n\n### $ilike\n\nFor PostgreSQL only, the keywork $ilike can be used instead of $like to make the match case insensitive. The following query retrieves all messages that start with `hello` (case insensitive):\n\n```js\napp.service('messages').find({\n  query: {\n    text: {\n      $ilike: 'hello%'\n    }\n  }\n});\n```\n\nThrough the REST API:\n\n```\n/messages?text[$ilike]=hello%\n```\n\n\n## Transaction Support\n\nThe Knex adapter comes with three hooks that allows to run service method calls in a transaction. They can be used as application wide (`app.hooks.js`) hooks or per service like this:\n\n```javascript\n// A common hooks file\nconst { hooks } = require('feathers-knex');\n\nconst { transaction } = hooks;\n\nmodule.exports = {\n  before: {\n    all: [ transaction.start() ],\n    find: [],\n    get: [],\n    create: [],\n    update: [],\n    patch: [],\n    remove: []\n  },\n\n  after: {\n    all: [ transaction.end() ],\n    find: [],\n    get: [],\n    create: [],\n    update: [],\n    patch: [],\n    remove: []\n  },\n\n  error: {\n    all: [ transaction.rollback() ],\n    find: [],\n    get: [],\n    create: [],\n    update: [],\n    patch: [],\n    remove: []\n  }\n};\n```\n\nTo use the transactions feature, you must ensure that the three hooks (start, end and rollback) are being used.\n\nAt the start of any request, a new transaction will be started. All the changes made during the request to the services that are using the `feathers-knex` will use the transaction. At the end of the request, if sucessful, the changes will be commited. If an error occurs, the changes will be forfeit, all the `creates`, `patches`, `updates` and `deletes` are not going to be commited.\n\nThe object that contains `transaction` is stored in the `params.transaction` of each request.\n\n> __Important:__ If you call another Knex service within a hook and want to share the transaction you will have to pass `context.params.transaction` in the parameters of the service call.\n\n\n## Customizing the query\n\nIn a `find` call, `params.knex` can be passed a KnexJS query (without pagination) to customize the find results.\n\nCombined with `.createQuery({ query: {...} })`, which returns a new KnexJS query with the [common filter criteria](https://docs.feathersjs.com/api/databases/querying.html) applied, this can be used to create more complex queries. The best way to customize the query is in a [before hook](https://docs.feathersjs.com/api/hooks.html) for `find`.\n\n```js\napp.service('messages').hooks({\n  before: {\n    find(context) {\n      const query = context.service.createQuery(context.params);\n\n      // do something with query here\n      query.orderBy('name', 'desc');\n\n      context.params.knex = query;\n      return context;\n    }\n  }\n});\n```\n\n## Configuring migrations\n\nFor using knex's migration CLI, we need to make the configuration available by the CLI. We can do that by providing a `knexfile.js` (OR `knexfile.ts` when using TypeScript) in the root folder with the following contents:\n\nknexfile.js\n```js\nconst app = require('./src/app')\nmodule.exports = app.get('postgres')\n```\nOR\n\nknexfile.ts\n```ts\nimport app from './src/app';\nmodule.exports = app.get('postgres');\n```\n\nYou will need to replace the `postgres` part with the adapter you are using. You will also need to add a `migrations` key to your feathersjs config under your database adapter. Optionally, add a `seeds` key if you will be using [seeds](http://knexjs.org/#Seeds-CLI).\n\n```js\n// src/config/default.json\n...\n  \"postgres\": {\n    \"client\": \"pg\",\n    \"connection\": \"postgres://user:password@localhost:5432/database\",\n    \"migrations\": {\n      \"tableName\": \"knex_migrations\"\n    },\n    \"seeds\": {\n      \"directory\": \"../src/seeds\"\n    }\n  }\n```\n\nThen, by running: `knex migrate:make create-users`, a `migrations` directory will be created, with the new migration.\n\n### Error handling\n\nAs of version 4.0.0 `feathers-knex` only throws [Feathers Errors](https://docs.feathersjs.com/api/errors.html) with the message. On the server, the original error can be retrieved through a secure symbol via  `error[require('feathers-knex').ERROR]`\n\n```js\nconst { ERROR } = require('feathers-knex');\n\ntry {\n  await knexService.doSomething();\n} catch(error) {\n  // error is a FeathersError with just the message\n  // Safely retrieve the Knex error\n  const knexError = error[ERROR];\n}\n```\n\n## License\n\nCopyright (c) 2019\n\nLicensed under the [MIT license](LICENSE).\n","readmeFilename":"README.md"}