One, foreword


Since the birth of NodeJS, a large number of Web frameworks have emerged, such as Express KOA2 Egg and so on. The front-end can no longer rely on the back-end to control the logic of the server side. Today we will talk about how the front end works with the mysql database in NodeJS.


Two, operation database


Using mysqlJS directly, for example, to query a field, the code logic seems clear, but the amount of code required to query just one field is too much hassle:

var mysql      = require('mysql');
var connection = mysql.createConnection(mysqlConfig);

connection.connect();

connection.query('SELECT 1 + 1 AS solution'.function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

connection.end();Copy the code


Some frameworks provide their own interfaces to simplify CRUD operations, such as egg-mysql:

const results = yield app.mysql.select('posts', {where: { status: 'draft' },
  orders: [['created_at'.'desc'], ['id'.'desc']],
  limit: 10,
  offset: 0
});Copy the code


Simple query conditions can be solved, but our real scene of the query conditions in the table association, a variety of fields like, in, findinset splicing conditions, the seed query and other operations can not meet, must write their OWN SQL text.

For example, write SQL to implement a server side paging, implementation is also more troublesome:

// Concatenate various conditionslet whereSql = 'where online_version is not null and state <> 1';
if (scope == 'only') {
  whereSql += ' and use_scope like "%' + query.use_scope + '%' ';
}
whereSql += handleIn(query) + handleEqual(query) + handleLike(query); Const sqlTotal ='select count(*) as total from component' + whereSql; const resultTotal = yield this.app.mysql.query(sqlTotal, values); // Get the current page datalet sqlSelect = 'select * from component'
sqlSelect += whereSql;
sqlSelect += ' order by modified_time desc, id desc limit ';
sqlSelect += (pageIndex - 1) * pageSize + ', '+ pageSize; const resultList = yield this.app.mysql.query(sqlSelect, values); Const result = {list: resultList, total: resultTotal[0]. Total,};return result;Copy the code


There are many great ORM or SQL Builder libraries for files, Sequelize, Knexjs, Squel, etc.


Iii. Tool introduction


Ali-mysql-client is an implementation of SQL Builder ideas, without the need for you to define additional data model more lightweight and concise.

Let’s look at an example query, if it looks succinct:

Const result = await db.select (const result = await db.select (const result = await db.select)"count(1)")
  .from("page")
  .where("name"."Test"."like") .queryValue(); / / query multiple data (server pages) returns the ressult = {total: 100, rows: [{...}, {...}]}. const result = await db .select("*")
  .from("page")
  .where("id", 100, "lt") // id < 100 .queryListWithPaging(3, 20); // There are 20 entries per pageCopy the code


Here are some of its features:


1. SQL Builder ability


Provides powerful SQL Builder capabilities for SELECT INSERT UPDATE delete

// Construct query const query = db.select ("a.a1, b.b1, count(a.c) as count")
  .from("table as a")
  .join("table2 as b")
  .where("a.date", db.literals.now, "lt") // date < now()
  .where("a.creator"."huisheng.lhs")     // creator = 'huisheng.lhs" .groupby("a.a1, b.b1") .having("count(a.category) > 10") .orderby("a.id desc"); Const tasks = [task1, taks2, task3]; const insert = db .insert("task", tasks) .column('create_time', db.literals.now) // loop to each row of data.column ('create_user', 'huisheng.lhs'); Const update = db.update ("task", task).column("create_time", db.literals.now)idConst delet = db.delete ("task"). Where ("id", 1)Copy the code


2. Rich Command


Provides a rich database command for easier access to the database

/ / querycommandconst select = builderSelect(); // query a column value const result1 = await select.queryValue(); // Query single row data {id:12, name:'Test page'. } const result2 = await select.queryRow(); // Query data list [{...}, {...}]; const result3 = await select.queryList(); {total: 100, rows:[{...}, {...}]}; const result4 = await select.queryListWithPaging(); // Delete const result5 = await insert.execute(); const result6 = await update.execute(); const result7 = await delete.execute(); SQL const result8 = await db.sql(SQL, values);Copy the code


3. Conditional encapsulation expansion

const result = await db
  .select("*")
  .from("page")
  .where("id", 100) // id = 100
  .where("name".'test'."like") // name like '%test%'
  .queryList();Copy the code


The third operator argument here is the conditional logic that we encapsulate. It can be passed as a string or function, but the default is equal,

The following operators are built into the class library:

  • eq (equal)
  • ne (not equal)
  • in (in)
  • gt (greater than)
  • ge (greater than or equal)
  • lt (less than)
  • Le (less than or equal)
  • isnull (is null)
  • isnotnull (is not null)
  • like (like)
  • startwith (start with)
  • endwith (end with)
  • between (between)
  • findinset (find_in_set(value, field))
  • insetfind (find_in_set(field, value))
  • sql (custom sql)
  • keywords (keywords query)

Support yourself to expand:

const config = db.config(); // Define operator config.registerOperator('ne', ({ field, value }) => {
  return { sql: '?? < >? ', arg: [ field, value ] };
});Copy the code


Dynamic conditions


This is our according to one of the parameters for the design of our own experience, in the community have yet to see any similar at present, it is mainly used to simplify the code, which is when meet the conditions of xx, ignore the query conditions, is designed to simplify the code, such as the following code is very common, the query interface with the input value, no input value is not used as the query conditions

For example, it is common to use an input value as a query condition

const query = db
  .select("*")
  .from("page");
  .where("id", 100, "lt");

if (name) {
    query.where("name", name, 'like');
}

if (isNumber(source_id)) {
    query.where('source_id', source_id)
}

const result = await query.queryList();Copy the code


The code above can be simplified to:

const result = await db
  .select("*")
  .from("page")
  .where("id", 100, "lt")
  .where("name", name, "like"."ifHave") // Use built-inifHave, if name is not null, add condition."source_id", tech, "eq"."ifNumber") // Use built-inifNumber
  .queryList();Copy the code


Supports passing a string or a function. Passing a string matches the defined logic. The function has the following form:

const customIgnore = ({field, value}) => {
    if(...). {return false;
    }
    
    return true; }; // Register globally using const config = db.config(); config.registerIgnore('ifNumber', ({ value }) => {
  return! isNaN(Number(value)); });Copy the code


5. Event support


Of course we need to look at the problem in development to see why the data is not correct, so we support some events in which you can log your SQL or do some other events, right

const config = db.config(); // Listen on events before execution config.onbeforeExecute (function({ sql }) { console.log(sql); }); Config. onAfterExecute()function({ sql, result }) { console.log(result); }); // Listen for event execution error config.onExecuteError(function({ sql, error }) {
  console.log(error);
});Copy the code


4. Use examples


A complete use example in the KOA framework:

├ ─ ─ the app

│ ├ ─ ─ the controller

│ │ └ ─ ─ home. Js

│ ├ ─ ─ the router. Js

│ └ ─ ─ the service

│ ├ ─ ─ bar. Js

│ └ ─ ─ foo. Js

├ ─ ─ app. Js

├ ─ ─ config. Js

└ ─ ─ package. Json


Config file config.js

'use strict'; Module. exports = {port: 7001, mysqlClient: {mysqlClient: {// host host:'127.0.0.1'// Port number:'3306'// user name user:'root'// password:'mypassword'// The database name is database:'information_schema'}, config: config => {// database tool config // custom operator config.registerOperator('ne', ({ field, value }) => {
        return { sql: '?? < >? ', arg: [ field, value ] };
      });
      
      // 自定义ignore
      config.registerIgnore('ifNumber', ({ value }) => {
        return! isNaN(Number(value)); }); // Listen on events before execution config.onbeforeExecute (function({ sql }) { console.log(sql); }); Config. onAfterExecute()function({ sql, result }) { console.log(result); }); // Listen for event execution error config.onExecuteError(function({ sql, error }) { console.log(error); }); ,}}};Copy the code


Entry file app.js

'use strict';
const Koa = require('koa'); const app = module.exports = new Koa(); // load controller const HomeController = require('./app/controller/home')(app); app.controller = { home: new HomeController(), }; // Load service const FooService = require('./app/service/foo')(app);
const BarService = require('./app/service/bar')(app); app.service = { foo: new FooService(), bar: new BarService(), }; Router = require('./app/router')(app); app.use(app.router.routes()); // obtain configuration information const config = app.config = require('./config'); const { mysqlClient, port } = config; // initialize the database const DbClient = require('ali-mysql-client'); app.db = new DbClient(mysqlClient); // Start the serviceif(! module.parent) { app.listen(port); console.log('$' open http://127.0.0.1: + port);
}Copy the code


Router.js is configured for routing

'use strict';
const Router = require('koa-router');
module.exports = app => {
  const router = new Router();
  router.get('/', app.controller.home.index);
  router.get('/foo', app.controller.home.foo);
  
  return router;
};Copy the code


Controller is the controller/home. Js

'use strict';
module.exports = app => {
  class HomeController {
    async index(ctx, next) {
      const result = await app.service.foo.getDetail();
      ctx.body = 'Table information' + JSON.stringify(result);
    }
    
    async foo(ctx, next) {
      const result = await app.service.foo.getCount();
      ctx.body = 'Table number:'+ result; }}return HomeController;
};Copy the code


Service service/foo. Js

'use strict';
module.exports = app => {
  class FooService {
    async getDetail() {
      const result = await app.db
        .select(The '*')
        .from('tables')
        .where('table_name'.'tables')
        .queryRow();
      
      return result;
    }
    
    async getCount() {
      const result = await app.db
        .select('count(*)')
        .from('tables')
        .queryValue();
      
      returnresult; }}return FooService;
};Copy the code


More examples

  • demo-egg
  • demo-koa
  • demo-express


Open source address


Ali-mysql-client has been open source on Github. The purpose is to provide a powerful and smooth API for NodeJS to access mysql database. The purpose is to use one line of code to complete the database logic, which makes the database access more simple and elegant. If you have any questions, please feel free to comment on github.