@bpframework/middleware-db

database middleware in bpframework.

Middleware specification

https://github.com/bpcloud/middleware

Setup.

import { Application } from 'bpframework';
import * as middleware_db from '@bpframework/middleware-db';

Application.use(middleware_db.middleware)
Application.runKoa(...);

Config.

spring.db:
  # db type: mysql, mssql
  type: mysql
  # (optional) timeout of connect to database.
  connectTimeout: 5000
  # (optional) timeout of query.
  queryTimeout: 5000
  # (optional) timeout of acquire connection from pool.
  acquireTimeout: 5000
  # (optional) max queue length of wait for acquire connection.
  queueLimit: 200
  # (optional) max count of connection in pool.
  connectionLimit: 10
  # (optional) idle timeout to recreate connect
  idleTimeoutMillis: 600000
  # database host.
  host: 127.0.0.1
  # (optional) databse port.
  port: 3306
  # database name.
  database: dbname 
  # user name of databse.
  user: username
  # the password of user.
  password: password
  # (optional) table prefix name.
  table_prefix: prefix_

Define table.

/**
 * table model
 */
@Table('tablename')
class Table1Model {
  @Column({ type: dataType.Char(32), primaryKey: true })
  id: string;

  @Column({ type: dataType.VarChar(64) })
  col1: string;

  @Column({ type: dataType.Bit() })
  col2: boolean;
}

/**
 * table mapper
 */
class Table1Mapper extends BaseMapper<Table1Model> implements IBaseMapper {
  /** Must have this static member */
  static Model = Table1Model;
}

Define Bean.

@Service()
class DBConfigure {
  @Bean()
  dbTemplate(): DBTemplate {
    return new DBTemplate();
  }
}

Use.

@Service()
class DBService {

  @Autowired("dbTemplate")
  dbTemplate: DBTemplate;

  async foo(): Promise<void> {
    
    // get mapper.
    let mapper: Table1Mapper = this.dbTemplate.getMapper(Table1Mapper);

    // query.
    let count = await mapper.count('1=1');
    console.log(`count: ${count}`);

    let exist = await mapper.exist('617a46bf66555e3ea251000001a5da4f');
    console.log(`exist: ${exist}`);
    
    let select = await mapper.select(
      mapper.condition.equal('id', '617a46bf66555e3ea251000001a5da4f')
    );
    console.log(`select: ${JSON.stringify(select[0])}`);
    
    let selectById = await mapper.selectById(
      '617a46bf66555e3ea251000001a5da4f'
    );
    console.log(`selectById: ${JSON.stringify(selectById)}`);

    // exec.
    await this.dbTemplate.exec('select * from tablename');
  }
}

store procedure

Invoke the following stored procedure.

# mysql procedure
CREATE PROCEDURE procedureName(out out1 int, out out2 int, in userid int)
BEGIN
  select count(id) INTO out1 from table1;
  select count(id) INTO out2 from table1 where id=userid;
  select out1, out2;
END

code:

import { procedureParams, dataType } from '@bpframework/middleware-db';

// make param.
var params = new procedureParams();
params.addOut('out1', dataType.Int());
params.addOut('out2', dataType.Int());
params.addIn('userid', dataType.Int(), 2);

// exec.
db.execProcedure('procedureName', params)
.then(ret=>{
  console.log('');
  console.log('[promise procedure]');
  console.log(ret.out.out1);
  console.log(ret.out.out2);
})
.catch(e=>{
  console.log(e);
})