orb » data modelling

The orb code generator manages three parts of your code-base: the database itself, in generating the SQL schema; the data layer, which interfaces with the database and maps between data and object representations (ORM); and the presentation layer, which formats objects into output media.

In normal usage, the data model is developed alongside your application's business logic, which applies your business rules to and from the data. This business logic usually covers data-business, such as generating reports, and presentation-business, which formats the reports.

In orb, the programming languages used are TypeScript and SQL. The data layer is generated using the Node.js framework, while the presentation-layer code is intended for running on a browser with access to the DOM. All of these components may be generated separately for use in disparate applications.

The code output for an orb application is built to work within Node.js conventions. This document lays out a simple example touching upon all parts of such an application.

To first use an orb Node.js application, the application environment must be prepared. This assumes the current directory is the root directory of the application.

$ npm init -y
$ npm install express @types/express
$ npx tsc --init

This will create the package.json, package-lock.json, and tsconfig.json files. The first defines some properties of your application, the second ties your project to certain versions of its dependencies, and the last sets out compilation properties.

In this example, however, we change the target property in tsconfig.json to be es2015. This means that JavaScript code generated by the TypeScript compiler will use newer JavaScript conventions. In package.json, we set the main application file to be index.ts instead of index.js. We also add a scripts object containing "tsc": "tsc". This lets us run npm run tsc to generate code.

For clarity, our example will have (or feature) only one structure, a user, consisting of a name string field, with the name being non-empty, and an id row identifier field. It has an insert operation and a single query, id, which searches on the identifier.

The node package manager (NPM) is a popular tool for managing source code, assets, and dependencies. orb exports an NPM registry, allowing developers to keep their sources fresh as the data model evolves.

To track sources with NPM, first create an authentication token in the user homepage, which gives instructions on how to add orb as a registry and bind it to the orb@ scope. Following that, data models may be tracked by branch.

The code for individual branches is given in the code panel of a data model, and may be used to track branches or connect to a specific version. In the following example, 1234-5678 is the branch identifier, and this assumes that the scope has been registered.

$ npm install @orb/1234-5678

This will install the branch sources along with all dependencies. The sources includes the SQL schema, back-end server, and front-end DOM formatting.

Some operating systems may require additional flags for this to work. On OpenBSD, for instance, this invocation may require installing a python2 interpreter from the ports tree for the binary dependencies to be compiled:

$ PYTHON=/usr/local/bin/python2.7 \
> npm install @orb/1234-5678

Older versions of OpenBSD require overriding the C/C++ compiler paths (CC, CXX) to newer versions of clang installed from the ports tree.

The SQL schema code generated by orb creates a database with the tables, columns, and foreign key relationships dictated by your configuration.

The SQL output is currently in the SQLite flavour consisting of a series of CREATE TABLE statements. The SQL schema uses the structure and field components of a configuration to generate both the tables, their columns, and the relationships between columns (foreign keys).

Each field in the configuration is mapped to an SQL type save struct, which is used when querying databases, but not when creating them. Only the SQL basic types are used: BLOB, INTEGER, REAL, and TEXT. The map between orb types and SQL types is the natural one (text into TEXT, etc.) with the possible exception of dates and date-times, which are all stored as INTEGER for the UNIX epoch.

Beyond field types, action statements are also relevant for references. These dictate the behaviour of the referencing row when the referent is updated or deleted. Field flags (if a row identifier, possibly null, or unique) are relevant as well, as are the default values.

In our example, we put the database alongside the Node.js script root in the build directory of our system.

$ mkdir build
$ sqlite3 build/ort.db < node_modules/@orb/1234-5678/sql/ort.sql

This will generate a new database for use by our application. It, and the directory where it resides, must be readable and writable by the user running the Node.js application.

The data layer produced by orb acts as an ORM and provides input validation, transport-layer export, and role management.

It is output as TypeScript using the Node.js framework. Its requirements are better-sqlite3 for databases operations, bcrypt for password hashing, and validator for input validation. These are all managed by the NPM distribution.

To generate the data layer, orb uses all parts of the data model: the operations, for which methods to output; structures and fields, for the data; and user-defined types for validation. All of these are generated in a single Node.js file that may be imported into a Node.js application with an import call.

The following example uses the express.js web application framework to trivially manipulate a database.

In the simple configuration listed in Basics, we stipulated an index.ts file, which we fill with the following TypeScript code.

import express from 'express';
import { ort, ortns, ortctx, ortdb } from '@orb/1234-5678/ort';

const app: express.Application = express();
const db: ortdb = ort('ort.db');

app.get("/put", 
  function(req: express.Request, res: express.Response) {
    const ctx: ortctx = db.connect();
    const id: bigint = ctx.db_user_insert('new user');
    return res.send(id.toString());
  }
);

app.get("/get",
  function(req: express.Request, res: express.Response) {
    const ctx: ortctx = db.connect();
    const user: ortns.user|null = ctx.db_user_get_id(BigInt(1));
    if (user === null)
      return res.status(404).send('not found');
    return res.send(user.obj.name);
  }
);

app.listen(3000, function() {
  console.log('Server is running.');
});

This responds to HTTP connections to port 3000, specifically the /put and /get paths. For the former, a new user is created with the name new user and its identifier output to the browser. For the latter, it outputs the first user's name; or if invoked before /put, outputs an error message.

The database interface is defined in the modules/ort.ts file, which is the data layer. The relevant parts are ort('ort.db'), which opens the database ort.db; db.connect(), which creates a per-request connection to the database; and operations on the context to manipulate the database itself.

The new index.ts file is compiled and run as follows:

$ npm run tsc
$ node build/index.js

On success, this will print Server is running to the console and may be accessed by navigating to port 3000 on the local host.

This example builds on the simple application by allowing form input and emitting formatted JSON objects as output.

The only new requirement to the code is the ortvalid namespace from the data layer. This namespace is required for validating untrusted HTTP form input. In a real application, this will likely be transparently used by an underlying validation layer.

import express from 'express';
import { ort, ortns, ortctx, ortdb, ortvalid } from '@orb/1234-5678/ort';

const app: express.Application = express();
const db: ortdb = ort('ort.db');

app.get("/put", 
  function(req: express.Request, res: express.Response) {
    const ctx: ortctx = db.connect();
    const name: string|null = ortvalid.ortValids
      ['user-name'](req.query['user-name']);
    if (name === null)
      return res.status(400).send('bad');
    const id: bigint = ctx.db_user_insert(name);
    return res.send(id.toString());
  }
);

app.get("/get",
  function(req: express.Request, res: express.Response) {
    const ctx: ortctx = db.connect();
    const id: bigint|null = ortvalid.ortValids
      ['user-id'](req.query['user-id']);
    if (id === null)
      return res.status(400).send('bad');
    const obj: ortns.user|null = ctx.db_user_get_id(id);
    if (obj === null)
      return res.status(404).send('not found');
    return res.send(JSON.stringify(obj.export()));
  }
);

app.listen(3000, function() {
  console.log('Server is running.');
});

Like in the simple example, there are two paths, /put and /get. And similarly, the need to acquire a database connection prior to any database operations. The difference is that both accept required parameters on the query string. The /put accepts a non-empty user-name, which will be used in the insertion when creating the user entry. The /get accepts a user identifier, such as the one output to the browser when the insertion occurs. This is used to look up the user.

The /get returns a JSON object of the underlying user, if found.