Interface.jianghuKnex

12003

jianghuKnex is a database query and operation library based on knex, providing a simple and easy-to-use API that supports standard CRUD operations, data history, transaction handling, and complex join queries. Below are common operations of jianghuKnex:

• Query: select, first, count
• Insert: jhInsert
• Update: jhUpdate
• Delete: jhDelete
• Inner Join: innerJoin
• Left Join: leftJoin
• Right Join: rightJoin
• Pagination: offset & limit
• Transaction Handling: transaction
• ... For more operations, refer to the knex documentation

Enabling jianghuKnex

Configure knex information in /config/config.local.js or /config/config.prod.js, the framework will automatically mount knex and jianghuKnex to this.app.

{  
    knex: {  
      client: {  
        dialect: 'mysql',  
        connection: {  
          host: '127.0.0.1',  
          port: 3306,  
          user: 'root',  
          password: '123456',  
          database: 'test01'  
        },  
        pool: { min: 0, max: 10 },  
        acquireConnectionTimeout: 30000  
      },  
      app: true  
    }  
}  

Using jianghuKnex

jianghuKnex extends the original knex, providing some additional features. Below are some common operations and example code:

Query

• select: Query returns a list

const list = await this.app.jianghuKnex("student").where({ gender: 'male' }).select();  
this.app.logger.info('jianghuKnexExample.js select()', '=====>', `result length: ${list.length}`);  

• first: Query returns the first result

const student = await this.app.jianghuKnex("student").where({ studentId: 'S10001' }).first();  
this.app.logger.info('jianghuKnexExample.js first()', '=====>', { studentId: student?.studentId, name: student?.name });  

• count: Query count

const countResult = await this.app.jianghuKnex("student").where({}).count('* as count').first();  
this.app.logger.info('jianghuKnexExample.js count()', '=====>', countResult);  

Insert

• jhInsert: Insert data.

const student = { studentId: 'T00001', name: 'Test001' };  
await this.app.jianghuKnex("student", this.ctx).jhInsert(student);  
this.app.logger.info('jianghuKnexExample.js jhInsert()', '=====>', student);  

Update

• jhUpdate: Update existing data

const student = { studentId: 'T00001', name: 'Test Student001' };  
await this.app.jianghuKnex("student", this.ctx)  
    .where({ studentId: student.studentId })  
    .jhUpdate({ name: student.name });  
this.app.logger.info('jianghuKnexExample.js jhUpdate()', '=====>', student);  

Delete

• jhDelete: Delete data

const student = { studentId: 'T00001' };  
await this.app.jianghuKnex("student", this.ctx)  
    .where({ studentId: student.studentId })  
    .jhDelete({ name: student.name });  
this.app.logger.info('jianghuKnexExample.js jhDelete()', '=====>', student);  

Join Queries

• innerJoin: Inner join
• leftJoin: Left join
• rightJoin: Right join

const classId = '2021-01 Class-01';  
const list = await this.app.jianghuKnex("class", this.ctx)  
    .leftJoin('student', 'class.classId', 'student.classId')  
    .whereRaw('class.classId = ?', [classId])  
    .select();  
this.app.logger.info('jianghuKnexExample.js leftJoin()', '=====>', `result length: ${list.length}`);  

Pagination Query

• offset: Page number
• limit: Number of records per page

const pageSize = 5;  
const pageNumber = 1;  
const list = await this.app.jianghuKnex("student")  
    .offset((pageNumber - 1) * pageSize)  
    .limit(pageSize)  
    .select();  
this.app.logger.info('jianghuKnexExample.js offset()', '=====>', `result length: ${list.length}`);  

Data History

jianghuKnex('student', this.ctx), after passing this.ctx, jianghuKnex will automatically record the operation records and also record the data history:

• Operation Records
  • Current table.operationByUserId: UserId of the operator.
  • Current table.operationByUser: Username of the operator.
  • Current table.operationAt: Time of operation.
• Data History: Data history is recorded in _record_history, you can use the jianghu-init tool --type=record-history-page tool to generate and view the data history page to see historical changes.

Transactions

jianghuKnex supports transaction handling to ensure the consistency and integrity of data operations. Transactions can ensure that a group of operations either all succeed or all fail and roll back. To use transactions, you can use the transaction method provided by the framework.

const student = { studentId: 'T00002', name: 'Test002' };  
const classObj = { classId: '2021-07 Class-01', className: 'Seventh Grade Class One' };  
await this.app.jianghuKnex.transaction(async trx => {  
    // Delete  
    await trx('student', this.ctx).jhDelete(student);  
    await trx('class', this.ctx).jhDelete(classObj);  
    // Insert  
    await trx('student', this.ctx).jhInsert(student);  
    await trx('class', this.ctx).jhInsert(classObj);  
});  
this.app.logger.info('jianghuKnexExample.js transaction()', '=====>', classObj);  

Test Tables

CREATE TABLE `class` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `classId` varchar(255) DEFAULT NULL COMMENT 'Class ID',  
  `className` varchar(255) DEFAULT NULL COMMENT 'Class Name',  
  `remarks` mediumtext COMMENT 'Remarks',  
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'Operation; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',  
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'Operator UserId',  
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'Operator Username',  
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'Operation Time; E.g: 2021-05-28T10:24:54+08:00 ',  
  PRIMARY KEY (`id`) USING BTREE  
) ENGINE=InnoDB AUTO_INCREMENT=175 DEFAULT CHARSET=utf8mb4;  

CREATE TABLE `student` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `studentId` varchar(255) DEFAULT NULL COMMENT 'Student ID',  
  `name` varchar(255) DEFAULT NULL COMMENT 'Student Name',  
  `gender` varchar(255) DEFAULT NULL COMMENT 'Gender',  
  `dateOfBirth` varchar(255) DEFAULT NULL COMMENT 'Date of Birth',  
  `classId` varchar(255) DEFAULT NULL COMMENT 'Class ID',  
  `level` varchar(255) DEFAULT NULL COMMENT 'Grade',  
  `bodyHeight` varchar(255) DEFAULT NULL COMMENT 'Height',  
  `studentStatus` varchar(255) DEFAULT NULL COMMENT 'Student Status',  
  `remarks` mediumtext COMMENT 'Remarks',  
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'Operation; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',  
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'Operator UserId',  
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'Operator Username',  
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'Operation Time; E.g: 2021-05-28T10:24:54+08:00 ',  
  PRIMARY KEY (`id`) USING BTREE,  
  KEY `studentId` (`studentId`) USING BTREE  
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;  

Data Isolation

When multiple applications share a single database, data isolation between each application is required. For updates, refer to Multi-Application-jhId

• enable: Whether to enable
• jhId: Data isolation marker
• careTableViewList: Tables that adopt data isolation

jianghuConfig:{  
    jhIdConfig: {  
        enable: false,  
        jhId: 'project01',  
        careTableViewList: [ 'class', 'student' ],  
    },  
}