1前言
达梦 Node.js 数据库驱动支持 ORM 框架 typeorm，提供方言包 typeorm-dm 进行适配。方言包安装方法：npm install typeorm-dm。安装方法将自动安装依赖包 dmdb。
达梦 Node.js 数据库驱动支持 SQL 查询构建器 knex，提供方言包 knex-dm 进行适配。方言包安装方法：npm install knex-dm。安装方法将自动安装依赖包 lodash 和 dmdb。
2前期准备
安装nodejs环境，当前测试环境nodejs版本为v24.9.0。
2.1先初始化package.json（如果选空项目）
npm init -y
2.2安装TYPEORM相关包
npm install typeorm reflect-metadata # TypeORM本体
npm install dmdb # 达梦官方驱动
npm install typeorm-dm # 官方方言包

2.3安装KNEX相关包
npm install knex dmdb # knex 本体 + 达梦驱动
npm install knex-dm # 达梦方言包
2.4查看是否安装成功
npm list dmdb typeorm-dm knex-dm typeorm reflect-metadata

3测试代码准备
3.1TYPEORM测试连接
const { DmdbDataSource } = require("typeorm-dm");

const AppDataSource = new DmdbDataSource({
type: "oracle",
innerType: "dmdb",
url: "dm://SYSDBA:SYSDBa111@localhost:5236?schema=SYSDBA&loginEncrypt=false",
entities: [],
synchronize: false,
logging: true,
});

AppDataSource.initialize()
.then(() => {
console.log("✅ 连接成功！");
process.exit(0);
})
.catch(err => {
console.error("❌ 连接失败：", err);
process.exit(1);
});

3.2KNEX测试连接
const { DmdbDataSource } = require("typeorm-dm");

const AppDataSource = new DmdbDataSource({
type: "oracle",
innerType: "dmdb",
url: "dm://SYSDBA:SYSDBa111@localhost:5236?schema=SYSDBA&loginEncrypt=false",
entities: [],
synchronize: false,
logging: true,
});

AppDataSource.initialize()
.then(() => {
console.log("✅ 连接成功！");
process.exit(0);
})
.catch(err => {
console.error("❌ 连接失败：", err);
process.exit(1);
});

3.3TYPEORM测试插入
const { DmdbDataSource } = require("typeorm-dm");
const { EntitySchema } = require("typeorm");

// 直接在同一个文件中定义实体
const PhotoSchema = new EntitySchema({
name: "Photo",
columns: {
id: {
primary: true,
type: "int",
generated: true
},
name: {
type: "varchar",
length: 255
}
}
});

const AppDataSource = new DmdbDataSource({
type: "oracle",
innerType: "dmdb",
host: "localhost",
port: 5236,
username: "SYSDBA",
password: "SYSDBa111",
schema: "SYSDBA",
url: "dm://SYSDBA:SYSDBa111@localhost:5236?schema=SYSDBA",
entities: [PhotoSchema],
synchronize: true,
logging: false,
})

AppDataSource.initialize()
.then(async () => {
let photoRepo = AppDataSource.getRepository("Photo")
let photo = { name: "photo1" }
await photoRepo.save(photo)
let allPhoto = await photoRepo.find()
console.log(allPhoto)
await AppDataSource.destroy()
})
.catch((error) => console.log(error))

3.4KNEX测试插入
async function main() {
const knex = require('knex')({
client: require('knex-dm'),
connection: {
connectString: 'localhost:5236',
user: 'SYSDBA',
password: 'SYSDBa111',
schema: 'SYSDBA',
},
});
// 创建表
await knex.schema.createTableIfNotExists('USERTEST', (table) => {
table.increments('ID'); // 自增INT列
table.string('USER_NAME'); // VARCHAR列
});
// 插入
await knex('USERTEST').insert({'USER_NAME': 'Tom'});
// 自增列手动插入必须指定选项
await knex('USERTEST', {identityInsert: true}).insert({'ID': 2, 'USER_NAME': 'Jack'});
// 查询
let result = await knex('USERTEST').select('ID', 'USER_NAME').where({'ID': 1});
console.log(result); // 输出： [ { ID: 1, USER_NAME: 'Tom' } ]
await knex.destroy();
}
main();

3.5 TYPEORM测试复杂查询
const { DmdbDataSource } = require("typeorm-dm");
const { EntitySchema } = require("typeorm");

// ========== 实体定义 ==========
const UserSchema = new EntitySchema({
name: "User",
tableName: "T_USER",
schema: "AAA",
columns: {
id: { primary: true, type: "int", generated: true },
name: { type: "varchar", length: 100 }
},
relations: {
posts: {
type: "one-to-many",
target: "Post",
inverseSide: "user",
cascade: true
}
}
});

const PostSchema = new EntitySchema({
name: "Post",
tableName: "T_POST",
schema: "BBB",
columns: {
id: { primary: true, type: "int", generated: true },
title: { type: "varchar", length: 200 },
userId: { type: "int", name: "user_id" }
},
relations: {
user: {
type: "many-to-one",
target: "User",
joinColumn: { name: "user_id" }
}
}
});

// ========== 数据源配置 ==========
const AppDataSource = new DmdbDataSource({
type: "oracle",
innerType: "dmdb",
host: "localhost",
port: 5236,
username: "SYSDBA",
password: "SYSDBa111",
extra: {
loginEncrypt: false
},
entities: [UserSchema, PostSchema],
synchronize: true,
logging: true,
});

// ========== 主函数 ==========
async function main() {
try {
await AppDataSource.initialize();
console.log("✅ 数据源初始化成功\n");

const userRepo = AppDataSource.getRepository("User");
const postRepo = AppDataSource.getRepository("Post");

// ========== 1. 先造测试数据 ==========
console.log("=== 1. 插入测试数据 ===");

// 清空现有数据（可选）
await postRepo.clear();
await userRepo.clear();
console.log("清空现有数据");

// 创建用户1
const user1 = { name: "张三" };
const savedUser1 = await userRepo.save(user1);
console.log("插入用户1:", savedUser1);

// 创建用户2
const user2 = { name: "李四" };
const savedUser2 = await userRepo.save(user2);
console.log("插入用户2:", savedUser2);

// 创建用户1的文章
const post1 = { title: "TypeORM 入门教程", userId: savedUser1.id };
const savedPost1 = await postRepo.save(post1);
console.log("插入文章1:", savedPost1);

const post2 = { title: "达梦数据库使用指南", userId: savedUser1.id };
const savedPost2 = await postRepo.save(post2);
console.log("插入文章2:", savedPost2);

// 创建用户2的文章
const post3 = { title: "Node.js 最佳实践", userId: savedUser2.id };
const savedPost3 = await postRepo.save(post3);
console.log("插入文章3:", savedPost3);

console.log("\n=== 2. 查询测试 ===");

// ========== 方式1：使用 Relations 自动加载关联 ==========
console.log("\n--- 方式1：Relations 自动加载（用户及文章）---");
const usersWithPosts = await userRepo.find({
relations: ["posts"]
});
console.log("结果:", JSON.stringify(usersWithPosts, null, 2));

// ========== 方式2：使用 QueryBuilder 关联查询 ==========
console.log("\n--- 方式2：QueryBuilder 关联查询 ---");
const result = await AppDataSource
.createQueryBuilder()
.select(["user.name", "post.title"])
.from("User", "user")
.leftJoin("user.posts", "post")
.getMany();
console.log("结果:", JSON.stringify(result, null, 2));

// ========== 方式3：带条件的 QueryBuilder ==========
console.log("\n--- 方式3：带条件的 QueryBuilder（查询张三的文章）---");
const resultWithCondition = await AppDataSource
.createQueryBuilder()
.select(["user.name", "post.title"])
.from("User", "user")
.leftJoin("user.posts", "post")
.where("user.name = :name", { name: "张三" })
.getMany();
console.log("结果:", JSON.stringify(resultWithCondition, null, 2));

// ========== 方式4：使用 Repository 的查询方法 ==========
console.log("\n--- 方式4：Repository 查询（文章带用户信息）---");
const postsWithUser = await postRepo.find({
relations: ["user"]
});
console.log("结果:", JSON.stringify(postsWithUser, null, 2));

// ========== 方式5：手动跨 Schema 查询（使用原生表名）==========
console.log("\n--- 方式5：手动跨 Schema 查询 ---");
const rawResult3 = await AppDataSource
.createQueryBuilder()
.select("user.name", "userName")
.addSelect("post.title", "postTitle")
.from("User", "user") // 使用实体名，不是表名
.leftJoin("user.posts", "post") // 使用关系路径
.getRawMany();

console.log("结果:", rawResult3);

// ========== 方式6：统计每个用户的文章数量 ==========
console.log("\n--- 方式6：统计每个用户的文章数量 ---");
const stats = await AppDataSource
.createQueryBuilder()
.select("user.name", "userName")
.addSelect("COUNT(post.id)", "postCount")
.from("User", "user")
.leftJoin("user.posts", "post")
.groupBy("user.id")
.addGroupBy("user.name")
.getRawMany();
console.log("结果:", stats);

await AppDataSource.destroy();
console.log("\n✅ 连接已关闭");

} catch (error) {
console.error("❌ 错误:", error);
console.error("错误详情:", error.message);
}
}

// 执行
main();

运行结果如下：

解释说明：
方法一，查询用户及文章，把AAA模式下的T_USER表和BBB模式下的T_POST表做一对多的关联，通过创建的表结构primary和joinColumn: { name: "user_id" }，自动构建主外键关联，以T_USER作为主表，构建语句如下：
SELECT "User"."id" AS "User_id", "User"."name" AS "User_name", "User__User_posts"."id" AS "User__User_posts_id", "User__User_posts"."title" AS
"User__User_posts_title", "User__User_posts"."user_id" AS "User__User_posts_user_id" FROM "AAA"."T_USER" "User" LEFT JOIN "BBB"."T_POST" "User__User_posts" ON "User__User_posts"."user_id"="User"."id"

方法二，使用 QueryBuilder 关联查询构建基于T_USER的id关联查询相关列，通过数据源构建QueryBuilder对象，通过查询构造器，getmany获取多条记录，构造sql如下：
SELECT "user"."name" AS "user_name", "user"."id" AS "user_id", "post"."title" AS "post_title", "post"."id" AS "post_id" FROM "AAA"."T_USER" "user" LEFT JOIN "BBB"."T_POST" "post" ON "post"."user_id"="user"."id"

方法三，查询张三的文章，构建带where条件的 QueryBuilder来查询，构造sql如下：
SELECT "user"."name" AS "user_name", "user"."id" AS "user_id", "post"."title" AS "post_title", "post"."id" AS "post_id" FROM "AAA"."T_USER" "user" LEFT JOIN "BBB"."T_POST" "post" ON "post"."user_id"="user"."id" WHERE "user"."name" = '张三'

方法四,使用Repository查询文章带用户信息，以T_post作为主表，右关联查询，构建sql如下：
SELECT "Post"."id" AS "Post_id", "Post"."title" AS "Post_title", "Post"."user_id" AS "Post_user_id", "Post__Post_user"."id" AS "Post__Post_user
_id", "Post__Post_user"."name" AS "Post__Post_user_name" FROM "BBB"."T_POST" "Post" LEFT JOIN "AAA"."T_USER" "Post__Post_user" ON "Post__Post_user"."id"="Post"."user_id"

方法五，手动跨 Schema做关联查询，手动构建QueryBuilder查询器，自定义列名别名，以T_USER作为主表，构建语句如下：
SELECT "user"."name" AS "userName", "post"."title" AS "postTitle" FROM "AAA"."T_USER" "user" LEFT JOIN "BBB"."T_POST" "post" ON "post"."user_id"="user"."id"

方法六，统计每个用户的文章数量，通过QueryBuilder构建查询器，用T_USER的id列进行分组，构建语句如下：
SELECT "user"."name" AS "userName", COUNT("post"."id") AS "postCount" FROM "AAA"."T_USER" "user" LEFT JOIN "BBB"."T_POST" "post" ON "post"."user_id"="user"."id" GROUP BY "user"."id", "user"."name"

3.6 KNEX测试复杂查询
async function main() {

// 创建连接函数
function createKnexInstance() {
return require('knex')({
client: require('knex-dm'),
connection: {
connectString: 'localhost:5236',
user: 'SYSDBA',
password: 'SYSDBa111',
},
});
}
let knex = createKnexInstance();

// 准备测试数据
await knex.schema.dropTableIfExists('ORDER_ITEMS');
await knex.schema.dropTableIfExists('ORDERS');
await knex.schema.dropTableIfExists('PRODUCTS');
await knex.schema.dropTableIfExists('USERTEST');

// 创建用户表
await knex.schema.createTable('USERTEST', (table) => {
table.increments('ID');
table.string('USER_NAME');
table.integer('AGE');
table.string('CITY');
table.string('STATUS').defaultTo('active');
table.timestamp('CREATED_AT').defaultTo(knex.fn.now());
});

// 创建订单表
await knex.schema.createTable('ORDERS', (table) => {
table.increments('ID');
table.integer('USER_ID');
table.decimal('AMOUNT', 10, 2);
table.string('STATUS');
table.timestamp('ORDER_DATE');
});

// 创建产品表
await knex.schema.createTable('PRODUCTS', (table) => {
table.increments('ID');
table.string('PRODUCT_NAME');
table.decimal('PRICE', 10, 2);
table.integer('STOCK');
});

// 创建订单明细表
await knex.schema.createTable('ORDER_ITEMS', (table) => {
table.increments('ID');
table.integer('ORDER_ID');
table.integer('PRODUCT_ID');
table.integer('QUANTITY');
table.decimal('UNIT_PRICE', 10, 2);
});

// 插入测试数据
await knex('USERTEST').insert([
{ USER_NAME: '张三', AGE: 25, CITY: '北京', STATUS: 'active' },
{ USER_NAME: '李四', AGE: 32, CITY: '上海', STATUS: 'active' },
{ USER_NAME: '王五', AGE: 28, CITY: '广州', STATUS: 'inactive' },
{ USER_NAME: '赵六', AGE: 35, CITY: '深圳', STATUS: 'active' }
]);

// 插入订单数据
await knex('ORDERS').insert([
{ USER_ID: 1, AMOUNT: 100.00, STATUS: 'completed', ORDER_DATE: new Date() },
{ USER_ID: 1, AMOUNT: 200.00, STATUS: 'completed', ORDER_DATE: new Date() },
{ USER_ID: 2, AMOUNT: 150.00, STATUS: 'completed', ORDER_DATE: new Date() },
{ USER_ID: 3, AMOUNT: 300.00, STATUS: 'cancelled', ORDER_DATE: new Date() }
]);

// 插入产品数据
await knex('PRODUCTS').insert([
{ PRODUCT_NAME: '产品A', PRICE: 50.00, STOCK: 100 },
{ PRODUCT_NAME: '产品B', PRICE: 30.00, STOCK: 200 },
{ PRODUCT_NAME: '产品C', PRICE: 80.00, STOCK: 50 }
]);

// 插入订单明细数据
await knex('ORDER_ITEMS').insert([
{ ORDER_ID: 1, PRODUCT_ID: 1, QUANTITY: 2, UNIT_PRICE: 50.00 },
{ ORDER_ID: 1, PRODUCT_ID: 2, QUANTITY: 1, UNIT_PRICE: 30.00 },
{ ORDER_ID: 2, PRODUCT_ID: 3, QUANTITY: 1, UNIT_PRICE: 80.00 },
{ ORDER_ID: 3, PRODUCT_ID: 1, QUANTITY: 3, UNIT_PRICE: 50.00 }
]);

// 1. INNER JOIN 查询
const result1 = await knex('USERTEST as u')
.join('ORDERS as o', 'u.ID', 'o.USER_ID')
.select('u.USER_NAME', 'u.AGE', 'o.AMOUNT', 'o.STATUS')
.where('u.STATUS', 'active');
console.log('INNER JOIN 结果:', result1);

// 2. 多表 JOIN
const result2 = await knex('USERTEST as u')
.join('ORDERS as o', 'u.ID', 'o.USER_ID')
.join('ORDER_ITEMS as oi', 'o.ID', 'oi.ORDER_ID')
.join('PRODUCTS as p', 'oi.PRODUCT_ID', 'p.ID')
.select(
'u.USER_NAME',
'o.ID',
'p.PRODUCT_NAME',
'oi.QUANTITY',
knex.raw('("oi".QUANTITY * "oi".UNIT_PRICE) as subtotal')
)
.where('u.STATUS', 'active')
.orderBy('o.ID', 'desc');
console.log('多表 JOIN 结果:', result2);

await knex.destroy();
}
main();

解释说明：

方法一：
USERTEST与ORDERS做关联查询，执行的sql为：
select "u"."USER_NAME", "u"."AGE", "o"."AMOUNT", "o"."STATUS"
from "USERTEST" as "u"
inner join "ORDERS" as "o" on "u"."ID" = "o"."USER_ID"
where "u"."STATUS" = 'active';
方法二：
多表关联查询，查询用户相关产品信息
select
"u"."USER_NAME",
"o"."ID",
"p"."PRODUCT_NAME",
"oi"."QUANTITY",
("oi"."QUANTITY" * "oi"."UNIT_PRICE") as "subtotal"
from "USERTEST" as "u"
inner join "ORDERS" as "o" on "u"."ID" = "o"."USER_ID"
inner join "ORDER_ITEMS" as "oi" on "o"."ID" = "oi"."ORDER_ID"
inner join "PRODUCTS" as "p" on "oi"."PRODUCT_ID" = "p"."ID"
where "u"."STATUS" = 'active'
order by "o"."ID" desc;

4 总结
TypeORM：全功能 ORM，将数据库表映射为对象，支持关联、事务、迁移，适合复杂业务系统，开发效率高但性能有损耗。
Knex：轻量级 SQL 构建器，只负责生成和执行 SQL，不关心对象映射，适合复杂查询和高性能场景，灵活但需要手写更多代码。
TypeORM 是 ORM 框架，让你像操作 JavaScript 对象一样操作数据库（自动映射表和关系）；Knex 是 SQL 查询构建器，帮你用链式方法拼写 SQL 语句（更贴近原生 SQL）。