Skip to content

About

Model-driven REST API for CRUD: endpoints, charts, stats and OpenAPI docs generated from models stored as JSON in PostgreSQL. Edit models through the API, and tables follow. Node.js, Express, TypeScript.

Topics

Resources

Stars

116 stars

Watchers

22 watching

Forks

Repository files navigation

Evolutility-Server-Node · CI GitHub license npm version Sponsor

Framework for building REST endpoints for CRUD with models rather than code

Evolutility-Server-Node provides a set of generic model-driven REST endpoints for CRUD (Create, Read, Update, Delete) and simple charts on objects of different structures.

screenshot

Stack: TypeScript with Node.js, Express, and PostgreSQL.

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

Table of Contents

  1. Installation
  2. Setup
  3. Configuration
  4. Models: Object - Field - Collection - Sample model
  5. REST API: Get - Update - Charts - More
  6. Users and Login
  7. API Documentation
  8. Rate Limiting
  9. 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. Create the tables and the models in the database with the SQL scripts generated by Evolutility-Models:

# In evolutility-models
npm install
npm run codegen:db

# Run the scripts (in "dist/db/sql/") in order
psql -d evolutility -f dist/db/sql/001_create_db_{datetime}.sql   # demo tables
psql -d evolutility -f dist/db/sql/002_seed_data_{datetime}.sql   # demo data
psql -d evolutility -f dist/db/sql/003_create_models_{datetime}.sql   # tables for models
psql -d evolutility -f dist/db/sql/004_seed_models_{datetime}.sql   # models
psql -d evolutility -f dist/db/sql/005_create_users_{datetime}.sql   # users and sessions
  1. In the file config.ts set the PostgreSQL connection string and the schema name to access your database.

  2. Review other configuration options as needed.

  3. In the command line type the following:

# Install dependencies
npm install

# Create your user (the password is shown once), see "Users and Login"
npm run user:add -- you@example.com admin
# or with your name and password
npm run user:add -- you@example.com admin --name="Your Name" --password=a-long-password

# Run the node.js server
npm start

The server loads the models from the database when it starts.

To stop the server, use Ctrl+C (or send it a SIGTERM, as process managers and containers do): it stops accepting requests, finishes the ones in progress, closes its connections to the database, and exits. Requests still running after 10 seconds are cut off.

URLs on localhost:

Configuration

Configuration options are set in the file config.ts.

Option Description
apiPath Path for REST API (i.e.: "/api/v1/").
apiPort Port for REST API (i.e.: 2000).
connectionString DB connection string (i.e.: "postgres://evol:love@localhost:5432/evol").
schema DB schema name (i.e.: "evolutility").
pageSize Number of rows per page in pagination (default = 50).
maxPageSize Largest page size a client can request with "pageSize" (default = 1000).
lovSize Maximum number of values allowed for form dropdowns (default = 100).
csvSize Maximum number of rows in CSV export (default = 1000).
csvHeader Use field ids or labels as CSV column headers.
uploadPath Path for pictures and document uploads (i.e.: "../evolutility-ui-react/public/pix/").
uploadMaxSize Maximum size of uploaded files in bytes (default: 2MB).
uploadExtensions Allowed file extensions for uploads (default: common image and document types, no SVG or HTML).
logToConsole Log SQL and errors to console.
logToFile Log SQL and errors to a file. Log files are named like "evol-2019-09-15.log".
wComments Allow for user comments (not implemented yet).
wRating Allow for user ratings (not implemented yet).
wTimestamp Timestamp columns w/ date of record creation and last update.
wWhoIs "Who-is" columns "created_by" and "updated_by", set to the logged-in user when records are created and updated (values sent by clients are ignored). Reading one record also returns the users' names ("created_by_txt", "updated_by_txt"). The tables need these columns: databases created w/ Evolutility-Models have them; for older tables run ALTER TABLE ... ADD COLUMN created_by integer, ADD COLUMN updated_by integer;, or set wWhoIs: false. Default: true.
createdDateColumn Column containing created date (default "created_at").
updatedDateColumn Column containing last update date (default "updated_at").
schemaQueries Enables endpoints to query for lists of tables and columns in the database schema.
apiDesigner Enables editing models through the API (POST, PUT, DELETE on /meta/models). Saving a model can add tables and columns to the database. Default: true.
rateLimit Maximum number of API requests per IP per 15-minute window. Set to 0 to disable. Default: 500.
trustProxy Set to true when running behind a reverse proxy (nginx, Heroku, etc.) so the real client IP is used for rate limiting instead of the proxy's IP. Default: false.
corsOrigins Web sites allowed to call the API from a browser (the url of the UI), with their login cookie. Writes from other sites are refused. Default: ["http://localhost:5173"].
auth.enabled Require a login. Default: true.
auth.anonymousRole Role of visitors who are not logged in: null (login required) or "viewer" for a public read-only site. Default: null.
auth.sessionDays Number of days before having to log in again. Default: 7.
auth.cookieSecure Send the login cookie over HTTPS only. Default: true when NODE_ENV is "production".
auth.cookieSameSite "Strict" when the UI and the API are on the same site (default), "None" (with HTTPS) when they are on different sites.

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.

Models are stored in the database as JSON (one document per model in the table "evol_model", and every saved version in "evol_model_history"). They are loaded when the server starts, and can be edited through the Models API or in the Designer of Evolutility-UI-React. Changes apply right away, without restarting the server.

The models are written in Evolutility-Models, which generates the SQL to store them in the database.

Object

Property Description
id Unique key to identify the entity (used as API parameter).
table Driving database table name (there are secondary tables for fields of type "lov").
pKey Name of the Primary key column (single column of type serial). Defaults to "id". In the data the key is always called "id".
fields Array of fields.
titleField Field id for the column value used as record title.
noCharts Disable charts and dashboard views.
noStats Disable stats for the object.

Field

Property Description
id Unique key for the field (can be the same as column but doesn't have to be).
column Database column name for the field.
lovTable Table to join to for field value (only for fields of type "lov").
lovColumn Column name (in the lovTable) for field value (only for fields of type "lov").
lovKey Primary key column name in the lovTable (default to "id").
lovIcon Set to true to include an icon with LOV items (only for fields of type "lov").
object Model id for the object to link to (only for fields of type "lov").
type Field 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)
  • list (multiselect)
  • money
  • text
  • textmultiline
  • time
  • url
required Determines if the field is required for saving.
readOnly Display field as readOnly (not editable).
inMany Determines if the field is present (by default) in lists of records.
inSearch Determines if the field is used in text searches.
max, min Maximum/Minimum value allowed (only applies to numeric fields).
maxLength, minLength Maximum/Minimum length allowed (only applies to text fields).
unique Values must be unique (not implemented yet).
noCharts Exclude field from charts.
noStats Exclude field from Stats.
deleteTrigger Deleting 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.

Property Meaning
id Unique key for the collection.
table DB Table to query (master table, other tables will be included in the query for "lov" fields).
column Column in the detail table to match against id of object.
object Model id for the object to display (optional).
orderBy Column(s) to sort by, e.g. "name" or "drink_date".
fields Array of fields (objects or ids). Fields in collections can be field objects or just ids of fields in the collection's object.

Example of collection in Wine cellar.

Sample model

Below is the model for a To-Do app (properties used by the server only, the model also has properties for the UI).

{
  "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 in a separate repository at evolutility-models.

REST API

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

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

Requesting Information

Get One

Gets a specific record by ID.

GET /{model.id}/{id}

GET /todo/12

By default this endpoint returns nested collections with the record. To reduce response size, collections can be omitted by using the parameter "shallow".

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

GET /todo/12?shallow=1

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:

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

Operator Meaning Example
eq equals /todo?category=eq.1
gt greater than /todo?duedate=gt.2026-01-15
lt less than /todo?duedate=lt.2026-01-15
gte greater than or equal /todo?duedate=gte.2026-01-15
lte less than or equal /todo?duedate=lte.2026-01-15
ct contains /todo?title=ct.e
sw starts with /todo?title=sw.a
fw ends with /todo?title=fw.z
in one of a list of values /todo?priority=in.1,2,3
0 is false or null /todo?complete=0
1 is true /todo?complete=1
null is null /todo?category=null
nn is 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" limit the response rows. "pageSize" is capped at maxPageSize in config.ts.

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

GET /todo?page=0&pageSize=50

Formatting

By default all APIs return data in JSON format. Add format=csv to export data as CSV (Excel-compatible).

GET /{model.id}?format=csv

GET /todo?format=csv

Note: Each returned object includes a _full_count property indicating the total number of matching records (before pagination).

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, saving a round-trip.

Update

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}

Note: The request returns the updated record, saving a round-trip.

Deletion

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

Extra endpoints

In addition to CRUD, Evolutility-Server-Node provides endpoints for charts, lists of values, file upload, and API discovery.

Discovery

Returns the list of all active objects with URLs to their REST endpoints.

GET /

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

GET /?id={model.id}

GET /?id=todo
GET /?id=contact

Note: These endpoints must be enabled in the configuration with { apiInfo: true }.

Charts

Returns aggregated data for fields of type lov, boolean, integer, decimal, and money. Use noCharts: true on a field to exclude it from charts.

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

GET /todo/chart/category

Stats

Returns the total count, and one object per field with the number of nulls (or empty strings), plus the min, max, average, variance, standard deviation and total for numeric fields, or the min and max length for string fields.

GET /{model.id}/stats

GET /todo/stats
{
  "count": 42,
  "title": { "nulls": 3, "minLength": 4, "maxLength": 52 },
  "price": { "nulls": 5, "min": 1, "max": 99, "avg": 20.5, "sum": 861, "stddev": 12.3, "variance": 151.3 },
  "category": { "nulls": 0 }
}

Lists of Values

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/category
GET /todo/lov/category?search=pro

File upload

Uploads a file to the server filesystem, in a subfolder named after the model id inside the directory specified by uploadPath in config.ts.

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

POST /comics/upload/5

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

File names are sanitized (no directories or special characters) and never overwrite an existing file (a random suffix is added). Errors: 404 unknown model, 400 no file, 413 file too large, 415 file type not allowed.

Nested collections

If the model has collections defined, they can be queried with this endpoint.

GET /{model.id}/collec/{collection.id}?id={id}

GET /winecellar/collec/wine_tasting?id=1

Models

Models are stored in the database as JSON (table "evol_model"). Each model has a version number (_version), and every saved version is kept (table "evol_model_history").

Get all models (with their _version).

GET /meta/models

Get one model.

GET /meta/models/{modelid}

GET /meta/models/todo

Get the JSON Schema of the metamodel (the properties a model can have, with their descriptions). The Designer of Evolutility-UI-React uses it to check models while they are edited.

GET /meta/schema

Get the saved versions of a model, or one of them.

GET /meta/models/{modelid}/history

GET /meta/models/{modelid}/history/{version}

Create a model (POST the model as JSON).

POST /meta/models

Update a model (PUT the model as JSON, with the _version it was read with). If someone else saved the model in between, the update is refused with a 409 error.

PUT /meta/models/{modelid}

Delete a model. Its table and data are kept in the database. Models used by other models (in lov fields or collections) can't be deleted.

DELETE /meta/models/{modelid}

Add ?dryRun=true to POST or PUT to only check the model and list the changes to the database, without saving.

Models are validated before they are saved (names of tables and columns, field types, references to other models...). Invalid models are refused with a 400 error and the list of invalid properties:

{
  "error": "Invalid model",
  "invalids": [{ "id": "fields[2].column", "value": "my col", "condition": "letters, digits and _ only (not starting w/ a digit)" }]
}

Saving a model also updates the database (in the same transaction):

  • a new model creates its table (and the tables for its lists of values),
  • a new field adds a column,
  • nothing is ever dropped: removed fields keep their columns and data,
  • changing the type of a column or the table of a model is refused.

The answer to POST and PUT has the saved model, the SQL run, and warnings:

{
  "model": { "id": "todo", "_version": 3, "...": "..." },
  "sql": ["ALTER TABLE \"evolutility\".\"task\" ADD COLUMN IF NOT EXISTS \"tag\" text;"],
  "warnings": [],
  "dryRun": false
}

Note: POST, PUT, and DELETE must be enabled in the configuration with { apiDesigner: true }, and need the admin role (see Users and Login).

Schema tables and columns

These endpoints query the database structure (rather than the data) and return 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}/columns

GET /db/contact/columns
GET /db/task/columns

Note: These endpoints must be enabled in the configuration with { schemaQueries: true }.

API version

Returns the API version from package.json.

GET /version

Users and Login

Users have a role:

Role Can
viewer Read records, models, charts, and stats.
editor Viewer, plus create, update, delete, and upload records.
admin Editor, plus edit models (Designer) and query the database structure.

Users and sessions are stored in the tables "evol_user" and "evol_session" (created by the SQL script 005_create_users from Evolutility-Models). Passwords are stored as scrypt hashes.

Create a user, or set a new password (and role) for an existing one:

# Password generated and shown once
npm run user:add -- you@example.com admin --name="Your Name"

# Password given
npm run user:add -- someone@example.com viewer --password=a-long-password

Setting a new password ends the sessions of the user.

Log in (the answer sets the session cookie, "httpOnly" so scripts can't read it):

POST /auth/login
{ "email": "you@example.com", "password": "..." }

Log out, or get the current user and role:

POST /auth/logout
GET /auth/me

Manage users (admin role). A password is generated (and returned once) when none is given. Admins can't delete or deactivate themselves, or remove their own admin role. A new password, or deactivating a user, ends the user's sessions.

GET    /auth/users
POST   /auth/users       { "email", "name", "role", "password" (optional) }
PATCH  /auth/users/{id}  { "name", "role", "active", "password", "resetPassword": true }
DELETE /auth/users/{id}

Requests without the role needed are refused with a 401 error (not logged in) or 403 (logged in, but role too low). After 10 failed logins in 15 minutes, logins from that IP address are refused for a while.

To disable login (all requests are allowed), set { auth: { enabled: false } } in the configuration.

API Documentation

Evolutility-Server-Node automatically generates an OpenAPI 3.1 specification from the loaded models — no manual authoring required. Every field type, required constraint, and endpoint is derived directly from the model definitions.

OpenAPI spec

Returns the full OpenAPI 3.1 spec as JSON, reflecting all currently active models.

GET /api/v1/openapi.json

Swagger UI

An interactive API explorer is served as a static page at: http://localhost:2000/api-docs.html

It loads Swagger UI and points it at /api/v1/openapi.json. Every model gets its own tag grouping CRUD, stats, charts, list-of-values, and sub-collection endpoints. The spec and the UI stay in sync with the models automatically — adding or modifying a model is immediately reflected.

screenshot

screenshot

The spec can also be imported directly into Postman, Insomnia, or any OpenAPI-compatible client or code generator.

Rate Limiting

All API endpoints are rate-limited per IP address to protect the server and database from abuse. The default is 500 requests per 15-minute window, configurable via rateLimit in config.js. Set it to 0 to disable rate limiting entirely.

When the limit is exceeded the API returns 429 Too Many Requests with a RateLimit header indicating when the window resets.

Reverse proxy setup

If the server runs behind a reverse proxy (nginx, Apache, a load balancer, Heroku, Render, Railway, etc.), Express sees the proxy's IP address instead of the real client IP. Without correction, the rate limiter would count all users as one and block everyone simultaneously.

To fix this, set trustProxy: true in config.js. This tells Express to read the real client IP from the X-Forwarded-For header that the proxy injects:

// config.js
rateLimit: 500,
trustProxy: true,   // enable when behind nginx, Heroku, etc.

Only enable trustProxy if you actually control the proxy and trust it to set X-Forwarded-For correctly — enabling it on a directly internet-facing server would allow clients to spoof their IP and bypass the limiter.

License

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

Encourage this project by becoming a sponsor.

Copyright (c) 2026 Olivier Giulieri.

About

Model-driven REST API for CRUD: endpoints, charts, stats and OpenAPI docs generated from models stored as JSON in PostgreSQL. Edit models through the API, and tables follow. Node.js, Express, TypeScript.

Topics

Resources

Stars

116 stars

Watchers

22 watching

Forks

Releases

Sponsor this project

Packages

Used by

Contributors

Languages