Query Builder

Select

Available methods - select / selectRaw / distinct

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

SELECT id, name, age
FROM table

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

SELECT id AS product_id, title AS product_title
FROM table

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

SELECT DISTINCT category
FROM table

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

SELECT one, two, three
FROM table

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

SELECT *
FROM table

From

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

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

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

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

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

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

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

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

SELECT *
FROM table
WHERE id = 10

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

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

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

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

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

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

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

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

SELECT *
FROM table
ORDER BY id ASC

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

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

SELECT *
FROM table
GROUP BY id

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

SELECT *
FROM table
GROUP BY id
HAVING id > 10

Limit & Offset

Available methods - limit / offset

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

SELECT *
FROM table
LIMIT 10

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

SELECT *
FROM table
LIMIT 10 OFFSET 10

Union

Available methods - union / unionAll

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

SELECT something_else
FROM another_table
UNION
SELECT something
FROM table

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

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