Discord plugin

Query data from Discord servers using SQL.

-- Get all messages from a channel
SELECT * FROM discord_messages('channel_id');

-- Check which invites are the most popular
SELECT * FROM discord_invites('guild_id') ORDER BY uses DESC;

-- Get all members of a guild
SELECT * FROM discord_members('guild_id');

-- Write a message to a channel
INSERT INTO discord_messages('channel_id', 'content') VALUES ('channel_id', 'Hello, world!');

-- Delete recent messages from a channel that contain the word 'hello'
DELETE FROM discord_messages('channel_id') WHERE content LIKE '%hello%' AND created_at > datetime('now', '-14 days');

Setup

1. Create a new Discord application at https://discord.com/developers/applications.
2. Go to the 'Bot' tab and click 'Add Bot'.
3. Reset the bot token and copy it. You will need it later.
4. Scroll down to configure the bot's permissions.
   1. Under 'Privileged Gateway Intents`, enable 'Server Members Intent' and 'Message Content Intent'.
   2. Under 'Bot permissions', enable 'Administrator'. If you know what you're doing, you can enable only the permissions you need.
5. Go to the 'OAuth2' tab and select 'bot' in the 'OAuth2 URL Generatorsection. Select theAdministrator` scope.
6. Copy the generated link and open it in your browser. Add the bot to your server.
7. Congratulations! You can now use the plugin.

Install the discord plugin:

anyquery install discord

and paste the bot token when prompted.

Guide on IDs

• guild_id is the ID of the server.
• channel_id is the ID of the channel.
• message_id is the ID of the message.
• user_id is the ID of the user.

You can find the guild_id by looking at the URL of the server. It is the first number after discord.com/channels/. For example, in https://discord.com/channels/123456789012345678/987654321098765432, the guild_id is 123456789012345678. The channel_id is the second number after discord.com/channels/. In the same example, the channel_id is 987654321098765432.

Other IDs can be found by running queries like SELECT * FROM discord_messages('channel_id').

Most tables require the guild_id or channel_id to be specified. You can either pass it as an argument to the table (e.g. discord_messages('channel_id')) or set it in the WHERE clause (e.g. SELECT * FROM discord_messages WHERE channel_id = 'channel_id').

Schema

discord_messages

The discord_messages table contains messages from a channel. It supports SELECT, INSERT, and DELETE queries. Inserted messages will be sent under the bot's name. Also, the plugin is unable to delete messages older than 14 days.

-- Get all messages from a channel
SELECT * FROM discord_messages('channel_id');
SELECT * FROM discord_messages WHERE channel_id = 'channel_id';

-- Write a message to a channel
INSERT INTO discord_messages('channel_id', 'content') VALUES ('channel_id', 'Hello, world!');

-- Delete recent messages from a channel that contain the word 'hello'
DELETE FROM discord_messages('channel_id') WHERE content LIKE '%hello%' AND created_at > datetime('now', '-14 days');

-- Count the number of messages in a channel that contain the word 'hello'
SELECT COUNT(*) FROM discord_messages('channel_id') WHERE content LIKE '%hello%';

-- Get the number of messages per user in a channel
SELECT username, COUNT(*) FROM discord_messages('channel_id') GROUP BY username, user_id ORDER BY COUNT(*) DESC;

discord_channels

The discord_channels table contains channels from a server. It supports SELECT, INSERT, UPDATE, and DELETE queries.

-- Get all channels from a server from top to bottom
SELECT * FROM discord_channels('guild_id') ORDER BY position;

-- Create a new channel
INSERT INTO discord_channels(guild_id, name, type) VALUES ('guild_id', 'my-channel', 'GUILD_TEXT');

-- Update a channel's name
UPDATE discord_channels('guild_id') SET name = 'new-name' WHERE name = 'old-name';

-- Delete a channel
DELETE FROM discord_channels('guild_id') WHERE name = 'channel-name';

Valid values for the channel type are GUILD_TEXT, DM, GUILD_VOICE, GROUP_DM, GUILD_CATEGORY, GUILD_NEWS, GUILD_STORE, GUILD_NEWS_THREAD, GUILD_PUBLIC_THREAD, GUILD_PRIVATE_THREAD, GUILD_STAGE_VOICE, GUILD_DIRECTORY, GUILD_FORUM, and GUILD_MEDIA.

discord_members

The discord_members list members of a server. It supports SELECT and DELETE queries.

-- Get all members of a server
SELECT * FROM discord_members('guild_id');

-- Count the number of members in a server
SELECT COUNT(*) FROM discord_members('guild_id');

-- Kick a member from a server
DELETE FROM discord_members('guild_id') WHERE username = 'username';

discord_bans

The discord_bans table contains banned users from a server. It supports SELECT, INSERT, and DELETE queries.

-- Get all banned users from a server
SELECT * FROM discord_bans('guild_id');

-- Ban a user from a server
INSERT INTO discord_bans('guild_id', 'user_id') VALUES ('guild_id', 'user_id');

-- Unban a user from a server
DELETE FROM discord_bans('guild_id') WHERE user_id = 'user_id';

discord_roles

List roles of a server. It supports SELECT queries.

-- Get all roles of a server
SELECT * FROM discord_roles('guild_id');

discord_guilds

List all the guilds the bot is in. It supports SELECT queries. You can use to get the guild_id of a server.

-- Get all guilds the bot is in
SELECT * FROM discord_guilds;

-- Get the guild ID of a server
SELECT guild_id FROM discord_guilds WHERE name = 'server-name';

discord_invites

List all the invites of a server. It supports SELECT queries.

-- Get all invites of a server
SELECT * FROM discord_invites('guild_id');

-- Get the number of uses per invite
SELECT code, uses FROM discord_invites('guild_id') ORDER BY uses DESC;