Data Models

The project is based on model objects that manage and handle object-type queries. There is a base object called DataModel that loads the models defined with Sequelize or Sequelize-Auto, providing a simple interface to interact with them. On the other hand, the Action class defines the actions that need to be performed in the database to achieve the desired impact.

To use this project, simply import DataModel into your models and the Action object into your actions. This way, you can utilize queries anywhere in the code while maintaining a clear and consistent structure.

As you progress through the project, more details will be provided about its functionality and best practices for its use.

Table of Contents

1. Installation
2. Usage
3. Acknowledgments

Installation

1. Clone this repository into your project and include it in your project.
2. Install the dependencies using: npm i or npm install.

Usage

Once you have integrated data-models into your project, you should import it as follows:

import { DataModel, actions } from '@bgroup/data-model/db';

Now that you have both objects, you can begin working with them. Below are the usages of each method for the Data Model:

• Connection to the database:

  const dbConnected = DataModel.connectDB(credentials);

  Where credentials has the following properties defined in the .env file:

  • dbName: Indicates the name of the database to connect to.
  • dbUser: Username.
  • dbPass: Password.
  • dbHost: Address of the database.
  • dbTimeZone: The time zone of the location (optional).
  • dialect: Database management system to use. Options: mysql, mariadb, sqlite, postgres, oracle.

• Models:

  const models = DataModel.models;

  Contains the models of the database.

• Sequelize: Use any functionality of Sequelize that you need. For example:

  const query = await DataModel.sequelize.query('
      SELECT * FROM user;
  ')

Now for actions:

• The DEFAULT property allows you to modify the default values for order, limit, and start (offset). It defaults to "timeCreated", 30, 0, respectively.

  Actions.DEFAULT = { order: 'timeCreated', limit: 30, start: 0 };

• The OPERATORS property returns a list of supported operators as keys in the object for queries.

  console.log(actions.OPERATORS);

• The op property allows the use of other Sequelize operators for custom queries.

• The processFilters function allows you to build filters for querying via Sequelize.

  const where = {
  	id: [10, 30, 40, 50],
  	date: {
  		between: ['2020-01-01', '2020-12-31'],
  	},
  	and: {
  		name: 'pedro',
  		quantity: {
  			gt: 10,
  		},
  		or: { status: 1 },
  	},
  };
  
  const filter = actions.processFilters(DataModel.models.Modelo, where);

• The list function allows you to list a query. For example:

  const params = {
  	order: 'id',
  	asc: 'ASC',
  	limit: 10,
  	start: 10,
  	attributes: ['id', 'name', 'quantity'],
  	where: {
  		id: [10, 30, 40, 50],
  		date: {
  			between: ['2020-01-01', '2020-12-31'],
  		},
  		and: {
  			name: 'pedro',
  			quantity: {
  				gt: 10,
  			},
  			or: { status: 1 },
  		},
  	},
  };
  const results = await actions.list(model, params, target);

  Where model is the models contained in DataModel.models, target is the location where the error occurred, and params is distributed as follows:

  • limit: limit of elements, defaults to 30.
  • start: offset of the elements to retrieve, defaults to 0.
  • order: field to order by.
  • asc: boolean to indicate whether it is ascending or descending.
  • where: where format as in the previous example.
  • attributes: indicates the attributes to display as an array of strings.

• The data function retrieves information for a single record from the database using an id. For example:

  const params = {
  	id: 2,
  };
  const data = await actions.data(model, params, target);

  Where model is the models contained in DataModel.models, target is the location where the error occurred, and params is the id of the record.

• The remove function aims to delete a record by id.

  const params = {
  	id: 2,
  };
  const remove = await actions.remove(params, model, target);

  Where model is the models contained in DataModel.models, target is the location where the error occurred, and params is the id of the record.

• The publish function allows you to pass the attributes to update or create the record. If the id is included in the parameters, it updates the record; otherwise, it creates a new one. Example:

  const create = {
  	name: 'Tomas',
  	phone: '+xxxxxxxxxxxx',
  };
  
  const update = {
  	id: 1,
  	name: 'Pedro',
  };
  
  const publish = await actions.publish(model, create, target);
  
  const publish = await actions.publish(model, update, target);

• The bulkSave function allows for an upsert of a massive quantity.

  const params = [
  	{
  		name: 'Maria',
  	},
  	{
  		id: 2,
  		name: 'tomas',
  	},
  ];
  
  const saveAll = await actions.bulkSave(model, params, target);

Implementation of SQL Server Use Case:

const { DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_TIMEZONE } = process.env;

const config = {
	name: DB_NAME,
	user: DB_USER,
	password: DB_PASS,
	host: DB_HOST,
	timeZone: DB_TIMEZONE,
	dialect: 'mssql',
	dialectOptions: {
		options: {
			encrypt: false,
			trustServerCertificate: true,
		},
	},
	initModels,
};

export /*bundle*/ const DataModel = DM.get(config);

Acknowledgments

We want to thank all the individuals who have contributed to this project and the resources we have used.

• Moisés Rodriguez
• Jorge Contreras