Building the Schema¶

After creating your JohoDB object, but before running db.init() you need to add your schema. A basic schema looks like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

var db = new JohoDB('YOUR_DATABASE_NAME');

db.addSchema('Person', {
    'id': {type: "string", primaryKey: true},
    'name': {type: "string", required: true},
    'age': {type: "int"}
});

db.addSchema('Message', {
    'id': {type: "string", primaryKey: true},
    'text': {type: "string", required: true},
    'author': {type: "fk", required: true,
               relation: "Person", relatedName: "messages"}
});

As you can see, we’re using the addSchema method to build up our schema.

db.addSchema(tableName, tableSchema)¶

Arguments:	tableName (string) – The name of the table this schema represents. tableSchema (object) – The schema of the table, including all fields, their names and properties.

Migrations¶

Once you’ve set up your schema, you might be tempted to run db.init(), however you’ll run into a problem. You’ll see this warning in your console:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

UPDATED MIGRATION REQUIRED:

db.addMigration({ actions: [
    new JohoDB.MigrationAction("add_table", {"tableName":"Person"}),
    new JohoDB.MigrationAction("add_column", {"tableName":"Person","columnName":"id","columnTraits":{"type":"string","primaryKey":true}}),
    new JohoDB.MigrationAction("add_column", {"tableName":"Person","columnName":"name","columnTraits":{"type":"string","required":true}})
    new JohoDB.MigrationAction("add_column", {"tableName":"Person","columnName":"age","columnTraits":{"type":"int"}}),
    new JohoDB.MigrationAction("add_table", {"tableName":"Message"}),
    new JohoDB.MigrationAction("add_column", {"tableName":"Message","columnName":"id","columnTraits":{"type":"string","primaryKey":true}}),
    new JohoDB.MigrationAction("add_column", {"tableName":"Message","columnName":"text","columnTraits":{"type":"string","required":true}}),
    new JohoDB.MigrationAction("add_column", {"tableName":"Message","columnName":"author","columnTraits":{"type":"pk","required":true,"relation":"Person","relatedName":"messages"}})
]});

So what’s happening here? JohoDB always checks on run time if the schema matches the migrations it has. It needs to know how to build the Schema it has been provided. This doesn’t make much sense now, but is critical if you need to ship updates to the Schema down the track.

To make it easier on developers, if the migrations path provided (or lack of migrations in this case) does not arrive at a perfect match with the schema, it will provide you with a snippet that you can copy/paste into your code.

db.addMigration(migrationObj)¶

Arguments:	migrationObj (object) – An object with an attribute ‘actions’ which represents all the actions that must occurs within this migration.

Most of the time, you’ll just be able to copy paste the code snippet in to some point before db.init(), but there’s one thing to take care with: migration action order does matter in some key ways. Whenever you create a foreign key field, in WebSQL indexes will be created to the other table, so you need to ensure foreign key fields come after their related table is created.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

db.addMigration({ actions: [
    new JohoDB.MigrationAction("add_table", {"tableName":"Person"}),
    new JohoDB.MigrationAction("add_column", {"tableName":"Person","columnName":"id","columnTraits":{"type":"string","primaryKey":true}}),
    new JohoDB.MigrationAction("add_column", {"tableName":"Person","columnName":"name","columnTraits":{"type":"string","required":true}})
    new JohoDB.MigrationAction("add_column", {"tableName":"Person","columnName":"age","columnTraits":{"type":"int"}})
});

db.addMigration({ actions: [
    new JohoDB.MigrationAction("add_table", {"tableName":"Message"}),
    new JohoDB.MigrationAction("add_column", {"tableName":"Message","columnName":"id","columnTraits":{"type":"string","primaryKey":true}}),
    new JohoDB.MigrationAction("add_column", {"tableName":"Message","columnName":"text","columnTraits":{"type":"string","required":true}}),
    new JohoDB.MigrationAction("add_column", {"tableName":"Message","columnName":"author","columnTraits":{"type":"pk","required":true,"relation":"Person","relatedName":"messages"}})
]});

Saving Data¶

If we were using the schema from before, with a Person and a Message table, a save could look like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

db.models.Person.save({
    'id': 'a-unique-id-of-some-sort',
    'name': 'Joe Bloggs',
    'age': 25
}).then(function(savedRecord) {
    // do something once the record is saved
}).catch(function(err) {
    // if you wish to catch any errors, throw catch on the end of
    // your promise chain
});

Model.save(saveRecord[, options])¶

Arguments:

saveRecord (object) – The object to be saved into the database. This can be any object, the only thing that matters is it has attributes matching the fields on the schema. options (object) – Options for the save operation, all are optional. updateRelations: Will update the keys for any relationship fields as required. This will ONLY update the keys, and not save the rest of the data in related records. updateRelatedRecords: Will update any related records for relationship fields if they are provided as objects or arrays.

When you use save whatever you pass to it will be validated against the schema and then saved in the data store. You can also just do a validation if you want by using validate instead in the same way.

Model.validate(saveRecord[, options])¶

Arguments:	saveRecord (object) – The object to be validated. options (object) – Options for the validation operation.
Returns Promise<validationResult>:
	Promise resolves on end of validation. The Validation Result returns the following information: isValid: is true if the record passed validation errorCount: number of invalid fields, if any validating: a reference to the validated record errors: an object with any field errors attached

Querying Data¶

Querying the data is done by building a query from chained methods and then evaluating that query. You can start a query by calling .query() from a model. An example where we get all of the available records:

db.models.Person.query().all().evaluate().then(function(records) {
    // do something with the records
});

If you want to get a single record you can use a get method in the query:

1
2
3
4
5

db.models.Person.query().get({
    'name': 'Joe Bloggs'
}).evaluate().then(function(record) {
    // do something with the record, if not found record will be null
});

If you want to get multiple records based on a query, you can use filter or exclude:

1
2
3
4
5
6
7
8

// get all persons with an age of 25
db.models.Person.query().filter({'age': 25}).evaluate();

// get all persons above the age of 25
db.models.Person.query().filter({'age__gt': 25}).evaluate();

// exclude all persons below of equal to the age of 25
db.models.Person.query().exclude({'age__lte': 25}).evaluate();

The object you pass to a get, filter, or exclude function contains the fields you want to query by. Providing the field name alone means you wish for an exact match to the value, otherwise you can provide a modifier after two underscores to do a different type of match.

Field Options¶

The following options are available to all field types and are optional.

Field Types¶

Fields are specified by type in the schema. The type is case insensitive, and in some cases allow for abbreviated versions of the field’s formal name.

Relation Options¶

The following options are available to all relation types.

Relation Types¶

Relations are specified by type in the schema. The type is case insensitive, and in some cases allow for abbreviated versions of the relations’s formal name.

Saving Relations¶

When using the save method, you must set an option making it clear you wish to update the relationships of this record. An example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

db.models.Household.save({
    id: "thisHouseID",
    address: "52 Goldilock Road",
    suburb: "Manjimup",
    postCode: "8732",
    occupants: [
      {id: "jrakich", firstName: "James", lastName: "Rakich", dateOfBirth: new Date(1952, 10, 12)},
      {id: "joebloggs", firstName: "Joe", lastName: "Bloggs", dateOfBirth: new Date(1982, 9, 2)}
    ]
}, {updateRelatedRecords: true});

In this case the Household is the subject of a foreign key relationship from the Person table, with occupants as the relatedName. Because we’ve set updateRelatedRecords to true, this save will also save/update the occupants and set their foreign key to the correct id (thisHouseID in this case).

If you want to only update the foreign keys, without affecting the rest of the relation’s data, set the option to updateRelations instead, and it won’t touch the rest of the data, just the foreign key.

The inverse of this, with a Person, could look like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

db.models.Person.save({
    id: "jrakich",
    firstName: "James",
    lastName: "Rakich",
    dateOfBirth: new Date(1952, 10, 12)},
    home: {
        id: "thisHouseID",
        address: "52 Goldilock Road",
        suburb: "Manjimup",
        postCode: "8732"
    }
}, {updateRelatedRecords: true});

Here the person would be saved, and so would their household, with the appropriate foreign key in it’s place. If the household already existed this would be equivalent:

1
2
3
4
5
6
7

db.models.Person.save({
    id: "jrakich",
    firstName: "James",
    lastName: "Rakich",
    dateOfBirth: new Date(1952, 10, 12)},
    home: "thisHouseID"
});

As you can see in this case we can directly set the foreign key if we wish.

Lookup Arguments¶

Most query steps will require lookup arguments to be used. The lookup arguments are an object literal with keys that define which field to target, and values which define what to match against. By default lookup arguments query for an exact match, but you can also provide modifiers as a postfix to the fields, for example age__gt for the everything greater than on the field age.

// filter all people who live in a home in the suburb 'Moonah'
db.models.Person.query().filter({'home__suburb': 'Moonah'}).evaluate();