Schema and Migrations

JohoDB is a library that creates a relational database structure, and as such you must create a Schema. For those looking for a NoSQL approach to data there are other libraries out there, but you won’t be able to support WebSQL with them. Anyway, lets get to the Schema.

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:

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.

The Table Schema

The table schema is an object literal, with keys representing the names of the fields. The values are the attributes of that field, here are the basics you’ll need:

type:The type of value the field represents. Basics include string, int, and fk for a relation. primaryKey:Whether or not the field is the primary key for this table. Be aware that JohoDB does not create keys [1]. required:The field is required to save a record. Primary keys are required by default. relation:For fields that are relations, the name of the table it is a relation too. relatedName:For fields that are relations, this is the name the other table should refer to it’s related records by.

See the remaining attributes and field types available at: Field Reference

Notes for building a Schema

• You must define the entire schema before running db.init().
• You can define the schema in any order. JohoDB will not attempt to validate the schema until you run db.init().

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:

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.

Migrations and Versioning

Each migration represents a version of your database. When the database is connected with IndexedDB or WebSQL, the number of migrations is used to check whether the client is in or out of date. That way you can automatically upgrade their database, applying only the migrations that are required.

In order to do this versioning, you simply call db.addMigration multiple times with new actions each time. In the above example we could make the tables in two separate migrations like so:

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"}})
]});

Now, this would only matter if you shipped out a version of your app with only the Person table, and down the track decided to add the Message table. That said, hopefully it illustrates how migrations are intended to work.

Anyway, onward! Time to hook this up to a real database at Initialising the Database.

[1]	JohoDB does not create primary keys for you automatically, the reason being it is an asynchronous library whether you use WebSQL or IndexedDB. As such it is better you take responsibility for key generation so you’re not running into race conditions, whether it’s a pseudo-UUID or some other system.