The road to robustness

When I was tasked to make a library management system, the first thing that came into my mind was “Seriously? It’s a cliche project!” but since it was the requirement for my college’s tech society - as a token of test, I decided to make it but not the usual way. Mostly, a library management system is primarily about basic CRUD operations, no place for handling edge cases like “What if two users try to borrow the same book in milliseconds of interval? Who will get the book then?” or “What if there is conflicting data in the database?” These questions made me think not just about CRUD but also about the architecture of the system.

After rigorous scribbles on paper, I’ve made the following database schema which I found to be robust enough to handle the edge cases.

Why this schema?

The schema prior to the current one was a mess, not because it couldn’t handle the edge cases but because it was not even able to handle the foreign key constraints properly. Notice the current schema, it implements soft deletes for users, inventory (which is an additional layer) between books and book loans, and the books table itself made independent and restricted any delete operation on it - it behaves like a central lobby or an attic of books where books are kept and never thrown away, only their status is updated. Why? because I wanted to build a system where the librarian simply adds list of ISBNs and the system automatically fetches the book details from the central repository of books (for speed) and if not found then from Google Books API and adds them to the database. this feature is not implemented yet but the books table acts as a central repository for books as of now.

The case of conflict

The simple case where user-1 tries to update his/her email but the provided email is already taken by some user-x, in that case I need to handle the conflicting case and prompt that the email is already taken by some user-x. To achieve this I came up with the idea of making a separate utility “conflict_handler” which would scan the database for conflicts before any write operation.

 javascript 
 import db, { pgp } from "../models/db.mjs";
// define conflicting data
/* A conflicting data is any data that matches already existing 
entity in the server on receiving the same from client. */

/*
    idea is to pass table_name and col_name into this function 
    as parameters and the function will check for the received_data into server_col
    if found match it will throw a 409 response code of "CONFLICT"
*/

/**
 * Checks if a record exists in the database.
 * Supports checking a single value or an array of values (bulk check).
 *
 * @param {string} options.schema - Name of the schema in database
 * @param {string} options.tableName - Name of the table in database
 * @param {string} options.colName - Name of the column in database
 * @param {any|any[]} options.value - The value (or array of values) to search for
 * @param {number|string} [options.excludeId] - (Optional) A user_id/uuid to exclude from the search.
 * @param {boolean} [options.returnRow=false] - If true, returns the full row object (or array of objects) instead of a boolean.
 * @returns {Promise<boolean|object|object[]|null>} Returns boolean by default, or the record object(s)/null if returnRow is true.
 */

export default async function recordExist({
  schema,
  tableName,
  colName,
  value,
  excludeID = null,
  returnRow = false,
}) {
  if (value === undefined || value === null) return false;

  // Always select * to check for deleted_at column availability in the result (if returned)
  // But for performance, if returnRow is false, we might want to optimize.
  // However, to filter 'deleted_at', we need to check it.
  const selectClause = "SELECT *";

  // Check if value is an array
  const isArrayValue = Array.isArray(value);

  // Set operator and value formatting based on whether it's an array or single value
  const operator = isArrayValue ? "IN" : "=";
  const valuePlaceholder = isArrayValue ? "($[value:csv])" : "$[value]";

  let query_template = `
        ${selectClause} FROM $[schema:name].$[tableName:name] WHERE $[colName:name] ${operator} ${valuePlaceholder}
    `;
  // core parameters of conflict_check
  const params = {
    schema: schema,
    tableName: tableName,
    colName: colName,
    value: value,
  };
  // if an exclusion is provided add it to the query template and make one more parameter named excludeID
  if (excludeID !== null) {
    query_template += ` AND user_id != $[excludeID]`;
    params.excludeID = excludeID;
  }

  try {
    // generate final query
    let formatted_query = pgp.as.format(query_template, params);

    // Use db.any to strictly avoid "Multiple rows unexpected" crash
    const rows = await db.any(formatted_query);

    // Filter out soft-deleted rows if 'deleted_at' column exists in the result
    const activeRows = rows.filter((row) => !row.deleted_at);

    if (isArrayValue) {
      // If returning rows, return active ones. If checking existence, return true if any active found.
      if (returnRow) return activeRows;
      return activeRows.length > 0;
    } else {
      // Single value check
      const record = activeRows.length > 0 ? activeRows[0] : null;
      if (returnRow) return record;
      return record !== null;
    }
  } catch (err) {
    console.log(
      `Error in checking conflicts from the server for Table: ${tableName}: `,
      err
    );
    return false;
  }
} 

This is the utility function that I created to handle the conflict resolution in the database. similarly the whole of the project is built with the same level of care and attention to detail.

Supported operations

You can explore more and can even play with it on litengine or check out its documentation on litengine docs or the code on github. That’s a wrap, there is more in it than I could write in a single blog post. Stay tuned :) Bye

Litengine - Robust library management

The road to robustness

Why this schema?

The case of conflict

Supported operations