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
1
'use strict';
2
3
const {
4
app,
5
loggerPlugin,
6
toolsLib
7
} = this.pluginLibraries;
8
const lodash = require('lodash');
9
const sequelize = require('sequelize');
10
const umzug = require('umzug');
11
const cron = require('node-cron');
12
const { body, validationResult } = require('express-validator');
13
14
const AVAILABLE_REQUIREMENTS = {
15
kyc_verification: {
16
title: 'KYC Verification',
17
description: 'Require users to verify their identity'
18
},
19
email_verification: {
20
title: 'Email Verification',
21
description: 'Require users to verify their email'
22
},
23
sms_verification: {
24
title: 'SMS Verification',
25
description: 'Require users to input a valid phone number'
26
},
27
bank_verification: {
28
title: 'Bank Verification',
29
description: 'Require users to input a valid bank account and status is 3'
30
}
31
};
32
33
const init = async () => {
34
const umzugInstance = new umzug({
35
storage: 'sequelize',
36
storageOptions: {
37
sequelize: toolsLib.database.getModel('sequelize'),
38
modelName: 'PluginMigrations',
39
tableName: 'PluginMigrations'
40
},
41
upName: 'up',
42
downName: 'down',
43
migrations: umzug.migrationsList(
44
[
45
{
46
name: 'automatic_tier_upgrade-add_requirements_column',
47
up: (queryInterface, Sequelize) => queryInterface.describeTable('Tiers')
48
.then((table) => {
49
if (table['requirements']) {
50
return new Promise((resolve) => resolve());
51
} else {
52
return queryInterface.addColumn('Tiers', 'requirements', {
53
type: Sequelize.JSONB,
54
defaultValue: []
55
});
56
}
57
}),
58
down: (queryInterface, Sequelize) => queryInterface.describeTable('Tiers')
59
.then((table) => {
60
if (table['requirements']) {
61
return queryInterface.removeColumn('Tiers', 'requirements');
62
} else {
63
return true;
64
}
65
})
66
}
67
],
68
[toolsLib.database.getModel('sequelize').getQueryInterface(), sequelize]
69
)
70
});
71
72
const pending = await umzugInstance.pending();
73
if (pending.length > 0) {
74
await umzugInstance.up('automatic_tier_upgrade-add_requirements_column');
75
}
76
};
77
78
const findAllTiers = async () => {
79
return toolsLib.database.getModel('sequelize').query('SELECT * FROM "Tiers" ORDER BY id ASC', {
80
raw: true,
81
type: sequelize.QueryTypes.SELECT
82
});
83
};
84
85
const findTier = async (id) => {
86
return toolsLib.database.getModel('sequelize').query('SELECT * FROM "Tiers" WHERE id = :id', {
87
plain: true,
88
raw: true,
89
replacements: {
90
id
91
},
92
type: sequelize.QueryTypes.SELECT
93
});
94
};
95
96
const updateTier = async (id, requirements) => {
97
const tier = await toolsLib.database.getModel('sequelize').query('UPDATE "Tiers" SET requirements = :requirements WHERE id = :id RETURNING *', {
98
replacements: {
99
id,
100
requirements: JSON.stringify(requirements)
101
}
102
});
103
104
return tier[0][0];
105
};
106
107
const validateTierUpdate = async (tier_id, data = []) => {
108
const tiers = await findAllTiers();
109
110
// if removing all requirements, check if upper requirements exist
111
if (lodash.isEmpty(data)) {
112
const isInvalid = tiers.some((tier) => {
113
return tier.id > tier_id && !lodash.isEmpty(tier.requirements);
114
});
115
116
if (isInvalid) {
117
throw new Error('Cannot remove requirements if a higher tier has requirements set');
118
}
119
120
return;
121
}
122
123
// check if lower tier has requirements set if level is 3 or above
124
if (tier_id > 2) {
125
const isInvalid = tiers.some((tier) => {
126
return tier.id >= 2 && tier.id < tier_id && lodash.isEmpty(tier.requirements);
127
});
128
129
if (isInvalid) {
130
throw new Error('Lower tiers must have requirements set');
131
}
132
}
133
134
// check if given static requirements are already set for other tier levels
135
136
const isInvalid = tiers.some((tier) => {
137
const existingRequirements = lodash.intersection(data, tier.requirements);
138
return tier.id !== tier_id && !lodash.isEmpty(existingRequirements);
139
});
140
141
if (isInvalid) {
142
throw new Error('Static requirements can only be set for one tier');
143
}
144
};
145
146
const runner = async () => {
147
const tiers = await findAllTiers();
148
149
const users = await toolsLib.database.findAll('user', {
150
where: {
151
activated: true,
152
flagged: false
153
},
154
raw: true,
155
attributes: [
156
'id',
157
'email',
158
'phone_number',
159
'id_data',
160
'verification_level',
161
'email_verified',
162
'activated',
163
'bank_account',
164
'flagged'
165
]
166
});
167
168
const groupedUsers = lodash.groupBy(users, 'verification_level');
169
170
for (const level in groupedUsers) {
171
const userLevel = parseInt(level);
172
173
for (const user of groupedUsers[level]) {
174
loggerPlugin.debug(
175
'AUTO TIER UPGRADE PLUGIN',
176
`Checking verifications for user ${user.email} with level ${userLevel}`
177
);
178
179
let updatedLevel = userLevel;
180
181
const checkedTiers = tiers.filter((tier) => tier.id >= 2 && tier.id > userLevel);
182
183
const userVerifications = [];
184
185
if (user.id_data.status === 3) {
186
userVerifications.push('kyc_verification');
187
}
188
189
if (!lodash.isEmpty(user.phone_number)) {
190
userVerifications.push('sms_verification');
191
}
192
193
if (user.email_verified) {
194
userVerifications.push('email_verification');
195
}
196
197
if(!lodash.isEmpty(user.bank_account) && user.bank_account.some((account) => account.status === 3)){
198
userVerifications.push('bank_verification');
199
}
200
201
loggerPlugin.debug(
202
'AUTO TIER UPGRADE PLUGIN',
203
'User verifications',
204
userVerifications
205
);
206
207
for (const tier of checkedTiers) {
208
if (lodash.isEmpty(tier.requirements)) {
209
loggerPlugin.debug(
210
'AUTO TIER UPGRADE PLUGIN',
211
`Tier ${tier.id} does not have any requirement set`
212
);
213
break;
214
}
215
216
if (lodash.difference(tier.requirements, userVerifications).length === 0) {
217
loggerPlugin.verbose(
218
'AUTO TIER UPGRADE PLUGIN',
219
`User ${user.email} meets requirements for tier`,
220
tier.id
221
);
222
223
updatedLevel = tier.id;
224
}
225
}
226
227
if (updatedLevel > userLevel) {
228
loggerPlugin.verbose(
229
'AUTO TIER UPGRADE PLUGIN',
230
`User ${user.email} level will be changed from ${userLevel} to ${updatedLevel}`
231
);
232
233
await toolsLib.user.changeUserVerificationLevelById(user.id, updatedLevel);
234
}
235
}
236
}
237
};
238
239
const cronjob = cron.schedule('0 0 0 * * *', async () => {
240
loggerPlugin.verbose(
241
'/plugins/automatic-tier-upgrade Upgrade start'
242
);
243
try {
244
await runner();
245
} catch (err) {
246
loggerPlugin.error(
247
'/plugins/automatic-tier-upgrade error during upgrade:',
248
err.message
249
);
250
}
251
}, {
252
scheduled: false
253
});
254
255
init()
256
.then(() => {
257
cronjob.start();
258
259
app.get(
260
'/plugins/automatic-tier-upgrade/available-requirements',
261
[toolsLib.security.verifyBearerTokenExpressMiddleware(['admin'])],
262
(req, res) => {
263
loggerPlugin.verbose(
264
req.uuid,
265
'/plugins/automatic-tier-upgrade/available-requirements',
266
req.auth.sub
267
);
268
269
return res.json(AVAILABLE_REQUIREMENTS);
270
}
271
);
272
273
app.get(
274
'/plugins/automatic-tier-upgrade/requirements',
275
[toolsLib.security.verifyBearerTokenExpressMiddleware(['admin'])],
276
async (req, res) => {
277
loggerPlugin.verbose(
278
req.uuid,
279
'GET /plugins/automatic-tier-upgrade/requirements auth',
280
req.auth.sub
281
);
282
283
try {
284
const tiers = await findAllTiers();
285
286
let response = {};
287
288
for (const tier of tiers) {
289
response[tier.id] = tier.requirements;
290
}
291
292
return res.json(response);
293
} catch (err) {
294
loggerPlugin.error(
295
req.uuid,
296
'GET /plugins/automatic-tier-upgrade/requirements err',
297
err.message
298
);
299
return res.status(err.status || 400).json({message: err.message});
300
}
301
}
302
);
303
304
app.put(
305
'/plugins/automatic-tier-upgrade/requirements',
306
[
307
toolsLib.security.verifyBearerTokenExpressMiddleware(['admin']),
308
body('tier').isInt({ min: 1 }),
309
body('requirements').isArray()
310
],
311
async (req, res) => {
312
const errors = validationResult(req);
313
if (!errors.isEmpty()) {
314
return res.status(400).json({errors: errors.array()});
315
}
316
317
loggerPlugin.verbose(
318
req.uuid,
319
'PUT /plugins/automatic-tier-upgrade/requirements auth',
320
req.auth.sub
321
);
322
323
try {
324
const { tier: level, requirements } = req.body;
325
326
const tier = await findTier(level);
327
328
if (!tier) {
329
throw new Error(`Tier ${level} does not exist`);
330
}
331
332
const formattedRequirements = lodash.uniq(requirements);
333
334
if (lodash.difference(formattedRequirements, Object.keys(AVAILABLE_REQUIREMENTS)).length > 0) {
335
throw new Error('Invalid requirements given');
336
}
337
338
await validateTierUpdate(level, requirements);
339
340
const updatedTier = await updateTier(tier.id, requirements);
341
342
return res.json(
343
lodash.pick(updatedTier, ['id', 'requirements'])
344
);
345
} catch (err) {
346
loggerPlugin.error(
347
req.uuid,
348
'PUT /plugins/automatic-tier-upgrade/requirements err',
349
err.message
350
);
351
352
return res.status(err.status || 400).json({message: err.message});
353
}
354
});
355
})
356
.catch((err) => {
357
loggerPlugin.error(
358
'AUTOMATIC TIER UPGRADE PLUGIN error during initialization:',
359
err.message
360
);
361
});
Copied!
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
1
const init = async () => {
2
const umzugInstance = new umzug({
3
storage: 'sequelize',
4
storageOptions: {
5
sequelize: toolsLib.database.getModel('sequelize'),
6
modelName: 'PluginMigrations',
7
tableName: 'PluginMigrations'
8
},
9
upName: 'up',
10
downName: 'down',
11
migrations: umzug.migrationsList(
12
[
13
{
14
name: 'automatic_tier_upgrade-add_requirements_column',
15
up: (queryInterface, Sequelize) => queryInterface.describeTable('Tiers')
16
.then((table) => {
17
if (table['requirements']) {
18
return new Promise((resolve) => resolve());
19
} else {
20
return queryInterface.addColumn('Tiers', 'requirements', {
21
type: Sequelize.JSONB,
22
defaultValue: []
23
});
24
}
25
}),
26
down: (queryInterface, Sequelize) => queryInterface.describeTable('Tiers')
27
.then((table) => {
28
if (table['requirements']) {
29
return queryInterface.removeColumn('Tiers', 'requirements');
30
} else {
31
return true;
32
}
33
})
34
}
35
],
36
[toolsLib.database.getModel('sequelize').getQueryInterface(), sequelize]
37
)
38
});
39
40
const pending = await umzugInstance.pending();
41
if (pending.length > 0) {
42
await umzugInstance.up('automatic_tier_upgrade-add_requirements_column');
43
}
44
};
Copied!
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 tosequelizestorageOptionssequelize: This is the sequelize instance being used throughout our exchagne. We can get this instance using the tools librarygetModelfunctionmodelName: The name of the to be used model. Should be set toPluginMigrationstableName: The name of the table that stores migrations in the DB. Should be set toPluginMigrations
upName: Should be set toupdownName: Should be set todownmigrations: This is where we use the umzugmigrationsListfunction to create our migration. Each migration will need aname,upfunction, anddownfunction.name: The name of this migration. This should be formatted as<PLUGIN_NAME>-<MIGRATION_ACTION>e.g.automatic_tier_upgrade-add_requirements_columnup: The function to run when running the migrationdown: 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
1
const findAllTiers = async () => {
2
return toolsLib.database.getModel('sequelize').query('SELECT * FROM "Tiers" ORDER BY id ASC', {
3
raw: true,
4
type: sequelize.QueryTypes.SELECT
5
});
6
};
7
8
const findTier = async (id) => {
9
return toolsLib.database.getModel('sequelize').query('SELECT * FROM "Tiers" WHERE id = :id', {
10
plain: true,
11
raw: true,
12
replacements: {
13
id
14
},
15
type: sequelize.QueryTypes.SELECT
16
});
17
};
18
19
const updateTier = async (id, requirements) => {
20
const tier = await toolsLib.database.getModel('sequelize').query('UPDATE "Tiers" SET requirements = :requirements WHERE id = :id RETURNING *', {
21
replacements: {
22
id,
23
requirements: JSON.stringify(requirements)
24
}
25
});
26
27
return tier[0][0];
28
};
Copied!
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.