Skip to content

Commit 22ed3ec

Browse files
freePixelfreePixel
authored
feat(postgres): add support for PostgreSQL indices (#11318)
1 parent 4ffe666 commit 22ed3ec

20 files changed

Lines changed: 480 additions & 71 deletions

File tree

‎docs/docs/advanced-topics/3-indices.md‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -144,6 +144,15 @@ TypeORM supports generating SQL with this option when the concurrent option is s
144144

145145
For more information see the [Postgres documentation](https://www.postgresql.org/docs/current/sql-createindex.html).
146146

147+
## Index Type
148+
If you need to specify a custom type for the index, you can use the `type` property. If the `spatial` property is set, this field will be ignored.
149+
150+
```typescript
151+
@Index({ type: 'hash' })
152+
```
153+
154+
This feature is currently supported only for PostgreSQL.
155+
147156
## Disabling synchronization
148157

149158
TypeORM does not support some index options and definitions (e.g. `lower`, `pg_trgm`) due to many database-specific differences and multiple

‎src/decorator/Index.ts‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -132,6 +132,7 @@ export function Index(
132132
? false
133133
: true,
134134
where: options ? options.where : undefined,
135+
type: options ? options.type : undefined,
135136
unique: options && options.unique ? true : false,
136137
spatial: options && options.spatial ? true : false,
137138
fulltext: options && options.fulltext ? true : false,

‎src/decorator/options/IndexOptions.ts‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,5 @@
1+
import { TableIndexTypes } from "../../schema-builder/options/TableIndexTypes"
2+
13
/**
24
* Describes all index options.
35
*/
@@ -63,4 +65,11 @@ export interface IndexOptions {
6365
* This option is only supported for mongodb database.
6466
*/
6567
expireAfterSeconds?: number
68+
69+
/**
70+
* The `type` option defines the type of the index being created.
71+
* Supported types include B-tree, Hash, GiST, SP-GiST, GIN, and BRIN
72+
* This option is only applicable in PostgreSQL.
73+
*/
74+
type?: TableIndexTypes
6675
}

‎src/driver/Driver.ts‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,9 @@ import { TableForeignKey } from "../schema-builder/table/TableForeignKey"
1616
import { UpsertType } from "./types/UpsertType"
1717
import { OnDeleteType } from "../metadata/types/OnDeleteType"
1818
import { OnUpdateType } from "../metadata/types/OnUpdateType"
19+
import { TableIndex } from "../schema-builder/table/TableIndex"
20+
import { IndexMetadata } from "../metadata/IndexMetadata"
21+
import { TableIndexTypes } from "../schema-builder/options/TableIndexTypes"
1922

2023
export type ReturningType = "insert" | "update" | "delete"
2124

@@ -96,6 +99,11 @@ export interface Driver {
9699
*/
97100
withLengthColumnTypes: ColumnType[]
98101

102+
/**
103+
* Supported index types
104+
*/
105+
supportedIndexTypes?: TableIndexTypes[]
106+
99107
/**
100108
* Gets list of column data types that support precision by a driver.
101109
*/
@@ -282,4 +290,12 @@ export interface Driver {
282290
* Creates an escaped parameter.
283291
*/
284292
createParameter(parameterName: string, index: number): string
293+
294+
/**
295+
* Returns true if both indexes types are equivalent
296+
*/
297+
compareTableIndexTypes?: (
298+
indexA: IndexMetadata,
299+
indexB: TableIndex,
300+
) => boolean
285301
}

‎src/driver/postgres/PostgresDriver.ts‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,9 @@ import { View } from "../../schema-builder/view/View"
2828
import { TableForeignKey } from "../../schema-builder/table/TableForeignKey"
2929
import { InstanceChecker } from "../../util/InstanceChecker"
3030
import { UpsertType } from "../types/UpsertType"
31+
import { IndexMetadata } from "../../metadata/IndexMetadata"
32+
import { TableIndex } from "../../schema-builder/table/TableIndex"
33+
import { TableIndexTypes } from "../../schema-builder/options/TableIndexTypes"
3134

3235
/**
3336
* Organizes communication with PostgreSQL DBMS.
@@ -269,6 +272,18 @@ export class PostgresDriver implements Driver {
269272
metadataValue: "text",
270273
}
271274

275+
/**
276+
* Table indices supported
277+
*/
278+
supportedIndexTypes: TableIndexTypes[] = [
279+
"brin",
280+
"btree",
281+
"gin",
282+
"gist",
283+
"hash",
284+
"spgist",
285+
]
286+
272287
/**
273288
* The prefix used for the parameters
274289
*/
@@ -1469,6 +1484,13 @@ export class PostgresDriver implements Driver {
14691484
return this.parametersPrefix + (index + 1)
14701485
}
14711486

1487+
compareTableIndexTypes = (indexA: IndexMetadata, indexB: TableIndex) => {
1488+
const normalizedA = indexA.isSpatial ? "gist" : (indexA.type ?? "btree")
1489+
const normalizedB = indexB.isSpatial ? "gist" : (indexB.type ?? "btree")
1490+
1491+
return normalizedA.toLowerCase() === normalizedB.toLowerCase()
1492+
}
1493+
14721494
// -------------------------------------------------------------------------
14731495
// Public Methods
14741496
// -------------------------------------------------------------------------

‎src/driver/postgres/PostgresQueryRunner.ts‎

Lines changed: 27 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -3233,13 +3233,14 @@ export class PostgresQueryRunner
32333233
const indicesSql =
32343234
`SELECT "ns"."nspname" AS "table_schema", "t"."relname" AS "table_name", "i"."relname" AS "constraint_name", "a"."attname" AS "column_name", ` +
32353235
`CASE "ix"."indisunique" WHEN 't' THEN 'TRUE' ELSE'FALSE' END AS "is_unique", pg_get_expr("ix"."indpred", "ix"."indrelid") AS "condition", ` +
3236-
`"types"."typname" AS "type_name" ` +
3236+
`"types"."typname" AS "type_name", "am"."amname" AS "index_type" ` +
32373237
`FROM "pg_class" "t" ` +
32383238
`INNER JOIN "pg_index" "ix" ON "ix"."indrelid" = "t"."oid" ` +
32393239
`INNER JOIN "pg_attribute" "a" ON "a"."attrelid" = "t"."oid" AND "a"."attnum" = ANY ("ix"."indkey") ` +
32403240
`INNER JOIN "pg_namespace" "ns" ON "ns"."oid" = "t"."relnamespace" ` +
32413241
`INNER JOIN "pg_class" "i" ON "i"."oid" = "ix"."indexrelid" ` +
32423242
`INNER JOIN "pg_type" "types" ON "types"."oid" = "a"."atttypid" ` +
3243+
`INNER JOIN "pg_am" "am" ON "i"."relam" = "am"."oid" ` +
32433244
`LEFT JOIN "pg_constraint" "cnst" ON "cnst"."conname" = "i"."relname" ` +
32443245
`WHERE "t"."relkind" IN ('m') AND "cnst"."contype" IS NULL AND (${constraintsCondition})`
32453246

@@ -3287,13 +3288,16 @@ export class PostgresQueryRunner
32873288
constraint["constraint_name"]
32883289
)
32893290
})
3291+
32903292
return new TableIndex(<TableIndexOptions>{
32913293
view: view,
32923294
name: constraint["constraint_name"],
32933295
columnNames: indices.map((i) => i["column_name"]),
32943296
isUnique: constraint["is_unique"] === "TRUE",
32953297
where: constraint["condition"],
3298+
isSpatial: constraint["index_type"] === "gist",
32963299
isFulltext: false,
3300+
type: constraint["index_type"],
32973301
})
32983302
})
32993303
return view
@@ -4028,6 +4032,7 @@ export class PostgresQueryRunner
40284032
isUnique: constraint["is_unique"] === "TRUE",
40294033
where: constraint["condition"],
40304034
isSpatial: constraint["index_type"] === "gist",
4035+
type: constraint["index_type"],
40314036
isFulltext: false,
40324037
})
40334038
})
@@ -4343,35 +4348,51 @@ export class PostgresQueryRunner
43434348
return new Query(`DROP TYPE ${enumName}`)
43444349
}
43454350

4351+
/**
4352+
* Builds the SQL `USING <index_type>` clause based on the index type, prioritizing `isSpatial` as `GiST`.
4353+
*/
4354+
4355+
private buildIndexTypeClause(index: TableIndex) {
4356+
const type = index.isSpatial ? "gist" : index.type
4357+
4358+
if (typeof type !== "string") return null
4359+
4360+
return `USING ${type}`
4361+
}
4362+
43464363
/**
43474364
* Builds create index sql.
43484365
*/
43494366
protected createIndexSql(table: Table, index: TableIndex): Query {
4367+
const indexTypeClause = this.buildIndexTypeClause(index)
4368+
43504369
const columns = index.columnNames
43514370
.map((columnName) => `"${columnName}"`)
43524371
.join(", ")
43534372
return new Query(
43544373
`CREATE ${index.isUnique ? "UNIQUE " : ""}INDEX${
43554374
index.isConcurrent ? " CONCURRENTLY" : ""
43564375
} "${index.name}" ON ${this.escapePath(table)} ${
4357-
index.isSpatial ? "USING GiST " : ""
4358-
}(${columns}) ${index.where ? "WHERE " + index.where : ""}`,
4376+
indexTypeClause ?? ""
4377+
} (${columns}) ${index.where ? "WHERE " + index.where : ""}`,
43594378
)
43604379
}
43614380

43624381
/**
43634382
* Builds create view index sql.
43644383
*/
43654384
protected createViewIndexSql(view: View, index: TableIndex): Query {
4385+
const indexTypeClause = this.buildIndexTypeClause(index)
4386+
43664387
const columns = index.columnNames
43674388
.map((columnName) => `"${columnName}"`)
43684389
.join(", ")
43694390
return new Query(
43704391
`CREATE ${index.isUnique ? "UNIQUE " : ""}INDEX "${
43714392
index.name
4372-
}" ON ${this.escapePath(view)} (${columns}) ${
4373-
index.where ? "WHERE " + index.where : ""
4374-
}`,
4393+
}" ON ${this.escapePath(view)} ${
4394+
indexTypeClause ?? ""
4395+
} (${columns}) ${index.where ? "WHERE " + index.where : ""}`,
43754396
)
43764397
}
43774398

‎src/metadata-args/IndexMetadataArgs.ts‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,5 @@
1+
import { TableIndexTypes } from "../schema-builder/options/TableIndexTypes"
2+
13
/**
24
* Arguments for IndexMetadata class.
35
*/
@@ -83,4 +85,11 @@ export interface IndexMetadataArgs {
8385
* This option is only supported for mongodb database.
8486
*/
8587
expireAfterSeconds?: number
88+
89+
/**
90+
* The `type` option defines the type of the index being created.
91+
* Supported types include B-tree, Hash, GiST, SP-GiST, GIN, and BRIN
92+
* This option is only applicable in PostgreSQL.
93+
*/
94+
type?: TableIndexTypes
8695
}

‎src/metadata/IndexMetadata.ts‎

Lines changed: 24 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4,6 +4,7 @@ import { NamingStrategyInterface } from "../naming-strategy/NamingStrategyInterf
44
import { ColumnMetadata } from "./ColumnMetadata"
55
import { EmbeddedMetadata } from "./EmbeddedMetadata"
66
import { TypeORMError } from "../error"
7+
import { TableIndexTypes } from "../schema-builder/options/TableIndexTypes"
78

89
/**
910
* Index metadata contains all information about table's index.
@@ -119,6 +120,13 @@ export class IndexMetadata {
119120
*/
120121
where?: string
121122

123+
/**
124+
* The `type` option defines the type of the index being created.
125+
* Supported types include B-tree, Hash, GiST, SP-GiST, GIN, and BRIN
126+
* This option is only applicable in PostgreSQL.
127+
*/
128+
type?: TableIndexTypes
129+
122130
/**
123131
* Map of column names with order set.
124132
* Used only by MongoDB driver.
@@ -135,6 +143,19 @@ export class IndexMetadata {
135143
columns?: ColumnMetadata[]
136144
args?: IndexMetadataArgs
137145
}) {
146+
// check if index type is supported
147+
if (
148+
options.args &&
149+
options.args.type &&
150+
((options.entityMetadata.connection.driver.supportedIndexTypes &&
151+
!options.entityMetadata.connection.driver.supportedIndexTypes.find(
152+
(idx) => idx === options.args?.type,
153+
)) ||
154+
!options.entityMetadata.connection.driver.supportedIndexTypes)
155+
) {
156+
throw new TypeORMError(`Unsupported index type`)
157+
}
158+
138159
this.entityMetadata = options.entityMetadata
139160
this.embeddedMetadata = options.embeddedMetadata
140161
if (options.columns) this.columns = options.columns
@@ -147,7 +168,8 @@ export class IndexMetadata {
147168
)
148169
this.synchronize = options.args.synchronize
149170
this.isUnique = !!options.args.unique
150-
this.isSpatial = !!options.args.spatial
171+
this.isSpatial =
172+
!!options.args.spatial || options.args.type === "gist"
151173
this.isFulltext = !!options.args.fulltext
152174
this.isNullFiltered = !!options.args.nullFiltered
153175
this.parser = options.args.parser
@@ -158,6 +180,7 @@ export class IndexMetadata {
158180
this.expireAfterSeconds = options.args.expireAfterSeconds
159181
this.givenName = options.args.name
160182
this.givenColumnNames = options.args.columns
183+
this.type = options.args.type
161184
}
162185
}
163186

0 commit comments

Comments
 (0)