knex-router v1.0.0
Knex router
Small and to the point: use this Express router if you want a light way to have your database exposed as a JSON Restful API
For example:
GET https://my.site/knex/users
could return[{id: 1, name: 'John'}, {id: 2, name: 'Sue'}]
GET https://my.site/knex/users/2
would return{id: 2, name: 'Sue'}
DELETE https://my.site/knex/users/1
would remove John (user with ID 1) from your database.
Install
npm i --save knex-router
Setting it up
Asumming the usual express and Knex settings, you would have to do something like this:
// import the module
const knexRouter = require("./knexRouter");
// import and set knex up
const knex = require("knex")(...settings...);
// use knex-router as a middleware, providing the knex instance
app.use("/knex", knexRouter({ knex: knex }));
Routes available
This router provides the four basic DML operations (CRUD, or create, read, update and delete) through the HTTP verbs GET, POST, PUT and DELETE.
Fetch all records from a table
Invoking
GET https://my.site/knex/users
will perform
SELECT * FROM users
and return the result as a JSON array.
Fetch one record by its primary key
Invoking
GET https://my.site/knex/users/33
will perform
SELECT * FROM users WHERE id = 33
and return the result as a JSON object.
Creating a new record
Invoking
POST https://my.site/knex/users
will perform
INSERT INTO users (col1, col2) VALUES (1, 2)
using as values the ones provided in the HTTP POST request body.
Updating an existing record
Invoking
PUT https://my.site/knex/users/47
will perform
UPDATE users SET col1 = 'ab', col2 = 'cd' WHERE id = 47
using as values the ones provided in the HTTP request body.
Erasing a record by its ID
Invoking
DELETE http://my.site/knex/users/83
will perform
DELETE FROM users WHERE id = 83
and return the result Knex gives as JSON.
IMPORTANT: All this routes assume that the route name is the same as the table name, and that the primary key column is named id
(if this isn't the case it can be customized, see next section)
Error handling
By default all requests handle the errors with console.error
and sending a response status HTTP 500, and the error object as JSON.
Special cases
If your primary key is not called id -as the module uses by default- you can specify its name, on each call, with the URL query param idColumn. For example, if you call
GET https://mysite.com/knex/users/3?idColumn=user_id
it would yield the following SQL query:
SELECT * FROM users where user_id = 3
Security
If you want to secure this router's routes, you can add any security middleware.
app.use("/knex", authMiddleware, knexRouter({ knex: app.knex }));
More customized routes
Build your own, this is supposed to be small and simple!
Cache
All routes have implemented a cache preventing mechanism. No cache here!
Coming soon
- CI/CD validations and badges
- Unit tests
- LIMIT & OFFSET
- Basic WHERE filtering
- Ordering
- Logging (now is logging to console...)
Author
@luispablo
4 years ago