how to create a knex migration for creating a column depending on other columns?

Question

I have a Node.js project and I'm using knex.js as a query builder.

I have model called cuboid:

import { Id, RelationMappings } from 'objection';
import { Bag } from './Bag';
import Base from './Base';

export class Cuboid extends Base {
  id!: Id;
  width!: number;
  height!: number;
  depth!: number;
  bagId?: Id;
  bag!: Bag;
  volume!: number;

  static tableName = 'cuboids';

  static get relationMappings(): RelationMappings {
    return {
      bag: {
        relation: Base.BelongsToOneRelation,
        modelClass: 'Bag',
        join: {
          from: 'cuboids.bagId',
          to: 'bags.id',
        },
      },
    };
  }
}

export default Cuboid;

I also have a migration where I create some columns for the cuboid table:

import { Bag, Cuboid } from '../../src/models';
import { Knex } from 'knex';

export const up = (knex: Knex): Promise<void> =>
  knex.schema.createTable(Cuboid.tableName, (table: Knex.TableBuilder) => {
    table.increments();
    table.timestamps();
    table.integer('width');
    table.integer('height');
    table.integer('depth');
    table.integer('bagId');
    table.foreign('bagId').references('id').inTable(Bag.tableName);
  });

export const down = (knex: Knex): Promise<void> =>
  knex.schema.dropTable(Cuboid.tableName);

I want to create a new migration where I define a column called 'volume'. The volume needs to be the product of the width, height, and depth columns(volume = width * height * depth).

import { Knex } from 'knex';
import { Cuboid } from '../../src/models';

export const up = (knex: Knex): Promise<void> =>
  knex.schema.alterTable(Cuboid.tableName, (table: Knex.TableBuilder) => {
/* 
* how can I define this column, so that it's value is the product of other three(width,height,depth) columns
* plus the value should be updated on insert and update queries for a cuboid record
* e.g if I change the height or width so the volume should also change after being created 
*/
    table.integer('volume');
  });

// ignore the down function
export const down = (knex: Knex): Promise<void> =>
  knex.schema.dropTable(Cuboid.tableName);

Note: the base model is created as a model from objection.js. It's something like this:

import { Model } from 'objection';
import * as path from 'path';

import knex from '../db/knex';

Model.knex(knex);

export default class Base extends Model {
  static get modelPaths(): string[] {
    return [path.resolve('src/models')];
  }
}

The create operation is something like

bag = await Bag.query().insertGraphAndFetch({
    volume: 100,
    title: 'A bag',
    cuboids: [{ width: 2, height: 2, depth: 2 }],
  });
//Note: A cubid belongs to a bag, so a bag is created before cuboids are assigned to it.

   cuboid = await Cuboid.query().insert({
      width,
      height,
      depth,
      bagId: bag.id,
    });

Answer 1

This isn't achievable using migrations, rather you can implement objection.js' model methods i.e afterInsert and afterUpdate to update the Cuboid's volume after a create/update operation is performed.

Your cuboid's model would become:

import { Id, ModelOptions, QueryContext, RelationMappings } from 'objection';
import { Bag } from './Bag';
import Base from './Base';

export class Cuboid extends Base {
  id!: Id;
  width!: number;
  height!: number;
  depth!: number;
  bagId?: Id;
  bag!: Bag;
  volume!: number;

  static tableName = 'cuboids';

  $afterInsert = async (queryContext: QueryContext) => {
    await super.$afterInsert(queryContext);
    console.log(' after insert working');
    await this.$query().updateAndFetchById(this.id, {
      volume: this.width * this.height * this.depth,
    });
  };
  $afterUpdate = async (opt: ModelOptions, queryContext: QueryContext) => {
    await super.$afterUpdate(opt, queryContext);
    console.log(' after update working');
    await this.$query().updateAndFetchById(this.id, {
      volume: this.width * this.height * this.depth,
    });
  };

  static get relationMappings(): RelationMappings {
    return {
      bag: {
        relation: Base.BelongsToOneRelation,
        modelClass: 'Bag',
        join: {
          from: 'cuboids.bagId',
          to: 'bags.id',
        },
      },
    };
  }
}

export default Cuboid;