Query Builder

The QueryBuilder class provides a fluent interface for constructing SQL queries in a type-safe manner. It supports various SQL operations.

Utility is still in beta, you can report issues on my github.

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

SELECT

The SELECT operation retrieves data from one or more tables.

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

{
   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.

const query = new QueryBuilder()
	.insert("name", "age")
	.into("users")
	.values("John Doe", 30)
	.build();

{
   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.

const query = new QueryBuilder()
	.update("users")
	.set("name", "Jane Doe")
	.set("age", 28)
	.where("id", 1)
	.build();

{
   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.

const query = new QueryBuilder()
	.delete()
	.from("users")
	.where("id", 1)
	.build();

{
   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.

const query = new QueryBuilder()
	.replace("name", "age")
	.in("users")
	.with("John Doe", 30)
	.build();

{
   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.

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)