Skip to content

Commit 2bfa300

Browse files
mmarifatmguida22
mmarifat
and
mguida22
authored
fix(postgres): resolve alias or table name in upsert/insert or update conditionally (#11452)
* fix: resolve regression in upsert and orUpdate for PostgreSQL driver This fix addresses a regression introduced in pull request #11082 by ensuring correct handling of alias names and table names when they are equal or distinct when entities use schema Closes: #11440 * style: code formatted * chore: run tests by running through all the drivers * chore: set enabledDrivers as postgres * chore: added postgres family members as enabled drivers * chore: accepted suggestion to remove extra comments Co-authored-by: Mike Guida <mike@mguida.com> --------- Co-authored-by: Mike Guida <mike@mguida.com>
1 parent 5003aaa commit 2bfa300

3 files changed

Lines changed: 128 additions & 1 deletion

File tree

‎src/query-builder/InsertQueryBuilder.ts‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -406,6 +406,10 @@ export class InsertQueryBuilder<
406406
*/
407407
protected createInsertExpression() {
408408
const tableName = this.getTableName(this.getMainTableName())
409+
const tableOrAliasName =
410+
this.alias !== this.getMainTableName()
411+
? this.escape(this.alias)
412+
: tableName
409413
const valuesExpression = this.createValuesExpression() // its important to get values before returning expression because oracle rely on native parameters and ordering of them is important
410414
const returningExpression =
411415
this.connection.driver.options.type === "oracle" &&
@@ -590,7 +594,7 @@ export class InsertQueryBuilder<
590594
query += overwrite
591595
.map(
592596
(column) =>
593-
`${this.escape(this.alias)}.${this.escape(
597+
`${tableOrAliasName}.${this.escape(
594598
column,
595599
)} IS DISTINCT FROM EXCLUDED.${this.escape(
596600
column,

‎test/github-issues/11440/entity/Post.ts‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,13 @@
1+
import { Column, Entity } from "../../../../src"
2+
3+
@Entity({
4+
schema: "typeorm_test",
5+
name: "post",
6+
})
7+
export class Post {
8+
@Column({ primary: true })
9+
id: number
10+
11+
@Column({ nullable: true })
12+
title: string
13+
}

‎test/github-issues/11440/issue-11440.ts‎

Lines changed: 110 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,110 @@
1+
import { expect } from "chai"
2+
import "reflect-metadata"
3+
4+
import { DataSource } from "../../../src"
5+
import {
6+
closeTestingConnections,
7+
createTestingConnections,
8+
reloadTestingDatabases,
9+
} from "../../utils/test-utils"
10+
import { Post } from "./entity/Post"
11+
12+
describe("github issues > #11440", () => {
13+
let dataSources: DataSource[]
14+
15+
before(async () => {
16+
dataSources = await createTestingConnections({
17+
entities: [Post],
18+
enabledDrivers: ["postgres", "aurora-postgres", "cockroachdb"],
19+
})
20+
})
21+
beforeEach(() => reloadTestingDatabases(dataSources))
22+
after(() => closeTestingConnections(dataSources))
23+
24+
it("should use table or alias name during upsert or doUpdate when both schema name and skipUpdateIfNoValuesChanged supplied", async () => {
25+
Promise.all(
26+
dataSources.map(async (dataSource) => {
27+
const repository = dataSource.getRepository(Post)
28+
await repository.save([
29+
{
30+
id: 1,
31+
title: "First Post",
32+
},
33+
{
34+
id: 2,
35+
title: "Second Post",
36+
},
37+
{
38+
id: 3,
39+
title: "Third Post",
40+
},
41+
])
42+
43+
// upsert does not cast alias as class name
44+
await repository.upsert(
45+
[
46+
{
47+
id: 1,
48+
title: "First Post",
49+
},
50+
{
51+
id: 2,
52+
title: "Second Post UPSERTED",
53+
},
54+
],
55+
{
56+
conflictPaths: ["id"],
57+
upsertType: "on-conflict-do-update",
58+
skipUpdateIfNoValuesChanged: true,
59+
},
60+
)
61+
62+
const query = repository
63+
.createQueryBuilder()
64+
.insert()
65+
.values([
66+
{
67+
id: 1,
68+
title: "First Post",
69+
},
70+
{
71+
id: 3,
72+
title: "Third Post OR_UPDATED",
73+
},
74+
])
75+
.orUpdate(["title"], ["id"], {
76+
skipUpdateIfNoValuesChanged: true,
77+
})
78+
79+
// orUpdate cast alias as class name
80+
expect(query.getSql()).to.equal(
81+
`INSERT INTO "typeorm_test"."post" AS "Post"("id", "title") ` +
82+
`VALUES ($1, $2), ($3, $4) ` +
83+
`ON CONFLICT ( "id" ) DO UPDATE ` +
84+
`SET "title" = EXCLUDED."title" ` +
85+
`WHERE ("Post"."title" IS DISTINCT FROM EXCLUDED."title")`,
86+
)
87+
await query.execute()
88+
89+
const posts = await repository.find({
90+
order: { id: "ASC" },
91+
})
92+
93+
expect(posts).to.deep.equal([
94+
{
95+
id: 1,
96+
title: "First Post",
97+
},
98+
{
99+
id: 2,
100+
title: "Second Post UPSERTED",
101+
},
102+
{
103+
id: 3,
104+
title: "Third Post OR_UPDATED",
105+
},
106+
])
107+
}),
108+
)
109+
})
110+
})

0 commit comments

Comments
 (0)