October 23, 2013 at 21:13Outdoor facilities for MySQL driver in Node.js
Anyone who writes on Node.js and uses MySQL knows that our dear friend Felix Geisendörfer made a heroic and selfless feat three years ago: he alone developed a very high-quality driver for connecting to MySQL by natively implementing the binary protocol of this DBMS in JavaScript. Then other respected comrades joined the project, support for connection pools, clusters, transactions, restoration with temporary loss of connection was implemented, etc. Now the driver is the most developed, well-developed and actively supported from what we have in the open npm and github repositories. It’s even surprising that with a well-developed low-level implementation,all the convenience this driver provides for an application developer comes down to a single query method. For me personally, this is not enough, well, grandfather got used to the convenience of returning scalar values, rows and columns to arrays, introspection of database structures. So, with pleasure I share these developments with you, my dear Khabravchians, but I warn you that all the amenities will be on the street . Of course, there is a variant of tight integration with the driver, but FelixGe wants to leave the driver exclusively low-level, so I settled on the option of an external library with the addition of impurities to the driver. In the form of impurities, convenience also got into the Impress platform, as well as published as a patch for the driver. About the functionality and use cases below.Data Retrieval Amenities
Further, under the word "returns" I will mean the second parameter callback, and not the result of calling the function.
Fetching a single record : connection. queryRow (sql, values, callback) returns a hash (associative array) in which field names become keys (instead of getting the array in the array through query).
Example
connection.queryRow('SELECT * FROM Language where LanguageId=?', [3], function(err, row) {
console.dir({queryRow:row});
/* Example:
queryRow: {
LanguageId: 3,
LanguageName: 'Russian',
LanguageSign: 'ru',
LanguageISO: 'ru',
Caption: 'Русский'
}
*/
});
Fetching a scalar (i.e., a single value): connection. queryValue (sql, values, callback) returns a single value (instead of getting an array in an array with a single value). It is convenient when fetching one field from one record, for example, Id by name with LIMIT 1 or functions count (*), max (field), etc.
Example
connection.queryValue('SELECT LanguageName FROM Language where LanguageId=?', [8],
function(err, name) {
console.dir({queryValue:name});
/* Example:
queryValue: 'Italiano'
*/
});
Fetch a single column : connection. queryCol (sql, values, callback) returns an array filled with the values of one field for each record from the result of the query. That is, it is a selection of a vertical column, as opposed to a selection of a horizontal record queryRow.
UPD: gelas convinced me to rename queryArray to queryCol, similar to queryRow.
Example
connection.queryCol('SELECT LanguageSign FROM Language', [], function(err, result) {
console.dir({queryCal:result});
/* Example:
queryArray: [ 'de', 'en', 'es', 'fr', 'it', 'pl', 'ru', 'ua' ]
*/
});
Sample hash : connection. queryHash (sql, values, callback) returns a hash (associative array) of two-level nesting, where the first-level keys are the values of the first field from the query result, and the second-level keys are all fields of the query result (including the first).
Example
connection.queryHash(
'SELECT LanguageSign, LanguageId, LanguageName, Caption, LanguageISO FROM Language', [],
function(err, result) {
console.dir({queryHash:result});
/* Example:
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'
}
}
*/
});
Selection of key / value pairs : connection. queryKeyValue (sql, values, callback) returns a hash (associative array), where the key will be the first field from the query result to the query.
Example
connection.queryKeyValue(
'SELECT LanguageISO, LanguageName FROM Language', [], function(err, keyValue) {
console.dir({queryKeyValue:keyValue});
/* Example:
keyValue: {
en: 'English',
ru: 'Russian',
uk: 'Ukrainian',
es: 'Espanol',
fr: 'Francais',
de: 'Deutsch',
it: 'Italiano',
pl: 'Poliski'
}
*/
});
The convenience of introspection
That is, the convenience of obtaining metadata, structures and database parameters for their analysis and automatic construction of logic or interfaces for working with this database.
Getting the primary key : connection. primary (table, callback) returns a hash (associative array), with metadata about the primary key, see the metadata set in the example.
Example
connection.primary('Language', function(err, primary) {
console.dir({primary:primary});
/* Example:
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: ''
}
*/
});
Getting foreign keys : connection. foreign (table, callback) returns a hash (associative array), with double nesting, on the first level are the names of foreign keys, and on the second is metadata describing this key. A set of fields is shown in the example.
Example
connection.foreign('TemplateCaption', function(err, foreign) {
console.dir({foreign:foreign});
/* Example:
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'
}
*/
});
Getting integrity constraints : connection. constraints (table, callback) returns a hash (associative array), with double nesting, on the first level are the names of integrity constraints, and on the second is metadata describing each constraint. A set of fields is shown in the example.
Example
connection.constraints('TemplateCaption', function(err, constraints) {
console.dir({constraints:constraints});
/* Example:
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'
}
}
*/
});
Getting metadata about table fields : connection. fields (table, callback) returns an array with metadata for each field, including the name, type, all modifiers and flags, comments (see the example for more details).
Example
connection.fields('Language', function(err, fields) {
console.dir({fields:fields});
/* Example:
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)'
}, ...
}
*/
});
Getting the list of databases available for this connection: connection. databases (callback) returns an array of database names (or “schemas,” as they are sometimes called).
Example
connection.databases(function(err, databases) {
console.dir({databases:databases});
/* Example:
databases: [ 'information_schema', 'mezha', 'mysql', 'performance_schema', 'test' ]
*/
});
Retrieving a list of tables for the database that is currently selected for this connection: connection. tables (callback) returns a hash (associative array) of double nesting, where on the first level, the keys are the names of the tables, and on the second, the metadata for each table.
Example
connection.tables(function(err, tables) {
console.dir({tables:tables});
/* Example:
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)'
}, ...
}
*/
});
UPD: Get a list of tables for the specified database: connection. databaseTables (database, callback) returns a hash (associative array) of double nesting, where on the first level the keys are the names of the tables, and on the second is the metadata for each table.
Example
connection.databaseTables("databaseName", function(err, tables) {
console.dir({databaseTables:tables});
/* Example:
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)'
}, ...
}
*/
});
Retrieving metadata for the specified table : connection. tableInfo (table, callback) returns a hash (associative array) with metadata (see the example for more details).
Example
connection.tableInfo('Language', function(err, info) {
console.dir({tableInfo:info});
/* Example:
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: ''
}
*/
});
Getting metadata about all the keys in this table : connection. indexes (table, callback) returns hash (associative array)? the keys of the first level in it are the names of the database keys (), and the keys of the second level are the metadata for each key (see the example for a detailed list of metadata).
Example
connection.indexes('Language', function(err, info) {
console.dir({tableInfo:info});
/* Example:
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: ''
}
}
*/
});
Getting processes on a MySQL server : connection. processes (callback) returns an array of hashes, where its parameters are given for each process.
Example
connection.processes(function(err, processes) {
console.dir({processes:processes});
/* Example:
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
} ]
*/
});
Getting global MySQL variables : connection. globalVariables (callback)
Example
connection.globalVariables(function(err, globalVariables) {
console.dir({globalVariables:globalVariables});
/* Example:
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',
...
}
*/
});
Obtaining the global status of MySQL: connection. globalStatus (callback)
Example
connection.globalStatus(function(err, globalStatus) {
console.dir({globalStatus:globalStatus});
/* Example:
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',
...
}
*/
});
Getting a list of MySQL users : connection. users (callback)
Example
connection.users(function(err, users) {
console.dir({users:users});
/* Example:
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: ,
x509_issuer: ,
x509_subject: ,
max_questions: 0,
max_updates: 0,
max_connections: 0,
max_user_connections: 0,
plugin: '',
authentication_string: ''
}, ...
]
*/
});
The convenience of generating queries
Well and absolutely already masterful conveniences allowing to generate SQL either completely or separate WHERE expressions. I myself am not a supporter of such sugar, but sometimes it is necessary to automate the generation of queries that are not known in advance and I allow myself such a luxury.
Condition Generation : connection. where (conditions) works synchronously, and not asynchronously, like other functions, i.e. does not use callback. Returns the built-in WHERE SQL expression for conditions described in JSON style. You must definitely look at an example to understand:
Example
var 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)"
A selection with the condition: connection. select (table, whereFilter, callback)
Example
connection.select('Language', '*', { LanguageId: "1..3" }, function(err, results) {
console.dir({select:results});
});
Insert Record: connection. insert (table, row, callback)
Example
connection.insert('Language', {
LanguageName: 'Tatar',
LanguageSign:'TT',
LanguageISO:'TT',
Caption:'Tatar'
}, function(err, recordId) {
console.dir({insert:recordId});
});
Editing a record: connection. update (table, row, callback)
Example
connection.update('Language', {
LanguageId: 25,
LanguageName:'Tatarca',
LanguageSign:'TT',
LanguageISO:'TT',
Caption:'Tatarca'
}, function(err, affectedRows) {
console.dir({update:affectedRows});
});
Insert if there is no such entry or change if it already exists: connection. upsert (table, row, callback)
Example
connection.upsert('Language', {
LanguageId: 25,
LanguageName:'Tatarca',
LanguageSign:'TT',
LanguageISO:'TT',
Caption:'Tatarca'
}, function(err, affectedRows) {
console.dir({upsert:affectedRows});
});
Getting the number of records in a table by a given filter or without a filter: connection.count (table, whereFilter, callback)
Example
connection.count('Language', { LanguageId: ">3" }, function(err, count) {
console.dir({count:count});
/* Example:
count: 9
*/
});
Delete a record or several records: connection. delete (table, whereFilter, callback)
Example
connection.delete('Language', { LanguageSign:'TT' }, function(err, affectedRows) {
console.dir({delete:affectedRows});
});
Use cases
UPD: It is preferable to use an independent module , with a higher level of abstraction than just a driver, but not as high as ORM libraries. I don’t want to do this cumbersome thing, and I want to leave the opportunity to add functionality to the connection through impurities (only necessary) groups of functions.
It is done like this:
// Library dependencies
var mysql = require('mysql'),
mysqlUtilities = require('utilities');
var 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],
function(err, row) {
console.dir({queryRow:row});
}
);
// Release connection
connection.end();
There is also a patch for the node-mysql driver with all the above functions: https://github.com/felixge/node-mysql
And the third use case is the Impress application server for Node.js, into which this library is built. All connections to MySQL immediately have two groups of sampling functions in Impress, they mix in automatically when the connection is opened, and only introspection can be added through the config where you need to uncomment the corresponding plug-in so that the set of plug-ins for MySQL is like this:
plugins: {
require: [
"db",
"db.mysql",
"db.mysql.introspection",
...
]
},...
Then the plugin automatically mixes with each connection: if (db.mysql.introspection) db.mysql.introspection (connection);
Here are the source code for the Impress plugins :
- https://github.com/tshemsedinov/impress/blob/master/lib/db.mysql.js
- https://github.com/tshemsedinov/impress/blob/master/lib/db.mysql.introspection.js
References
UPD: At the time of writing, there were two implementation options (as a patch for the driver and as modules for Impress), but the survey convincingly showed that it makes sense to spend time and design this library as a separate module. This was done by the evening of the same day. But I do not close the survey to determine nevertheless the degree of usefulness of these amenities.
The main use case is here:
On Github: https://github.com/tshemsedinov/node-mysql-utilities
In the nmp repository: https://npmjs.org/package/mysql-utilities
Only registered users can participate in the survey. Please come in.
Do you need such amenities?
- 16.8% Yes, I wrote something similar to myself 33
- 4.5% Yes, I already use it, but another implementation 9
- 64.2% Yes, I was just thinking of looking for them 126
- 14.2% No, these are completely superfluous functions 28
Should I separate them into a separate library and publish separately from the driver and from Impress?
- 89% Yes, if possible, do it (UPD: ready) 49
- 10.9% Do not waste time 6