Skip to main content

Database Setup

MJ Bot uses Prisma ORM to interact with the database. This guide covers installing PostgreSQL on Ubuntu and configuring it for the bot.

Install PostgreSQL​

sudo apt update
sudo apt install postgresql postgresql-contrib -y

Verify the installation:

sudo systemctl status postgresql

Create a Database and User​

Switch to the postgres system user and open the PostgreSQL shell:

sudo -u postgres psql

Run the following SQL commands to create a dedicated user and database:

CREATE USER mjbot WITH PASSWORD 'your-secure-password';
CREATE DATABASE mjbot OWNER mjbot;
GRANT ALL PRIVILEGES ON DATABASE mjbot TO mjbot;
\q
warning

Replace your-secure-password with a strong, unique password. Do not use the example password in production.

Configure the Connection String​

Add the following to your bot/.env and dashboard/.env files:

DATABASE_URL=postgresql://mjbot:your-secure-password@localhost:5432/mjbot

The format is:

postgresql://USER:PASSWORD@HOST:PORT/DATABASE

Using MySQL Instead​

If you prefer MySQL, install it and use this connection string format:

DATABASE_URL=mysql://mjbot:your-secure-password@localhost:3306/mjbot
info

Make sure the provider field in your prisma/schema.prisma file matches your database. It should be set to postgresql or mysql accordingly.

Push the Schema​

From the bot directory, run Prisma to create the database tables:

cd bot
npx prisma db push

You should see output confirming that the schema was synced successfully.

Verify the Connection​

You can verify the database is set up correctly by running:

npx prisma studio

This opens a web-based database browser at http://localhost:5555 where you can inspect your tables.

tip

If you cannot connect, check the following:

  • PostgreSQL is running: sudo systemctl status postgresql
  • The username and password are correct
  • The database exists: sudo -u postgres psql -l
  • PostgreSQL is listening on localhost:5432: check /etc/postgresql/*/main/postgresql.conf