Javascript JSON Database

JSONDatabase.js

/**
 * Title    : JSON Database written in Javascript.
 * Version  : 1.0.0.0
 * Author   : Ernst Reidinga
 * Copyleft : 2022 (c) ERDesigns - Ernst Reidinga
 * License  : GPL
 */

// Import fs module
const fs = require('fs');

/**
 * Default values
 */
 const defaults = {
    uuid:    function () { return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, (c) => {var d = new Date().getTime(); var r = (d + Math.random() * 16) % 16 | 0; d = Math.floor(d / 16); return (c == 'x' ? r : (r & 0x3 | 0x8)).toString(16);}); },
    id:      function () { return 0 },
    text:    function () { return '' },
    integer: function () { return 0 },
    float:   function () { return 0.0 },
    boolean: function () { return false },
    date:    function () { return new Date() },
    time:    function () { return 0 },
    any:     function () { return undefined },
}

/**
 * Validator functions
 */
 const validators = {
    uuid:    function (value) { return /^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$/.test(value) },
    id:      function (value) { return Number.isInteger(value) },
    text:    function (value) { return Object.prototype.toString.call(value) === '[object String]' },
    integer: function (value) { return Number.isInteger(value) },
    float:   function (value) { return Number.isFinite(value) },
    boolean: function (value) { return value === true || value === false },
    date:    function (value) { return Object.prototype.toString.call(value) === '[object Date]' },
    time:    function (value) { return Number.isInteger(value) && (value >= 0 && value < 86400) },
    any:     function (value) { return true }
}

/**
 * Column data types
 */
const datatypes = [
    { id: 1, name: 'uuid', unique: true, locked: true, notNull: true, default: defaults.uuid, minLength: 32, maxLenght: 32, validator: validators.uuid, expected: 'String', defaultOnUpdate: false },
    { id: 2, name: 'id', unique: true, locked: true, notNull: true, default: defaults.id, minLength: -1, maxLength: -1, validator: validators.id , expected: 'Number', defaultOnUpdate: false },
    { id: 3, name: 'tinytext', unique: false, locked: false, notNull: true, default: defaults.text, minLength: 0, maxLenght: 255, validator: validators.text, expected: 'String', defaultOnUpdate: false },
    { id: 4, name: 'text', unique: false, locked: false, notNull: true, default: defaults.text, minLength: 0, maxLenght: 65535, validator: validators.text, expected: 'String', defaultOnUpdate: false },
    { id: 5, name: 'mediumtext', unique: false, locked: false, notNull: true, default: defaults.text, minLength: 0, maxLenght: 16777215, validator: validators.text, expected: 'String', defaultOnUpdate: false },
    { id: 6, name: 'longtext', unique: false, locked: false, notNull: true, default: defaults.text, minLength: 0, maxLenght: 4294967295, validator: validators.text, expected: 'String', defaultOnUpdate: false },
    { id: 7, name: 'integer', unique: false, locked: false, notNull: true, default: defaults.integer, minLength: Number.MIN_SAFE_INTEGER, maxLenght: Number.MAX_SAFE_INTEGER, validator: validators.integer, expected: 'Number', defaultOnUpdate: false },
    { id: 8, name: 'float', unique: false, locked: false, notNull: true, default: defaults.integer, minLength: Number.MIN_VALUE, maxLenght: Number.MAX_VALUE, validator: validators.float, expected: 'Number', defaultOnUpdate: false },
    { id: 9, name: 'boolean', unique: false, locked: false, notNull: true, default: defaults.boolean, minLength: -1, maxLenght: -1, validator: validators.boolean, expected: 'Boolean', defaultOnUpdate: false },
    { id: 10, name: 'date', unique: false, locked: false, notNull: true, default: defaults.date, minLength: -1, maxLenght: -1, validator: validators.date, expected: 'Date', defaultOnUpdate: false },
    { id: 11, name: 'time', unique: false, locked: false, notNull: true, default: defaults.time, minLength: -1, maxLenght: -1, validator: validators.time, expected: 'Time', defaultOnUpdate: false },
    { id: 12, name: 'any', unique: false, locked: false, notNull: false, default: defaults.any, minLength: -1, maxLenght: -1, validator: validators.any, expected: '*', defaultOnUpdate: false },
];

/**
 * Filter in place
 * @param {*} arr 
 * @param {*} condition 
 * @param {*} context 
 */
const filterInPlace = (arr, condition, context) => {
    let len = 0;
    arr.forEach((elem, index) => {
        if (condition.call(context, elem, index, arr)) {
            if (index !== len) {
                arr[len] = elem;
            }
            len++;
        }
    });
}

/**
 * Construct filename with directory
 * @param {String} directory 
 * @param {String} fn 
 */
const constructFilename = (directory, fn) => {
    return require('node:path').format({
        dir  : directory,
        base : fn
    });
}

/**
 * Assign options to new column
 * @param {String} type 
 * @param {Object} options 
 * @param {Object} column 
 */
const assignOptions = (type, options) => {
    const column = {...datatypes.find((t) => t.name == type)};
    if (options !== undefined) {
        Object.keys(datatypes.find((d) => d.name == type)).forEach((key) => {
            if (options[key] !== undefined) {
                column[key] = options[key];
            }
        });
    }
    return column;
}

/**
 * Validate column
 * @param {Array} columns 
 * @param {String} column 
 * @param {Array} rows 
 * @param {*} value 
 */
const validateColumn = (columns, column, rows, value) => {
    // Find column in columns
    const col = columns.find((c) => c.name === column);
    const datatype = datatypes.find((d) => d.id === col.id);
    // Check if the column is locked
    if (datatype.locked) {
        return 'Column is locked!';
    }
    // Check unique
    if (datatype.unique && rows.find((row) => row[i] === value)) {
        return 'Value is not unique!';
    }
    // Check null
    if (value === null && datatype.notNull) {
        return 'Value can\'t be null!';
    }
    // Validate value
    if (!datatype.validator(value)) {
        return `Value doen't match the columns datatype! Expected ${datatype.expected}`;
    }
    // Validate min length
    if (datatype.minLength > -1 && value.length < datatype.minLength) {
        return `Min length for this column is ${datatype.minLength} but value has a length of ${value.length}!`;
    }
    // Validate max length
    if (datatype.maxLength > -1 && value.length > datatype.maxLength) {
        return `Max length for this column is ${datatype.minLength} but value has a length of ${value.length}!`;
    }
    // Success
    return true;
}

/**
 * Stringify object including functions
 * @param {Object} obj 
 * @returns 
 */
const stringify = (obj) => {
    return JSON.stringify(obj, (key, value) => {
        if (typeof value === 'function') {
            return value.toString();
        }
        return value;
    });
}

/**
 * Parse stringified JSON including functions
 * @param {String} str 
 * @returns 
 */
const parse = (str) => {
    return JSON.parse(str, (key, value) => {
        if (typeof value === 'string' && value.indexOf('function') !== -1) {
            var pstart = value.indexOf('('), pend = value.indexOf(')');
            var params = value.substring(pstart+1, pend);
            params = params.trim();
            var bstart = value.indexOf('{'), bend = value.lastIndexOf('}');
            var value = value.substring(bstart+1, bend);
            return Function(params, value);
        }
        return value;
    });
}

/**
 * Table Class
 */
class Table {

    // For saving to file
    #directory = '';
    #filename  = '';
    
    // Table name
    name = '';
    // Table columns
    #columns = [];
    // Table rows
    #rows = [];

    /**
     * Class constructor
     * @param {String} name 
     * @param {Object} options 
     */
    constructor(name, options) {
        if (!name || !name.length) {
            throw new Error('No table name defined!');
        }
        // Set Table name
        this.name = name;
        // Set filename
        if (options && options.filename) {
            this.#filename = options.filename;
        }
        // Directory where file will be stored
        if (options && options.directory) {
            this.#directory = options.directory;
        }
        // Add columns
        if (options && options.columns && Array.isArray(options.columns)) {
            options.columns.forEach((column, index) => {
                if (!column.type) {
                    throw new Error('No column type defined!');
                }
                this.insertColumn(column.type, column.index || parseInt(index), column.options, true);
            });
            // Write to file
            this.saveToFile();
        }
        // Add rows
        if (options && options.rows && Array.isArray(options.rows)) {
            this.insert(options.rows, true);
        }
    }

    /**
     * Write table to file
     * @param {String} filename 
     * @param {String} directory 
     */
    saveToFile(filename, directory) {
        // Set filename if not set
        if (!this.#filename || !this.#filename.length) {
            if (filename && filename.length) {
                this.#filename = filename;
            } else {
                this.#filename = `${this.name}.json`;
            }
        }
        // Set directory if not set
        if (!this.#directory || !this.#directory.length) {
            this.#directory = directory;
        }
        // Construct filename
        let fn = '';
        if (filename && filename.length) {
            fn = filename;
        }
        if (filename && filename.length && directory && directory.length) {
            fn = constructFilename(directory, filename)
        }
        fs.writeFileSync(fn && fn.length ? fn : constructFilename(this.#directory, this.#filename), stringify({
            name    : this.name,
            date    : Date.now(),
            columns : this.#columns,
            rows    : this.#rows
        }));
    }

    /**
     * Load table data from file
     * @param {String} filename 
     * @param {String} directory
     */
    loadFromFile(filename, directory) {
        // Set filename if not set
        if (!this.#filename || !this.#filename.length) {
            this.#filename = filename;
        }
        // Set directory if not set
        if (!this.#directory || !this.#directory.length) {
            this.#directory = directory;
        }
        // Check if file exists
        const fn = constructFilename(directory, filename);
        if (!fs.existsSync(fn)) {
            throw new Error(`File doesn\'t exist! ${fn}`);
        }
        // Read table data
        const table = parse(fs.readFileSync(fn, 'utf8'));
        // Set name if not set
        if (!this.name || !this.name.length) {
            this.name = table.name;
        }
        // Load columns
        this.#columns.length = 0;
        this.#columns.push(...table.columns);
        // Load rows
        this.#rows.length = 0;
        this.#rows.push(...table.rows);
        // Convert values (e.g. Create Date object from date column, parse Integer and Float)
        this.#rows.forEach((r) => {
            r.forEach((column, index, row) => {
                // Integer
                if (index === 2 || index === 7) {
                    row[index] = parseInt(column);
                }
                // Float
                if (index === 8) {
                    row[index] = parseFloat(column);
                }
                // Date
                if (index === 10) {
                    row[index] = new Date(column);
                }
            })
        });
    }

    /**
     * Insert column
     * @param {String} type 
     * @param {Number} index 
     * @param {Object} options 
     * @param {Boolean} waitWriteTable
     */
    insertColumn(type, index, options, waitWriteTable) {
        // Check valid datatype
        if (!datatypes.map((t) => t.name).includes(type)) {
            throw new Error(`Invalid data type! ${type}`);
        }
        // Check valid index
        if (!Number.isInteger(index)) {
            throw new Error(`Invalid column index! ${index}`);
        }
        // Create column and assign options
        const column = assignOptions(type, options);
        // Insert column in columns
        this.#columns.splice(index, 0, column);
        // Generate default value
        const def = column.default();
        // Add new column to rows with default value
        this.#rows.forEach((row) => row.splice(index, 0, def));
        // Write to file
        if (waitWriteTable !== true) {
            this.saveToFile();
        }
    }

    /**
     * Alter column
     * @param {Number} index 
     * @param {String} type 
     * @param {Object} options
     * @param {Boolean} waitWriteTable 
     */
    alterColumn(index, type, options, waitWriteTable) {
        if (!Number.isInteger(index) || index > this.#columns.length || index < 0) {
            throw new Error(`Invalid column index! ${index}`);
        }
        // Check valid datatype
        if (!datatypes.map((t) => t.name).includes(type)) {
            throw new Error(`Invalid data type! ${type}`);
        }
        // Update column
        this.#columns[index] = assignOptions(type, options);
        // Generate default value
        const def = this.#columns[index].default();
        // Update rows and set default value
        this.#rows.forEach((row) => row[index] = def);
        // Write to file
        if (waitWriteTable !== true) {
            this.saveToFile();
        }
    }

    /**
     * Drop column
     * @param {Number} index 
     * @param {Boolean} waitWriteTable 
     */
    dropColumn(index, waitWriteTable) {
        if (!Number.isInteger(index) || index > this.#columns.length || index < 0) {
            throw new Error(`Invalid column index! ${type}`);
        }
        // Delete column from rows
        this.#rows.forEach((row) => row.splice(index, 1));
        // Write to file
        if (waitWriteTable !== true) {
            this.saveToFile();
        }
    }

    /**
     * Insert new row(s)
     * @param {Array|Object} rows
     */
    insert(rows) {
        // Array with column names
        const cols = this.#columns.map((col) => col.name);
        // Insert row function
        const insertRow = (row) => {
            // Check if there are columns that are not in this table
            const not = Object.keys(row).filter((col) => !cols.includes(col));
            if (not.length) {
                throw new Error(`Found columns that are not in this table!`, not);
            }
            // Check if the column values are valid
            Object.keys(row).map((column) => {
                let valid = validateColumn(this.#columns, column, this.#rows, row[column]);
                if (valid !== true) {
                    throw new Error(valid, column, row[column]);
                }
            });
            // Create new row
            const newRow = [];
            // Add column values
            this.#columns.map((col) => {
                if (Object.prototype.hasOwnProperty.call(row, col.name)) {
                    newRow.push(row[col.name]);
                } else {
                    newRow.push(col.default());
                }
            });
            // Add row to the table
            this.#rows.push(newRow);
        }
        // Insert row(s)
        if (Array.isArray(rows)) {
            rows.forEach((row) => insertRow(row));
        } else {
            insertRow(rows);
        }
        // Write to file
        this.saveToFile();
    }

    /**
     * Update values in row(s) that match condition
     * @param {Object} values 
     * @param {Function} condition 
     */
    update(values, condition) {
        if (Object.prototype.toString.call(condition) !== '[object Function]') {
            throw new Error('Condition must be a function!');
        }
        // Array with column names
        const cols = this.#columns.map((col) => col.name);
        // Check if there are columns that are not in this table
        const not = Object.keys(values).filter((col) => !cols.includes(col));
        if (not.length) {
            throw new Error(`Found columns that are not in this table! ${not}`);
        }
        // Check if the column values are valid
        Object.keys(values).map((column) => {
            let valid = validateColumn(this.#columns, column, this.#rows, row[column]);
            if (valid !== true) {
                throw new Error(valid, column, values[column]);
            }
        });
        // Update row function
        const updateRow = (row) => {
            Object.keys(values).map((column) => {
                row[column] = values[column];
            });
        }
        // Update rows that match the condition
        this.#rows.filter(condition).map((rows) => {
            if (Array.isArray(rows)) {
                rows.forEach((row) => updateRow(row));
            } else {
                updateRow(rows);
            }
        });
        // Write to file
        this.saveToFile();   
    }

    /**
     * Delete row(s)
     * @param {Fuction} condition 
     */
    delete(condition) {
        if (Object.prototype.toString.call(condition) !== '[object Function]') {
            throw new Error('Condition must be a function!');
        }
        // Filter rows
        filterInPlace(this.#rows, condition);
        // Write to file
        this.saveToFile();
    }

    /**
     * Select row(s)
     * @param {Array} columns 
     * @param {Function} condition 
     */
    select(columns, condition) {
        // Columns must be of type array
        if (!Array.isArray(columns)) {
            throw new Error('Columns must be a array');
        }
        // Check condition
        if (Object.prototype.toString.call(condition) !== '[object Function]') {
            throw new Error('Condition must be a function!');
        }
        // Array with column names
        const cols = this.#columns.map((col) => col.name);
        // Check if there are columns that are not in this table
        const not = Object.keys(columns).filter((col) => !cols.includes(col));
        if (not.length) {
            throw new Error(`Found columns that are not in this table!`, not);
        }
        // Find column indexes
        let colIndexes = [];
        columns.forEach((col) => {
            colIndexes.push({
                index : this.#columns.findIndex((c) => c.name === col), 
                name  : col
            });
        });
        // Select values
        const filtered = this.#rows.filter(condition);
        return filtered.map((row) => {
            let res = {};
            colIndexes.forEach((col) => {
                res[col.name] = row[col.index];
            });
            return res;
        });
    }

    /**
     * Columns in table
     * @returns Array
     */
    columns() {
        return this.#columns;
    }

    /**
     * Rows in table
     * @returns Array
     */
    rows() {
        return this.#rows;
    }

    /**
     * Return column count and row count
     */
    count() {
        return {
            columns : this.#columns.length,
            rows    : this.#rows.length
        }
    }

}

/**
 * Database Class
 */
class Database {
    
    // Database name
    name = '';

    // Database and tables directory
    #directory = '';
    // Database filename
    #filename = '';

    // Tables
    #tables = [];

    /**
     * Class constructor
     * @param {String} name 
     * @param {Object} options 
     */
    constructor(name, options) {
        if (!name || !name.length) {
            throw new Error('No database name defined!');
        }
        // Set Database name
        this.name = name;
        // Set filename
        if (options && options.filename) {
            this.#filename = options.filename;
        }
        // Directory where files will be stored
        if (options && options.directory) {
            this.#directory = options.directory;
        }
        // Open database?
        if (options && options.open === true) {
            this.loadFromFile(this.#filename, this.#directory);
        }
    }

    /**
     * Save database and tables to file
     * @param {String} filename 
     * @param {String} directory 
     */
    saveToFile(filename, directory) {
        // Set filename if not set
        if (!this.#filename || !this.#filename.length) {
            if (filename && filename.length) {
                this.#filename = filename;
            } else {
                this.#filename = `${this.name}.json`;
            }
        }
        // Set directory if not set
        if (!this.#directory || !this.#directory.length) {
            this.#directory = directory;
        }
        // Construct filename
        let fn = '';
        if (filename && filename.length) {
            fn = filename;
        }
        if (filename && filename.length && directory && directory.length) {
            fn = constructFilename(directory, filename)
        }
        fs.writeFileSync(fn && fn.length ? fn : constructFilename(this.#directory, this.#filename), stringify({
            name    : this.name,
            date    : Date.now(),
            tables  : this.#tables.map((table) => {
                return {
                    name     : table.name,
                    filename : table.filename
                }
            })
        }));
        this.#tables.forEach((table) => {
            table.table.saveToFile(table.filename, this.#directory);
        });
    }

    /**
     * Load database and tables from file
     * @param {String} filename 
     * @param {String} directory 
     */
    loadFromFile(filename, directory) {
        // Set filename if not set
        if (!this.#filename || !this.#filename.length) {
            this.#filename = filename;
        }
        // Set directory if not set
        if (!this.#directory || !this.#directory.length) {
            this.#directory = directory;
        }
        // Check if file exists
        const fn = constructFilename(directory, filename);
        if (!fs.existsSync(fn)) {
            throw new Error(`File doesn't exist! ${fn}`);
        }
        // Read database data
        const database = parse(fs.readFileSync(fn, 'utf8'));
        // Set name if not set
        if (!this.name || !this.name.length) {
            this.name = database.name;
        }
        // Load tables
        this.#tables = database.tables.map((table) => {
            return {
                name     : table.name,
                filename : table.filename,
                table    : new Table(table.name)
            }
        });
        this.#tables.forEach((table) => {
            table.table.loadFromFile(table.filename, this.#directory);
        });
    }

    /**
     * Create new table
     * @param {String} name 
     * @param {Object} options 
     */
    createTable(name, options) {
        // Check if table already exists
        const tableExists = this.#tables.findIndex((table) => table.name === name) > -1;
        if (tableExists) {
            throw new Error(`Table ${name} already exists! Please choose another name.`);
        }
        // Create and add new table
        this.#tables.push({
            name     : name,
            filename : `${name}.json`,
            table    : new Table(name, options)
        });
    }

    /**
     * Delete table with name
     * @param {String} name 
     */
    deleteTable(name) {
        // Check if table exists
        const tableIndex = this.#tables.findIndex((table) => table.name === name);
        if (tableIndex === -1) {
            throw new Error(`Table ${name} doesn't exist!`);
        }
        // Delete table file
        fs.unlinkSync(constructFilename(directory, this.#tables[tableIndex].filename));
        // Delete table
        this.#tables.splice(tableIndex, 1);
    }

    /**
     * List tables in this database
     */
    tables() {
        return this.#tables.map((table) => {
            const count = table.table.count();
            return {
                name     : table.name,
                filename : table.filename,
                columns  : count.columns,
                rows     : count.rows,
                table    : table.table
            }
        });
    }

}

/**
 * Exports
 */
exports.Table = Table;
exports.Database = Database;