init.js

const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const fs = require('fs');

// Define the database file path
const dbFilePath = './database.sqlite';

// Initialize the SQLite database
const db = new sqlite3.Database(dbFilePath, (err) => {
    if (err) {
        console.error('Error opening database:', err.message);
    } else {
        console.log('Connected to the SQLite database.');
        // Create tables if they don't already exist
        initializeDatabase();
    }
});

function initializeDatabase() {
    // Enable foreign key constraints in SQLite
    db.run("PRAGMA foreign_keys = ON;", (err) => {
        if (err) {
            console.error('Error enabling foreign keys:', err.message);
            return;
        }
    });

    // Read the schema from the schema.sql file
    const schemaFilePath = 'schema.sql';
    fs.readFile(schemaFilePath, 'utf8', (err, schemaSql) => {
        if (err) {
            console.error('Error reading schema file:', err.message);
            return;
        }

        // Execute the schema SQL
        db.serialize(() => {
            db.exec(schemaSql, (execErr) => {
                if (execErr) {
                    console.error('Error executing schema SQL:', execErr.message);
                } else {
                    console.log('Database schema initialized successfully.');
                }
            });
        });
    });
}