Skip to content
knex plugin for egg
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
config fix: response of will be whatever the underlying sql library Dec 13, 2017
lib fix: exit logger Aug 26, 2018
test
.autod.conf.js feat: enhancement config (#1) Aug 4, 2017
.eslintignore
.eslintrc feat: enhancement config (#1) Aug 4, 2017
.gitignore first commit Sep 5, 2016
.travis.yml fix: ci is working now. Mar 6, 2018
History.md
LICENSE
README.md
README.zh-CN.md
agent.js release 2.0.11 May 15, 2018
app.js feat: enhancement config (#1) Aug 4, 2017
contributors.md
index.d.ts
package.json 2.2.0 Jan 27, 2019
release.sh support typescript Jan 12, 2019

README.md

egg-knex

NPM version Known Vulnerabilities npm download

Knex for egg framework.

Knex is a "batteries included" SQL query builder for Postgres, MSSQL, MySQL, MariaDB, SQLite3, and Oracle. Knex compare to ali-rds:

  1. support multiple type database system
  2. API is all Promise, easy to using async/await
  3. Community-Driven
  4. Support stream

Installation

$ npm i --save egg-knex

Configuration

Install External Dependencies

  • using mysql default support, there is no need to install any external things
  • using mysql2 install dependency npm i --save mysql2
  • using mariadb install dependency npm i --save mariasql
  • using postgres install dependency npm i --save pg
  • using mssql install dependency npm i --save mssql
  • using oracledb install dependency npm i --save oracledb
  • using sqlite install dependency npm i --save sqlite3

Enable Plugin

Edit ${app_root}/config/plugin.js:

exports.knex = {
  enable: true,
  package: 'egg-knex',
};

Add Configurations

Edit ${app_root}/config/config.${env}.js:

exports.knex = {
  // database configuration
  client: {
    // database dialect
    dialect: 'mysql',
    connection: {
      // host
      host: 'mysql.com',
      // port
      port: '3306',
      // username
      user: 'mobile_pub',
      // password
      password: 'password',
      // database
      database: 'mobile_pub',
    },
    // connection pool
    pool: { min: 0, max: 5 },
    // acquire connection timeout, millisecond
    acquireConnectionTimeout: 30000,
  },
  // load into app, default is open
  app: true,
  // load into agent, default is close
  agent: false,
};

Usage

You can access to database instance by using:

app.knex

CURD

Create

// insert
const result = yield app.knex.insert({title: 'Hello World'}).into('posts')
const insertSuccess = result === 1

if you want mysql, sqlite, oracle return ids after insert multiple rows, you can choose batchInsert, it will insert raws one by one in a transaction.

Read

// get one
const post = yield app.knex.first('*').where('id', 12).from('posts')
// query
const results = yield app.knex('posts')
  .select()
  .where({ status: 'draft' })
  .orderBy('created_at', 'desc')
  .orderBy('id', 'desc')
  .orderByRaw('description DESC NULLS LAST')
  .offset(0)
  .limit(10)

// join
const results = yield app.knex('posts')
  .innerJoin('groups', 'groups.id', 'posts.group_id')
  .select('posts.*', 'groups.name');

Update

const row = {
  name: 'fengmk2',
  otherField: 'other field value',
  modifiedAt: app.knex.raw('CURRENT_TIMESTAMP'),
};
// Returns int in "mysql", "sqlite", "oracle"; [] in "postgresql" unless the 'returning' parameter is set.
// following is mysql example
const affectedRowsCount = yield app.knex('posts')
  .update({row})
  .where(id, 1);
  
// affectedRowsCount equals 1

Delete

const affectedRows = yield app.knex('table').where({ name: 'fengmk2' }).del();

Transaction

egg-knex support manual/auto commit.

Manual commit

const trx = yield app.knex.transaction();
try {
  yield trx.insert(row1).into('table');
  yield trx('table').update(row2);
  yield trx.commit()
} catch (e) {
  yield trx.rollback();
  throw e;
}

Auto commit

const result = yield app.knex.transaction(function* transacting (trx) {
  yield trx(table).insert(row1);
  yield trx(table).update(row2).where(condition);
  return { success: true };
});

Advanced Usage

Multiple database instance: mysql + postgres + oracledb

Install dependencies:

$ npm i --save pg orcaledb

Add configurations:

exports.knex = {
  clients: {
    // clientId, access the client instance by app.knex.get('mysql')
    mysql: {
      dialect: 'mysql',
      connection: {
        // host
        host: 'mysql.com',
        // port
        port: '3306',
        // username
        user: 'mobile_pub',
        // password
        password: 'password',
        // database
        database: 'mobile_pub',
      },
      postgres: {
        dialect: 'postgres',
        connection: {
          ...
        }
      },
      oracle: {
        dialect: 'oracledb',
        connection: {
          ...
        }
      }
    },
    // ...
  },
  // default configuration for all databases
  default: {
  },
  // load into app, default is open
  app: true,
  // load into agent, default is close
  agent: false,
};

Usage:

const mysql = app.knex.get('mysql');
mysql.raw(sql);

const pg = app.knex.get('postgres');
pg.raw(sql);

const oracle = app.knex.get('oracle');
oracle.raw(sql);

Custom SQL splicing

  • mysql
const [results] = yield app.knex.raw('update posts set hits = (hits + ?) where id = ?', [1, postId]);
  • pg
const { rows: result } = yield app.knex.raw('update posts set hits = (hits + ?) where id = ?', [1, postId]);
  • mssql
const result = yield app.knex.raw('update posts set hits = (hits + ?) where id = ?', [1, postId]);

Raw

If you want to call literals or functions in mysql , you can use raw.

Inner Literal

  • CURRENT_TIMESTAMP(): The database system current timestamp, you can obtain by app.knex.fn.now().
yield app.knex.insert(, {
  create_time: app.knex.fn.now()
}).into(table);

// INSERT INTO `$table`(`create_time`) VALUES(NOW())

Custom literal

The following demo showed how to call CONCAT(s1, ...sn) funtion in mysql to do string splicing.

const first = 'James';
const last = 'Bond';
yield app.knex.insert({
  id: 123,
  fullname: app.knex.raw(`CONCAT("${first}", "${last}"`),
}).into(table);

// INSERT INTO `$table`(`id`, `fullname`) VALUES(123, CONCAT("James", "Bond"))

License

MIT

You can’t perform that action at this time.