We often need to write a restful API for database. This tool can generate the api code for you as a start point for your projects based on the configuration defined in one config file. Then, continue to develop your projects. It can save you a lot of repeatitive coding time and let you focus on the business logic.
The design idea is to let you define the api paths and how to the access the database objects, such as tables, views, custom queries, and stored procedures.
You can define the fields/columns you want to retrieve and update. You can also define the fields for search criteria. This tool will generate the code and SQL statements for you.
This tool will not generate generic entity CRUD.
npx create data-api-app [-c config.ymal] [target directory]
The configuration file is config.yaml by default. It is located in the root folder of your project. See a simple config or the config for the RealWorld API for examples.
name: My API app
port: 8080
path: /api
database: mysql
models:
- users:
- table users:
get:
- int id
- varchar name
- varchar email
The tool generates three source code files: server.js
. You can run the app by running the following command:
node server.js
The app will listen on the port that you defined in the config file.
A model has a collection of objects. Each object can be a table, view, stored procedure, or custom query. Each model will generate a router file under the api
folder. And a model file under the model
folder.
api
- users.js
- articles.js
- tags.js
model
- users.js
- articles.js
- tags.js
The server.js
will import the routes and models from the api
folder dynamically.
Table can use the following methods:
- GET - retrieve record(s)
- POST - create a new record
- PUT - upsert a record
- DELETE - delete a record
- PATCH - update a record
Each method has corresponding fields that you will need to provide.
- table users:
get:
- int id
- varchar name
- varchar email
post:
- varchar name
- varchar email
In real world, there could be multiple ways of creating records, retrieving records and updating records. Therefore, you can define multiple paths with different combinations of fields as you need.
- table users:
get:
- int id
- varchar name
- varchar email
get /users/:id:
- int id PK
- varchar name
- varchar email
post:
- int id PK
- varchar name
- varchar email
patch users/name:
- int id PK
- varchar name
patch users/email:
- int id PK
- varchar email
delete :id:
- int id PK
Access to views are similar to tables. Only GET method is supported. You can define the fields that you want to retrieve. And the fields for search criteria.
- view v_users:
get:
- int id
- varchar name
- varchar email
get /:id:
- int id
- varchar name PK
- varchar email
Calls to the stored procedures are straight forward. You define the parameters.
- procedure usp_update_user:
post:
- int id
- varchar name
- varchar email
- datetime created_at
- datetime updated_at
You can also define custom query that has joins to other tables. You can not define the fields that you want to retrieve. It is defined by the query. But, you still can define the fields for search criteria. And define the field names for api. E.g. the field name in the query is u.id, but you want to use id in the api.
- query users_posts:
select: select * from users u join posts p on u.id = p.user_id
get:
get /posts/:userid/:
- int id FK "u.id"
Each field is defined in one line. The format is as follows:
# type name keys alias
- int id FK "u.id"
When the fields are PK (primary key), FK (foreign key), or SK (search key), they will be used to generate the SQL where clause. The alias is used in SQL if provided. For example, the following path:
- query: users_posts
select: select * from users u join posts p on u.id = p.user_id
get /users_posts/:user_id:
- int user_id FK "u.id"
will generate the following SQL statement:
await sql.query`select * from users u join posts p on u.id = p.user_id WHERE u.id = ${id}`;
Paths are generated as /{object name}. But you can also explictly define the path.
- table: users
# default path is /users
get:
- int id
- varchar name
- varchar email
# explicit path
get /users:id:
- int id PK
- varchar name
- varchar email
You can denine the authentication: jwtAuth or apiKeyAuth in the config file.
authentication: jwtAuth
or
authentication: apiKeyAuth
Then, you can appy the authentication to the routes by adding the '*' sign.
- table: users
get:
- int id
- varchar name
- varchar email
get* /users:id:
- int id PK
- varchar name
- varchar email
post*:
- varchar name
- varchar email
patch* /name:
- int id PK
- varchar name
patch* /email:
- int id PK
- varchar email
delete* /:id:
- int id PK
By default, if you use jwtAuth, you get the authentication as follows:
const jwt = require('jsonwebtoken');
app.authentication = (req, res, next) => {}
app.post('/auth/login', async (req, res) => {});
app.post('/auth/register', async (req, res) => {});
app.post('/auth/logout', async (req, res) => {});
app.post('/auth/refresh', async (req, res) => {});
You can modify and implement the authentication middleware in the server.js
file.
// TODO: implement authentication
app.authentication = (_, __, next) => next();
The tool also generates the api specification in the api-spec.yaml
file.
This project is licensed under the MIT License - see the LICENSE file for details.
Have fun!
(C) 2024 Yiyi Sun, All Rights Reserved.