rmsqldb

Provides express middleware and JS decorator functions allowing easy management of connections to the legacy RM databases. It also provides a mapping of legacy databases in order to connect to a legacy database using an routematch provided agency name.

Agency Header

In order to access a legacy database, the API call must include the header x-rm-agency-name which should match the name of the legacy database (which also matches the name of the agency). For instance a request intended for GRTA would include x-rm-agency-name: RM_GA_GRTA as a request header.

Authorization

To authorize that the requestor has access to the provided agency, the request must also include user data decrypted from a JWT bearer token. This data will include a set of scopes (permissions) that the requestor is allowed to access. If the scope includes access to the agency database listed in the header, the request is allowed. Otherwise, a 403 Forbidden response will be given.

Usage

rmsqldb provides both express middleware and javascript decorator functions for managing access to the legacy RM databases.

Consumption and Authorization of the x-rm-agency-name Header

The AgencyResolver middleware is provided to retrieve and authorize the agency name stored in the header of a request. Athorization requires that a JWT bearer token has already been decrypted on the request using the express-jwt middleware (as suggested by auth0). If no information from the token is found, an exception will be thrown.

import { AgencyAuthorizer } from "rmsqldb";

const app = express();
app.use(AgencyAuthorizer);
...
app.get("/", (req, res) => {
    const agency = req.agency;
});

As you can see, the middleware will attach the agency name to the request object where it can be used by any code that is processing the request.

Legacy Database Connections

The ConnectAgency middleware is provided to simplify access to the the legacy database for the agency specified. It requires the request to have first been processed by the AgencyAuthorizer middleware. Usage of the middleware will attach a connected db object to the request that wraps calls to the mssql library and can be used to communicate directly with the agency's database.

import { ConnectAgency } from "rmsqldb";

const router = express.Router();
router.get("/:id", ConnentAgency, (req, res) => {
    req.db.query("SELECT * FROM tblUser")
        .then(...)
        .catch(...);
});

See this document for more information about the usage of the db object.

A decorator function (RMDBConnect) is also provided for the purpose of providing a connection to the agencies database. This is intended for use on a controller function as opposed to on a route path. The two are interchangeable (depending on your preference) with the exception that the middleware can be applied to a base path (in which case it would apply to all downstream paths as well).

router.ts

import { UserController } from "./controllers"

export const router = express.Router();
router.get("/:id", UserController.GetUserById);

controllers.ts

import { RMDBConnect } from "rmsqldb";

export class UserController {
    @RMDBConnect
    GetUserById(req, res) {
        // EXAMPLE ONLY: SQL injection vulnerability!!
        req.db.query(`SELECT * FROM tblUser WHERE ID = ${request.params.id}`)
            .then(...)
            .catch(...);
    }
}