Advanced Tutorial: Adding a new database table column

In this tutorial, we will go over how to add a new column for an existing table by creating a new migration. To illustrate this, we are creating a plugin that allows an admin to set a list of requirements for tier levels. The requirements will be stored in a newly created column in the Tier table called requirements. This column will be created by a migration using the umzug library.

Changes to the database can result in unexpected side effects. Please be careful when altering yo

Script

'use strict';

const {
	app,
	loggerPlugin,
	toolsLib
} = this.pluginLibraries;
const lodash = require('lodash');
const sequelize = require('sequelize');
const umzug = require('umzug');
const cron = require('node-cron');
const { body, validationResult } = require('express-validator');

const AVAILABLE_REQUIREMENTS = {
	kyc_verification: {
		title: 'KYC Verification',
		description: 'Require users to verify their identity'
	},
	email_verification: {
		title: 'Email Verification',
		description: 'Require users to verify their email'
	},
	sms_verification: {
		title: 'SMS Verification',
		description: 'Require users to input a valid phone number'
	},
	bank_verification: {
		title: 'Bank Verification',
		description: 'Require users to input a valid bank account and status is 3'
	}
};

const init = async () => {
	const umzugInstance = new umzug({
		storage: 'sequelize',
		storageOptions: {
			sequelize: toolsLib.database.getModel('sequelize'),
			modelName: 'PluginMigrations',
			tableName: 'PluginMigrations'
		},
		upName: 'up',
		downName: 'down',
		migrations: umzug.migrationsList(
			[
				{
					name: 'automatic_tier_upgrade-add_requirements_column',
					up: (queryInterface, Sequelize) => queryInterface.describeTable('Tiers')
						.then((table) => {
							if (table['requirements']) {
								return new Promise((resolve) => resolve());
							} else {
								return queryInterface.addColumn('Tiers', 'requirements', {
									type: Sequelize.JSONB,
									defaultValue: []
								});
							}
						}),
					down: (queryInterface, Sequelize) => queryInterface.describeTable('Tiers')
						.then((table) => {
							if (table['requirements']) {
								return queryInterface.removeColumn('Tiers', 'requirements');
							} else {
								return true;
							}
						})
				}
			],
			[toolsLib.database.getModel('sequelize').getQueryInterface(), sequelize]
		)
	});

	const pending = await umzugInstance.pending();
	if (pending.length > 0) {
		await umzugInstance.up('automatic_tier_upgrade-add_requirements_column');
	}
};

const findAllTiers = async () => {
	return toolsLib.database.getModel('sequelize').query('SELECT * FROM "Tiers" ORDER BY id ASC', {
		raw: true,
		type: sequelize.QueryTypes.SELECT
	});
};

const findTier = async (id) => {
	return toolsLib.database.getModel('sequelize').query('SELECT * FROM "Tiers" WHERE id = :id', {
		plain: true,
		raw: true,
		replacements: {
			id
		},
		type: sequelize.QueryTypes.SELECT
	});
};

const updateTier = async (id, requirements) => {
	const tier = await toolsLib.database.getModel('sequelize').query('UPDATE "Tiers" SET requirements = :requirements WHERE id = :id RETURNING *', {
		replacements: {
			id,
			requirements: JSON.stringify(requirements)
		}
	});

	return tier[0][0];
};

const validateTierUpdate = async (tier_id, data = []) => {
	const tiers = await findAllTiers();

	// if removing all requirements, check if upper requirements exist
	if (lodash.isEmpty(data)) {
		const isInvalid = tiers.some((tier) => {
			return tier.id > tier_id && !lodash.isEmpty(tier.requirements);
		});

		if (isInvalid) {
			throw new Error('Cannot remove requirements if a higher tier has requirements set');
		}

		return;
	}

	// check if lower tier has requirements set if level is 3 or above
	if (tier_id > 2) {
		const isInvalid = tiers.some((tier) => {
			return tier.id >= 2 && tier.id < tier_id && lodash.isEmpty(tier.requirements);
		});

		if (isInvalid) {
			throw new Error('Lower tiers must have requirements set');
		}
	}

	// check if given static requirements are already set for other tier levels

	const isInvalid = tiers.some((tier) => {
		const existingRequirements = lodash.intersection(data, tier.requirements);
		return tier.id !== tier_id && !lodash.isEmpty(existingRequirements);
	});

	if (isInvalid) {
		throw new Error('Static requirements can only be set for one tier');
	}
};

const runner = async () => {
	const tiers = await findAllTiers();

	const users = await toolsLib.database.findAll('user', {
		where: {
			activated: true,
			flagged: false
		},
		raw: true,
		attributes: [
			'id',
			'email',
			'phone_number',
			'id_data',
			'verification_level',
			'email_verified',
			'activated',
			'bank_account',
			'flagged'
		]
	});

	const groupedUsers = lodash.groupBy(users, 'verification_level');

	for (const level in groupedUsers) {
		const userLevel = parseInt(level);

		for (const user of groupedUsers[level]) {
			loggerPlugin.debug(
				'AUTO TIER UPGRADE PLUGIN',
				`Checking verifications for user ${user.email} with level ${userLevel}`
			);

			let updatedLevel = userLevel;

			const checkedTiers = tiers.filter((tier) => tier.id >= 2 && tier.id > userLevel);

			const userVerifications = [];

			if (user.id_data.status === 3) {
				userVerifications.push('kyc_verification');
			}

			if (!lodash.isEmpty(user.phone_number)) {
				userVerifications.push('sms_verification');
			}

			if (user.email_verified) {
				userVerifications.push('email_verification');
			}

			if(!lodash.isEmpty(user.bank_account) && user.bank_account.some((account) => account.status === 3)){
				userVerifications.push('bank_verification');
			}

			loggerPlugin.debug(
				'AUTO TIER UPGRADE PLUGIN',
				'User verifications',
				userVerifications
			);

			for (const tier of checkedTiers) {
				if (lodash.isEmpty(tier.requirements)) {
					loggerPlugin.debug(
						'AUTO TIER UPGRADE PLUGIN',
						`Tier ${tier.id} does not have any requirement set`
					);
					break;
				}

				if (lodash.difference(tier.requirements, userVerifications).length === 0) {
					loggerPlugin.verbose(
						'AUTO TIER UPGRADE PLUGIN',
						`User ${user.email} meets requirements for tier`,
						tier.id
					);

					updatedLevel = tier.id;
				}
			}

			if (updatedLevel > userLevel) {
				loggerPlugin.verbose(
					'AUTO TIER UPGRADE PLUGIN',
					`User ${user.email} level will be changed from ${userLevel} to ${updatedLevel}`
				);

				await toolsLib.user.changeUserVerificationLevelById(user.id, updatedLevel);
			}
		}
	}
};

const cronjob = cron.schedule('0 0 0 * * *', async () => {
	loggerPlugin.verbose(
		'/plugins/automatic-tier-upgrade Upgrade start'
	);
	try {
		await runner();
	} catch (err) {
		loggerPlugin.error(
			'/plugins/automatic-tier-upgrade error during upgrade:',
			err.message
		);
	}
}, {
	scheduled: false
});

init()
	.then(() => {
		cronjob.start();
		
		app.get(
			'/plugins/automatic-tier-upgrade/available-requirements',
			[toolsLib.security.verifyBearerTokenExpressMiddleware(['admin'])],
			(req, res) => {
				loggerPlugin.verbose(
					req.uuid,
					'/plugins/automatic-tier-upgrade/available-requirements',
					req.auth.sub
				);

				return res.json(AVAILABLE_REQUIREMENTS);
			}
		);

		app.get(
			'/plugins/automatic-tier-upgrade/requirements',
			[toolsLib.security.verifyBearerTokenExpressMiddleware(['admin'])],
			async (req, res) => {
				loggerPlugin.verbose(
					req.uuid,
					'GET /plugins/automatic-tier-upgrade/requirements auth',
					req.auth.sub
				);

				try {
					const tiers = await findAllTiers();

					let response = {};

					for (const tier of tiers) {
						response[tier.id] = tier.requirements;
					}

					return res.json(response);
				} catch (err) {
					loggerPlugin.error(
						req.uuid,
						'GET /plugins/automatic-tier-upgrade/requirements err',
						err.message
					);
					return res.status(err.status || 400).json({message: err.message});
				}
			}
		);

		app.put(
			'/plugins/automatic-tier-upgrade/requirements',
			[
				toolsLib.security.verifyBearerTokenExpressMiddleware(['admin']),
				body('tier').isInt({ min: 1 }),
				body('requirements').isArray()
			],
			async (req, res) => {
				const errors = validationResult(req);
				if (!errors.isEmpty()) {
					return res.status(400).json({errors: errors.array()});
				}

				loggerPlugin.verbose(
					req.uuid,
					'PUT /plugins/automatic-tier-upgrade/requirements auth',
					req.auth.sub
				);

				try {
					const { tier: level, requirements } = req.body;

					const tier = await findTier(level);

					if (!tier) {
						throw new Error(`Tier ${level} does not exist`);
					}

					const formattedRequirements = lodash.uniq(requirements);

					if (lodash.difference(formattedRequirements, Object.keys(AVAILABLE_REQUIREMENTS)).length > 0) {
						throw new Error('Invalid requirements given');
					}

					await validateTierUpdate(level, requirements);

					const updatedTier = await updateTier(tier.id, requirements);

					return res.json(
						lodash.pick(updatedTier, ['id', 'requirements'])
					);
				} catch (err) {
					loggerPlugin.error(
						req.uuid,
						'PUT /plugins/automatic-tier-upgrade/requirements err',
						err.message
					);

					return res.status(err.status || 400).json({message: err.message});
				}
			});
	})
	.catch((err) => {
		loggerPlugin.error(
			'AUTOMATIC TIER UPGRADE PLUGIN error during initialization:',
			err.message
		);
	});

Breakdown

We will go over the important parts of the script above that demonstrate how we create and use the newly created requirements column.

Create and run the new migration in the init function

const init = async () => {
	const umzugInstance = new umzug({
		storage: 'sequelize',
		storageOptions: {
			sequelize: toolsLib.database.getModel('sequelize'),
			modelName: 'PluginMigrations',
			tableName: 'PluginMigrations'
		},
		upName: 'up',
		downName: 'down',
		migrations: umzug.migrationsList(
			[
				{
					name: 'automatic_tier_upgrade-add_requirements_column',
					up: (queryInterface, Sequelize) => queryInterface.describeTable('Tiers')
						.then((table) => {
							if (table['requirements']) {
								return new Promise((resolve) => resolve());
							} else {
								return queryInterface.addColumn('Tiers', 'requirements', {
									type: Sequelize.JSONB,
									defaultValue: []
								});
							}
						}),
					down: (queryInterface, Sequelize) => queryInterface.describeTable('Tiers')
						.then((table) => {
							if (table['requirements']) {
								return queryInterface.removeColumn('Tiers', 'requirements');
							} else {
								return true;
							}
						})
				}
			],
			[toolsLib.database.getModel('sequelize').getQueryInterface(), sequelize]
		)
	});

	const pending = await umzugInstance.pending();
	if (pending.length > 0) {
		await umzugInstance.up('automatic_tier_upgrade-add_requirements_column');
	}
};

To add our new requirements column in the Tier table, we need to create a new migration for our database. We can do this using the umzug library. We are first creating an instance of umzug with the following configurations:

• storage: Should be set to sequelize

• storageOptions

  • sequelize: This is the sequelize instance being used throughout our exchagne. We can get this instance using the tools library getModel function

  • modelName: The name of the to be used model. Should be set to PluginMigrations

  • tableName: The name of the table that stores migrations in the DB. Should be set to PluginMigrations

• upName: Should be set to up

• downName: Should be set to down

• migrations: This is where we use the umzug migrationsList function to create our migration. Each migration will need a name, up function, and down function.

  • name: The name of this migration. This should be formatted as <PLUGIN_NAME>-<MIGRATION_ACTION> e.g. automatic_tier_upgrade-add_requirements_column

  • up: The function to run when running the migration

  • down: The function to run when removing the migration

Once configured, we need to check if the migration has already been ran in the database. To do so, we can use umzugInstance.pending() to check if there are any migrations with the given name that have not been ran. If none are found, we run the migration using umzugInstance.up(<MIGRATION_NAME>).

Add helper functions for accessing the new column

const findAllTiers = async () => {
	return toolsLib.database.getModel('sequelize').query('SELECT * FROM "Tiers" ORDER BY id ASC', {
		raw: true,
		type: sequelize.QueryTypes.SELECT
	});
};

const findTier = async (id) => {
	return toolsLib.database.getModel('sequelize').query('SELECT * FROM "Tiers" WHERE id = :id', {
		plain: true,
		raw: true,
		replacements: {
			id
		},
		type: sequelize.QueryTypes.SELECT
	});
};

const updateTier = async (id, requirements) => {
	const tier = await toolsLib.database.getModel('sequelize').query('UPDATE "Tiers" SET requirements = :requirements WHERE id = :id RETURNING *', {
		replacements: {
			id,
			requirements: JSON.stringify(requirements)
		}
	});

	return tier[0][0];
};

Newly created columns are not included in the base database model for the Kit. This means we can't access the column using basic Tools Library functions. Instead, we need to use the Sequelize instance for the exchange to run raw SQL queries. We can do this using the getModel('sequelize').query(...) function. For our plugin, we need to get tiers with the requirements column included and update the requirements column.

The rest of the script is using the newly created column to set requirements for a tier level. There is also a cron job that upgrades user levels if they meet the requirements set for a tier level.