node sql 链式调用 源码

该插件只满足大部分功能（新增 分页插叙 更新 联表查询 查询总数 查询不返回某个字段等）并且可能存在bug 建议进行测试 （如果发现 也请告诉我一声谢谢 ）

// mysql.ts // 文件

// 引入它的类型判断
///<reference path="./mysql.d.ts" />

import mysql2, { Pool, ConnectionOptions, PoolConnection, QueryError } from 'mysql2';

export interface ConnectionOptionsType {
  host: string;
  user: string;
  password: string;
  database: string;
}

export interface MySQLQueryBuilderType {
  table(data: string): MySQLQueryBuilderType;
  alias(data: string): MySQLQueryBuilderType;
  field(data: Array<string> | string, excludeField?: boolean): MySQLQueryBuilderType;
  join(data: Array<JoinClause> | JoinClause): MySQLQueryBuilderType;
  where(data: Array<WhereClause> | WhereClause): MySQLQueryBuilderType;
  order(field: string, order?: string): MySQLQueryBuilderType;
  limit(limit: number, offset?: number): MySQLQueryBuilderType;
  select(): Promise<any>;
  count(): Promise<any>;
  add(data: addData): Promise<any>;
  update(data: updateData): Promise<any>;
  delete(): Promise<any>;
  executeSQL(params: QueryParams): void;
}

/**
 * 错误函数
 * @param str 错误信息
 * @returns 错误
 */
const consoleError = (str: string) => {
  console.error('\x1b[31m%s\x1b[0m', `ERROR: ${str}`);
  throw new Error(str);
}

/**
 * 成功函数
 * @param str 成功信息
 * @returns 成功
 */
const consoleSuccess = (str: string) => {
  console.log('\x1b[32m%s\x1b[0m', `SUCCESS SQL statement: ${str}`);
}

/**
 * @description 判断数组每一项是否是符合的类型
 * @param array
 * @param type 
*/
const isEveryItemType = (array: Array<any>, type: string): Boolean => {
  return array.every(item => typeof item === type && item !== null);
}

// ! 配置方法
/**
 * 设置查询字段
 * @param queryOptions 查询参数
 * @returns 查询字段
 */
const setFields = (queryOptions: QueryOptions): string => {
  if (!queryOptions.fields || queryOptions.fields.length === 0) return '*';
  if (!Array.isArray(queryOptions.fields) && typeof queryOptions.fields !== 'string') consoleError('请传入数组或者字符串');
  return Array.isArray(queryOptions.fields) ? queryOptions.fields.join(',') : queryOptions.fields;
};

/**
 * 设置别名
 * @param queryOptions 查询参数
 * @returns 别名
*/
const setAlias = (queryOptions: QueryOptions): string => {
  return queryOptions.tableAlias ? ` AS ${queryOptions.tableAlias}` : '';
};

/**
 * 设置连接
 * @param queryOptions 查询参数
 * @returns 连接
*/
const setJoin = (queryOptions: QueryOptions) => {
  if (queryOptions.joinClauses.length === 0) return '';
  if (!isEveryItemType(queryOptions.joinClauses, 'object')) return consoleError('joinClauses 必须是对象数组')
  let join = ''
  queryOptions.joinClauses.forEach(item => {
    if (Object.prototype.toString.call(item.on) !== '[object Object]') return consoleError('join 对象的 on 属性必须是一个对象')
    // 判断是否使用了别名 
    if (!item.as) item.as = item.table;
    if (!queryOptions.tableAlias) queryOptions.tableAlias = queryOptions.table;
    join += ` ${item.join.toUpperCase()} JOIN ${item.table} AS ${item.as} ON `;
    const onConditions = Object.entries(item.on).map(([key, value], index, array) => {
      const left = key.includes('.') ? key : `${queryOptions.tableAlias}.${key}`;
      const right = value.includes('.') ? value : `${item.as}.${value}`;
      return `${left} = ${right}`;
    });
    join += onConditions.join(' AND ');
  })
  return join;
}

/**
 * 设置查询条件
 * @param queryOptions 查询参数
 * @returns 查询条件
*/
const setWhere = (queryOptions: QueryOptions) => {
  if (!queryOptions.whereClause) {
    return {
      where: '',
      values: null
    } as ReturnWhereClause;
  }
  if (typeof queryOptions.whereClause !== 'object') return consoleError('请传入对象');
  let values = [] as Array<string | number>;
  const { where } = Object.entries(queryOptions.whereClause).reduce((acc, [key, value], index, array) => {
    if (typeof value !== 'string' && typeof value !== 'number' && !Array.isArray(value)) {
      return consoleError('请传入一个对象其中值为数组或者是字符串和数值');
    }
    if (Array.isArray(value)) {
      if (value.length !== 2) return consoleError('数组长度必须为2 [ param, !=, status ]');
      acc.where += `${key} ${value[0]} ?`;
      values.push(value[1]);
    } else {
      acc.where += `${key} = ?`;
      values.push(value);
    }
    if (index < array.length - 1) {
      acc.where += ' AND ';
    }
    return acc;
  }, { where: ' WHERE ' });
  return {
    where,
    values
  } as ReturnWhereClause;
};

/**
 * 设置查询分页
 * @param queryOptions 查询参数
 * @returns 查询条件 分页
 */
const setPage = (queryOptions: QueryOptions): string => {
  const pageNum = Number(queryOptions.pageNum);
  const pageSize = Number(queryOptions.pageSize);
  if (isNaN(pageNum)) return '';
  return isNaN(pageSize) ? ` LIMIT ${pageNum}` : ` LIMIT ${pageNum}, ${pageSize}`;
};

/**
 * 设置排序规则 (有多列排序) DESC 正序 ASC 倒序
 * @param queryOptions 查询参数
 * @returns 排序规则
 */
const setOrder = (queryOptions: QueryOptions): string => {
  if (!queryOptions.orderBy) return '';
  if (!Array.isArray(queryOptions.orderBy) && typeof queryOptions.orderBy !== 'string') {
    console.error('请传入一个数组或者字符串');
    return '';
  }
  const orderBy = Array.isArray(queryOptions.orderBy)
    ? queryOptions.orderBy.join(', ')
    : queryOptions.orderBy;
  return ` ORDER BY ${orderBy}`;
};

/**
 * mySQL语句构造器
 * @class MySQLQueryBuilder
 * @param connectionProperties 连接参数
 */
class MySQLQueryBuilder {
  private mysql: typeof mysql2;
  private myPool: Pool | null;
  private queryOptions: QueryOptions;
  constructor() {
    this.mysql = mysql2; // MySQL
    this.myPool = null; // 创建一个连接池
    this.queryOptions = {} as QueryOptions;
  }
  // 初始化链接池
  connectionConfig(connectionProperties: ConnectionOptions) {
    this.myPool = this.mysql.createPool(connectionProperties)
    return this;
  }
  // 初始化数据
  private init() {
    this.queryOptions = {
      excludeField: false,
      fields: [] as Array<string> | string,
      joinClauses: [] as Array<JoinClause>,
    } as QueryOptions;
    return this;
  }
  /**
   * 设置表名
   * @param table 表名
  */
  table(table: string) {
    // 初始化数据
    this.init();
    this.queryOptions.table = table;
    return this;
  }
  /**
   * 设置别名
   * @param data 别名
   */
  alias(data: string) {
    this.queryOptions.tableAlias = data;
    return this;
  }
  /**
   * 设置查询字段
   * @param data 查询字段
   * @param excludeField 是否排除字段
  */
  field(data: Array<string> | string, excludeField?: boolean) {
    this.queryOptions.fields = data;
    this.queryOptions.excludeField = excludeField;
    return this;
  }
  /**
   * 设置join
   * @param joinClauses join条件
   * @returns join
  */
  join(joinClauses: Array<JoinClause> | JoinClause) {
    if (Array.isArray(joinClauses)) {
      this.queryOptions.joinClauses = joinClauses;
    } else {
      this.queryOptions.joinClauses.push(joinClauses);
    }
    return this;
  }
  /**
   * 设置查询条件
   * @param condition 查询条件
   * @returns 查询条件
   */
  where(condition: WhereClause[]) {
    this.queryOptions.whereClause = condition;
    return this;
  }
  /**
   * 设置排序规则 (有多列排序) DESC 正序 ASC 倒序
   * @param orderBy 排序规则
  */
  order(orderBy: Array<string> | string) {
    this.queryOptions.orderBy = orderBy;
    return this;
  }
  /**
   * 设置分页
   * @param pageNum 页码
   * @param pageSize 每页数量
  */
  limit(pageNum: number, pageSize: number) {
    this.queryOptions.pageNum = pageNum;
    this.queryOptions.pageSize = pageSize;
    return this;
  }
  /**
   * 查询
   * @returns 查询结果
  */
  select() {
    return new Promise((resolve, reject) => {
      // 查询什么字段 默认值 *
      const fields = setFields(this.queryOptions);
      // 设置别名
      const alias = setAlias(this.queryOptions);
      // 设置join
      const join = setJoin(this.queryOptions);
      // where 查询
      const where = setWhere(this.queryOptions);
      // 排序
      const order = setOrder(this.queryOptions);
      // 分页
      const page = setPage(this.queryOptions);
      // 最终的sql语句
      const sql = `SELECT ${fields} FROM ${this.queryOptions.table}${alias}${join}${where!.where}${order}${page}`
      this.executeSQL({ sql, resolve, values: where.values });
    })
  }
  /**
   * 查询总数
   * @returns 查询结果
  */
  count() {
    return new Promise((resolve, reject) => {
      // 设置别名
      const alias = setAlias(this.queryOptions);
      // 设置join
      const join = setJoin(this.queryOptions)
      // where 查询
      const where = setWhere(this.queryOptions);
      const sql = `SELECT COUNT(*) AS count FROM ${this.queryOptions.table}${alias}${join}${where!.where}`;
      this.executeSQL({ sql, resolve, count: true, values: where!.values });
    })
  }
  /**
   * 新增
   * @param data 新增数据
   * @returns 新增结果
  */
  add(data: addData) {
    return new Promise((resolve, reject) => {
      let sql = `INSERT INTO ${this.queryOptions.table}`;
      let keys: Array<string> = [];
      let nulls: Array<string> = [];
      let values: Array<any> = [];
      if (!Array.isArray(data) && typeof data !== 'object') return consoleError('add函数传入数组或者是对象');
      if (Array.isArray(data)) {
        // 批量添加
        if (data.some(item => typeof item !== 'object')) {
          consoleError('sql add data must be Array contains objects');
          return;
        }
        keys = Object.keys(data[0]);

        data.forEach(item => {
          const valueList = Object.values(item);
          nulls.push(`(${valueList.map(() => '?').join(',')})`);
          values.push(valueList);
        });
        sql += ` (${keys.join(',')}) VALUES ${nulls.join(',')}`;
      } else if (typeof data === 'object') {
        // 单个添加
        keys = Object.keys(data);
        nulls = keys.map(() => '?');
        values = Object.values(data);
        sql += ` (${keys.join(',')}) VALUES (${nulls.join(',')})`;
      }
      this.executeSQL({ sql, resolve, values });
    });
  }
  /**
   * 编辑
   * @param data 编辑数据
   * @returns 编辑结果
  */
  update(data: updateData) {
    return new Promise((resolve, reject) => {
      if (typeof data !== 'object') return consoleError('update函数请传入一个正确的对象');
      let sql = `UPDATE ${this.queryOptions.table} SET`;
      let where = setWhere(this.queryOptions);
      let keys = Object.keys(data);
      let values = Object.values(data);
      const setClause = keys.map(key => ` ${key} = ?`).join(',');
      if (!where.where) return consoleError('where函数必须传入参数 查询条件');
      sql += `${setClause}${where.where}`;
      values = values.concat(where.values);
      this.executeSQL({ sql, resolve, values });
    });
  }
  /**
   * 删除
   * @returns 删除结果
  */
  delete() {
    return new Promise((resolve, reject) => {
      // where 查询
      let where = setWhere(this.queryOptions);
      if (!where) consoleError('where函数必须传入参数 查询条件');
      let sql = `DELETE FROM ${this.queryOptions.table}${where!.where}`
      this.executeSQL({ sql, resolve, values: where!.values });
    })
  }
  // 最后执行sql 语句
  executeSQL(params: QueryParams) {
    this.myPool!.getConnection((err: ErrnoException | null, connection: PoolConnection) => {
      if (err) return consoleError(String(err));
      connection.query(params.sql, params.values, (err: QueryError | null, res: any) => {
        if (err) consoleError(`YOUR ERROR SQL statement: ${params.sql} and ${err}`);
        consoleSuccess(params.sql);
        if (params.count) {
          params.resolve(res[0].count);
        } else {
          if (!this.queryOptions.excludeField) {
            params.resolve(res);
          } else {
            const fieldToRemove = Array.isArray(this.queryOptions.fields) ? this.queryOptions.fields.join(',') : this.queryOptions.fields;
            const data = res.map(({ [fieldToRemove]: removedField, ...rest }) => rest);
            params.resolve(data);
          }
        }
        connection.release();
      });
    });
  }
}

/**
 * 初始化mysql
 * @param connectionOptions 连接配置
 * @returns mysql实例
*/
const initMysql = (connectionOptions: ConnectionOptions) => {
  const mysqlConfig = new MySQLQueryBuilder();
  mysqlConfig.connectionConfig(connectionOptions);
  return mysqlConfig;
}

export default initMysql

mysql.d.ts 这里是mysql使用的大部分的数据类型 私有

interface ErrnoException extends Error {
  errno?: number;
  code?: string;
  path?: string;
  syscall?: string;
  stack?: string;
}

interface JoinClause {
  as: string;
  join: string;
  table: string;
  on: {[key: string]: string};
}

interface WhereClause {
  [key: string]: string | number | boolean | Array;
}

interface ReturnWhereClause {
  where: string,
  values: Array<string | number> | null
}

interface QueryOptions {
  fields: Array<string> | string,
  excludeField?: boolean,
  joinClauses: JoinClause[];
  tableAlias?: string;
  table: string;
  pageNum: number,
  pageSize: number,
  orderBy: Array<string> | string,
  whereClause: WhereClause[]
}

interface QueryParams {
  sql: string,
  count?: boolean,
  resolve: function,
  values: Array<string | number | Array<string | number>> | null,
}

interface addData {
  [key: string]: any
}[]

interface updateData {
  [key: string]: any
}

 使用 

import initMysql, { MySQLQueryBuilderType, ConnectionOptionsType } from './mysql';
/**
 * mysql配置
 * @type {ConnectionOptionsType}
 * @description 连接mysql数据库
*/
const mysqlConfig = {
  connection: {} as MySQLQueryBuilderType,
  setMysql: () => {
    let host = config.mysqlConfig();
    mysqlConfig.connection = initMysql(host);
  },
}

使用 链式调用

/**
 * 获取用户信息
 * @param search 条件
 * @param res 响应
 * @returns promise
 */
const getUserInfo = (search: WhereClause, res: Response) => {
  return new Promise((resolve, reject) => {
    mysqlConfig.connection.table('user').alias('user')
    .join([
      // // 链接 用户书表
      {
        table: 'user_book',
        join: 'left',
        as: 'ub',
        on: {
          'user.status': 'status',
          'user.id': 'user_id',
        }
      },
      // // 链接 用户文章表
      {
        table: 'note',
        join: 'left',
        as: 'note',
        on: {
          'user.status': 'status',
          'user.id': 'user_id'
        }
      },
      // // 用户文章like
      {
        table: 'user_note_like',
        join: 'left',
        as: 'unl',
        on: {
          'user.status': 'status',
          'user.id': 'user_id'
        }
      },
      // // 用户文章收藏
      {
        table: 'user_note_collect',
        join: 'left',
        as: 'unc',
        on: {
          'user.status': 'status',
          'user.id': 'user_id'
        }
      },
      // // 用户点赞评论
      {
        table: 'user_comment_like',
        join: 'left',
        as: 'ucl',
        on: {
          'user.status': 'status',
          'user.id': 'user_id'
        }
      }
    ])
    .field([
      'user.id',
      'user.name',
      'user.auth',
      'user.info',
      'user.image',
      'user.username',
      'ub.id as userBookId',
      'ub.book_id as userBookBookId',
      'note.id as userNoteId',
      'note.title userNoteTitle',
      'note.update_time as userNoteUpdateTime',
      'unl.id as userNoteLikeId',
      'unl.note_id as userLikeNoteId',
      'unc.id as userNoteCollectId',
      'unc.note_id as userCollectNoteId',
      'ucl.comment_id as commentId',
    ])
    .where(search).select()
    .then((data: any[]) => {
      let obj = {
        userBook: [] as Array<userBookType>,
        userNote: [] as Array<userNoteType>,
        userNoteLike: [] as Array<userNoteLikeType>,
        userNoteCollect: [] as Array<userNoteCollectType>,
        userCommentLike: [] as Array<number>,
      } as userInfoType;
      // 拿到的参数进行处理
      data.forEach((ele, index: number) => {
        if (index === 0) {
          // 默认赋值一次
          obj.id = ele.id;
          obj.name = ele.name;
          obj.auth = ele.auth;
          obj.info = ele.info;
          obj.image = `${utils.tools.getIPAndProt()}${ele.image}`;
          obj.username = ele.username;
        }
        // 书
        if (ele.userBookId) {
          // 没有相同的参数才存入
          if (!obj.userBook.filter(item => item.id === ele.userBookId).length) {
            obj.userBook.push({
              id: ele.userBookId,
              bookId: ele.userBookBookId
            })
          }
        }
        // 笔记
        if (ele.userNoteId) {
          if (!obj.userNote.filter(item => item.id === ele.userNoteId).length) {
            obj.userNote.push({
              id: ele.userNoteId,
              title: ele.userNoteTitle,
              updateTime: ele.userNoteUpdateTime
            })
          }
        }
        // 喜欢
        if (ele.userNoteLikeId) {
          if (!obj.userNoteLike.filter(item => item.id === ele.userNoteLikeId).length) {
            obj.userNoteLike.push({
              id: ele.userNoteLikeId,
              noteId: ele.userLikeNoteId
            })
          }
        }
        // 收藏
        if (ele.userNoteCollectId) {
          if (!obj.userNoteCollect.filter(item => item.id === ele.userNoteCollectId).length) {
            obj.userNoteCollect.push({
              id: ele.userNoteCollectId,
              noteId: ele.userCollectNoteId,
            })
          }
        }
        // 评论点赞
        if (ele.commentId) {
          obj.userCommentLike.push(ele.commentId)
        }
      })
      resolve(obj);
    }).catch((err) => {
      res.send(config.returnData(500, '获取用户信息失败！'))
    })
  })
}