SQL to PostgreSQL function, CRUD generator for NestJS

Project using DDL CREATE TABLE definition will generate:

PostgreSQL functions _delete, _get, _list, _save .
NestJS REST API with dto definition, service, controller and module template and basic E2E tests.
WWW Angular module with: dto-s, service, routing, edit page and list page with pagination and basic filtering that is remembered

Comments on table column is used for API description and labels for edits. Of course, this will not meet in 100% yours needs, but will speed up your development time.

Usage

! Important ! Before usage please change inside sql_to_fnc.constants.ts database username.

Linux usage:

npm run linux

ts-node sql_to_fnc.ts table.example.sql

Windows usage:

npm run win

ts-node-script .\sql_to_fnc.ts .\table.example.sql

where table.sql is file with DDL table definition.

Generation SQL

Example table.sql definition:

CREATE TABLE auth.user
(
  id                 integer    NOT NULL DEFAULT NEXTVAL('user_serial'::regclass),
  login              varchar    NOT NULL,
  active             boolean    NOT NULL DEFAULT true,
  password           varchar    NOT NULL,
  notes              varchar
);
COMMENT ON TABLE auth.user IS 'Example table';
COMMENT ON COLUMN auth.user.login IS 'Login, minimum 3 letters';
COMMENT ON COLUMN auth.user.active IS 'Is account active?';
COMMENT ON COLUMN auth.user.password IS 'Please use minimum 8 letters, upper and lower case, number';
COMMENT ON COLUMN auth.user.notes IS 'Admin notes about user';

Main assumptions:

file should have only table definition
table name should have schema information. For public: CREATE TABLE public.cfg
table name should contain only small letters
table name should not be in plural (wrong table name: users)
primary key is first element, number type with NEXTVAL() definition
second element (in this case - login) will be searchable at list method (inside filter)

Generation SQL functions

It will create files in dist/sql directory with functions :

auth.user_delete(id integer)
auth.user_get(id integer)
auth.user_list(filter varchar) - filter is prepared for Angular Materials Server Side pagination
auth.user_save(user varchar) - parameter is JSONB object cast on varchar. If id more than zero - will update data, else - will create new record.

Generation NestJS REST API

Using Node.js Style Guide from https://github.com/airbnb/javascript and https://github.com/felixge/node-style-guide

It will generate files:

dist/api/dto/FilterItem.dto.ts - global definition for filter item, used in ListFilterRequest
dist/api/dto/ListFilterRequest.dto.ts - global definition request for list api
dist/api/customer/dto/User.dto.ts - with type definition for user
dist/api/customer/dto/UserFilter.dto.ts - list filter. Example: {"filter": [{"field": "login", "value": "Joh%"}], "sort": ["login"], "page_size": 25, "page_index": 1, "sort_direction": "asc"}
dist/api/customer/dto/UserListResponse.dto.ts - response from list function with table count information and data array - filtered user table
dist/api/customer/user.controller.ts - with API endpoints: POST /list, GET :id, POST /, DELETE :id
dist/api/customer/user.service.ts - with methods covering controller above definitions: list, save, get, delete. All methods call SQL functions.

Generation E2E supertest files

It will generate:

dist/tests/data/user.data.ts - contain valid user data, invalid user data and filter data. This should be filled by needs.
dist/tests/user.e2e-spec.ts - contain supertest template with test cases:
- POST /user/list - with filter data, get first element from list and use it
- GET /user/{number} - use above data for fetch item
- POST /user - save new item with valid data
- POST /user - update item with valid data
- DELETE /user/0 - check wrong data
- DELETE /user/{number} - delete added above data

Generation WWW CRUD Angular module

Using Style Guide by https://angular.io/guide/styleguide