我完全懂你这种感受——当项目规模扩大，路由控制器里塞满原生SQL字符串，代码看起来就像是SQL和JavaScript的“拼接怪物”，维护和阅读都特别头疼。而且你坚持使用原生SQL的想法非常合理：ORM虽然能简化开发，但有时候会带来性能损耗，也没法完全发挥SQL的灵活性（比如复杂的联表、聚合查询）。

下面我分享几个经过实战验证的最佳实践和架构模式，帮你在坚持原生SQL的同时，让代码变得结构化、可维护且易于测试：

1. 采用Repository模式分离数据访问逻辑 #

核心思路是把所有数据库查询逻辑从路由/控制器中抽离出来，放到专门的Repository层，让控制器只负责处理HTTP请求、调用Repository、返回响应，而Repository专注于和数据库交互。

实战示例： #

创建src/repositories/functionRepository.js：

const db = require('../db');
const { mapRowToFunctionConfig } = require('../mappers/functionMapper');

class FunctionRepository {
  async getPermissionsConfig() {
    const sql = `
      SELECT 
        \`function\`.\`function_key\` AS \`key\`,
        GROUP_CONCAT(DISTINCT \`permission\`.\`role_id\` ORDER BY \`permission\`.\`role_id\` ASC) AS permission
      FROM \`function\`
      LEFT JOIN \`permission\` ON \`function\`.\`function_id\` = \`permission\`.\`function_id\`
      GROUP BY \`function\`.\`function_id\`
    `;
    
    const { err, rows } = await db.async.all(sql, []);
    if (err) {
      // 抛出自定义错误，由全局中间件统一处理
      throw new Error(`获取权限配置失败: ${err.message}`);
    }
    
    // 调用映射器转换数据（下文会讲）
    return rows.map(mapRowToFunctionConfig);
  }
}

module.exports = new FunctionRepository();

然后你的路由控制器src/controllers/configController.js就会变得非常简洁：

const functionRepository = require('../repositories/functionRepository');

async function getConfig(req, res, next) {
  try {
    const config = await functionRepository.getPermissionsConfig();
    return res.status(200).json({ code: 200, config });
  } catch (err) {
    // 把错误传递给全局错误处理中间件
    next(err);
  }
}

module.exports = { getConfig };

2. 用独立SQL文件管理复杂查询 #

对于特别长或者格式复杂的SQL，把它们从JavaScript文件中抽离到单独的.sql文件里，这样SQL的格式和在数据库客户端里完全一致，不用在JS中处理引号转义，也更方便用数据库工具调试。

实战示例： #

创建src/queries/function/getPermissionsConfig.sql：

SELECT 
  `function`.`function_key` AS `key`,
  GROUP_CONCAT(DISTINCT `permission`.`role_id` ORDER BY `permission`.`role_id` ASC) AS permission
FROM `function`
LEFT JOIN `permission` ON `function`.`function_id` = `permission`.`function_id`
GROUP BY `function`.`function_id`

然后在Repository中读取这个文件：

const fs = require('fs/promises');
const path = require('path');
const db = require('../db');
const { mapRowToFunctionConfig } = require('../mappers/functionMapper');

class FunctionRepository {
  async getPermissionsConfig() {
    // 读取SQL文件（项目启动时可缓存，避免重复IO）
    const sqlPath = path.join(__dirname, '../queries/function/getPermissionsConfig.sql');
    const sql = await fs.readFile(sqlPath, 'utf8');
    
    const { err, rows } = await db.async.all(sql, []);
    if (err) throw new Error(`获取权限配置失败: ${err.message}`);
    
    return rows.map(mapRowToFunctionConfig);
  }
}

module.exports = new FunctionRepository();

3. 封装数据映射逻辑（DTO转换） #

你代码中把数据库行转换为业务对象的逻辑（rows.map(...)），也应该从控制器中抽离出来，放到专门的Mapper层，让Repository返回的直接是干净的业务数据，不用控制器操心数据格式转换。

实战示例： #

创建src/mappers/functionMapper.js：

function mapRowToFunctionConfig(row) {
  return {
    key: row.key,
    permission: row.permission 
      ? row.permission.split(',').map(id => Number(id)) 
      : []
  };
}

module.exports = { mapRowToFunctionConfig };

这样Repository返回的就是已经处理好的业务对象，控制器直接返回即可，完全不用关心数据库行的结构。

4. 统一错误处理与数据库连接 #

不要在每个Repository或控制器中重复编写if (err)的错误处理逻辑，而是在全局层面统一处理数据库错误：

方案： #

1. 封装数据库查询的基础方法，在db/index.js中统一处理错误，转为Promise风格：

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD
});

async function query(sql, params = []) {
  try {
    const [rows] = await pool.execute(sql, params);
    return rows;
  } catch (err) {
    console.error('数据库查询错误:', err);
    throw new Error('数据库查询失败');
  }
}

module.exports = { query };

2. 在Express中添加全局错误处理中间件src/middlewares/errorHandler.js：

function errorHandler(err, req, res, next) {
  console.error('请求错误:', err);
  
  // 根据错误类型返回标准化响应
  if (err.message.includes('数据库查询失败')) {
    return res.status(500).json({ code: 500, msg: '数据库查询失败' });
  }
  
  res.status(500).json({ code: 500, msg: '服务器内部错误' });
}

module.exports = errorHandler;

然后在app.js中注册这个中间件：

const errorHandler = require('./middlewares/errorHandler');

// ... 其他中间件和路由注册

app.use(errorHandler);

这样所有的数据库错误都会被统一捕获并返回标准响应，不用在每个控制器或Repository中重复编写错误处理代码。

5. 安全处理动态SQL（参数化查询 + 轻量查询构建器） #

如果需要编写动态SQL（比如根据请求参数添加WHERE条件），绝对不能用字符串拼接（会导致SQL注入！），必须使用参数化查询。如果动态逻辑复杂，可以使用轻量的查询构建器（比如Knex.js）来生成安全的SQL字符串，同时保持原生SQL的执行方式。

示例：用Knex生成动态SQL #

const knex = require('knex')({ client: 'mysql2' });
const { query } = require('../db');
const { mapRowToFunctionConfig } = require('../mappers/functionMapper');

async function getFunctionsByRole(roleId) {
  // 用Knex构建动态查询
  const queryBuilder = knex('function')
    .leftJoin('permission', 'function.function_id', 'permission.function_id')
    .where('permission.role_id', roleId)
    .select('function.function_key as key');
  
  // 生成原生SQL和参数数组
  const { sql, bindings } = queryBuilder.toSQL().toNative();
  
  // 用封装的query方法执行
  const rows = await query(sql, bindings);
  return rows.map(mapRowToFunctionConfig);
}

Knex在这里只是作为SQL生成工具，不会接管数据库连接或ORM的其他功能，完全符合你坚持原生SQL的需求。

6. 项目目录结构参考 #

按照上面的模式，你的项目结构会变得非常清晰：

project/
├── src/
│   ├── controllers/          # HTTP请求处理（路由逻辑）
│   │   └── configController.js
│   ├── repositories/         # 数据访问层（封装SQL查询）
│   │   └── functionRepository.js
│   ├── mappers/              # 数据映射（数据库行 → 业务对象）
│   │   └── functionMapper.js
│   ├── queries/              # 原生SQL文件（存放复杂查询）
│   │   └── function/
│   │       └── getPermissionsConfig.sql
│   ├── db/                   # 数据库连接与基础查询封装
│   │   └── index.js
│   ├── middlewares/          # 全局中间件（错误处理、认证等）
│   │   └── errorHandler.js
│   ├── routes/               # 路由定义
│   │   └── configRoutes.js
│   └── app.js                # Express应用入口
├── tests/                    # 测试用例
│   └── repositories/
│       └── functionRepository.test.js
└── .env                      # 环境变量

7. 测试Repository层 #

因为Repository层是独立的模块，你可以轻松地为它编写单元测试或集成测试：

const { query } = require('../src/db');
const functionRepository = require('../src/repositories/functionRepository');

// 用jest模拟数据库查询
jest.mock('../src/db', () => ({
  query: jest.fn()
}));

describe('FunctionRepository', () => {
  it('应该返回格式化后的权限配置', async () => {
    // 模拟数据库返回结果
    query.mockResolvedValue([
      { key: 'user:view', permission: '1,2' },
      { key: 'user:edit', permission: null }
    ]);
    
    const config = await functionRepository.getPermissionsConfig();
    
    expect(config).toEqual([
      { key: 'user:view', permission: [1, 2] },
      { key: 'user:edit', permission: [] }
    ]);
  });
});

总结 #

这些实践的核心是关注点分离：

• 控制器：只处理HTTP请求/响应
• Repository：只处理数据库查询
• Mapper：只处理数据格式转换
• 中间件：只处理全局逻辑（错误、认证等）

通过这种方式，你既能保留原生SQL的灵活性和性能优势，又能让代码结构清晰、易于维护和测试。