Working with Databases

npm install sequelize mysql2      # the ORM AND the driver — both are needed

// 1 — import the sequelize library
const Sequelize = require('sequelize');

// 2 — create a configured Sequelize object with our connection data
const sequelize = new Sequelize(
    'se371db',      // database name
    'se371',        // database user
    'se371pwd',     // user password
    {
        dialect: 'mysql',     // which database server — change this to switch DBMS
        host: 'localhost'     // a local db, so localhost
    }
);

// 3 — connect. authenticate() is async, so await it inside try/catch.
const connectToDB = async () => {
    try {
        await sequelize.authenticate();
        console.log(`Successfully connected to database server...`);
    } catch (error) {
        console.log(error);
    }
};

module.exports = { sequelize, connectToDB };

const db = require("../config/database");          // the connection object
const { DataTypes } = require('sequelize');       // the type definitions

const Employee = db.sequelize.define('Employee', {

    id: {
        type: DataTypes.INTEGER,
        primaryKey: true
    },
    // ↑ NOTE: a model named 'Employee' creates a table named 'employees'
    //   — Sequelize pluralises and lower-cases it automatically.

    name: {
        type: DataTypes.STRING,
        allowNull: false,          // a CONSTRAINT — enforced by the database
        validate: { max: 100 }      // a VALIDATION — enforced by Sequelize
    },

    position: {
        type: DataTypes.STRING,
        defaultValue: "Developer"
    }
});

// Apply the model to the actual database.
//   table missing  → it is CREATED
//   table exists   → changes are APPLIED to its structure
db.sequelize.sync({ alter: true });

module.exports = Employee;

app.post('/api/employees/v1/', async (request, response) => {
    const { id, name, position } = request.body;    // destructuring (ch. 4)

    const newEmployee = Employee.build({
        "id": id, "name": name, "position": position
    });

    try {
        await newEmployee.save();
        response.status(201).json(newEmployee);      // 201 = RESOURCE CREATED, not 200
    } catch (error) {
        response.json(error);
    }
});

// build() makes the object in memory; save() writes it to the database.
// Employee.create({...}) does both in one call.
//
// Testing it (slide 24): change the method to POST, set the URL, and send
// the test data as a JSON body. The 201 response comes back with the
// record plus metadata Sequelize added (createdAt, updatedAt).

// ── SELECT ALL ─────────────────────────────────────────────────
router.get('/api/employees/v1/', async (request, response) => {
    const employees = await Employee.findAll();
    response.status(200).json({ employees: employees });   // a JSON ARRAY
});

// ── SEARCH BY ID — findOne returns ONE object ──────────────────
router.get('/employees/v1/:id', async (request, response) => {
    try {
        const employee = await Employee.findOne({
            where: { id: request.params.id }         // req.params — chapter 6
        });
        response.status(200).json(employee);
    } catch (error) {
        response.json(error);
    }
});

// ── SEARCH BY CRITERIA — findAll returns an ARRAY ──────────────
router.get('/employees/v1/position/:position', async (request, response) => {
    try {
        const employee = await Employee.findAll({
            where: { position: request.params.position }
        });
        response.status(200).json(employee);
    } catch (error) {
        response.json(error);
    }
});

// ── PROJECTION — choose WHICH properties come back ─────────────
//    "some properties should stay secret"
router.get('/employees/v1/position/:position', async (request, response) => {
    try {
        const employee = await Employee.findAll({
            attributes: ['id', 'name'],           // ← the projection
            where: { position: request.params.position }
        });
        response.status(200).json(employee);
    } catch (error) {
        response.json(error);
    }
});

const { Op } = require('sequelize');   // MUST be imported — it defines the operations

// ── AND — slide 34 ─────────────────────────────────────────────
const employee = await Employee.findAll({
    where: {
        [Op.and]: [{ city: request.params.city },
                   { position: request.params.position }]
    }
});
//   the [Op.and] square brackets are a COMPUTED PROPERTY NAME — Op.and
//   is a symbol, not the literal text "Op.and".

// ── OR — slide 35 ──────────────────────────────────────────────
await Employee.findAll({
    where: {
        [Op.or]: [{ city: request.params.city },
                  { position: request.params.position }]
    }
});

// ── NOT EQUAL — slide 37. Note it nests INSIDE the column. ─────
await Employee.findAll({
    where: { city: { [Op.ne]: request.params.city } }     // ne = not equal
});

// ── LESS THAN — slide 38 ───────────────────────────────────────
await Employee.findAll({
    where: { age: { [Op.lt]: request.params.age } }       // lt / gt / lte / gte
});

// ── DELETE, with a WHERE — slide 36 ────────────────────────────
router.delete('/employees/v1/city/:city', async (request, response) => {
    try {
        const employee = await Employee.destroy({
            where: { city: request.params.city }
        });
        response.status(200).json(employee);   // returns the NUMBER of rows deleted
    } catch (error) {
        response.json(error);
    }
});

db.sequelize.sync({ alter: true })
    .then(async () => {
        Country.count()                    // how many records are there?
            .then(async (count) => {
                if (!count) {              // 0 is FALSY (chapter 4) → table empty
                    await Country.bulkCreate([
                        { name: "KSA" },
                        { name: "Oman" },
                        { name: "Egypt" }
                    ]);
                }
            });
    });

// The count() guard is the point: re-run this whenever tables are
// deleted and recreated, without duplicating the seed data.

const Employee = db.sequelize.define('Employee', { /* … */ });

// Every employee is associated with exactly one country
const Country = db.sequelize.define('Country', {
    name: {
        type: DataTypes.STRING,
        unique: true
    }
}, {
    timestamps: false     // no createdAt/updatedAt — static, non-critical data
});

// ── DECLARE THE RELATIONSHIP FROM BOTH SIDES ───────────────────
Country.hasMany(Employee);      // one country → many employees
Employee.belongsTo(Country);    // each employee → one country

// CONSEQUENCES:
//   • Employee gets the FOREIGN KEY, named CountryId  (Model + Id)
//   • Employee instances gain a getter:  .getCountry()

// ── USING IT — slide 41 ────────────────────────────────────────
router.get('/employees/v1/:id', async (request, response) => {
    try {
        const employee = await Employee.findOne({
            where: { id: request.params.id }
        });
        const country = await employee.getCountry();   // ← the enriched API

        response.status(200).json({ employee, country });
    } catch (error) {
        response.json(error);
    }
});

const User = sequelize.define('user', {
    username: {
        type: DataTypes.STRING,
        allowNull: false,           // ← CONSTRAINT: not null, at SQL level
        unique: true,               // ← CONSTRAINT: unique, at SQL level
    },
    hashedPassword: {
        type: DataTypes.STRING(64),
        validate: {
            is: /^[0-9a-f]{64}$/i,      // ← VALIDATION: enforced by Sequelize
        },
    },
});

// The regex is chapter 5 material: ^ start, $ end, {64} exactly 64,
// [0-9a-f] hex characters, /i case-insensitive.

// ── Using a validator — slide 45 ───────────────────────────────
const User = sequelize.define('user', {
    email: {
        type: DataTypes.STRING,
        validate: { isEmail: true }
    },
});

// Then in the controller:
const user = User.build({ username: 'Salah', email: 'salah' });
if (user.validate()) {
    try {
        await user.save();
    } catch (error) { /* … */ }
}