gukandrew / usql

Tiny, zero-dependency SQL query generator

µSQL

An easy-to-use super tiny flexible and 0-dependency SQL query builder with Knex compatible API! Work both in browser and as node package.

Installation

yarn

yarn add usql

npm

npm install usql

Usage

import USql from 'usql'

const sql = new USql('table').where({ 'column': '5', 'column2': '4' })

Then sql.toString() will produce:

SELECT * FROM `table` WHERE `column` = "5" AND `column2` = "4"

API

Column selection

select — .select([*columns])

new USql('books').select('title', 'author', 'year')

Result:

SELECT `title`, `author`, `year` FROM `books`

Actually select is totally optional. When it isn't set then * will be used:

new USql('books')

Result:

SELECT * FROM `books`

Where Methods

where — .where(mixed)

Object Syntax:

new USql('table').where({
  first_name: 'Test',
  last_name:  'User'
}).select('id')

Result:

SELECT `id` FROM `users` WHERE `first_name` = 'Test' AND `last_name` = 'User'

Key, Value:

new USql('table').where('id', 1).where('info', null)

Result:

SELECT * FROM `users` WHERE `id` = "1" AND `info` IS NULL

Could be chained with other methods and with itself:

new USql('table').where('id', 1).whereNot('role', 'admin').orWhere({ 'created_at': Date.now() }).where({ 'is_deleted': 0 })

Result:

SELECT * FROM `table` WHERE `id` = "1" AND `role` != "admin" OR `created_at` = "1576417577608" AND `is_deleted` = "0"

---

whereNot — .whereNot(mixed)

Object Syntax:

new USql('table').whereNot({
  first_name: 'Test',
  last_name:  'User'
}).select('id')

Result:

SELECT `id` FROM `users` WHERE `first_name` != 'Test' AND `last_name` != 'User'

Key, Value:

new USql('table').whereNot('id', 1).whereNot('name', null)

Result:

SELECT * FROM `users` WHERE `id` != "1" AND `name` IS NOT NULL

Could be chained with other methods and with itself.

---

orWhere — .orWhere(mixed)

Object Syntax:

new USql('table').orWhere({
  first_name: 'Test',
  last_name:  'User'
}).select('id')

Result:

SELECT `id` FROM `users` WHERE `first_name` != 'Test' OR `last_name` != 'User'

Key, Value:

new USql('table').orWhere('id', 1).orWhere('name', null)

Result:

SELECT * FROM `users` WHERE `id` != "1" OR `name` IS NOT NULL

Could be chained with other methods and with itself.

---

Join method

join — .join(table, first, [operator], second)

Syntax:

new USql('table')
  .join('contacts', 'users.id', '=', 'contacts.user_id')
  .select('id')

Result:

SELECT `id` FROM `table` JOIN `contacts` ON `users`.`id` = `contacts`.`user_id`

You can omit the operator value:

new USql('table')
  .join('contacts', 'users.id', 'contacts.user_id')
  .select('id')

Result:

SELECT `id` FROM `table` JOIN `contacts` ON `users`.`id` = `contacts`.`user_id`

Could be chained with other methods and with itself.

---

ClearClauses

orderBy — .orderBy(column|columns, [direction])

Adds an order by clause to the query. column can be string, or list mixed with string and object.

new USql('table')
  .orderBy('table1.column1_value', 'desc')

Result:

SELECT * FROM `table1` ORDER BY `table1`.`column1_value` desc

Multiple orderBy syntax:

new USql('table')
  .orderBy('table1.column1_value', 'desc')
  .orderBy('table1.column2_value', 'asc')

Result:

SELECT * FROM `table1` ORDER BY `table1`.`column1_value` desc, `table1`.`column2_value` asc

---

limit — .limit(value)

Adds a limit clause to the query.

new USql('table').limit(2)

Result:

SELECT * FROM `table1` LIMIT 2

---

offset — .offset(value)

Adds an offset clause to the query. Doesn't work without explicit set of limit value

new USql('table').limit(2).offset(5)

Result:

SELECT * FROM `table1` LIMIT 5, 2

as — .as(name)

Allows for aliasing a subquery, taking the string you wish to name the current query. If the query is not a sub-query, it will be ignored.

new USql('table').select('column').as('subquery')

Result:

(SELECT `column` FROM `table`) as `subquery`

Usage:

const subquery = new USql('groups').select('groups.name').where('users.group_id', USql.raw('`groups`.`id`')).as('group_name')

const sql = new USql('users').select('users.*', subquery)

Result:

SELECT `users`.*, (SELECT `groups`.`name` FROM `groups` WHERE `users`.`group_id` = `groups`.`id`) as `group_name` FROM `users`

---

Raw queries

raw — raw(statement)

Run an arbitrary sql query in the schema builder chain.

Syntax:

new USql('users').select(DB.raw('count(*) as item_number'))

Result:

SELECT count(*) as item_number FROM `table`

Raw supported mostly everywhere including: select, where statments, join (for example for table aliasing) and order by column name.