14829952700

Committed 05 May 2025 05:51AM UTC coverage: 45.792% (-0.3%) from 46.084%

Build # 14829952700

Build Type

Pull #173

github

Committed by

web-flow

Commit Message

Merge 5b4971fe2 into 2d902472a

Pull Request Pull Request #173: feat(sqlQueryBuilder): add getSQLFromSequelize function

Run Details

302 of 774 branches covered (39.02%)

Branch coverage included in aggregate %.

3 of 13 new or added lines in 1 file covered. (23.08%)

29 existing lines in 1 file now uncovered.

819 of 1674 relevant lines covered (48.92%)

7.04 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

2.14

/src/utils/sqlQueryBuilder.js

export const getSQLFromSequelize = (() => {
  const SQLFromLogMatcher = /^Executing\s+\([^)]*\):\s+(.*)$/;
  class _DummyError extends Error {
    constructor(message = '', ...args) {
      super(message, ...args);
    }
  }

  /**
   * Generates the SQL query string that Sequelize would use for a specific query.
   * WARNING: This is a hacky way to capture the SQL string generated by Sequelize, but it
   * not might be a 1:1 match with the actual SQL executed on the database (I've found a source
   * stating this that I can't find right now). Use with caution and track if in the future
   * such feature is added to Sequelize.
   * TODO: Investigate as well the move to another ORM like Prisma, that could better allow this.
   *
   * @param {import('sequelize').ModelStatic<import('sequelize').Model>} model - The sequelize model against which the query is executed.
   * @param {keyof import('sequelize').ModelStatic<import('sequelize').Model>} action - The action to be performed (findAll, findOne, etc.).
   * @param {import('sequelize').FindOptions} options - Options for the query
   * @returns {Promise<string | undefined>} - The SQL string generated by Sequelize.
   */
  return async function (model, action, ...options) {
    let sqlString;

    try {
      await model[action]({
        ...(options ?? {}),
        logging: (sql) => {
          const match = SQLFromLogMatcher.exec(sql);
          if (match) {
            sqlString = match[1];
            throw new _DummyError();
          }
        },
      });
    } catch (error) {
      if (error instanceof _DummyError && sqlString) {
        return sqlString;
      }
      console.error('Error while capturing Sequelize SQL:', error);
    }
  }
})();

/**
 * Genera una consulta SQL dinámica basada en los parámetros proporcionados.
 *
 * @param {Object} sql - Objeto con los parámetros para construir la consulta.
 * @param {Array<{func: string, attr: string}>} [sql.aggregations=[]] - Lista de agregaciones, cada una con su función (COUNT, AVG, etc.) y el atributo correspondiente.
 * @param {Array<string>} [sql.columns=[]] - Lista de columnas a seleccionar en la consulta.
 * @param {Array<{key: string, operator: string, value: any}>} [sql.whereConditions=[]] - Lista de condiciones para la cláusula WHERE, cada una con una clave, un operador y un valor.
 * @param {string} [sql.whereLogic='AND'] - Operador lógico que une las condiciones WHERE ('AND' o 'OR').
 * @param {string} [sql.groupBy] - Columna por la que se agruparán los resultados.
 * @param {string} [sql.orderByAttr] - Columna por la que se ordenarán los resultados.
 * @param {string} [sql.orderDirection='ASC'] - Dirección de orden (ASC o DESC).
 * @param {string} [sql.table='computation'] - Nombre de la tabla, por defecto 'computation'.
 *
 * @returns {string} - La consulta SQL generada.
 */

function createSQLQuery({
  aggregations = [],
  columns = [],
  whereConditions = [],
  whereLogic = 'AND',
  groupBy,
  orderByAttr,
  orderDirection,
  table = 'computation',
}) {
  let query = 'SELECT ';

  // Aggregations
  if (aggregations.length > 0) {
    query += aggregations
      .map((agg) => `${agg.func}(${agg.attr})`)
      .join(', ');
  }

  // Raw columns
  if (columns.length > 0) {
    if (aggregations.length > 0) query += ', ';
    query += columns.map((col) => `${col}`).join(', ');
  }

  if (aggregations.length === 0 && columns.length === 0) {
    query += '*';
  }

  query += ` FROM statusdb.${table}`;

  // WHERE
  if (whereConditions.length > 0) {
    const whereClause = whereConditions
      .map((cond) => {
        const value =
                    typeof cond.value === 'number' ||
                    cond.value === true ||
                    cond.value === false
                      ? cond.value
                      : `'${cond.value}'`;
        return `${cond.key} ${cond.operator} ${value}`;
      })
      .join(` ${whereLogic} `);
    query += ` WHERE (${whereClause})`;
  }

  // GROUP BY
  if (groupBy) {
    query += ` GROUP BY ${groupBy}`;
  }

  // ORDER BY
  if (orderByAttr) {
    const direction =
            orderDirection && orderDirection.toUpperCase() === 'DESC'
              ? 'DESC'
              : 'ASC';
    query += ` ORDER BY ${orderByAttr} ${direction}`;
  }

  return query;
}

function parseSQLQuery(query) {
  const result = {
    aggregations: [],
    columns: [],
    whereConditions: [],
    whereLogic: 'AND',
    groupBy: null,
    orderByAttr: null,
    orderDirection: null,
    table: 'computation',
  };

  // Table
  const tableMatch = query.match(/FROM\s+statusdb\.(\w+)/i);
  if (tableMatch) {
    result.table = tableMatch[1];
  }

  // Columns and Aggregations
  const selectMatch = query.match(/SELECT\s+(.+?)\s+FROM/i);
  if (selectMatch) {
    const selectFields = selectMatch[1].split(',');
    selectFields.forEach((field) => {
      field = field.trim();
      const aggMatch = field.match(/(\w+)\((\*)\)/); // ej: COUNT(*)
      if (aggMatch) {
        result.aggregations.push({
          func: aggMatch[1],
          attr: aggMatch[2],
        });
      } else {
        result.columns.push(field);
      }
    });
  }

  // WHERE
  const whereMatch = query.match(/WHERE\s+\((.+)\)/i);
  if (whereMatch) {
    const conditions = whereMatch[1].split(/\s+(AND|OR)\s+/i);
    if (conditions.length === 1) {
      const [key, operator, value] = conditions[0].split(
        /\s+(=|>|<|>=|<=|!=)\s+/
      );
      result.whereConditions.push({
        key: key.trim(),
        operator: operator.trim(),
        value: parseWhereValue(value.trim()),
      });
    } else {
      result.whereLogic = conditions[1].toUpperCase() || 'AND'; // AND or OR
      conditions.forEach((condition) => {
        if (condition !== 'AND' && condition !== 'OR') {
          const [key, operator, value] = condition.split(
            /\s+(=|>|<|>=|<=|!=)\s+/
          );
          result.whereConditions.push({
            key: key.trim(),
            operator: operator.trim(),
            value: parseWhereValue(value.trim()),
          });
        }
      });
    }
  }

  // GROUP BY
  const groupByMatch = query.match(/GROUP\s+BY\s+(\w+)/i);
  if (groupByMatch) {
    result.groupBy = groupByMatch[1];
  }

  // ORDER BY
  const orderByMatch = query.match(/ORDER\s+BY\s+(\w+)\s+(ASC|DESC)?/i);
  if (orderByMatch) {
    result.orderByAttr = orderByMatch[1];
    result.orderDirection = orderByMatch[2]
      ? orderByMatch[2].toUpperCase()
      : 'ASC';
  }

  return result;
}

function parseWhereValue(value) {
  if (value.toLowerCase() === 'true') {
    return true;
  }
  if (value.toLowerCase() === 'false') {
    return false;
  }
  if (!isNaN(value)) {
    return value;
  }
  return value.replace(/['"]/g, '');
}

export { createSQLQuery, parseSQLQuery };

/* EJEMPLOS
SELECT * FROM statusdb.computation
{}

SELECT id, date FROM statusdb.computation WHERE (id > '5' AND limit < '5')
{
    "columns": ["id", "date"],
    "whereConditions": [
        { "key": "id", "operator": ">", "value": 5 },
        { "key": "limit", "operator": "<", "value": 5 }
    ],
    "whereLogic": "AND"
}

SELECT COUNT(limit), MAX(date) FROM statusdb.computation
{
    "aggregations": [
        { "func": "COUNT", "attr": "limit" },
        { "func": "MAX", "attr": "date" }
    ]
}

SELECT COUNT(limit) FROM statusdb.computation WHERE (available != '5' OR active = '1') GROUP BY limit
{
    "aggregations": [
        { "func": "COUNT", "attr": "limit" }
    ],
    "whereConditions": [
        { "key": "available", "operator": "!=", "value": "5" },
        { "key": "active", "operator": "=", "value": "1" }
    ],
    "whereLogic": "OR",
    "groupBy": "limit"
}

SELECT id FROM statusdb.computation ORDER BY limit ASC
{
    "columns": ["id"],
    "orderByAttr": "limit",
    "orderDirection": "ASC"
}

SELECT COUNT(limit), id FROM statusdb.computation WHERE (id > '5' AND limit <= '10')
{
    "columns": ["id"],
    "aggregations": [
        { "func": "COUNT", "attr": "limit" }
    ],
    "whereConditions": [
        { "key": "id", "operator": ">", "value": 5 },
        { "key": "limit", "operator": "<=", "value": 10 }
    ],
    "whereLogic": "AND"
}
*/

1	export const getSQLFromSequelize = (() => {	21✔
2	const SQLFromLogMatcher = /^Executing\s+\([^)]\):\s+(.)$/;	21✔
3	class _DummyError extends Error {
4	constructor(message = '', ...args) {	×
NEW 5	super(message, ...args);	×
6	}
7	}
8
9	/**
10	* Generates the SQL query string that Sequelize would use for a specific query.
11	* WARNING: This is a hacky way to capture the SQL string generated by Sequelize, but it
12	* not might be a 1:1 match with the actual SQL executed on the database (I've found a source
13	* stating this that I can't find right now). Use with caution and track if in the future
14	* such feature is added to Sequelize.
15	* TODO: Investigate as well the move to another ORM like Prisma, that could better allow this.
16	*
17	* @param {import('sequelize').ModelStatic<import('sequelize').Model>} model - The sequelize model against which the query is executed.
18	* @param {keyof import('sequelize').ModelStatic<import('sequelize').Model>} action - The action to be performed (findAll, findOne, etc.).
19	* @param {import('sequelize').FindOptions} options - Options for the query
20	* @returns {Promise<string \| undefined>} - The SQL string generated by Sequelize.
21	*/
22	return async function (model, action, ...options) {	21✔
23	let sqlString;
24
NEW 25	try {	×
NEW 26	await model[action]({	×
27	...(options ?? {}),	×
28	logging: (sql) => {
NEW 29	const match = SQLFromLogMatcher.exec(sql);	×
NEW 30	if (match) {	×
NEW 31	sqlString = match[1];	×
NEW 32	throw new _DummyError();	×
33	}
34	},
35	});
36	} catch (error) {
NEW 37	if (error instanceof _DummyError && sqlString) {	×
NEW 38	return sqlString;	×
39	}
NEW 40	console.error('Error while capturing Sequelize SQL:', error);	×
41	}
42	}
43	})();
44
45	/**
46	* Genera una consulta SQL dinámica basada en los parámetros proporcionados.
47	*
48	* @param {Object} sql - Objeto con los parámetros para construir la consulta.
49	* @param {Array<{func: string, attr: string}>} [sql.aggregations=[]] - Lista de agregaciones, cada una con su función (COUNT, AVG, etc.) y el atributo correspondiente.
50	* @param {Array<string>} [sql.columns=[]] - Lista de columnas a seleccionar en la consulta.
51	* @param {Array<{key: string, operator: string, value: any}>} [sql.whereConditions=[]] - Lista de condiciones para la cláusula WHERE, cada una con una clave, un operador y un valor.
52	* @param {string} [sql.whereLogic='AND'] - Operador lógico que une las condiciones WHERE ('AND' o 'OR').
53	* @param {string} [sql.groupBy] - Columna por la que se agruparán los resultados.
54	* @param {string} [sql.orderByAttr] - Columna por la que se ordenarán los resultados.
55	* @param {string} [sql.orderDirection='ASC'] - Dirección de orden (ASC o DESC).
56	* @param {string} [sql.table='computation'] - Nombre de la tabla, por defecto 'computation'.
57	*
58	* @returns {string} - La consulta SQL generada.
59	*/
60
61	function createSQLQuery({
62	aggregations = [],	×
63	columns = [],	×
64	whereConditions = [],	×
65	whereLogic = 'AND',	×
66	groupBy,
67	orderByAttr,
68	orderDirection,
69	table = 'computation',	×
70	}) {
UNCOV 71	let query = 'SELECT ';	×
72
73	// Aggregations
74	if (aggregations.length > 0) {	×
75	query += aggregations	×
UNCOV 76	.map((agg) => `${agg.func}(${agg.attr})`)	×
77	.join(', ');
78	}
79
80	// Raw columns
81	if (columns.length > 0) {	×
82	if (aggregations.length > 0) query += ', ';	×
UNCOV 83	query += columns.map((col) => `${col}`).join(', ');	×
84	}
85
86	if (aggregations.length === 0 && columns.length === 0) {	×
UNCOV 87	query += '*';	×
88	}
89
UNCOV 90	query += ` FROM statusdb.${table}`;	×
91
92	// WHERE
93	if (whereConditions.length > 0) {	×
UNCOV 94	const whereClause = whereConditions	×
95	.map((cond) => {
96	const value =
UNCOV 97	typeof cond.value === 'number' \|\|	×
98	cond.value === true \|\|
99	cond.value === false
100	? cond.value
101	: `'${cond.value}'`;
UNCOV 102	return `${cond.key} ${cond.operator} ${value}`;	×
103	})
104	.join(` ${whereLogic} `);
UNCOV 105	query += ` WHERE (${whereClause})`;	×
106	}
107
108	// GROUP BY
109	if (groupBy) {	×
UNCOV 110	query += ` GROUP BY ${groupBy}`;	×
111	}
112
113	// ORDER BY
UNCOV 114	if (orderByAttr) {	×
115	const direction =
UNCOV 116	orderDirection && orderDirection.toUpperCase() === 'DESC'	×
117	? 'DESC'
118	: 'ASC';
UNCOV 119	query += ` ORDER BY ${orderByAttr} ${direction}`;	×
120	}
121
UNCOV 122	return query;	×
123	}
124
125	function parseSQLQuery(query) {
UNCOV 126	const result = {	×
127	aggregations: [],
128	columns: [],
129	whereConditions: [],
130	whereLogic: 'AND',
131	groupBy: null,
132	orderByAttr: null,
133	orderDirection: null,
134	table: 'computation',
135	};
136
137	// Table
138	const tableMatch = query.match(/FROM\s+statusdb\.(\w+)/i);	×
139	if (tableMatch) {	×
UNCOV 140	result.table = tableMatch[1];	×
141	}
142
143	// Columns and Aggregations
144	const selectMatch = query.match(/SELECT\s+(.+?)\s+FROM/i);	×
145	if (selectMatch) {	×
146	const selectFields = selectMatch[1].split(',');	×
147	selectFields.forEach((field) => {	×
148	field = field.trim();	×
149	const aggMatch = field.match(/(\w+)\((\)\)/); // ej: COUNT()	×
150	if (aggMatch) {	×
UNCOV 151	result.aggregations.push({	×
152	func: aggMatch[1],
153	attr: aggMatch[2],
154	});
155	} else {
UNCOV 156	result.columns.push(field);	×
157	}
158	});
159	}
160
161	// WHERE
162	const whereMatch = query.match(/WHERE\s+\((.+)\)/i);	×
163	if (whereMatch) {	×
164	const conditions = whereMatch[1].split(/\s+(AND\|OR)\s+/i);	×
165	if (conditions.length === 1) {	×
UNCOV 166	const [key, operator, value] = conditions[0].split(	×
167	/\s+(=\|>\|<\|>=\|<=\|!=)\s+/
168	);
UNCOV 169	result.whereConditions.push({	×
170	key: key.trim(),
171	operator: operator.trim(),
172	value: parseWhereValue(value.trim()),
173	});
174	} else {
175	result.whereLogic = conditions[1].toUpperCase() \|\| 'AND'; // AND or OR	×
176	conditions.forEach((condition) => {	×
177	if (condition !== 'AND' && condition !== 'OR') {	×
UNCOV 178	const [key, operator, value] = condition.split(	×
179	/\s+(=\|>\|<\|>=\|<=\|!=)\s+/
180	);
UNCOV 181	result.whereConditions.push({	×
182	key: key.trim(),
183	operator: operator.trim(),
184	value: parseWhereValue(value.trim()),
185	});
186	}
187	});
188	}
189	}
190
191	// GROUP BY
192	const groupByMatch = query.match(/GROUP\s+BY\s+(\w+)/i);	×
193	if (groupByMatch) {	×
UNCOV 194	result.groupBy = groupByMatch[1];	×
195	}
196
197	// ORDER BY
198	const orderByMatch = query.match(/ORDER\s+BY\s+(\w+)\s+(ASC\|DESC)?/i);	×
199	if (orderByMatch) {	×
200	result.orderByAttr = orderByMatch[1];	×
UNCOV 201	result.orderDirection = orderByMatch[2]	×
202	? orderByMatch[2].toUpperCase()
203	: 'ASC';
204	}
205
UNCOV 206	return result;	×
207	}
208
209	function parseWhereValue(value) {
210	if (value.toLowerCase() === 'true') {	×
UNCOV 211	return true;	×
212	}
213	if (value.toLowerCase() === 'false') {	×
UNCOV 214	return false;	×
215	}
216	if (!isNaN(value)) {	×
UNCOV 217	return value;	×
218	}
UNCOV 219	return value.replace(/['"]/g, '');	×
220	}
221
222	export { createSQLQuery, parseSQLQuery };
223
224	/* EJEMPLOS
225	SELECT * FROM statusdb.computation
226	{}
227
228	SELECT id, date FROM statusdb.computation WHERE (id > '5' AND limit < '5')
229	{
230	"columns": ["id", "date"],
231	"whereConditions": [
232	{ "key": "id", "operator": ">", "value": 5 },
233	{ "key": "limit", "operator": "<", "value": 5 }
234	],
235	"whereLogic": "AND"
236	}
237
238	SELECT COUNT(limit), MAX(date) FROM statusdb.computation
239	{
240	"aggregations": [
241	{ "func": "COUNT", "attr": "limit" },
242	{ "func": "MAX", "attr": "date" }
243	]
244	}
245
246	SELECT COUNT(limit) FROM statusdb.computation WHERE (available != '5' OR active = '1') GROUP BY limit
247	{
248	"aggregations": [
249	{ "func": "COUNT", "attr": "limit" }
250	],
251	"whereConditions": [
252	{ "key": "available", "operator": "!=", "value": "5" },
253	{ "key": "active", "operator": "=", "value": "1" }
254	],
255	"whereLogic": "OR",
256	"groupBy": "limit"
257	}
258
259	SELECT id FROM statusdb.computation ORDER BY limit ASC
260	{
261	"columns": ["id"],
262	"orderByAttr": "limit",
263	"orderDirection": "ASC"
264	}
265
266	SELECT COUNT(limit), id FROM statusdb.computation WHERE (id > '5' AND limit <= '10')
267	{
268	"columns": ["id"],
269	"aggregations": [
270	{ "func": "COUNT", "attr": "limit" }
271	],
272	"whereConditions": [
273	{ "key": "id", "operator": ">", "value": 5 },
274	{ "key": "limit", "operator": "<=", "value": 10 }
275	],
276	"whereLogic": "AND"
277	}
278	*/

statuscompliance / status-backend / 14829952700

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous