Repository: salsita/node-pg-migrate (ts)

Issue:
Feature request: support for INCLUDE on index creation
Since Postgres 11: https://www.postgresql.org/docs/11/sql-createindex.html

----

> **`INCLUDE`**
The optional INCLUDE clause specifies a list of columns which will be included in the index as non-key columns. A non-key column cannot be used in an index scan search qualification, and it is disregarded for purposes of any uniqueness or exclusion constraint enforced by the index. However, an index-only scan can return the contents of non-key columns without having to visit the index's table, since they are available directly from the index entry. Thus, addition of non-key columns allows index-only scans to be used for queries that otherwise could not use them. (...)

Pull request:
Adding include option for createIndex
Fixes #536

Patch:
diff --git a/docs/indexes.md b/docs/indexes.md
index 201b225..55e0622 100644
--- a/docs/indexes.md
+++ b/docs/indexes.md
@@ -16,6 +16,7 @@
   - `ifNotExists` _[bool]_ - default false
   - `opclass` _[[Name](migrations.md#type)]_ - name of an operator class to use
   - `method` _[string]_ - btree | hash | gist | spgist | gin
+  - `include` _[string or array of strings]_ - columns to add to the include clause
 
 **Aliases:** `addIndex`
 **Reverse Operation:** `dropIndex`
diff --git a/src/operations/indexes.ts b/src/operations/indexes.ts
index 7eb419c..a7b7c21 100644
--- a/src/operations/indexes.ts
+++ b/src/operations/indexes.ts
@@ -64,16 +64,21 @@ export function createIndex(mOptions: MigrationOptions) {
     */
     const indexName = generateIndexName(typeof tableName === 'object' ? tableName.name : tableName, columns, options)
     const columnsString = generateColumnsString(columns, mOptions.literal)
-    const unique = options.unique ? ' UNIQUE ' : ''
-    const concurrently = options.concurrently ? ' CONCURRENTLY ' : ''
+    const unique = options.unique ? ' UNIQUE' : ''
+    const concurrently = options.concurrently ? ' CONCURRENTLY' : ''
     const ifNotExistsStr = options.ifNotExists ? ' IF NOT EXISTS' : ''
     const method = options.method ? ` USING ${options.method}` : ''
     const where = options.where ? ` WHERE ${options.where}` : ''
     const opclass = options.opclass ? ` ${mOptions.schemalize(options.opclass)}` : ''
+    const include = options.include
+      ? ` INCLUDE (${(_.isArray(options.include) ? options.include : [options.include])
+          .map(mOptions.literal)
+          .join(', ')})`
+      : ''
     const indexNameStr = mOptions.literal(indexName)
     const tableNameStr = mOptions.literal(tableName)
 
-    return `CREATE ${unique} INDEX ${concurrently}${ifNotExistsStr} ${indexNameStr} ON ${tableNameStr}${method} (${columnsString}${opclass})${where};`
+    return `CREATE${unique} INDEX${concurrently}${ifNotExistsStr} ${indexNameStr} ON ${tableNameStr}${method} (${columnsString}${opclass})${include}${where};`
   }
   _create.reverse = dropIndex(mOptions)
   return _create
diff --git a/src/operations/indexesTypes.ts b/src/operations/indexesTypes.ts
index 420cddb..72a3212 100644
--- a/src/operations/indexesTypes.ts
+++ b/src/operations/indexesTypes.ts
@@ -8,6 +8,7 @@ export interface CreateIndexOptions {
   ifNotExists?: boolean
   opclass?: Name
   method?: 'btree' | 'hash' | 'gist' | 'spgist' | 'gin'
+  include?: string | string[]
 }
 
 export interface DropIndexOptions extends DropOptions {


Test patch:
diff --git a/test/indexes-test.ts b/test/indexes-test.ts
index 5801889..6a7e909 100644
--- a/test/indexes-test.ts
+++ b/test/indexes-test.ts
@@ -10,8 +10,8 @@ describe('lib/operations/indexes', () => {
       const args: CreateIndexParams = [{ schema: 'mySchema', name: 'myTable' }, ['colA', 'colB']]
       const sql1 = Indexes.createIndex(options1)(...args)
       const sql2 = Indexes.createIndex(options2)(...args)
-      expect(sql1).to.equal('CREATE  INDEX  "myTable_colA_colB_index" ON "mySchema"."myTable" ("colA", "colB");')
-      expect(sql2).to.equal('CREATE  INDEX  "my_table_col_a_col_b_index" ON "my_schema"."my_table" ("col_a", "col_b");')
+      expect(sql1).to.equal('CREATE INDEX "myTable_colA_colB_index" ON "mySchema"."myTable" ("colA", "colB");')
+      expect(sql2).to.equal('CREATE INDEX "my_table_col_a_col_b_index" ON "my_schema"."my_table" ("col_a", "col_b");')
     })
 
     it('add opclass option', () => {
@@ -27,12 +27,18 @@ describe('lib/operations/indexes', () => {
       ]
       const sql1 = Indexes.createIndex(options1)(...args)
       const sql2 = Indexes.createIndex(options2)(...args)
-      expect(sql1).to.equal(
-        'CREATE  INDEX  "zIndex" ON "xTable" USING gist ("yName" someOpclass) WHERE some condition;',
-      )
+      expect(sql1).to.equal('CREATE INDEX "zIndex" ON "xTable" USING gist ("yName" someOpclass) WHERE some condition;')
       expect(sql2).to.equal(
-        'CREATE  INDEX  "z_index" ON "x_table" USING gist ("y_name" some_opclass) WHERE some condition;',
+        'CREATE INDEX "z_index" ON "x_table" USING gist ("y_name" some_opclass) WHERE some condition;',
       )
     })
+
+    it('add include option', () => {
+      const args: CreateIndexParams = ['xTable', ['yName'], { name: 'zIndex', include: 'someOtherColumn' }]
+      const sql1 = Indexes.createIndex(options1)(...args)
+      const sql2 = Indexes.createIndex(options2)(...args)
+      expect(sql1).to.equal('CREATE INDEX "zIndex" ON "xTable" ("yName") INCLUDE ("someOtherColumn");')
+      expect(sql2).to.equal('CREATE INDEX "z_index" ON "x_table" ("y_name") INCLUDE ("some_other_column");')
+    })
   })
 })