Which database should your Discord bot use?

Almost every bot ends up storing something: each server's prefix, an economy balance, moderation warnings, the welcome channel. Anything kept in a variable is gone after the next restart, so you need a database.

The list looks long, but in practice there are four candidates: SQLite, PostgreSQL, MongoDB and Redis. What decides between them is how your bot writes and relates its data.

What criteria matter when choosing?#

Four questions settle most cases:

1. How many processes write to the database? A single-process bot differs from a bot with shards in separate processes, or a bot and a web dashboard writing together.
2. Does your data have relations? Users with items, items with trades, trades with history: that calls for tables and JOIN. Loose per-server settings do not.
3. What happens to the data on a deploy? If the database is a file inside the bot's folder, a deploy that replaces the folder can take the file with it. A database on a separate server has no such risk.
4. How much operations work will you take on? A file needs no server. A client/server database needs a host, credentials and a connection.

When is SQLite the best choice?#

SQLite is a database in a single file, with no server. In Node.js, the better-sqlite3 library is synchronous and simple. The examples in this article use ES modules ("type": "module" in package.json) and a recent Node.js version, as the current better-sqlite3 requires:

import Database from 'better-sqlite3';

const db = new Database('bot.db');
db.pragma('journal_mode = WAL');

db.exec(`
  CREATE TABLE IF NOT EXISTS guild_settings (
    guild_id TEXT PRIMARY KEY,
    prefix TEXT NOT NULL DEFAULT '!'
  )
`);

const setPrefix = db.prepare(`
  INSERT INTO guild_settings (guild_id, prefix) VALUES (?, ?)
  ON CONFLICT(guild_id) DO UPDATE SET prefix = excluded.prefix
`);
const getPrefix = db.prepare('SELECT prefix FROM guild_settings WHERE guild_id = ?');

setPrefix.run('123456789012345678', '?');
console.log(getPrefix.get('123456789012345678')?.prefix); // "?"

Pros: zero setup, fast, great for starting out, no network dependency.

Cons: the SQLite documentation says it supports only one writer at a time per database file, and advises against it when many computers access the same database over a network. If the file lives on a disk that disappears or is replaced on every deploy, you lose the data.

Use it when: you have a single process, the data fits in a file and you know where that file is kept durably.

When is PostgreSQL the best choice?#

PostgreSQL is a client/server relational database. It is built for many connections and simultaneous writes (with MVCC concurrency control), has transactions and supports queries across relations. It is the safest pick for economies, leaderboards, tickets and anything where losing or duplicating a record hurts. Read more in what is PostgreSQL.

With the pg library, use a Pool and create only one for the whole bot:

import pg from 'pg';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
// without this listener, an error on an idle connection crashes the process
pool.on('error', (err) => console.error('PostgreSQL pool error', err));

await pool.query(`
  CREATE TABLE IF NOT EXISTS balances (
    user_id TEXT PRIMARY KEY,
    coins BIGINT NOT NULL DEFAULT 0
  )
`);

// $1, $2: parameters prevent SQL injection
await pool.query(
  `INSERT INTO balances (user_id, coins) VALUES ($1, $2)
   ON CONFLICT (user_id) DO UPDATE SET coins = balances.coins + excluded.coins`,
  ['123456789012345678', 50],
);

const { rows } = await pool.query('SELECT coins FROM balances WHERE user_id = $1', ['123456789012345678']);
console.log(rows[0].coins);

Pros: transactions, integrity, scales across several processes, and the data lives outside the bot's folder.

Cons: it needs a database server and a connection string. For a five-command bot, that is more than necessary.

When does MongoDB make sense?#

MongoDB stores JSON-like documents with no fixed schema. It is handy when each record has different fields or the shape changes with every bot version, such as a player profile with varying items. More in what is MongoDB and when to use it.

import { MongoClient } from 'mongodb';

const client = new MongoClient(process.env.MONGODB_URI);
await client.connect();

const profiles = client.db('bot').collection('profiles');

await profiles.updateOne(
  { _id: '123456789012345678' },
  { $inc: { xp: 10 }, $setOnInsert: { createdAt: new Date() } },
  { upsert: true },
);

console.log(await profiles.findOne({ _id: '123456789012345678' }));

Pros: flexible modeling, atomic upsert and $inc.

Cons: with no schema it is easy to write inconsistent fields, and relations between collections are on you. If you use MongoDB Atlas's free cluster, know that it has a storage limit (indexes count) and pauses clusters that go a long time with no connections. Check the current values in Atlas's limits documentation.

Is Redis a database for a bot?#

Redis keeps data in memory (writing to disk is optional and configurable) and is very fast. It is a good fit for cooldowns, counters, queues and caching, but data you cannot lose belongs in a main database. See the difference in Redis as a cache vs as a database. Before adding Redis, ask whether an in-memory Map already works: for a single process, it usually does.

Which database for each scenario?#

When in doubt, start with SQLite if the bot is small and you know where the file is kept, or with PostgreSQL if you already know the bot will grow. Switching later is possible, but migration takes work.

What mistakes make a bot lose data?#

• Database inside the folder a deploy replaces. Confirm the file survives a new upload, or use an external database.
• A new connection per command. Create the client or Pool once at startup and reuse it.
• Concatenating text into SQL. Always use parameters (?, $1).
• Credentials in code. Put the connection string in an environment variable, as with your bot token.
• No copy of the data. Test restoring a copy before you need one.

How much the database adds to the bot's monthly bill is covered in how much it costs to host a bot.

How to do this on Square Cloud#

Once you have picked a database, the next question is where it runs. On Square Cloud, Hobby (from R$12.49 per month) hosts the bot but does not include database hosting. MongoDB, PostgreSQL, MySQL and Redis hosting starts on the Standard plan.

You create the database in the dashboard (name, memory and engine), in the same datacenter as your applications, with about 1 ms of average latency. The dashboard gives you the full connection URL and a certificate to download. TLS is mandatory, so the client has to trust the downloaded CA (on PostgreSQL and MongoDB it also presents the .pem certificate). Put the URL in the bot's environment variable (DATABASE_URL or MONGODB_URI, as in the examples above).

Each database is a single node with up to 512 connections. The minimum is 512 MB for Redis and 1024 MB for the other engines. You take manual snapshots whenever you want, and every plan that includes databases also has an automatic daily snapshot. A snapshot restores into the same database or a new one.

If your bot is small, SQLite on Hobby is still a valid option: the application's disk persists across restarts and deploys, and a new commit overwrites only the files in the ZIP, so keep the database file out of it. See Discord bot hosting and the plans.