If your Discord bot keeps any kind of state, like warnings, prefixes, levels, or a welcome channel, you need somewhere to put that data so it survives a restart. This tutorial walks you through adding real persistent storage to a bot, starting with SQLite for small projects and moving up to PostgreSQL when you outgrow it. It's written for people who already have a working bot in Python (discord.py) or JavaScript (discord.js) and want to stop losing data every time the process stops.
Why flat JSON files stop working
Lots of bots start by dumping everything into a single data.json file. You load it on startup, hold it in memory, and write the whole thing back to disk whenever something changes. It's simple, and for a tiny bot in one server it's fine.
The trouble shows up as the bot grows. A few problems we've seen over and over:
- You rewrite the entire file for one tiny change. Update one user's warning count and you serialize and write the whole structure again. With thousands of users that gets slow.
- A crash mid-write corrupts everything. If the bot dies while writing, you can end up with a half-written file and lose all of it, not just the last change.
- Concurrent writes clash. Two commands firing at once can both load, both edit, and the second one overwrites the first one's change.
- Querying is painful. "Give me the top 10 users by XP" means loading everything into memory and sorting by hand. A database does that in one line.
A real database fixes all of that. It writes only the rows that change, it handles concurrent access, and it lets you ask precise questions. So let's set one up.
Start with SQLite
SQLite is the right first step for most bots. It's a single file on disk, there's no separate server to install or keep running, and it speaks normal SQL. When your bot is in a handful of servers, SQLite will happily handle it.
The one thing to watch out for is that the default SQLite libraries are synchronous, and a Discord bot runs on an event loop. A blocking call freezes the whole bot while it runs. So pick a library that fits your runtime.
Step 1: Install the right library
For Python with discord.py, use aiosqlite, which gives you async access so queries don't block the loop.
pip install aiosqliteFor Node with discord.js, better-sqlite3 is the popular choice. It's synchronous, but its queries are fast enough that for a small bot the blocking time is tiny, and it has a clean API.
npm install better-sqlite3Step 2: Create a table
Before you store anything you need a table to store it in. A good habit is to run your table creation on startup with CREATE TABLE IF NOT EXISTS, so the table is built the first time and skipped after that.
Here's how that looks in Python. This sets up a connection and makes two tables, one for per-guild settings and one for per-user data.
import aiosqlite
DB_PATH = "bot.db"
async def setup_db():
async with aiosqlite.connect(DB_PATH) as db:
await db.execute("""
CREATE TABLE IF NOT EXISTS guild_settings (
guild_id INTEGER PRIMARY KEY,
prefix TEXT DEFAULT '!',
welcome_channel INTEGER
)
""")
await db.execute("""
CREATE TABLE IF NOT EXISTS user_data (
guild_id INTEGER,
user_id INTEGER,
xp INTEGER DEFAULT 0,
warnings INTEGER DEFAULT 0,
PRIMARY KEY (guild_id, user_id)
)
""")
await db.commit()Call setup_db() once when the bot is ready, for example inside setup_hook or your on_ready handler.
The same idea in Node with better-sqlite3:
const Database = require('better-sqlite3');
const db = new Database('bot.db');
db.exec(`
CREATE TABLE IF NOT EXISTS guild_settings (
guild_id TEXT PRIMARY KEY,
prefix TEXT DEFAULT '!',
welcome_channel TEXT
);
CREATE TABLE IF NOT EXISTS user_data (
guild_id TEXT,
user_id TEXT,
xp INTEGER DEFAULT 0,
warnings INTEGER DEFAULT 0,
PRIMARY KEY (guild_id, user_id)
);
`);One small note on Discord IDs. They're snowflakes, which can be larger than a normal 64-bit integer in some languages. In Node, store them as TEXT to be safe. In Python, native ints are fine.
Step 3: Run basic queries
With tables in place, you read and write with plain SQL. Always use parameter placeholders (the ? marks) and pass values separately. Never build SQL by gluing strings together, because that opens you up to injection and breaks on odd input.
Storing a per-guild prefix in Python:
async def set_prefix(guild_id: int, prefix: str):
async with aiosqlite.connect(DB_PATH) as db:
await db.execute("""
INSERT INTO guild_settings (guild_id, prefix)
VALUES (?,?)
ON CONFLICT(guild_id) DO UPDATE SET prefix = excluded.prefix
""", (guild_id, prefix))
await db.commit()
async def get_prefix(guild_id: int) -> str:
async with aiosqlite.connect(DB_PATH) as db:
async with db.execute(
"SELECT prefix FROM guild_settings WHERE guild_id =?",
(guild_id,)
) as cursor:
row = await cursor.fetchone()
return row[0] if row else "!"That ON CONFLICT... DO UPDATE is an upsert. It inserts a new row, or if one already exists for that guild, it updates instead. It saves you from writing a separate "does this row exist" check.
The same upsert in Node, plus reading a user's warning count:
function addWarning(guildId, userId) {
db.prepare(`
INSERT INTO user_data (guild_id, user_id, warnings)
VALUES (?,?, 1)
ON CONFLICT(guild_id, user_id) DO UPDATE SET warnings = warnings + 1
`).run(guildId, userId);
}
function getWarnings(guildId, userId) {
const row = db.prepare(
'SELECT warnings FROM user_data WHERE guild_id =? AND user_id =?'
).get(guildId, userId);
return row? row.warnings: 0;
}Notice how the warning increment happens inside the database with warnings + 1. You don't read the value, add one in your code, and write it back. Doing the math in the query avoids the race where two warnings land at the same time and one gets lost.
Per-guild settings and per-user data
The two tables above show the pattern most bots need. Guild settings are keyed on guild_id alone, one row per server, holding things like the prefix or the log channel. User data is keyed on the pair (guild_id, user_id), because the same person in two servers should have separate XP and warnings.
That composite primary key matters. If you only keyed on user_id, a user's data would bleed across every server the bot is in. Keying on both keeps each server's records isolated, which is almost always what you want.
When you need a leaderboard, the database does the heavy lifting:
SELECT user_id, xp
FROM user_data
WHERE guild_id =?
ORDER BY xp DESC
LIMIT 10;That returns the top ten in one go. No loading everything into memory, no manual sort.
When to move up to PostgreSQL or MySQL
SQLite is great until it isn't. The main limit is writes. SQLite locks the whole database file during a write, so only one write happens at a time. For a bot in a few dozen servers that's invisible. For a bot in thousands of servers with constant XP updates, those writes start lining up behind each other.
Move to a client/server database like PostgreSQL or MySQL when you hit one of these:
- The bot is on many servers and you see lots of concurrent writes.
- You want to run more than one process or shard against the same data.
- You need the database on a different machine from the bot.
- You want stronger tooling for backups, replication, and access control.
The nice part is that your SQL barely changes. Here's the Python switch to PostgreSQL using asyncpg, which is async and pools connections for you.
pip install asyncpgimport asyncpg
pool = None
async def setup_db():
global pool
pool = await asyncpg.create_pool(
host="127.0.0.1",
port=5432,
user="botuser",
password="change_me",
database="discordbot",
min_size=1,
max_size=10,
)
async with pool.acquire() as conn:
await conn.execute("""
CREATE TABLE IF NOT EXISTS user_data (
guild_id BIGINT,
user_id BIGINT,
xp INTEGER DEFAULT 0,
warnings INTEGER DEFAULT 0,
PRIMARY KEY (guild_id, user_id)
)
""")
async def add_xp(guild_id: int, user_id: int, amount: int):
async with pool.acquire() as conn:
await conn.execute("""
INSERT INTO user_data (guild_id, user_id, xp)
VALUES ($1, $2, $3)
ON CONFLICT (guild_id, user_id) DO UPDATE
SET xp = user_data.xp + $3
""", guild_id, user_id, amount)The biggest visible difference is placeholders. SQLite uses ?, while PostgreSQL uses numbered $1, $2 markers. MySQL with a driver like aiomysql goes back to %s style placeholders. The logic stays the same.
Connection handling and the event loop
A bot serves many events at once, so how you handle connections matters more than for a normal script. Two rules keep you out of trouble.
First, don't block the event loop. With aiosqlite and asyncpg you await every query, so the loop stays free while the database works. If you ever reach for a synchronous library inside an async bot, wrap the call so it runs off the main thread, for example with asyncio.to_thread. Otherwise a slow query stalls every command in the bot at once.
Second, use a connection pool for client/server databases. Opening a fresh PostgreSQL connection per query is slow and wasteful. A pool keeps a small set of connections open and hands them out as needed, which is exactly what asyncpg.create_pool gives you. For SQLite there's no separate server, so a pool isn't needed in the same way, though you should still avoid opening and closing a connection on every single query in a hot path.
Backing up the database
Data you can't restore is data you'll eventually lose. Set up a backup before you need one.
For SQLite, the safe way to copy a live database is the built-in backup command, which handles the file correctly even while the bot is running:
sqlite3 bot.db ".backup 'backup-$(date +%F).db'"For PostgreSQL, use pg_dump to write a restorable snapshot:
pg_dump -U botuser -h 127.0.0.1 discordbot > discordbot-$(date +%F).sqlThen schedule it. A simple cron entry that runs nightly at 4am looks like this:
0 4 * * * pg_dump -U botuser discordbot > /home/bot/backups/db-$(date +\%F).sqlIf you run your bot on a host with NVMe storage and snapshots, like the VPS plans we offer at Bytte.cloud, those backups are quick to take and quick to pull down. Wherever you host, keep at least one copy somewhere other than the same disk as the bot.
Troubleshooting
"database is locked" (SQLite). This is the classic SQLite error. It means another write was in progress when yours tried to start. The usual causes are holding a connection open too long or making writes from several places at once. Keep write transactions short, commit promptly, and don't share one connection across many concurrent tasks without care. If it keeps happening under load, that's your signal to move to PostgreSQL.
The bot freezes when a query runs. You're probably calling a synchronous database function on the event loop. Switch to an async library (aiosqlite or asyncpg), and make sure you actually await the calls. A forgotten await returns a coroutine that never runs, which looks like data silently not saving.
Data not saving at all. With SQLite you must commit after a write, or the change is thrown away when the connection closes. Check that every write path ends in a commit. In Node with better-sqlite3, writes commit automatically, so if data vanishes there, confirm you're opening the same database file and not a new one in a different working directory.
Changing a table later (migrations). Sooner or later you'll want a new column. Adding one is easy and safe:
ALTER TABLE user_data ADD COLUMN level INTEGER DEFAULT 0;Run that once. Because new columns get a default, existing rows fill in cleanly. For anything more involved, like renaming or splitting tables, keep your schema changes in numbered files (001_init.sql, 002_add_level.sql) and apply them in order. That way every copy of your database, including a fresh one for a teammate, ends up identical.
Snowflake IDs come back wrong in Node. If a stored ID doesn't match later, it was likely truncated as a JavaScript number. Store Discord IDs as TEXT, not INTEGER, and compare them as strings.
Where to go from here
You now have a bot that remembers things. Start with SQLite, create your tables on startup, use parameterized upserts, and always await your queries so the loop stays responsive. When write traffic grows or you want to split the bot across processes, lift the same SQL into PostgreSQL with a connection pool. Get a backup running early, test that you can actually restore it, and you'll be in good shape no matter how big the bot gets.
