---
title: Making a Currency System
---

A common feature of Discord bots is a currency system. It's possible to do everything in one object, but we can also abstract that in terms of _relations_ between objects. This is where the power of a RDBMS (Relational Database Management System) truly shines. Sequelize calls these _associations_, so we'll be using that term from now on.

## File overview

There will be multiple files: a DB init script, your models, and your bot script. In [the sequelize guide](./), we placed all of these in the same file. Having everything in one file isn't an ideal practice, so we'll correct that.

This time we'll have six files.

- `app.js` is where we'll keep the main bot code.
- `dbInit.js` is the initialization file for the database. We run this once and forget about it.
- `dbObjects.js` is where we'll import the models and create associations here.
- `models/Users.js` is the Users model. Users will have a currency attribute in here.
- `models/CurrencyShop.js` is the Shop model. The shop will have a name and a price for each item.
- `models/UserItems.js` is the junction table between the users and the shop. A junction table connects two tables. Our junction table will have an additional field for the amount of that item the user has.

## Create models

Here is an entity relation diagram of the models we'll be making:

<Mermaid
	chart="
erDiagram
	USER ||--o{ USERITEMS : owns 
	CURRENCYSHOP ||..o{ USERITEMS : offers
	USER {
		string user_id PK
		string balance
	}
	USERITEMS
	USERITEMS {
		string user_id FK
		string item_id FK
		number amount
	}
	CURRENCYSHOP
	CURRENCYSHOP {
		string id PK
		string name
		number cost
	}
"
/>

`Users` have a `user_id`, and a `balance`. Each `user_id` can have multiple links to the `UserItems` table, and each entry in the table connects to one of the items in the `CurrencyShop`, which will have a `name` and a `cost` associated with it.

To implement this, begin by making a `models` folder and create a `Users.js` file inside which contains the following:

```js title="models/Users.js" lineNumbers
module.exports = (sequelize, DataTypes) => {
	return sequelize.define(
		'users',
		{
			user_id: {
				type: DataTypes.STRING,
				primaryKey: true, // [!code word:primaryKey]
			},
			balance: {
				type: DataTypes.INTEGER,
				defaultValue: 0,
				allowNull: false,
			},
		},
		{
			timestamps: false, // [!code word:timestamps]
		},
	);
};
```

Like you see in the diagram above, the Users model will only have two attributes: a `user_id` primary key and a `balance`. A primary key is a particular attribute that becomes the default column used when joining tables together, and it is automatically unique and not `null`.

Balance also sets `allowNull` to `false`, which means that both values have to be set in conjunction with creating a primary key; otherwise, the database would throw an error. This constraint guarantees correctness in your data storage. You'll never have `null` or empty values, ensuring that if you somehow forget to validate in the application that both values are not `null`, the database would do a final validation.

Notice that the options object sets `timestamps` to `false`. This option disables the `createdAt` and the `updatedAt` columns that sequelize usually creates for you. Setting `user_id` to primary also eliminates the `id` primary key that Sequelize usually generates for you since there can only be one primary key on a table.

Next, still in the same `models` folder, create a `CurrencyShop.js` file that contains the following:

```js title="models/CurrencyShop.js" lineNumbers
module.exports = (sequelize, DataTypes) => {
	return sequelize.define(
		'currency_shop',
		{
			name: {
				type: DataTypes.STRING,
				unique: true, // [!code word:unique]
			},
			cost: {
				type: DataTypes.INTEGER,
				allowNull: false,
			},
		},
		{
			timestamps: false,
		},
	);
};
```

Like the Users model, timestamps aren't needed here, so you can disable it. Unlike the Users model, however, the `unique` field is set to `true` here, allowing you to change the name without affecting the primary key that joins this to the next object. This gets generated automatically by sequelize since a primary key isn't set.

The next file will be `UserItems.js`, the junction table.

```js title="models/UserItems.js" lineNumbers
module.exports = (sequelize, DataTypes) => {
	return sequelize.define(
		'user_item',
		{
			user_id: DataTypes.STRING, // [!code word:user_id]
			item_id: DataTypes.INTEGER, // [!code word:item_id]
			amount: {
				type: DataTypes.INTEGER,
				allowNull: false,
				default: 0,
			},
		},
		{
			timestamps: false,
		},
	);
};
```

The junction table will link `user_id` and the `id` of the currency shop together. It also contains an `amount` number, which indicates how many of that item a user has.

## Initialize database

Now that the models are defined, you should create them in your database to access them in the bot file. We ran the sync inside the `ready` event in the previous tutorial, which is entirely unnecessary since it only needs to run once. You can make a file to initialize the database and never touch it again unless you want to remake the entire database.

Create a file called `dbInit.js` in the base directory (_not_ in the `models` folder).

<Callout type="error" title="Attention! Security Risk!">
	**Make sure you use version 5 or later of Sequelize!** As used in this guide, version 4 and earlier will pose a
	security threat. You can read more about this issue on the [Sequelize issue
	tracker](https://github.com/sequelize/sequelize/issues/7310).
</Callout>

```js title="dbInit.js" lineNumbers
const Sequelize = require('sequelize');

const sequelize = new Sequelize('database', 'username', 'password', {
	host: 'localhost',
	dialect: 'sqlite',
	logging: false,
	storage: 'database.sqlite',
});

const CurrencyShop = require('./models/CurrencyShop.js')(sequelize, Sequelize.DataTypes);
require('./models/Users.js')(sequelize, Sequelize.DataTypes);
require('./models/UserItems.js')(sequelize, Sequelize.DataTypes);

const force = process.argv.includes('--force') || process.argv.includes('-f');

sequelize
	.sync({ force })
	.then(async () => {
		const shop = [
			// [!code word:upsert]
			CurrencyShop.upsert({ name: 'Tea', cost: 1 }),
			CurrencyShop.upsert({ name: 'Coffee', cost: 2 }),
			CurrencyShop.upsert({ name: 'Cake', cost: 5 }),
		];

		await Promise.all(shop);
		console.log('Database synced');

		sequelize.close();
	})
	.catch(console.error);
```

Here you pull the two models and the junction table from the respective model declarations, sync them, and add items to the shop.

A new function here is the `.upsert()` function. It's a portmanteau for **up**date or in**sert**. `upsert` is used here to avoid creating duplicates if you run this file multiple times. That shouldn't happen because `name` is defined as _unique_, but there's no harm in being safe. Upsert also has a nice side benefit: if you adjust the cost, the respective item should also have their cost updated.

<Callout>
	Execute `node dbInit.js` to create the database tables. Unless you make a change to the models, you'll never need to
	touch the file again. If you change a model, you can execute `node dbInit.js --force` or `node dbInit.js -f` to force
	sync your tables. It's important to note that this **will** empty and remake your model tables.
</Callout>

## Create associations

Next, add the associations to the models. Create a file named `dbObjects.js` in the base directory, next to `dbInit.js`.

```js title="dbObjects.js" lineNumbers
const Sequelize = require('sequelize');

const sequelize = new Sequelize('database', 'username', 'password', {
	host: 'localhost',
	dialect: 'sqlite',
	logging: false,
	storage: 'database.sqlite',
});

const Users = require('./models/Users.js')(sequelize, Sequelize.DataTypes);
const CurrencyShop = require('./models/CurrencyShop.js')(sequelize, Sequelize.DataTypes);
const UserItems = require('./models/UserItems.js')(sequelize, Sequelize.DataTypes);

UserItems.belongsTo(CurrencyShop, { foreignKey: 'item_id', as: 'item' }); // [!code word:belongsTo]

Reflect.defineProperty(Users.prototype, 'addItem', {
	value: async (item) => {
		const userItem = await UserItems.findOne({
			// [!code word:findOne]
			where: { user_id: this.user_id, item_id: item.id },
		});

		if (userItem) {
			userItem.amount += 1;
			return userItem.save();
		}

		return UserItems.create({ user_id: this.user_id, item_id: item.id, amount: 1 });
	},
});

Reflect.defineProperty(Users.prototype, 'getItems', {
	value: () => {
		return UserItems.findAll({
			// [!code word:findAll]
			where: { user_id: this.user_id },
			include: ['item'],
		});
	},
});

module.exports = { Users, CurrencyShop, UserItems };
```

Note that the connection object could be abstracted in another file and had both `dbInit.js` and `dbObjects.js` use that connection file, but it's not necessary to overly abstract things.

Another new method here is the `.belongsTo()` method. Using this method, you add `CurrencyShop` as a property of `UserItem` so that when you do `userItem.item`, you get the respectively attached item. You use `item_id` as the foreign key so that it knows which item to reference.

You then add some methods to the `Users` object to finish up the junction: add items to users, and get their current inventory. The code inside should be somewhat familiar from the last tutorial. `.findOne()` is used to get the item if it exists in the user's inventory. If it does, increment it; otherwise, create it.

Getting items is similar; use `.findAll()` with the user's id as the key. The `include` key is for associating the CurrencyShop with the item. You must explicitly tell Sequelize to honor the `.belongsTo()` association; otherwise, it will take the path of the least effort.

## Application code

Create an `app.js` file in the base directory with the following skeleton code to put it together.

```js title="app.js" lineNumbers
const { Op } = require('sequelize');
const { Client, codeBlock, Collection, Events, GatewayIntentBits } = require('discord.js');
const { Users, CurrencyShop } = require('./dbObjects.js');

const client = new Client({ intents: [GatewayIntentBits.Guilds, GatewayIntentBits.GuildMessages] });
const currency = new Collection();

client.once(Events.ClientReady, (readyClient) => {
	console.log(`Ready! Logged in as ${readyClient.user.tag}`);
});

client.on(Events.MessageCreate, async (message) => {
	if (message.author.bot) return;
	addBalance(message.author.id, 1);
});

client.on(Events.InteractionCreate, async (interaction) => {
	if (!interaction.isChatInputCommand()) return;

	const { commandName } = interaction;
	// ...
});

client.login('your-token-goes-here');
```

Nothing special about this skeleton. You import the Users and CurrencyShop models from our `dbObjects.js` file and add a currency Collection. Every time someone talks, add 1 to their currency count. The rest is just standard discord.js code and a simple if/else command handler. A Collection is used for the `currency` variable to cache individual users' currency, so you don't have to hit the database for every lookup. An if/else handler is used here, but you can put it in a framework or command handler as long as you maintain a reference to the models and the currency collection.

### Helper methods

```js title="app.js" lineNumbers=5
// ...
const client = new Client({ intents: [GatewayIntentBits.Guilds, GatewayIntentBits.GuildMessages] });
const currency = new Collection();

async function addBalance(id, amount) {
	// [!code ++:18]
	const user = currency.get(id);

	if (user) {
		user.balance += Number(amount);
		return user.save();
	}

	const newUser = await Users.create({ user_id: id, balance: amount });
	currency.set(id, newUser);

	return newUser;
}

function getBalance(id) {
	const user = currency.get(id);
	return user ? user.balance : 0;
}
```

This defines the `addBalance()` helper function, since it'll be used quite frequently. A `getBalance()` function is also defined, to ensure that a number is always returned.

### Ready event data sync

```js title="app.js"
client.once(Events.ClientReady, async (readyClient) => {
	const storedBalances = await Users.findAll(); // [!code ++:2]
	storedBalances.forEach((b) => currency.set(b.user_id, b));

	console.log(`Ready! Logged in as ${readyClient.user.tag}!`);
});
```

In the ready event, sync the currency collection with the database for easy access later.

### Show user balance

```js title="app.js"
client.on(Events.InteractionCreate, async (interaction) => {
	if (!interaction.isChatInputCommand()) return;

	const { commandName } = interaction;

	if (commandName === 'balance') {
		// [!code ++:5]
		const target = interaction.options.getUser('user') ?? interaction.user;

		return interaction.reply(`${target.tag} has ${getBalance(target.id)}💰`); // [!code word:getBalance]
	}
});
```

Nothing tricky here. The `getBalance()` function is used to show either the author's or the mentioned user's balance.

### Show user inventory

```js title="app.js"
if (commandName === 'balance') {
	// ...
} // [!code --]
} else if (commandName === 'inventory') { // [!code ++:9]
	const target = interaction.options.getUser('user') ?? interaction.user;
	const user = await Users.findOne({ where: { user_id: target.id } });
	const items = await user.getItems();

	if (!items.length) return interaction.reply(`${target.tag} has nothing!`);

	return interaction.reply(`${target.tag} currently has ${items.map((i) => `${i.amount} ${i.item.name}`).join(', ')}`);
}
```

This is where you begin to see the power of associations. Even though users and the shop are different tables, and the data is stored separately, you can get a user's inventory by looking at the junction table and join it with the shop; no duplicated item names that waste space!

### Transfer currency to another user

```js title="app.js"
if (commandName === 'balance') {
	// ...
} else if (commandName === 'inventory') // [!code focus:16]
	// ... /
} // [!code --]
} else if (commandName === 'transfer') { // [!code ++:13]
	const currentAmount = getBalance(interaction.user.id);
	const transferAmount = interaction.options.getInteger('amount');
	const transferTarget = interaction.options.getUser('user');

	if (transferAmount > currentAmount) return interaction.reply(`Sorry ${interaction.user}, you only have ${currentAmount}.`);
	if (transferAmount <= 0) return interaction.reply(`Please enter an amount greater than zero, ${interaction.user}.`);

	addBalance(interaction.user.id, - transferAmount); // [!code word:addBalance]
	addBalance(transferTarget.id, transferAmount);

	return interaction.reply(`Successfully transferred ${transferAmount}💰 to ${transferTarget.tag}. Your current balance is ${getBalance(interaction.user.id)}💰`); // [!code word:getBalance]
}
```

As a bot creator, you should always be thinking about how to make the user experience better. Good UX makes users less frustrated with your commands. If your inputs are different types, don't make them memorize which parameters come before the other.

`addBalance()` is used for both removing and adding currency. Since transfer amounts below zero are disallowed, it's safe to apply the transfer amount's additive inverse to their balance.

### Buying an item

```js title="app.js"
if (commandName === 'balance') {
	// ...
} else if (commandName === 'inventory')
	// ... /
} else if (commandName === 'transfer') {
	// ...
} // [!code --]
} else if (commandName === 'buy') { // [!code ++:15]
	const itemName = interaction.options.getString('item');
	const item = await CurrencyShop.findOne({ where: { name: { [Op.like]: itemName } } });

	if (!item) return interaction.reply(`That item doesn't exist.`);
	if (item.cost > getBalance(interaction.user.id)) {
		return interaction.reply(`You currently have ${getBalance(interaction.user.id)}, but the ${item.name} costs ${item.cost}!`);
	}

	const user = await Users.findOne({ where: { user_id: interaction.user.id } });
	addBalance(interaction.user.id, -item.cost);
	await user.addItem(item);

	return interaction.reply(`You've bought: ${item.name}.`);
}
```

For users to search for an item without caring about the letter casing, you can use the `$iLike` modifier when looking for the name. Keep in mind that this may be slow if you have millions of items, so please don't put a million items in your shop.

### Display the shop

```js title="app.js"
if (commandName === 'balance') {
	// ...
} else if (commandName === 'inventory')
	// ... /
} else if (commandName === 'transfer') {
	// ...
} else if (commandName === 'buy') { // [!code focus:8]
	// ...
} // [!code --]
} else if (commandName === 'shop') { // [!code ++:4]
	const items = await CurrencyShop.findAll(); // [!code word:findAll]
	return interaction.reply(codeBlock(items.map(i => `${i.name}: ${i.cost}💰`).join('\n')));
}
```

There's nothing special here; just a regular `.findAll()` to get all the items in the shop and `.map()` to transform that data into something nice looking.

### Display the leaderboard

```js title="app.js"
if (commandName === 'balance') {
	// ...
} else if (commandName === 'inventory')
	// ... /
} else if (commandName === 'transfer') {
	// ...
} else if (commandName === 'buy') {
	// ...
} else if (commandName === 'shop') { // [!code focus:14]
	// ...
} // [!code --]
} else if (commandName === 'leaderboard') { // [!code ++:11]
	return interaction.reply(
		codeBlock(
			currency.sort((a, b) => b.balance - a.balance)
				.filter(user => client.users.cache.has(user.user_id))
				.first(10)
				.map((user, position) => `(${position + 1}) ${(client.users.cache.get(user.user_id).tag)}: ${user.balance}💰`)
				.join('\n'),
		),
	);
}
```

Nothing extraordinary here either. You could query the database for the top ten currency holders, but since you already have access to them locally inside the `currency` variable, you can sort the Collection and use `.map()` to display it in a friendly format. The filter is in case the users no longer exist in the bot's cache.
