Skip to content

Node.JS SQL Connector supporting MySQL, SQL Server and Oracle Databases

License

Notifications You must be signed in to change notification settings

radixxko/liqd-sql

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

Node.JS SQL connector for MySQL, SQL Server and Oracle Databases

Version npm NPM downloads Build Status Coverage Status MIT License

Table of Contents

Installing

$ npm i liqd-sql

Usage

const SQL = new (require('liqd-sql'))(
{
	mysql :
	{
		host     : 'localhost',
		user     : 'root',
		password : '',
		database : 'test'
	}
});

Create table

SQL.query( config, table ).execute( execute )

  • config {Object}
  • table {String}
  • execute {Boolean}
await SQL.query({
	columns :
	{
		id      : { type: 'BIGINT:UNSIGNED', increment: true },
		name    : { type: 'VARCHAR:255' },
		surname : { type: 'VARCHAR:255' },
		cityID  : { type: 'BIGINT:UNSIGNED' }
	},
	indexes : {
		primary : 'id',
		unique  : [],
		index   : [ 'city' ]
	}
}, 'users' ).create_table( true );

await SQL.query({
	columns :
	{
		id   : { type: 'BIGINT:UNSIGNED', increment: true },
		name : { type: 'VARCHAR:255' }
	},
	indexes : {
		primary : 'id',
		unique  : [],
		index   : [ 'name' ]
	}
}, 'cities' ).create_table( true );

Select

.select_row( [columns = '*'[, data = null]] )

  • columns {String}
  • data {Any}
let data = await SQL.query( 'users' ).select_row();

Output

{
	ok            : true,
	error         : null,
	affected_rows : 1,
	changed_rows  : 0,
	inserted_id   : null,
	inserted_ids  : [],
	changed_id    : null,
	changed_ids   : [],
	row           : { id: 1, name: 'John', surname: 'D.', cityID: 1 },
	rows          : [ { id: 1, name: 'John', surname: 'D.', cityID: 1 } ],
	sql_time      : 1,
	time          : 1,
	query         : 'SELECT * FROM `users` LIMIT 1'
};

.select( [columns = '*'[, data = null]] )

  • columns {String}
  • data {Any}
let data = await SQL.query( 'users' ).select();

Output

{
	ok            : true,
	error         : null,
	affected_rows : 2,
	changed_rows  : 0,
	inserted_id   : null,
	inserted_ids  : [],
	changed_id    : null,
	changed_ids   : [],
	row           : { id: 1, name: 'John', surname: 'D.', cityID: 1 },
	rows          : [ { id: 1, name: 'John', surname: 'D.', cityID: 1 }, { id: 2, name: 'Mark', surname: 'T.', cityID: 1 } ],
	sql_time      : 1,
	time          : 1,
	query         : 'SELECT * FROM `users`'
};

Select query

.select_row_query( [columns = '*'[, data = null[, alias = null]]] )

  • columns {String}
  • data {Any}
  • alias {String}
let data = await SQL.query( 'users' ).select_row_query();

Output

'SELECT * FROM `users` LIMIT 1'

.select_query( [columns = '*'[, data = null[, alias = null]]] )

  • columns {String}
  • data {Any}
  • alias {String}
let data = await SQL.query( 'users' ).select_query();  

Output

SELECT * FROM `users`

Result

.select_row( [columns = '*'[, data = null]] )

{
	ok            : true,
	error         : null,
	affected_rows : 0,
	changed_rows  : 0,
	inserted_id   : null,
	inserted_ids  : [],
	changed_id    : null,
	changed_ids   : [],
	row           : null,
	rows          : [],
	sql_time      : 0,
	time          : 0,
	query         : ''
};
  • ok {Boolean}
  • error {Object}
  • affected_rows {Number}
  • changed_rows {Number}
  • inserted_id
  • inserted_ids {Array}
  • changed_id
  • changed_ids {Array}
  • row {Object}
  • rows {Array}
  • rows {Array}
  • sql_time {Number}
  • time {Number}
  • query {String}

Join

.join( table, condition[, data = null] )

  • table {String}
  • condition {String}
  • data {Any}
let data = await SQL.query( 'users u' ).join( 'cities c', 'u.cityID = c.id' ).select_query( '*' );

Output

SELECT * FROM `users` `u` LEFT JOIN `cities` `c` ON `u`.`cityID` = `c`.`id`

Inner join

.inner_join( table, condition[, data = null] )

  • table {String}
  • condition {String}
  • data {Any}
let data = await SQL.query( 'users u' ).inner_join( 'cities c', 'u.cityID = c.id' ).select_query( '*' );

Output

SELECT * FROM `users` `u` INNER JOIN `work` `w` ON `u`.`id` = `w`.`userID`

Union

.union( union )

  • union {String|Array|Query}

Where

.where( condition[, data = null] )

  • condition {String}
  • data {Any}
let data = await SQL.query( 'users' ).where( ' id > 10 AND name = :?', 'John' ).select_query( '*' );

Output

SELECT * FROM `users` WHERE `id` > 10 AND `name` = 'John'
let data = await SQL.query( 'users' ).where( ' id > 10 ' ).where( 'name = :?', 'John' ).select_query( '*' );

Output

SELECT * FROM `users` WHERE ( `id` > 10 ) AND ( `name` = 'John' )

Order by

.order_by( columns[, data = null] )

  • columns {String}
  • data {Any}
let data = await SQL.query( 'users' ).order_by( 'name ASC, surname DESC' ).select_query( '*' );

Output

SELECT * FROM `users` ORDER BY `name` ASC, `surname` DESC

Group by

  • use one time

.group_by( columns[, data = null] )

  • columns {String}
  • data {Any}
let data = await SQL.query( 'users' ).group_by( 'surname DESC' ).select_query( '*' );

Output

SELECT * FROM `users` GROUP BY `surname`

Having

.having( condition[, data = null] )

  • condition {String}
  • data {Any}
let data = await SQL.query( 'users' ).having( 'id > 3' ).select_query( '*' );

Output

SELECT * FROM `users` HAVING id > 3

Limit

.limit( limit )

  • limit {Number}
let data = await SQL.query( 'users' ).limit( 15 ).select_query( '*' );

Output

SELECT * FROM `users` LIMIT 15

Offset

.offset( offset )

  • offset {Number}
let data = await SQL.query( 'users' ).limit( 15 ).offset( 15 ).select_query( '*' );

Output

SELECT * FROM `users` LIMIT 15 OFFSET 15

Execute

.execute()

let data = await SQL.query( 'SELECT * FROM users' ).execute();

Output

{
	ok            : true,
	error         : null,
	affected_rows : 2,
	changed_rows  : 0,
	inserted_id   : null,
	inserted_ids  : [],
	changed_id    : null,
	changed_ids   : [],
	row           : { id: 1, name: 'John', surname: 'D.', cityID: 1 },
	rows          : [ { id: 1, name: 'John', surname: 'D.', cityID: 1 }, { id: 2, name: 'Mark', surname: 'T.', cityID: 1 } ],
	sql_time      : 1,
	time          : 1,
	query         : 'SELECT * FROM `users`'
};

Update

.update( set[, data = null] )

  • set {String|Array|Object}
  • data {Any}

Update with indexes

let data = await SQL.query( 'users' ).update( { id: 1, name: 'Johnson' } );

Output

{
	ok            : true,
	error         : null,
	affected_rows : 1,
	changed_rows  : 1,
	inserted_id   : null,
	inserted_ids  : [],
	changed_id    : null,
	changed_ids   : [],
	row           : null,
	rows          : [],
	sql_time      : 1,
	time          : 1,
	query         : 'UPDATE `users` SET `id` = CASE WHEN `id` = 1 THEN 1 ELSE `id` END, `name` = CASE WHEN `id` = 1 THEN 'Johnson' ELSE `name` END WHERE ( `id` IN (1) )'
};

Update with where

let data = await SQL.query( 'users' ).where( 'id = 1' ).update( { name: 'Johnson' } );

Output

{
	ok            : true,
	error         : null,
	affected_rows : 1,
	changed_rows  : 1,
	inserted_id   : null,
	inserted_ids  : [],
	changed_id    : null,
	changed_ids   : [],
	row           : null,
	rows          : [],
	sql_time      : 1,
	time          : 1,
	query         : 'UPDATE `users` SET `name` = 'Johnson' WHERE `id` = 1 '
};

Update with string

let data = await SQL.query( 'users' ).where( 'id = 1' ).update( 'name = :?', 'Johnson' } );

Output

{
	ok            : true,
	error         : null,
	affected_rows : 1,
	changed_rows  : 1,
	inserted_id   : null,
	inserted_ids  : [],
	changed_id    : null,
	changed_ids   : [],
	row           : null,
	rows          : [],
	sql_time      : 1,
	time          : 1,
	query         : 'UPDATE `users` SET `name` = 'Johnson' WHERE `id` = 1 '
};

Insert

.insert( data[, ignore = false] )

  • data {Array|Object}
  • ignore {Boolean}
let data = await SQL.query( 'users' ).insert( { id: 1, name: 'John', surname: 'D.' } );

Output

{
	ok            : true,
	error         : null,
	affected_rows : 1,
	changed_rows  : 1,
	inserted_id   : 1,
	inserted_ids  : [ 1 ],
	changed_id    : null,
	changed_ids   : [ 1 ],
	row           : null,
	rows          : [],
	sql_time      : 1,
	time          : 1,
	query         : 'INSERT INTO `users` ( id, name, surname ) VALUES ( 1, 'John', 'D.' )'
};
let data = await SQL.query( 'users' ).insert( { id: 1, name: 'John', surname: 'D.' }, true );

Output

{
	ok            : true,
	error         : null,
	affected_rows : 1,
	changed_rows  : 1,
	inserted_id   : 1,
	inserted_ids  : [ 1 ],
	changed_id    : null,
	changed_ids   : [ 1 ],
	row           : null,
	rows          : [],
	sql_time      : 1,
	time          : 1,
	query         : 'INSERT IGNORE INTO `users` ( id, name, surname ) VALUES ( 1, 'John', 'D.' )'
};

Set

.set( data )

  • data {Array|Object}
let data = await SQL.query( 'users' ).set( { id: 1, name: 'John', surname: 'D.' } );

Output

{
	ok            : true,
	error         : null,
	affected_rows : 1,
	changed_rows  : 1,
	inserted_id   : 1,
	inserted_ids  : [ 1 ],
	changed_id    : null,
	changed_ids   : [ 1 ],
	row           : null,
	rows          : [],
	sql_time      : 1,
	time          : 1
};

About

Node.JS SQL Connector supporting MySQL, SQL Server and Oracle Databases

Resources

License

Stars

Watchers

Forks

Packages

No packages published