Product
Introducing License Enforcement in Socket
Ensure open-source compliance with Socket’s License Enforcement Beta. Set up your License Policy and secure your software!
mysql-utilities
Advanced tools
Utilities for node-mysql driver with specialized result types, introspection and other helpful functionality.
Utilities for node-mysql driver with specialized result types, introspection and other helpful functionality for node.js. Initially this utilities were part of Impress Application Server and extracted separately for use with other frameworks.
$ npm install mysql-utilities
Utilities can be attached to connection using mix-ins:
// Library dependencies
const mysql = require('mysql');
const mysqlUtilities = require('mysql-utilities');
const connection = mysql.createConnection({
host: 'localhost',
user: 'userName',
password: 'secret',
database: 'databaseName',
});
connection.connect();
// Mix-in for Data Access Methods and SQL Autogenerating Methods
mysqlUtilities.upgrade(connection);
// Mix-in for Introspection Methods
mysqlUtilities.introspection(connection);
// Do something using utilities
connection.queryRow(
'SELECT * FROM _Language where LanguageId=?',
[3],
(err, row) => {
console.dir({ queryRow: row });
}
);
// Release connection
connection.end();
Single row selection: connection.queryRow(sql, values, callback) returns hash as callback second parameter, field names becomes hash keys.
connection.queryRow(
'SELECT * FROM Language where LanguageId=?',
[3],
(err, row) => {
console.dir({ queryRow: row });
}
);
Output:
queryRow: {
LanguageId: 3,
LanguageName: 'Russian',
LanguageSign: 'ru',
LanguageISO: 'ru',
Caption: 'Русский'
}
Single value selection: connection.queryValue(sql, values, callback) returns single value as callback second parameter (instead of array in array). For example, for Id selection by name with LIMIT 1 or count(*), max(field) etc.
connection.queryValue(
'SELECT LanguageName FROM Language where LanguageId=?',
[8],
(err, name) => {
console.dir({ queryValue: name });
}
);
Output:
{
queryValue: 'Italiano';
}
Single column selection: connection.queryCol(sql, values, callback) returns array as callback second parameter.
connection.queryCol('SELECT LanguageSign FROM Language', [], (err, result) => {
console.dir({ queryCal: result });
});
Output:
queryArray: ['de', 'en', 'es', 'fr', 'it', 'pl', 'ru', 'ua'];
Hash selection: connection.queryHash(sql, values, callback) returns hash as callback second parameter, hash keyed by first field values from SQL statement.
connection.queryHash(
'SELECT LanguageSign, LanguageId, LanguageName, Caption, LanguageISO FROM Language',
[],
(err, result) => {
console.dir({ queryHash: result });
}
);
Output:
queryHash: {
en: {
LanguageSign: 'en',
LanguageId: 2,
LanguageName: 'English',
Caption: 'Английский',
LanguageISO: 'en' },
ru: {
LanguageSign: 'ru',
LanguageId: 3,
LanguageName: 'Russian',
Caption: 'Русский',
LanguageISO: 'ru' },
de: {
LanguageSign: 'de',
LanguageId: 7,
LanguageName: 'Deutsch',
Caption: 'Немецкий',
LanguageISO: 'de' },
it: {
LanguageSign: 'it',
LanguageId: 8,
LanguageName: 'Italiano',
Caption: 'Итальянский',
LanguageISO: 'it'
}
}
Key/value pair selection: connection.queryKeyValue(sql, values, callback) returns hash as callback second parameter, hash keyed by first field, values filled by second field.
connection.queryKeyValue(
'SELECT LanguageISO, LanguageName FROM Language',
[],
(err, keyValue) => {
console.dir({ queryKeyValue: keyValue });
}
);
Output:
keyValue: {
en: 'English',
ru: 'Russian',
uk: 'Ukrainian',
es: 'Espanol',
fr: 'Francais',
de: 'Deutsch',
it: 'Italiano',
pl: 'Poliski'
}
Get primary key list with metadata: connection.primary(table, callback) returns metadata as callback second parameter.
connection.primary('Language', (err, primary) => {
console.dir({ primary });
});
Output:
primary: {
Table: 'language',
Non_unique: 0,
Key_name: 'PRIMARY',
Seq_in_index: 1,
Column_name: 'LanguageId',
Collation: 'A',
Cardinality: 9,
Sub_part: null,
Packed: null,
Null: '',
Index_type: 'BTREE',
Comment: '',
Index_comment: ''
}
Get foreign key list with metadata: connection.foreign(table, callback) returns metadata as callback second parameter.
connection.foreign('TemplateCaption', (err, foreign) => {
console.dir({ foreign });
});
Output:
foreign: {
fkTemplateCaptionLanguage: {
CONSTRAINT_NAME: 'fkTemplateCaptionLanguage',
COLUMN_NAME: 'LanguageId',
ORDINAL_POSITION: 1,
POSITION_IN_UNIQUE_CONSTRAINT: 1,
REFERENCED_TABLE_NAME: 'language',
REFERENCED_COLUMN_NAME: 'LanguageId' },
fkTemplateCaptionTemplate: {
CONSTRAINT_NAME: 'fkTemplateCaptionTemplate',
COLUMN_NAME: 'TemplateId',
ORDINAL_POSITION: 1,
POSITION_IN_UNIQUE_CONSTRAINT: 1,
REFERENCED_TABLE_NAME: 'template',
REFERENCED_COLUMN_NAME: 'TemplateId'
}
}
Referential constraints list with metadata: connection.constraints(table, callback).
connection.constraints('TemplateCaption', (err, constraints) => {
console.dir({ constraints });
});
Output:
constraints: {
fkTemplateCaptionLanguage: {
CONSTRAINT_NAME: 'fkTemplateCaptionLanguage',
UNIQUE_CONSTRAINT_NAME: 'PRIMARY',
REFERENCED_TABLE_NAME: 'Language',
MATCH_OPTION: 'NONE',
UPDATE_RULE: 'RESTRICT',
DELETE_RULE: 'CASCADE' },
fkTemplateCaptionTemplate: {
CONSTRAINT_NAME: 'fkTemplateCaptionTemplate',
UNIQUE_CONSTRAINT_NAME: 'PRIMARY',
REFERENCED_TABLE_NAME: 'Template',
MATCH_OPTION: 'NONE',
UPDATE_RULE: 'RESTRICT',
DELETE_RULE: 'CASCADE'
}
}
Get table fields with metadata: connection.fields(table, callback).
connection.fields('Language', (err, fields) => {
console.dir({ fields });
});
Output:
fields: {
LanguageId: {
Field: 'LanguageId',
Type: 'int(10) unsigned',
Collation: null,
Null: 'NO',
Key: 'PRI',
Default: null,
Extra: 'auto_increment',
Privileges: 'select,insert,update,references',
Comment: 'Id(EN),Код(RU)' },
LanguageName: {
Field: 'LanguageName',
Type: 'varchar(32)',
Collation: 'utf8_general_ci',
Null: 'NO',
Key: 'UNI',
Default: null,
Extra: '',
Privileges: 'select,insert,update,references',
Comment: 'Name(EN),Имя(RU)'
}, ...
}
Get database list for current connection: connection.databases(callback).
connection.databases((err, databases) => {
console.dir({ databases });
});
Output:
databases: [
'information_schema',
'mezha',
'mysql',
'performance_schema',
'test',
];
Get table list for current database: connection.tables(callback).
connection.tables((err, tables) => {
console.dir({ tables });
});
Output:
tables: {
Language: {
TABLE_NAME: 'Language',
TABLE_TYPE: 'BASE TABLE',
ENGINE: 'InnoDB',
VERSION: 10,
ROW_FORMAT: 'Compact',
TABLE_ROWS: 9,
AVG_ROW_LENGTH: 1820,
DATA_LENGTH: 16384,
MAX_DATA_LENGTH: 0,
INDEX_LENGTH: 49152,
DATA_FREE: 8388608,
AUTO_INCREMENT: 10,
CREATE_TIME: Mon Jul 15 2013 03:06:08 GMT+0300 (Финляндия (лето)),
UPDATE_TIME: null,
CHECK_TIME: null,
TABLE_COLLATION: 'utf8_general_ci',
CHECKSUM: null,
CREATE_OPTIONS: '',
TABLE_COMMENT: '_Language:Languages(EN),Языки(RU)'
}, ...
}
Get table list for specified database: connection.databaseTables(database, callback).
connection.databaseTables('databaseName', (err, tables) => {
console.dir({ databaseTables: tables });
});
Output:
tables: {
Language: {
TABLE_NAME: 'Language',
TABLE_TYPE: 'BASE TABLE',
ENGINE: 'InnoDB',
VERSION: 10,
ROW_FORMAT: 'Compact',
TABLE_ROWS: 9,
AVG_ROW_LENGTH: 1820,
DATA_LENGTH: 16384,
MAX_DATA_LENGTH: 0,
INDEX_LENGTH: 49152,
DATA_FREE: 8388608,
AUTO_INCREMENT: 10,
CREATE_TIME: Mon Jul 15 2013 03:06:08 GMT+0300 (Финляндия (лето)),
UPDATE_TIME: null,
CHECK_TIME: null,
TABLE_COLLATION: 'utf8_general_ci',
CHECKSUM: null,
CREATE_OPTIONS: '',
TABLE_COMMENT: '_Language:Languages(EN),Языки(RU)'
}, ...
}
Get table metadata: connection.tableInfo(table, callback).
connection.tableInfo('Language', (err, info) => {
console.dir({ tableInfo: info });
});
Output:
tableInfo: {
Name: 'language',
Engine: 'InnoDB',
Version: 10,
Row_format: 'Compact',
Rows: 9,
Avg_row_length: 1820,
Data_length: 16384,
Max_data_length: 0,
Index_length: 49152,
Data_free: 9437184,
Auto_increment: 10,
Create_time: Mon Jul 15 2013 03:06:08 GMT+0300 (Финляндия (лето)),
Update_time: null,
Check_time: null,
Collation: 'utf8_general_ci',
Checksum: null,
Create_options: '',
Comment: ''
}
Get table indexes metadata: connection.indexes(table, callback).
connection.indexes('Language', function (err, info) {
console.dir({ tableInfo: info });
});
Output:
indexes: {
PRIMARY: {
Table: 'language',
Non_unique: 0,
Key_name: 'PRIMARY',
Seq_in_index: 1,
Column_name: 'LanguageId',
Collation: 'A',
Cardinality: 9,
Sub_part: null,
Packed: null,
Null: '',
Index_type: 'BTREE',
Comment: '',
Index_comment: '' },
akLanguage: {
Table: 'language',
Non_unique: 0,
Key_name: 'akLanguage',
Seq_in_index: 1,
Column_name: 'LanguageName',
Collation: 'A',
Cardinality: 9,
Sub_part: null,
Packed: null,
Null: '',
Index_type: 'BTREE',
Comment: '',
Index_comment: ''
}
}
Get MySQL process list: connection.processes(callback).
connection.processes(function (err, processes) {
console.dir({ processes });
});
Output:
processes: [
{
ID: 62,
USER: 'mezha',
HOST: 'localhost:14188',
DB: 'mezha',
COMMAND: 'Query',
TIME: 0,
STATE: 'executing',
INFO: 'SELECT * FROM information_schema.PROCESSLIST',
},
{
ID: 33,
USER: 'root',
HOST: 'localhost:39589',
DB: null,
COMMAND: 'Sleep',
TIME: 1,
STATE: '',
INFO: null,
},
];
Get MySQL global variables: connection.globalVariables(callback)
connection.globalVariables((err, globalVariables) => {
console.dir({ globalVariables });
});
Output:
globalVariables: {
MAX_PREPARED_STMT_COUNT: '16382',
MAX_JOIN_SIZE: '18446744073709551615',
HAVE_CRYPT: 'NO',
PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_LONG_SIZE: '10000',
INNODB_VERSION: '5.5.32',
FLUSH_TIME: '1800',
MAX_ERROR_COUNT: '64',
...
}
Get MySQL global status: connection.globalStatus(callback)
connection.globalStatus((err, globalStatus) => {
console.dir({ globalStatus });
});
Output:
globalStatus: {
ABORTED_CLIENTS: '54',
ABORTED_CONNECTS: '2',
BINLOG_CACHE_DISK_USE: '0',
BINLOG_CACHE_USE: '0',
BINLOG_STMT_CACHE_DISK_USE: '0',
BINLOG_STMT_CACHE_USE: '0',
BYTES_RECEIVED: '654871',
BYTES_SENT: '212454927',
COM_ADMIN_COMMANDS: '594',
...
}
Get MySQL user list: connection.users(callback)
connection.users((err, users) => {
console.dir({ users });
});
Output:
users: [
{
Host: 'localhost',
User: 'root',
Password: '*90E462C37378CED12064BB3388827D2BA3A9B689',
Select_priv: 'Y',
Insert_priv: 'Y',
Update_priv: 'Y',
Delete_priv: 'Y',
Create_priv: 'Y',
Drop_priv: 'Y',
Reload_priv: 'Y',
Shutdown_priv: 'Y',
Process_priv: 'Y',
File_priv: 'Y',
Grant_priv: 'Y',
References_priv: 'Y',
Index_priv: 'Y',
Alter_priv: 'Y',
Show_db_priv: 'Y',
Super_priv: 'Y',
Create_tmp_table_priv: 'Y',
Lock_tables_priv: 'Y',
Execute_priv: 'Y',
Repl_slave_priv: 'Y',
Repl_client_priv: 'Y',
Create_view_priv: 'Y',
Show_view_priv: 'Y',
Create_routine_priv: 'Y',
Alter_routine_priv: 'Y',
Create_user_priv: 'Y',
Event_priv: 'Y',
Trigger_priv: 'Y',
Create_tablespace_priv: 'Y',
ssl_type: '',
ssl_cipher: <Buffer >,
x509_issuer: <Buffer >,
x509_subject: <Buffer >,
max_questions: 0,
max_updates: 0,
max_connections: 0,
max_user_connections: 0,
plugin: '',
authentication_string: ''
}, ...
]
Generate MySQL WHERE statement: connection.where(conditions), works synchronously, no callback. Returns WHERE statement for given JSON-style conditions.
const where = connection.where({
id: 5,
year: '>2010',
price: '100..200',
level: '<=3',
sn: '*str?',
label: 'str',
code: '(1,2,4,10,11)',
});
console.dir(where);
// Output: "id = 5 AND year > '2010' AND (price BETWEEN '100' AND '200') AND
// level <= '3' AND sn LIKE '%str_' AND label = 'str' AND code IN (1,2,4,10,11)"
Generate SELECT statement: connection.select(table, whereFilter, orderBy, callback)
connection.select(
'Language',
'*',
{ LanguageId: '1..3' },
{ LanguageId: 'desc' },
(err, results) => {
console.dir({ select: results });
}
);
Generate INSERT statement: connection.insert(table, row, callback)
connection.insert(
'Language',
{
LanguageName: 'Tatar',
LanguageSign: 'TT',
LanguageISO: 'TT',
Caption: 'Tatar',
},
(err, recordId) => {
console.dir({ insert: recordId });
}
);
Generate UPDATE statement: connection.update(table, row, callback)
connection.update(
'Language',
{
LanguageId: 25,
LanguageName: 'Tatarca',
LanguageSign: 'TT',
LanguageISO: 'TT',
Caption: 'Tatarca',
},
(err, affectedRows) => {
console.dir({ update: affectedRows });
}
);
Generate UPDATE statement with "where": connection.update(table, row, where, callback)
connection.update(
'Language',
{ LanguageSign: 'TT' },
{ LanguageId: 1 },
(err, affectedRows) => {
console.dir({ update: affectedRows });
}
);
Generate INSERT statement if record not exists or UPDATE if it exists: connection.upsert(table, row, callback)
connection.upsert(
'Language',
{
LanguageId: 25,
LanguageName: 'Tatarca',
LanguageSign: 'TT',
LanguageISO: 'TT',
Caption: 'Tatarca',
},
(err, affectedRows) => {
console.dir({ upsert: affectedRows });
}
);
Get record count: connection.count(table, whereFilter, callback)
connection.count('Language', { LanguageId: '>3' }, (err, count) => {
console.dir({ count });
// count: 9
});
Generate DELETE statement: connection.delete(table, whereFilter, callback)
connection.delete('Language', { LanguageSign: 'TT' }, (err, affectedRows) => {
console.dir({ delete: affectedRows });
});
Copyright (c) 2012-2021 Metarhia <timur.shemsedinov@gmail.com> See github for full contributors list. Node MySql Utilities is MIT licensed.
FAQs
Utilities for node-mysql driver with specialized result types, introspection and other helpful functionality.
The npm package mysql-utilities receives a total of 3,111 weekly downloads. As such, mysql-utilities popularity was classified as popular.
We found that mysql-utilities demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Product
Ensure open-source compliance with Socket’s License Enforcement Beta. Set up your License Policy and secure your software!
Product
We're launching a new set of license analysis and compliance features for analyzing, managing, and complying with licenses across a range of supported languages and ecosystems.
Product
We're excited to introduce Socket Optimize, a powerful CLI command to secure open source dependencies with tested, optimized package overrides.