Devii is an application API server, designed to connect to an arbitrary number of SQL database backends and automatically reflect their schemas into GraphQL schemas, serving the GraphQL schemas as web service APIs. The GraphQL schemas include a regular, standardized set of parameters which offer robust and complex filtering, ordering, and paging of queries, as well as automatically generated mutations to create, update, and delete records. Devii is designed to ease the development of networked applications by automating API generation, providing built-in user management, enforcing security policies, offering programmable server-side extensibility, and for web applications, automatically generating client-side data models.

Queries, in particular, are filtered using an expression language, based on (but not entirely like) SQL “WHERE clause” syntax. This syntax allows complex queries based on more than just equality comparisons; various comparison and mathematical operators, list membership lookups, and even (limited, whitelisted) database functions may be used. In some cases, the syntax has been simplified from SQL standard, so that, e.g., “createdate = max(createdate)” is a valid expression for getting the most recently created record, whereas it would not be valid SQL in a WHERE clause without a subselect. The expressions are not passed directly to a SQL backend, but are parsed using a LALR(1)¹ parser, and the results applied programmatically to create a valid, and safe, SQL query. The risk of SQL injection attacks is thus eliminated.

Devii offers a built-in user and group (“role”) management system, independent of the database schemas themselves. Roles can be created in hierarchies, or can be grouped together into non-hierarchical “role classes”, or both, as administrators see fit. Roles and role classes are key to organizing users, and also to granting access, using Devii’s security system.

This security system is based on a relatively new theoretical foundation, known as Attribute-Based Access Control, or (as we prefer to call it) Policy-Based Access Control.² PBAC, as we term it, defines access based on a set of rules which define how various roles and/or role classes can access tables, views, and other exported objects in a given database. These rules are effectively logical expressions based on attributes of the role, role class, operation, and target table or records within the table; when evaluated, if the rule returns “true”, access is granted. Policy rules implement “filters” based on the same expression language used for query filtering.

PBAC provides a powerful and flexible means of defining security policies, without the combinatorial explosion of entries inherent in the classic access control list, or the similar issue of role proliferation in standard role-based access control (RBAC). PBAC policies can implement both mandatory and discretionary access controls, as well as RBAC, using policy rules.

Devii is multi-tenant by design; each database is considered a separate “tenant” for organizational purposes. Each tenant has its own schema, roles, role classes, and policy rules. Furthermore, as long as the database systems used are compatible with Devii’s backend, there is no restriction on which Relational Database Management System (RDBMS) a tenant lives on; Devii can, and has, connected to multiple RDBMS instances at once to serve up APIs. Tenancies can live on Oracle DBMS, Microsoft SQL Server (and Azure SQL), MySQL and MariaDB, PostgreSQL, or even SQLite.³

Devii will support inter-tenant queries, using our “Advanced Connectivity” package. This will provide a means for roles in one tenant to query objects in another tenant; the “host” tenant will determine the policy for the “guest” access, and the “guest” roles, in turn, will be limited by their own policy in which parts of the “foreign” data they can access. This feature is currently in the design phase.

In addition to its support for the standard Create, Read, Update, and Delete operations needed in a web API, user management, and security system, Devii also comes with extensions known as packages. Functionality is grouped into packages in order to allow enabling that functionality for various tenants, and for billing purposes, if desired.

Packages enhance Devii’s capabilities in a number of ways, such as:

• Associating table records with files stored in a file storage backend, such as a cloud storage provider
• Geospatial capabilities, including storing and querying geospatial data and rendering it via standard OpenGeospatial Web Services (OWS)
• Image and multimedia processing
• Cross-database querying and access mediated via policies
• Analytical functions and queries
• Financial transaction handling via the BNG payments API
• Sending of email and SMS notifications
• PDF report generation based on templates

The system can be further extended using the built-in “process rule” engine. This engine allows adminstrators to create rules that run a Python program when mutations occur on a target table; conceptually, this is like a database trigger, but is far more capable, as the Python program can perform any operation desired. Process rule programs are designed to be generic, with much of their behavior determined by parameters stored in the database; the same program can thus be run by several different rules and produce very different results. Because process rule programs would be a major security risk if they could be configured by external users, Devii only allows process rules to be created by installation administrators on behalf of tenants.

To ease development of web or Node.js applications, Devii also comes with an associated Javascript library, devii.js. This library uses GraphQL schema introspect and Javascript metaclass code generation to create Backbone model and collection classes for GraphQL schema objects, with models corresponding to individual rows, and collections corresponding to tables or views. These automatically generated classes come with built-in methods for query filtering, ordering, and paging, as well as validations for mutations. In the future, a newer version of devii.js will provide automatic generation of Redux sagas, for use with React components.

With this client library, a Javascript developer effectively has the ability to code to the database schema; from the database to the web application, Devii server and client software provides end-to-end reflection and automatic code generation to handle data transport.

The end result is that, with Devii, a set of databases can be turned into web-ready API endpoints in a matter of hours: a few minutes to introspect the databases, and then the time needed to design and implement the security policy and roles for each tenant. Most of this work is business-logic decisions, not coding time; developers can thus spend their time writing the application.

_{1. LALR(1) parsers are described in further detail at Wikipedia; see the article’s references list for further information.}

_{2. For further reading on the theoretical basis of ABAC/PBAC, this paper by Xin Jin, Ram Krishnan, and Ravi Sandhu is a good starting point. That said, our implementation is not precisely like the one described in their paper: they discuss using an XML-based syntax called XACML to implement ABAC, which we regarded as both overcomplicated and painfully verbose. Also, XACML allows defining rules which can deny access; we prefer a “default deny” approach, where the absence of rules is what denies access, as this makes rule evaluation much simpler.}

_{3. SQLite is not recommended for production, unless the tenant’s data is read-only. Multiple attempts to write to the schema would not perform well, as SQLite locks the entire database file for writing.}