在Drizzle中创建视图时如何重命名SELECT语句中的列？

我用Drizzle ORM定义了PostgreSQL的表结构，尝试创建视图时在SELECT里指定了自定义属性名作为列别名，但生成的SQL未自动添加别名，导致两表id列重名报错。

表结构定义 #

export const testSchema = pgSchema("test_schema");

export const testTableOne = testSchema.table('test_table_one', {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    column1: varchar({ length: 255 }).notNull(),
    table2: integer().references(() => testTableTwo.id)
})

export const testTableTwo = testSchema.table('test_table_two', {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    column2: varchar({ length: 255 }).notNull(),
})

视图创建代码 #

export const testView = testSchema.view('test_view').as((qb) => {
    const query = qb
    .select({
        id_table_one: testTableOne.id,
        id_table_two: testTableTwo.id,
        column1: testTableOne.column1,
        column2: testTableTwo.column2
    })
    .from(testTableOne)
    .leftJoin(testTableTwo, eq(testTableOne.table2, testTableTwo.id))

    console.log(query.toSQL())
    return query
})

报错信息 #

error: column "id" specified more than once
    at ...\node_modules\pg-pool\index.js:45:11
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async Object.query ...\node_modules\drizzle-kit\bin.cjs:78341:26)
    at async pgPush (...\node_modules\drizzle-kit\bin.cjs:81930:13)
    at async Object.handler (...\node_modules\drizzle-kit\bin.cjs:92155:9)
    at async run (...\node_modules\drizzle-kit\bin.cjs:91395:7) {
  length: 100,
  severity: 'ERROR',
  code: '42701',

解决办法 #

要让Drizzle自动将自定义属性名作为列别名，有两种可靠方式：

方法一：使用alias()方法 #

通过Drizzle提供的alias()工具，显式为字段绑定别名：

import { alias } from 'drizzle-orm';

export const testView = testSchema.view('test_view').as((qb) => {
    const query = qb
    .select({
        id_table_one: alias(testTableOne.id, 'id_table_one'),
        id_table_two: alias(testTableTwo.id, 'id_table_two'),
        column1: testTableOne.column1,
        column2: testTableTwo.column2
    })
    .from(testTableOne)
    .leftJoin(testTableTwo, eq(testTableOne.table2, testTableTwo.id))

    console.log(query.toSQL())
    return query
})

方法二：使用sql模板字符串 #

通过sql模板手动拼接字段与别名，灵活性更强：

import { sql } from 'drizzle-orm';

export const testView = testSchema.view('test_view').as((qb) => {
    const query = qb
    .select({
        id_table_one: sql`${testTableOne.id} AS id_table_one`,
        id_table_two: sql`${testTableTwo.id} AS id_table_two`,
        column1: testTableOne.column1,
        column2: testTableTwo.column2
    })
    .from(testTableOne)
    .leftJoin(testTableTwo, eq(testTableOne.table2, testTableTwo.id))

    console.log(query.toSQL())
    return query
})

两种方式都会生成带AS别名的SQL，彻底解决列名重复问题。

内容的提问来源于stack exchange，提问作者Karel Čančara