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

'use strict';

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

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: 'twitter_plugin-create_twitter_table',
					up: (queryInterface, Sequelize) => queryInterface.createTable(
						'Twitters',
						{
							id: {
								allowNull: false,
								autoIncrement: true,
								primaryKey: true,
								type: Sequelize.INTEGER
							},
							user_id: {
								type: Sequelize.INTEGER,
								onDelete: 'CASCADE',
								allowNull: false,
								references: {
									model: 'Users',
									key: 'id'
								}
							},
							username: {
								type: Sequelize.STRING,
								allowNull: false,
								unique: true
							},
							updated_at: {
								allowNull: false,
								type: Sequelize.DATE,
								defaultValue: Sequelize.literal('NOW()')
							},
							created_at: {
								allowNull: false,
								type: Sequelize.DATE,
								defaultValue: Sequelize.literal('NOW()')
							}
						},
						{
							timestamps: true,
							underscored: true
						}
					),
					down: (queryInterface, Sequelize) => queryInterface.dropTable('Twitters')
				}
			],
			[toolsLib.database.getModel('sequelize').getQueryInterface(), sequelize]
		)
	});

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

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

const createUserTwitter = async (user_id, username) => {
	const twitter = await toolsLib.database.getModel('sequelize').query('INSERT INTO "Twitters" (user_id, username) VALUES (:user_id, :username) RETURNING *', {
		replacements: {
			user_id,
			username
		},
		type: sequelize.QueryTypes.INSERT
	});

	return twitter[0][0];
};

const updateUserTwitter = async (user_id, username) => {
	const twitter = await toolsLib.database.getModel('sequelize').query('UPDATE "Twitters" SET username = :username WHERE user_id = :user_id RETURNING *', {
		replacements: {
			user_id,
			username
		},
		type: sequelize.QueryTypes.UPDATE
	});

	return twitter[0][0];
};

init()
	.then(() => {
		app.get(
			'/plugins/twitter/username',
			[toolsLib.security.verifyBearerTokenExpressMiddleware(['user'])],
			async (req, res) => {
				loggerPlugin.verbose(
					req.uuid,
					'GET /plugins/twitter/username auth',
					req.auth.sub
				);

				const { id } = req.auth.sub;

				try {
					const user = await toolsLib.user.getUserByKitId(id);

					if (!user) {
						throw new Error('User not found');
					}

					const twitter = await findUserTwitter(user.id);

					if (!twitter) {
						throw new Error('User twitter username not found');
					}

					return res.json(twitter);
				} catch (err) {
					loggerPlugin.error(
						req.uuid,
						'GET /plugins/twitter/username err',
						err.message
					);
					return res.status(err.status || 400).json({message: err.message});
				}
			}
		);

		app.post(
			'/plugins/twitter/username',
			[
				toolsLib.security.verifyBearerTokenExpressMiddleware(['user']),
				body('username').isString().notEmpty()
			],
			async (req, res) => {
				const errors = validationResult(req);
				if (!errors.isEmpty()) {
					return res.status(400).json({errors: errors.array()});
				}

				const { id } = req.auth.sub;
				const { username } = req.body;

				loggerPlugin.verbose(
					req.uuid,
					'POST /plugins/twitter/username username:',
					username
				);

				try {
					const user = await toolsLib.user.getUserByKitId(id);

					if (!user) {
						throw new Error('User not found');
					}

					const twitter = await findUserTwitter(user.id);

					if (twitter) {
						throw new Error('User already has twitter username set');
					}

					const result = await createUserTwitter(user.id, username);

					return res.json(result);
				} catch (err) {
					loggerPlugin.error(
						req.uuid,
						'POST /plugins/twitter/username err:',
						err.message
					);
					return res.status(err.status || 400).json({message: err.message});
				}
			}
		);

		app.put(
			'/plugins/twitter/username',
			[
				toolsLib.security.verifyBearerTokenExpressMiddleware(['user']),
				body('username').isString().notEmpty()
			],
			async (req, res) => {
				const errors = validationResult(req);
				if (!errors.isEmpty()) {
					return res.status(400).json({errors: errors.array()});
				}

				const { id } = req.auth.sub;
				const { username } = req.body;

				loggerPlugin.verbose(
					req.uuid,
					'PUT /plugins/twitter/username username:',
					username
				);

				try {
					const user = await toolsLib.user.getUserByKitId(id);

					if (!user) {
						throw new Error('User not found');
					}

					const twitter = await findUserTwitter(user.id);

					if (!twitter) {
						throw new Error('User twitter username not found');
					}

					if (twitter.username === username) {
						throw new Error(`User twitter username is already ${username}`);
					}

					const result = await updateUserTwitter(user.id, username);

					return res.json(result);
				} catch (err) {
					loggerPlugin.error(
						req.uuid,
						'PUT /plugins/twitter/username err:',
						err.message
					);
					return res.status(err.status || 400).json({message: err.message});
				}
			}
		);
	})
	.catch((err) => {
		loggerPlugin.error(
			'TWITTER PLUGIN error during initialization:',
			err.message
		);
	});

Breakdown

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

Create New Table Migration

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: 'twitter_plugin-create_twitter_table',
					up: (queryInterface, Sequelize) => queryInterface.createTable(
						'Twitters',
						{
							id: {
								allowNull: false,
								autoIncrement: true,
								primaryKey: true,
								type: Sequelize.INTEGER
							},
							user_id: {
								type: Sequelize.INTEGER,
								onDelete: 'CASCADE',
								allowNull: false,
								references: {
									model: 'Users',
									key: 'id'
								}
							},
							username: {
								type: Sequelize.STRING,
								allowNull: false,
								unique: true
							},
							updated_at: {
								allowNull: false,
								type: Sequelize.DATE,
								defaultValue: Sequelize.literal('NOW()')
							},
							created_at: {
								allowNull: false,
								type: Sequelize.DATE,
								defaultValue: Sequelize.literal('NOW()')
							}
						},
						{
							timestamps: true,
							underscored: true
						}
					),
					down: (queryInterface, Sequelize) => queryInterface.dropTable('Twitters')
				}
			],
			[toolsLib.database.getModel('sequelize').getQueryInterface(), sequelize]
		)
	});

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

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

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

const createUserTwitter = async (user_id, username) => {
	const twitter = await toolsLib.database.getModel('sequelize').query('INSERT INTO "Twitters" (user_id, username) VALUES (:user_id, :username) RETURNING *', {
		replacements: {
			user_id,
			username
		},
		type: sequelize.QueryTypes.INSERT
	});

	return twitter[0][0];
};

const updateUserTwitter = async (user_id, username) => {
	const twitter = await toolsLib.database.getModel('sequelize').query('UPDATE "Twitters" SET username = :username WHERE user_id = :user_id RETURNING *', {
		replacements: {
			user_id,
			username
		},
		type: sequelize.QueryTypes.UPDATE
	});

	return twitter[0][0];
};

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.