Huge News!Announcing our $40M Series B led by Abstract Ventures.Learn More →
Socket
Sign inDemoInstall
Socket
Sign inDemoInstall

node-mysql-query-utils

Package Overview
Dependencies
Maintainers
1
Versions
5
Alerts
File Explorer

Advanced tools

License
Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

node-mysql-query-utils - npm Package Compare versions

Comparing version 1.0.2 to 1.0.3

8

CHANGELOG.md
# Changelog
## [1.0.3] - 2024-10-01
### Improved
- Enhanced parameter checking and error messages for `TableModel` class return methods.
### Added
- Added `NOT IN`, `NOT LIKE`, and `REGEXP` operators to the `where` condition parameter in the `SQLBuilder` class.
- For `LIKE` and `NOT LIKE` operators, previously only accepted strings. Now added support for `contains`, `startsWith`, and `endsWith` for more flexible use cases.
## [1.0.2] - 2024-09-30

@@ -4,0 +12,0 @@ ### Added

52

dist/dto/sql-builder-class.js

@@ -135,3 +135,3 @@ "use strict";

for (const operator in value) {
// Handle IN, BETWEEN, NOT_BETWEEN, =, !=, <, <=, >, >=, LIKE, IS_NULL, IS_NOT_NULL
// Handle IN
if (operator === 'IN' && Array.isArray(value[operator])) {

@@ -143,3 +143,12 @@ if (value[operator].length === 0) {

localParams.push(...value[operator]);
// Handle NOT_IN
}
else if (operator === 'NOT_IN' && Array.isArray(value[operator])) {
if (value[operator].length === 0) {
throw new Error(this.printPrefixMessage(`processConditions :: NOT_IN :: condition must be a non-empty array`));
}
clauses.push(`${sanitizedKey} NOT IN (${value[operator].map(() => '?').join(', ')})`);
localParams.push(...value[operator]);
// Handle BETWEEN
}
else if (operator === 'BETWEEN' && Array.isArray(value[operator])) {

@@ -151,2 +160,3 @@ if (value[operator].length !== 2) {

localParams.push(value[operator][0], value[operator][1]);
// Handle NOT_BETWEEN
}

@@ -159,7 +169,45 @@ else if (operator === 'NOT_BETWEEN' && Array.isArray(value[operator])) {

localParams.push(value[operator][0], value[operator][1]);
// Handle =, !=, <, <=, >, >=
}
else if (['=', '!=', '<', '<=', '>', '>=', 'LIKE'].includes(operator)) {
else if (['=', '!=', '<', '<=', '>', '>='].includes(operator)) {
clauses.push(`${sanitizedKey} ${operator} ?`);
localParams.push(value[operator]);
// Handle LIKE, NOT LIKE
}
else if (operator === 'LIKE' || operator === 'NOT_LIKE') {
const patternType = value[operator];
const clauseString = `${sanitizedKey} ${operator === 'NOT_LIKE' ? 'NOT LIKE' : 'LIKE'} ?`;
if (typeof patternType === 'string') {
// Direct string pattern
clauses.push(clauseString);
localParams.push(patternType);
}
else if (typeof patternType === 'object' && patternType !== null) {
switch (true) {
case !!patternType.contains:
clauses.push(clauseString);
localParams.push(`%${patternType.contains}%`);
break;
case !!patternType.startsWith:
clauses.push(clauseString);
localParams.push(`${patternType.startsWith}%`);
break;
case !!patternType.endsWith:
clauses.push(clauseString);
localParams.push(`%${patternType.endsWith}`);
break;
default:
throw new Error(this.printPrefixMessage(`processConditions :: ${operator} :: Invalid pattern type`));
}
}
// Handle REGEXP
}
else if (operator === "REGEXP") {
if (typeof value[operator] !== 'string' || value[operator].length === 0) {
throw new Error(this.printPrefixMessage(`processConditions :: ${operator} :: condition must be a non empty string`));
}
clauses.push(`${sanitizedKey} REGEXP ?`);
localParams.push(value[operator]);
// Handle IS_NULL, IS_NOT_NULL
}
else if (['IS_NULL', 'IS_NOT_NULL'].includes(operator)) {

@@ -166,0 +214,0 @@ if (value[operator] !== true) {

18

dist/dto/types.d.ts

@@ -204,2 +204,7 @@ import { ConnectionOptions } from "mysql2/promise";

}
type LikePatternType = {
contains?: string;
startsWith?: string;
endsWith?: string;
};
type EqualOperator = {

@@ -224,7 +229,16 @@ '='?: any;

type LikeOperator = {
'LIKE'?: any;
'LIKE'?: LikePatternType | string;
};
type NotLikeOperator = {
'NOT_LIKE'?: LikePatternType | string;
};
type RegexpOperator = {
'REGEXP'?: string;
};
type InOperator = {
'IN'?: any[];
};
type NotInOperator = {
'NOT_IN'?: any[];
};
type BetweenOperator = {

@@ -242,3 +256,3 @@ 'BETWEEN'?: [any, any];

};
type OperatorCondition = Prettify<EqualOperator & NotEqualOperator & LessThanOperator & LessThanOrEqualOperator & GreaterThanOperator & GreaterThanOrEqualOperator & LikeOperator & InOperator & BetweenOperator & NotBetweenOperator & IsNullOperator & IsNotNullOperator>;
type OperatorCondition = Prettify<EqualOperator & NotEqualOperator & LessThanOperator & LessThanOrEqualOperator & GreaterThanOperator & GreaterThanOrEqualOperator & LikeOperator & NotLikeOperator & RegexpOperator & InOperator & NotInOperator & BetweenOperator & NotBetweenOperator & IsNullOperator & IsNotNullOperator>;
export type SimpleCondition<ColumnKeys extends string> = {

@@ -245,0 +259,0 @@ [key in ColumnKeys]?: OperatorCondition | string | number;

3

dist/src/table-model/index.d.ts

@@ -25,3 +25,3 @@ import { SQLBuilder } from "../../dto/sql-builder-class";

}) => import("../../dto/types").WhereQueryBuilder<ColumnKeys, ResultSetHeader>;
createInsert(options?: InsertOptions): (data: ColumnData<ColumnKeys>) => import("../../dto/types").InsertQueryBuilder<ResultSetHeader>;
createInsert(options?: InsertOptions): (data: InsertValue<ColumnKeys>) => import("../../dto/types").InsertQueryBuilder<ResultSetHeader>;
createDelete(): (values: {

@@ -58,3 +58,2 @@ where: WhereCondition<ColumnKeys>;

where: WhereCondition<ColumnKeys>;
orderBy?: OrderByField<ColumnKeys>[];
}): QueryAction<ResultSetHeader>;

@@ -61,0 +60,0 @@ patchSingleField<T>(values: {

33

dist/src/table-model/index.js

@@ -104,4 +104,9 @@ "use strict";

return (data) => {
this.throwEmptyObjectError(data, this.printPrefixMessage('CreateInsert :: Data cannot be empty'));
const structuredData = { ...data };
if (Array.isArray(data)) {
this.throwEmptyArrayError(data, this.printPrefixMessage('CreateInsert :: Data cannot be empty'));
}
else {
this.throwEmptyObjectError(data, this.printPrefixMessage('CreateInsert :: Data cannot be empty'));
}
const structuredData = Array.isArray(data) ? data : [data];
this.removeExtraFieldsAndLog(structuredData);

@@ -114,3 +119,3 @@ return SQLBuild.insert(this.tableName, structuredData, options);

return (values) => {
const { where } = values || {};
const { where = {} } = values || {};
this.throwEmptyObjectError(where, this.printPrefixMessage('CreateDelete :: Where condition cannot be empty'));

@@ -128,3 +133,3 @@ return SQLBuild.deleteFrom(this.tableName)

findOne(values) {
const { where, orderBy = [], fields } = values || {};
const { where = {}, orderBy = [], fields } = values || {};
this.throwEmptyObjectError(where, this.printPrefixMessage('FindOne :: Where condition cannot be empty'));

@@ -151,3 +156,3 @@ const SQLBuild = this.initSQLBuilder();

updateOne(values) {
const { data, where, options } = values || {};
const { data = {}, where = {}, options } = values || {};
this.throwEmptyObjectError(where, this.printPrefixMessage('UpdateOne :: Where condition cannot be empty'));

@@ -163,3 +168,6 @@ this.throwEmptyObjectError(data, this.printPrefixMessage('UpdateOne :: Data cannot be empty'));

updateAll(values) {
const { data, where = {}, options } = values || {};
const { data = {}, where = {}, options } = values || {};
this.throwEmptyObjectError(data, this.printPrefixMessage('UpdateOne :: Data cannot be empty'));
if (where)
this.throwEmptyObjectError(where, this.printPrefixMessage('UpdateOne :: Where condition cannot be empty'));
if (this.primaryKey in data)

@@ -184,3 +192,3 @@ delete data[this.primaryKey]; // For javascript type checking

removeOne(values) {
const { where, orderBy = [] } = values || {};
const { where = {}, orderBy = [] } = values || {};
this.throwEmptyObjectError(where, this.printPrefixMessage('RemoveOne :: Where condition cannot be empty'));

@@ -194,3 +202,3 @@ const SQLBuild = this.initSQLBuilder();

remove(values) {
const { where = {}, orderBy = [] } = values || {};
const { where = {} } = values || {};
// Prevent accidental deletion of all records

@@ -203,5 +211,4 @@ this.throwEmptyObjectError(where, this.printPrefixMessage('Remove :: Where condition cannot be empty'));

patchSingleField(values) {
const { where, value, options, patchField } = values || {};
// const { patchField } = options || {};
if (!patchField) {
const { where = {}, value, options, patchField } = values || {};
if (typeof patchField !== 'string' || patchField.length === 0) {
throw new Error(this.printPrefixMessage('PatchSingleField :: Patch field is required'));

@@ -216,3 +223,3 @@ }

softDeleteOne(values) {
const { where, value, options } = values || {};
const { where = {}, value, options } = values || {};
this.throwEmptyObjectError(where, this.printPrefixMessage('SoftDeleteOne :: Where condition cannot be empty'));

@@ -226,3 +233,3 @@ const SQLBuild = this.initSQLBuilder();

softDelete(values) {
const { where, value, options } = values || {};
const { where = {}, value, options } = values || {};
this.throwEmptyObjectError(where, this.printPrefixMessage('SoftDelete :: Where condition cannot be empty'));

@@ -229,0 +236,0 @@ const SQLBuild = this.initSQLBuilder();

48

dto/sql-builder-class.ts

@@ -169,3 +169,3 @@ import {

for (const operator in value) {
// Handle IN, BETWEEN, NOT_BETWEEN, =, !=, <, <=, >, >=, LIKE, IS_NULL, IS_NOT_NULL
// Handle IN
if (operator === 'IN' && Array.isArray(value[operator])) {

@@ -177,2 +177,10 @@ if (value[operator].length === 0) {

localParams.push(...value[operator]);
// Handle NOT_IN
} else if (operator === 'NOT_IN' && Array.isArray(value[operator])) {
if (value[operator].length === 0) {
throw new Error(this.printPrefixMessage(`processConditions :: NOT_IN :: condition must be a non-empty array`));
}
clauses.push(`${sanitizedKey} NOT IN (${value[operator].map(() => '?').join(', ')})`);
localParams.push(...value[operator]);
// Handle BETWEEN
} else if (operator === 'BETWEEN' && Array.isArray(value[operator])) {

@@ -184,2 +192,3 @@ if (value[operator].length !== 2) {

localParams.push(value[operator][0], value[operator][1]);
// Handle NOT_BETWEEN
} else if (operator === 'NOT_BETWEEN' && Array.isArray(value[operator])) {

@@ -191,5 +200,40 @@ if (value[operator].length !== 2) {

localParams.push(value[operator][0], value[operator][1]);
} else if (['=', '!=', '<', '<=', '>', '>=', 'LIKE'].includes(operator)) {
// Handle =, !=, <, <=, >, >=
} else if (['=', '!=', '<', '<=', '>', '>='].includes(operator)) {
clauses.push(`${sanitizedKey} ${operator} ?`);
localParams.push(value[operator]);
// Handle LIKE, NOT LIKE
} else if (operator === 'LIKE' || operator === 'NOT_LIKE') {
const patternType = value[operator];
const clauseString = `${sanitizedKey} ${operator === 'NOT_LIKE' ? 'NOT LIKE' : 'LIKE'} ?`;
if (typeof patternType === 'string') {
// Direct string pattern
clauses.push(clauseString);
localParams.push(patternType);
} else if (typeof patternType === 'object' && patternType !== null) {
switch (true) {
case !!patternType.contains:
clauses.push(clauseString);
localParams.push(`%${patternType.contains}%`);
break;
case !!patternType.startsWith:
clauses.push(clauseString);
localParams.push(`${patternType.startsWith}%`);
break;
case !!patternType.endsWith:
clauses.push(clauseString);
localParams.push(`%${patternType.endsWith}`);
break;
default:
throw new Error(this.printPrefixMessage(`processConditions :: ${operator} :: Invalid pattern type`));
}
}
// Handle REGEXP
} else if (operator === "REGEXP") {
if (typeof value[operator] !== 'string' || value[operator].length === 0) {
throw new Error(this.printPrefixMessage(`processConditions :: ${operator} :: condition must be a non empty string`));
}
clauses.push(`${sanitizedKey} REGEXP ?`);
localParams.push(value[operator]);
// Handle IS_NULL, IS_NOT_NULL
} else if (['IS_NULL', 'IS_NOT_NULL'].includes(operator)) {

@@ -196,0 +240,0 @@ if (value[operator] !== true) {

14

dto/types.ts

@@ -174,2 +174,8 @@ import { ConnectionOptions } from "mysql2/promise";

type LikePatternType = {
contains?: string;
startsWith?: string;
endsWith?: string;
};
type EqualOperator = { '='?: any };

@@ -181,4 +187,7 @@ type NotEqualOperator = { '!='?: any };

type GreaterThanOrEqualOperator = { '>='?: any };
type LikeOperator = { 'LIKE'?: any };
type LikeOperator = { 'LIKE'?: LikePatternType | string };
type NotLikeOperator = { 'NOT_LIKE'?: LikePatternType | string };
type RegexpOperator = { 'REGEXP'?: string };
type InOperator = { 'IN'?: any[] };
type NotInOperator = { 'NOT_IN'?: any[] };
type BetweenOperator = { 'BETWEEN'?: [any, any] };

@@ -196,3 +205,6 @@ type NotBetweenOperator = { 'NOT_BETWEEN'?: [any, any] };

LikeOperator &
NotLikeOperator &
RegexpOperator &
InOperator &
NotInOperator &
BetweenOperator &

@@ -199,0 +211,0 @@ NotBetweenOperator &

12

package.json
{
"name": "node-mysql-query-utils",
"version": "1.0.2",
"version": "1.0.3",
"description": "A simple MySQL query utils for Node.js",

@@ -9,3 +9,3 @@ "author": "John Tam <johntam718@gmail.com>",

"type": "git",
"url": "https://github.com/johntam718/node-mysql2-helper.git"
"url": "git+https://github.com/johntam718/node-mysql2-helper.git"
},

@@ -18,3 +18,9 @@ "homepage": "https://github.com/johntam718/node-mysql2-helper#readme",

"utils",
"node"
"node",
"nodejs",
"query-builder",
"query-builder-mysql",
"mysql-query-builder",
"mysql-query-utils",
"mysql-query-helper"
],

@@ -21,0 +27,0 @@ "main": "dist/index.js",

628

README.md
# node-mysql-query-utils
A MySQL query builder and helper for Node.js.
A MySQL helper to provide ways to connect to the database, build SQL queries, and perform common database operations for Node.js.
[![npm version](https://badge.fury.io/js/node-mysql-query-utils.svg)](https://badge.fury.io/js/node-mysql-query-utils)
<!-- [![npm downloads](https://img.shields.io/npm/dm/node-mysql-query-utils.svg)](https://www.npmjs.com/package/node-mysql-query-utils) -->
## Table of contents
- [Introduction](#introduction)
- [Installation](#installation)
- [Overview](#overview)
- [Quick Start](#quick-start)
- [DatabaseManagement](#databasemanagement)
- [Single database connection](#single-database-connection)
- [Multiple database connections](#multiple-database-connections)
- [SQLBuilder Class](#sqlbuilder-class)
- [Example](#example)
- [.select() Method](#select-method)
- [.from() Method](#from-method)
- [.join() Method](#join-method)
- [.where() Method](#where-method)
- [.orderBy() Method](#orderby-method)
- [.limit() and .offset() Methods](#limit-and-offset-methods)
- [.insert() Method](#insert-method)
- [.update() Method](#update-method)
- [Update Options](#update-options)
- [.delete() Method](#delete-method)
- [.buildQuery() Method](#buildquery-method)
- [.executeQuery() Method](#executequery-method)
- [TableModel Class](#tablemodel-class)
- [1. Using DatabaseManagement to Connect to the Database](#1-using-databasemanagement-to-connect-to-the-database)
- [2. Using the TableModel Class Directly](#2-using-the-tablemodel-class-directly)
- [Methods](#methods)
- [createSelect](#createselect)
- [createInsert](#createinsert)
- [createDelete](#createdelete)
- [createCount](#createcount)
- [Common Parameters](#common-parameters)
- [findOne](#findone)
- [findAll](#findall)
- [updateOne](#updateone)
- [updateAll](#updateall)
- [insertRecord](#insertrecord)
- [removeOne](#removeone)
- [remove](#remove)
- [patchSingleField](#patchsinglefield)
- [softDeleteOne](#softdeleteone)
- [softDelete](#softdelete)
- [Changelog](#changelog)
## Introduction
`node-mysql-query-utils` is a MySQL query builder and helper for Node.js. It provides a simple and easy way to build and execute MySQL queries.
## Installation

@@ -21,8 +67,21 @@

## Usage
## Overview
### Common usage
This package provides three classes to help you manage and interact with your MySQL database:
1. [DatabaseManagement](#databasemanagement)
2. [SQLBuilder](#sqlbuilder-class)
3. [TableModel](#tablemodel-class)
## Quick Start
Here’s a quick overview of how to make use of this library:
```typescript
import { DatabaseManagement, TableModel, SQLBuilder, sqlHelper } from "node-mysql-query-utils";
import {
DatabaseManagement,
TableModel,
SQLBuilder,
sqlHelper,
} from "node-mysql-query-utils";

@@ -33,3 +92,3 @@ // entry point of the application e.g. start server

{
identifierName: 'mainDB',
identifierName: "mainDB",
config: {

@@ -41,8 +100,26 @@ host: "localhost",

},
}
options: {
verbose: true, // optional, default is true. If true, will log all queries to console
},
},
]);
//or
DatabaseManagement.connectSingleDatabase(
"mainDB",
{
host: "localhost",
user: "root",
password: "password",
database: "test_db",
},
{
verbose: true, // optional, default is true. If true, will log all queries to console
}
);
// model folder
// user.ts
const mainDB = DatabaseManagement.getInstance('mainDB'); // referring to the identifierName
const mainDB = DatabaseManagement.getInstance("mainDB"); // get instance of mainDB
const columns = sqlHelper.createColumns([

@@ -63,8 +140,8 @@ "user_id",

centralFields: {
ctimeField: 'ctime', // created_at column name in db
utimeField: 'utime', // updated_at column name in db
isActiveField: 'is_active', // is_active column name in db
isDeletedField: 'is_deleted', // is_deleted column name in db
statusField: 'status', // status column name in db
}
ctimeField: "ctime", // created_at column name in db
utimeField: "utime", // updated_at column name in db
isActiveField: "is_active", // is_active column name in db
isDeletedField: "is_deleted", // is_deleted column name in db
statusField: "status", // status column name in db
},
});

@@ -74,47 +151,10 @@

// get user by id
const [user] = await userModel.findOne({ where: { user_id: 1234 } }).executeQuery();
// get all users
const users = await userModel.findAll().executeQuery();
// updateOne user
const updatedOneUser = await userModel.updateOne({
where: { user_id: 1052 },
data: { email: '123@gmail.com' }
}).executeQuery()
// deleteOne user
const deletedOneUser = await userModel.removeOne({
where: { user_id: { ">": 1234 } },
orderBy: [{ field: 'user_id', direction: 'ASC' }]
}).executeQuery()
// deleteAll users
const deletedAllUsers = await userModel.remove({ where: { user_id: 1234 } }).executeQuery()
// soft delete user
// update is_deleted = 1, utime = current_timestamp
const softDeletedUser = await userModel.softDelete({
where: { user_id: 1234 },
value: 1,
options: { enableTimestamps: true, deleteField: 'is_deleted', utimeField: 'utime' }
}).executeQuery();
// patch single field
const patchedActiveField = await userModel.patchSingleField({
patchField: 'is_active',
where: { user_id: 1234 },
value: 1,
options: { enableTimestamps: true, utimeField: 'utime' } // update updated_at field
}).executeQuery();
```
### Database Management
## DatabaseManagement
The **`DatabaseManagement`** class is a singleton class that helps manage database connections. It supports connecting to single or multiple databases and provides a way to retrieve instances of the connections.
#### Example
#### Single database connection
Single database connection
```typescript

@@ -139,3 +179,3 @@ import { DatabaseManagement } from "node-mysql-query-utils";

Multiple database connections
#### Multiple database connections

@@ -176,11 +216,9 @@ ```typescript

### Summary
# SQLBuilder Class
- **Example**: Shows how to connect to a single database and retrieve the instance.
- **Connecting to Multiple Databases**: Demonstrates how to connect to multiple databases and retrieve their instances.
- **API**: Documents the main methods of the [`DatabaseManagement`] class.
The **`SQLBuilder`** class provides a way to build SQL queries for various operations such as SELECT, INSERT, UPDATE, DELETE, etc.
### Table Model
To generate the final SQL query and its parameters, you must call the `.buildQuery()` method at the end of your query-building process.
The **`TableModel`** class provides a way to build a table model from a JSON object. The table model can be used to create a table in a database.
If the `SQLBuilder` constructor is provided with a `queryFn`, you can use the `.executeQuery()` method to execute the query directly.

@@ -190,62 +228,2 @@ #### Example

```typescript
import {
DatabaseManagement,
TableModel,
sqlHelper,
} from "node-mysql-query-utils";
// Connected to a database
const dbInstance = DatabaseManagement.getInstance("mainDB");
// Define the columns of the table
const columns = sqlHelper.createColumns([
"user_id",
"ctime",
"utime",
"email",
"mobile",
"password",
"is_active",
"is_deleted",
]);
// E.g. Create a table model for a user table
// Define the table model by using the DatabaseManagement instance
const userAccountModel = master.createTableModel({
tableName: "user_account",
primaryKey: "user_id",
columns,
});
// or
// Define the table model by using the TableModel class directly
const userAccountModel = new TableModel({
tableName: "user_account",
primaryKey: "user_id",
columns,
queryFn: db.query.bind(db), // Optional: put your own query function here if you don't connect DB by DatabaseManagement class from this package
});
```
#### Query Function
The **queryFn** is an optional function that should match the following type definition:
```typescript
type QueryFunction = <T>(sql: string, params?: any[]) => Promise<T>;
```
### Summary
- **Example**: Shows how to create a table model for a user table.
- **API**: Documents the main methods of the [`TableModel`] class.
### SQL Builder
The **`SQLBuilder`** class provides a way to build SQL queries for CRUD operations.
#### Example
```typescript
import { SQLBuilder, sqlHelper } from "node-mysql-query-utils";

@@ -256,5 +234,2 @@

// Javascript
const sqlBuilder = new SQLBuilder();
const columns = sqlHelper.createColumns([

@@ -272,4 +247,7 @@ "user_id",

// typescript
const sqlBuilder = new SQLBuilder<typeof columns[number]>();
const sqlBuilder = new SQLBuilder<(typeof columns)[number]>();
// Javascript - no type hinting, but still works. Suggest using TableModel class for better type hinting if using Javascript
const sqlBuilder = new SQLBuilder();
// Accept QueryFunction for SQLBuilder to enable query execution

@@ -279,3 +257,5 @@ const sqlBuilder = new SQLBuilder(db.query.bind(db)); // Optional: put your own query function here if you don't connect DB by DatabaseManagement class from this package

// Accept second generic type for query function
const sqlBuilder = new SQLBuilder<typeof columns[number], any>(db.query.bind(db)); // Optional: put your own query function here if you don't connect DB by DatabaseManagement class from this package
const sqlBuilder = new SQLBuilder<(typeof columns)[number], any>(
db.query.bind(db)
); // Optional: put your own query function here if you don't connect DB by DatabaseManagement class from this package

@@ -287,3 +267,6 @@ // Call buildQuery to get the SQL query and parameters in the end

// BuildQuery also accepts an options object. it will return complete query with format. params will be empty array if format is true
const [sql, params] = sqlBuilder.select().from(tableName).buildQuery({ format: true });
const [sql, params] = sqlBuilder
.select()
.from(tableName)
.buildQuery({ format: true });

@@ -408,2 +391,32 @@ // run your own query function if you don't connect DB by DatabaseManagement class from this package

```typescript
// AND example
const [sql, params] = sqlBuilder
.select()
.from("user_account")
.where({
email: "123@gmail.com",
is_active: 1,
})
.buildQuery();
// alternative AND example
const [sql, params] = sqlBuilder
.select()
.from("user_account")
.where({
AND: [{ email: { LIKE: "%@gmail.com" } }, { username: "jane" }],
});
```
```typescript
// OR example
const [sql, params] = sqlBuilder
.select()
.from("user_account")
.where({
OR: [{ email: { LIKE: "%@gmail.com" } }, { username: "jane" }],
});
```
Supported Operators:

@@ -417,4 +430,7 @@

- `<=`
- `LIKE`
- `IN`
- `LIKE`,
- `NOT_LIKE`
- `REGEXP`,
- `IN`,
- `NOT_IN`
- `BETWEEN`

@@ -470,12 +486,31 @@ - `NOT_BETWEEN`

const [sql, params] = sqlBuilder
.insert('user_account', {email: '123@gmail.com'}, {
enableTimestamps = false, // if true, will add ctime and utime to insert object
ctimeField = 'ctime',
utimeField = 'utime',
ctimeValue = Math.floor(Date.now() / 1000), // default is current unix timestamp
utimeValue = Math.floor(Date.now() / 1000) // default is current unix timestamp
})
.insert(
"user_account",
{ email: "123@gmail.com" },
{
enableTimestamps = false, // if true, will add ctime and utime to insert object
ctimeField = "ctime",
utimeField = "utime",
ctimeValue = Math.floor(Date.now() / 1000), // default is current unix timestamp
utimeValue = Math.floor(Date.now() / 1000), // default is current unix timestamp
insertIgnore = false, // if true, will add IGNORE to the insert query
}
)
.buildQuery();
```
### Insert Options
The `insert` method accepts options parameter with the following properties:
| Property | Type | Description |
| ------------------ | ------- | ------------------------------------------------------------------------------------- |
| `enableTimestamps` | boolean | (Optional) If true, will add `ctime` and `utime` to the insert object. Default is false. |
| `ctimeField` | string | (Optional) The field name for the create timestamp. Default is `ctime`. |
| `utimeField` | string | (Optional) The field name for the update timestamp. Default is `utime`. |
| `ctimeValue` | any | (Optional) The value for the create timestamp. Default is the current Unix timestamp. |
| `utimeValue` | any | (Optional) The value for the update timestamp. Default is the current Unix timestamp. |
| `insertIgnore` | boolean | (Optional) If true, will add `IGNORE` to the insert query. Default is false. |
### .update() Method

@@ -486,8 +521,13 @@

const [sql, params] = sqlBuilder
.update('user_account', {email: '123@gmail.com'}, {
enableTimestamps = false, // if true, will add utime to update object
utimeField = 'utime',
utimeValue = Math.floor(Date.now() / 1000) // default is current unix timestamp
})
.where({user_id: 1})
.update(
"user_account",
{ email: "123@gmail.com" },
// Options
{
enableTimestamps = false, // if true, will add utime to update object
utimeField = "utime",
utimeValue = Math.floor(Date.now() / 1000), // default is current unix timestamp
}
)
.where({ user_id: 1 })
.buildQuery();

@@ -497,8 +537,19 @@

const [sql, params] = sqlBuilder
.update('user_account')
.set({email: '123@gmail.com'})
.where({user_id: 1})
.update("user_account")
.set({ email: "123@gmail.com" })
.where({ user_id: 1 })
.buildQuery();
```
### Update Options
The `update` method accepts options parameter with the following properties:
| Property | Type | Description |
| ------------------ | ------- | ------------------------------------------------------------------------------------- |
| `enableTimestamps` | boolean | (Optional) If true, will add `utime` to the update object. Default is false. |
| `primaryKey` | string | (Optional) The primary key field name. For removing primaryKey in update object |
| `utimeField` | string | (Optional) The field name for the update timestamp. Default is `utime`. |
| `utimeValue` | any | (Optional) The value for the update timestamp. Default is the current Unix timestamp. |
### .delete() Method

@@ -508,12 +559,14 @@

// Delete Whole Table
const [sql, params] = sqlBuilder.deleteFrom('user_account').buildQuery();
const [sql, params] = sqlBuilder.deleteFrom("user_account").buildQuery();
// Delete with where
const [sql, params] = sqlBuilder.deleteFrom('user_account')
.where({user_id: 1})
const [sql, params] = sqlBuilder
.deleteFrom("user_account")
.where({ user_id: 1 })
.buildQuery();
// Delete with limit
const [sql, params] = sqlBuilder.deleteFrom('user_account')
.where({user_id: {">": 1}})
const [sql, params] = sqlBuilder
.deleteFrom("user_account")
.where({ user_id: { ">": 1 } })
.limit(1)

@@ -523,4 +576,279 @@ .buildQuery();

### .buildQuery() Method
```typescript
// Build query with format
const sqlBuilder = new SQLBuilder<(typeof columns)[number]>();
const [sql, params] = sqlBuilder
.select()
.from("user_account")
.buildQuery({ format: true });
```
### .executeQuery() Method
```typescript
// Execute query
const sqlBuilder = new SQLBuilder<(typeof columns)[number]>();
const result = await sqlBuilder.select().from("user_account").executeQuery();
```
### Without calling .buildQuery() or .executeQuery()
```typescript
// Without calling .buildQuery() or .executeQuery() as end of query building
const sqlBuilder = new SQLBuilder<(typeof columns)[number]>();
const result = sqlBuilder.select().from("user_account");
// result will be an instance of SQLBuilder
// SQLBuilder {
// queryFn: [Function: bound executeQuery] AsyncFunction,
// message: 'Call .buildQuery() or .executeQuery() to get the result'
// }
// To get the result, call .buildQuery() or .executeQuery()
const [sql, params] = result.buildQuery();
// or
const result = await result.executeQuery(); // if query function is provided in SQLBuilder
```
## TableModel Class
The **`TableModel`** class is a wrapper around the **`SQLBuilder`** class that provides a way to build and execute common database operations like SELECT, INSERT, UPDATE, DELETE, etc.
To generate the final SQL query and its parameters, you must call the `.buildQuery()` method at the end of your query-building process. If you have provided a query function, you can use the `.executeQuery()` method to execute the query directly.
There are two ways to define and use the `TableModel` class:
### 1. Using DatabaseManagement to Connect to the Database
Connect database by `DatabaseManagement` class can refer to the [Quick Start](#quick-start) example. This method involves connecting to multiple databases using the `DatabaseManagement` class and then retrieving instances to create table models.
### 2. Using the TableModel Class Directly
Alternatively, you can define the table model by using the `TableModel` class directly. This method is useful if you prefer to use your own query function or if you don't connect to the database using the `DatabaseManagement` class from this package.
```typescript
// Define the table model by using the TableModel class directly
const userModel = new TableModel({
tableName: "users",
primaryKey: "user_id",
columns: sqlHelper.createColumns(["user_id", "name", "email", "phone"]),
queryFn: db.query.bind(db), // Optional: put your own query function here if you don't connect DB by DatabaseManagement class from this package
});
```
### Methods
The `TableModel` class provides the following methods to perform database operations:
| Method | Description |
| ------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --- |
| `createSelect` | Creates a SELECT query. Returns a function that returns an instance of the `SQLBuilder` class. You can chain the `SQLBuilder` methods to build your desired query. |
| `createInsert` | Creates an INSERT query. Returns a function that returns an instance of the `SQLBuilder` class. You can chain the `SQLBuilder` methods to build your desired query. |
| `createUpdate` | Creates an UPDATE query. Returns a function that returns an instance of the `SQLBuilder` class. You can chain the `SQLBuilder` methods to build your desired query. |
| `createDelete` | Creates a DELETE query. Returns a function that returns an instance of the `SQLBuilder` class. You can chain the `SQLBuilder` methods to build your desired query. |
| `createCount` | Creates a COUNT query. Returns a function that returns an instance of the `SQLBuilder` class. You can chain the `SQLBuilder` methods to build your desired query. |
| `findOne` | Finds a single record. Returns an instance of the `SQLBuilder` class with the `findOne` method configured. |
| `findAll` | Finds multiple records. Returns an instance of the `SQLBuilder` class with the `findAll` method configured. |
| `remove` | Removes records based on conditions. Returns an instance of the `SQLBuilder` class with the `remove` method configured. |
| `removeOne` | Removes a single record based on conditions. Returns an instance of the `SQLBuilder` class with the `removeOne` method configured. |
| `patchSingleField` | Updates a single field in records based on conditions. Returns an instance of the `SQLBuilder` class with the `patchSingleField` method configured. |
| `softDeleteOne` | Soft deletes a single record based on conditions. Returns an instance of the `SQLBuilder` class with the `softDeleteOne` method configured. |
| `softDelete` | Soft deletes multiple records based on conditions. Returns an instance of the `SQLBuilder` class with the `softDelete` method configured. | |
The `TableModel` class provides the following methods to perform database operations.
Methods that start with `create` (e.g., `createSelect`, `createInsert`) return a function that returns an instance of the `SQLBuilder` class. You can chain the `SQLBuilder` methods to build your desired query.
Other methods, such as `findOne`, return an instance of the `SQLBuilder` class that is already configured with some chained methods. These methods act as wrapper functions for common CRUD operations and are not intended for further chaining.
#### createSelect
```typescript
const selectUser = userModel.createSelect();
const result = await selectUser({
fields: ["user_id", "email", "nickname"],
})
.where({ user_id: 1 })
.executeQuery();
```
#### createInsert
```typescript
const insertUser = userModel.createInsert();
const result = await insertUser({
data: { user_id: 1, email: "123@email.com", nickname: "John Doe" },
}).executeQuery();
```
#### createUpdate
```typescript
const updateUser = userModel.createUpdate();
const result = await updateUser({
data: { nickname: "John Doe 2" },
where: { user_id: { "<=": 5 } },
})
.limit(2)
.executeQuery();
```
#### createDelete
```typescript
const deleteUser = userModel.createDelete();
const result = await deleteUser({ where: { user_id: 1 } })
.limit(1)
.executeQuery();
```
#### createCount
```typescript
const countResult = await countUser("user_id")
.where({ user_id: { "<": 2 } })
.executeQuery();
```
### Common Parameters
The following table lists common parameters that many methods accept. Note that some parameters may be available for certain methods while others may not, as each method performs different operations. TypeScript will provide hints for the available parameters for each method.
| Parameter | Type | Description |
| --------- | ------ | -------------------------------------------------------------------------------------------------------------- |
| `fields` | Array | An array of strings specifying the columns to select. |
| `where` | Object | An object specifying the conditions for the query. |
| `orderBy` | Array | An array of objects specifying the columns to order by and the direction (ASC or DESC). |
| `limit` | Number | A number specifying the maximum number of rows to return. |
| `offset` | Number | A number specifying the offset of the first row to return. |
| `options` | Object | An object specifying additional options for the query. Can be referenced in [Update Options](#update-options). |
#### findOne
```typescript
const [user] = await userModel
.findOne({ where: { user_id: 1 } })
.executeQuery();
```
#### findAll
```typescript
const users = await userModel
.findAll({
fields: ["user_id", "email"],
where: { is_active: true },
orderBy: [{ column: "user_id", direction: "ASC" }],
limit: 10,
offset: 0,
})
.executeQuery();
```
#### updateOne
```typescript
const updatedOneUser = await userModel
.updateOne({
where: { user_id: 1 },
data: { email: "123@gmail.com" },
})
.executeQuery();
```
#### updateAll
```typescript
const updateAllUsers = await userModel
.updateAll({
data: { nickname: "John Doe" },
where: { user_id: { "<=": 5 } },
options: { enableTimestamps: true, utimeField: "utime" },
})
.executeQuery();
```
#### insertRecord
```typescript
const insertedUser = await userModel
.insertRecord({
data: { user_id: 1, email: "123@gmail.com", nickname: "John Doe" },
options: {
enableTimestamps: true,
ctimeField: "ctime",
utimeField: "utime",
},
})
.executeQuery();
```
#### removeOne
```typescript
const deletedOneUser = await userModel
.removeOne({
where: { user_id: { ">": 1 } },
orderBy: [{ field: "user_id", direction: "ASC" }],
})
.executeQuery();
```
#### remove
```typescript
const deletedAllUsers = await userModel
.remove({ where: { user_id: 1 } })
.executeQuery();
```
#### patchSingleField
```typescript
const patchedActiveField = await userModel
.patchSingleField({
patchField: "is_active",
where: { user_id: 1 },
value: 1,
options: { enableTimestamps: true, utimeField: "utime" },
})
.executeQuery();
```
#### softDeleteOne
```typescript
const softDeletedUser = await userModel
.softDeleteOne({
where: { user_id: 1 },
value: 1,
options: {
enableTimestamps: true,
deleteField: "is_deleted",
utimeField: "utime",
},
})
.executeQuery();
```
#### softDelete
```typescript
const softDeletedUsers = await userModel
.softDelete({
where: { user_id: { ">": 1 } },
value: 1,
options: {
enableTimestamps: true,
deleteField: "is_deleted",
utimeField: "utime",
},
})
.executeQuery();
```
## Changelog
Detailed changes for each version are documented in the [CHANGELOG.md](https://github.com/johntam718/node-mysql2-helper/blob/main/CHANGELOG.md) file.
Detailed changes for each version are documented in the [CHANGELOG.md](https://github.com/johntam718/node-mysql2-helper/blob/main/CHANGELOG.md) file.

35

src/table-model/index.ts

@@ -137,7 +137,10 @@ import { SQLBuilder } from "@dto/sql-builder-class";

const SQLBuild = this.initSQLBuilder<ColumnKeys, ResultSetHeader>();
return (data: ColumnData<ColumnKeys>) => {
this.throwEmptyObjectError(data, this.printPrefixMessage('CreateInsert :: Data cannot be empty'));
const structuredData = { ...data };
return (data: InsertValue<ColumnKeys>) => {
if (Array.isArray(data)) {
this.throwEmptyArrayError(data, this.printPrefixMessage('CreateInsert :: Data cannot be empty'));
} else {
this.throwEmptyObjectError(data, this.printPrefixMessage('CreateInsert :: Data cannot be empty'));
}
const structuredData = Array.isArray(data) ? data : [data];
this.removeExtraFieldsAndLog(structuredData);
return SQLBuild.insert(this.tableName, structuredData, options);

@@ -152,3 +155,3 @@ }

}) => {
const { where } = values || {};
const { where = {} } = values || {};
this.throwEmptyObjectError(where, this.printPrefixMessage('CreateDelete :: Where condition cannot be empty'));

@@ -172,3 +175,3 @@ return SQLBuild.deleteFrom(this.tableName)

}) {
const { where, orderBy = [], fields } = values || {};
const { where = {}, orderBy = [], fields } = values || {};
this.throwEmptyObjectError(where, this.printPrefixMessage('FindOne :: Where condition cannot be empty'));

@@ -204,3 +207,3 @@ const SQLBuild = this.initSQLBuilder<ColumnKeys, RowDataPacket[]>();

}>) {
const { data, where, options } = values || {};
const { data = {}, where = {}, options } = values || {};
this.throwEmptyObjectError(where, this.printPrefixMessage('UpdateOne :: Where condition cannot be empty'));

@@ -220,3 +223,5 @@ this.throwEmptyObjectError(data, this.printPrefixMessage('UpdateOne :: Data cannot be empty'));

}>) {
const { data, where = {}, options } = values || {};
const { data = {}, where = {}, options } = values || {};
this.throwEmptyObjectError(data, this.printPrefixMessage('UpdateOne :: Data cannot be empty'));
if (where) this.throwEmptyObjectError(where, this.printPrefixMessage('UpdateOne :: Where condition cannot be empty'));
if (this.primaryKey in data) delete data[this.primaryKey as unknown as keyof typeof data]; // For javascript type checking

@@ -244,3 +249,3 @@ const SQLBuild = this.initSQLBuilder<ColumnKeys, ResultSetHeader>();

}) {
const { where, orderBy = [] } = values || {};
const { where = {}, orderBy = [] } = values || {};
this.throwEmptyObjectError(where, this.printPrefixMessage('RemoveOne :: Where condition cannot be empty'));

@@ -256,5 +261,4 @@ const SQLBuild = this.initSQLBuilder<ColumnKeys, ResultSetHeader>();

where: WhereCondition<ColumnKeys>,
orderBy?: OrderByField<ColumnKeys>[]
}) {
const { where = {}, orderBy = [] } = values || {};
const { where = {} } = values || {};
// Prevent accidental deletion of all records

@@ -273,5 +277,4 @@ this.throwEmptyObjectError(where, this.printPrefixMessage('Remove :: Where condition cannot be empty'));

}) {
const { where, value, options, patchField } = values || {};
// const { patchField } = options || {};
if (!patchField) {
const { where = {}, value, options, patchField } = values || {};
if (typeof patchField !== 'string' || patchField.length === 0) {
throw new Error(this.printPrefixMessage('PatchSingleField :: Patch field is required'));

@@ -291,3 +294,3 @@ }

}) {
const { where, value, options } = values || {};
const { where = {}, value, options } = values || {};
this.throwEmptyObjectError(where, this.printPrefixMessage('SoftDeleteOne :: Where condition cannot be empty'));

@@ -306,3 +309,3 @@ const SQLBuild = this.initSQLBuilder<ColumnKeys, ResultSetHeader>();

}) {
const { where, value, options } = values || {};
const { where = {}, value, options } = values || {};
this.throwEmptyObjectError(where, this.printPrefixMessage('SoftDelete :: Where condition cannot be empty'));

@@ -309,0 +312,0 @@ const SQLBuild = this.initSQLBuilder<ColumnKeys, ResultSetHeader>();

dist/dto/sql-builder-class.d.ts.map

Sorry, the diff of this file is not supported yet

dist/dto/sql-builder-class.js.map

Sorry, the diff of this file is not supported yet

dist/dto/types.d.ts.map

Sorry, the diff of this file is not supported yet

dist/src/table-model/index.d.ts.map

Sorry, the diff of this file is not supported yet

dist/src/table-model/index.js.map

Sorry, the diff of this file is not supported yet

SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap
  • Changelog

About

Packages

npm

Go

Maven

PyPI

Rubygems

Stay in touch

Get open source security insights delivered straight into your inbox.

  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc