SAP B1, MariaDb, Node.JS express and OpenUI5 walk into a bar…
Introduction
Lately, my attention has been drawn to javascript technologies and I thought it’d be good fun (that’s how funny I am), to learn some of them.
Since I’ve been working on SAP Business One for several years now, my idea was to hit two birds with one stone and build a simple web application that would eventually communicate with the ERP, breaking the codes of the traditional B1 applications you may find out there. In other words: I didn’t want to build a .NET API nor the Entity Framework to fetch data from the database (we’ll talk about the writing a few paragraphs below).
For this project, I used SAP B1 9.3 PL11 for MSSQL and the french demo database.
They say a picture is worth a thousand words so here’s what this is about (the orders you see in the gif all come from SAP B1):
Disclaimer: this is not a tutorial, but rather a description of my approach at a Business One web UI.
With the upcoming web version of B1 (version 10), now than ever it is more important to understand how Business One can interract with the open world.
Historically, people would customize B1 by building Add-Ons that would directly plug into the fat client and enhance the solution using both DI-API (to add/update/delete business objects) and UI-API (to build custom screens).
This approach is definitely an option if the users don’t want to leave the B1 client to perform custom tasks.
In this article, we will build a Sales Order screen using SAPUI5.
You may already know some of the most popular javascript front-end frameworks on the market, a non exhaustive list would be: React, Angular and Vue.JS. Even if you’ve never used them, there’s a great chance you’ve, at least, heard about them. Applications built on top of these frameworks are also called SPA for Single Page Applications because you usually only request one page to the server, and that page is re-rendered with javascript as you navigate through the app.
However, people that work around SAP may know another JS framework that is (of course), far less used that the ones I mentionned above. Indeed, I’m talking about:
When working with UI5, you may hear a lot of different names for it: SAPUI5, OPENUI5, Fiori App etc…
There’s a lot of confusion as to what is what. I am not going to go into the details but let’s just see it this way: OpenUI5 is the base framework, it contains all the controls you need to build your application and is free to use. SapUI5 adds another layer on top of OpenUI5 with more controls (for instance, you can use charts that aren’t available in OpenUI5) and requires a license to use. Then, Fiori refers to the UX guidelines defined by SAP. Finally, SAP Fiori Elements which is a framework on top of UI5 to automate the creation of page based on custom OData annotations provided by SAP.
If you want to dig deeper into this, just check the demo kits of the two versions (OpenUI5 & SapUI5), you will see the difference:
So... This is all about HANA right... ? 🤨
No.
HANA adds a lot of extensibility features, and one of them is holy grail for SAP B1 developers. I’m talking about the Service Layer.
The Service Layer is an out-of-the-box and simple REST API you can use to Create, Read, Update and Delete entities in SAP Business One. It is only available on HANA for the moment (for production purpose) but you can try it out for the MSSQL version for testing purposes. And I must have read somewhere that it will be available for MSSQL on version 10 (along with the Web UI). 
UPDATE 2022: Service Layer is now available for SAP B1 version for MSSQL.
I can’t even stress how much I love the Service Layer #BFF, I wish it would have been available on the MSSQL version from the start because it just makes building apps for Business One so much easier.
Anyway, the point is, you can now have a cheap test environnement to play around with.
For this application, I am using a MSSQL B1 with the « experimental » service layer.
Technologie stack
Alright, enough of the introduction blabla, let’s see exactly what types of bricks I used to build that house.
Back-End
SAP Business One (version for MSSQL) 9.3 PL11
Service Layer to Create, Read, Update and Delete data in B1
MariaDb to store my application users
Node.JS + Express to build a custom API (think of it as a middleware between B1 and our web application)
Front-End
OpenUI5 (Javascript front-end framework to build our UI)
How do they work together?
Let’s focus on the back-end because this is where the interesting part is. See that box on the bottom left « ExpressJS + node JS » ? It is the keystone of everything: it authenticates user, and then, allows him to perform a specific set of actions based on his roles and permissions. It is also the only entry point for every request sent by our web application, which cannot sends requests directly to the databases nor the service layer.
- I chose to store my users in a separate database and use a JWT authentication mechanism.
- User logs in and get a JWT token that he must send in every subsequent request.
- Based on the token, the API knows which user it is talking to and can then check the authorizations and permissions for that user.
- I am using TypeORM to query the MariaDb data which allows me to easily handle all my entites (such as Users, Roles and Permissions). Why TypeORM? Simply because it was compatible with MariaDb and available in Typescript. Remember that I started this project to learn technologies I’ve never really used. And I must admit I was totally amazed by the ease of use of TypeORM!
- As mentionned earlier, calling the Service Layer is also done through the Node.JS Express API. The user never queries directly the Service Layer.
How do you make sure of it? By setting the « Access-Control-Allow-Origin » header in the Service Layer configuration and setting the API IP address. Basically, the web application calls the API which checks if the user has the right to use the Service Layer, and then calls the Service Layer which performs actions with a pre-defined technical user.
And of course, since we’re using the Service Layer, that means our application is able to Create, Read, Update and Delete (aka CRUD) SAP B1 Data.
Handling the authorizations
Some words about the authentication mechanism, the first step is to login to the application, which grants you a token should the login and password match a database user. The /Auth/Login endpoint is public and can be accessed by anyone.
Once the user is authenticated, he can then call the other routes which are protected, meaning that the user must send a valid JWT token. (remember, it’s in the cookie!)
If the JWT is not valid for some reason, either expired or altered, the request fails and returns an HTTP Status 401 Unauthorized.
Each subsequent call performs its own verifications, for instance:
- Is the user active?
- Does the user have access to that particular resource?
- Example: The user is authenticated, active and tries to call the « api/B1 » endpoint but he doesn’t have the « CALL_B1 » permission.
Does he listen to Drake?(alright, I didn’t implement that one)
All of these permissions are stored in MariaDb and checked against if needed when the user calls an endpoint.
Developement Environnement
To develop both the API and the web application, I simply used… Visual Studio Code.
The OpenUI5 application is handled via the OpenUI5 CLI which helps you kickstart and run your application. (More information here)
Conclusion
So, that was my attempt at a Business One client using JS technologies. And yes, lots of things can be improved/changed, like the authentication that could be performed using Auth0, the front-end framework that can be switched and so on…
With the upcoming web version of SAP B1 now more than ever it is the time to keep up with all of these web technologies!
Hope you liked the article! 🤙🏼
Check out the gallery below for some more screenshots of the app!
