read24 - Server: Administrating Students

July 24, 2020

Roger Ngo

I will begin by implementing the logic to administrate students first. To remind myself, here are the routes that will be needing some shiny new code:

GET /admin/classroom/:classroomId/students
      POST /admin/classroom/:classroomId/students
      PUT /admin/classroom/:classroomId/students
      GET /admin/classroom/:classroomId/students/:studentId
      DELETE /admin/classroom/:classroomId/students
      

Listing all Students by Classroom ID

Listing all the students by classroom is as simple as implementing a method in the Student resource class called listByClassroomId to find all the students with their classroomId property being the classroomId passed in.

public static async listByClassroomId(classroomId: number) {
    const studentTypes = await DatabaseConnector.select('students', (s: StudentType) => s.classroomId === classroomId) as StudentType[];

    return await Promise.all(studentTypes.map(async s => await new Student().load(s.id)));
}

Then, the route can just call this and return the JSON list.

app.get('/admin/classroom/:classroomId/students', async (req, res) => {
    const classroomId = parseInt(req.params.classroomId);

    const students = (await Student.listByClassroomId(classroomId)).map(s => s.json());

    return res.status(200).json(students);
});

Adding a Student to a Classroom

I will handle any access control checking at a later time. For now, I just want to be able to add an additional student to the database, given the classroom ID.

The approach to adding a student is to 1) Create the User, then 2) Create the Student tied to the user.

The request payload is assumed to have all the information necessary to create both objects in the database.

Create the User by hashing the given password, and including it in the User object with the username, and then calling the insert method.

const hashedObj = hashPassword(password);

const user = await (new User({
    username,
    salt: hashedObj.salt,
    password: hashedObj.hashed
})).insert();

Then user will have the new User which was created in the database. We can reuse the id found in this object to create the Student object.‌

But how do we do that? We currently don't know what ID is inserted to the database, but we can modify the database connector to return the latest ID after insert.

In sql.ts, modify the insert method to return the insertId from the results object.

public insert(tableName: string, data: DataRow): Promise<number> {
    const list = this._buildList(data);
    const values = [];
    for(const col in data) {
        values.push(data[col.toString()]);
    }

    const formatted = mysql.format(`INSERT INTO ${TableMapping[tableName]} SET ${list}`, values);

    const promise = new Promise<number>((resolve, reject) => {
        const query = connection.query(formatted, (err, results) => {
            if (err)
                return reject(err);

            console.log(query.sql);
            console.log('INSERT results', results);
            console.log('Last Insert ID', results.insertId);

            return resolve(results.insertId);
        });
    });

    return promise;
}

And then in base_resource.ts, modify insert to set the ID returned by the connector's insert method.

public async insert() {
    if (this.id > 0)
        throw new Error(`Cannot insert this row because it already has an ID value: ${this.id}.`);

    this.dateCreated = Date.now();

    const toSave = this.serializeForDb();

    const id = await DatabaseConnector.insert(this._tableName, toSave);

    this.id = id;

    return this;
}

So now, all resource classes will have the id set after insert when the insert method is invoked.

After the User is created, we can then access the id of the user when creating the Student.

const student = await (new Student({
    classroomId,
    firstName,
    middleName,
    lastName,
    grade: parseInt(grade, 10),
    userId: user.id
})).insert();

Update a Student

The precondition is that the studentId is included in the request body. Once the studentId is received, a Student instance can be loaded from the studentId. Then this instance can be updated from the rest of the data from the request body, and then saved with the update call.

app.put('/admin/classroom/:classroomId/students', async (req, res) => {
    const {
        studentId,
        firstName,
        lastName,
        middleName,
        grade    
    } = req.body;

    if(!studentId)
        return res.status(404).json({message: 'Not found'});

    const student = await new Student().load(studentId);

    student.firstName = firstName;
    student.lastName = lastName;
    student.middleName = middleName;
    student.grade = parseInt(grade, 10);

    await student.update();

    return res.status(200).json(student.json());
});

Retrieve a Student

This is the easiest method to implement so far. Once a studentId is received, a simple Student object can be loaded from the studentId, and returned.

app.get('/admin/classroom/:classroomId/students/:studentId', async (req, res) => {
    const classroomId = parseInt(req.params.classroomId, 10);
    const studentId = parseInt(req.params.studentId, 10);

    const student = (await new Student().load(studentId)).json();

    if(student.classroomId !== classroomId)
        return res.status(404).send({message: 'Student not found'});        

    return res.status(200).json(student);
})

Deleting a Student

I actually do not have any functionality implemented to delete objects from the database (in-memory, or MySQL).

Before implementing the DELETE route, I will need to add a delete method in the database connector.

In db_connector.ts, let's define a new function signature to be required for implementation:

public abstract delete(tableName: string, whereFunc: (o: Partial<DataRow>) => boolean): Promise<number>;

Since now there is a new interface method to be implemented, both in-memory, and MySQL database connectors will need to explicitly implement the delete method.

First, for the in-memory version, the IDs of what is needing to be deleted gathered by the whereFunc will just be the IDs to be filtered out from in the array returned by db.data[tableName].

Then we take this array which has the data filtered out, and reassign it back to db.data[tableName].

public delete(tableName: string, whereFunc: (o: DataRow) => boolean): Promise<number> {
    const filteredIds = db.data[tableName].filter(whereFunc).map((f: DataRow) => f.id);

    db.data[tableName] = db.data[tableName].filter(
        (r: DataRow) => !filteredIds.find((f: DataRow) => f.id === r.id));

    return Promise.resolve(filteredIds.length);
}

For the MySQL version of the delete method, is to first select the rows needing to be deleted, then construct a list of IDs to build out the DELETE query.

public delete(tableName: string, whereFunc: (o: DataRow) => boolean): Promise<number> {
    const promise = new Promise<number>(async (resolve, reject) => {
        const selected = await this.select(tableName, whereFunc);

        if (selected.length === 0)
            return resolve(0);

        const ids = selected.map((r: DataRow) => r.id);
        const idGroup = `(${ids.join(', ')})`;

        const query = connection.query(`DELETE FROM ${TableMapping[tableName]} WHERE id IN ${idGroup}`, (err, results) => {
            if (err)
                return reject(err);

            console.log(query.sql);
            console.log(`DELETED IDS: ${idGroup} FROM ${tableName}`);

            return resolve(results.affectedRows);
        });
    });

    return promise;
}

Finally, in base_resource.ts, the delete method that the resource can call will call the delete method found in the current database connector, and return itself for method chaining.

public async delete() {
    if (this.id === 0)
        return this;

    await DatabaseConnector.delete(this._tableName, (o: DataRow) => o.id === this.id);

    return this;
}

After all this code, the implemented DELETE route is quite simple:

app.delete('/admin/classroom/:classroomId/students', async (req, res) => {
    const classroomId = req.params.classroomId;
    const studentId = parseInt(req.body.studentId);

    const deleted = (await new Student().load(studentId)).delete();

    return res.status(200).json({classroomId, studentId});
});

.env File

It's now time to do a little upgrade to the current development environment setup. I want to be able to switch between using the in-memory, and MySQL data sources easily.

The best way to do that for now is by the use of a .env file. I can specify a .env file containing the DATA_SOURCE specifying the type of database connector to use.

DATA_SOURCE=mysql

Then in code, this can be loaded by the dotenv npm package.

npm install --save dotenv
npm install --save-dev @types/dotenv

In index.ts, the .env parameters should be loaded immediately at the top of the file.

import * as dotenv from 'dotenv';
const config = dotenv.config();