PersistenceJS: Updating an existing database using migrate()

Question

I'm currently trying to make changes to an existing DB using the migrations plugin for PersistenceJS. I can add/edit/delete items in the DB just fine — but…

• How to add a column to an existing(!) table?

• How to change the type of an existing(!) column, e.g. from 'text' to 'integer'?

• These changes should retain currently existing data.

Sadly, the documentation is a little scarce, maybe you could help?

Here's the current, working setup:

persistence.store.websql.config(persistence, 'tododatabase', 'todos are fun', 5*1024*1024);

var Todo = persistence.define('Todo', {
    task: 'TEXT',
    priority: 'INT',
    done: 'BOOL'
});

persistence.schemaSync();

function addTodo( item ){
    var todo = new Todo();
    todo.task = item.task;
    todo.priority = item.priority;
    todo.done = item.done;

    persistence.add(todo);
    persistence.flush();
};

function deleteTodo( item, callback ){
    // item.id was created automatically by calling "new Todo()"
    Todo.all().filter('id','=', item.id ).destroyAll( function(){
        persistence.flush( callback );
    });
};

The migration code that kinda works:

persistence.defineMigration(1, {
    up: function() {
        this.createTable('Todo', function(t){
            t.text('task');
            t.integer('priority');
            t.boolean('done');
        });
    },
    down: function() {
        this.dropTable('Todo');
    }
});

persistence.defineMigration(2, {
    up: function() {
        this.addColumn('Todo', 'due', 'DATE');
    },
    down: function() {
        this.removeColumn('Todo', 'due');
    }
});


function migrate( callback ){
    console.log('migrating...');
    persistence.migrations.init( function(){
        console.log('migration init');
        // this should migrate up to the latest version, in our case: 2
        persistence.migrate( function(){
            console.log('migration complete!');
        } );
    });
}

Results…

1. calling migrate() will only log up to "migration init", the complete handler is never called, the "due" column is not created
2. not calling schemaSync() before calling migrate() as Zef Hemel himself proposed in this post yields the same result as 1.
3. changing the first line to persistence.store.websql.config(persistence, 'newdatabase', 'testing migration', 5*1024*1024);, not calling schemaSync() and only calling migrate() will successfully log "migration complete!" — but it does so in a new, completely empty database "newdatabase", which will of course not retain any exsiting data.

Summary

There is a database that was created using persistence.store.websql.config(...), persistence.define('Todo',...) and persistence.schemaSync().

I now want to keep all the data that already exist in that database, but want to

• change the type of column priority from 'integer' to 'text'
• add a column due with type 'date' to all existing Todos

If you could push me in the right direction, I'd greatly appreciate it!

Thanks!

Answer 1

I finally got it working. There are a number of issues with my initial requirements that I'd like to point out for future reference. Take a look at the first migration definition:

persistence.defineMigration(1, {
    up: function() {
        this.createTable('Todo', function(t){
        ...

Not surprisingly, createTable will do exactly that: it will execute the SQL statement 'CREATE TABLE Todo ...', which will silently fail and halt the migration if there is a table with the name Todo already. This is why it worked with a new database, but not with the existing one. Bear in mind: I already had a live database with a table "Todo" that needed updating. If you're starting fresh (i.e. you've not used schemaSync), createTable works just fine. Since the Migrations plugin does not provide a createTableIfNotExists method, I needed to utilize executeSql as follows:

persistence.defineMigration(1, {
    up: function() {
        this.executeSql('CREATE TABLE IF NOT EXISTS Todo (id VARCHAR(32) PRIMARY KEY, task TEXT, priority INT, done BOOL)');
        ...

Now that the migration from schema version 0 to 1 succeeded, the migration to version 2 was successful as well.

With the migration to version 3 the type of the priority column needed to change from int to text. This would normally be done using the ALTER COLUMN SQL command, wich is not supported by Web SQL / SQLite. See Omitted Features for SQLite.

Altering a column with SQLite requires a 4-step workaround:

persistence.defineMigration(3, {
    up: function() {
        // rename current table
        this.executeSql('ALTER TABLE Todo RENAME TO OldTodo');
        // create new table with required columns and column types
        this.executeSql('CREATE TABLE Todo (id VARCHAR(32) PRIMARY KEY, task TEXT, priority TEXT, done BOOL)');
        // copy contents from old table to new table
        this.executeSql('INSERT INTO Todo(id, task, priority, done) SELECT id, task, priority, done FROM OldTodo');
        // delete old table
        this.executeSql('DROP TABLE OldTodo');
    },
    ...

Of course, after changing the column type, the entity definition for 'Todo' should also be changed:

var Todo = persistence.define('Todo', {
    task: 'TEXT',
    priority: 'TEXT', // was 'INT'
    due: 'DATE',
    done: 'BOOL'
});

And finally, the complete source:

persistence.store.websql.config(persistence, 'tododatabase', 'todos are fun', 5*1024*1024);

// persistence.debug = true;

//v0 + v1
// var Todo = persistence.define('Todo', {
//  task: 'TEXT',
//  priority: 'INT',
//  done: 'BOOL'
// });

//v2
// var Todo = persistence.define('Todo', {
//  task: 'TEXT',
//  priority: 'INT',
//  due: 'DATE',
//  done: 'BOOL'
// });

//v3
var Todo = persistence.define('Todo', {
    task: 'TEXT',
    priority: 'TEXT',
    due: 'DATE',
    done: 'BOOL'
});


persistence.defineMigration(1, {
    up: function() {
        this.executeSql('CREATE TABLE IF NOT EXISTS Todo (id VARCHAR(32) PRIMARY KEY, task TEXT, priority INT, done BOOL)');
    },
    down: function() {
        this.dropTable('Todo');
    }
});

persistence.defineMigration(2, {
    up: function() {
        this.addColumn('Todo', 'due', 'DATE');
    },
    down: function() {
        this.removeColumn('Todo', 'due');
    }
});

persistence.defineMigration(3, {
    up: function() {
        // rename current table
        this.executeSql('ALTER TABLE Todo RENAME TO OldTodo');
        // create new table with required columns
        this.executeSql('CREATE TABLE Todo (id VARCHAR(32) PRIMARY KEY, task TEXT, priority TEXT, due DATE, done BOOL)');
        // copy contents from old table to new table
        this.executeSql('INSERT INTO Todo(id, task, priority, due, done) SELECT id, task, priority, due, done FROM OldTodo');
        // delete current table
        this.executeSql('DROP TABLE OldTodo');
    },
    down: function() {
        this.executeSql('ALTER TABLE Todo RENAME TO OldTodo');
        this.executeSql('CREATE TABLE Todo (id VARCHAR(32) PRIMARY KEY, task TEXT, priority INT, due DATE, done BOOL)');
        this.executeSql('INSERT INTO Todo(id, task, priority, due, done) SELECT id, task, priority, due, done FROM OldTodo');
        this.executeSql('DROP TABLE OldTodo');
    }
});


function migrate( callback ){
    console.log('migrating...');
    persistence.migrations.init( function(){
        console.log('migration init');
        persistence.migrate( function(){
            console.debug('migration complete!');
            callback();
        } );
    });
};

migrate( onMigrationComplete );

function onMigrationComplete(){
    // database is ready. do amazing things...
};

Answer 2

That's a great explanation, thank you! But I think I know an easier way to achieve this.

I got in the same trouble like you: I'v got a set of schemas described with persistence.define and created with persistence.schemaSync.

So this is my particular case:

    // This is my mixin for all schemas    
    var Versioned = persistence.defineMixin('Versioned', {
      serverId: "TEXT",
      intVersion: "INT",
      dtSynced: "DATE",
      dtCreatedAt: "DATE",
      dtUpdatedAt: "DATE",
      delete: "BOOL",
      update: "BOOL",
      add: "BOOL",
      isReadOnly: "BOOL"
    });

    // This is one of the schemas I need to update with a new field.        
    var Person = persistence.define('Person', {
      fullName: "TEXT",
      rate: "INT"
    });

    //... More schema definitions

    // Setup mixin        
    Person.is(Versioned);

    // Sync schemas
    persistence.schemaSync();

Ok. Nothing special about it. Now after a few months my app's being in production I want to add a new field isEmployed to the Person schema.

According to the docs I should rewrite all of my schema definitions to the migrations and to stop using persistence.schemaSync(). But I don't want to rewrite all of my definitions. Instead of it I define a new migration right behind the PersistenceJS init code:

    // Init ORM
    persistence.store.websql.config(
      persistence,
      'Sarafan',
      '0.0.2',                
      'Sarafan.app database', 
      100 * 1024 * 1024,      
      0                        
   );

    // Define Migrations
    persistence.defineMigration(1, {
      up: function () {
        this.addColumn('Person', 'isEmployed', 'BOOL');
      }
    });

    // ... describing isVersioned mixin


    // Updated schema definition with a new field 'isEmployed'
    var Person = persistence.define('Person', {
      fullName: "TEXT",
      rate: "INT",
      isEmployed: "BOOL"
    });

    //... More schema definitions

    // Setup mixin        
    Person.is(Versioned);

    // Apply the migration right away from the schemaSync call.
    persistence.schemaSync(function (tx) {
      persistence.migrations.init(function () {
      persistence.migrate(function(){
        // Optional callback to be executed after initialization
        });
      });
    });

So that's it! I tested this approach only to add new fields to the schema.

Let me know if it does or doesn't work for you.