Skip to content

Commit 72c6991

Browse files
Ben1306alumni
Ben1306
and
alumni
authored
fix: incorrect table alias in insert orUpdate with Postgres driver (#11082)
* fix: resolve issues in insert query orUpdate method with postgres driver This fix make use of table name alias in WHERE clause of onUpdate method with postgres driver to avoid throwing error Closes: #11077 * test: update insert on conflict test --------- Co-authored-by: Lucian Mocanu <[email protected]>
1 parent 5922519 commit 72c6991

File tree

3 files changed

+126
-49
lines changed

3 files changed

+126
-49
lines changed

‎src/query-builder/InsertQueryBuilder.ts‎

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -579,7 +579,6 @@ export class InsertQueryBuilder<
579579
)
580580

581581
query += updatePart.join(", ")
582-
query += " "
583582
}
584583

585584
if (
@@ -591,7 +590,7 @@ export class InsertQueryBuilder<
591590
query += overwrite
592591
.map(
593592
(column) =>
594-
`${tableName}.${this.escape(
593+
`${this.escape(this.alias)}.${this.escape(
595594
column,
596595
)} IS DISTINCT FROM EXCLUDED.${this.escape(
597596
column,

‎test/functional/query-builder/insert-on-conflict/entity/Category.ts‎

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

‎test/functional/query-builder/insert-on-conflict/query-builder-insert-on-conflict.ts‎

Lines changed: 112 additions & 47 deletions
Original file line numberDiff line numberDiff line change
@@ -1,34 +1,41 @@
1+
import { expect } from "chai"
12
import "reflect-metadata"
3+
4+
import { DataSource } from "../../../../src/data-source/DataSource"
25
import {
36
closeTestingConnections,
47
createTestingConnections,
58
reloadTestingDatabases,
69
} from "../../../utils/test-utils"
7-
import { DataSource } from "../../../../src/data-source/DataSource"
10+
import { Category } from "./entity/Category"
811
import { Post } from "./entity/Post"
9-
import { expect } from "chai"
10-
11-
describe("query builder > insertion > on conflict", () => {
12-
let connections: DataSource[]
13-
before(
14-
async () =>
15-
(connections = await createTestingConnections({
16-
entities: [__dirname + "/entity/*{.js,.ts}"],
17-
enabledDrivers: ["postgres", "sqlite", "better-sqlite3"], // since on conflict statement is only supported in postgres and sqlite >= 3.24.0
18-
})),
19-
)
20-
beforeEach(() => reloadTestingDatabases(connections))
21-
after(() => closeTestingConnections(connections))
12+
import { DriverUtils } from "../../../../src/driver/DriverUtils"
13+
14+
describe("query builder > insert > on conflict", () => {
15+
let dataSources: DataSource[]
16+
before(async () => {
17+
dataSources = await createTestingConnections({
18+
entities: [Category, Post],
19+
enabledDrivers: [
20+
"cockroachdb",
21+
"postgres",
22+
"sqlite",
23+
"better-sqlite3",
24+
], // since on conflict statement is only supported in postgres and sqlite >= 3.24.0
25+
})
26+
})
27+
beforeEach(() => reloadTestingDatabases(dataSources))
28+
after(() => closeTestingConnections(dataSources))
2229

2330
it("should perform insertion correctly using onConflict", () =>
2431
Promise.all(
25-
connections.map(async (connection) => {
32+
dataSources.map(async (dataSource) => {
2633
const post1 = new Post()
2734
post1.id = "post#1"
2835
post1.title = "About post"
2936
post1.date = new Date("06 Aug 2020 00:12:00 GMT")
3037

31-
await connection
38+
await dataSource
3239
.createQueryBuilder()
3340
.insert()
3441
.into(Post)
@@ -40,15 +47,15 @@ describe("query builder > insertion > on conflict", () => {
4047
post2.title = "Again post"
4148
post2.date = new Date("06 Aug 2020 00:12:00 GMT")
4249

43-
await connection
50+
await dataSource
4451
.createQueryBuilder()
4552
.insert()
4653
.into(Post)
4754
.values(post2)
4855
.onConflict(`("id") DO NOTHING`)
4956
.execute()
5057

51-
await connection.manager
58+
await dataSource.manager
5259
.findOne(Post, {
5360
where: {
5461
id: "post#1",
@@ -60,7 +67,7 @@ describe("query builder > insertion > on conflict", () => {
6067
date: new Date("06 Aug 2020 00:12:00 GMT"),
6168
})
6269

63-
await connection
70+
await dataSource
6471
.createQueryBuilder()
6572
.insert()
6673
.into(Post)
@@ -69,7 +76,7 @@ describe("query builder > insertion > on conflict", () => {
6976
.setParameter("title", post2.title)
7077
.execute()
7178

72-
await connection.manager
79+
await dataSource.manager
7380
.findOne(Post, {
7481
where: {
7582
id: "post#1",
@@ -83,10 +90,12 @@ describe("query builder > insertion > on conflict", () => {
8390
}),
8491
))
8592

86-
it("should support alias in insert", () =>
93+
it("should support alias in insert using onConflict", () =>
8794
Promise.all(
88-
connections.map(async (connection) => {
89-
if (connection.driver.options.type !== "postgres") return
95+
dataSources.map(async (dataSource) => {
96+
if (!DriverUtils.isPostgresFamily(dataSource.driver)) {
97+
return
98+
}
9099

91100
const post1 = new Post()
92101
post1.id = "post#1"
@@ -98,15 +107,15 @@ describe("query builder > insertion > on conflict", () => {
98107
post2.title = "Again post"
99108
post2.date = new Date("06 Aug 2020 00:12:02 GMT")
100109

101-
await connection
110+
await dataSource
102111
.createQueryBuilder()
103112
.insert()
104113
.into(Post)
105114
.values([post1, post2])
106115
.orIgnore()
107116
.execute()
108117

109-
await connection.manager
118+
await dataSource.manager
110119
.find(Post, {
111120
order: {
112121
id: "ASC",
@@ -130,7 +139,7 @@ describe("query builder > insertion > on conflict", () => {
130139
post2.title = "Edited post"
131140
post2.date = new Date("07 Aug 2020 00:12:04 GMT")
132141

133-
await connection
142+
await dataSource
134143
.createQueryBuilder(Post, "p")
135144
.insert()
136145
.values([post1, post2])
@@ -140,7 +149,7 @@ describe("query builder > insertion > on conflict", () => {
140149
.setParameter("title", post2.title)
141150
.execute()
142151

143-
await connection.manager
152+
await dataSource.manager
144153
.find(Post, {
145154
order: {
146155
id: "ASC",
@@ -163,13 +172,13 @@ describe("query builder > insertion > on conflict", () => {
163172

164173
it("should perform insertion correctly using orIgnore", () =>
165174
Promise.all(
166-
connections.map(async (connection) => {
175+
dataSources.map(async (dataSource) => {
167176
const post1 = new Post()
168177
post1.id = "post#1"
169178
post1.title = "About post"
170179
post1.date = new Date("06 Aug 2020 00:12:00 GMT")
171180

172-
await connection
181+
await dataSource
173182
.createQueryBuilder()
174183
.insert()
175184
.into(Post)
@@ -181,15 +190,15 @@ describe("query builder > insertion > on conflict", () => {
181190
post2.title = "Again post"
182191
post2.date = new Date("06 Aug 2020 00:12:00 GMT")
183192

184-
await connection
193+
await dataSource
185194
.createQueryBuilder()
186195
.insert()
187196
.into(Post)
188197
.values(post2)
189198
.orIgnore("date")
190199
.execute()
191200

192-
await connection.manager
201+
await dataSource.manager
193202
.findOne(Post, {
194203
where: {
195204
id: "post#1",
@@ -205,13 +214,13 @@ describe("query builder > insertion > on conflict", () => {
205214

206215
it("should perform insertion correctly using orUpdate", () =>
207216
Promise.all(
208-
connections.map(async (connection) => {
217+
dataSources.map(async (dataSource) => {
209218
const post1 = new Post()
210219
post1.id = "post#1"
211220
post1.title = "About post"
212221
post1.date = new Date("06 Aug 2020 00:12:00 GMT")
213222

214-
await connection
223+
await dataSource
215224
.createQueryBuilder()
216225
.insert()
217226
.into(Post)
@@ -223,7 +232,7 @@ describe("query builder > insertion > on conflict", () => {
223232
post2.title = "Again post"
224233
post2.date = new Date("06 Aug 2020 00:12:00 GMT")
225234

226-
await connection
235+
await dataSource
227236
.createQueryBuilder()
228237
.insert()
229238
.into(Post)
@@ -232,7 +241,7 @@ describe("query builder > insertion > on conflict", () => {
232241
.setParameter("title", post2.title)
233242
.execute()
234243

235-
await connection.manager
244+
await dataSource.manager
236245
.findOne(Post, {
237246
where: {
238247
id: "post#1",
@@ -245,16 +254,20 @@ describe("query builder > insertion > on conflict", () => {
245254
})
246255
}),
247256
))
257+
248258
it("should perform insertion on partial index using orUpdate", () =>
249259
Promise.all(
250-
connections.map(async (connection) => {
251-
if (connection.driver.options.type !== "postgres") return
260+
dataSources.map(async (dataSource) => {
261+
if (!DriverUtils.isPostgresFamily(dataSource.driver)) {
262+
return
263+
}
264+
252265
const post1 = new Post()
253266
post1.id = "post#1"
254267
post1.title = "About post"
255268
post1.date = new Date("06 Aug 2020 00:12:00 GMT")
256269

257-
const sql = connection.manager
270+
const sql = dataSource.manager
258271
.createQueryBuilder()
259272
.insert()
260273
.into(Post)
@@ -275,14 +288,17 @@ describe("query builder > insertion > on conflict", () => {
275288
))
276289
it("should perform insertion using partial index and skipping update on no change", () =>
277290
Promise.all(
278-
connections.map(async (connection) => {
279-
if (connection.driver.options.type !== "postgres") return
291+
dataSources.map(async (dataSource) => {
292+
if (dataSource.driver.options.type !== "postgres") {
293+
return
294+
}
295+
280296
const post1 = new Post()
281297
post1.id = "post#1"
282298
post1.title = "About post"
283299
post1.date = new Date("06 Aug 2020 00:12:00 GMT")
284300

285-
const sql = connection.manager
301+
const sql = dataSource.manager
286302
.createQueryBuilder()
287303
.insert()
288304
.into(Post)
@@ -298,26 +314,75 @@ describe("query builder > insertion > on conflict", () => {
298314
expect(sql).to.equal(
299315
`INSERT INTO post(id, title, date) ` +
300316
`VALUES ($1, $2, $3) ON CONFLICT ( date ) ` +
301-
`WHERE ( date > 2020-01-01 ) DO UPDATE SET title = EXCLUDED.title ` +
317+
`WHERE ( date > 2020-01-01 ) DO UPDATE SET title = EXCLUDED.title ` +
302318
`WHERE (post.title IS DISTINCT FROM EXCLUDED.title)`,
303319
)
304320
}),
305321
))
306-
it("should throw error if using indexPredicate amd an unsupported driver", () =>
322+
323+
it("should support alias in insert using orUpdate", () =>
307324
Promise.all(
308-
connections.map(async (connection) => {
325+
dataSources.map(async (dataSource) => {
326+
if (!DriverUtils.isPostgresFamily(dataSource.driver)) {
327+
return
328+
}
329+
330+
const categoryRepo = dataSource.getRepository(Category)
331+
332+
const initialCategories = ["Documents", "Applications"].map(
333+
(name, id) => ({ id, name }),
334+
)
335+
await categoryRepo.save(initialCategories)
336+
337+
const mockCategories = ["Video", "Photo", "Audio"].map(
338+
(name, index) => ({ id: index + 1, name }),
339+
)
340+
const query = categoryRepo
341+
.createQueryBuilder()
342+
.insert()
343+
.values(mockCategories)
344+
.orUpdate(["name"], ["id"], {
345+
skipUpdateIfNoValuesChanged: true,
346+
})
347+
348+
expect(query.getSql()).to.equal(
349+
`INSERT INTO "category" AS "Category"("id", "name") ` +
350+
`VALUES ($1, $2), ($3, $4), ($5, $6) ` +
351+
`ON CONFLICT ( "id" ) DO UPDATE ` +
352+
`SET "name" = EXCLUDED."name" ` +
353+
`WHERE ("Category"."name" IS DISTINCT FROM EXCLUDED."name")`,
354+
)
355+
expect(await query.execute()).not.to.throw
356+
357+
const categories = await categoryRepo.find({
358+
order: { id: "ASC" },
359+
})
360+
expect(categories).to.deep.equal([
361+
{ id: 0, name: "Documents" },
362+
{ id: 1, name: "Video" },
363+
{ id: 2, name: "Photo" },
364+
{ id: 3, name: "Audio" },
365+
])
366+
}),
367+
))
368+
369+
it("should throw error if using indexPredicate and an unsupported driver", () =>
370+
Promise.all(
371+
dataSources.map(async (dataSource) => {
309372
if (
310-
!connection.driver.supportedUpsertTypes.includes(
373+
!dataSource.driver.supportedUpsertTypes.includes(
311374
"on-duplicate-key-update",
312375
)
313-
)
376+
) {
314377
return
378+
}
379+
315380
const post1 = new Post()
316381
post1.id = "post#1"
317382
post1.title = "About post"
318383
post1.date = new Date("06 Aug 2020 00:12:00 GMT")
319384

320-
const sql = connection.manager
385+
const sql = dataSource.manager
321386
.createQueryBuilder()
322387
.insert()
323388
.into(Post)

0 commit comments

Comments
 (0)