Sign Up for Free

RunKit +

Try any Node.js package right in your browser

This is a playground to test code. It runs a full Node.js environment and already has all of npm’s 400,000 packages pre-installed, including evolutility-server-node with all npm packages installed. Try it out:

evolutility-server-node lists no main file and has no index.js, so it can't be directly required. If this is a mistake, please let us know. It may however contain internal files that you can require manually:

// require("evolutility-server-node/[??]")

This service is provided by RunKit and is not affiliated with npm, Inc or the package authors.

evolutility-server-node v0.6.0

Model-driven RESTful endpoints for CRUD and more, using Node.js, Express, and Postgres.

Evolutility-Server-Node

Model-driven RESTful backend for CRUD and more, using Node.js, Express, and PostgreSQL.

Evolutility-Server-Node provides a set of generic REST endpoints for CRUD (Create, Read, Update, Delete) and simple charts. These views can adapt to different data structures according to their models.

For a matching model-driven Web UI, use Evolutility-UI-React or Evolutility-UI-jQuery.

Table of Contents

  1. Installation
  2. Setup
  3. Configuration
  4. Models
  5. API
  6. License

Installation

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 the npm package:

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

Setup

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

  1. Create a PostgreSQL database.

  2. In the file config.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 dependencies
npm install

# Create sample database w/ demo tables
node js/setup/database.js

# Run the node.js server
npm start

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

In a web browser, go to the url http://localhost:2000/api/v1/evolutility/todo.

Configuration

Configuration options are set in the file config.js.

OptionDescription
apiPathPath for REST API (i.e.: "/api/v1/evolutility/").
apiPortPort for REST API (i.e.: 2000).
connectionStringDB connection string (i.e.: "postgres://evol:love@localhost:5432/evol").
schemaDB schema name (i.e.: "evolutility").
pageSizeNumber of rows per page in pagination (default = 50).
lovSizeMaximum number of values allowed for form dropdowns (default = 100).
csvSizeMaximum number of rows in CSV export (default = 1000).
csvHeaderCSV list of labels for CSV export
consoleLogLog SQL statements to console.
wCommentsAllow for user comments.
wRatingAllow for user ratings.
wTimestampTimestamp columns u_date and c_date w/ date of record creation and last update.

Models

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

PropertyDescription
idUnique key to identify the entity (used as API parameter).
tableDriving database table name (there are secondary tables for fields of type "lov").
pkeyName of the Primary key column (single column of type serial). Default to "id". In the data the key is always called "id".
fieldsArray of fields.
titleFieldField id for the column value used as record title.
searchFieldsArray of field ids for fields used to perform searches.

Field

PropertyDescription
idUnique key for the field (can be the same as column but doesn't have to be).
columnDatabase column name for the field.
lovtableTable to join to for field value (only for fields of type "lov").
lovcolumnColumn name (in the lovtable) for field value (only for fields of type "lov").
loviconSet to True to include icon with LOV items (only for fields of type "lov").
objectModel id for the object to link to (only for fields of type "lov").
typeField type is not a database column type but more a UI field type. Possible field types:
  • boolean
  • date
  • datetime
  • decimal
  • document
  • email
  • image
  • integer
  • lov (list of values)
  • money
  • text
  • textmultiline
  • time
  • url
requiredDetermines if the field is required for saving.
readonlyPrevents field modification.
inManyDetermines if the field is present (by default) in lists of records.
max, minMaximum/Minimum value allowed (only applies to numeric fields).
maxLength, minLengthMaximum/Minimum length allowed (only applies to text fields).
uniqueValues must be unique (not implemented yet).
noChartsForbids charts on the field.
deletetriggerDeleting records in the lovtable will trigger a cascade delete (this property is only used while creating the database).

Collection

Multiple Master-Details can be specified with collections.

PropertyMeaning
idUnique key for the collection.
tableDB Table to query.
columnColumn in the detail table to match against id of object.
objectModel id for the object to display (optional).
order"asc"/"desc" for sorting by the first field in fields.
fieldsArray of fields. Fields in collections do not need all properties of Fields in objects.

Example of collection in Wine cellar.

Sample model

Below is the model for a To-Do app.

module.exports = {
    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.

API

Evolutility-Server-Node provides a generic RESTful API for CRUD (Create, Read, Update, Delete) and more. It uses Node.js, Express and PostgreSQL. The API is inspired from PostgREST.

When running Evolutility-Server-Node locally, the url for the "todo" app is http://localhost:2000/api/v1/evolutility/todo.

Requesting Information

Get One

Gets a specific record by ID.

GET /<model.id>/<id>

GET /todo/12

Get Many

Gets a list of records.

GET /<model.id>

GET /todo

Filtering

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.a
GET /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).

OperatorMeaningExample
eqequals/todo?category=eq.1
gtgreater than/todo?duedate=gt.2019-01-15
ltless than/todo?duedate=lt.2019-01-15
gteless than or equal/todo?duedate=gte.2019-01-15
lteless than or equal/todo?duedate=lte.2019-01-15
ctcontains/todo?title=ct.e
swstart with/todo?title=sw.a
fwfinishes with/todo?title=fw.z
inone of a list of values/todo?priority=in.1,2,3
0is false or null/todo?complete=0
1is true/todo?complete=1
nullis null/todo?category=null
nnis not null/todo?category==nn

Searching

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

Ordering

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

Limiting and Pagination

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

GET /<model.id>?page=<pageindex>&pageSize=<pagesize>

GET /todo?page=0&pageSize=50

Formatting

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 using csv-express.

GET /<model.id>?format=csv

GET /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).

Updating Data

Record creation

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.

Update

PATCH or PUT can be used to update specific records.

PATCH /<model.id>/<id>

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

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.

Deletion

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

DELETE /<model.id>/<id>

DELETE /todo/5

Extras endpoints

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

Discovery

Returns the list of Objects and their APIs (only objects flagged active are included).

GET /

Note: This end-point can be disabled in the configuration with {apiInfo: false}.

Charts

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

Stats

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

GET /<model.id>/stats

GET /todo/stats

Lists of Values

Dropdown fields in the UI (field.type="lov" in the model) have a REST endpoint to get the list of values (used for dropdowns in the UI).

GET /<model.id>/lov/<field.id>

GET /todo/lov/category

File upload

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".

Nested collections

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

API version

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

GET /version

License

Copyright (c) 2019 Olivier Giulieri.

Evolutility-Server-Node is released under the MIT license.

RunKit is a free, in-browser JavaScript dev environment for prototyping Node.js code, with every npm package installed. Sign up to share your code.
Sign Up for Free