Security News
MCP Community Begins Work on Official MCP Metaregistry
The MCP community is launching an official registry to standardize AI tool discovery and let agents dynamically find and install MCP servers.
By Sarah Gooding - May 09, 2025
🚀 Big News: Socket Acquires Coana to Bring Reachability Analysis to Every Appsec Team.Learn more →
@kitdbase/mysql-query-builder
Advanced tools
@kitdbase/mysql-query-builder
mysql-query-builder is a lightweight and fluid library for managing MySQL databases in Node.js. It makes it easy to create MySQL queries without losing flexibility.
@kitdbase/mysql-query-builder
Language/Lenguaje
English documentation
Table of contents 🚀
- Introduction
- Features
- Installation
- Configuration
- Basic Usage
- Table Operations
- CRUD Operations
- Advanced Queries
- Aggregate Functions
- Finding Records
- Column Management
- Executing Raw SQL Queries
- Complete API
- License
Introduction
@kitdbase/mysql-query-builder is a Node.js library designed to simplify interactions with MySQL databases using an object-oriented approach. This library allows you to easily perform CRUD (Create, Read, Update, Delete) operations and manage your table structures.
Features
- MySQL Connection: Database connection management using the Singleton pattern.
- CRUD Operations: Perform insertion, selection, update, and deletion operations.
- Advanced Queries: Support for queries with
JOIN,WHERE,ORDER BY,GROUP BY,LIMIT,OFFSET, etc. - Table Management: Create, drop, and modify tables and columns.
- Data Validation: Automatic validation of data types and values before executing queries.
- Error Handling: Efficient error management and reporting.
Installation
To install the library, run the following command:
npm install @kitdbase/mysql-query-builder
Configuration
Before using the library, make sure to configure the necessary environment variables in a .env file:
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=password
MYSQL_DATABASE=mydatabase
MYSQL_PORT=3306
Basic Usage
Database Connection
The connection is automatically established when creating a MySQL instance. You don't need to connect manually.
import db from "@kitdbase/mysql-query-builder";
Table Operations
Creating a Table
You can create a table using the create method. Define the columns and their properties.
const usersTable = db.table("users");
await usersTable.create([
{ name: "id", type: "INT", options: ["primary", "autoincrement"] },
{ name: "name", type: "VARCHAR", length: 255 },
{ name: "email", type: "VARCHAR", length: 255 },
{ name: "age", type: "INT", defaultValue: 18 },
]);
Dropping a Table
You can drop a table using the drop method.
await usersTable.drop();
CRUD Operations
Inserting Data
Use the insert method to add new records to a table.
const newUsers = await usersTable.insert([
{ name: "Alice", email: "alice@example.com", age: 28 },
{ name: "Bob", email: "bob@example.com", age: 32 },
]);
console.log(newUsers); // [{ id: 1, name: 'Alice', ... }, { id: 2, name: 'Bob', ... }]
Selecting Data
Use the select method to retrieve data from a table.
const users = await usersTable.select(["id", "name", "email"]).get();
console.log(users); // [{ id: 1, name: 'Alice', email: 'alice@example.com' }, ...]
Updating Data
Use the update method to modify existing records.
await usersTable.where("id", "=", 1).update({ age: 29 });
Deleting Data
Use the delete method to remove records from a table.
await usersTable.where("id", "=", 2).delete();
Advanced Queries
WHERE Query
Filter records using the where method.
const adultUsers = await usersTable.where("age", ">", 18).get();
console.log(adultUsers); // [{ id: 1, name: 'Alice', age: 28 }, ...]
OR WHERE Query
Use orWhere to add OR conditions to your query.
const users = await usersTable
.where("age", ">", 25)
.orWhere("name", "=", "Alice")
.get();
console.log(users); // [{ id: 1, name: 'Alice', age: 28 }, ...]
WHERE Condition Groups
Group conditions using whereGroup.
const users = await usersTable
.whereGroup((query) => {
query.where("age", ">", 25).orWhere("name", "=", "Jane");
})
.get();
console.log(users); // [{ id: 1, name: 'Alice', age: 28 }, ...]
BETWEEN Query
Search for values within a range using whereBetween.
const users = await usersTable.whereBetween("age", [25, 35]).get();
console.log(users); // [{ id: 1, name: 'Alice', age: 28 }, { id: 2, name: 'Bob', age: 32 }]
IN Query
Search for values that match a set of values using whereIn.
const users = await usersTable.whereIn("id", [1, 3, 5]).get();
console.log(users); // [{ id: 1, name: 'Alice', age: 28 }, { id: 3, name: 'Charlie', age: 35 }]
IS NULL / IS NOT NULL Query
Search for null or non-null values using whereNull and whereNotNull.
const usersWithoutEmail = await usersTable.whereNull("email").get();
const usersWithEmail = await usersTable.whereNotNull("email").get();
JOIN Query
Join tables using the join method.
const usersWithOrders = await usersTable
.join("orders", "users.id", "=", "orders.user_id")
.select(["users.name", "orders.order_id"])
.get();
console.log(usersWithOrders); // [{ name: 'Alice', order_id: 101 }, ...]
LEFT JOIN Query
Perform a left join using the leftJoin method.
const usersWithOrders = await usersTable
.leftJoin("orders", "users.id", "=", "orders.user_id")
.select(["users.name", "orders.order_id"])
.get();
console.log(usersWithOrders); // [{ name: 'Alice', order_id: 101 }, { name: 'Bob', order_id: null }, ...]
RIGHT JOIN Query
Perform a right join using the rightJoin method.
const ordersWithUsers = await usersTable
.rightJoin("orders", "users.id", "=", "orders.user_id")
.select(["users.name", "orders.order_id"])
.get();
console.log(ordersWithUsers); // [{ name: 'Alice', order_id: 101 }, { name: null, order_id: 102 }, ...]
ORDER BY Query
Sort results using the orderBy method.
const sortedUsers = await usersTable.orderBy("name", "ASC").get();
console.log(sortedUsers); // [{ id: 1, name: 'Alice', ... }, { id: 2, name: 'Bob', ... }]
LIMIT and OFFSET Query (Pagination)
Limit the number of results and paginate using limit and page.
const firstTwoUsers = await usersTable.limit(2).page(1).get();
const nextTwoUsers = await usersTable.limit(2).page(2).get();
console.log(firstTwoUsers); // [{ id: 1, name: 'Alice', ... }, { id: 2, name: 'Bob', ... }]
console.log(nextTwoUsers); // [{ id: 3, name: 'Charlie', ... }, { id: 4, name: 'Dave', ... }]
GROUP BY Query
Group results using the groupBy method.
const usersByAge = await usersTable.groupBy("age").get();
console.log(usersByAge); // [{ age: 28, count: 1 }, { age: 32, count: 1 }]
DISTINCT Query
Retrieve unique records using the distinct method.
const uniqueNames = await usersTable.distinct().select(["name"]).get();
console.log(uniqueNames); // [{ name: 'Alice' }, { name: 'Bob' }]
Aggregate Functions
count
Count the number of records.
const userCount = await usersTable.count().first();
console.log(userCount); // { count: 2 }
sum
Calculate the sum of a column.
const totalAge = await usersTable.sum("age").first();
console.log(totalAge); // { sum: 60 }
avg
Calculate the average of a column.
const averageAge = await usersTable.avg("age").first();
console.log(averageAge); // { avg: 30 }
max
Find the maximum value in a column.
const maxAge = await usersTable.max("age").first();
console.log(maxAge); // { max: 32 }
min
Find the minimum value in a column.
const minAge = await usersTable.min("age").first();
console.log(minAge); // { min: 28 }
Finding Records
find
Find a record by a specific column value.
const user = await usersTable.find(1, "id");
console.log(user); // { id: 1, name: 'Alice', email: 'alice@example.com', age: 28 }
first
Get only the first record that meets the conditions.
const firstUser = await usersTable.where("age", ">", 25).first();
console.log(firstUser); // { id: 1, name: 'Alice', age: 28, ... }
Column Management
Adding Columns
Add new columns to a table using the add method of columns().
await usersTable
.columns()
.add([{ name: "phone", type: "VARCHAR", length: 15 }]);
Editing Columns
Modify existing columns using the edit method of columns().
await usersTable.columns().edit([
{
name: "email",
type: "VARCHAR",
length: 255,
defaultValue: "new@example.com",
},
]);
Deleting Columns
Remove columns from a table using the delete method of columns().
await usersTable.columns().delete(["phone"]);
Executing Raw SQL Queries
If you need to execute a raw SQL query, you can use the query method.
const result = await db.query("SELECT * FROM users WHERE age > 25;");
console.log(result); // { status: 'success', message: 'Query executed successfully', data: [...] }
Error Handling
The library captures common errors, such as SQL syntax errors or connection issues, and returns them in JSON format.
try {
const result = await db.query("INVALID SQL QUERY;");
} catch (error) {
console.error(error); // { status: 'error', message: 'SQL syntax error', data: null }
}
Complete API
MySQL Class
table(tableName: string): TableQuery
Creates and returns a new TableQuery instance for the specified table.
const usersTable = db.table("users");
query(sqlQuery: string): Promise<{ status: string, message: string, data: any | null }>
Executes a direct SQL query on the database.
const result = await db.query("SELECT * FROM users;");
TableQuery Class
create(fields: Field[]): Promise<boolean>
Creates a new table with the specified fields.
await usersTable.create([
{ name: "id", type: "INT", options: ["primary", "autoincrement"] },
{ name: "name", type: "VARCHAR", length: 255 },
]);
drop(): Promise<boolean>
Drops the table.
await usersTable.drop();
select(fields: string[] = []): TableQuery
Specifies the columns to select in a SELECT query.
usersTable.select(["id", "name", "email"]);
where(column: string, operator: string | undefined, value: any): TableQuery
Adds a WHERE condition to the query.
usersTable.where("age", ">", 25);
orWhere(column: string, operator: string | undefined, value: any): TableQuery
Adds an OR WHERE condition to the query.
usersTable.orWhere("name", "=", "Jane");
whereGroup(callback: any): TableQuery
Adds a group of WHERE conditions to the query.
usersTable.whereGroup((query) => {
query.where("age", ">", 25).orWhere("name", "=", "Jane");
});
whereBetween(column: string, [value1, value2]: any): TableQuery
Adds a WHERE BETWEEN condition to the query.
usersTable.whereBetween("age", [25, 35]);
whereIn(column: string, values: any): TableQuery
Adds a WHERE IN condition to the query.
usersTable.whereIn("id", [1, 3, 5]);
whereNull(column: string): TableQuery
Adds a WHERE IS NULL condition to the query.
usersTable.whereNull("email");
whereNotNull(column: string): TableQuery
Adds a WHERE IS NOT NULL condition to the query.
usersTable.whereNotNull("email");
join(table: string, column1: string, operator: string, column2: string): TableQuery
Adds a JOIN clause to the query.
usersTable.join("orders", "users.id", "=", "orders.user_id");
leftJoin(table: string, column1: string, operator: string, column2: string): TableQuery
Adds a LEFT JOIN clause to the query.
usersTable.leftJoin("orders", "users.id", "=", "orders.user_id");
rightJoin(table: string, column1: string, operator: string, column2: string): TableQuery
Adds a RIGHT JOIN clause to the query.
usersTable.rightJoin("orders", "users.id", "=", "orders.user_id");
orderBy(column: string, direction: string = 'ASC'): TableQuery
Adds an ORDER BY clause to the query.
usersTable.orderBy("name", "ASC");
groupBy(column: string): TableQuery
Adds a GROUP BY clause to the query.
usersTable.groupBy("age");
distinct(): TableQuery
Adds a DISTINCT clause to the query.
usersTable.distinct();
count(column = '*'): TableQuery
Adds a COUNT clause to the query.
usersTable.count();
sum(column: string): TableQuery
Adds a SUM clause to the query.
usersTable.sum("age");
avg(column: string): TableQuery
Adds an AVG clause to the query.
usersTable.avg("age");
max(column: string): TableQuery
Adds a MAX clause to the query.
usersTable.max("age");
min(column: string): TableQuery
Adds a MIN clause to the query.
usersTable.min("age");
limit(number: number): TableQuery
Adds a LIMIT clause to the query.
usersTable.limit(10);
page(number: number): TableQuery
Adds pagination to the query using LIMIT and OFFSET.
usersTable.limit(10).page(2);
get(): Promise<any[]>
Executes the query and returns all matching rows.
const users = await usersTable.get();
first(): Promise<any | null>
Executes the query and returns the first matching row.
const user = await usersTable.first();
insert(data: Record<string, any>[]): Promise<Record<string, any>[]>
Inserts new records into the table.
const newUsers = await usersTable.insert([
{ name: "Alice", email: "alice@example.com" },
]);
update(data: Record<string, any>): Promise<boolean>
Updates records in the table based on WHERE conditions.
await usersTable.where("id", "=", 1).update({ name: "Alice Smith" });
delete(): Promise<boolean>
Deletes records from the table based on WHERE conditions.
await usersTable.where("id", "=", 1).delete();
find(value: any, column: string = 'id'): Promise<any | null>
Finds a record by its column value.
const user = await usersTable.find(1);
columns(): Columns
Returns an instance of the Columns class to manage table columns.
const columns = usersTable.columns();
Columns Class
add(columns: Field[]): Promise<boolean>
Adds new columns to the table.
await usersTable
.columns()
.add([{ name: "phone", type: "VARCHAR", length: 15 }]);
edit(columns: Field[]): Promise<boolean>
Modifies existing columns in the table.
await usersTable.columns().edit([
{
name: "email",
type: "VARCHAR",
length: 255,
defaultValue: "example@mail.com",
},
]);
delete(columns: string[]): Promise<boolean>
Deletes columns from the table.
await usersTable.columns().delete(["phone"]);
License
This project is licensed under the MIT License - see the LICENSE file for details.
Documentación en español
Tabla de contenidos 🚀
- Introducción
- Características
- Instalación
- Configuración
- Uso básico
- Operaciones de tabla
- Operaciones CRUD
- Consultas avanzadas
- Consulta con WHERE
- Consulta con OR WHERE
- Consulta con grupos de condiciones WHERE
- Consulta con BETWEEN
- Consulta con IN
- Consulta con IS NULL / IS NOT NULL
- Consulta con JOIN
- Consulta con LEFT JOIN
- Consulta con RIGHT JOIN
- Consulta con ORDER BY
- Consulta con LIMIT y OFFSET
- Consulta con GROUP BY
- Consulta con DISTINCT
- Funciones de agregación
- Buscar registros
- Gestión de columnas
- Ejecutar consultas SQL crudas
- API completa
- Licencia
Introducción
@kitdbase/mysql-query-builder es una biblioteca de Node.js diseñada para simplificar las interacciones con bases de datos MySQL utilizando un enfoque orientado a objetos. Esta biblioteca te permite realizar operaciones CRUD (Crear, Leer, Actualizar, Eliminar) fácilmente, así como gestionar la estructura de tus tablas.
Características
- Conexión a MySQL: Gestión de conexiones a la base de datos utilizando el patrón Singleton.
- Operaciones CRUD: Realizar operaciones de inserción, selección, actualización y eliminación.
- Consultas avanzadas: Soporte para consultas con
JOIN,WHERE,ORDER BY,GROUP BY,LIMIT,OFFSET, etc. - Gestión de tablas: Crear, eliminar y modificar tablas y columnas.
- Validación de datos: Validación automática de tipos de datos y valores antes de ejecutar consultas.
- Manejo de errores: Gestión y reporte eficiente de errores.
Instalación
Para instalar la biblioteca, ejecuta el siguiente comando:
npm install @kitdbase/mysql-query-builder
Configuración
Antes de usar la biblioteca, asegúrate de configurar las variables de entorno necesarias en un archivo .env:
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=password
MYSQL_DATABASE=mydatabase
MYSQL_PORT=3306
Uso básico
Conexión a la base de datos
La conexión se establece automáticamente al crear una instancia de MySQL. No necesitas conectarte manualmente.
import db from "@kitdbase/mysql-query-builder";
Operaciones de tabla
Crear una tabla
Puedes crear una tabla utilizando el método create. Define las columnas y sus propiedades.
const usersTable = db.table("users");
await usersTable.create([
{ name: "id", type: "INT", options: ["primary", "autoincrement"] },
{ name: "name", type: "VARCHAR", length: 255 },
{ name: "email", type: "VARCHAR", length: 255 },
{ name: "age", type: "INT", defaultValue: 18 },
]);
Eliminar una tabla
Puedes eliminar una tabla utilizando el método drop.
await usersTable.drop();
Operaciones CRUD
Insertar datos
Utiliza el método insert para añadir nuevos registros a una tabla.
const newUsers = await usersTable.insert([
{ name: "Alice", email: "alice@example.com", age: 28 },
{ name: "Bob", email: "bob@example.com", age: 32 },
]);
console.log(newUsers); // [{ id: 1, name: 'Alice', ... }, { id: 2, name: 'Bob', ... }]
Seleccionar datos
Utiliza el método select para recuperar datos de una tabla.
const users = await usersTable.select(["id", "name", "email"]).get();
console.log(users); // [{ id: 1, name: 'Alice', email: 'alice@example.com' }, ...]
Actualizar datos
Utiliza el método update para modificar registros existentes.
await usersTable.where("id", "=", 1).update({ age: 29 });
Eliminar datos
Utiliza el método delete para eliminar registros de una tabla.
await usersTable.where("id", "=", 2).delete();
Consultas avanzadas
Consulta con WHERE
Filtra registros utilizando el método where.
const adultUsers = await usersTable.where("age", ">", 18).get();
console.log(adultUsers); // [{ id: 1, name: 'Alice', age: 28 }, ...]
Consulta con OR WHERE
Utiliza orWhere para añadir condiciones OR a tu consulta.
const users = await usersTable
.where("age", ">", 25)
.orWhere("name", "=", "Alice")
.get();
console.log(users); // [{ id: 1, name: 'Alice', age: 28 }, ...]
Consulta con grupos de condiciones WHERE
Agrupa condiciones utilizando whereGroup.
const users = await usersTable
.whereGroup((query) => {
query.where("age", ">", 25).orWhere("name", "=", "Jane");
})
.get();
console.log(users); // [{ id: 1, name: 'Alice', age: 28 }, ...]
Consulta con BETWEEN
Busca valores entre un rango utilizando whereBetween.
const users = await usersTable.whereBetween("age", [25, 35]).get();
console.log(users); // [{ id: 1, name: 'Alice', age: 28 }, { id: 2, name: 'Bob', age: 32 }]
Consulta con IN
Busca valores que coincidan con un conjunto de valores utilizando whereIn.
const users = await usersTable.whereIn("id", [1, 3, 5]).get();
console.log(users); // [{ id: 1, name: 'Alice', age: 28 }, { id: 3, name: 'Charlie', age: 35 }]
Consulta con IS NULL / IS NOT NULL
Busca valores nulos o no nulos utilizando whereNull y whereNotNull.
const usersWithoutEmail = await usersTable.whereNull("email").get();
const usersWithEmail = await usersTable.whereNotNull("email").get();
Consulta con JOIN
Une tablas utilizando el método join.
const usersWithOrders = await usersTable
.join("orders", "users.id", "=", "orders.user_id")
.select(["users.name", "orders.order_id"])
.get();
console.log(usersWithOrders); // [{ name: 'Alice', order_id: 101 }, ...]
Consulta con LEFT JOIN
Realiza un left join utilizando el método leftJoin.
const usersWithOrders = await usersTable
.leftJoin("orders", "users.id", "=", "orders.user_id")
.select(["users.name", "orders.order_id"])
.get();
console.log(usersWithOrders); // [{ name: 'Alice', order_id: 101 }, { name: 'Bob', order_id: null }, ...]
Consulta con RIGHT JOIN
Realiza un right join utilizando el método rightJoin.
const ordersWithUsers = await usersTable
.rightJoin("orders", "users.id", "=", "orders.user_id")
.select(["users.name", "orders.order_id"])
.get();
console.log(ordersWithUsers); // [{ name: 'Alice', order_id: 101 }, { name: null, order_id: 102 }, ...]
Consulta con ORDER BY
Ordena resultados utilizando el método orderBy.
const sortedUsers = await usersTable.orderBy("name", "ASC").get();
console.log(sortedUsers); // [{ id: 1, name: 'Alice', ... }, { id: 2, name: 'Bob', ... }]
Consulta con LIMIT y OFFSET (paginación)
Limita el número de resultados y pagina utilizando limit y page.
const firstTwoUsers = await usersTable.limit(2).page(1).get();
const nextTwoUsers = await usersTable.limit(2).page(2).get();
console.log(firstTwoUsers); // [{ id: 1, name: 'Alice', ... }, { id: 2, name: 'Bob', ... }]
console.log(nextTwoUsers); // [{ id: 3, name: 'Charlie', ... }, { id: 4, name: 'Dave', ... }]
Consulta con GROUP BY
Agrupa resultados utilizando el método groupBy.
const usersByAge = await usersTable.groupBy("age").get();
console.log(usersByAge); // [{ age: 28, count: 1 }, { age: 32, count: 1 }]
Consulta con DISTINCT
Recupera registros únicos utilizando el método distinct.
const uniqueNames = await usersTable.distinct().select(["name"]).get();
console.log(uniqueNames); // [{ name: 'Alice' }, { name: 'Bob' }]
Funciones de agregación
count
Cuenta el número de registros.
const userCount = await usersTable.count().first();
console.log(userCount); // { count: 2 }
sum
Calcula la suma de una columna.
const totalAge = await usersTable.sum("age").first();
console.log(totalAge); // { sum: 60 }
avg
Calcula el promedio de una columna.
const averageAge = await usersTable.avg("age").first();
console.log(averageAge); // { avg: 30 }
max
Encuentra el valor máximo en una columna.
const maxAge = await usersTable.max("age").first();
console.log(maxAge); // { max: 32 }
min
Encuentra el valor mínimo en una columna.
const minAge = await usersTable.min("age").first();
console.log(minAge); // { min: 28 }
Buscar registros
find
Encuentra un registro por un valor específico de columna.
const user = await usersTable.find(1, "id");
console.log(user); // { id: 1, name: 'Alice', email: 'alice@example.com', age: 28 }
first
Obtiene solo el primer registro que cumple con las condiciones.
const firstUser = await usersTable.where("age", ">", 25).first();
console.log(firstUser); // { id: 1, name: 'Alice', age: 28, ... }
Gestión de columnas
Añadir columnas
Añade nuevas columnas a una tabla utilizando el método add de columns().
await usersTable
.columns()
.add([{ name: "phone", type: "VARCHAR", length: 15 }]);
Editar columnas
Modifica columnas existentes utilizando el método edit de columns().
await usersTable.columns().edit([
{
name: "email",
type: "VARCHAR",
length: 255,
defaultValue: "new@example.com",
},
]);
Eliminar columnas
Elimina columnas de una tabla utilizando el método delete de columns().
await usersTable.columns().delete(["phone"]);
Ejecutar consultas SQL crudas
Si necesitas ejecutar una consulta SQL cruda, puedes utilizar el método query.
const result = await db.query("SELECT * FROM users WHERE age > 25;");
console.log(result); // { status: 'success', message: 'Query executed successfully', data: [...] }
Manejo de errores
La biblioteca captura errores comunes, como errores de sintaxis SQL o problemas de conexión, y los devuelve en formato JSON.
try {
const result = await db.query("INVALID SQL QUERY;");
} catch (error) {
console.error(error); // { status: 'error', message: 'SQL syntax error', data: null }
}
API completa
Clase MySQL
table(tableName: string): TableQuery
Crea y devuelve una nueva instancia de TableQuery para la tabla especificada.
const usersTable = db.table("users");
query(sqlQuery: string): Promise<{ status: string, message: string, data: any | null }>
Ejecuta una consulta SQL directa en la base de datos.
const result = await db.query("SELECT * FROM users;");
Clase TableQuery
create(fields: Field[]): Promise<boolean>
Crea una nueva tabla con los campos especificados.
await usersTable.create([
{ name: "id", type: "INT", options: ["primary", "autoincrement"] },
{ name: "name", type: "VARCHAR", length: 255 },
]);
drop(): Promise<boolean>
Elimina la tabla.
await usersTable.drop();
select(fields: string[] = []): TableQuery
Especifica las columnas a seleccionar en una consulta SELECT.
usersTable.select(["id", "name", "email"]);
where(column: string, operator: string | undefined, value: any): TableQuery
Añade una condición WHERE a la consulta.
usersTable.where("age", ">", 25);
orWhere(column: string, operator: string | undefined, value: any): TableQuery
Añade una condición OR WHERE a la consulta.
usersTable.orWhere("name", "=", "Jane");
whereGroup(callback: any): TableQuery
Añade un grupo de condiciones WHERE a la consulta.
usersTable.whereGroup((query) => {
query.where("age", ">", 25).orWhere("name", "=", "Jane");
});
whereBetween(column: string, [value1, value2]: any): TableQuery
Añade una condición WHERE BETWEEN a la consulta.
usersTable.whereBetween("age", [25, 35]);
whereIn(column: string, values: any): TableQuery
Añade una condición WHERE IN a la consulta.
usersTable.whereIn("id", [1, 3, 5]);
whereNull(column: string): TableQuery
Añade una condición WHERE IS NULL a la consulta.
usersTable.whereNull("email");
whereNotNull(column: string): TableQuery
Añade una condición WHERE IS NOT NULL a la consulta.
usersTable.whereNotNull("email");
join(table: string, column1: string, operator: string, column2: string): TableQuery
Añade una cláusula JOIN a la consulta.
usersTable.join("orders", "users.id", "=", "orders.user_id");
leftJoin(table: string, column1: string, operator: string, column2: string): TableQuery
Añade una cláusula LEFT JOIN a la consulta.
usersTable.leftJoin("orders", "users.id", "=", "orders.user_id");
rightJoin(table: string, column1: string, operator: string, column2: string): TableQuery
Añade una cláusula RIGHT JOIN a la consulta.
usersTable.rightJoin("orders", "users.id", "=", "orders.user_id");
orderBy(column: string, direction: string = 'ASC'): TableQuery
Añade una cláusula ORDER BY a la consulta.
usersTable.orderBy("name", "ASC");
groupBy(column: string): TableQuery
Añade una cláusula GROUP BY a la consulta.
usersTable.groupBy("age");
distinct(): TableQuery
Añade una cláusula DISTINCT a la consulta.
usersTable.distinct();
count(column = '*'): TableQuery
Añade una cláusula COUNT a la consulta.
usersTable.count();
sum(column: string): TableQuery
Añade una cláusula SUM a la consulta.
usersTable.sum("age");
avg(column: string): TableQuery
Añade una cláusula AVG a la consulta.
usersTable.avg("age");
max(column: string): TableQuery
Añade una cláusula MAX a la consulta.
usersTable.max("age");
min(column: string): TableQuery
Añade una cláusula MIN a la consulta.
usersTable.min("age");
limit(number: number): TableQuery
Añade una cláusula LIMIT a la consulta.
usersTable.limit(10);
page(number: number): TableQuery
Añade paginación a la consulta utilizando LIMIT y OFFSET.
usersTable.limit(10).page(2);
get(): Promise<any[]>
Ejecuta la consulta y devuelve todas las filas coincidentes.
const users = await usersTable.get();
first(): Promise<any | null>
Ejecuta la consulta y devuelve la primera fila coincidente.
const user = await usersTable.first();
insert(data: Record<string, any>[]): Promise<Record<string, any>[]>
Inserta nuevos registros en la tabla.
const newUsers = await usersTable.insert([
{ name: "Alice", email: "alice@example.com" },
]);
update(data: Record<string, any>): Promise<boolean>
Actualiza registros en la tabla según las condiciones WHERE.
await usersTable.where("id", "=", 1).update({ name: "Alice Smith" });
delete(): Promise<boolean>
Elimina registros de la tabla según las condiciones WHERE.
await usersTable.where("id", "=", 1).delete();
find(value: any, column: string = 'id'): Promise<any | null>
Encuentra un registro por su valor de columna.
const user = await usersTable.find(1);
columns(): Columns
Devuelve una instancia de la clase Columns para gestionar columnas de la tabla.
const columns = usersTable.columns();
Clase Columns
add(columns: Field[]): Promise<boolean>
Añade nuevas columnas a la tabla.
await usersTable
.columns()
.add([{ name: "phone", type: "VARCHAR", length: 15 }]);
edit(columns: Field[]): Promise<boolean>
Modifica columnas existentes en la tabla.
await usersTable.columns().edit([
{
name: "email",
type: "VARCHAR",
length: 255,
defaultValue: "example@mail.com",
},
]);
delete(columns: string[]): Promise<boolean>
Elimina columnas de la tabla.
await usersTable.columns().delete(["phone"]);
Licencia
Este proyecto está licenciado bajo la Licencia MIT - consulta el archivo LICENSE para más detalles.
FAQs
mysql-query-builder is a lightweight and fluid library for managing MySQL databases in Node.js. It makes it easy to create MySQL queries without losing flexibility.
The npm package @kitdbase/mysql-query-builder receives a total of 21 weekly downloads. As such, @kitdbase/mysql-query-builder popularity was classified as not popular.
We found that @kitdbase/mysql-query-builder demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Package last updated on 15 Apr 2025
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Related posts
Research
Security News
Malicious npm Packages Use Telegram to Exfiltrate BullX Credentials
Socket uncovers an npm Trojan stealing crypto wallets and BullX credentials via obfuscated code and Telegram exfiltration.
By Kush Pandya - May 08, 2025
Research
Security News
Backdooring the IDE: Malicious npm Packages Hijack Cursor Editor on macOS
Malicious npm packages posing as developer tools target macOS Cursor IDE users, stealing credentials and modifying files to gain persistent backdoor access.
By Kirill Boychenko - May 07, 2025