Click here to Skip to main content
12,762,098 members (40,408 online)
Click here to Skip to main content
Add your own
alternative version

Stats

4.4K views
80 downloads
9 bookmarked
Posted 14 Feb 2017

Model-driven RESTful API for CRUD and more

, 16 Feb 2017 MIT
Rate this:
Please Sign up or sign in to vote.
RESTful Micro-ORM for CRUD and more, written in Javascript, using Node.js, Express, and PostgreSQL.

Introduction

This article introduces a model-driven RESTful API for CRUD (Create, Read, Update, Delete). With it you can write simple models (specifying a database table and the set of columns to be exposed) and the REST endpoints for CRUD will become available automatically. No hand-coding of any SQL is necessary.

The concept could be implemented on different technology stacks and languages. Here I used Javascript (which generates SQL) with Node.js, Express, and PostgreSQL.

Background

Most projects need to Create, Read, Update, and Delete objects. When these objects are simple enough (one driving table and a few columns in the database), the code is very similar from one object to the next. In fact, the patterns are the same, and the only differences are the names of the tables and the names and types of the columns.

Of course, there will always be complex endpoints which need to be written by hand but by automating the simple ones we can save a lot of time.

Using the code

The sample database provide 3 examples for a To-Do list, an Addressbook, and a Graphic Novels inventory. These examples use object IDs "todo", "contact", and "comics", as specified in their models.

After playing with them, change the database (in config.js), change the models (in the models directory), and the RESTful API will change according to your data structure.

Follow these steps to install and setup the project with the sample database.

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

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

Configuration

In the root directory, edit config.js file to set options.

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.

Entity

PropertyMeaning
idUnique key to identify the entity (used as API parameter).
tableDatabase table name.
fieldsArray of fields.
titleFieldField id for the column value used as record title.
searchFieldsArray of field ids for fields used to perform searches.

Field

PropertyMeaning
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 "lov" type).
lovcolumnColumn name (in the lovtable) for field value (only for fields of "lov" type).
typeField type is not a database column type but more a UI field type. Possible field types:
  • boolean (yes/no)
  • date
  • datetime
  • decimal
  • document
  • email
  • image
  • integer
  • lov (list of values)
  • money
  • text
  • textmultiline
  • time
  • url
readonlyPrevents field modification.
inManyDetermines if the field is present (by default) in lists of records.

Notes: More field properties (unique, min, max, minLength, maxLength...) will be added later.

Sample model

Here is a 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", 
            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"
        }
    ]

};

This model only covers the backend. It is also possible to modelize the front-end (as long as the UX pattern is simple enough or it becomes so complex it is not worth it). More about it in my previous article Minimalist Meta-Model for CRUD Applications.

API

The API of Evolutility-Server-Node is heavily inspired (even partially copied) from the one of PostgREST which also provide generic CRUD but directly inspects the database schema instead of using models.

When running the project locally, the url for the "todo" app is http://localhost:3000/api/v1/evolutility/todo.

Requesting Information

Get One

To get a specific record by ID, use "< ObjectName >/ID".

GET /<object>/<id>

GET /todo/12

Get Many

Every model is exposed. You can query lists of items by using the model ID.

GET /<object>

GET /todo

Filtering

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

GET /<object>/<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.2017-01-01

These operators are available:

OperatorMeaningExample
eqequals/todo?category=eq.1
gtgreater than/todo?duedate=gt.2017-01-15
ltless than/todo?duedate=lt.2017-01-15
gteless than or equal/todo?duedate=gte.2017-01-15
lteless than or equal/todo?duedate=lte.2017-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/todo?complete=0
1is true/todo?complete=1
nullis null/todo?category=null
nnis not null/todo?category==nn

Ordering

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

GET /<object>?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 /<object>?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 express-csv.

GET /<object>?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 /todo
{ title: 'Finish testing', priority: 2}

Update

PATCH /todo
{ title: 'Finish testing', priority: 2}

Deletion

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

DELETE /<object>/<id>

DELETE /todo/5

Extras endpoints

In addition to CRUD, Evolutility-Server-Node provides endpoints for common UI needs like Charts and Lists of values.

Charts

For charts data, it is possible to get aggregated data.

GET /<object>/chart/<field id>

GET /todo/chart/category

Lists of Values

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

GET /<object>/lov/<field id>

GET /todo/lov/category

File upload

This endpoint lets you upload a file. The current (naive) implementation only saves the file on the file server in a folder named like the object id.

POST /<object>/upload/<id>

POST /comics/upload/5

With Query parameters: file and "fieldid".

Points of Interest

The Model Driven Architecture (MDA) will get you up-and-running faster but also save you time in the evolutionary maintenance of your project.

For example, when the project is done, we often need to add fields. Normally, that means adding a column to the database, and manually adding the field in every REST endpoint which uses the database table. With the model-driven approach, once the column is added to the database, you only need to add the field in a single place (the model) and every endpoint using that model will expose it.

The model-driven approach can also be applied to UI. If you want to play with it, I made two different implementation of a matching model-driven UI, Evolutility-UI-React (for React) and Evolutility-UI-jQuery (for jQuery and BackboneJS).

License

This article, along with any associated source code and files, is licensed under The MIT License

Share

About the Author

Evoluteur
United States United States
I'm a UI engineer for a startup in California. What I really enjoy is to build tools to describe UI in metadata, store that metadata (outside of the code) in a database, XML or JSON, and then dynamically generate the UI at run-time based on that metadata... which I do with my open source project Evolutility.

My articles on the topic:


My GitHub is Evoluteur.

You may also be interested in...

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170217.1 | Last Updated 16 Feb 2017
Article Copyright 2017 by Evoluteur
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid