server/src/scripts/init_db.js

import database from "better-sqlite3";
import dotenv from "dotenv";
import fs from "fs";
dotenv.config();
const db_path = process.env.DB_NAME;
console.log(db_path);
fs.unlink(db_path, (err) => {
    if (err) {
        console.error(err);
    }
    else {
        console.log(`deleted old database:\t${db_path}`);
        const db = database(db_path);
        db.exec(`
            create table users(
                id Text not null primary key,
                username varchar(40) UNIQUE NOT NULL,
                name varchar(40) NOT NULL,
                password varchar(20) NOT NULL
            );
        `);
        db.exec(`
            create table sessions(
                id Text not null primary key,
                expires_at INTEGER NOT NULL,
                user_id TEXT NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users(id)
            );
        `);
        db.exec(`
            create table chats(
                id Text not null primary key,
                member_id TEXT NOT NULL,
                FOREIGN KEY (member_id) REFERENCES users(id)
            );
        `);
        db.exec(`
            create table messages(
                id Text not null primary key,
                send_at INTEGER NOT NULL,
                message Text not null,
                send_from TEXT NOT NULL,
                chat_id TEXT NOT NULL,
                is_seen TINYINT DEFAULT 0,
                FOREIGN KEY (send_from) REFERENCES users(id),
                FOREIGN KEY (chat_id) REFERENCES users(id)
            );
        `);
    }
});