Home Original page

feat: added support for NOWAIT & SKIP LOCKED in Postgres (#5927) · typeorm/typeorm@2c90e1c

@@ -23,7 +23,7 @@ userRepository.find({ relations: ["profile", "photos", "videos", "videos.video_a

2323

* `join` - joins needs to be performed for the entity. Extended version of "relations".

24242525

```typescript

26-

userRepository.find({

26+

userRepository.find({

2727

join: {

2828

alias: "user",

2929

leftJoinAndSelect: {

@@ -40,7 +40,7 @@ userRepository.find({

4040

```typescript

4141

userRepository.find({ where: { firstName: "Timber", lastName: "Saw" } });

4242

```

43-

Querying a column from an embedded entity should be done with respect to the hierarchy in which it was defined. Example:

43+

Querying a column from an embedded entity should be done with respect to the hierarchy in which it was defined. Example:

44444545

```typescript

4646

userRepository.find({ where: { name: { first: "Timber", last: "Saw" } } });

@@ -57,7 +57,7 @@ userRepository.find({

5757

});

5858

```

595960-

will execute following query:

60+

will execute following query:

61616262

```sql

6363

SELECT * FROM "user" WHERE ("firstName" = 'Timber' AND "lastName" = 'Saw') OR ("firstName" = 'Stan' AND "lastName" = 'Lee')

@@ -66,7 +66,7 @@ SELECT * FROM "user" WHERE ("firstName" = 'Timber' AND "lastName" = 'Saw') OR ("

6666

* `order` - selection order.

67676868

```typescript

69-

userRepository.find({

69+

userRepository.find({

7070

order: {

7171

name: "ASC",

7272

id: "DESC"

@@ -79,28 +79,28 @@ userRepository.find({

7979

* `skip` - offset (paginated) from where entities should be taken.

80808181

```typescript

82-

userRepository.find({

82+

userRepository.find({

8383

skip: 5

8484

});

8585

```

86868787

* `take` - limit (paginated) - max number of entities that should be taken.

88888989

```typescript

90-

userRepository.find({

90+

userRepository.find({

9191

take: 10

9292

});

9393

```

94949595

** If you are using typeorm with MSSQL, and want to use `take` or `limit`, you need to use order as well or you will receive the following error: `'Invalid usage of the option NEXT in the FETCH statement.'`

96969797

```typescript

98-

userRepository.find({

99-

order: {

100-

columnName: 'ASC'

101-

},

102-

skip: 0,

103-

take: 10

98+

userRepository.find({

99+

order: {

100+

columnName: 'ASC'

101+

},

102+

skip: 0,

103+

take: 10

104104

})

105105

```

106106

@@ -120,7 +120,7 @@ userRepository.find({

120120

```

121121

or

122122

```ts

123-

{ mode: "pessimistic_read"|"pessimistic_write"|"dirty_read" }

123+

{ mode: "pessimistic_read"|"pessimistic_write"|"dirty_read"|"pessimistic_partial_write"|"pessimistic_write_or_fail" }

124124

```

125125126126

for example:

@@ -131,15 +131,17 @@ userRepository.findOne(1, {

131131

})

132132

```

133133134+

`pessimistic_partial_write` and `pessimistic_write_or_fail` are supported only on Postgres and are equivalents of `SELECT .. FOR UPDATE SKIP LOCKED` and `SELECT .. FOR UPDATE NOWAIT`, accordingly.

135+134136

Complete example of find options:

135137136138

```typescript

137-

userRepository.find({

139+

userRepository.find({

138140

select: ["firstName", "lastName"],

139141

relations: ["profile", "photos", "videos"],

140-

where: {

141-

firstName: "Timber",

142-

lastName: "Saw"

142+

where: {

143+

firstName: "Timber",

144+

lastName: "Saw"

143145

},

144146

order: {

145147

name: "ASC",

@@ -166,7 +168,7 @@ const loadedPosts = await connection.getRepository(Post).find({

166168

})

167169

```

168170169-

will execute following query:

171+

will execute following query:

170172171173

```sql

172174

SELECT * FROM "post" WHERE "title" != 'About #1'

@@ -182,7 +184,7 @@ const loadedPosts = await connection.getRepository(Post).find({

182184

});

183185

```

184186185-

will execute following query:

187+

will execute following query:

186188187189

```sql

188190

SELECT * FROM "post" WHERE "likes" < 10

@@ -198,7 +200,7 @@ const loadedPosts = await connection.getRepository(Post).find({

198200

});

199201

```

200202201-

will execute following query:

203+

will execute following query:

202204203205

```sql

204206

SELECT * FROM "post" WHERE "likes" <= 10

@@ -214,7 +216,7 @@ const loadedPosts = await connection.getRepository(Post).find({

214216

});

215217

```

216218217-

will execute following query:

219+

will execute following query:

218220219221

```sql

220222

SELECT * FROM "post" WHERE "likes" > 10

@@ -230,7 +232,7 @@ const loadedPosts = await connection.getRepository(Post).find({

230232

});

231233

```

232234233-

will execute following query:

235+

will execute following query:

234236235237

```sql

236238

SELECT * FROM "post" WHERE "likes" >= 10

@@ -246,7 +248,7 @@ const loadedPosts = await connection.getRepository(Post).find({

246248

});

247249

```

248250249-

will execute following query:

251+

will execute following query:

250252251253

```sql

252254

SELECT * FROM "post" WHERE "title" = 'About #2'

@@ -262,7 +264,7 @@ const loadedPosts = await connection.getRepository(Post).find({

262264

});

263265

```

264266265-

will execute following query:

267+

will execute following query:

266268267269

```sql

268270

SELECT * FROM "post" WHERE "title" LIKE '%out #%'

@@ -278,7 +280,7 @@ const loadedPosts = await connection.getRepository(Post).find({

278280

});

279281

```

280282281-

will execute following query:

283+

will execute following query:

282284283285

```sql

284286

SELECT * FROM "post" WHERE "likes" BETWEEN 1 AND 10

@@ -294,7 +296,7 @@ const loadedPosts = await connection.getRepository(Post).find({

294296

});

295297

```

296298297-

will execute following query:

299+

will execute following query:

298300299301

```sql

300302

SELECT * FROM "post" WHERE "title" IN ('About #2','About #3')

@@ -310,7 +312,7 @@ const loadedPosts = await connection.getRepository(Post).find({

310312

});

311313

```

312314313-

will execute following query (Postgres notation):

315+

will execute following query (Postgres notation):

314316315317

```sql

316318

SELECT * FROM "post" WHERE "title" = ANY(['About #2','About #3'])

@@ -326,7 +328,7 @@ const loadedPosts = await connection.getRepository(Post).find({

326328

});

327329

```

328330329-

will execute following query:

331+

will execute following query:

330332331333

```sql

332334

SELECT * FROM "post" WHERE "title" IS NULL

@@ -342,7 +344,7 @@ const loadedPosts = await connection.getRepository(Post).find({

342344

});

343345

```

344346345-

will execute following query:

347+

will execute following query:

346348347349

```sql

348350

SELECT * FROM "post" WHERE "likes" = "dislikes" - 4

@@ -359,7 +361,7 @@ const loadedPosts = await connection.getRepository(Post).find({

359361

});

360362

```

361363362-

will execute following query:

364+

will execute following query:

363365364366

```sql

365367

SELECT * FROM "post" WHERE "currentDate" > NOW()

@@ -379,7 +381,7 @@ const loadedPosts = await connection.getRepository(Post).find({

379381

});

380382

```

381383382-

will execute following query:

384+

will execute following query:

383385384386

```sql

385387

SELECT * FROM "post" WHERE NOT("likes" > 10) AND NOT("title" = 'About #2')