> For the complete documentation index, see [llms.txt](https://discord-base.gitbook.io/discord-base/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://discord-base.gitbook.io/discord-base/utilities/query-builder.md).

# Query Builder

{% hint style="danger" %}
Utility is still in beta, you can report issues on my github.
{% endhint %}

<details>

<summary>Complete list of methods</summary>

* `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<any>)`

</details>

## `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](/discord-base/utilities/remote-mysql.md).

```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<any>(query, params); // you can replace this with anything
})

if (error) {
	console.error(error)
	return;
}

console.log(result)
		
```