Using your MySQL database

Every plan comes with one MySQL database (MariaDB 11.8) on the same machine as your server. Your bot reaches it over the machine's private network, so a query does not have to cross the internet.

Create it

1. Open your server in the control panel and go to Databases.
2. Press New Database. Type a Database Name, like bot. Leave Connections From as %.
3. Press the eye button on the new database. Database connection details shows the Endpoint (address and port), the Username and the Password. The database's full name starts with s and your server's number, like s31_bot.

Give your bot the details

Put them in a .env file in your server's top folder, not in your code (Startup settings explains why):

DB_HOST=172.18.255.250
DB_USER=u31_AbCdEfGhIj
DB_PASSWORD=the password from the panel
DB_NAME=s31_bot

Use the values your panel shows, not these.

Python (discord.py)

Add aiomysql and python-dotenv to requirements.txt. A pool keeps a few connections open and hands them out, which is what an async bot wants:

import os
import aiomysql
from dotenv import load_dotenv

load_dotenv()

async def make_pool():
    return await aiomysql.create_pool(
        host=os.environ["DB_HOST"], port=3306,
        user=os.environ["DB_USER"], password=os.environ["DB_PASSWORD"],
        db=os.environ["DB_NAME"], autocommit=True, maxsize=5, pool_recycle=300,
    )

async def add_point(pool, user_id: int):
    async with pool.acquire() as conn, conn.cursor() as cur:
        await cur.execute(
            "INSERT INTO points (user_id, total) VALUES (%s, 1) "
            "ON DUPLICATE KEY UPDATE total = total + 1",
            (user_id,),
        )

Create the pool once, in setup_hook, not in every command. Make the table once with CREATE TABLE IF NOT EXISTS points (user_id BIGINT UNSIGNED PRIMARY KEY, total INT NOT NULL).

JavaScript (discord.js)

Add mysql2 and dotenv to package.json:

require("dotenv").config();
const mysql = require("mysql2/promise");

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  connectionLimit: 5,
  supportBigNumbers: true,
  bigNumberStrings: true,
});

async function addPoint(userId) {
  await pool.execute(
    "INSERT INTO points (user_id, total) VALUES (?, 1) ON DUPLICATE KEY UPDATE total = total + 1",
    [userId],
  );
}

Keep Discord IDs as strings in JavaScript (bigNumberStrings does that for results): they are larger than a JavaScript number can hold exactly.

Java

Include org.mariadb.jdbc:mariadb-java-client in your jar. The panel's details show a ready JDBC Connection String; read it and the password from a file in your server's folder rather than writing them into your code. A pool such as HikariCP with a maximum of 5 connections suits a bot.

Limits

• One database per server, up to 1 GB. Over 1 GB, adding or changing data is paused and you get an email. Reading and deleting keep working, and writing comes back by itself once it is under 921 MB. TRUNCATE or DROP TABLE frees space straight away.
• Only reachable from servers here, not from your own computer. To see what is inside, query it from your bot, or download the nightly copy below.
• Up to 20 connections at a time. A pool of 5 is plenty for most bots.
• Scheduled events do not run. Use a timer in your bot, or a schedule in the panel.

Nightly copies and backups

At 04:00 (Vienna time) every night, a copy of your database is saved in your server's files as .snowservers/mysql/s31_bot.sql.gz, with your database's name. The daily backup half an hour later includes it, and you can download it from Files whenever you like. If saving the copy would take your disk past 90% full, it is skipped that night.

Restoring a backup brings back the copy file, not the database itself. To load a copy back into the database, ask us in Discord or by email.

Deleting a database in the panel deletes its data straight away. Download last night's copy first if you might want it.

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