fastify-knex-api
Advanced tools
Fastify plugin to expose API for Knex Query Builder
If you are using Fastify as your server and Knex as your ODM, fastify-knex-api is the easiest solution to run API server for your models.
fastify-knex-api generates REST routes with refs subroutes like /api/authors and /api/authors/AUTHOR_ID autodiscovered all tables and primary keys or permit to select which tables to expose.
import knexAPI from 'fastify-knex-api';
import Fastify from 'fastify';
const fastify = Fastify();
fastify.register(knexAPI, { // plugin registration
knexConfig: { // See: initialization parameters
client: 'YOUR_CLIENT_ADAPTER (mysql, oracle)',
connection: 'YOUR_QUERY_STRING',
}
});
await fastify.ready(); // waiting for plugins registration
await fastify.listen(8080); // running the server
And if your database has authors and posts tables the result routes will be something like this
/
└── api/
├── authors (GET, HEAD, POST)
│ └── / (GET, HEAD, POST)
│ └── :id (GET, HEAD, PATCH, DELETE)
└── posts (GET, HEAD, POST)
└── / (GET, HEAD, POST)
└── :id (GET, HEAD, PATCH, DELETE)
npm i fastify-knex-api -s
Register plugin on fastify instance:
const fastify = Fastify();
fastify.register(knexAPI, options);
with following options:
Function to run before any API request to check authorization permissions in.
If not set, all crud routes are allowed.
checkAuth: async (req, reply) => {
...
return true;
}, // all crud routes are allowed. As not set checkAuth
If return true, user is allowed to perform action.
If return false, user is not allowed to perform action. In this case you should return a 401 custom error code as this
checkAuth: async(req, reply) => {
reply.status(401);
} // all crud routes are denied
If checkAuth doesn't set an error code, a default is set.
{
"statusCode": 401,
"code": "CRUD_NOT_AUTHORIZED",
"error": "Unauthorized",
"message": "Not authorized to perform CRUD operation"
}
Knex configuration options like
{
client: 'YOUR_CLIENT_ADAPTER (mysql, oracle)',
connection: 'YOUR_QUERY_STRING',
}
See Knex documentation for other parameters.
Path prefix. Default is /api/.
An array of tables to expose or an object of tables to expose with their primary key
[ 'authors', 'posts']
// or
[
{name: 'authors', pk: 'id' },
{name: 'posts', pk: 'post_id' }
]
Default is to autodiscover and expose all tables and, on request, to autodiscover their primary keys if not manually set.
Optional function to call to alter default validation and serialization schema.
See here for some example.
.schemas has this signature
.schemas = (table_name, schema) => {
...
return schema;
}
where table_name is the table name and schema is the generated schema.
If .schemaDirPath is defined, it's called after .schemaDirPath.
Directory where it's possible to define schemas for validation and serialization.
If exists, module .schemaDirPath/${table_name}.schema.js is loaded and the default export function has called.
This function should have this signature:
(schema) => {
...
return schema;
}
where schema is the generated schema.
If .schemas is defined, it's called before .schemas.
GET /api/authors
List records in authors table. Return a structure like this
{
total: 70,
items: [
{
id: 1,
first_name: 'Michael',
last_name: 'Messina',
email: 'monia89@example.org',
active: 1,
added: '2011-10-09T08:34:11.000Z'
},
{
id: 2,
first_name: 'Joannes',
last_name: 'Russo',
email: 'sorrentino.akira@example.net',
active: 1,
added: '1982-02-11T19:39:12.000Z'
},
...
]
}
where
total is the count of record in the table based on filters (where)items are records based on filters (where) and pagination (skip, page, limit, ...)GET /api/authors/1
Return a single record with the primary key as parameter.
{
id: 1,
first_name: 'Michael',
last_name: 'Messina',
email: 'monia89@example.org',
active: 1,
added: '2011-10-09T08:34:11.000Z'
}
Supports projection as parameter.
GET /api/authors/1?fields=id,first_name
return
{
id: 1,
first_name: 'Michael',
}
POST /api/authors/
{
first_name: 'Michael',
last_name: 'Messina',
email: 'monia89@example.org',
}
Create a new record in authors. Return saved record.
{
id: 1,
first_name: 'Michael',
last_name: 'Messina',
email: 'monia89@example.org',
active: 1,
added: '2011-10-09T08:34:11.000Z'
}
Supports projection as parameter.
POST /api/authors/?fields=id,first_name
return only selected fields
PATCH /api/authors/1
{
last_name: 'Jefferson',
}
Update a record in authors. Return updated record
{
id: 1,
first_name: 'Michael',
last_name: 'Jefferson',
email: 'monia89@example.org',
active: 1,
added: '2011-10-09T08:34:11.000Z'
}
Supports projection as parameter.
PATCH /api/authors/1?fields=id,first_name
return only selected fields
In list method (GET /api/authors) we can use URL options to paginating, sorting, filter items and a mix of them.
Options to limit and eventually skip some records
/api/authors // return first 50 items
/api/authors?limit=-1 // return all items in table
/api/authors?limit=5 // return first 5 items
/api/authors?limit=5&skip=6 // skip first 6 items and return next 5
/api/authors?limit=5&page=2 // page of 5 items. We get page 2 (6 to 10)
| Option Name | Default Value | |
|---|---|---|
| Offset | offset | 0 |
| Offset | skip | 0 |
| Limit | limit | 50 |
| Page | page | 1 |
| Page | window | 1 |
To get all records force limit=-1
Pass sort option string. A - at the beginning for descending order. name for sorting by name field or -name for descending sort by it.
Separate fields by , to allow multiple sorting. So -first_name,last_name for sorting by first_name in descending order and then for last_name in ascending.
/api/authors?sort=-first_name,last_name
| Option Name | Default Value | |
|---|---|---|
| Sort | sort | null |
Raw filtering is available via filter query option.
/api/posts?filter=author_id%3D1
will return all posts for author_id with id equals to 1.
Filter must be obviously url encoded and so %3D here is urlencoded '=' symbol, so actual option value is author_id=1.
We can use some http(s) library to simplify URL encoding. For axios, at an example, we can write
const res = await axios.get('/api/posts', {
params: {
filter: 'author_id=1'
}
});
| Option Name | Default Value | |
|---|---|---|
| Filter | filter | null |
The filter parameter has passed as is to knex whereRaw
Return only fields that matches the parameter. If first character is -
will return everything except fields in the parameter.
/api/authors?fields=first_name,last_name // only return first_name and last_name
/api/authors?fields=-id,first_name // return everything except id and first_name
| Option Name | Default Value | |
|---|---|---|
| Projection | fields | * |
Generated API support standard fastify validation and serialization via .schemas option.
If you are not confidable with fastify validation and serialization logics, see documentation.
If you don't set .schemas, it's automatic generated See later for an example of a generated schema.
If you set .schemas as a function, it received the name of table and its generated schema. What it's returned, it will be used as schema for the table.
You can disable serialization and validation at all returning undefined
schemas: () => {}
You can disable it for a single table
schemas: (tn, schema) => tn === 'authors' ? undefined : schema;
You can add or update default schema by change it inside schemas(tn,schema) and return the changed schema.
fastify.register(knexAPI, {
knexConfig: {...},
schemas: (tn, schema) => {
if (tn === 'authors') {
// for author creation, we set 'active' as required
schema.create.schema.body.required = ['active'];
}
// remember to return schema
return schema;
},
...
where schema.create are validation and/or serialization schema for related restful http verbs where:
| validation name | URL | VERB |
|---|---|---|
| schema.list | / | GET |
| schema.create | / | POST |
| schema.view | /id | GET |
| schema.update | /id | PATCH |
| schema.delete | /id | DELETE |
If you omit one of these, the default is used.
If you set to undefined one, validation and serialization are disabled for this verb.
As you can see, taking a look to defaults, this plugin supports the URI references $ref to other schemas.
You can add manually these references through fastify.addSchema(schema) or automatically if your schema has a ref attribute.
This attribute could be a single object or an array of objects if you wish to register more references at once.
If .schemas and .schemaDirPath are used together, the schemas defined in .schemas have precedence to there loaded in .schemaDirPath.
The generated validation and serialization is compatible with other plugins like @fastify/swagger and @fastify/swagger-ui for automatically serving OpenAPI v2/v3 schemas
As an example, in MySQL, if you have an authors table like this
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(50) NOT NULL,
`last_name` varchar(50) NOT NULL,
`email` varchar(100) NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT 1,
`added` timestamp NOT NULL DEFAULT current_timestamp(),
this is the default generated schema for CRUD operations over this table
{
"view": {
"schema": {
"summary": "Get details of single authors",
"tags": [
"authors"
],
"params": {
"type": "object",
"properties": {
"id": {
"type": "string",
"description": "Unique identifier of authors"
}
}
},
"query": {
"$ref": "fastify-knex-api/query#/properties/fields"
},
"response": {
"200": {
"type": "object",
"$ref": "fastify-knex-api/tables/authors#"
},
"404": {
"$ref": "fastify-knex-api/http-code#/properties/404"
},
"500": {
"$ref": "fastify-knex-api/http-code#/properties/500"
}
}
}
},
"list": {
"schema": {
"summary": "List authors",
"tags": [
"authors"
],
"query": {
"$ref": "fastify-knex-api/query#/properties/list"
},
"response": {
"200": {
"total": {
"type": "integer",
"example": "1"
},
"items": {
"type": "array",
"items": {
"$ref": "fastify-knex-api/tables/authors#"
}
}
},
"500": {
"$ref": "fastify-knex-api/http-code#/properties/500"
}
}
}
},
"create": {
"schema": {
"summary": "Create a new authors",
"tags": [
"authors"
],
"query": {
"type": "object",
"$ref": "fastify-knex-api/query#/properties/fields"
},
"response": {
"200": {
"type": "object",
"$ref": "fastify-knex-api/tables/authors#"
},
"500": {
"$ref": "fastify-knex-api/http-code#/properties/500"
}
},
"body": {
"$ref": "fastify-knex-api/tables/authors#"
}
}
},
"update": {
"schema": {
"summary": "Update existing authors",
"tags": [
"authors"
],
"params": {
"type": "object",
"properties": {
"id": {
"type": "string",
"description": "Unique identifier of authors"
}
}
},
"query": {
"$ref": "fastify-knex-api/query#/properties/fields"
},
"response": {
"200": {
"type": "object",
"$ref": "fastify-knex-api/tables/authors#"
},
"404": {
"$ref": "fastify-knex-api/http-code#/properties/404"
},
"500": {
"$ref": "fastify-knex-api/http-code#/properties/500"
}
},
"body": {
"$ref": "fastify-knex-api/tables/authors#"
}
}
},
"delete": {
"schema": {
"summary": "Delete existing authors",
"tags": [
"authors"
],
"params": {
"type": "object",
"properties": {
"id": {
"type": "string",
"description": "Unique identifier of authors"
}
}
},
"response": {
"204": {
"$ref": "fastify-knex-api/http-code#/properties/204"
},
"404": {
"$ref": "fastify-knex-api/http-code#/properties/404"
},
"500": {
"$ref": "fastify-knex-api/http-code#/properties/500"
}
}
}
}
}
where fastify-knex-api/tables/authors# is this auto-generated schema for table
"$ref": "fastify-knex-api/tables/authors#"
"type": "object",
"properties": {
"id": {
"type": "integer"
},
"first_name": {
"type": "string",
"maxLength": 50
},
"last_name": {
"type": "string",
"maxLength": 50
},
"email": {
"type": "string",
"maxLength": 100
},
"active": {
"type": "integer"
},
"added": {
"type": "string",
"format": "date-time"
}
}
while
fastify-knex-api/http-code#/properties/204fastify-knex-api/http-code#/properties/404fastify-knex-api/http-code#/properties/500are schemas for delete response and errors and
fastify-knex-api/query#/properties/listfastify-knex-api/query#/properties/fieldare schemas for list method query string and for projection returning query string.
Definitions of there references can be found in ./src/DefaultSchemas.js.
This module supports both ESM and CommonJS. If you are using CommonJS, you can import it like so:
const knexAPI = require('knex-fastify-api');
In Typescript and @fastify/autoload environment you can register this plugin by creating a file in plugins folder like this:
import { type FastifyPluginAsync } from 'fastify';
import knexAPI, {type IKAPluginOptions } from 'fastify-knex-api';
import fp from 'fastify-plugin';
const plugin: FastifyPluginAsync = async app => {
const knexAPIOptions: IKAPluginOptions = {
knexConfig: {
client: 'YOUR_CLIENT_ADAPTER (mysql, oracle)',
connection: 'YOUR_QUERY_STRING',
},
};
app.register(knexAPI, knexAPIOptions);
app.after(err => {
if (err) throw err;
});
};
export default fp(plugin,
{
name: 'knex-api',
}
);
For feature requests or help, please visit the discussions page on GitHub.
For bug reports, please file an issue on the issues page on GitHub.
Contributions welcome! Please open a pull request on GitHub with your changes. You can run them by me first on the discussions page if you'd like. Please add tests for any changes.
Licensed under APACHE 2.0
Emiliano Bruni (info@ebruni.it)
[0.2.2] 2024-02-08
FAQs
Fastify plugin to expose API for Knex Query Builder
The npm package fastify-knex-api receives a total of 1 weekly downloads. As such, fastify-knex-api popularity was classified as not popular.
We found that fastify-knex-api demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer 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
Socket’s threat research team has detected six malicious npm packages typosquatting popular libraries to insert SSH backdoors.
Security News
MITRE's 2024 CWE Top 25 highlights critical software vulnerabilities like XSS, SQL Injection, and CSRF, reflecting shifts due to a refined ranking methodology.
Security News
In this segment of the Risky Business podcast, Feross Aboukhadijeh and Patrick Gray discuss the challenges of tracking malware discovered in open source softare.