Repository files navigation

Model-driven REST API for CRUD and more, using Node.js, Express, and PostgreSQL.

Evolutility-Server-Node provides a set of genericREST APIs for CRUD (Create, Read, Update, Delete) and simple charts. on objects of different structures.

For a matching model-driven Web UI, useEvolutility-UI-React orEvolutility-UI-jQuery.

1. Installation
2. Setup
3. Configuration
4. Models:Object -Field -Collection -Sample model
5. REST API:Get -Update -Charts -More
6. License

Download or clone from GitHub.

# To get the latest stable version, use git from the command line.git clone https://github.com/evoluteur/evolutility-server-node

or use thenpm package:

# To get the latest stable version, use npm from the command line.npm install evolutility-server-node

Dependencies:Node.js,Express,PostgreSQL, andPG-Promise.

Evolutility-Server-Node works withNode.js v12.12.0 (not yet compatible w/ later versions).

After installing Evolutility-Server-Node, follow these steps:

1. Create a PostgreSQL database.

2. In the fileconfig.js set the PostgreSQL connection string and the schema name to access your new database.

3. Maybe, also change other config options in the same file.

4. In the command line type the following:

# Install dependenciesnpm install# Create sample database w/ demo tablesnpm run makedb# Run the node.js servernpm start

Note: The database creation and population scripts are logged in the files "evol-db-schema-{datetime}.sql" and "evol-db-data-{datetime}.sql".

URLs on localhost:

• REST:http://localhost:2000/api/v1/

Configuration options are set in the fileconfig.js.

To be accessible by the REST API, each database table must be described in a model.Models contain the name of the driving table and the list of fields/columns present in the API.

• Object
• Field
• Collection
• Sample model

Multiple Master-Details can be specified with collections.

Example of collection inWine cellar.

Below is the model for a To-Do app.

exportdefault{id:"todo",table:"task",titleField:"title",searchFields:["title","duedate","description"],fields:[{id:"title",column:"title",type:"text",required:true,inMany:true},{id:"duedate",column:"duedate",type:"date",inMany:true},{id:"category",column:"category_id",type:"lov",lovTable:"task_category",inMany:true},{id:"priority",column:"priority_id",type:"lov",lovTable:"task_priority",required:true,inMany:true,},{id:"complete",column:"complete",type:"boolean",inMany:true},{id:"description",column:"description",type:"textmultiline"}]};

More sample models:Address book,Restaurants list,Wine cellar,Graphic novels inventory.

More information about Evolutility models and some useful scripts are available atevolutility-models.

Evolutility-Server-Node provides a generic RESTful API for CRUD (Create, Read, Update, Delete) and more. It is inspired fromPostgREST.

• Get
• Update
• Charts
• More

When running Evolutility-Server-Node locally, the base url ishttp://localhost:2000/api/v1/.

Gets a specific record by ID.

GET /{model.id}/{id}GET /todo/12

By default this endpoint returns nested collections with the record. For optimization, collections can be ommited by using the parameter "shallow".

GET /{model.id}/{id}?shallow=1GET /todo/12?shallow=1

Gets a list of records.

GET /{model.id}GET /todo

You can filter result rows by adding conditions on fields, each condition is a query string parameter.

GET /{model.id}/{field.id}={operator}.{value}GET /todo?title=sw.aGET /todo?priority=in.1,2,3

Adding multiple parameters conjoins the conditions:

todo?complete=0&duedate=lt.2018-12-24

For each field a sub-set of the operators below will be supported by the API (depending field types).

You can search for a specific string across multiple fields at once with the "search" parameter. The list of fields to be searched is specified with "searchFields" in the model (if unspecified, text fields flagged with "inMany" for list view will be used).

GET /{model.id}?search={value}GET /todo?search=translation

The reserved word "order" reorders the response rows. It uses a comma-separated list of fields and directions:

GET /{model.id}?order={field.id}.{asc/desc}GET /todo?order=priority.desc,title.asc

If no direction is specified it defaults to ascending order:

GET /todo?order=duedate

The reserved words "page" and "pageSize" limits the response rows.

GET /{model.id}?page={pageindex}&pageSize={pagesize}GET /todo?page=0&pageSize=50

By default all APIs return data in JSON format. This API call allows to request data in CSV format (export to Excel).This feature is usingcsv-express.

GET /{model.id}?format=csvGET /todo?format=csv

Notes: In the returned data every object has an extra property "_full_count" which indicate the total number of records in the query (before limit).

To create a row in a database table post a JSON object whose keys are the names of the columns you would like to create. Missing keys will be set to default values when applicable.

POST {model.id} {data}POST /todo{ title: 'Finish testing', priority: 2}

Even though it is a "POST", the request also returns the newly created record. It is not standard but it saves the UI a subsequent call.

PATCH or PUT can be used to update specific records.

PATCH /{model.id}/{id} {data}PATCH /todo/5{ title: 'Finish testing', priority: 2}

PUT /{model.id}/{id} {data}PUT /todo/5{ title: 'Finish testing', priority: 2}

Notes: The request returns the updated record. It is not standard but it saves the UI a subsequent call.

Simply use the DELETE verb with the id of the record to remove.

DELETE /{model.id}/{id}DELETE /todo/5

To delete multiple records at once, pass multiple ids (separated by commas).

DELETE /{model.id}/{id1},{id2},{id3}DELETE /todo/5,7,12

In addition to CRUD, Evolutility-Server-Node provides a few endpoints for Charts, Lists of values, file upload, and API discovery.

Returns the list of all active objects with urls to their REST end-points.

GET /

It is also possible to get a more detailed list of REST end-points for a specific model.

GET /?id={model.id}GET /?id=todoGET /?id=contact

Note: These end-point must be enabled in the configuration with { apiInfo: true }.

For charts data, it is possible to get aggregated data for field of types lov, boolean, integer, decimal, and money. Use the attribute "noCharts" to exclude a field from Charts.

GET /{model.id}/chart/{field id}GET /todo/chart/category

Returns the total count, and the min, max, average, variance, standard deviation and total for numeric fields in the model.

GET /{model.id}/statsGET /todo/stats

Dropdown fields in the UI (field.type="lov" in the model) have a REST endpoint to get the list of values. This endpoint can also take a search query parameter.

GET /{model.id}/lov/{field.id}GET /todo/lov/categoryGET /todo/lov/category?search=pro

This endpoint lets you upload a file. The current (naive) implementation simply saves the file on the file server in a folder named like the model id (inside the folder specified by the option "uploadPath" in config.js).

POST /{model.id}/upload/{id}POST /comics/upload/5

With query parameters: file and "field.id".

If the model has collections defined, they can be queried with this end-point.

GET /{model.id}/collec/{collection.id}?id={id}GET /winecellar/collec/wine_tasting?id=1

When storing models in evol_object and evol_field tables, they can be queried through REST.

Get all models flagged as active.

GET /meta/models

Get a model by ID (integer).

GET /meta/model/{modelid}GET /meta/model/1

Note: Schema and Models end-points must be enabled in the configuration with { apiDesigner: true }.

These endpoints query for the database structure (rather than the data), and returns lists of tables and columns.

List of schema tables (props: table, type, readOnly).

GET /db/tables

List of columns (props: column, type, required) for a specified table.

GET /db/{table_name}/columnsGET /db/contact/columnsGET /db/task/columns

Note: These end-point must be enabled in the configuration with { schemaQueries: true }.

This endpoint gets the API version (as specified in the project's package.json file).

GET /version

Copyright (c) 2024Olivier Giulieri.

Evolutility-Server-Node is released under theAGPLv3 license.