Multitenant Business Application with PostgreSQL – Part 3

Purpose

Today our goal is get the approuter running locally and to route traffic to our tenant specific schemas.

Let’s get started, If you haven’t completed Part 2, run through that blog post then meet me back here. You can always reference the mt-tacoshop repository after postgreSQL deployment here.

You can review the project at the completion of this blog post here.

XSUAA set-up

The easiest way I have found to handle approuter traffic locally is to create a development specific xsuaa service. Instead of using a shared xsuaa services, we’ll be using a dedicated one. Duplicate and rename xs-security.json to xs-security-vscode.json and change the tenant-mode to “dedicated”. Also append “_vscode” to the name of our role-collections to avoid duplicates not allowed error.

Create new xsuaa service, create service key and view credentials

cf create-service xsuaa application mt-tacoshop-vscode-uaa -c ./xs-security-vscode.json
cf create-service-key mt-tacoshop-vscode-uaa default
cf service-key mt-tacoshop-vscode-uaa default

The last cmd will print the newly created xsuaa credentials to the terminal. Replace the default-env.json xsuaa credentials with mt-tacoshop-vscode-uaa credentials. Also replace the updated xsuaa credentials in the app/default-env.json file.

Assign the newly created role-collections to your BTP login in the trial dev space.

role-collection-assignment

Restart the cds-server and approuter. The approuter will re-direct you to a login page and and you can now route traffic through the approuter locally.

srv-info

Cusom Handler set-up

Create a srv/tenant-model.js file and populate the logic below. What we are doing here is passing in a schema name based on the xsuaa tenant id shown in the previous section. Our cds-dbm will register that we’re requesting data from a tenant specific schema and target it.

const cds = require('@sap/cds');
const lib = require('./library');

console.log('before-exports');
module.exports = cds.service.impl(function () {

    this.before('READ', '*', async (data) => {
        data.req.user.schema = lib.formatSchema(data.req.authInfo.getZoneId());
    });

    this.before('CREATE',  '*', async (data) => {
        data.req.user.schema = lib.formatSchema(data.req.authInfo.getZoneId());
    });
   
    this.before('DELETE', '*', async (data) => {
        data.req.user.schema = lib.formatSchema(data.req.authInfo.getZoneId());
    });

    this.before('UPDATE', '*', async (data) => {
        data.req.user.schema = lib.formatSchema(data.req.authInfo.getZoneId());
    });
});

Open the srv/library.js file and add the format Schema function. At this point in the project I discovered postgreSQL does not like schema names that begin with 0 so we should give each tenant the “_” prefix.

module.exports = {
    getSubscriptions: getSubscriptions,
    createRoute: createRoute,
    deleteRoute: deleteRoute,
    getDestination: getDestination,
    formatSchema: formatSchema
};

....

function formatSchema(tenantId) {
    //postgreSQL does not allow first character "0" in schema name
    var schema = "_" + tenantId;
    //postgreSQL seems to error when passing '-' to schema name
    schema = schema.replace(/-/g, '');
    return schema;
};

Let’s update our package.json to reflect this naming convention and also create a new tenant for local development (the trial – dev space tenant Id).

package.json

Re-deploy the database to local docker or to cloud. You should see our new tenant schemas synced up with the public schema. You can delete any previously un-used tenant schemas.

Add a tenant specific data Taco to the tenant shown on srv/info.

With both the cds server and approuter running, view your tenant specific taco. You’re now looking at tenant specific data. Way to go! Our multitenant SaaS solution is really starting to take shape.

Not all data is specific to tenants. What about common units of measure, currencies or parcel carriers? create a db/crosstenant-model.cds and populate a new entity with an entity that could be shared amongst our tenants.

namespace mt.tacoshop.crosstenant;
entity Currency {
    key code	: String(70);
	iso3		: String(3);
	iso3Number	: Integer;
}

Update our original tenant-model.cds to reference the new crosstenant entity.

namespace mt.tacoshop;

using {mt.tacoshop.crosstenant} from './crosstenant-model';
using {cuid} from '@sap/cds/common';

entity Taco: cuid {
    type        : String(50);
    value       : Decimal(10,3);
    Currency    : Association to crosstenant.Currency;
};

Populate some sample data in db/data/mt.tacoshop.crosstenant.Currency.csv

code,iso3,iso3Number
USD,USD,840
CAD,CAD,124
EUR,EUR,978

expose the data as a new odata service srv/crosstenant-model.cds

using mt.tacoshop.crosstenant as ct from '../db/crosstenant-model';

service CrosstenantService @(path : '/Crosstenant/Catalog') {
    entity Currencys as projection on ct.Currency;
}

add a custom handler to the new service srv/crosstenant-model.jsNotice, we’ll always reference the public schema for our common / shared entitys.

const cds = require('@sap/cds');

console.log('before-exports');
module.exports = cds.service.impl(function () {

    this.before('READ', '*', (data) => {
        data.req.user.schema = 'public';
    });

    this.before('CREATE',  '*', (data) => {
        data.req.user.schema = 'public';
    });
   
    this.before('DELETE', '*', (data) => {
        data.req.user.schema = 'public';
    });

    this.before('UPDATE', '*', (data) => {
        data.req.user.schema = 'public';
    });
});

Update our srv/tenant-model.cds to expose the crosstenant entity as read-only

using mt.tacoshop as mt from '../db/tenant-model';
using mt.tacoshop.crosstenant from '../db/crosstenant-model';
service CatalogService @(path : '/Tenant/Catalog') {
    entity Tacos as projection on mt.Taco;

    @readonly
    entity Currencys              as projection on crosstenant.Currency;
}

What we’re saying here is that we’d like all users to be able to read our crosstenant data but not neccesarily edit it. Let’s add a route to our app/xs-app.json and require Administrator access to CrossTenant catalog. Remember we’re exposing this data as read-only to all users but don’t want Joe-Shmo from tacos-and-tequilla to be able to modify data that’s shared accross all tenants.

{
  "welcomeFile": "index.html",
  "authenticationMethod": "route",
  "logout": {
    "logoutEndpoint": "/logout",
    "logoutPage": "/logout.html"
  },
  "routes": [
    {
      "source": "^/srv/(.*)$",
      "authenticationType": "xsuaa",
      "scope": "$XSAPPNAME.User",
      "destination": "srv"
    },
    {
      "source": "^/Crosstenant/(.*)$",
      "authenticationType": "xsuaa",
      "scope": "$XSAPPNAME.Administrator",
      "destination": "srv"
    },
    {
      "source": "^/Tenant/(.*)$",
      "authenticationType": "xsuaa",
      "scope": "$XSAPPNAME.User",
      "destination": "srv"
    },
    {
      "source": "^/(.*)",
      "authenticationType": "none",
      "localDir": "resources"
    }
  ]
}

This would be another great time to commit, re-deploy and confirm the application is working as expected. There’s a lot of exciting ways to continue to build upon this SaaS + CAP framework.

Using either subscription tenant, you should now be able to see both cross-tenant and tenant data.

Drop a comment or any questions below if you found the blog helpful or want to see new features. Currently, there’s a small community using cds with postgres but our hope that community grows as more of the SAP and open-sourced community see that this framework can really offer an efficient way to build enterprise level and scalable solutions.