All Projects → tshemsedinov → Node Mysql Utilities

tshemsedinov / Node Mysql Utilities

Licence: mit
Query builder for node-mysql with introspection, etc.

Programming Languages

javascript
184084 projects - #8 most used programming language
js
455 projects
introspection
24 projects

Projects that are alternatives of or similar to Node Mysql Utilities

Db
Data access layer for PostgreSQL, CockroachDB, MySQL, SQLite and MongoDB with ORM-like features.
Stars: ✭ 2,832 (+2789.8%)
Mutual labels:  sql, database, mysql, db
Electrocrud
Database CRUD Application Built on Electron | MySQL, Postgres, SQLite
Stars: ✭ 1,267 (+1192.86%)
Mutual labels:  sql, database, mysql, crud
Laravel Log To Db
Custom Laravel and Lumen 5.6+ Log channel handler that can store log events to SQL or MongoDB databases. Uses Laravel/Monolog native logging functionality.
Stars: ✭ 76 (-22.45%)
Mutual labels:  sql, database, mysql, db
Laravel Db Profiler
Database Profiler for Laravel Web and Console Applications.
Stars: ✭ 141 (+43.88%)
Mutual labels:  sql, database, mysql, db
Php Sql Query Builder
An elegant lightweight and efficient SQL Query Builder with fluid interface SQL syntax supporting bindings and complicated query generation.
Stars: ✭ 313 (+219.39%)
Mutual labels:  sql, database, mysql, crud
Qb
The database toolkit for go
Stars: ✭ 524 (+434.69%)
Mutual labels:  sql, database, mysql, db
Evolutility Server Node
Model-driven REST or GraphQL backend for CRUD and more, written in Javascript, using Node.js, Express, and PostgreSQL.
Stars: ✭ 84 (-14.29%)
Mutual labels:  sql, metadata, database, crud
Ezsql
PHP class to make interacting with a database ridiculusly easy
Stars: ✭ 804 (+720.41%)
Mutual labels:  sql, mysql, crud
Reiner
萊納 - A MySQL wrapper which might be better than the ORMs and written in Golang
Stars: ✭ 19 (-80.61%)
Mutual labels:  sql, database, mysql
Graphjin
GraphJin - Build APIs in 5 minutes with GraphQL. An instant GraphQL to SQL compiler.
Stars: ✭ 1,264 (+1189.8%)
Mutual labels:  sql, database, mysql
Qtl
A friendly and lightweight C++ database library for MySQL, PostgreSQL, SQLite and ODBC.
Stars: ✭ 92 (-6.12%)
Mutual labels:  sql, database, mysql
Imdbpy
IMDbPY is a Python package useful to retrieve and manage the data of the IMDb movie database about movies, people, characters and companies
Stars: ✭ 792 (+708.16%)
Mutual labels:  sql, database, db
Migrate
Database migrations. CLI and Golang library.
Stars: ✭ 7,712 (+7769.39%)
Mutual labels:  sql, database, mysql
Node Pg Migrate
Node.js database migration management for Postgresql
Stars: ✭ 838 (+755.1%)
Mutual labels:  sql, database, db
Mysqldump Php
PHP version of mysqldump cli that comes with MySQL
Stars: ✭ 975 (+894.9%)
Mutual labels:  sql, database, mysql
Db Mysql
Stars: ✭ 22 (-77.55%)
Mutual labels:  database, mysql, db
Eralchemy
Entity Relation Diagrams generation tool
Stars: ✭ 767 (+682.65%)
Mutual labels:  sql, database, mysql
Gorose
GoRose(go orm), a mini database ORM for golang, which inspired by the famous php framwork laravle's eloquent. It will be friendly for php developer and python or ruby developer. Currently provides six major database drivers: mysql,sqlite3,postgres,oracle,mssql, Clickhouse.
Stars: ✭ 947 (+866.33%)
Mutual labels:  sql, database, db
Event Management
helps to register an users for on events conducted in college fests with simple logic with secured way
Stars: ✭ 65 (-33.67%)
Mutual labels:  sql, database, mysql
Dolt
Dolt – It's Git for Data
Stars: ✭ 9,880 (+9981.63%)
Mutual labels:  sql, database, mysql

Node MySql Utilities

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.

TravisCI Codacy Badge NPM Version NPM Downloads/Month NPM Downloads

impress logo

Installation

$ npm install mysql-utilities

Features

  • MySQL Data Access Methods
    • Query selecting single row: connection.queryRow(sql, values, callback)
    • Query selecting scalar (single value): connection.queryValue(sql, values, callback)
    • Query selecting column into array: connection.queryCol(sql, values, callback)
    • Query selecting hash of records: connection.queryHash(sql, values, callback)
    • Query selecting key/value hash: connection.queryKeyValue(sql, values, callback)
  • MySQL Introspection Methods
    • Get primary key metadata: connection.primary(table, callback)
    • Get foreign key metadata: connection.foreign(table, callback)
    • Get table constraints metadata: connection.constraints(table, callback)
    • Get table fields metadata: connection.fields(table, callback)
    • Get connection databases array: connection.databases(callback)
    • Get database tables list for current db: connection.tables(callback)
    • Get database tables list for given db: connection.databaseTables(database, callback)
    • Get table metadata: connection.tableInfo(table, callback)
    • Get table indexes metadata: connection.indexes(table, callback)
    • Get server process list: connection.processes(callback)
    • Get server global variables: connection.globalVariables(callback)
    • Get server global status: connection.globalStatus(callback)
    • Get database users: connection.users(callback)
  • MySQL SQL Statements Autogenerating Methods
    • Selecting record(s): connection.select(table, whereFilter, callback)
    • Inserting record: connection.insert(table, row, callback)
    • Updating record: connection.update(table, row, where, callback)
    • Inserting or selecting record: connection.upsert(table, row, callback)
    • Count records with filter: connection.count(table, whereFilter, callback)
    • Delete record(s): connection.delete(table, whereFilter, callback)
  • Events
    • Catch any query execution: connection.on('query', function(err, res, fields, query) {});
    • Catch errors: connection.on('error', function(err, query) {});
    • Catch slow query execution: connection.on('slow', function(err, res, fields, query, executionTime) {});

Initialization

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();

Examples

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

Contributors

  • Timur Shemsedinov (marcusaurelius)
  • See github for full contributors list

License

Dual licensed under the MIT or RUMI licenses.

Copyright (c) 2012-2021 MetaSystems <[email protected]>

Note that the project description data, including the texts, logos, images, and/or trademarks, for each open source project belongs to its rightful owner. If you wish to add or remove any projects, please contact us at [email protected].