QueryBuilder

Overview

The QueryBuilder class provides a fluent interface for building and executing queries against your spreadsheet data. It supports filtering, sorting, joining, grouping, and aggregation operations.

Creating a Query Builder

// Via table() method
const query = db.table('Users');

// Via Model
const query = User.query();

Select Methods

select()

Specify which columns to retrieve.

const users = db.table('Users')
  .select('id', 'name', 'email')
  .get();

const names = db.table('Users')
  .select('name')
  .get();

...cols

string

Column names to select. If empty, selects all columns

return

QueryBuilder

Returns the query builder for chaining

distinct()

Retrieve only unique/distinct results.

const uniqueStatuses = db.table('Users')
  .select('status')
  .distinct()
  .get();

return

QueryBuilder

Returns the query builder for chaining

Where Clauses

where()

Add a basic where clause to filter results.

// Simple equality
const activeUsers = db.table('Users')
  .where('status', 'active')
  .get();

// With operator
const highValueOrders = db.table('Orders')
  .where('total', '>', 100)
  .get();

// Multiple conditions (AND)
const users = db.table('Users')
  .where('status', 'active')
  .where('role', 'admin')
  .get();

column

string

required

Column name to filter

operator

string

Comparison operator: ==, ===, !=, <, >, <=, >= (defaults to == if value is provided as second argument)

value

any

required

Value to compare against

return

QueryBuilder

Returns the query builder for chaining

orWhere()

Add an OR where clause.

const users = db.table('Users')
  .where('role', 'admin')
  .orWhere('role', 'moderator')
  .get();

column

string

required

Column name to filter

operator

string

Comparison operator (defaults to ==)

value

any

required

Value to compare against

return

QueryBuilder

Returns the query builder for chaining

whereIn()

Filter where column value is in an array.

const users = db.table('Users')
  .whereIn('status', ['active', 'pending', 'verified'])
  .get();

column

string

required

Column name to filter

values

array

required

Array of values to match

return

QueryBuilder

Returns the query builder for chaining

whereNotIn()

Filter where column value is not in an array.

const users = db.table('Users')
  .whereNotIn('role', ['banned', 'suspended'])
  .get();

column

string

required

Column name to filter

values

array

required

Array of values to exclude

return

QueryBuilder

Returns the query builder for chaining

whereNull()

Filter where column value is null or empty.

const usersWithoutEmail = db.table('Users')
  .whereNull('email')
  .get();

column

string

required

Column name to check

return

QueryBuilder

Returns the query builder for chaining

whereNotNull()

Filter where column value is not null or empty.

const verifiedUsers = db.table('Users')
  .whereNotNull('verified_at')
  .get();

column

string

required

Column name to check

return

QueryBuilder

Returns the query builder for chaining

whereBetween()

Filter where column value is between two values.

const orders = db.table('Orders')
  .whereBetween('total', [100, 500])
  .get();

column

string

required

Column name to filter

range

array

required

Array with two elements [min, max]

return

QueryBuilder

Returns the query builder for chaining

Joins

join()

Add an inner join clause.

const results = db.table('Orders')
  .join('Users', 'Orders.user_id', '==', 'Users.id')
  .select('Orders.*', 'Users.name')
  .get();

table

string

required

Table to join

left

string

required

Left column for join condition

operator

string

Comparison operator (defaults to ==)

right

string

required

Right column for join condition

return

QueryBuilder

Returns the query builder for chaining

leftJoin()

Add a left join clause.

const results = db.table('Users')
  .leftJoin('Orders', 'Users.id', 'Orders.user_id')
  .get();

table

string

required

Table to join

left

string

required

Left column for join condition

operator

string

Comparison operator (defaults to ==)

right

string

required

Right column for join condition

return

QueryBuilder

Returns the query builder for chaining

Ordering & Limiting

orderBy()

Sort results by a column.

const users = db.table('Users')
  .orderBy('created_at', 'desc')
  .get();

// Multiple columns
const users = db.table('Users')
  .orderBy('role', 'asc')
  .orderBy('name', 'asc')
  .get();

column

string

required

Column name to sort by

dir

string

default:"asc"

Sort direction: asc or desc

return

QueryBuilder

Returns the query builder for chaining

limit()

Limit the number of results.

const topUsers = db.table('Users')
  .orderBy('points', 'desc')
  .limit(10)
  .get();

number

required

Maximum number of results to return

return

QueryBuilder

Returns the query builder for chaining

offset()

Skip a number of results.

const users = db.table('Users')
  .offset(20)
  .limit(10)
  .get();

number

required

Number of results to skip

return

QueryBuilder

Returns the query builder for chaining

Grouping & Aggregation

groupBy()

Group results by one or more columns.

const statusGroups = db.table('Users')
  .groupBy('status')
  .get();

const roleStatusGroups = db.table('Users')
  .groupBy('role', 'status')
  .get();

...cols

string

required

Column names to group by

return

QueryBuilder

Returns the query builder for chaining

Grouped results include count and items properties for each group.

having()

Filter groups by a condition.

const popularStatuses = db.table('Users')
  .groupBy('status')
  .having('count', '>', 10)
  .get();

column

string

required

Column name to filter (use ‘count’ for group count)

operator

string

Comparison operator (defaults to ==)

value

any

required

Value to compare against

return

QueryBuilder

Returns the query builder for chaining

Retrieval Methods

get()

Execute the query and get results as a Collection.

const users = db.table('Users')
  .where('status', 'active')
  .get();

users.each(user => {
  console.log(user.name);
});

return

Collection

Returns a Collection of results (or Model instances if using a Model query)

first()

Retrieve the first result.

const user = db.table('Users')
  .where('email', '[email protected]')
  .first();

if (user) {
  console.log(user.name);
}

return

object|Model|null

Returns the first result or null if no results found

pluck()

Retrieve an array of values for a single column.

const emails = db.table('Users')
  .where('status', 'active')
  .pluck('email');

console.log(emails); // ['[email protected]', '[email protected]', ...]

column

string

required

Column name to extract values from

return

array

Returns an array of column values

count()

Get the count of matching records.

const activeCount = db.table('Users')
  .where('status', 'active')
  .count();

console.log(`${activeCount} active users`);

return

number

Returns the number of matching records

exists()

Check if any matching records exist.

const hasAdmin = db.table('Users')
  .where('role', 'admin')
  .exists();

if (hasAdmin) {
  console.log('Admin user found');
}

return

boolean

Returns true if at least one record matches, false otherwise

paginate()

Retrieve paginated results.

const result = db.table('Users')
  .where('status', 'active')
  .orderBy('created_at', 'desc')
  .paginate(15, 2);

console.log(result.data); // Collection of results
console.log(result.current_page); // 2
console.log(result.total); // 150
console.log(result.last_page); // 10

// Or with options object
const result = db.table('Users')
  .paginate({ perPage: 25, page: 3 });

perPage

number|object

default:"15"

Items per page, or options object with perPage and page properties

page

number

default:"1"

Current page number

return

object

Returns pagination object with properties:

data: Collection of items for current page
total: Total number of matching records
per_page: Items per page
current_page: Current page number
last_page: Last page number
from: First item number on page (or null)
to: Last item number on page (or null)

Write Operations

insert()

Insert a new record.

const inserted = db.table('Users')
  .insert({
    name: 'John Doe',
    email: '[email protected]',
    status: 'active'
  });

console.log(inserted.id);

obj

object

required

Object with column-value pairs to insert

return

object

Returns the inserted object with ID and row number

update()

Update matching records.

const updatedCount = db.table('Users')
  .where('status', 'pending')
  .update({ status: 'active' });

console.log(`Updated ${updatedCount} users`);

patch

object

required

Object with column-value pairs to update

return

number

Returns the number of records updated

updateById()

Update a specific record by ID.

const updated = db.table('Users')
  .updateById(42, { 
    name: 'Updated Name',
    status: 'verified'
  });

any

required

ID of the record to update

patch

object

required

Object with column-value pairs to update

return

number

Returns the number of records updated (0 or 1)

delete()

Delete matching records.

const deletedCount = db.table('Users')
  .where('status', 'spam')
  .delete();

console.log(`Deleted ${deletedCount} users`);

return

number

Returns the number of records deleted

deleteById()

Delete a specific record by ID.

const deleted = db.table('Users')
  .deleteById(42);

any

required

ID of the record to delete

return

number

Returns the number of records deleted (0 or 1)

deleteRow()

Delete a specific row by row number.

db.table('Users').deleteRow(10);

rowNumber

number

required

Row number to delete

return

number

Returns 1

increment()

Increment a column’s value.

const updated = db.table('Users')
  .where('id', 42)
  .increment('login_count', 1);

// Default increment is 1
db.table('Products')
  .where('id', 10)
  .increment('views');

column

string

required

Column name to increment

number

default:"1"

Amount to increment by

return

number

Returns the number of records updated

decrement()

Decrement a column’s value.

const updated = db.table('Products')
  .where('id', 10)
  .decrement('stock', 5);

column

string

required

Column name to decrement

number

default:"1"

Amount to decrement by

return

number

Returns the number of records updated

Aggregates

sum()

Get the sum of a column’s values.

const totalRevenue = db.table('Orders')
  .where('status', 'completed')
  .sum('total');

console.log(`Total: $${totalRevenue}`);

column

string

required

Column name to sum

return

number

Returns the sum of matching records

avg()

Get the average of a column’s values.

const avgPrice = db.table('Products')
  .avg('price');

console.log(`Average price: $${avgPrice}`);

column

string

required

Column name to average

return

number

Returns the average of matching records

max()

Get the maximum value of a column.

const highestScore = db.table('Scores')
  .max('points');

column

string

required

Column name to find maximum

return

number|null

Returns the maximum value or null if no records

min()

Get the minimum value of a column.

const lowestPrice = db.table('Products')
  .where('in_stock', true)
  .min('price');

column

string

required

Column name to find minimum

return

number|null

Returns the minimum value or null if no records

Advanced Features

with()

Eager load relationships (when using Models).

const users = User.query()
  .with('posts', 'comments')
  .get();

users.each(user => {
  console.log(user.posts); // Already loaded
});

...relations

string

required

Names of relationships to eager load

return

QueryBuilder

Returns the query builder for chaining

scope()

Apply a model scope (when using Models).

const activeUsers = User.query()
  .scope('active')
  .get();

...scopes

string

required

Names of scopes to apply

return

QueryBuilder

Returns the query builder for chaining

Complete Example

// Complex query with multiple clauses
const results = db.table('Orders')
  .join('Users', 'Orders.user_id', 'Users.id')
  .join('Products', 'Orders.product_id', 'Products.id')
  .select('Orders.*', 'Users.name as user_name', 'Products.name as product_name')
  .where('Orders.status', 'completed')
  .where('Orders.total', '>', 100)
  .whereIn('Users.country', ['US', 'CA', 'UK'])
  .orderBy('Orders.created_at', 'desc')
  .limit(50)
  .get();

// Pagination
const page = db.table('Users')
  .where('status', 'active')
  .orderBy('name')
  .paginate({ perPage: 20, page: 2 });

console.log(`Showing ${page.from}-${page.to} of ${page.total}`);
page.data.each(user => console.log(user.name));

// Aggregation
const stats = {
  total: db.table('Orders').count(),
  revenue: db.table('Orders').where('status', 'completed').sum('total'),
  avgOrder: db.table('Orders').avg('total'),
  maxOrder: db.table('Orders').max('total')
};

// Grouping
const statusCounts = db.table('Users')
  .groupBy('status')
  .having('count', '>', 5)
  .get();

statusCounts.forEach(group => {
  console.log(`${group.status}: ${group.count} users`);
});

// Write operations
db.table('Users').insert({
  name: 'John Doe',
  email: '[email protected]'
});

db.table('Users')
  .where('status', 'pending')
  .update({ status: 'active' });

db.table('Users')
  .where('last_login', '<', '2020-01-01')
  .delete();

db.table('Products')
  .where('id', 42)
  .increment('views');

Core Classes

Relationships

Utilities

Overview

Creating a Query Builder

Select Methods

Where Clauses

Joins

Ordering & Limiting

Grouping & Aggregation

Retrieval Methods

Write Operations

Aggregates

Advanced Features

Complete Example

Build docs developers (and LLMs) love

Core Classes

Relationships

Utilities

​Overview

​Creating a Query Builder

​Select Methods

​Where Clauses

​Joins

​Ordering & Limiting

​Grouping & Aggregation

​Retrieval Methods

​Write Operations

​Aggregates

​Advanced Features

​Complete Example

Build docs developers (and LLMs) love

Overview

Creating a Query Builder

Select Methods

Where Clauses

Joins

Ordering & Limiting

Grouping & Aggregation

Retrieval Methods

Write Operations

Aggregates

Advanced Features

Complete Example