Raw 查询构建器

本指南涵盖 raw 查询构建器。你将学会如何：

使用绑定安全地执行原始 SQL 语句
在其他查询构建器内部嵌入原始 SQL 片段
使用位置绑定（?、??）和命名绑定（:value、:column:）
在事务内部运行原始查询
检查、调试和标记原始查询

概述

Raw 查询构建器为你提供了一种运行 Lucid 查询构建器未建模的 SQL 的方法，同时通过绑定保持安全，免受 SQL 注入。Lucid 暴露了两个互补的入口点：

db.rawQuery(sql, bindings) 返回一个独立执行的查询。用于你想直接运行的完整 SQL 语句。
db.raw(sql, bindings) 返回一个片段，你可以将其传递给另一个查询构建器方法。用于在原本由构建器构建的查询内部降级使用 SQL。

当标准查询构建器无法表达你的需求时，请使用原始查询。对于所有适合的场景，请留在 select、insert 或 update and delete 查询构建器中——它们会为你处理方言差异、标识符引号和结果类型。

原始查询的结果直接来自底层数据库驱动程序，因此其形状是特定于方言的。PostgreSQL 返回一个带有 rows 的结果对象，MySQL 返回一个数组，而 SQLite 直接返回行。请相应地检查或解构结果。

// PostgreSQL
const result = await db.rawQuery('select * from users')
console.log(result.rows)

// MySQL / SQLite
const [rows] = await db.rawQuery('select * from users')

切勿将用户输入拼接到原始 SQL 字符串中。始终使用绑定，驱动程序会对其进行正确的转义。拼接的输入会打开 SQL 注入的大门。

执行原始 SQL

使用 db.rawQuery 运行完整的 SQL 语句。

app/services/reports_service.ts

import db from '@adonisjs/lucid/services/db'

const result = await db.rawQuery(
  `select date_trunc('month', created_at) as month, count(*) as total
   from orders
   group by month
   order by month desc`
)

将动态值作为绑定传递，切勿作为字符串插值。第二个参数可以是数组（用于位置绑定）或对象（用于命名绑定）。

await db.rawQuery('select * from users where id = ?', [userId])

await db.rawQuery('select * from users where id = :id', { id: userId })

嵌入原始片段

使用 db.raw 在另一个查询构建器内部降级到 SQL。该片段通过引用传递，并在查询构建器接受值的地方内联。

const users = await db
  .from('users')
  .select(
    'id',
    'email',
    db.raw('(select count(*) from posts where posts.user_id = users.id) as posts_count')
  )

原始片段可用作 WHERE 值、ORDER BY 表达式、GROUP BY 键或构建器接受表达式的任何其他位置。

await db
  .from('posts')
  .where('view_count', '>', db.raw('(select avg(view_count) from posts)'))

await db.from('posts').orderByRaw('LENGTH(title) DESC')

绑定与 rawQuery 的工作方式相同。

db.from('users').whereRaw('LOWER(email) = ?', [email.toLowerCase()])

绑定

位置绑定

? 被值替换，驱动程序会针对方言转义该值。

await db.rawQuery('select * from users where email = ? and is_active = ?', [
  email,
  true,
])

?? 被标识符（列名或表名）替换，并适当添加引号。

await db.rawQuery('select * from users where ?? = ?', ['users.id', 1])

// SQL: select * from users where "users"."id" = 1

命名绑定

:name 被绑定对象中的匹配值替换。

await db.rawQuery(
  `select * from posts where status = :status and author_id = :authorId`,
  { status: 'published', authorId: user.id }
)

:name:（带尾随冒号）被绑定对象中的标识符替换。

await db.rawQuery(
  `select * from user_logins inner join users on :left: = :right:`,
  { left: 'users.id', right: 'user_logins.user_id' }
)

// SQL: select * from user_logins inner join users on "users"."id" = "user_logins"."user_id"

当相同的值出现多次或 SQL 具有许多参数时，命名绑定可读性更强。对于简单的查询，位置绑定更简短。

包装片段

当将原始片段作为子查询嵌入时，用前缀和后缀将其包装。查询构建器不会自动用括号包装原始片段，因此当周围的 SQL 需要时，你必须自己包装它们。

await db
  .from('users')
  .select(
    'id',
    db
      .raw('select ip_address from user_logins where user_logins.user_id = users.id order by id desc limit 1')
      .wrap('(', ')')
  )

如果没有 wrap，内部 SQL 会逐字插入，当用作列表达式时，这是无效的 SQL。

在事务内部

要在事务内运行原始查询，直接在事务客户端上构建它。trx 对象暴露与 db 服务相同的 .rawQuery 和 .raw 入口点。参见事务指南了解完整模式。

await db.transaction(async (trx) => {
  await trx.rawQuery(
    'update inventory set stock = stock - ? where product_id = ?',
    [quantity, productId]
  )

  await trx.rawQuery(
    'insert into stock_movements (product_id, change, reason) values (?, ?, ?)',
    [productId, -quantity, 'order_fulfilled']
  )
})

检查与标记

toSQL

返回一个描述查询的 { sql, bindings } 对象，而不实际运行它。对日志记录、调试或与另一个 rawQuery 调用组合很有用。

const { sql, bindings } = db
  .rawQuery('select * from users where id = ?', [1])
  .toSQL()

toQuery

返回绑定了替换值的单个字符串形式的 SQL。方便临时检查。转发到日志时，优先使用 toSQL + 绑定，因为字符串插值可能会引入引号问题。

const sql = db.rawQuery('select * from users where id = ?', [1]).toQuery()

debug

仅为该查询启用调试输出。当至少附加了一个监听器时，查询会在 db:query 事件上发出。参见调试指南了解完整的调试工作流。

await db.rawQuery('select * from users').debug(true)

timeout

如果查询运行时间超过给定的毫秒数则中止它。在 PostgreSQL 和 MySQL 上传入 { cancel: true } 以取消底层查询，而不是在客户端超时后让它在服务器上继续运行。

await db.rawQuery('select * from large_audit_log').timeout(30_000, { cancel: true })

reporterData

将任意元数据附加到 db:query 事件载荷，监听器可以访问这些数据。用于使用请求 ID、用户 ID 或功能标志标记查询以便可观测。

await db
  .rawQuery('select * from posts where author_id = ?', [user.id])
  .reporterData({ userId: user.id, source: 'feed' })

start/events.ts

import emitter from '@adonisjs/core/services/emitter'

emitter.on('db:query', (query) => {
  console.log(query.userId, query.source)
})

逃生舱

knexQuery

返回底层的 Knex Raw 实例。当你需要 Lucid 未暴露的原始查询上的 Knex 方法时使用此方法。结果由 Knex 塑造而非 Lucid。有关更广泛的讨论，请参见数据库服务指南中的降级到 Knex。