Set Up and Reset the UniSierra Eats SQLite Database

UniSierra Eats stores all application data in a single SQLite file named unisierra_eats.db, created automatically in the project root the first time init_db.js runs. The script creates all four tables using CREATE TABLE IF NOT EXISTS and populates them with seed roles, an administrator account, and eight sample cafeteria products. Because every write uses either IF NOT EXISTS or INSERT OR IGNORE, you can safely re-run the script against an existing database without overwriting live data.

How the Server Connects

At startup, server.js opens the same database file with the sqlite3 package. The connection block is:

const db = new sqlite3.Database('./unisierra_eats.db', (err) => {
    if (err) {
        console.error('Error al conectar con la base de datos:', err.message);
    } else {
        console.log('Servidor conectado a la base de datos SQLite.');
    }
});

The path ./unisierra_eats.db is relative to the working directory from which node server.js is launched, so the server must always be started from the project root.

Initialization Steps

Ensure you are in the project root directory

All paths in both init_db.js and server.js are relative to the project root. Before running any command, confirm your terminal is in the right place:

pwd
# Expected: /path/to/Unisierra-eats

Run the initialization script

Execute init_db.js with Node.js to create the database file, build the schema, and insert seed data:

node init_db.js

Verify the output

A successful run prints confirmation messages to the console:

Conectado a la base de datos SQLite.
Tablas creadas correctamente.
Datos iniciales insertados.
Configuración de base de datos finalizada con éxito.

You should also see a new file unisierra_eats.db appear in the project root.

Start the application server

Once the database is ready, start the Express server:

node server.js

The server will print:

Servidor conectado a la base de datos SQLite.
Servidor de UniSierra Eats corriendo en http://localhost:3000

Running init_db.js against an existing unisierra_eats.db file is safe — CREATE TABLE IF NOT EXISTS and INSERT OR IGNORE ensure no existing tables or rows are overwritten. However, deleting unisierra_eats.db before re-running will permanently erase all users, products, and reviews that were created after the initial seed. Only delete the file if you intend a full reset back to factory defaults.

Resetting the Database

To wipe all data and start fresh, delete the database file and re-run the initialization script:

# From the project root
rm unisierra_eats.db
node init_db.js

After the reset, the database will contain only the two seeded roles, the single admin account, and the eight sample products. All student accounts, custom products, and reviews will be gone.

Full init_db.js Script

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

// 1. Conectar a la base de datos
// Esto creará un archivo llamado "unisierra_eats.db"
const db = new sqlite3.Database('./unisierra_eats.db', (err) => {
    if (err) {
        console.error('Error al abrir la base de datos:', err.message);
    } else {
        console.log('Conectado a la base de datos SQLite.');
    }
});

db.serialize(() => {
    // Habilitar el soporte para llaves foráneas en SQLite
    db.run("PRAGMA foreign_keys = ON;");

    // Crear tabla Roles
    db.run(`CREATE TABLE IF NOT EXISTS Roles (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        nombre TEXT UNIQUE NOT NULL
    )`);

    // Crear tabla Usuarios
    db.run(`CREATE TABLE IF NOT EXISTS Usuarios (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        nombre TEXT NOT NULL,
        correo TEXT UNIQUE NOT NULL,
        password TEXT NOT NULL,
        rol_id INTEGER,
        FOREIGN KEY (rol_id) REFERENCES Roles (id)
    )`);

    // Crear tabla Productos
    db.run(`CREATE TABLE IF NOT EXISTS Productos (
    id_producto INTEGER PRIMARY KEY AUTOINCREMENT,
    nombre TEXT NOT NULL,
    precio REAL NOT NULL,
    precioNivel TEXT NOT NULL,
    descripcion TEXT,
    imagen TEXT,
    calificacion REAL DEFAULT 0.0,
    numResenas INTEGER DEFAULT 0,
    categoria TEXT NOT NULL
    )`);

    // Crear tabla Reseñas
    db.run(`CREATE TABLE IF NOT EXISTS Resenas (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        usuario_id INTEGER NOT NULL,
        producto_id INTEGER NOT NULL,
        calificacion INTEGER NOT NULL CHECK(calificacion >= 1 AND calificacion <= 5),
        comentario TEXT,
        fecha DATETIME DEFAULT CURRENT_TIMESTAMP,
        estado TEXT DEFAULT 'activa',
        FOREIGN KEY (usuario_id) REFERENCES Usuarios (id),
        FOREIGN KEY (producto_id) REFERENCES Productos (id_producto)
    )`);

    console.log('Tablas creadas correctamente.');

    //Datos de prueba
    db.run(`INSERT OR IGNORE INTO Roles (id, nombre) VALUES (1, 'Administrador'), (2, 'Estudiante')`);
    
    db.run(`INSERT OR IGNORE INTO Usuarios (id, nombre, correo, password, rol_id) 
            VALUES (1, 'Admin Principal', 'admin@unisierra.edu.mx', 'admin123', 1)`);

    db.run(`INSERT OR IGNORE INTO Productos (nombre, precio, precioNivel, descripcion, imagen, categoria) 
            VALUES ('Torta de Asada', 65.00, '$$', 'Deliciosa torta de carne asada con aguacate, tomate y mayonesa. Perfecta para un gran apetito.', 'https://images.unsplash.com/photo-1615719413546-198b25453f85?auto=format&fit=crop&w=500&q=60', 'comidas'),
            ('Chilaquiles Rojos', 55.00, '$$', 'Chilaquiles tradicionales con queso, crema, cebolla y un huevo estrellado.', 'https://images.unsplash.com/photo-1640718879612-40156d6ba433?auto=format&fit=crop&w=500&q=60', 'comidas'),
            ('Café Americano', 20.00, '$', 'Café americano caliente recién hecho. Ideal para despertar antes de clases.', 'https://images.unsplash.com/photo-1550547660-d9450f859349?auto=format&fit=crop&w=500&q=60', 'bebidas'),
            ('Agua Fresca de Jamaica', 15.00, '$', 'Vaso grande de agua de jamaica 100% natural y refrescante.', 'https://images.unsplash.com/photo-1513558161293-cdaf765ed2fd?auto=format&fit=crop&w=500&q=60', 'bebidas'),
            ('Papas a la Francesa', 35.00, '$', 'Porción generosa de papas fritas crujientes con un toque de sal.', 'https://images.unsplash.com/photo-1576107232684-1279f390859f?auto=format&fit=crop&w=500&q=60', 'snacks'),
            ('Galleta con Chispas', 15.00, '$', 'Galleta grande recién horneada con deliciosas chispas de chocolate.', 'https://images.unsplash.com/photo-1499636136210-6f4ee915583e?auto=format&fit=crop&w=500&q=60', 'snacks'),
            ('Ensalada de Pollo', 75.00, '$$$', 'Mezcla de lechugas frescas, tomate, pepino y pechuga de pollo a la plancha.', 'https://images.unsplash.com/photo-1512621776951-a57141f2eefd?auto=format&fit=crop&w=500&q=60', 'sanas'),
            ('Coctel de Frutas', 40.00, '$', 'Fruta de temporada picada (melón, papaya, plátano y manzana) con un poco de miel y granola.', 'https://images.unsplash.com/photo-1490474418585-ba9bad8fd0ea?auto=format&fit=crop&w=500&q=60', 'sanas');`);

    console.log('Datos iniciales insertados.');
});

// Cierre de la conexión a la base de datos
db.close((err) => {
    if (err) {
        console.error('Error al cerrar la base de datos:', err.message);
    } else {
        console.log('Configuración de base de datos finalizada con éxito.');
    }
});

Seed Data Reference

Roles

Two roles are inserted with explicit IDs so the application can reference them by a known constant:

INSERT OR IGNORE INTO Roles (id, nombre) VALUES (1, 'Administrador'), (2, 'Estudiante');

id	nombre
1	Administrador
2	Estudiante

Administrator Account

A single admin user is seeded for first-time access. This account is assigned rol_id = 1 (Administrador):

INSERT OR IGNORE INTO Usuarios (id, nombre, correo, password, rol_id)
VALUES (1, 'Admin Principal', 'admin@unisierra.edu.mx', 'admin123', 1);

Sample Products

Eight products are inserted across the four menu categories. All are seeded without an explicit id_producto, so SQLite’s AUTOINCREMENT assigns IDs 1-8 in insertion order:

INSERT OR IGNORE INTO Productos (nombre, precio, precioNivel, descripcion, imagen, categoria)
VALUES
    ('Torta de Asada',         65.00, '$$',  '...', '...', 'comidas'),
    ('Chilaquiles Rojos',      55.00, '$$',  '...', '...', 'comidas'),
    ('Café Americano',         20.00, '$',   '...', '...', 'bebidas'),
    ('Agua Fresca de Jamaica', 15.00, '$',   '...', '...', 'bebidas'),
    ('Papas a la Francesa',    35.00, '$',   '...', '...', 'snacks'),
    ('Galleta con Chispas',    15.00, '$',   '...', '...', 'snacks'),
    ('Ensalada de Pollo',      75.00, '$$$', '...', '...', 'sanas'),
    ('Coctel de Frutas',       40.00, '$',   '...', '...', 'sanas');

To inspect the database interactively after initialization, use any SQLite client. Two popular options are:

DB Browser for SQLite — a graphical desktop app available at sqlitebrowser.org that lets you browse tables, run queries, and view raw data without writing SQL.
sqlite3 CLI — the official command-line shell. Run sqlite3 unisierra_eats.db from the project root, then use .tables to list tables and .schema TableName to inspect a definition.

Getting Started

Core Features

Admin Panel

Database

Set Up and Reset the UniSierra Eats SQLite Database

How the Server Connects

Initialization Steps

Resetting the Database

Full init_db.js Script

Seed Data Reference

Roles

Administrator Account

Sample Products

Build docs developers (and LLMs) love

Getting Started

Core Features

Admin Panel

Database

Documentation Index

​How the Server Connects

​Initialization Steps

​Resetting the Database

​Full init_db.js Script

​Seed Data Reference

​Roles

​Administrator Account

​Sample Products

Build docs developers (and LLMs) love

How the Server Connects

Initialization Steps

Resetting the Database

Full init_db.js Script

Seed Data Reference

Roles

Administrator Account

Sample Products