# Query Builder
{% hint style="danger" %}
Utility is still in beta, you can report issues on my github.
{% endhint %}
Complete list of methods
* `select(...columns: string[])`
* `selectAll()`
* `from(table: string)`
* `where(column: string, value: any)`
* `insert(...columns: string[])`
* `into(table: string)`
* `values(...values: any[])`
* `update(table: string)`
* `set(column: string, value: any)`
* `delete()`
* `replace(...columns: string[])`
* `in(table: string)`
* `with(...values: any[])`
* `sort(column: string, direction: "ASC" | "DESC" = "ASC")`
* `upper(column: string)`
* `lower(column: string)`
* `concat(column: string, ...values: string[])`
* `curdate()`
* `now()`
* `dateFormat(column: string, format: string)`
* `day(column: string)`
* `month(column: string)`
* `year(column: string)`
* `abs(column: string)`
* `round(column: string, decimals: number)`
* `floor(column: string)`
* `ceil(column: string)`
* `pow(column: string, exponent: number)`
* `sqrt(column: string)`
* `groupBy(...columns: string[])`
* `having(column: string, value: any)`
* `limit(limit: number)`
* `offset(offset: number)`
* `join(type: string, table: string, on: string)`
* `distinct()`
* `count(column: string)`
* `sum(column: string)`
* `avg(column: string)`
* `min(column: string)`
* `max(column: string)`
* `build()`
* `destruct()`
* `run(callback: (query: string, params: any[]) => Promise)`
## `SELECT`
The `SELECT` operation retrieves data from one or more tables.
```typescript
const query = new QueryBuilder()
.select("id", "name")
.from("users")
.where("age", 25)
.sort("name", "DESC")
.limit(10)
.offset(5)
.join("INNER", "orders", "users.id = orders.user_id")
.distinct()
.count("orders.id")
.build();
```
```typescript
{
query: "SELECT DISTINCT id, name, COUNT(orders.id) FROM users INNER JOIN orders ON users.id = orders.user_id WHERE age = ? ORDER BY name DESC LIMIT 10 OFFSET 5",
params: [25]
}
```
#### Chaining Methods
* `select(...columns: string[])`: Specifies the columns to select.
* `from(table: string)`: Specifies the table to select from.
* `where(column: string, value: any)`: Adds a WHERE clause.
* `sort(column: string, direction: "ASC" | "DESC")`: Adds an ORDER BY clause.
* `limit(limit: number)`: Adds a LIMIT clause.
* `offset(offset: number)`: Adds an OFFSET clause.
* `join(type: string, table: string, on: string)`: Adds a JOIN clause.
* `distinct()`: Adds a DISTINCT clause.
* `count(column: string)`: Adds a COUNT aggregate function.
* `sum(column: string)`: Adds a SUM aggregate function.
* `avg(column: string)`: Adds an AVG aggregate function.
* `min(column: string)`: Adds a MIN aggregate function.
* `max(column: string)`: Adds a MAX aggregate function.
## `INSERT`
The `INSERT` operation adds new records to a table.
```typescript
const query = new QueryBuilder()
.insert("name", "age")
.into("users")
.values("John Doe", 30)
.build();
```
```typescript
{
query: "INSERT INTO users (name, age) VALUES (?, ?)",
params: ["John Doe", 30]
}
```
#### Chaining Methods
* `insert(...columns: string[])`: Specifies the columns to insert.
* `into(table: string)`: Specifies the table to insert into.
* `values(...values: any[])`: Specifies the values to insert.
## `UPDATE`
The `UPDATE` operation modifies existing records in a table.
```typescript
const query = new QueryBuilder()
.update("users")
.set("name", "Jane Doe")
.set("age", 28)
.where("id", 1)
.build();
```
```typescript
{
query: "UPDATE users SET name = ?, age = ? WHERE id = ?",
params: ["Jane Doe", 28, 1]
}
```
#### Chaining Methods
* `update(table: string)`: Specifies the table to update.
* `set(column: string, value: any)`: Adds a SET clause.
* `where(column: string, value: any)`: Adds a WHERE clause.
## `DELETE`
The `DELETE` operation removes records from a table.
```typescript
const query = new QueryBuilder()
.delete()
.from("users")
.where("id", 1)
.build();
```
```typescript
{
query: "DELETE FROM users WHERE id = ?",
params: [1]
}
```
#### Chaining Methods
* `delete()`: Sets the operation to DELETE.
* `from(table: string)`: Specifies the table to delete from.
* `where(column: string, value: any)`: Adds a WHERE clause.
## `REPLACE`
The `REPLACE` operation inserts a new record or replaces an existing one.
```typescript
const query = new QueryBuilder()
.replace("name", "age")
.in("users")
.with("John Doe", 30)
.build();
```
```typescript
{
query: "REPLACE INTO users (name, age) VALUES (?, ?)",
params: ["John Doe", 30]
}
```
#### Chaining Methods
* `replace(...columns: string[])`: Specifies the columns to replace.
* `in(table: string)`: Specifies the table to replace into.
* `with(...values: any[])`: Specifies the values to replace.
## `Executing the query`
This can be implemented using **any** database library. You can see the example below of how you can implement it with my [Remote MySQL](https://discord-base.gitbook.io/discord-base/utilities/remote-mysql).
```typescript
import DB, { DBOptions } from "@utils/db";
const options: DBOptions = {
host: "localhost",
user: "root",
password: "password",
database: "database",
port: 3306,
};
const db = new DB(options);
const query = new QueryBuilder().selectAll().from("users").sort("id", "DESC");
const { data: result, error } = await query.run((query, params) => {
return db.query(query, params); // you can replace this with anything
})
if (error) {
console.error(error)
return;
}
console.log(result)
```
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://discord-base.gitbook.io/discord-base/utilities/query-builder.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.