Knex - 使用JS管理数据库

什么是Knex

A SQL query builder that is flexible, portable, and fun to use!

Knex 是一个开源的轻量级的数据库连接工具.  可以用来数据查询/数据维护/数据迁移等工作. 

官网地址: SQL Query Builder for Javascript | Knex.js

Github: GitHub - knex/knex: A query builder for PostgreSQL, MySQL, CockroachDB, SQL Server, SQLite3 and Oracle, designed to be flexible, portable, and fun to use.

支持多种数据库: 

• PostgreSQL,
• MySQL,
• CockroachDB,
• MSSQL,
• SQLite3,
• Oracle (including Oracle Wallet Authentication)

安装Kenx

npm install knex --save

针对不同数据库还需要安装对应的model：

# Then add one of the following (adding a --save) flag:
$ npm install pg
$ npm install pg-native
$ npm install sqlite3
$ npm install better-sqlite3
$ npm install mysql
$ npm install mysql2
$ npm install oracledb
$ npm install tedious

使用Kenx

Mysql: 

const knex = require('knex')({
  client: 'mysql',
  connection: {
    host: '127.0.0.1',
    port: 3306,
    user: 'your_database_user',
    password: 'your_database_password',
    database: 'myapp_test',
  },
});

SQLite3:

const knex = require('knex')({
  client: 'sqlite3', // or 'better-sqlite3'
  connection: {
    filename: './mydb.sqlite',
  },
});

构建SQL查询

Knex Query Builder | Knex.js

Query Builder 的核心是Promise. SQL在 promise的then被调用 或者 wait 时才会执行.  我们可以利用这种特性来构建一个基础查询, 在根据具体业务来拓展. 

例如: 基于条件查询查询数据列表和查询数据count. 

function buildDeviceSelectSQL(req) {

	return DB.from(TABLE_NAME).where(function () {
		this.where("site_id", "=", req.query.site_id);
		if(req.query.sn) {
			this.where("sn", "like", `%${req.query.sn}%`)
		}
	});
}

router.get('/', async function (req, res, next) {
	try {
		let deviceSelectSQLBuilder = buildDeviceSelectSQL(req).select('*').orderBy('id', 'desc');

		if (req.query.page) {
			if (req.query.limit) {
				deviceSelectSQLBuilder.offset(req.query.page).limit(req.query.limit);
			} else {
				deviceSelectSQLBuilder.offset(req.query.page).limit(20);
			}
		}

		let rs = await deviceSelectSQLBuilder;

		let rc = await buildDeviceSelectSQL(req).count('* as recordCount');

		res.json({
			code: 0,
			data: {
				total: rc.recordCount,
				data: rs
			}
		})
	} catch (e) {
		res.json({
			code: 1,
			data: null
		})
	}
});

事务

Transactions | Knex.js

Transactions are handled by passing a handler function into knex.transaction. The handler function accepts a single argument, an object which may be used in two ways:

1. As the "promise aware" knex connection
2. As an object passed into a query with transacting and eventually call commit or rollback.

接口

Interfaces | Knex.js

Promises are the preferred way of dealing with queries in knex, as they allow you to return values from a fulfillment handler, which in turn become the value of the promise. The main benefit of promises are the ability to catch thrown errors without crashing the node app, making your code behave like a .try / .catch / .finally in synchronous code.

使用Knex做Migrations

Migrations | Knex.js

在做migration是需要将knex安装成全局命令. 

npm install knex -g

初始化 Knex

$ knex init

# or for .ts

$ knex init -x ts

创建一个更新数据库文件:

$ knex migrate:make migration_name

# or for .ts

$ knex migrate:make migration_name -x ts
例如: knex --knexfile ./knexfile.js --migrations-directory ./migrations migrate:make

生成的文件名为: 

20240524055343_{migration_name}.js

文件内容如下： 

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.up = function(knex) {
    return knex.schema.table("{table name}", function(table) {
        
    });
};

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.down = function(knex) {
    return knex.schema.table("{table name}", function(table) {
        table.dropColumn("i4");
        table.dropColumn("i5");
        table.dropColumn("i7");
        table.dropColumn("i8");
        table.dropColumn("i9");
    });
};

1. up 方法 对应下面这个命令 用来更新数据库.  

   knex --knexfile ./knexfile.js --migrations-directory ./migrations migrate:up

2. down 方法对应下面命令, 用来将up方法的实现撤销. 

   knex --knexfile ./knexfile.js --migrations-directory ./migrations migrate:down

"migrateMake": "knex --knexfile ./knexfile.js --migrations-directory ./migrations migrate:make",

"migrateUp": "knex --knexfile ./knexfile.js --migrations-directory ./migrations migrate:up",


"migrateDown": "knex --knexfile ./knexfile.js --migrations-directory ./migrations migrate:down"
  

以上是我常用用的三个命令.

参考文献

Installation | Knex.js

GitHub - knex/knex: A query builder for PostgreSQL, MySQL, CockroachDB, SQL Server, SQLite3 and Oracle, designed to be flexible, portable, and fun to use.