egg-knex

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 = await 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 = await app.knex.first("*").where("id", 12).from("posts");
// query
const results = await 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 = await 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 = await app.knex("posts").update({ row }).where(id, 1);

// affectedRowsCount equals 1

Delete

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

Transaction

egg-knex support manual/auto commit.

Manual commit

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

Auto commit

const result = await app.knex.transaction(async function transacting(trx) {
  await trx(table).insert(row1);
  await 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,
] = await app.knex.raw("update posts set hits = (hits + ?) where id = ?", [
  1,
  postId,
]);

• pg

const {
  rows: result,
} = await app.knex.raw("update posts set hits = (hits + ?) where id = ?", [
  1,
  postId,
]);

• mssql

const result = await 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().

await 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";
await 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