Skip to main content

Database

🗄️ Database​

The bot stores everything in a database that is created automatically on first start. By default this is a local SQLite file, no setup required. You can optionally point the bot at an external MySQL/MariaDB or PostgreSQL database instead.

The backend is chosen with a single environment variable: DATABASE_URL.

Choosing a backend​

Set DATABASE_URL in your .env:

# Leave unset / empty → bundled SQLite file (data/tickets.db). Default.
# DATABASE_URL=""

# MySQL / MariaDB
DATABASE_URL="mysql://user:password@host:3306/ticketbot"

# PostgreSQL
DATABASE_URL="postgres://user:password@host:5432/ticketbot"

# SQLite at a custom path
DATABASE_URL="sqlite:./data/tickets.db"
BackendDATABASE_URLNotes
SQLite (default)unset or sqlite:./path.dbZero setup, a file is created automatically. Best for single-server self-hosting.
MySQL / MariaDBmysql://… or mariadb://…Default port 3306.
PostgreSQLpostgres://… or postgresql://…Default port 5432.

💾 No driver install needed. The MySQL (mysql2) and PostgreSQL (pg) drivers ship with the bot, a normal npm install covers all three backends.

🔐 Credentials belong in .env, never in config.jsonc. The config file is editable through the hosted dashboard and must not contain database passwords.

TLS / SSL​

For managed databases that require an encrypted connection, append ?ssl=true (or ?sslmode=require) to the URL:

DATABASE_URL="postgres://user:password@db.example.com:5432/ticketbot?ssl=true"

Migrating an existing SQLite database​

If you already ran the bot on SQLite and want to switch to MySQL or PostgreSQL, copy your existing data with the bundled migration script, your ticket history, ratings and statistics are preserved.

# Reads data/tickets.db, writes to the database in DATABASE_URL
npm run db:migrate

You can also pass the source and target explicitly:

node scripts/migrate-db.js --from ./data/tickets.db --to "postgres://user:pass@host:5432/ticketbot"

The script creates the target schema automatically, copies every table in foreign-key-safe order with the original primary keys, and (on PostgreSQL) resets the id sequences so newly created tickets don't collide.

⚠️ Safety guard. The migration aborts if the target database already contains data. Re-run with --force to write into a non-empty target anyway.

How it works​

  • The schema is identical across all three engines; only the column types differ internally. Timestamps are stored as integers and booleans as 0/1, so behaviour is the same regardless of the backend.
  • Missing columns are added automatically on start (inline migrations), so updating the bot never requires manual database steps.
  • Switching backends does not move your data automatically, use npm run db:migrate for that.

ℹ️ Existing SQLite users don't need to change anything. Without DATABASE_URL, the bot keeps using data/tickets.db exactly as before.