Database Manager: create manual migration

Feature Request
#1

Sometimes you need to create very specific migrations that are not covered in the UI.

This FR is to add a Create manual migration in the context menu of Changes.

image

It should open a template with the following contents or similar.

// For documentation on how to create migrations go to http://knexjs.org

exports.up = function (knex) {
  return knex...
};

exports.down = function (knex) {
  return knex...
};

On save it should add the appropiate naming convention and add the database info when processed.

Now that we have 5.0 and the team is getting bigger I went through the feature requests
TimescaleDB time-series database support
#2

I’m assuming this feature is not used much. Do people even use migrations?

Anyway, I bump this.

I had to manually create a migration file to add a custom function(one that is very common).

For this I created a fake table in the Database Manager so it would generate a migration file.

Next I proceed to revert the changes and change the migration filename in .wappler/migrations/db to one that suits what I want it to do.

Then I code the actual migration which is not possible to do from the UI.

const ON_UPDATE_TIMESTAMP_FUNCTION = `
  CREATE OR REPLACE FUNCTION on_update_timestamp()
  RETURNS trigger AS $$
  BEGIN
    NEW.updated_at = now();
    RETURN NEW;
  END;
$$ language 'plpgsql';
`

const DROP_ON_UPDATE_TIMESTAMP_FUNCTION = `DROP FUNCTION on_update_timestamp`

exports.up = knex => knex.raw(ON_UPDATE_TIMESTAMP_FUNCTION)
exports.down = knex => knex.raw(DROP_ON_UPDATE_TIMESTAMP_FUNCTION)

I apply the changes and now I have a good migration file and the wappler_migrations tables in sync plus the function I want in the database.

After that I created a knexfile.js with a helper in .wappler/migrations

module.exports = {
    onUpdateTrigger: table => `
      CREATE TRIGGER ${table}_updated_at
      BEFORE UPDATE ON "${table}"
      FOR EACH ROW
      EXECUTE PROCEDURE on_update_timestamp();
    `
}

And finally I can create hot sexy migrations for all my tables that will update my updated_at column whenever a row changes its data.

const { onUpdateTrigger } = require('../knexfile')

exports.up = function (knex) {
    return knex.schema
        .createTable('whatever').then(() => knex.raw(onUpdateTrigger('whatever')))
};

exports.down = function (knex) {
    return knex.schema
        .dropTable('whatever')
};

This might seem like a how-to guide but it really is a post that shows the shortcomings of the UI when it comes to managing migrations.

Being able to create manual migrations from the UI so it creates the appropiate files and database changes in the migrations table would be nice.

Either that or just add some options in the table window to create timestamps automatically, defaultTo and to add the appropiate code to add triggers onUpdate and others.

image

Look at all that blank space there waiting for some checkboxes or dropdowns :wink:

#3

Bump, let us create manual migrations!

I ask ChatGPT to build odd migrations for me (like, marking a combination of three columns unique as a whole), things that Wappler doesn’t do :wink:

1 Like