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.
SELECT
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
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
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
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
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
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)
Last updated