GuidesOnly show guides for your app
GuidesGuidesThe handful of table shapes almost every Discord bot needs — per-user, per-guild, per-guild-per-user, and event logs — with the right keys, why Discord IDs are always text, and when to split data into a second table.
Before you write a single query, you decide what your tables look like — the columns, the keys, how one table relates to another. Get the shape right and everything downstream (leaderboards, settings, upserts) falls out easily. Get it wrong and you fight your own schema forever. The good news: almost every bot needs the same four shapes, and this guide gives you all four.
| At a glance | |
|---|---|
| You need | somewhere to store data — SQLite in a bot, or a managed database |
| Helps to know | Just enough SQL |
| Time | about twenty minutes |
The examples use SQLite (better-sqlite3, the bot default) but the shapes are identical in the managed MySQL and PostgreSQL databases — only the column-type spellings differ, and this guide flags those.
Every user, guild, channel, and message on Discord has a snowflake — a big numeric ID like 739283749283749283. Store it as TEXT (SQLite) or VARCHAR(20) (MySQL/PostgreSQL), never as a plain number.
⚠️ Heads up: Snowflakes are 64-bit, but JavaScript numbers lose precision above about 9 quadrillion — paste one into a
numbercolumn and the last few digits quietly change. Text stores the ID exactly, and you never do arithmetic on an ID anyway, so nothing is lost.
That is why every verified recipe here — leveling, economy, migrations — uses TEXT for user_id and guild_id. Follow the same rule and your data lines up with theirs.
Match your data to one of these and the primary key writes itself:
| Your data is… | Primary key | Example |
|---|---|---|
| One row per user | user_id |
a wallet, a profile |
| One row per guild | guild_id |
server settings, a prefix |
| One row per user in a guild | (guild_id, user_id) |
XP, per-server stats |
| Many rows per user (a list) | its own id |
warnings, purchases, a log |
When a fact belongs to a person no matter which server they're in, the user's ID is the key. This is the economy recipe's wallet:
CREATE TABLE wallets (
user_id TEXT PRIMARY KEY,
balance INTEGER NOT NULL DEFAULT 0,
last_daily INTEGER NOT NULL DEFAULT 0
);
Settings belong to a server, so the guild ID is the key. One row holds that server's configuration:
CREATE TABLE guild_settings (
guild_id TEXT PRIMARY KEY,
prefix TEXT NOT NULL DEFAULT '!',
log_channel TEXT
);
XP is earned per server — the same person has separate progress in each guild. That needs both IDs in the key, together, as a composite primary key. This is the leveling recipe:
CREATE TABLE levels (
guild_id TEXT NOT NULL,
user_id TEXT NOT NULL,
xp INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (guild_id, user_id)
);
The composite key means one row per (guild, user) pair, and every query filters by guild_id so servers never bleed into each other.
When a user can have many of something — several warnings, a purchase history, a moderation trail — one row per user won't fit. Give the log its own table with its own auto-incrementing id, and store the user_id as an ordinary column:
CREATE TABLE warnings (
id INTEGER PRIMARY KEY, -- auto-assigned; AUTO_INCREMENT in MySQL
user_id TEXT NOT NULL,
guild_id TEXT NOT NULL,
reason TEXT,
created INTEGER NOT NULL -- a timestamp
);
Now one user maps to many warning rows — the classic one-to-many relationship. You fetch a user's warnings with WHERE user_id = ? and join back to other tables when you need names or settings (see JOINs in the SQL guide).
NOT NULL DEFAULT, always. Giving balance, xp, and friends a default of 0 lets a first-seen user's row be created by a plain upsert with no special case — exactly what makes INSERT ... ON CONFLICT in the recipes "just work" on the first message and the thousandth.
Money is an INTEGER. Store whole coins, cents, or points — never a floating-point number. 0.1 + 0.2 isn't 0.3 in floats, and that drift is a bug an economy can't afford. The economy recipe counts whole coins for exactly this reason.
Index what you sort or filter by. The primary key is already indexed. A leaderboard that runs ORDER BY xp DESC on a big table benefits from an extra index so it doesn't scan everyone:
CREATE INDEX idx_levels_xp ON levels (guild_id, xp);
The deciding question is: is it one value, or a list?
user_id column, one row per item. The economy recipe notes this exact move: a shop that grants items adds an inventory table rather than cramming a list into one field.Resist the temptation to store a list as a comma-joined string or a JSON blob in one column. The moment you want to ask "who owns a Crown?" or "how many warnings this month?", a real row per item answers it with one query; a packed string forces you to load and parse everything. That's what "normalize" means in practice — give repeating things their own rows.
🎯 Good to know: Keep it flat until a list forces your hand. Over-splitting a simple bot into a dozen tables is its own kind of pain. Start with the four shapes above; add tables when the data is genuinely a list.
The shapes don't change on a managed database — a few type names do:
| SQLite | MySQL | PostgreSQL |
|---|---|---|
TEXT (for IDs) |
VARCHAR(20) |
VARCHAR(20) / TEXT |
INTEGER |
INT / BIGINT |
INTEGER / BIGINT |
INTEGER PRIMARY KEY (auto) |
INT AUTO_INCREMENT |
SERIAL / BIGSERIAL |
Everything else — composite keys, NOT NULL DEFAULT, indexes, one-to-many tables — is written the same way. Pick your engine with Choosing between Redis, SQLite, and MySQL; the shapes above travel with you.