Swordbattle.io V1 Database Integration Guide
Overview
This documentation provides instructions for setting up a PostgreSQL database to work with the Swordbattle.io code. It outlines the steps to initialize the database and configure the environment so you have functionality like shop, accounts, leaderboards, etc locally.
Prerequisites
- A running PostgreSQL instance. (You can use Render as a free option)
- Administrative rights to create tables in the PostgreSQL instance.
- Running swordbattle.io application codebase.
Steps to Connect to the Database
1. Initialize the Database
Before you connect the application to the database, you need to initialize the required tables. Execute the following SQL queries to set up the necessary tables:
-- Table for account details
CREATE TABLE public.accounts (
skins jsonb NULL,
password text NOT NULL,
secret text NOT NULL,
email text NULL,
username text NOT NULL,
created_at timestamp without time zone NOT NULL DEFAULT now(),
id serial NOT NULL,
lastusernamechange timestamp(6) without time zone NULL
);
ALTER TABLE public.accounts ADD CONSTRAINT accounts_pkey PRIMARY KEY (id);
-- Table for game details
CREATE TABLE public.games (
"time" bigint NULL,
killerverified boolean NULL,
verified boolean NULL,
name text NULL,
killedby text NULL,
created_at timestamp without time zone NULL DEFAULT now(),
kills bigint NULL,
coins bigint NULL,
id serial NOT NULL
);
ALTER TABLE public.games ADD CONSTRAINT games_pkey PRIMARY KEY (id);
-- Table for game stats
CREATE TABLE public.stats (
id serial NOT NULL,
game_date date NOT NULL DEFAULT CURRENT_DATE,
username text NOT NULL,
game_time bigint NOT NULL DEFAULT 0,
game_count integer NOT NULL DEFAULT 0,
stabs integer NOT NULL DEFAULT 0,
coins integer NOT NULL DEFAULT 0
);
ALTER TABLE public.stats ADD CONSTRAINT stats_pkey PRIMARY KEY (id);
ALTER TABLE stats ADD CONSTRAINT unique_username UNIQUE (username, game_date);
-- Table for content details
CREATE TABLE public.content (
id serial NOT NULL,
created_at timestamp without time zone NOT NULL DEFAULT now(),
link text NULL,
title text NULL,
author text NULL,
chance integer NOT NULL DEFAULT 1,
source text NOT NULL DEFAULT 'youtube'::text,
label text NULL
);
ALTER TABLE public.content ADD CONSTRAINT content_pkey PRIMARY KEY (id);
2. Set Environment Variable
To enable the swordbattle.io application to connect to the PostgreSQL database, set the DATABASE_URL environment variable to your Postgres URI (in .env file):
DATABASE_URL=postgres://your_username:your_password@your_host:your_port/your_database_name
Replace the placeholders with your actual PostgreSQL connection details.
Note: It’s essential to keep this URI confidential, as it contains sensitive information.
Conclusion
After following the steps outlined above, the swordbattle.io application should be integrated with your PostgreSQL database. Remember to secure your database and application, ensure regular backups, and monitor for any anomalies or issues.
Feel free to make a new topic with the dev tag to ask any questions or report any bugs regarding this process.