Expose Database over Rest API with TypeORM and First-Class Entity

Photo by Arnold Francisca on Unsplash

Exposing a database over REST API seems like a crazy idea. But with good API design, proper security handling, and options for customization, it can provide an advantage for building API instantly.

It’s helpful for a small team or a full-stack programmer that needs to create an application with backend functionality while focusing their work on creating a good UI/UX for user experience.

In this story, I will explain the Plumier feature called first-class entity which internally uses TypeORM for data access. First-class entity focuses on security, customization, and clarity. First-class entity guarantees the exposed API to be secured, fully customizable, and transparent for security review.

For those of you who are unfamiliar, Plumier is a TypeScript backend framework that focuses on productivity with a dedicated reflection library to help you create a robust, secure, and fast API delightfully.

The Challenge

Some problems arise related to exposing a database over an API, especially the SQL database over a Rest API. Most are security issues, but there are more of them.

• How does it work with relation? One-to-many, many-to-one, and one-to-one? How to set relation data? And how to retrieve them related to the parent's record?
• Since the database table may contain sensitive data, how does it prevent data from getting leaked? How does it secure read/write access based on user role?
• How does it handle micro-processes such as password hashing before the record is saved? Does it send notification (email or push notification) after the data is saved? Or even use a request-context value such as getting the current login user for createdBy column?
• Since the table is exposed directly while using REST API, how does it handle querying (filter, select, order)? How does it ensure the query isn’t harmful to sensitive data?

Before we go into further details, we will take a sneak peek at what the first-class entity looks like and how Plumier handles it behind the scene.

Terminology

The first-class entity refers to an ORM (Object Relational Mapping) entity that is treated as a first-class citizen amongst the framework components. It has more control than a common ORM entity, which makes it possible to be exposed as API safely. Besides its original task as a representation of table schema, first-class entities have control over framework features such as.

• Control over the API URLs.
• Control over validation.
• Control over request and response schema for value/data type conversion.
• Control over security and authorization.

A first-class entity is not an ORM-specific feature, instead, it's an independent extension feature for the entity provided by Plumier. Possibly it can be applied in other ORM with some normalization.

How Does It Work?

The simplest first-class entity is a TypeORM entity decorated with Plumier configuration like below.

By applying @genericController() decorator on the entity like above, Plumier route generator understands that the entity will be handled by a generic controller. Plumier has some built-in generic controllers which will be the base class of each controller created. For example, for a simple (non-relation) entity the base generic controller is as shown below.

Above is a simplified version of the built-in generic controller. It has T generic parameter which will be the model of response and a request body, which, in this case, is the entity.

It also has TID parameter that defines the data type of the entity ID, furthermore, this is used to check the data type of the ID on resource identifier with ID, such as GET /path/:id, PUT /path/:id etc. It makes sure the value provided matches the data type of the entity ID.

Plumier reflection library supports generic type introspection that makes it possible for Plumier route generator to generate controller inherited from the generic controller. In the background, Plumier route generator creates a new controller on the fly like below.

The above controller inherits all its base class methods which produce six routes below.

GET    /products       # get products list
GET    /products/:id   # get product by id
POST   /products       # add new product
PUT    /products/:id   # replace product
PATCH  /products/:id   # modify product 
DELETE /products/:id   # delete product

How Does It Work with Object Relation?

It's a common behavior for an ORM entity to have a one-to-one relation or many-to-one relation, which usually has a data type of object, for example, our last Product entity may have category property which of type Category.

The problem is, how will we set the value for category property? Since it's just a reference property, its value is the ID of the Category instead of the category object itself. Another important issue is since the data type of category property is of type Category, It’s impossible for us to provide value with type of number (the ID) because the framework type converter will refuse it.

Plumier solves this issue by providing a custom type converter allowing an object relation to be set by ID match with the object ID data type, it keeps maintaining the data type conversion. So in this case, we can send product request body on POST method like below.

POST /products
{ 
   /** ---- other properties ---- **/
   // use the ID instead of the category object 
   "category": 20
}

category property also automatically populated when requesting the product API GET /products or GET /products/:id, which solves the issue when retrieving object relation.

How Does It Work With Array Relation?

Unlike object relation, setting value in an array relation can be problematic, because it can’t be done by providing the IDs. For example, when modifying the record it's unclear if it's intended to add the collection or replace the current collection with the new value. Retrieving the values also problematic when the property has an extensive amount of records, which usually solved by pagination.

REST has best practice when describing nested resources. It's done by using nested resource identifiers (URLs) such as.

GET    /parents/{parentId}/children?offset&limit&filter
GET    /parents/{parentId}/children/{id}
POST   /parents/{parentId}/children
PUT    /parents/{parentId}/children/{id}
PATCH  /parents/{parentId}/children/{id}
DELETE /parents/{parentId}/children/{id}

Plumier follows those practices and provides a special generic controller to serve nested resources. For example, having an entity with relation Shop — Product like example below.

Note that the @genericController() decorator applied directly on the products property. It tells Plumier route generator to use a nested generic controller when generating the routes. As the result, it produces nested routes below.

GET    /shops/:pid/products       # get shop's products list
GET    /shops/:pid/products/:id   # get shop's product by id
POST   /shops/:pid/products       # add new shop's product
PUT    /shops/:pid/products/:id   # replace shop's product
PATCH  /shops/:pid/products/:id   # modify shop's product
DELETE /shops/:pid/products/:id   # delete shop's product

How Does It Secure The Data?

Finally, we arrived at the most important part of the story. How do we protect sensitive data being accessed (read/write) by the API client, such as a password or credit card number being leaked? A timestamp or delete flag being overwritten? And even how do we protect sensitive data that is based on user role?

Plumier has policy-based authorization, which can be applied to first class entity declaration intended to secure API endpoints, request body, and response body. Our last Product entity then look like this.

The snippet above shows that we add some configuration to restrict access to users based on their role.

1. We configure the generic controller to restrict access to its mutators (PUT, PATCH, POST, DELETE) endpoints, only accessible by ShopOwner and Staff policy.
2. We protect the basePrice property only accessible by ShopOwner and Staff policy. Since we already secured the mutators endpoints, it’s not necessary to secure write access to the basePrice property because route authorization evaluated earlier than others.
3. We protect the price property only can be set by ShopOwner policy, and can be seen by anyone.
4. We protect the createdOn for being overwritten by anyone. Because it will be populated by TypeORM.

To define the policy ShopwOnwer and Staff is quite simple. The trick stays on the Role claims we add on the JWT token during the login process. Then we check that claim on authorization logic like below.

How Does It Solve Request Context Process?

Like mentioned earlier, an API sometimes requires doing some microprocesses before or after an entity is saved to the database. TypeORM provided entity listener to execute some microprocess during entity events, such as BeforeInsert, AfterInsert, BeforeUpdate, AfterUpdate etc. But, we can’t depend on them because it's not a request context process. We are unable to retrieve request data from inside the method.

Plumier provided request hook which is a method called internally by a specialized middleware. Its supported parameter binding, to easily bind request-context value into a method parameter for further use.

For example in our previous Product entity, we add createdBy entity which should automatically be populated with the current login user.

The important part of the code above is from line 9 to 16. First, we secure the createdBy property with @authorize.readonly() decorator, to prevent any user overwrite the value of the property. Then we provide the @preSave("post") hook, which marks the method as a request hook that executed before the entity saved to the database. Note that it uses post filter, which means the hook only called on POST Method.

Then on line 14, we bind request context value (the JWT Claims) used to populate the value of createdBy property.

How Does It Work With Queries?

Since the table exposed directly over an API, by default when getting a list of records will return records in the default schema and order. It requires a further query (filter, select, order) to make the data returned match our need.

Back to REST best practice, defining an extra parameter on REST identifier done using a query string like below.

GET /users?name=john&deleted=false

The problem of using query string is it's impossible for an API client to query data using complex filtering such as using OR operator, or even filter with grouping such as (name=john or name=jane) and deleted=false.

Plumier provided Query Parser, which provided a simple expression language parser. It provided three types of parser that is: filter parser, select parser, and order parser. Based on the issue above, can be solved easily using the request below.

GET /users?filter=((name='john' or name='jane') and deleted=false)

It's also possible to provide orders and select at the same time like below.

# get list users which is not deleted
# select only the name and email
# order by createdAt descending
GET /users?filter=(deleted=false)&select=name,email&order=-createdAt

The above query string doesn’t parsed directly into SQL query but parsed into TypeORM query. So it's guaranteed to be handle SQL injection properly.

The query parser respects the user access to queried properties. It checks if the user has read access to the property. For example, giving our previous Product entity below.

Based on the configuration above, we give read access to the basePrice property only to specific roles. It means querying basePrice only works to those roles too.

GET /products?filter=basePrice > 200

The request above only works when the current login user has ShopOwner or Staff role. Other than that will return HTTP error 401 or 403. This security restriction also applied to Select Parser and Order Parser.

More Extreme Customization?

The first-class entity is so flexible, @genericController() receive some configuration to customize the generic controller behavior you need, you can check the comprehensive documentation here. In case those customization doesn’t meet your need, you can omit using first-class entity and create controller manually or by extending the generic controller directly. Plumier provided a factory to create the reusable generic controller like below.

The GenericController is a factory function that returns the generic controller class. You can put the above controller anywhere on your project directory and Plumier will treat them like a common controller.

Keep in mind above modification required rarely, mostly all the @genericController() configuration can solve the customization you need.

Last Words

Finally, we arrive at the end of this story. If you were following the story above, you now understand how the first-class entity can speed up your API development by exposing the database directly and providing good API endpoints, while maintaining the safety of your data. Refer to the Plumier documentation on how to start a first-class entity project here.

Plumier is a relatively young framework. If you love this feature, feel free to star the project on its GitHub repository.

More content at plainenglish.io