I am using MySQL, ExpressJS, and Sequelize to build a function that returns a list of columns from a given SQL query. The function should work even if the query returns no rows.
Here is the current implementation of my function:
async getColumnsFromQuery ( query ) {
try {
let queryResponse = await db.sequelize.query ( query, {
type: db.sequelize.QueryTypes.SELECT
});
let columns = [];
if ( queryResponse.length > 0 ) {
columns = Object.keys( queryResponse[ 0 ] );
}
return columns;
} catch ( err ) {
throw err;
}
}
This function works perfectly when the query returns rows, but if the query returns no rows, the columns array is empty. I need to ensure that the function still returns the correct column names even when no data is returned.
I tried using INFORMATION_SCHEMA , SHOW COLUMNS or DESCRIBE but they are only for tables not queries.
I think I was able to find a way to get all the column names from a query including the datatypes of columns.
I used node-sql-parser a library that parses SQL statements.
By using the result I got from parsing the query, I was able to get all the column names.
Here is how I did it:
const { Parser } = require('node-sql-parser');
// For SELECT TableName.* FROM ....
getAllColumnsFromAllTables (allTableAllColumns) {
const TablesFromDB = Object.keys(allTableAllColumns);
return TablesFromDB.map(dbTable => {
return allTableAllColumns[dbTable].columns;
});
}
// For SELECT TableName.* FROM ....
getAllColumnsFromATable(allTableAllColumns, tableAlias) {
const TablesFromDB = Object.keys(allTableAllColumns);
for ( let i = 0 ; i < TablesFromDB.length; i++ ) {
if (TablesFromDB[i].toLowerCase() === tableAlias[table].toLowerCase()) {
// let tableColumns = allTableAllColumns[TablesFromDB[i]].columns.map( column => `${table.as ? table.as : table.table}.${column}`)
return allTableAllColumns[TablesFromDB[i]].columns;
}
}
}
// For SELECT TableName.ColumnName FROM ....
getAColumnFromATable(allTableAllColumns, tableAlias, col) {
const TablesFromDB = Object.keys(allTableAllColumns);
for ( let i = 0 ; i < TablesFromDB.length; i++ ) {
if (TablesFromDB[i].toLowerCase() === tableAlias[table].toLowerCase()) {
for (let j = 0; j < allTableAllColumns[TablesFromDB[i]].columns.length; j++) {
const dbCol = allTableAllColumns[TablesFromDB[i]].columns[j];
if (dbCol.COLUMN_NAME.toLowerCase() === col.expr.column.toLowerCase()) {
return dbCol;
}
}
}
}
}
// For SELECT ColumnName FROM ....
getAColumnFromAllTables (allTableAllColumns, col) {
const TablesFromDB = Object.keys(allTableAllColumns);
for ( let i = 0 ; i < TablesFromDB.length; i++ ) {
for (let j = 0; j < allTableAllColumns[TablesFromDB[i]].columns.length; j++) {
const dbCol = allTableAllColumns[TablesFromDB[i]].columns[j];
if (dbCol.COLUMN_NAME.toLowerCase() === col.expr.column.toLowerCase()) {
return dbCol;
}
}
}
}
// Gets all columns of tables from database
async getAllColumnsOfTablesFromDB(tableNames) {
const allTableAllColumns = {};
var query = `
SELECT
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_SCALE
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME IN (:tableNames)
AND TABLE_SCHEMA = :DB_NAME;
`;
await db.sequelize.query(query, {
replacements: {
tableNames: tableNames,
DB_NAME: this.dbConfig.database
}
}).spread((results, metadata) => {
results.map(row => {
if (allTableAllColumns[row.TABLE_NAME] && allTableAllColumns[row.TABLE_NAME].columns) {
allTableAllColumns[row.TABLE_NAME].columns.push(row)
} else {
allTableAllColumns[row.TABLE_NAME] = {};
allTableAllColumns[row.TABLE_NAME].columns = [row]
}
});
});
return allTableAllColumns;
}
async getColumnNames(ast) {
let columns = [];
let tableAlias = {};
let tableNames = ast.from.map( t => t.table );
let allTableAllColumns = await this.getAllColumnsOfTablesFromDB(tableNames);
ast.from.forEach (table => {
if (table.as) {
tableAlias[table.as] = table.table
} else {
tableAlias[table.table] = table.table
}
});
ast.columns.forEach(col => {
if (col.expr.type === 'column_ref') {
const table = col.expr.table;
const column = col.expr.column === '*' ? '*' : `${table}.${col.expr.column}`;
if (!col.expr.table && col.expr.column === '*') {
// For SELECT * FROM ....
return columns.push(...this.getAllColumnsFromAllTables(allTableAllColumns));
} else if (col.expr.table && column === '*') {
// For SELECT TableName.* FROM ....
return columns.push(...this.getAllColumnsFromATable (allTableAllColumns, tableAlias));
} else if (col.expr.table && col.expr.column) {
// For SELECT TableName.ColumnName FROM ....
return columns.push(this.getAColumnFromATable(allTableAllColumns, tableAlias, col));
} else if (!col.expr.table && col.expr.column) {
// For SELECT ColumnName FROM ....
return columns.push(this.getAColumnFromAllTables(allTableAllColumns, col));
}
}
});
return columns;
}
async getColumnsFromQuery( query ) {
try {
await db.sequelize.query ( query ); // to check if query works
const parser = new Parser();
const pasredQuery = parser.astify(query);
let columns = [];
if (Array.isArray(pasredQuery)) {
// only giving columns for 1st query if the result of parsed queries results in multiple queries
columns = await this.getColumnNames( pasredQuery[0] );
} else {
columns = await this.getColumnNames( pasredQuery );
}
return response.SendResponse(res, columns, 0, "List of Columns");
} catch ( err ) {
return response.SendResponse(res, "An Error occoured", 1, err);
}
}
Sample test of the above code:
let query = `
SELECT * FROM Customer INNER JOIN database.Order ON database.Order.CustomerId = Customer.Id;
`
getColumnsFromQuery ( query );
Returns:
[{
"TABLE_NAME": "Customer",
"COLUMN_NAME": "Id",
"DATA_TYPE": "int",
"CHARACTER_MAXIMUM_LENGTH": null,
"NUMERIC_PRECISION": 10,
"NUMERIC_SCALE": 0
},
{
"TABLE_NAME": "Customer",
"COLUMN_NAME": "Name",
"DATA_TYPE": "varchar",
"CHARACTER_MAXIMUM_LENGTH": 10,
"NUMERIC_PRECISION": null,
"NUMERIC_SCALE": null
},
{
"TABLE_NAME": "Customer",
"COLUMN_NAME": "Age",
"DATA_TYPE": "int",
"CHARACTER_MAXIMUM_LENGTH": 10,
"NUMERIC_PRECISION": 10,
"NUMERIC_SCALE": 0
},
.
.
.
{
"TABLE_NAME": "Order",
"COLUMN_NAME": "OrderName",
"DATA_TYPE": "varchar",
"CHARACTER_MAXIMUM_LENGTH": 10,
"NUMERIC_PRECISION": null,
"NUMERIC_SCALE": null
},
{
"TABLE_NAME": "Order",
"COLUMN_NAME": "OrderDate",
"DATA_TYPE": "datetime",
"CHARACTER_MAXIMUM_LENGTH": null,
"NUMERIC_PRECISION": null,
"NUMERIC_SCALE": null
},
{
"TABLE_NAME": "Customer",
"COLUMN_NAME": "Age",
"DATA_TYPE": "int",
"CHARACTER_MAXIMUM_LENGTH": 10,
"NUMERIC_PRECISION": 10,
"NUMERIC_SCALE": 0
},
.
.
.
]
You can modify the query in the getAllColumnsOfTablesFromDB to get any data about all the columns.
I tested it with many queries including queries that use alias for table names and also for columns.
This code can probably be refactored much further but this was the maximum I could do since I am not enough experienced to take this further.