Support data migrations

[nrempel]

Currently, Adonis only supports schema migrations which only modify the schema of the database.

It would be convenient to also be able to run data migrations which modify the contents of the database.

I can already do this by using the Database provider in a migration but it seems out of place and I can't use co.

[thetutlage]

@nrempel I was think of something like

this.table('users', function () {
})

this.db(function * (database) {
  yield database.from('users').select('name')
})

[nrempel]

That looks 👌

[nrempel]

🎉

[RomainLanz]

So if we want to migrate data and then remove a field we should do?

this.table('users', function () {
  // add the new columns
})

this.db(function * (database) {
  // migrate the data
})

this.table('users', function () {
  // remove the old column
})

[nrempel]

That looks correct to me @RomainLanz. You could also break it out into several migration files too if you want.

@thetutlage I've tested this in my system and everything looks to be working correctly. 👍

[nrempel]

@thetutlage so far this is working really well.

One awkward thing is working with transactions.

this.db(function * (database) {


database.transaction(function(trx) {

  var books = [
    {title: 'Canterbury Tales'},
    {title: 'Moby Dick'},
    {title: 'Hamlet'}
  ];

  database.insert({name: 'Old Books'}, 'id')
    .into('catalogues')
    .transacting(trx)
    .then(function(ids) {
      return Promise.map(books, function(book) {
        book.catalogue_id = ids[0];

        // Some validation could take place here.

        return database.insert(info).into('books').transacting(trx);
      });
    })
    .then(trx.commit)
    .catch(trx.rollback);
})


})

I wonder if it's possible to facilitate the use of transactions?

Something like:

database.transaction(function * (trx) {
  yield database.insert({name: 'Old Books'}, 'id')
    .into('catalogues')
    .transacting(trx)
});

Or possibly even (not sure if this is possible):

const trx = yield database.transaction();
yield database.insert({name: 'Old Books'}, 'id')
  .into('catalogues')
  .transacting(trx)

[thetutlage]

Both are possible http://adonisjs.com/docs/3.0/query-builder#database-transactions

[nrempel]

Amazing! thanks.

[dustinboss]

@thetutlage has this functionality been removed in Adonis 4? I'm struggling to get the migration to wait for some data to be migrated. I want to do this:

1. Create a table
2. Move some data around
3. Drop a column on a table

I'm not sure the best way to do this. If I just use an async up call, the migration runs, but the migration action never terminates -- the connection just hangs.

[thetutlage]

Mind sharing some code :)

[dustinboss]

@thetutlage - I've created a simple example, which show my struggle.

Example 1: Using an async up call. The data migration happens before the photos table is created.

async up () {
  // Create a new table
  this.create('photos', (table) => {
    this.increments()
    this.string('url')
  })
  
  // Move some data
  const photos = await use('App/Models/User').query().where('photo_url', '>', '')
  const photosData = photos.map((record) => {
    return { 
      url: record.photo_url
    })
  await use('App/Models/Photo').createMany(photosData)
  
  // Modify another table
  this.table('users', (table) => {
    table.dropColumn('photo_url')
  })
}

Example 2: I move the data migration into the closure that will modify the second table. This waits until the first table is created, but the second table never ends up getting modified, and the migration script never terminates itself. It completes the migration, but the command doesn't terminate.

up () {
  // Create a new table
  this.create('photos', (table) => {
    this.increments()
    this.string('url')
  })
  
  // Modify another table
  this.table('users', async (table) => {
  
    // Move some data 
    const photos = await use('App/Models/User').query().where('photo_url', '>', '')
    const photosData = photos.map((record) => {
      return { 
        url: record.photo_url
      })
    await use('App/Models/Photo').createMany(photosData)
    
    // now modify the table
    table.dropColumn('photo_url')
  })
}

Example 3: I put the data migration in it's own closure (using a table closure, because the this.db doesn't seem to be available in Adonis 4 -- or at least I can't find it. This doesn't fire in sequence. The third function doesn't wait for the second function.

async up () {
  // Create a new table
  this.create('photos', (table) => {
    this.increments()
    this.string('url')
  })
  
  // Put the data migration in a closure, so it'll be queued with the other migrations
  this.table('photos', async (table) => {
    // Move some data 
    const photos = await use('App/Models/User').query().where('photo_url', '>', '')
    const photosData = photos.map((record) => {
      return { 
        url: record.photo_url
      })
    await use('App/Models/Photo').createMany(photosData)
  })

  // Modify another table
  this.table('users', (table) => {
    table.dropColumn('photo_url')
  })
}

[dustinboss]

@thetutlage - Any thoughts on this? If it's not possible, then I can sort out another way. Thanks!

[thetutlage]

@dusbuss Sorry for getting late. Also I recommend creating new issues, since at times closed issues are missed.

I have add a new commit, which let you run arbitrary code in the same sequence as you have wrote code.

Here's the commit for that and related docs.

Would you mind trying it by installing from Github and lemme know, then I will publish it on npm

[dustinboss]

@thetutlage - awesome! I just installed, and it works like a dream! Thank you!

[usmanalicc]

I installed it on MySQL Workbench 8.0 but it don't show any catalog in tool, but the plugin has been installed, how can I work with him? Catalog menu/sub menu is not showing in tools