Plugin API » Database

Relational database

As well as the object store, the Haplo Platform provides a relational style database. Each plugin has its own table namespace, providing isolation and allowing multiple plugins to have a table with the same name.

Declaring tables

Make sure "pDatabase" is included in the "privilegesRequired" array in plugin.json.

Then, in one of your JavaScript files, define the tables using the plugin’s Database object accessed as the db property. Database tables can only be defined when the plugin is loaded, so you must place the database declarations outside any other code.

For example:

P.db.table("department", {
    name: { type:"text" }
});

P.db.table("employee", {
    firstName: { type:"text" },
    lastName: { type:"text" },
    startDate: { type:"date" },
    salary: { type:"int", nullable:true, indexed:true },
    department: { type:"link", nullable:true }
});

The first example definition is a very simple table with a single name column, and an implicit id field. The second is more interesting, with a department field which refers to rows in the department table.

Note that by default, fields do not allow null values, and you must specify nullable:true to allow them. This is the other way round to SQL, where you need to specify NOT NULL to disallow NULL values.

A DatabaseTable object for each table will be added to the db object as a property named after the table.

Because each plugin’s database is isolated by the platform, you do not have to worry about table name collisions. Just use short and descriptive names which make sense in the context of your code.

Adding rows

Functions in the plugin access the plugin’s Database object as either P.db, following conventions, or if they are methods called on the plugin object, this.db. To create objects, call create() on the table object.

var exampleCreation = function() {
    // Create some rows in the department table
    var sales = P.db.department.create();
    sales.name = "Sales";
    sales.save();
    var engineering = P.db.department.create({name:"Engineering"}).save();
    // Create a row in the employee table
    var john = P.db.employee.create({
        firstName: "John",
        lastName: "Smith",
        startDate: new Date(2010, 09, 01),
        salary: 30000,
        department: engineering
    }).save();
};

This creates two departments and a employee.

Retrieving rows

Given an ID of a row, it can be retrieved with the load() function.

// Load a single row from the employee table.
var john = P.db.employee.load(1 /* row id property */);
// Retrieve the department object from the employee object
var engineering = employee.department;

Line 2 loads a single row by the ID of that row. Line 4 implicitly loads a row from the department table by accessing the property.

Querying

Use the select() function on the table object to create a DatabaseQuery object, set the criteria for the search, then use it as a JavaScript Array to access the results. Alternatively, use the each() function to iterate over each row.

There is no explicit ‘execute query’ function.

// Query for employees who started after 1 Jan 2010
var employees = P.db.employee.select().where("startDate", ">=", new Date(2010, 00, 01));
// Use the results
employees.each(function(employee, index) {
    // Do something with the employee row
});

Each member of the ‘Array’ is a DatabaseRow object.

See DatabaseQuery for all the selection, sorting and performance options.

Updating

Call save() on a DatabaseRow after updating the field properties to update a single database row.

If you are updating multiple rows at once, it will be more efficient to use update() on a DatabaseQuery.

When not to use the database

If you want to store a small amount of infrequently changing data, consider using your plugin’s DataStore, accessed through the data property.

Relational databases are very good at storing ‘data’. You should only use the database where it makes sense. For ‘information’, especially when it integrates well into the rest of the user’s information, you should probably be using the object store as it’s the core strength of the platform.

Implementing work flow style processes to guide users through actions should be done with the platform’s WorkFlow support.