This article introduces a model-driven REST or GraphQL API for CRUD (Create, Read, Update, Delete) with which you can write simple models and the REST endpoints for CRUD will become available automatically.
Introduction
This article introduces a model-driven REST or GraphQL 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 provides three 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:
- Create a PostgreSQL database.
- In the file config.js, set the PostgreSQL connection string and the schema name to access your new database.
- 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 the file "config.js" to set the database connection and other options like paging and upload directory.
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
| Property | Meaning |
id | Unique key to identify the entity (used as API parameter) |
table | Database table name |
fields | Array of fields |
titleField | Field id for the record title |
searchFields | Array of field ids for fields used to perform searches |
Field
| Property | Meaning |
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 "lov" type) |
lovcolumn | Column name (in the lovtable) for field value (only for fields of "lov" type) |
type | Field type is not a database column type but more a UI field type. Possible field types:
boolean (yes/no)datedatetimedecimaldocumentemailimageintegerlov (list of values)moneytexttextmultilinetimeurl
|
readonly | Prevents field modification |
inMany | Determines 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 that it is not worth it). You can read more about it in my previous article, Minimalist Meta-Model for CRUD Applications.
REST API
The API of Evolutility-Server-Node is heavily inspired (even partially copied) from the one of PostgREST which also provides 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:
| Operator | Meaning | Example |
eq | equals | /todo?category=eq.1 |
gt | greater than | /todo?duedate=gt.2017-01-15 |
lt | less than | /todo?duedate=lt.2017-01-15 |
gte | less than or equal | /todo?duedate=gte.2017-01-15 |
lte | less than or equal | /todo?duedate=lte.2017-01-15 |
ct | contains | /todo?title=ct.e |
sw | start with | /todo?title=sw.a |
fw | finishes with | /todo?title=fw.z |
in | one of a list of values | /todo?priority=in.1,2,3 |
0 | is false | /todo?complete=0 |
1 | is true | /todo?complete=1 |
null | is null | /todo?category=null |
nn | is 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 indicates 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.
Discovery
Returns the list of Objects and their APIs (only objects flagged active are included).
GET /
Note: This end-point must be enabled in the configuration with {apiInfo: true}.
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
Statistics
Returns the total count, and the min, max, average, and total for numeric fields in the model.
GET /<object>/stats
GET /todo/stats
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".
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</code>
API Version
This endpoint gets the API version (as specified in the project's package.json file).
GET /version
GraphQL
Evolutility-Server-Node provides a GraphQL interface based on the same models as the REST API.
When running the project locally, the URL for garphiQL is http://localhost:2000/graphql.
Requesting Information
Get One record by Id
For getting a single record by Id.
{
contact (id: 1 ){
firstname
lastname
category_txt
email
}
}
Get Many records
All objects are exposed for queries with search and filters. Filter use the same syntax for conditions as the REST API (for example: { firstname: "sw.A" } for "Firstname starts with "A").
Fields of type "lov" (List of values) are represented as 2 fields for Id and value.
{
urgent_tasks: todos ( complete: "false", priority: "lt.3" ){
title
description
priority
priority_txt
category
category_txt
complete
}
ab_a_contacts: contacts (search: "ab", firstname: "sw.A") {
id
firstname
lastname
category_txt
email
}
}
Charts Data
For all objects records can be aggregated and counted by field (for fields of numeric or "lov" types).
{
contacts_by_category: contact_charts(fieldId:"category"){
label
value
}
task_by_priority: todo_charts(fieldId:"priority") {
label
value
}
restaurants_by_cuisine: restaurant_charts(fieldId:"cuisine") {
label
value
}
}
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 implementations of a matching model-driven UI, Evolutility-UI-React (for React) and Evolutility-UI-jQuery (for jQuery and BackboneJS).
History
- 15th February, 2017: Initial version
