Migration adding unique constraint fails.

[mixio]

Fluent 3.0.0
Fluent SQLite 3.0.0
SQL 2.0.2

In implementing the TILApp tutorial, but using SQLite instead of PostgreSQL in chapter 23: Making Categories unique, the following migration fails:

import FluentSQLite
import Vapor

struct MakeCategoriesUnique: Migration {
    typealias Database = SQLiteDatabase
    static func prepare(on connection: SQLiteConnection) -> Future<Void> {
        return Database.update(Category.self, on: connection) { builder in
            builder.unique(on: \.name)
        }
    }
    static func revert(on connection: SQLiteConnection) -> Future<Void> {
        return Database.update(Category.self, on: connection) { builder in
            builder.deleteUnique(from: \.name)
        }
    }
}

with fatalError("SQLite only supports adding one (1) column in an ALTER query.") in FluentSQLite/SQLiteDatabase+SchemaSupporting.swift:75 :

    /// See `SchemaSupporting`.
    public static func schemaExecute(_ fluent: Schema, on conn: SQLiteConnection) -> Future<Void> {
        let query: SQLiteQuery
        switch fluent.statement {
        case ._createTable:
            var createTable: SQLiteCreateTable = .createTable(fluent.table)
            createTable.columns = fluent.columns
            createTable.tableConstraints = fluent.constraints
            query = ._createTable(createTable)
        case ._alterTable:
            guard fluent.columns.count == 1 && fluent.constraints.count == 0 else {
                jjprint(fluent.columns.count) // 0 columns
                jjprint(fluent.constraints.count) // 1 constraint
                /// See https://www.sqlite.org/lang_altertable.html
/*>>>>>>>>>*/   fatalError("SQLite only supports adding one (1) column in an ALTER query.")
            }
            query = .alterTable(.init(
                table: fluent.table,
                value: .addColumn(fluent.columns[0])
            ))
        case ._dropTable:
            let dropTable: SQLiteDropTable = .dropTable(fluent.table)
            query = ._dropTable(dropTable)
        }
        return conn.query(query).transform(to: ())
    }

Notice that fluent.columns.count is 0 and fluent.constraints.count is 1. Which is the contrary of what the guard condition expects.

Apparently, you can't add constraints to SQLite columns without recreating the table:

https://stackoverflow.com/questions/15497985/how-to-add-unique-constraint-in-already-made-table-in-sqlite-ios

But you can add a unique index to the table:

    CREATE UNIQUE INDEX `uq:Category.name` ON Category(name);

Is that a behavior that FluentSQLite could adopt? Or is the cause of this fatalError somewhere else?

[Rep2]

Ran into the same problem. Moving index to the model worked.

extension Device: SQLiteMigration {
    static func prepare(on conn: SQLiteConnection) -> Future<Void> {
        return Database.create(self, on: conn) { builder in
            try addProperties(to: builder)

            builder.unique(on: \.udid)
        }
    }
}