Query Builder

The query builder provides a convenient and smooth interface for creating and running database queries. It can be used to perform most database operations in your application and works perfectly with all database systems supported by Kawkab.

Kawkab’s query builder allows you to write and execute SQL queries.

We have divided the query builders into the following categories:

• The standard query builder allows you to build SQL queries for select, update, and delete operations.
• The insert query builder allows you to build SQL queries for insert operations.
• The raw query builder allows you to write and execute queries from a raw SQL string.

Running Database Queries

Running SQL Queries

Once you have configured your database connection, you can use the raw method to run a basic query:

import { db } from "kawkab";
 
const response = await db.raw('SET TIME_ZONE = ?', ['UTC']);

The response will be what the underlying SQL library (such as mysql2) returns for a normal query, so you might want to check the documentation of the underlying library on which the query is executed to determine how to handle the response.

Retrieving All Rows from a Table

You can use the table method provided by DB to start a query. The table method returns a fluent query builder instance for the specified table, allowing you to add more constraints to the query and then retrieve the query results using the get method:

import { db } from "kawkab";
 
const users = await db.table('users').get();

The get method returns an array containing the results of the query, where each result is an object. You can access the value of each column by accessing the column as a property of the object:

const users = await db.table('users').get();
 
users.map(user => {
  console.log(user.name);
})

Retrieving a Single Row or Column from a Table

If you only need to retrieve a single row from a database table, you can use the first method:

const user = await db.table('users').where('name', 'John').first();
 
console.log(user.email);

To retrieve a single row by its id column value, use the find method:

const user = await db.table('users').find(3);

Retrieving a List of Column Values

If you want to retrieve an array containing the values of a single column, you can use the pluck method. In this example, we will retrieve a set of user titles:

const titles = await db.table('users').pluck('title');
 
titles.map(title => {
  console.log(title)
});

Chunking Results

If you need to work with thousands of database records, consider using the chunk method. This method retrieves a small chunk of the results at a time and feeds each chunk into a closure for processing. For example, let’s retrieve the entire users table in chunks of 100 records at a time:

await db.table('users').orderBy('id').chunk(100, users => {
  users.map(user => {
    // Do something...
  })
});

You can stop processing additional chunks by returning false from the closure:

await db.table('users').orderBy('id').chunk(100, users => {
  // Process the records...
 
  return false;
});

Aggregates

The query builder also provides a variety of methods for retrieving aggregate values such as count, max, min, avg, and sum. You can call any of these methods after building your query:

const count = await db.table('users').count();
 
const price = await db.table('orders').max('price');

Of course, you can combine these methods with other clauses to modify how your aggregate value is calculated:

const price = await db.table('orders')
  .where('finalized', 1)
  .avg('price');

Determining If Records Exist

Instead of using the count method to determine if any records exist that match your query’s constraints, you can use the exists methods:

const isExists = await table('orders').where('finalized', 1).exists()
if (isExists) {
    // ...
}

Select Statements

Specifying the Select Clause

You may not always want to select all columns from a database table. Using the select method, you can specify a custom “select” clause for the query:

const users = await db.table('users')
  .select('name', 'email as user_email')
  .get();

The distinct method forces the query to return distinct results:

const users = await db.table('users').distinct().get();

Raw Expressions

Sometimes you may need to insert a raw string into a query. To create a raw string expression, you can use the raw method:

const users = await db.table('users')
  .select(db.raw('count(*) as user_count, status'))
  .where('status', '<>', 1)
  .groupBy('status')
  .get();

Raw Methods

Instead of using the raw method, you can also use the following methods to insert a raw expression into various parts of your query. Kawkab cannot guarantee that any query using raw expressions is protected against SQL injection vulnerabilities.

whereRaw

The whereRaw methods can be used to insert a raw “where” clause into your query. These methods accept an optional array of bindings as the second argument:

const orders = await db.table('orders')
  .whereRaw('price > IF(state = "TX", ?, 100)', [200])
  .get();

havingRaw

The havingRaw and orHavingRaw methods can be used to provide a raw string as the value for a “having” clause. These methods accept an optional array of bindings as the second argument:

const orders = await db.table('orders')
  .select('department', db.raw('SUM(price) as total_sales'))
  .groupBy('department')
  .havingRaw('SUM(price) > ?', [2500])
  .get();

orderByRaw

The orderByRaw method can be used to provide a raw string as the value for an “order by” clause:

const orders = await db.table('orders')
  .orderByRaw('updated_at - created_at DESC')
  .get();

groupByRaw

The groupByRaw method can be used to provide a raw string as the value for a “group by” clause:

const orders = await db.table('orders')
  .select('city', 'state')
  .groupByRaw('city, state')
  .get();

Joins

Basic Join Clause

The query builder can also be used to add join clauses to your queries. To perform a basic “inner join,” you can use the join method on a query builder instance. The name of the table you need to join is passed as the first argument to the join method, while the remaining arguments specify the column constraints for the join. You can even join multiple tables in a single query:

const users = await db.table('users')
  .join('contacts', 'users.id', '=', 'contacts.user_id')
  .join('orders', 'users.id', '=', 'orders.user_id')
  .select('users.*', 'contacts.phone', 'orders.price')
  .get();

Left Join / Right Join Clause

If you want to perform a “left join” or “right join” instead of an “inner join,” use the leftJoin or rightJoin methods. These methods have the same signature as the join method:

const users = await db.table('users')
  .leftJoin('posts', 'users.id', '=', 'posts.user_id')
  .get();
 
const users = await db.table('users')
  .rightJoin('posts', 'users.id', '=', 'posts.user_id')
  .get();

Cross Join Clause

You can use the crossJoin method to perform a “cross join.” Cross joins generate a Cartesian product between the first table and the joined table:

const sizes = await db.table('sizes')
  .crossJoin('colors')
  .get();

Advanced Join Clauses

You can also specify more advanced join clauses. To get started, pass a closure as the second argument to the join method.

await db.table('users')
  .join('contacts', () => {
    this.on('users.id', '=', 'contacts.user_id').orOn(/* ... */);
  })
  .get();

Unions

The query builder also provides a convenient way to “union” two or more queries together. For example, you can create an initial query and use the union method to union it with more queries:

const first = db.table('users')
  .whereNull('first_name');
 
const users = await db.table('users')
  .whereNull('last_name')
  .union(first)
  .get();

In addition to the union method, the query builder provides a unionAll method. Queries combined using the unionAll method will not have their duplicate results removed. The unionAll method has the same signature as the union method.

Basic Where Clauses

Where Clauses

You can use the where method of the query builder to add “where” clauses to the query. The most basic call to the where method requires three arguments. The first argument is the column name. The second argument is the operator, which can be any of the operators supported by your database. The third argument is the value to compare against the column’s value.

For example, the following query retrieves users where the value of the votes column is equal to 100 and the value of the age column is greater than 35:

const users = await db.table('users')
  .where('votes', '=', 100)
  .where('age', '>', 35)
  .get();

For convenience, if you want to verify that a column is equal to a given value, you can pass the value as the second argument to the where method. Kawkab will assume you want to use the = operator:

const users = await db.table('users').where('votes', 100).get();

As mentioned previously, you can use any operator supported by your database system:

const users = await db.table('users')
  .where('votes', '>=', 100)
  .get();
 
const users = await db.table('users')
  .where('votes', '<>', 100)
  .get();
 
const users = await db.table('users')
  .where('name', 'like', 'T%')
  .get();

Or Where Clauses

When chaining where method calls on the query builder, the where clauses will be joined using the and operator. However, you can use the orWhere method to join a clause to the query using the or operator. The orWhere method accepts the same arguments as the where method:

const users = await db.table('users')
  .where('votes', '>', 100)
  .orWhere('name', 'John')
  .get();

If you need to group an “or” condition within parentheses, you can pass a closure as the first argument to the orWhere method:

const users = await db.table('users')
  .where('votes', '>', 100)
  .orWhere(query => {
    query.where('name', 'Abigail')
      .where('votes', '>', 50);
  })
  .get();

The example above will produce the following SQL:

select * from users where votes > 100 or (name = 'Abigail' and votes > 50)

Where Not Clauses

The whereNot and orWhereNot methods can be used to negate a set of query constraints. For example, the following query excludes products that are on clearance or have a price less than ten:

const products = await db.table('products')
  .whereNot(() => {
    this.where('clearance', true).orWhere('price', '<', 10);
  })
  .get();

Additional Where Clauses

whereBetween / orWhereBetween

The whereBetween method verifies that a column’s value is between two values:

const users = await db.table('users')
  .whereBetween('votes', [1, 100])
  .get();

whereNotBetween / orWhereNotBetween

The whereNotBetween method verifies that a column’s value is outside of two values:

const users = await db.table('users')
  .whereNotBetween('votes', [1, 100])
  .get();

whereIn / whereNotIn / orWhereIn / orWhereNotIn

The whereIn method verifies that a given column’s value is contained within the given array:

const users = await db.table('users')
  .whereIn('id', [1, 2, 3])
  .get();

The whereNotIn method verifies that a given column’s value is not contained in the given array:

const users = await db.table('users')
  .whereNotIn('id', [1, 2, 3])
  .get();

whereNull / whereNotNull / orWhereNull / orWhereNotNull

The whereNull method verifies that a given column’s value is NULL:

const users = await db.table('users')
  .whereNull('updated_at')
  .get();

The whereNotNull method verifies that a column’s value is not NULL:

const users = await db.table('users')
  .whereNotNull('updated_at')
  .get();

WhereX

There is a neat way to transform this:

const users = await User.query().where('approved', 1).get();
const posts = await Post.query().where('views_count', '>', 100).get();

Into this:

const users = await User.query().whereApproved(1).get();
const posts = await Post.query().whereViewsCount('>', 100).get();

Logical Grouping

Sometimes you may need to group several “where” clauses within parentheses to achieve the desired logical grouping for your query. In fact, you should generally always group orWhere method calls within parentheses to avoid unexpected query behavior. To achieve this, you can pass a closure to the where method:

const users = await db.table('users')
  .where('name', '=', 'John')
  .where(() => {
    this.where('votes', '>', 100).orWhere('title', '=', 'Admin');
  })
  .get();

As you can see, passing a closure to the where method instructs the query builder to begin a constraint group. The closure will receive a query builder instance which you can use to set the constraints that should be contained within the parentheses group. The example above will produce the following SQL:

select * from users where name = 'John' and (votes > 100 or title = 'Admin')

Ordering, Grouping, Limit, & Offset

Ordering

orderBy Method

The orderBy method allows you to sort the results of the query by a given column. The first argument accepted by the orderBy method should be the column you wish to sort by, while the second argument determines the direction of the sort and can be either asc or desc:

const users = await db.table('users')
  .orderBy('name', 'desc')
  .get();

To sort by multiple columns, you can simply call orderBy as many times as needed:

const users = await db.table('users')
  .orderBy('name', 'desc')
  .orderBy('email', 'asc')
  .get();

latest & oldest Methods

The latest and oldest methods allow you to easily order results by date. By default, the result will be ordered by the created_at column of the table. Or, you can pass the name of the column you wish to sort by:

const user = await db.table('users')
  .latest()
  .first();

Random Ordering

The inRandomOrder method may be used to sort the results of the query randomly. For example, you can use this method to get a random user:

const randomUser = await db.table('users')
  .inRandomOrder()
  .first();

Removing Existing Orders

The clearOrder method removes all previously applied “order by” clauses from the query:

const query = db.table('users').orderBy('name');
 
const unorderedUsers = await query.clearOrder().get();

Grouping

groupBy & having Methods

As you might expect, the groupBy and having methods can be used to group the results of the query. The signature of the having method is similar to the signature of the where method:

const users = await db.table('users')
  .groupBy('account_id')
  .having('account_id', '>', 100)
  .get();

You can use the havingBetween method to filter results within a given range:

const report = await db.table('orders')
  .selectRaw('count(id) as number_of_orders, customer_id')
  .groupBy('customer_id')
  .havingBetween('number_of_orders', [5, 15])
  .get();

You can pass multiple arguments to the groupBy method to group by multiple columns:

const users = await db.table('users')
  .groupBy('first_name', 'status')
  .having('account_id', '>', 100)
  .get();

To build more advanced “having” clauses, see the havingRaw method.

Limit & Offset

skip & take Methods

You can use the skip and take methods to specify the number of results to return from the query or to skip a given number of results in the query:

const users = await db.table('users').skip(10).take(5).get();

Alternatively, you can use the limit and offset methods. These methods are functionally equivalent to the take and skip methods, respectively:

const users = await db.table('users')
  .offset(10)
  .limit(5)
  .get();

Insert Statements

The query builder also provides an insert method that can be used to insert records into a database table. The insert method accepts an array of column names and values:

await db.table('users').insert({
  email: 'kayla@example.com',
  votes: 0
});

You can insert multiple records at once by passing an array of arrays. Each array represents a record to be inserted into the table:

await db.table('users').insert([
  { email: 'picard@example.com', votes: 0 },
  { email: 'janeway@example.com', votes: 0 },
]);

Update Statements

In addition to inserting records into the database, the query builder can also update existing records using the update method. The update method, like the insert method, accepts an array of column and value pairs indicating the columns to be updated. The update method returns the number of affected rows. You can constrain the update query using where clauses:

await db.table('users')
  .where('id', 1)
  .update({
    votes: 1
  });

Increments & Decrements

The query builder also provides convenient methods for incrementing or decrementing the value of a given column. Each of these methods accepts at least one argument: the column to modify. A second argument may be provided to specify the amount by which the column should be incremented or decremented:

await db.table('users').increment('votes');
 
await db.table('users').increment('votes', 5);
 
await db.table('users').decrement('votes');
 
await db.table('users').decrement('votes', 5);

Delete Statements

The query builder’s delete method can be used to delete records from the table. The delete method returns the number of affected rows. You can constrain delete statements by adding where clauses before calling the delete method:

const deleted = await db.table('users').delete();
 
const deleted = await db.table('users').where('votes', '>', 100).delete();

Pessimistic Locking

The query builder also includes a few functions to help you achieve “pessimistic locking” when executing your select statements. To execute a statement with a “shared lock,” you can call the forShare method. The shared lock prevents the selected rows from being modified until your transaction is committed:

await db.table('users')
  .where('votes', '>', 100)
  .forShare()
  .get();

Alternatively, you can use the forUpdate method. The “for update” lock prevents the selected records from being modified or selected with another shared lock:

await db.table('users')
  .where('votes', '>', 100)
  .forUpdate()
  .get();