Storing data: files, SQLite and databases

Anything your bot writes into its folder stays there through restarts, crashes and plan changes. What you choose depends on how much data there is and how often it changes.

A JSON file

Fine for small settings, like a prefix per server. The catch is that a crash halfway through writing can leave a broken file. Write to a temporary file and then swap it in, which is atomic:

# Python
import json, os

def save(data, path="data.json"):
    tmp = path + ".tmp"
    with open(tmp, "w") as f:
        json.dump(data, f)
    os.replace(tmp, path)

// JavaScript
const fs = require("node:fs");

function save(data, path = "data.json") {
  fs.writeFileSync(path + ".tmp", JSON.stringify(data));
  fs.renameSync(path + ".tmp", path);
}

SQLite

The best choice for most bots. It is a real database kept in a single file, it is part of Python already (import sqlite3), and it handles a bot's traffic easily. For async bots, aiosqlite keeps database calls from blocking the event loop:

import aiosqlite

async def setup():
    async with aiosqlite.connect("bot.db") as db:
        await db.execute(
            "CREATE TABLE IF NOT EXISTS points (user_id INTEGER PRIMARY KEY, total INTEGER)"
        )
        await db.commit()

In JavaScript, better-sqlite3 is the usual choice. It installs a native module, and the build tools it needs are already on the server:

const Database = require("better-sqlite3");
const db = new Database("bot.db");
db.exec("CREATE TABLE IF NOT EXISTS points (user_id TEXT PRIMARY KEY, total INTEGER)");
db.prepare("INSERT INTO points VALUES (?, 1) ON CONFLICT(user_id) DO UPDATE SET total = total + 1").run(userId);

Keep the .db file in your server's folder, not in /tmp, so it survives restarts. Store Discord IDs as text in JavaScript: they are larger than a JavaScript number can hold exactly.

MySQL

Every plan includes a MySQL database on the same machine as your bot. Use it if your code or a bot you are running already expects MySQL or MariaDB, or if more than one of your servers needs the same data. Using your MySQL database covers creating it and connecting from Python, JavaScript and Java.

An outside database

If your bot uses MongoDB or Postgres, connect to one hosted elsewhere. Free tiers from MongoDB Atlas, Neon or Supabase are enough for most bots.

• Put the connection string in a .env file, not in your code. See Startup settings.
• If the provider asks which IP addresses may connect, the address is shown on your server's Network page.
• Pick a region in Europe if you can. Your bot runs in Vienna, and a database on another continent adds a delay to every query.

Data and backups

Backups copy your whole server folder, database files included. Two things follow:

• Restoring a backup rolls your data back too. Restore last night's backup to undo a bad code change and you also lose everything your bot saved since then. Download the .db file first if that matters.
• A backup taken while SQLite is mid-write is usually fine, but the safest copy is one taken with the bot stopped.

The MySQL database is copied into your server's files every night before the backup runs, so backups carry a copy of it too. An outside database is not in our backups at all. Check what your provider keeps.

Data and Git deploy

If you deploy from GitHub, keep data files out of the repository: add *.db, data.json and similar to .gitignore. Files your bot creates that are not in the repository are never touched by an update. A data file that is committed and also changed by the bot will stop updates, because an update never overwrites a file edited on the server.

Something here wrong or out of date? Tell us and it gets fixed.