Database interface

A JavaScript object implementing the Database interface provides access to a relational style database.

See Database for documentation on how to use the relational database.

Construction

Database objects cannot be directly created. See the database overview for how to use databases.

Use the db property of your Plugin.

Naming restrictions

Database table and field names must match this regular expression: \A[a-z][a-zA-Z0-9]*\z

Names must start with a lowercase letter, and be formed of letters and numbers only. Underscores are not allowed.

Interface

function table(name, fields, initialiserOrMethods)

Declares a table with the given name and the defined fields. See below for field definitions. There is an implicit numeric id field in every table which is used as the primary key.

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 property is added to this object to access the new DatabaseTable object.

The optional initialiserOrMethods argument allows you to extend the prototype of the row object to add additional methods and getters and setters. If it’s a function, it’ll be called with the prototype as a single argument. If it’s a JavaScript Object, those properties will be added to the prototype.

This function can only be called when the plugin is loaded. See the database overview for details and an example.

property T [access to tables]

A call of the table() function to create a table with name T, creates a property of this object with the name T. The property exposes a DatabaseTable object.

Field definitions

The fields argument to table() is a JavaScript Object used as a dictionary of field name to field definition, another JavaScript Object. The field definition object has the following keys:

key type

Name	Data type	JavaScript type
`text`	Unicode text ¹	String
`datetime`	Date and time, UTC timezone	Date
`date`	Date only	Date
`time`	Time only	`DBTime`
`boolean`	Boolean	`true` or `false`
`smallint`	16-bit signed integer	Number
`int`	32-bit signed integer	Number
`bigint`	64-bit signed integer ²	Number
`float`	double precision floating point number	Number
`ref`	Reference to store object	`Ref`
`link`	Reference to row in other table	Database row object ³
`user`	Reference to user	`SecurityPrincipal` ³
`file`	File stored in the file store	`File`
`json`	JSON compatible data structure with automatic serialisation.	JavaScript `Object` see notes on json field limitations
`labelList`	Labels	`LabelList`

key linkedTable

For link types only. A string specifying the name of the table this refers to, if the name of the table does not match exactly the name of the field.

key nullable

If true, null values will be allowed for this field. Otherwise it’s an error to attempt to set the property to null or save a new row object without specifying a value for this field.

key caseInsensitive

If true for a text field, any comparisons in queries will be case insensitive. The case of the string will be preserved in the database.

If an index is specified on a case insensitive field, the index will be case insensitive too, providing efficient indexed queries on the the field.

key indexed

If true, an index will be created for this field.

key indexedWith

If set to an array of names of other fields, a multi-field index will be created with this field as the first field, then the others in order. Useful for speeding up queries on multiple fields, but use with caution.

key uniqueIndex

If true and indexed is set to true or indexedWith is specified, the resulting index will be a unique index. Attempting to store a row with duplicate value(s) will throw an exception.

Example table definition

P.db.table("employee", {
    firstName: { type:"text" },
    lastName: { type:"text" },
    user: { type:"user", nullable:true },
    startDate: { type:"date" },
    salary: { type:"int", nullable:true, indexed:true },
    primaryExpertise: { type:"ref", indexed:true, uniqueIndex:true },
    department: { type:"link", nullable:true, indexedWith:["startDate","salary"] }
}, {
    name: function() {
        return this.firstName()+" "+this.lastName();
    }
});

JSON field type

json fields automatically JSON serialise and deserialise values for convenience, but do come with limitations:

Cannot be indexed
Cannot be used in where() clauses, except when comparing to null
The deserialised value is mutable, so you have to think carefully about how you use the deserialised value
So that errors in usage are easier to spot, you need to explicitly assign to the property to make a change which will be committed to the database when save() is called on the row.

// Where data is a json column...
var row = P.db.table.create({data:{a:1,b:2}});
row.save();
var reloaded = P.db.table.load(row.id);
reloaded.data.b = 3; // INCORRECT USAGE
reloaded.save();
// Because line 5 mutated a cached value, it's not saved
var reloaded2 = P.db.table.load(row.id);
// reloaded2.data.b === 2
// To update the value, assign to the property
reloaded2.data = {a:1,b:3}; // CORRECT
reloaded2.save();
var reloaded3 = P.db.table.load(row.id);
// reloaded3.data.b === 3

Notes

¹ No maximum size is specified for text fields, because the underlying implementation is just as efficient for unrestricted text fields as it is for size limited text fields.

² When using bigint values, remember that JavaScript numbers can only represent 53-bit integers with full precision because it represents all numbers as a double precision floats.

³ link and user values can be set with numeric IDs, but properties in row objects always return the specified object.