@kitdbase/mysql-query-builder

Language/Lenguaje

• English
• Español

English documentation

Table of contents 🚀

• Introduction
• Features
• Installation
• Configuration
• Basic Usage
  • Database Connection
• Table Operations
  • Creating a Table
  • Dropping a Table
• CRUD Operations
  • Inserting Data
  • Selecting Data
  • Updating Data
  • Deleting Data
• Advanced Queries
  • WHERE Query
  • OR WHERE Query
  • WHERE Condition Groups
  • BETWEEN Query
  • IN Query
  • IS NULL / IS NOT NULL Query
  • JOIN Query
  • LEFT JOIN Query
  • RIGHT JOIN Query
  • ORDER BY Query
  • LIMIT and OFFSET Query (Pagination)
  • GROUP BY Query
  • DISTINCT Query
• Aggregate Functions
  • count
  • sum
  • avg
  • max
  • min
• Finding Records
  • find
  • first
• Column Management
  • Adding Columns
  • Editing Columns
  • Deleting Columns
• Executing Raw SQL Queries
  • Error Handling
• Complete API
  • MySQL Class
  • TableQuery Class
  • Columns Class
• 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:

1npm install @kitdbase/mysql-query-builder

Configuration

Before using the library, make sure to configure the necessary environment variables in a .env file:

1MYSQL_HOST=localhost
2MYSQL_USER=root
3MYSQL_PASSWORD=password
4MYSQL_DATABASE=mydatabase
5MYSQL_PORT=3306

Basic Usage

Database Connection

The connection is automatically established when creating a MySQL instance. You don't need to connect manually.

1import 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.

1const usersTable = db.table("users");
2await usersTable.create([
3  { name: "id", type: "INT", options: ["primary", "autoincrement"] },
4  { name: "name", type: "VARCHAR", length: 255 },
5  { name: "email", type: "VARCHAR", length: 255 },
6  { name: "age", type: "INT", defaultValue: 18 },
7]);

Dropping a Table

You can drop a table using the drop method.

1await usersTable.drop();

CRUD Operations

Inserting Data

Use the insert method to add new records to a table.

1const newUsers = await usersTable.insert([
2  { name: "Alice", email: "alice@example.com", age: 28 },
3  { name: "Bob", email: "bob@example.com", age: 32 },
4]);
5console.log(newUsers); // [{ id: 1, name: 'Alice', ... }, { id: 2, name: 'Bob', ... }]

Selecting Data

Use the select method to retrieve data from a table.

1const users = await usersTable.select(["id", "name", "email"]).get();
2console.log(users); // [{ id: 1, name: 'Alice', email: 'alice@example.com' }, ...]

Updating Data

Use the update method to modify existing records.

1await usersTable.where("id", "=", 1).update({ age: 29 });

Deleting Data

Use the delete method to remove records from a table.

1await usersTable.where("id", "=", 2).delete();

Advanced Queries

WHERE Query

Filter records using the where method.

1const adultUsers = await usersTable.where("age", ">", 18).get();
2console.log(adultUsers); // [{ id: 1, name: 'Alice', age: 28 }, ...]

OR WHERE Query

Use orWhere to add OR conditions to your query.

1const users = await usersTable
2  .where("age", ">", 25)
3  .orWhere("name", "=", "Alice")
4  .get();
5console.log(users); // [{ id: 1, name: 'Alice', age: 28 }, ...]

WHERE Condition Groups

Group conditions using whereGroup.

1const users = await usersTable
2  .whereGroup((query) => {
3    query.where("age", ">", 25).orWhere("name", "=", "Jane");
4  })
5  .get();
6console.log(users); // [{ id: 1, name: 'Alice', age: 28 }, ...]

BETWEEN Query

Search for values within a range using whereBetween.

1const users = await usersTable.whereBetween("age", [25, 35]).get();
2console.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.

1const users = await usersTable.whereIn("id", [1, 3, 5]).get();
2console.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.

1const usersWithoutEmail = await usersTable.whereNull("email").get();
2const usersWithEmail = await usersTable.whereNotNull("email").get();

JOIN Query

Join tables using the join method.

1const usersWithOrders = await usersTable
2  .join("orders", "users.id", "=", "orders.user_id")
3  .select(["users.name", "orders.order_id"])
4  .get();
5console.log(usersWithOrders); // [{ name: 'Alice', order_id: 101 }, ...]

LEFT JOIN Query

Perform a left join using the leftJoin method.

1const usersWithOrders = await usersTable
2  .leftJoin("orders", "users.id", "=", "orders.user_id")
3  .select(["users.name", "orders.order_id"])
4  .get();
5console.log(usersWithOrders); // [{ name: 'Alice', order_id: 101 }, { name: 'Bob', order_id: null }, ...]

RIGHT JOIN Query

Perform a right join using the rightJoin method.

1const ordersWithUsers = await usersTable
2  .rightJoin("orders", "users.id", "=", "orders.user_id")
3  .select(["users.name", "orders.order_id"])
4  .get();
5console.log(ordersWithUsers); // [{ name: 'Alice', order_id: 101 }, { name: null, order_id: 102 }, ...]

ORDER BY Query

Sort results using the orderBy method.

1const sortedUsers = await usersTable.orderBy("name", "ASC").get();
2console.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.

1const firstTwoUsers = await usersTable.limit(2).page(1).get();
2const nextTwoUsers = await usersTable.limit(2).page(2).get();
3console.log(firstTwoUsers); // [{ id: 1, name: 'Alice', ... }, { id: 2, name: 'Bob', ... }]
4console.log(nextTwoUsers); // [{ id: 3, name: 'Charlie', ... }, { id: 4, name: 'Dave', ... }]

GROUP BY Query

Group results using the groupBy method.

1const usersByAge = await usersTable.groupBy("age").get();
2console.log(usersByAge); // [{ age: 28, count: 1 }, { age: 32, count: 1 }]

DISTINCT Query

Retrieve unique records using the distinct method.

1const uniqueNames = await usersTable.distinct().select(["name"]).get();
2console.log(uniqueNames); // [{ name: 'Alice' }, { name: 'Bob' }]

Aggregate Functions

count

Count the number of records.

1const userCount = await usersTable.count().first();
2console.log(userCount); // { count: 2 }

sum

Calculate the sum of a column.

1const totalAge = await usersTable.sum("age").first();
2console.log(totalAge); // { sum: 60 }

avg

Calculate the average of a column.

1const averageAge = await usersTable.avg("age").first();
2console.log(averageAge); // { avg: 30 }

max

Find the maximum value in a column.

1const maxAge = await usersTable.max("age").first();
2console.log(maxAge); // { max: 32 }

min

Find the minimum value in a column.

1const minAge = await usersTable.min("age").first();
2console.log(minAge); // { min: 28 }

Finding Records

find

Find a record by a specific column value.

1const user = await usersTable.find(1, "id");
2console.log(user); // { id: 1, name: 'Alice', email: 'alice@example.com', age: 28 }

first

Get only the first record that meets the conditions.

1const firstUser = await usersTable.where("age", ">", 25).first();
2console.log(firstUser); // { id: 1, name: 'Alice', age: 28, ... }

Column Management

Adding Columns

Add new columns to a table using the add method of columns().

1await usersTable
2  .columns()
3  .add([{ name: "phone", type: "VARCHAR", length: 15 }]);

Editing Columns

Modify existing columns using the edit method of columns().

1await usersTable.columns().edit([
2  {
3    name: "email",
4    type: "VARCHAR",
5    length: 255,
6    defaultValue: "new@example.com",
7  },
8]);

Deleting Columns

Remove columns from a table using the delete method of columns().

1await usersTable.columns().delete(["phone"]);

Executing Raw SQL Queries

If you need to execute a raw SQL query, you can use the query method.

1const result = await db.query("SELECT * FROM users WHERE age > 25;");
2console.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.

1try {
2  const result = await db.query("INVALID SQL QUERY;");
3} catch (error) {
4  console.error(error); // { status: 'error', message: 'SQL syntax error', data: null }
5}

Complete API

MySQL Class

table(tableName: string): TableQuery

Creates and returns a new TableQuery instance for the specified table.

1const usersTable = db.table("users");

query(sqlQuery: string): Promise<{ status: string, message: string, data: any | null }>

Executes a direct SQL query on the database.

1const result = await db.query("SELECT * FROM users;");

TableQuery Class

create(fields: Field[]): Promise<boolean>

Creates a new table with the specified fields.

1await usersTable.create([
2  { name: "id", type: "INT", options: ["primary", "autoincrement"] },
3  { name: "name", type: "VARCHAR", length: 255 },
4]);

drop(): Promise<boolean>

Drops the table.

1await usersTable.drop();

select(fields: string[] = []): TableQuery

Specifies the columns to select in a SELECT query.

1usersTable.select(["id", "name", "email"]);

where(column: string, operator: string | undefined, value: any): TableQuery

Adds a WHERE condition to the query.

1usersTable.where("age", ">", 25);

orWhere(column: string, operator: string | undefined, value: any): TableQuery

Adds an OR WHERE condition to the query.

1usersTable.orWhere("name", "=", "Jane");

whereGroup(callback: any): TableQuery

Adds a group of WHERE conditions to the query.

1usersTable.whereGroup((query) => {
2  query.where("age", ">", 25).orWhere("name", "=", "Jane");
3});

whereBetween(column: string, [value1, value2]: any): TableQuery

Adds a WHERE BETWEEN condition to the query.

1usersTable.whereBetween("age", [25, 35]);

whereIn(column: string, values: any): TableQuery

Adds a WHERE IN condition to the query.

1usersTable.whereIn("id", [1, 3, 5]);

whereNull(column: string): TableQuery

Adds a WHERE IS NULL condition to the query.

1usersTable.whereNull("email");

whereNotNull(column: string): TableQuery

Adds a WHERE IS NOT NULL condition to the query.

1usersTable.whereNotNull("email");

join(table: string, column1: string, operator: string, column2: string): TableQuery

Adds a JOIN clause to the query.

1usersTable.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.

1usersTable.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.

1usersTable.rightJoin("orders", "users.id", "=", "orders.user_id");

orderBy(column: string, direction: string = 'ASC'): TableQuery

Adds an ORDER BY clause to the query.

1usersTable.orderBy("name", "ASC");

groupBy(column: string): TableQuery

Adds a GROUP BY clause to the query.

1usersTable.groupBy("age");

distinct(): TableQuery

Adds a DISTINCT clause to the query.

1usersTable.distinct();

count(column = '*'): TableQuery

Adds a COUNT clause to the query.

1usersTable.count();

sum(column: string): TableQuery

Adds a SUM clause to the query.

1usersTable.sum("age");

avg(column: string): TableQuery

Adds an AVG clause to the query.

1usersTable.avg("age");

max(column: string): TableQuery

Adds a MAX clause to the query.

1usersTable.max("age");

min(column: string): TableQuery

Adds a MIN clause to the query.

1usersTable.min("age");

limit(number: number): TableQuery

Adds a LIMIT clause to the query.

1usersTable.limit(10);

page(number: number): TableQuery

Adds pagination to the query using LIMIT and OFFSET.

1usersTable.limit(10).page(2);

get(): Promise<any[]>

Executes the query and returns all matching rows.

1const users = await usersTable.get();

first(): Promise<any | null>

Executes the query and returns the first matching row.

1const user = await usersTable.first();

insert(data: Record<string, any>[]): Promise<Record<string, any>[]>

Inserts new records into the table.

1const newUsers = await usersTable.insert([
2  { name: "Alice", email: "alice@example.com" },
3]);

update(data: Record<string, any>): Promise<boolean>

Updates records in the table based on WHERE conditions.

1await usersTable.where("id", "=", 1).update({ name: "Alice Smith" });

delete(): Promise<boolean>

Deletes records from the table based on WHERE conditions.

1await usersTable.where("id", "=", 1).delete();

find(value: any, column: string = 'id'): Promise<any | null>

Finds a record by its column value.

1const user = await usersTable.find(1);

columns(): Columns

Returns an instance of the Columns class to manage table columns.

1const columns = usersTable.columns();

Columns Class

add(columns: Field[]): Promise<boolean>

Adds new columns to the table.

1await usersTable
2  .columns()
3  .add([{ name: "phone", type: "VARCHAR", length: 15 }]);

edit(columns: Field[]): Promise<boolean>

Modifies existing columns in the table.

1await usersTable.columns().edit([
2  {
3    name: "email",
4    type: "VARCHAR",
5    length: 255,
6    defaultValue: "example@mail.com",
7  },
8]);

delete(columns: string[]): Promise<boolean>

Deletes columns from the table.

1await 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
  • Conexión a la base de datos
• Operaciones de tabla
  • Crear una tabla
  • Eliminar una tabla
• Operaciones CRUD
  • Insertar datos
  • Seleccionar datos
  • Actualizar datos
  • Eliminar datos
• 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
  • count
  • sum
  • avg
  • max
  • min
• Buscar registros
  • find
  • first
• Gestión de columnas
  • Añadir columnas
  • Editar columnas
  • Eliminar columnas
• Ejecutar consultas SQL crudas
  • Manejo de errores
• API completa
  • Clase MySQL
  • Clase TableQuery
  • Clase Columns
• 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:

1npm 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:

1MYSQL_HOST=localhost
2MYSQL_USER=root
3MYSQL_PASSWORD=password
4MYSQL_DATABASE=mydatabase
5MYSQL_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.

1import 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.

1const usersTable = db.table("users");
2await usersTable.create([
3  { name: "id", type: "INT", options: ["primary", "autoincrement"] },
4  { name: "name", type: "VARCHAR", length: 255 },
5  { name: "email", type: "VARCHAR", length: 255 },
6  { name: "age", type: "INT", defaultValue: 18 },
7]);

Eliminar una tabla

Puedes eliminar una tabla utilizando el método drop.

1await usersTable.drop();

Operaciones CRUD

Insertar datos

Utiliza el método insert para añadir nuevos registros a una tabla.

1const newUsers = await usersTable.insert([
2  { name: "Alice", email: "alice@example.com", age: 28 },
3  { name: "Bob", email: "bob@example.com", age: 32 },
4]);
5console.log(newUsers); // [{ id: 1, name: 'Alice', ... }, { id: 2, name: 'Bob', ... }]

Seleccionar datos

Utiliza el método select para recuperar datos de una tabla.

1const users = await usersTable.select(["id", "name", "email"]).get();
2console.log(users); // [{ id: 1, name: 'Alice', email: 'alice@example.com' }, ...]

Actualizar datos

Utiliza el método update para modificar registros existentes.

1await usersTable.where("id", "=", 1).update({ age: 29 });

Eliminar datos

Utiliza el método delete para eliminar registros de una tabla.

1await usersTable.where("id", "=", 2).delete();

Consultas avanzadas

Consulta con WHERE

Filtra registros utilizando el método where.

1const adultUsers = await usersTable.where("age", ">", 18).get();
2console.log(adultUsers); // [{ id: 1, name: 'Alice', age: 28 }, ...]

Consulta con OR WHERE

Utiliza orWhere para añadir condiciones OR a tu consulta.

1const users = await usersTable
2  .where("age", ">", 25)
3  .orWhere("name", "=", "Alice")
4  .get();
5console.log(users); // [{ id: 1, name: 'Alice', age: 28 }, ...]

Consulta con grupos de condiciones WHERE

Agrupa condiciones utilizando whereGroup.

1const users = await usersTable
2  .whereGroup((query) => {
3    query.where("age", ">", 25).orWhere("name", "=", "Jane");
4  })
5  .get();
6console.log(users); // [{ id: 1, name: 'Alice', age: 28 }, ...]

Consulta con BETWEEN

Busca valores entre un rango utilizando whereBetween.

1const users = await usersTable.whereBetween("age", [25, 35]).get();
2console.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.

1const users = await usersTable.whereIn("id", [1, 3, 5]).get();
2console.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.

1const usersWithoutEmail = await usersTable.whereNull("email").get();
2const usersWithEmail = await usersTable.whereNotNull("email").get();

Consulta con JOIN

Une tablas utilizando el método join.

1const usersWithOrders = await usersTable
2  .join("orders", "users.id", "=", "orders.user_id")
3  .select(["users.name", "orders.order_id"])
4  .get();
5console.log(usersWithOrders); // [{ name: 'Alice', order_id: 101 }, ...]

Consulta con LEFT JOIN

Realiza un left join utilizando el método leftJoin.

1const usersWithOrders = await usersTable
2  .leftJoin("orders", "users.id", "=", "orders.user_id")
3  .select(["users.name", "orders.order_id"])
4  .get();
5console.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.

1const ordersWithUsers = await usersTable
2  .rightJoin("orders", "users.id", "=", "orders.user_id")
3  .select(["users.name", "orders.order_id"])
4  .get();
5console.log(ordersWithUsers); // [{ name: 'Alice', order_id: 101 }, { name: null, order_id: 102 }, ...]

Consulta con ORDER BY

Ordena resultados utilizando el método orderBy.

1const sortedUsers = await usersTable.orderBy("name", "ASC").get();
2console.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.

1const firstTwoUsers = await usersTable.limit(2).page(1).get();
2const nextTwoUsers = await usersTable.limit(2).page(2).get();
3console.log(firstTwoUsers); // [{ id: 1, name: 'Alice', ... }, { id: 2, name: 'Bob', ... }]
4console.log(nextTwoUsers); // [{ id: 3, name: 'Charlie', ... }, { id: 4, name: 'Dave', ... }]

Consulta con GROUP BY

Agrupa resultados utilizando el método groupBy.

1const usersByAge = await usersTable.groupBy("age").get();
2console.log(usersByAge); // [{ age: 28, count: 1 }, { age: 32, count: 1 }]

Consulta con DISTINCT

Recupera registros únicos utilizando el método distinct.

1const uniqueNames = await usersTable.distinct().select(["name"]).get();
2console.log(uniqueNames); // [{ name: 'Alice' }, { name: 'Bob' }]

Funciones de agregación

count

Cuenta el número de registros.

1const userCount = await usersTable.count().first();
2console.log(userCount); // { count: 2 }

sum

Calcula la suma de una columna.

1const totalAge = await usersTable.sum("age").first();
2console.log(totalAge); // { sum: 60 }

avg

Calcula el promedio de una columna.

1const averageAge = await usersTable.avg("age").first();
2console.log(averageAge); // { avg: 30 }

max

Encuentra el valor máximo en una columna.

1const maxAge = await usersTable.max("age").first();
2console.log(maxAge); // { max: 32 }

min

Encuentra el valor mínimo en una columna.

1const minAge = await usersTable.min("age").first();
2console.log(minAge); // { min: 28 }

Buscar registros

find

Encuentra un registro por un valor específico de columna.

1const user = await usersTable.find(1, "id");
2console.log(user); // { id: 1, name: 'Alice', email: 'alice@example.com', age: 28 }

first

Obtiene solo el primer registro que cumple con las condiciones.

1const firstUser = await usersTable.where("age", ">", 25).first();
2console.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().

1await usersTable
2  .columns()
3  .add([{ name: "phone", type: "VARCHAR", length: 15 }]);

Editar columnas

Modifica columnas existentes utilizando el método edit de columns().

1await usersTable.columns().edit([
2  {
3    name: "email",
4    type: "VARCHAR",
5    length: 255,
6    defaultValue: "new@example.com",
7  },
8]);

Eliminar columnas

Elimina columnas de una tabla utilizando el método delete de columns().

1await usersTable.columns().delete(["phone"]);

Ejecutar consultas SQL crudas

Si necesitas ejecutar una consulta SQL cruda, puedes utilizar el método query.

1const result = await db.query("SELECT * FROM users WHERE age > 25;");
2console.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.

1try {
2  const result = await db.query("INVALID SQL QUERY;");
3} catch (error) {
4  console.error(error); // { status: 'error', message: 'SQL syntax error', data: null }
5}

API completa

Clase MySQL

table(tableName: string): TableQuery

Crea y devuelve una nueva instancia de TableQuery para la tabla especificada.

1const 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.

1const result = await db.query("SELECT * FROM users;");

Clase TableQuery

create(fields: Field[]): Promise<boolean>

Crea una nueva tabla con los campos especificados.

1await usersTable.create([
2  { name: "id", type: "INT", options: ["primary", "autoincrement"] },
3  { name: "name", type: "VARCHAR", length: 255 },
4]);

drop(): Promise<boolean>

Elimina la tabla.

1await usersTable.drop();

select(fields: string[] = []): TableQuery

Especifica las columnas a seleccionar en una consulta SELECT.

1usersTable.select(["id", "name", "email"]);

where(column: string, operator: string | undefined, value: any): TableQuery

Añade una condición WHERE a la consulta.

1usersTable.where("age", ">", 25);

orWhere(column: string, operator: string | undefined, value: any): TableQuery

Añade una condición OR WHERE a la consulta.

1usersTable.orWhere("name", "=", "Jane");

whereGroup(callback: any): TableQuery

Añade un grupo de condiciones WHERE a la consulta.

1usersTable.whereGroup((query) => {
2  query.where("age", ">", 25).orWhere("name", "=", "Jane");
3});

whereBetween(column: string, [value1, value2]: any): TableQuery

Añade una condición WHERE BETWEEN a la consulta.

1usersTable.whereBetween("age", [25, 35]);

whereIn(column: string, values: any): TableQuery

Añade una condición WHERE IN a la consulta.

1usersTable.whereIn("id", [1, 3, 5]);

whereNull(column: string): TableQuery

Añade una condición WHERE IS NULL a la consulta.

1usersTable.whereNull("email");

whereNotNull(column: string): TableQuery

Añade una condición WHERE IS NOT NULL a la consulta.

1usersTable.whereNotNull("email");

join(table: string, column1: string, operator: string, column2: string): TableQuery

Añade una cláusula JOIN a la consulta.

1usersTable.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.

1usersTable.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.

1usersTable.rightJoin("orders", "users.id", "=", "orders.user_id");

orderBy(column: string, direction: string = 'ASC'): TableQuery

Añade una cláusula ORDER BY a la consulta.

1usersTable.orderBy("name", "ASC");

groupBy(column: string): TableQuery

Añade una cláusula GROUP BY a la consulta.

1usersTable.groupBy("age");

distinct(): TableQuery

Añade una cláusula DISTINCT a la consulta.

1usersTable.distinct();

count(column = '*'): TableQuery

Añade una cláusula COUNT a la consulta.

1usersTable.count();

sum(column: string): TableQuery

Añade una cláusula SUM a la consulta.

1usersTable.sum("age");

avg(column: string): TableQuery

Añade una cláusula AVG a la consulta.

1usersTable.avg("age");

max(column: string): TableQuery

Añade una cláusula MAX a la consulta.

1usersTable.max("age");

min(column: string): TableQuery

Añade una cláusula MIN a la consulta.

1usersTable.min("age");

limit(number: number): TableQuery

Añade una cláusula LIMIT a la consulta.

1usersTable.limit(10);

page(number: number): TableQuery

Añade paginación a la consulta utilizando LIMIT y OFFSET.

1usersTable.limit(10).page(2);

get(): Promise<any[]>

Ejecuta la consulta y devuelve todas las filas coincidentes.

1const users = await usersTable.get();

first(): Promise<any | null>

Ejecuta la consulta y devuelve la primera fila coincidente.

1const user = await usersTable.first();

insert(data: Record<string, any>[]): Promise<Record<string, any>[]>

Inserta nuevos registros en la tabla.

1const newUsers = await usersTable.insert([
2  { name: "Alice", email: "alice@example.com" },
3]);

update(data: Record<string, any>): Promise<boolean>

Actualiza registros en la tabla según las condiciones WHERE.

1await usersTable.where("id", "=", 1).update({ name: "Alice Smith" });

delete(): Promise<boolean>

Elimina registros de la tabla según las condiciones WHERE.

1await usersTable.where("id", "=", 1).delete();

find(value: any, column: string = 'id'): Promise<any | null>

Encuentra un registro por su valor de columna.

1const user = await usersTable.find(1);

columns(): Columns

Devuelve una instancia de la clase Columns para gestionar columnas de la tabla.

1const columns = usersTable.columns();

Clase Columns

add(columns: Field[]): Promise<boolean>

Añade nuevas columnas a la tabla.

1await usersTable
2  .columns()
3  .add([{ name: "phone", type: "VARCHAR", length: 15 }]);

edit(columns: Field[]): Promise<boolean>

Modifica columnas existentes en la tabla.

1await usersTable.columns().edit([
2  {
3    name: "email",
4    type: "VARCHAR",
5    length: 255,
6    defaultValue: "example@mail.com",
7  },
8]);

delete(columns: string[]): Promise<boolean>

Elimina columnas de la tabla.

1await usersTable.columns().delete(["phone"]);

Licencia

Este proyecto está licenciado bajo la Licencia MIT - consulta el archivo LICENSE para más detalles.