❤
npm
Sign UpSign In

liqd-sql

1.6.2 • Public • Published

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

Readme

Keywords

Package Sidebar

Install

npm i liqd-sql

Repository

github.com/radixxko/liqd-sql

Homepage

github.com/radixxko/liqd-sql#readme

Weekly Downloads

7

Version

1.6.2

License

MIT

Unpacked Size

269 kB

Total Files

13

Last publish

Collaborators

  • radixxko
Try on RunKit
Report malware