Knex SQL 生成器

KnexJS 是一个 SQL 查询构建器，支持多种 SQL 方言。

¥KnexJS is a SQL query builder with support for a number of SQL dialects.

SheetJS 是一个用于从电子表格读取和写入数据的 JavaScript 库。

¥SheetJS is a JavaScript library for reading and writing data from spreadsheets.

该演示使用 KnexJS 和 SheetJS 在电子表格和 SQL 服务器之间交换数据。我们将探讨如何使用将数据库中的表保存到电子表格以及如何将电子表格中的数据添加到数据库中。

¥This demo uses KnexJS and SheetJS to exchange data between spreadsheets and SQL servers. We'll explore how to use save tables from a database to spreadsheets and how to add data from spreadsheets into a database.

测试部署

本 demo 在以下环境下进行了测试：

¥This demo was tested in the following environments:

版本	数据库	连接器模块	日期
0.21.20	SQLite	sqlite3	2024-04-09
2.4.2	SQLite	better-sqlite3	2024-04-09
2.5.1	SQLite	better-sqlite3	2024-04-09
3.1.0	SQLite	better-sqlite3	2024-04-09

集成详情

¥Integration Details

SheetJS NodeJS 模块 可以加载到使用 KnexJS 的 NodeJS 脚本中。

¥The SheetJS NodeJS module can be loaded in NodeJS scripts that use KnexJS.

导出数据

¥Exporting Data

KnexJS select 方法 [^1] 创建一个 SELECT 查询。返回值是一个解析为对象数组的 Promise。

¥The KnexJS select method[^1] creates a SELECT query. The return value is a Promise that resolves to an array of objects.

SheetJS json_to_sheet 方法 [^2] 可以从对象数组生成工作表对象 [^3]：

¥The SheetJS json_to_sheet method[^2] can generate a worksheet object[^3] from the array of objects:

const table_name = "Tabeller1"; // name of table

/* fetch all data from specified table */
const aoo = await knex.select("*").from(table_name);

/* generate a SheetJS worksheet object from the data */
const worksheet = XLSX.utils.json_to_sheet(aoo);

可以使用实用函数 [^4] 从工作表构建工作簿对象。可以使用 SheetJS writeFile 方法 [^5] 导出工作簿：

¥A workbook object can be built from the worksheet using utility functions[^4]. The workbook can be exported using the SheetJS writeFile method[^5]:

/* create a new workbook and add the worksheet */
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, worksheet, "Sheet1");

/* export workbook to XLSX */
XLSX.writeFile(wb, "SheetJSKnexJSExport.xlsx");

导入数据

¥Importing Data

SheetJS sheet_to_json 函数 [^6] 接受一个工作表对象并生成一个对象数组。

¥The SheetJS sheet_to_json function[^6] takes a worksheet object and generates an array of objects.

KnexJS insert 方法 [^7] 创建 INSERT 查询。返回值是一个 Promise，在执行查询时解析：

¥The KnexJS insert method[^7] creates INSERT queries. The return value is a Promise that resolves when the query is executed:

const table_name = "Blatte1"; // name of table

/* generate an array of arrays from the worksheet */
const aoo = XLSX.utils.sheet_to_json(ws);

/* insert every row into the specified database table */
await knex.insert(aoo).into(table_name);

创建表

¥Creating a Table

KnexJS Schema Builder 支持使用 createTable[^8] 创建表并删除使用 dropTableIfExists[^9] 的表。

¥The KnexJS Schema Builder supports creating tables with createTable[^8] and dropping tables with dropTableIfExists[^9].

可以扫描对象数组以确定列名称和类型。

¥The array of objects can be scanned to determine column names and types.

Implementation Details (click to show)

The aoo_to_knex_table function:

• scans each row object to determine column names and types
• drops and creates a new table with the determined column names and types
• loads the entire dataset into the new table

/* create table and load data given an array of objects and a Knex connection */
async function aoo_to_knex_table(knex, aoo, table_name) {
  /* define types that can be converted (e.g. boolean can be stored in float) */
  const T_FLOAT = ["float", "boolean"];
  const T_BOOL = ["boolean"];

  /* types is a map from column headers to Knex schema column type */
  const types = {};

  /* names is an ordered list of the column header names */
  const names = [];

  /* loop across each row object */
  aoo.forEach(row =>
    /* Object.entries returns a row of [key, value] pairs */
    Object.entries(row).forEach(([k,v]) => {

      /* If this is first occurrence, mark unknown and append header to names */
      if(!types[k]) { types[k] = ""; names.push(k); }

      /* skip null and undefined values */
      if(v == null) return;

      /* check and resolve type */
      switch(typeof v) {
        /* change type if it is empty or can be stored in a float */
        case "number": if(!types[k] || T_FLOAT.includes(types[k])) types[k] = "float"; break;
        /* change type if it is empty or can be stored in a boolean */
        case "boolean": if(!types[k] || T_BOOL.includes(types[k])) types[k] = "boolean"; break;
        /* no other type can hold strings */
        case "string": types[k] = "text"; break;
        default: types[k] = "text"; break;
      }
    })
  );

  /* Delete table if it exists in the DB */
  await knex.schema.dropTableIfExists(table_name);

  /* use column type info to create table */
  await knex.schema.createTable(table_name, (table) => {
    names.forEach(h => {
      /* call schema function e.g. table.text("Name"); table.float("Index"); */
      table[types[h] || "text"](h);
    });
  });

  /* insert each row */
  await knex.insert(aoo).into(table_name);
  return knex;
}

连接到 SQLite 时，Knex 构造函数可能会显示警告：

¥The Knex constructor may display a warning when connecting to SQLite:

sqlite does not support inserting default values. Set the `useNullAsDefault` flag to hide this warning. (see docs https://knex.nodejs.cn/guide/query-builder.html#insert).

该标志应该添加到选项参数中：

¥That flag should be added to the options argument:

const Knex = require('knex');
let knex = Knex({
  client: 'better-sqlite3',
  connection: { filename: "SheetJSKnex.db" },
  useNullAsDefault: true
});

完整示例

¥Complete Example

1. 安装依赖：

   ¥Install dependencies:

npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz knex better-sqlite3

对于 KnexJS 版本 0.21.20，必须安装 sqlite3 模块：

¥For KnexJS version 0.21.20, the sqlite3 module must be installed:

npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz knex sqlite3

2. 下载 测试文件

   ¥Download the test file

curl -LO https://xlsx.nodejs.cn/pres.numbers

3. 下载 SheetJSKnexTest.js：

   ¥Download SheetJSKnexTest.js:

curl -LO https://xlsx.nodejs.cn/knex/SheetJSKnexTest.js

该脚本将：

¥This script will:

• 读取并解析测试文件 pres.numbers

  ¥read and parse the test file pres.numbers

• 创建与存储在 SheetJSKnex.db 的 SQLite 数据库的连接

  ¥create a connection to a SQLite database stored at SheetJSKnex.db

• 将第一个工作表中的数据加载到名为 Test_Table 的表中

  ¥load data from the first worksheet into a table with name Test_Table

• 断开并重新连接到数据库

  ¥disconnect and reconnect to the database

• 从表 Test_Table 转储数据

  ¥dump data from the table Test_Table

• 将数据集导出到 SheetJSKnex.xlsx

  ¥export the dataset to SheetJSKnex.xlsx

4. 运行脚本：

   ¥Run the script:

node SheetJSKnexTest.js

该脚本将生成两个工件：

¥The script will generate two artifacts:

SheetJSKnex.xlsx 可以在电子表格应用中打开或在终端中测试：

¥SheetJSKnex.xlsx can be opened in a spreadsheet app or tested in the terminal:

npx xlsx-cli SheetJSKnex.xlsx

SheetJSKnex.db 可以使用 sqlite3 命令行工具进行验证：

¥SheetJSKnex.db can be verified with the sqlite3 command line tool:

sqlite3 SheetJSKnex.db 'select * from Test_Table'

旧版本的 KnexJS 会抛出错误：

¥Older versions of KnexJS will throw an error:

Error: knex: Unknown configuration option 'client' value better-sqlite3. Note that it is case-sensitive, check documentation for supported values.

旧版本的 KnexJS 不支持 better-sqlite3 模块。必须编辑 SheetJSKnexTest.js 脚本才能使用 sqlite3：

¥Older versions of KnexJS do not support the better-sqlite3 module. The SheetJSKnexTest.js script must be edited to use sqlite3:

SheetJSKnexTest.js (edit highlighted lines)

(async() => {
  /* open connection to SheetJSKnex.db */
  let knex = Knex({ client: 'sqlite3', connection: { filename: "SheetJSKnex.db" }, useNullAsDefault: true });

  try {
    /* generate array of objects from worksheet */
    const aoo = XLSX.utils.sheet_to_json(oldws);

    /* create table and load data */
    await aoo_to_knex_table(knex, aoo, "Test_Table");
  } finally {
    /* disconnect */
    knex.destroy();
  }

  /* reconnect to SheetJSKnex.db */
  knex = Knex({ client: 'sqlite3', connection: { filename: "SheetJSKnex.db" }, useNullAsDefault: true });

[^1]: 请参阅 KnexJS 查询构建器文档中的 select。

¥See select in the KnexJS query builder documentation.

[^2]: 见 json_to_sheet 于 "实用工具"

¥See json_to_sheet in "Utilities"

[^3]: 详细信息请参见 "SheetJS 数据模型" 中的 "Sheet 对象"。

¥See "Sheet Objects" in "SheetJS Data Model" for more details.

[^4]: 有关 book_new 和 book_append_sheet 的详细信息，请参阅 "工作簿助手" 于 "实用工具"。

¥See "Workbook Helpers" in "Utilities" for details on book_new and book_append_sheet.

[^5]: 见 writeFile 于 "写入文件"

¥See writeFile in "Writing Files"

[^6]: 见 sheet_to_json 于 "实用工具"

¥See sheet_to_json in "Utilities"

[^7]: 请参阅 KnexJS 查询构建器文档中的 insert。

¥See insert in the KnexJS query builder documentation.

[^8]: 请参阅 KnexJS Schema Builder 文档中的 createTable。

¥See createTable in the KnexJS Schema Builder documentation.

[^9]: 请参阅 KnexJS Schema Builder 文档中的 dropTableIfExists。

¥See dropTableIfExists in the KnexJS Schema Builder documentation.