Build a JSON Database in Node.js (Part 2): Table Definition & Data Integrity

In database design, the domain integrity constraint states that all of the attributes of a relation must be declared upon a defined domain.

In other words, this means that whenever creating a new attribute in a relation, a certain number of properties must be defined in order to control the kind of data that can be stored in it, such as its:

• data type,
• length,
• acceptance or not of null values,
• set of allowable values,
• default value.

In this second part, we'll implement a method for defining database tables whose data structure will be enforced by validation schemas responsible for validating and sanitizing the data of objects before they are inserted or updated as records.

Then, we'll implement an ORM-like layer that will later on provide methods for performing CRUD operations on database tables.

Ready? Let's build!

Create a table definition method

Within the RowStackDB class, let's declare a define() method responsible for:

1. Creating new database tables.
2. Defining the structure of these tables.

// ...

export default class RowStackDB {
  // ...

  define(name, attributes) {
    //
  }
}

This method takes as parameters:

• name <string>: The table's name (e.g., 'users').
• attributes <Object>: The table's attributes (e.g., { email, username }).

💡 This method is in a way equivalent to the CREATE TABLE statement in SQL.

Create a new table

Within the define() method, let's:

1. Throw an error if the name parameter is not a non-empty string.
2. Create a new database table by adding a new property to the #tables private class field using the name parameter as key, and assign it an empty array ([]) that will be used to store the table's record objects.

// ...

export default class RowStackDB {
  // ...
  #tables;
  
  // ...

  define(name, attributes) {
    if (!isNonEmptyString(name)) {
      throw new Error(`RowStackDB: define(): "name" must be a string`);
    }

    if (!this.#tables[name]) {
      this.#tables[name] = [];
    }
  }
}

Enforce domain integrity

To enforce domain integrity on the records of a table, each attribute in the attributes object parameter will be defined according to the following properties:

{
  attribute: {
    type,
    allowNull?,
    values?,
    defaultValue?,
  }
}

Where:

• attribute <string>: The attribute's name (e.g., 'account_type').
• type <Function>: The attribute's Joi data type (e.g., Joi.boolean(), Joi.string()).
• allowNull? <boolean>: Whether the attribute accepts null as a value (e.g., true). Optional. Default: false.
• values? <Array[<any>]>: A list of accepted values (e.g., ['freemium', 'premium']). Optional.
• defaultValue? <any>: A default value if the attribute is undefined during the creation or update of a record (e.g., 'freemium'). Optional. If defaultValue is undefined, the attribute must be specified.

Upon execution of the define() method, the attributes object will be translated into a schema that will later on be used by another layer of the application to validate and sanitize data objects before they are inserted or updated in the table.

To do so, we'll use the joi package — a popular data validation library that helps developers define the shape and constraints of data, and check that any given object matches that shape before the application uses it.

For example, the following call to the define() method:

define('users', {
  username: {
    type: Joi.string()
  },
  fullName: {
    type: Joi.string(),
    allowNull: true
  },
  account: {
    type: Joi.any(),
    values: ['freemium', 'premium'],
    defaultValue: 'freemium'
  }
});

Will result into the creation of the following validation schema:

Joi.object({
  username: Joi.string().max(255).invalid(null).required(),
  fullName: Joi.string().max(255).allow(null).required(),
  account: Joi.any().valid('freemium', 'premium').invalid(null).default('freemium')
});

Import the Joi package

Let's start by importing the joi package into the engine.js module.

import * as path from 'node:path';
import * as fs from 'node:fs';
import Joi from 'joi';
import { isNonEmptyString, isPlainObject } from './utils.js';

// ...

Iterate on attributes

Within the define() method, let's throw an error if the attributes parameter is not a valid object with at least one property.

// ...

export default class RowStackDB {
  // ...

  define(name, attributes) {
    // ...

    if (!isPlainObject(attributes) || !Object.keys(attributes).length) {
      throw new Error(`RowStackDB: define(): "attributes" must be a non-empty object`);
    }
  }
}

Let's declare a variable named schema that will be used to aggregate the translated properties of the attributes parameter.

// ...

export default class RowStackDB {
  // ...

  define(name, attributes) {
    // ...

    let schema = {};
  }
}

For each attribute, let's:

1. Store the attribute's name into the attribute variable and its properties into the config variable.
2. Throw an error if config is not an object with at least one property.
3. Otherwise, declare a new rule variable that will be used to build its validation rule.

// ...

export default class RowStackDB {
  // ...

  define(name, attributes) {
    // ...

    let schema = {};

    for (const [attribute, config] of Object.entries(attributes)) {
      if (!isPlainObject(config) || !Object.keys(config).length) {
        throw new Error(`RowStackDB: define(): config for "${attribute}" must be a non-empty object`);
      }

      let rule;
    }
  }
}

Parse the attribute's type

Let's:

1. Throw an error if config.type is not a valid Joi data type.
2. Otherwise, store its value into the rule variable to which we'll chain other Joi validation rules.

// ...

export default class RowStackDB {
  // ...

  define(name, attributes) {
    // ...

    for (const [attribute, config] of Object.entries(attributes)) {
      // ...

      if (!Joi.isSchema(config.type)) {
        throw new Error(`RowStackDB: define(): "type" for "${attribute}" must be a Joi data type`);
      } else {
        rule = config.type;
      }
    }
  }
}

Parse the attribute's accepted values

Let's check if config.values is defined and:

1. Throw an error if it is not an array.
2. Otherwise, chain the Joi valid() method to the rule variable to only allow for the values it contains.

// ...

export default class RowStackDB {
  // ...

  define(name, attributes) {
    // ...

    for (const [attribute, config] of Object.entries(attributes)) {
      // ...

      if (config.values !== undefined) {
        if (!Array.isArray(config.values)) {
          throw new Error(`RowStackDB: define(): "values" of "${attribute}" must be an array`);
        }
        rule = rule.valid(...config.values);
      }
    }
  }
}

Parse the attribute's default value

Let's check if config.defaultValue is defined and:

1. Chain the Joi default() method to the rule variable to specify the attribute's default value upon record creation/update.
2. Otherwise, chain the Joi required() method to the rule variable to make the attribute's definition mandatory upon record creation/update.

// ...

export default class RowStackDB {
  // ...

  define(name, attributes) {
    // ...
    for (const [attribute, config] of Object.entries(attributes)) {
      // ...

      if (config.defaultValue !== undefined) {
        rule = rule.default(config.defaultValue);
      } else {
        rule = rule.required();
      }
    }
  }
}

👉 Since the value of config.defaultValue can be set to 0 or null, we have to explicitly check that it is not undefined instead of simply truthy.

Parse the attribute's nullable value

Let's check if config.allowNull is defined and:

1. Throw an error if it's not a boolean.
2. Chain the Joi allow() method to the rule variable to allow for null as a value if the property is set to true, and the Joi invalid() method otherwise.
3. Otherwise, chain the Joi invalid() method.

// ...

export default class RowStackDB {
  // ...

  define(name, attributes) {
    // ...
    for (const [attribute, config] of Object.entries(attributes)) {
      // ...
      
      if (config.allowNull !== undefined && typeof config.allowNull !== 'boolean') {
        throw new Error(`RowStackDB: define(): "allowNull" of "${attribute}" must be a boolean`);
      }
      
      if (config.allowNull) {
        rule = rule.allow(null);
      } else {
        rule = rule.invalid(null);
      }
    }
  }
}

Store the attribute's rule

Now that the validation chain is complete, let's store the rule variable into the schema variable under the attribute's key.

// ...

export default class RowStackDB {
  // ...

  define(name, attributes) {
    // ...
    let schema = {};

    for (const [attribute, config] of Object.entries(attributes)) {
      // ...
      schema[attribute] = rule;
    }
  }
}

Convert and store the validation schema

Within the constructor() method, let's initialize a new private class field named #schemas with an empty object literal ({}) that will be used to store the validation schemas of the database tables.

// ...

export default class RowStackDB {
  // ...
  #schemas;

  constructor(dbName, dirPath = 'data') {
    // ...
    this.#schemas = {};
  }

  // ...
}

Within the define() method, let's use the Joi.object() method to convert the schema object into a usable Joi validation schema and store it into the #schemas object using the table's name as key.

// ...

export default class RowStackDB {
  // ...
  #schemas;

  // ...

  define(name, attributes) {
    // ...
    this.#schemas[name] = Joi.object(schema);
  }
}

Create reusable data types

In order to improve the consistency and configurability of the validation schemas, let's create a new module named datatypes.js responsible for exporting a list of factory functions that wrap around the Joi API.

Within this file, let's:

1. Import the joi package.
2. Export an object whose properties are named after MySQL's most commonly used data types.

import Joi from 'joi';

export default {
  BOOLEAN: () => Joi.boolean(),
  INTEGER: () => Joi.number().integer(),
  FLOAT: (precision = 2) => {
    let rule = Joi.number();
    if (Number.isInteger(precision)) {
      rule = rule.precision(precision);
    }
    return rule;
  },
  VARCHAR: (size = 255, pattern = null) => {
    let rule = Joi.string();
    if (Number.isInteger(size)) {
      rule = rule.max(size);
    }
    if (pattern instanceof RegExp) {
      rule = rule.pattern(pattern);
    }
    return rule;
  },
  DATETIME: () => Joi.date().iso().custom(date => (date instanceof Date ? date.toISOString() : date)),
  ENUM: () => Joi.any()
};

Where:

• BOOLEAN <Function>: Returns a Joi boolean type.

• INTEGER <Function>: Returns a Joi integer number type.

• FLOAT <Function>: Returns a Joi floating point number type that takes an optional parameter where:

  • precision <integer>: The maximum number of decimal places.

• VARCHAR <Function>: Returns a Joi string type that takes optional parameters where:

  • size <integer>: The maximum number of characters.
  • pattern <RegExp>: A pattern rule.

• DATETIME <Function>: Returns a Joi date type and time expressed in the ISO format.

• ENUM <Function>: Returns a generic Joi type meant to be combined with values.

For example:

define('users', {
  username: {
    type: DataTypes.VARCHAR(8, new RegExp('^[a-zA-Z0-9-_]{1,8}$'))
  },
  fullName: {
    type: DataTypes.VARCHAR(100),
    allowNull: true
  },
  account: {
    type: DataTypes.ENUM(),
    values: ['freemium', 'premium'],
    defaultValue: 'freemium'
  }
});

Export the data types module

Within the "barrel" module, let's export the datatypes.js module as DataTypes.

export { default as RowStackDB } from './engine.js';
export { default as DataTypes } from './datatypes.js';

Create a model class

In ORM design, a model is an abstraction that maps to a single table in the database.

This layer is responsible for providing create, read, update, and delete (CRUD) methods to manipulate the table's data and enforcing domain integrity using its associated validation schema.

Separating the model from the database engine allows us to:

• Keep the database logic (file management, table creation) isolated from the data logic (validation, queries).
• Reuse the same model class for every table instead of re-implementing CRUD operations for each one.
• Write cleaner, more expressive code, for example, Products.find() instead of db.find('products').
• Improve maintainability and scalability, since each model operates independently of the others.

Create the model module

Let's create a new module named model.js, and within it, declare and export a class named Model.

export default class Model {
  //
}

Initialize the table and schema

Within the Model class, let's declare a constructor() method.

export default class Model {
  constructor(table, schema) {
    //
  }
}

This method takes as parameters:

• table <Array>: An array of records from the #tables private class field of the RowStackDB class.
• schema <Object>: A Joi validation schema from the #schemas private class field of the RowStackDB class.

Within this method, let's:

1. Import the Joi package.
2. Throw an error if the table parameter is not an array.
3. Throw an error if the schema parameter is not a Joi validation schema.
4. Otherwise, store these parameters into private class fields named #table and #schema.

import Joi from 'joi';

export default class Model {
  #table;
  #schema;

  constructor(table, schema) {
    if (!Array.isArray(table)) {
      throw new Error(`Model: constructor(): "table" must be an array`);
    } else if (!Joi.isSchema(schema)) {
      throw new Error(`Model: constructor(): "schema" must be a validation schema`);
    }

    this.#table = table;
    this.#schema = schema;
  }
}

Instantiate the model

The most practical way to expose the CRUD methods provided by the model to the end-user is to instantiate the Model class within the define() method of the RowStackDB class, and return its instance to the calling code.

This way, we'll have direct access to the underlying table's API when defining a table, for example:

const Users = database.define('users', { /* ... */ });

await Users.create(/* ... */);
const results = Users.read(/* ... */);
const updated = await Users.update(/* ... */);
const deleted = await Users.delete(/* ... */);

So, let's:

1. Import the model.js module into the engine.js module.
2. Within the define() method of the RowStackDB class, return a new instance of the Model class using as parameter the table and schema we've previously defined.

// ...
import Model from './model.js';

export default class RowStackDB {
  // ...

  define(name, attributes) {
    // ...

    return new Model(this.#tables[name], this.#schemas[name]);
  }
}

Conclusion

Congratulations!

You now have a database engine that can define database tables, structure them, and return a CRUD operation layer.

In the next part, you'll learn how to implement a method for inserting records into the database and persisting them to the disk.

Read next: Build a JSON Database in Node.js (Part 3): Insert & Persist Records