Most application development frameworks make use of the underlying database as a means of storing data only, while the logic is safely stored away in the application layer. Calculating stock levels, performing checks, managing user access permissions – everything is usually done in the application. Sclable has a different approach.

Their use of the Postgres database to its full potential allows to keep the application layer clean – complex code logic can be kept in the database, where it belongs, while the application takes care of moving the data between the views and the data models.

Let’s illustrate this with some examples.

user permissions

In the usual setup, your application will contain a lot of complex logic of “who can do what” in your application. Part of this logic is stored in the database, maintaining user accounts, groups, and roles, but a lot of this logic will happen in the application itself.

Want to view a list of records? Better check that the user is authorized! Want to edit a record – check the permissions first! This burdens the application with a lot of tedious checks that have to be performed before the actual interaction with the database can happen.

Sclable’s approach is to leave the user management in the database.

User accounts, roles (called agendas) and the actions they can perform are all stored along with the data. Since every CRUD action runs through Sclable’s stored procedures in the database, the user cannot perform any action he or she is not authorized to do without the database throwing an exception (which Sclable returns as a restriction with a fairly descriptive error message that can be caught and treated separately from other, potentially severe, database exceptions).

This means that as the developer I can focus on the logic that needs to happen in the application layer without constantly having to perform these checks – all I need to do is be prepared to catch database restrictions in case there is a permission problem.

As for reading data, Sclable also has this covered: read queries on entities that the performing user is not authorized to view return empty record sets – so there is no reading data you’re not allowed to!

Selections

When requiring access to complex datasets that span multiple tables in the database, there are usually a few options: create a complex join statement in the application (which makes for messy code), load in lists of data from the tables separately and join them in the application (which makes for even messier code), or create a view in the database (and hope that it will be available when your code runs).

Sclable has a very nice solution to this problem that stems from the tight integration with the database: Selections.

Selections are a key concept of the Sclable development platform: these custom-tailored datasets represent complex joins over various tables, that are stored as views in the database and accessed in the application as Selection Objects.

Since the concept of selections is so tightly integrated into the development platform, selections can also be used as the basis for displaying data in tables in the application, with barely any coding required: just create the selection, add adjustments (which columns to show, what to sort the table by), and you get a fully searchable and sortable table to display the results in the application.

Calculated and persistent attributes

Every slightly more complex application gets to a point where an entity (be it for example a product, a sales order, or an invoice) has certain information attached to it that needs to change based on the properties of the entity itself, or – in more complex settings – on other entities in the application.

For example, a product’s stock in a warehouse location is calculated based on the stock movements of the product in the location, and needs to update whenever a new movement is created, or an existing movement edited or deleted. Maintaining this calculation logic in the application can be quite a challenge and, if not managed carefully, can be a source of bugs (and stock differences, in the case of a product’s stock).

Sclable includes the concept of a calculated attribute in its database core logic.

This is simply a way of storing and referencing a stored procedure attached to an entity in the database. By moving the stock calculation to a stored procedure, I’m placing this piece of code logic where it belongs: in the database itself, rather than the application. By attaching this procedure to the entity, I have an “attribute” in the entity that always contains the correctly calculated stock for the product.

This obviously has performance implications: listing thousands of products, where each product in the list invokes a stored procedure will slow down your application remarkably. This is why we usually introduce a “real” attribute to the entity that acts as a persistent store for the calculation procedure. By creating a stored procedure to update the persistent store on demand, and invoking this procedure on every change to the entity, we have a reliable and fast method of moving complex calculations out of the application and into the database.

The Sclable team is already working on an even more convenient way to cache these calculated attributes without the need to manually create and update persistent attributes. This will make this particular aspect of the development platform even more useful and easier to use in the future.

These were just a few examples of how the tight integration of Sclable’s development platform and the database core opens up a world of possibilities for creating a lean application layer on top of a powerful, fast and reliable Postgres database core. As a developer who has seen the benefits of working with the current version of Sclable, I am looking forward to what the Sclable team will come up with in the future to make the database core even more powerful and convenient to use.