the-query-builder

Query Builder

• Select

• From

• Joins

  • LEFT Join
  • RIGHT Join
  • INNER Join
  • Join Raw
  • Advanced Join

• Where Clauses

  • Where
  • Where IN
  • Where BETWEEN
  • Where LIKE
  • Where IS NULL
  • Where Subquery
  • Where Nested

• Ordering, Grouping, Limit & Offset

  • Ordering
  • Grouping
  • Limit & Offset

• Union

Select

Available methods - select / selectRaw / distinct

const sql = (new QueryBuilder())
  .select('id', 'name', 'age')
  .from('table')
  .getSQL();

Generated SQL

SELECT id, name, age
FROM table

Column alias

const sql = (new QueryBuilder())
  .select({
    id: 'product_id',
    title: 'product_title'
  })
  .from('table')
  .getSQL();

Generated SQL:

SELECT id AS product_id, title AS product_title
FROM table

Distinct

const sql = (new QueryBuilder())
  .distinct()
  .select('category')
  .from('table')
  .getSQL();

Generated SQL:

SELECT DISTINCT category
FROM table

selectRaw

const sql = (new QueryBuilder())
  .selectRaw('SELECT one, two, three')
  .from('table')
  .getSQL();

Generated SQL

SELECT one, two, three
FROM table

By default, all columns will be selected from table.

const sql = (new QueryBuilder())
  .from('table')
  .getSQL();

Generated SQL

SELECT *
FROM table

From

const sql = (new QueryBuilder())
  .from('table')
  .getSQL();

Set multiple FROM

const sql = (new QueryBuilder())
  .from('table')
  .from('another_table')
  .getSQL();

Generated SQL

SELECT *
FROM table,
     another_table

Joins

Available methods - leftJoin / rightJoin / innerJoin / joinRaw / join

LEFT Join

const sql = (new QueryBuilder())
  .from('table')
  .leftJoin('another_table', 'id', 'another_id')
  .getSQL();

Generated SQL

SELECT *
FROM table
         LEFT JOIN another_table ON id = another_id

RIGHT Join

const sql = (new QueryBuilder())
  .from('table')
  .rightJoin('another_table', 'id', 'another_id')
  .getSQL();

Generated SQL

SELECT *
FROM table
         RIGHT JOIN another_table ON id = another_id

INNER Join

const sql = (new QueryBuilder())
  .from('table')
  .innerJoin('another_table', 'id', 'another_id')
  .getSQL();

Generated SQL

SELECT *
FROM table
         INNER JOIN another_table ON id = another_id

Join Raw

const sql = (new QueryBuilder())
  .from('table')
  .joinRaw('LEFT JOIN another_table t ON id = another_id')
  .getSQL();

Generated SQL

SELECT *
FROM table
         LEFT JOIN another_table t ON id = another_id

Advanced Join

  const sql = (new QueryBuilder())
  .from('table')
  .join(qb => {
    qb
      .leftJoin('another_table')
      .on('id', 'another_id')
      .and('other_column', 'something')
      .or('other_column', 'other_thing')
  })
  .getSQL();

Generated SQL

SELECT *
FROM table
         LEFT JOIN another_table ON id = another_id AND other_column = 'something' OR other_column = 'other_thing'

Where Clauses

Where

Available methods - where / orWhere

const sql = (new QueryBuilder())
  .where('id', 10)
  .from('table')
  .getSQL();

Generated SQL

SELECT *
FROM table
WHERE id = 10

Set multiple WHERE

const sql = (new QueryBuilder())
  .where('id', 10)
  .where('status', 'published')
  .from('table')
  .getSQL();

Generated SQL

SELECT *
FROM table
WHERE id = 10
  AND status = 'published'

Where IN

Available methods - whereIn / orWhereIn / whereNotIn / orWhereNotIn

const sql = (new QueryBuilder())
  .from('table')
  .whereIn('id', [10, 100])
  .getSQL();

Generated SQL

SELECT *
FROM table
WHERE id IN (10, 100)

Where BETWEEN

Available methods - whereBetween / orWhereBetween / whereNotBetween / orWhereNotBetween

const sql = (new QueryBuilder())
  .from('table')
  .whereBetween('id', 10, 100)
  .getSQL();

Generated SQL

SELECT *
FROM table
WHERE id BETWEEN 10 AND 100

Where LIKE Clauses

Available methods - whereLike / orWhereLike / whereNotLike / orWhereNotLike

const sql = (new QueryBuilder())
  .from('table')
  .whereLike('status', 'something')
  .getSQL();

Generated SQL

SELECT *
FROM table
WHERE status LIKE '%something%'

Where IS NULL

Available methods - whereIsNull / orWhereIsNull / whereIsNotNull / orWhereIsNotNull

const sql = (new QueryBuilder())
  .from('table')
  .whereIsNull('id')
  .getSQL();

Generated SQL

SELECT *
FROM table
WHERE id IS NULL

Where Subquery

const sql = (new QueryBuilder())
  .from('table')
  .whereIn('id', qb => {
    qb
      .select('another_id')
      .from('another_table')
      .where('category', 'something')
  })
  .getSQL();

Generated SQL

SELECT *
FROM table
WHERE id IN (SELECT another_id FROM another_table WHERE category = 'something')

Where Nested

const sql = (new QueryBuilder())
  .from('table')
  .where('status', 'published')
  .orWhere(qb => {
    qb
      .where('status', 'draft')
      .where('writing', 'locked')
  })
  .getSQL();

Generated SQL

SELECT *
FROM table
WHERE status = 'published'
   OR (status = 'draft' AND writing = 'locked')

Ordering, Grouping, Limit & Offset

Ordering

const sql = (new QueryBuilder())
  .from('table')
  .orderBy('id')
  .getSQL();

Generated SQL

SELECT *
FROM table
ORDER BY id ASC

Order by multiple columns

const sql = (new QueryBuilder())
  .from('table')
  .orderBy('id')
  .orderBy('column_one', 'DESC')
  .getSQL();

Generated SQL

SELECT *
FROM table
ORDER BY id ASC, column_one DESC

Grouping

Available methods - groupBy / having / orHaving / havingCount / orHavingCount / havingMin / orHavingMin / havingMax / orHavingMax / havingAvg / orHavingAvg / havingSum / orHavingSum / havingRaw

const sql = (new QueryBuilder())
  .from('table')
  .groupBy('id')
  .getSQL();

Generated SQL

SELECT *
FROM table
GROUP BY id

Having

const sql = (new QueryBuilder())
  .from('table')
  .groupBy('id')
  .having('id', '>', 10)
  .getSQL();

Generated SQL

SELECT *
FROM table
GROUP BY id
HAVING id > 10

Limit & Offset

Available methods - limit / offset

Limit

const sql = (new QueryBuilder())
  .from('table')
  .limit(10)
  .getSQL();

Generated SQL

SELECT *
FROM table
LIMIT 10

Offset

const sql = (new QueryBuilder())
  .from('table')
  .limit(10)
  .offset(10)
  .getSQL();

Generated SQL

SELECT *
FROM table
LIMIT 10 OFFSET 10

Union

Available methods - union / unionAll

Union

const builder1 = (new QueryBuilder())
  .select('something')
  .from('table');

const sql = (new QueryBuilder())
  .select('something_else')
  .from('another_table')
  .union(builder1)
  .getSQL();

Generated SQL

SELECT something_else
FROM another_table
UNION
SELECT something
FROM table

Union ALL

const builder1 = (new QueryBuilder())
  .select('something')
  .from('table');

const builder2 = (new QueryBuilder())
  .select('another_thing')
  .from('another_table');

const sql = (new QueryBuilder())
  .select('something_else')
  .from('some_table')
  .unionAll(builder1, builder2)
  .getSQL();

Generated SQL

SELECT something_else
FROM some_table
UNION ALL
SELECT something
FROM table
UNION ALL
SELECT another_thing
FROM another_table