Insert 查询构建器

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

向表中插入单行或多行数据
使用 returning 读回生成的列
使用 onConflict().ignore() 和 .merge() 解决唯一约束冲突
使用通用表表达式（CTE）组合插入操作
在事务内部和备用 schema 上执行插入操作
为日志和可观测性注释查询
检查和调试生成的 SQL

概述

Insert 查询构建器是 Lucid 用于构建 INSERT 语句的流式接口。有关 SELECT、UPDATE 和 DELETE，请参见 select 查询构建器和更新与删除查询。有关原始 SQL，请参见 raw 查询构建器。对于基于模型的插入，优先使用 Model.create() 和 model.save()，它们在底层通过此构建器运行并返回带类型的模型实例。

通过 db 服务获取构建器实例。

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

const query = db.insertQuery()  // 未选择表的构建器
const usersQuery = db.table('users')  // 快捷方式：已选择表的构建器

两种形式都返回相同的 InsertQueryBuilder 实例。db.table(table) 是你最常使用的快捷方式。

插入行

insert

通过传入列值对对象来插入单行。

app/controllers/users_controller.ts

import type { HttpContext } from '@adonisjs/core/http'
import db from '@adonisjs/lucid/services/db'
import hash from '@adonisjs/core/services/hash'

export default class UsersController {
  async store({ request }: HttpContext) {
    const [user] = await db
      .table('users')
      .returning(['id', 'email'])
      .insert({
        email: request.input('email'),
        password: await hash.make(request.input('password')),
      })

    return user
  }
}

insert 的返回值取决于方言，这就是为什么大多数生产代码使用 returning 使结果具有可移植性。

方言	默认返回值
PostgreSQL	除非设置了 `returning`，否则为 `[]`
MSSQL	除非设置了 `returning`，否则为 `[]`
MySQL / MariaDB	`[insertId]`（最后插入的自增 ID）
SQLite (better-sqlite3, SQLite 3.35+)	默认 `[insertId]`；原生支持 `returning`
Oracle	除非设置了 `returning`，否则为 `[]`

multiInsert

通过传入对象数组在单条语句中插入多行。

app/services/imports_service.ts

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

await db.table('users').multiInsert([
  { email: 'virk@adonisjs.com', password_hash: '...' },
  { email: 'romain@adonisjs.com', password_hash: '...' },
])

发出的 SQL 是一条 INSERT INTO ... VALUES (...), (...), ... 语句，与循环调用 insert 相比，它更快且原子提交。不同的行可以包含不同的键；缺失的键将填充为 NULL（或者当 useNullAsDefault 关闭时填充为列的默认值）。

returning 与 multiInsert 配合使用，为每条插入的记录产生一行结果。

const inserted = await db
  .table('users')
  .returning(['id', 'email'])
  .multiInsert([
    { email: 'virk@adonisjs.com' },
    { email: 'romain@adonisjs.com' },
  ])

inserted.forEach((row) => console.log(row.id, row.email))

returning

设置 RETURNING 子句以从插入的行中读回生成的列。在 PostgreSQL、MSSQL、Oracle 以及通过 better-sqlite3 的 SQLite 3.35+ 上受支持。MySQL 不支持 RETURNING 并会忽略该调用；请使用返回的自增 ID 或执行后续的 SELECT 查询。

// 单列
const [{ id }] = await db
  .table('posts')
  .returning('id')
  .insert({ title: 'Hello', body: 'World' })

// 多列
const [post] = await db
  .table('posts')
  .returning(['id', 'created_at'])
  .insert({ title: 'Hello', body: 'World' })

// 所有列
const [post] = await db
  .table('posts')
  .returning('*')
  .insert({ title: 'Hello', body: 'World' })

使用 onConflict 进行 Upsert

onConflict 允许你选择当插入违反唯一约束时发生的事情。它在 PostgreSQL、MySQL、MariaDB 和 SQLite 上受支持。调用 onConflict 后，链式调用 .ignore() 以静默丢弃冲突行，或链式调用 .merge() 以更新现有行（即 upsert）。

传入列名、列名数组或根本不传参数（适用于任何唯一约束）。

db.table('users').insert({ email }).onConflict('email').ignore()

db.table('users').insert({ email }).onConflict(['email', 'tenant_id']).ignore()

db.table('users').insert({ email }).onConflict().ignore()

onConflict().ignore()

当发生冲突时静默跳过插入。发出 ON CONFLICT ... DO NOTHING（或方言等价语句）。

await db
  .table('users')
  .insert({ email: 'virk@adonisjs.com', name: 'Harminder' })
  .onConflict('email')
  .ignore()

// SQL:
// INSERT INTO "users" ("email", "name") VALUES (?, ?)
// ON CONFLICT ("email") DO NOTHING

无论插入是否实际发生，查询都会成功解析。如果你需要检测发生了哪种情况，请结合 returning 使用——只有新插入的行会出现在结果中。

onConflict().merge()

当发生冲突时执行 Upsert（更新或插入）。发出 ON CONFLICT ... DO UPDATE SET（或方言等价语句）。

不带参数时，插入载荷中的每一列都会被更新。

await db
  .table('users')
  .insert({ email: 'virk@adonisjs.com', name: 'Harminder' })
  .onConflict('email')
  .merge()

// SQL:
// INSERT INTO "users" ("email", "name") VALUES (?, ?)
// ON CONFLICT ("email")
// DO UPDATE SET "email" = EXCLUDED."email", "name" = EXCLUDED."name"

传入列名数组以在冲突时仅更新部分列。

await db
  .table('users')
  .insert({ email: 'virk@adonisjs.com', name: 'Harminder', last_seen_at: now })
  .onConflict('email')
  .merge(['last_seen_at'])

传入对象以在冲突时设置不同于插入载荷的显式值。

await db
  .table('users')
  .insert({ email: 'virk@adonisjs.com', login_count: 1 })
  .onConflict('email')
  .merge({ login_count: db.raw('users.login_count + 1') })

通用表表达式 (CTE)

CTE 允许你将子查询与插入操作组合。当你想要插入的行依赖于其他地方计算的数据时很有用。

with

定义插入操作可以引用的 CTE。

db
  .table('user_audit_log')
  .with('latest_login', (query) => {
    query
      .from('user_logins')
      .select('user_id', db.raw('max(created_at) as logged_in_at'))
      .groupBy('user_id')
  })
  .insert(/* ... */)

withRecursive

定义递归 CTE。在 PostgreSQL、MySQL 8+、SQLite 3.8+、MSSQL 和 Oracle 上受支持。有关完整演练，请参见 select 查询构建器的 withRecursive；insert 构建器上的 API 完全相同。

withMaterialized 和 withNotMaterialized

PostgreSQL 特有的提示，强制规划器物化（或拒绝物化）CTE 结果。

db
  .table('archive')
  .withMaterialized('inactive_users', (query) => {
    query.from('users').select('*').where('is_active', false)
  })
  .insert(/* ... */)

执行查询

查询构建器是一个 Promise。await 它以发送插入。

await db.table('users').insert({ email })

在事务内部

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

await db.transaction(async (trx) => {
  const [user] = await trx
    .table('users')
    .returning(['id'])
    .insert({ email: 'virk@adonisjs.com' })

  await trx
    .table('profiles')
    .insert({ user_id: user.id, full_name: 'Harminder Virk' })
})

注释查询

这些助手将识别信息附加到查询上，这在读取慢查询日志或在可观测性管道中处理 db:query 事件时很有用。

comment

为发出的查询添加 SQL 注释。注释会与 SQL 一起出现在数据库的慢查询日志中，这使得 grep 查找源自特定代码路径的查询变得容易。

db
  .table('users')
  .comment('signup endpoint')
  .insert({ email: 'virk@adonisjs.com' })

// SQL: /* signup endpoint */ INSERT INTO "users" ("email") VALUES (?)

reporterData

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

await db
  .table('users')
  .reporterData({ userId: auth.user.id, source: 'signup' })
  .insert({ email: 'virk@adonisjs.com' })

start/events.ts

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

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

参见调试指南了解完整的 db:query 事件载荷。

检查与调试

toSQL

返回一个描述查询将执行的 SQL 的 { sql, bindings } 对象，而不实际运行它。在结果上调用 .toNative() 以查看针对当前方言格式化的 SQL（使用该方言实际的占位符语法）。

const { sql, bindings } = db
  .table('users')
  .insert({ email: 'virk@adonisjs.com' })
  .toSQL()

const native = db
  .table('users')
  .insert({ email: 'virk@adonisjs.com' })
  .toSQL()
  .toNative()

toQuery

将查询作为绑定了替换值的单个插值字符串返回。方便临时检查。转发到日志时，优先使用 toSQL + bindings 以避免引号问题。

const sql = db.table('users').insert({ email: 'virk@adonisjs.com' }).toQuery()

debug

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

db.table('users').debug(true).insert({ email: 'virk@adonisjs.com' })

timeout

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

db.table('users').timeout(5000).insert({ email })
db.table('users').timeout(5000, { cancel: true }).insert({ email })

Schema 与逃生舱

withSchema

为插入操作设置非默认 schema（PostgreSQL、MSSQL）。

db.table('users').withSchema('analytics').insert({ event: 'signup' })

knexQuery

返回底层的 Knex 查询构建器。当你需要 Lucid 未暴露的 Knex 方法时使用此方法。结果由 Knex 塑造而非 Lucid，因此你将失去 Lucid 的带类型结果。有关更广泛的讨论，请参见数据库服务指南中的降级到 Knex。