This is a seed project for the General Framework(GF), which is designed to quickly create crud screens and reducing boilerplate. GF allows you to craft simple data aware react components that require no server side programming. GF is a database first approach, meaning it's expecting a database schema to be defined. General Framework is a collection of open source libraries outlined below.
JQL Json Query language. Converts json to sql.
General Endpoint (GE) data driven http api
General Store (GS) - mobx store integrated with GE
General Components - data aware react components integrated with GS and GE.
This seed project is a reference implementation of the General EndPoint. General Endpoint is an api that accepts and returns json objects that implement sql crud commands with no programming. It uses a variation of Rest conventions. Internally GE uses jql, which turns json into sql statements.
General Endpoint is designed for typical uses cases, not all queries. Create custom endpoints for more complicated database operations.
This seed project is dependent on: node, express, auth0, and Sql Server database (and it's driver). However the conventions of General Endpoint and JQL are independent.
General endpoint is depenent on 3 database normalization conventions:
- All tables must have an ID field that is primary key and autoincrements.
- FKs follow naming convention {tableName}ID.
- FK lookup tables have a "name" field for display.
Tip: Use a computed column for names that are composites.
GE conventions are similar to rest conventions.
| HTTP | Url | Description | Returns |
|---|---|---|---|
| GET | /api/general/{tableName}/ | Get all records | Object[] |
| GET | /api/general/{tableName}/{ID} | Get record by ID | Object |
| POST | /api/general/{tableName} | Query records. See JQL for body specifications | Object[] |
| POST | /api/general/count/{tableName} | Pulls count for table. Accepts body with jql where clause | number |
| PUT | /api/general/{tableName} | Insert or update a record. Post body is object to insert or update. does upsert based on whether ID field is present in object. Arrays attached to object will be added as child tables using the object property as the tableName and using foreign key naming convention to set parent ID. deletedChildren is an object property that will delete from child tables using first object attribute as table name and property of the table name is an array of ids to be deleted. This creates stipulation that tables can't have fields named deletedChildren or the name of child tables. |
|
| DELETE | /api/general/{tableName}/{ID} | Delete a record |
In GE all authenticated users are trusted with access to select, insert, update, and delete functionality for all tables. Role based security is currently not implemented. GE is useful for prototyping and for apps where all users are highly trusted, for instance an app for a small company or an admin app for a small saas product.
It's important to not have security credentials such as usernames and passwords in the database. Otherwise authenticated users could compromise security. They could, for instance, get a list of all usernames and update other user's passwords. You can use another database or use a 3rd party authentication service. This seed project uses auth0.
JQL validates the table and column names against a schema. It also parameterizes it's inputs against sql injection.
General EndPoint also has a logging mechanism that requires this table be added named HistoryLog.
(TODO: make optional)
CREATE TABLE dbo.HistoryLog (
ID int NOT NULL IDENTITY(1,1),
tableName varchar(100) NULL,
tableKey int NULL,
userName varchar(100) NULL,
eventDate datetime NULL,
description varchar(255) NULL,
[before] text(16) NULL,
[after] text(16) NULL,
diff text(16) NULL
);
ALTER TABLE dbo.HistoryLog ADD CONSTRAINT PK_HistoryLog PRIMARY KEY (ID);
The config for auth0 is in /server/index.js
The database config is in server/db/index.js
From /server directory.
npm install
Starts the app for development on port 3001
Starts the production version of the app
Tests jql