Advanced Tutorial: Creating a new database table
In this tutorial, we will go over how to create a new table in our database by creating a new migration. To illustrate this, we are creating a plugin that allows users to add their twitter usernames. The requirements will be stored in a newly created table Twitters in the DB. This table will be created by a migration using the umzug library.
We DO NOT recommend creating a new table in the DB. This can potentially have cause adverse side effects on your exchange.

Script

1
'use strict';
2
3
const {
4
app,
5
loggerPlugin,
6
toolsLib
7
} = this.pluginLibraries;
8
const sequelize = require('sequelize');
9
const umzug = require('umzug');
10
const { body, validationResult } = require('express-validator');
11
12
const init = async () => {
13
const umzugInstance = new umzug({
14
storage: 'sequelize',
15
storageOptions: {
16
sequelize: toolsLib.database.getModel('sequelize'),
17
modelName: 'PluginMigrations',
18
tableName: 'PluginMigrations'
19
},
20
upName: 'up',
21
downName: 'down',
22
migrations: umzug.migrationsList(
23
[
24
{
25
name: 'twitter_plugin-create_twitter_table',
26
up: (queryInterface, Sequelize) => queryInterface.createTable(
27
'Twitters',
28
{
29
id: {
30
allowNull: false,
31
autoIncrement: true,
32
primaryKey: true,
33
type: Sequelize.INTEGER
34
},
35
user_id: {
36
type: Sequelize.INTEGER,
37
onDelete: 'CASCADE',
38
allowNull: false,
39
references: {
40
model: 'Users',
41
key: 'id'
42
}
43
},
44
username: {
45
type: Sequelize.STRING,
46
allowNull: false,
47
unique: true
48
},
49
updated_at: {
50
allowNull: false,
51
type: Sequelize.DATE,
52
defaultValue: Sequelize.literal('NOW()')
53
},
54
created_at: {
55
allowNull: false,
56
type: Sequelize.DATE,
57
defaultValue: Sequelize.literal('NOW()')
58
}
59
},
60
{
61
timestamps: true,
62
underscored: true
63
}
64
),
65
down: (queryInterface, Sequelize) => queryInterface.dropTable('Twitters')
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('twitter_plugin-create_twitter_table');
75
}
76
};
77
78
const findUserTwitter = async (user_id) => {
79
return toolsLib.database.getModel('sequelize').query('SELECT * FROM "Twitters" WHERE user_id = :user_id', {
80
plain: true,
81
raw: true,
82
replacements: {
83
user_id
84
},
85
type: sequelize.QueryTypes.SELECT
86
});
87
};
88
89
const createUserTwitter = async (user_id, username) => {
90
const twitter = await toolsLib.database.getModel('sequelize').query('INSERT INTO "Twitters" (user_id, username) VALUES (:user_id, :username) RETURNING *', {
91
replacements: {
92
user_id,
93
username
94
},
95
type: sequelize.QueryTypes.INSERT
96
});
97
98
return twitter[0][0];
99
};
100
101
const updateUserTwitter = async (user_id, username) => {
102
const twitter = await toolsLib.database.getModel('sequelize').query('UPDATE "Twitters" SET username = :username WHERE user_id = :user_id RETURNING *', {
103
replacements: {
104
user_id,
105
username
106
},
107
type: sequelize.QueryTypes.UPDATE
108
});
109
110
return twitter[0][0];
111
};
112
113
init()
114
.then(() => {
115
app.get(
116
'/plugins/twitter/username',
117
[toolsLib.security.verifyBearerTokenExpressMiddleware(['user'])],
118
async (req, res) => {
119
loggerPlugin.verbose(
120
req.uuid,
121
'GET /plugins/twitter/username auth',
122
req.auth.sub
123
);
124
125
const { id } = req.auth.sub;
126
127
try {
128
const user = await toolsLib.user.getUserByKitId(id);
129
130
if (!user) {
131
throw new Error('User not found');
132
}
133
134
const twitter = await findUserTwitter(user.id);
135
136
if (!twitter) {
137
throw new Error('User twitter username not found');
138
}
139
140
return res.json(twitter);
141
} catch (err) {
142
loggerPlugin.error(
143
req.uuid,
144
'GET /plugins/twitter/username err',
145
err.message
146
);
147
return res.status(err.status || 400).json({message: err.message});
148
}
149
}
150
);
151
152
app.post(
153
'/plugins/twitter/username',
154
[
155
toolsLib.security.verifyBearerTokenExpressMiddleware(['user']),
156
body('username').isString().notEmpty()
157
],
158
async (req, res) => {
159
const errors = validationResult(req);
160
if (!errors.isEmpty()) {
161
return res.status(400).json({errors: errors.array()});
162
}
163
164
const { id } = req.auth.sub;
165
const { username } = req.body;
166
167
loggerPlugin.verbose(
168
req.uuid,
169
'POST /plugins/twitter/username username:',
170
username
171
);
172
173
try {
174
const user = await toolsLib.user.getUserByKitId(id);
175
176
if (!user) {
177
throw new Error('User not found');
178
}
179
180
const twitter = await findUserTwitter(user.id);
181
182
if (twitter) {
183
throw new Error('User already has twitter username set');
184
}
185
186
const result = await createUserTwitter(user.id, username);
187
188
return res.json(result);
189
} catch (err) {
190
loggerPlugin.error(
191
req.uuid,
192
'POST /plugins/twitter/username err:',
193
err.message
194
);
195
return res.status(err.status || 400).json({message: err.message});
196
}
197
}
198
);
199
200
app.put(
201
'/plugins/twitter/username',
202
[
203
toolsLib.security.verifyBearerTokenExpressMiddleware(['user']),
204
body('username').isString().notEmpty()
205
],
206
async (req, res) => {
207
const errors = validationResult(req);
208
if (!errors.isEmpty()) {
209
return res.status(400).json({errors: errors.array()});
210
}
211
212
const { id } = req.auth.sub;
213
const { username } = req.body;
214
215
loggerPlugin.verbose(
216
req.uuid,
217
'PUT /plugins/twitter/username username:',
218
username
219
);
220
221
try {
222
const user = await toolsLib.user.getUserByKitId(id);
223
224
if (!user) {
225
throw new Error('User not found');
226
}
227
228
const twitter = await findUserTwitter(user.id);
229
230
if (!twitter) {
231
throw new Error('User twitter username not found');
232
}
233
234
if (twitter.username === username) {
235
throw new Error(`User twitter username is already ${username}`);
236
}
237
238
const result = await updateUserTwitter(user.id, username);
239
240
return res.json(result);
241
} catch (err) {
242
loggerPlugin.error(
243
req.uuid,
244
'PUT /plugins/twitter/username err:',
245
err.message
246
);
247
return res.status(err.status || 400).json({message: err.message});
248
}
249
}
250
);
251
})
252
.catch((err) => {
253
loggerPlugin.error(
254
'TWITTER PLUGIN error during initialization:',
255
err.message
256
);
257
});
Copied!

Breakdown

For this tutorial, we will only go over how we create and use the newly created DB table Twitters.

Create New Table Migration

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: 'twitter_plugin-create_twitter_table',
15
up: (queryInterface, Sequelize) => queryInterface.createTable(
16
'Twitters',
17
{
18
id: {
19
allowNull: false,
20
autoIncrement: true,
21
primaryKey: true,
22
type: Sequelize.INTEGER
23
},
24
user_id: {
25
type: Sequelize.INTEGER,
26
onDelete: 'CASCADE',
27
allowNull: false,
28
references: {
29
model: 'Users',
30
key: 'id'
31
}
32
},
33
username: {
34
type: Sequelize.STRING,
35
allowNull: false,
36
unique: true
37
},
38
updated_at: {
39
allowNull: false,
40
type: Sequelize.DATE,
41
defaultValue: Sequelize.literal('NOW()')
42
},
43
created_at: {
44
allowNull: false,
45
type: Sequelize.DATE,
46
defaultValue: Sequelize.literal('NOW()')
47
}
48
},
49
{
50
timestamps: true,
51
underscored: true
52
}
53
),
54
down: (queryInterface, Sequelize) => queryInterface.dropTable('Twitters')
55
}
56
],
57
[toolsLib.database.getModel('sequelize').getQueryInterface(), sequelize]
58
)
59
});
60
61
const pending = await umzugInstance.pending();
62
if (pending.length > 0) {
63
await umzugInstance.up('twitter_plugin-create_twitter_table');
64
}
65
};
Copied!
To create our new Twitters 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. twitter_plugin-create_twitter_table
    • up: The function to run when running the migration. Should be formatted as a basic sequelize migration.
    • down: The function to run when removing the migration. Should be formatted as a basic sequelize 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 table

1
const findUserTwitter = async (user_id) => {
2
return toolsLib.database.getModel('sequelize').query('SELECT * FROM "Twitters" WHERE user_id = :user_id', {
3
plain: true,
4
raw: true,
5
replacements: {
6
user_id
7
},
8
type: sequelize.QueryTypes.SELECT
9
});
10
};
11
12
const createUserTwitter = async (user_id, username) => {
13
const twitter = await toolsLib.database.getModel('sequelize').query('INSERT INTO "Twitters" (user_id, username) VALUES (:user_id, :username) RETURNING *', {
14
replacements: {
15
user_id,
16
username
17
},
18
type: sequelize.QueryTypes.INSERT
19
});
20
21
return twitter[0][0];
22
};
23
24
const updateUserTwitter = async (user_id, username) => {
25
const twitter = await toolsLib.database.getModel('sequelize').query('UPDATE "Twitters" SET username = :username WHERE user_id = :user_id RETURNING *', {
26
replacements: {
27
user_id,
28
username
29
},
30
type: sequelize.QueryTypes.UPDATE
31
});
32
33
return twitter[0][0];
34
};
Copied!
Newly created tables are not included in the base sequelize instance 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, create, and update Twitters rows for new users.
The rest of the script is using the newly created table to get, set, and update user Twitter usernames.