How to add an index to a postgres table?

Question

I'm trying to add a column index to a PostgreSQL table using Vapor. I found several tutorial doing this, but none of those code snippets work with the current version.

Answer 1

You can run RAW SQL in migartions like:

import FluentPostgreSQL

struct MigrationTest: PostgreSQLMigration {

    static func revert(on conn: PostgreSQLConnection) -> EventLoopFuture<Void> {
        return conn.future()
    }

    static func prepare(on conn: PostgreSQLConnection) -> Future<Void> {
        return conn.raw("CREATE INDEX test on some_table (field1, field2);").run()
    }
}

To add more statements in one migartion, I do it like this:

static func prepare(on conn: PostgreSQLConnection) -> Future<Void> {
    let _ = conn.raw("create index if not exists idx_one (field1, field2);").run()  
    let _ = conn.raw("create index if not exists idx_two (field3, field4);").run()

    return conn.future()
}

You cant add more statements in one run! For each statement new let _ = conn.raw().run()

and in config

migrations.add(migration: MigrationTest.self, database: .psql)

The advantage of this is that you can add partial indexes etc.