Outdoor facilities for MySQL drivers in Node.js

Who writes on Node.js and uses MySQL, he certainly knows that our dear comrade Felix Geisendörfer accomplished a heroic and selfless deed three years ago: he alone developed a very high-quality driver for connecting to MySQL, natively implementing the binary protocol of this database in JavaScript. Then other respected comrades joined the project, support for connection pools, clusters, transactions, recovery with temporary connection loss, etc. was implemented. Now the driver is the most developed, well-developed and actively supported from what we have in the open repositories npm and github. It is even surprising that with a well-designed low-level implementation, all the amenities that this driver provides for the application developer are reduced to a single query method. For me personally, this is very little, well, Grandpa got used to the convenience of returning scalar values, rows and columns to arrays, introspection of database structures. So, I am pleased to share these developments with you, my dear Habravchane, but I warn you that all amenities will be on the street . Of course, there is the option of tight integration with the driver, but FelixGe wants to leave the driver extremely low-level, so I stopped at the version of the external library with the addition to the driver through admixture. In the form of impurities, convenience got into the Impress platform, as well as published as a patch to the driver. On the functional and use options below.

Data retrieval facilities

Further, under the word "returns" I will understand the second parameter callback, and not the result of a function call.

Selection of one record : connection. queryRow (sql, values, callback) returns a hash (associative array) in which field names become keys (instead of getting an array in an array via 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: '' } */ });

A sample of a scalar (that is, a single value): connection. queryValue (sql, values, callback) returns one value (instead of getting an array in an array with one value). Conveniently when selecting one field from one record, for example, Id by name with indication 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' */ });

Selection of one 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 sample of a vertical column, in contrast to a sample of a horizontal queryRow record.
UPD: gelas convinced me to rename queryArray to queryCol by analogy with 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' ] */ });

Hash selection: 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 result of the query, 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 is the first field from the query result of 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' } */ });

Introspection facilities

That is, the convenience of obtaining metadata, structures and parameters of the database for their analysis and the 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 - the metadata describing this key. A set of fields, see 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, at the first level the names of integrity constraints, and at the second level - metadata describing each constraint. A set of fields, see 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 a list of databases available on this connection: connection. databases (callback) returns an array of database names (or "schemes", as they are sometimes called).

Example

 connection.databases(function(err, databases) { console.dir({databases:databases}); /* Example: databases: [ 'information_schema', 'mezha', 'mysql', 'performance_schema', 'test' ] */ });

Getting the list of tables for the database selected as current for this connection: connection. tables (callback) returns a hash (associative array) of double nesting, where at the first level the keys are the names of the tables, and at the second level 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 at the first level the keys are the names of the tables and at the second level 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)' }, ... } */ });

Getting the metadata of the specified table : connection. tableInfo (table, callback) returns a hash (associative array) with metadata (for more details, see the example).

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 keys of this table : connection. indexes (table, callback) returns a hash (associative array)? the keys of the first level in it are the names of the keys of the database (), and the keys of the second level are the metadata about 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 the MySQL server : connection. processes (callback) returns an array of hashes, where for each process its parameters are given.

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 MySQL global 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', ... } */ });

Getting 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: <Buffer >, x509_issuer: <Buffer >, x509_subject: <Buffer >, max_questions: 0, max_updates: 0, max_connections: 0, max_user_connections: 0, plugin: '', authentication_string: '' }, ... ] */ });

Query generation facilities

Well, quite lordly convenience, allowing to generate SQL or fully or separate WHERE expressions. I myself do not advocate such sugar, but sometimes it is necessary to automate the generation of requests that are not known in advance and I allow myself the luxury.

Condition generation : connection. where (conditions) works synchronously, not asynchronously, like other functions, i.e. does not use callback. Returns a built-in WHERE SQL expression for conditions described in JSON style. It is necessary to 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)"

Sample 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}); });

Change 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 record 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 the table for a given filter or without filter: connection.count (table, whereFilter, callback)

Example

 connection.count('Language', { LanguageId: ">3" }, function(err, count) { console.dir({count:count}); /* Example: count: 9 */ });

Deleting 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 the ORM library. I do not want to do a cumbersome thing out of this, and I want to leave the opportunity to add functionality to the compound through impurities (only necessary) groups of functions.
This 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 to 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, which has this library built into it. Two groups of functions in the sample in Impress immediately have all connections to MySQL, they are automatically added when opening the connection, and only introspection can be added via the config where you need to uncomment the corresponding plug-in so that the set of plug-ins for MySQL is:

  plugins: { require: [ "db", "db.mysql", "db.mysql.introspection", ... ] },...

Then the plugin will automatically be added to each connection: if (db.mysql.introspection) db.mysql.introspection (connection);
Here is the source of the plugins for Impress :

Links

UPD: At the time of writing, there were two options for implementation (as a patch to the driver and as modules for Impress), but the survey convincingly showed that it makes sense to spend time and arrange 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 the degree of usefulness of these amenities.

The main use case is here:
In Github: https://github.com/tshemsedinov/node-mysql-utilities
In the nmp repository: https://npmjs.org/package/mysql-utilities

Source: https://habr.com/ru/post/198738/

All Articles

Outdoor facilities for MySQL drivers in Node.js

Data retrieval facilities

Introspection facilities

Query generation facilities

Use cases

Links

More articles: