server: column "id" specified more than once (checkInsertTargets)

[GP89]

Describe the bug

I'm a bit at a loss of how to proceed. Everything was working as intended but recently I started getting this error when saving to the table. I have added logging to the db (postgres) and indeed the INSERT statement has two 'id' fields. This wasn't happening before

After looking through, I think I see the problem. I added a foreign key to the table with the name id also. Im not sure how it was working before but I now can't save to the table (It was working for quite a while after the foreign key was added)

I'm thinking I need to create a migration to rename the field- how would I do that?

db_1                     | 2023-05-18 14:57:17.275 UTC [43] ERROR:  column "id" specified more than once at character 234
db_1                     | 2023-05-18 14:57:17.275 UTC [43] STATEMENT:  INSERT INTO "activation_data_v2" ("id", "licence_type", "order_reference", "registered_copies", "registered_user", "registered_key", "subscription_expiry", "subscription_quantity", "subscription_reference", "subscription_signature", "id") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) RETURNING "id"

To Reproduce

This is the migration

struct CreateActivationDataNew: Migration {

    func prepare(on database: Database) -> EventLoopFuture<Void> {

        return database.schema("activation_data_v2")
            .field("id", .string, .identifier(auto: false))
            .field("licence_type", .int)
            .field("order_reference", .string)
            .field("registered_copies", .int)
            .field("registered_user", .string)
            .field("registered_key", .string)
            .field("subscription_expiry", .string)
            .field("subscription_quantity", .int)
            .field("subscription_reference", .string)
            .field("subscription_signature", .string)
            .create()

    }

    func revert(on database: Database) -> EventLoopFuture<Void> {

        return database.schema("activation_data_v2").delete()

    }

}

struct AddActivationDataForeignKey: Migration {

    func prepare(on database: Database) -> EventLoopFuture<Void> {

        return database.schema("activation_data_v2")
            .foreignKey("id", references: "customers", "id", onDelete: .cascade, name: "customer_id_fk")
            .update()

    }

    // undo
    func revert(on database: Database) -> EventLoopFuture<Void> {

        return database.schema("activation_data_v2")
            .deleteConstraint(.name("customer_id_fk"))
            .update()

    }

}

The Model

final class ActivationDataDB: Model {

    static let schema = "activation_data_v2"

    @ID(custom: "id", generatedBy: .user)
    var id: String?

    @Field(key: "licence_type")
    var licenceType: Int
    @Field(key: "order_reference")
    var orderReference: String
    @Field(key: "registered_copies")
    var registeredCopies: Int
    @Field(key: "registered_user")
    var registeredUser: String
    @Field(key: "registered_key")
    var registeredKey: String
    @Field(key: "subscription_expiry")
    var subscriptionExpiry: String
    @Field(key: "subscription_quantity")
    var subscriptionQuantity: Int
    @Field(key: "subscription_reference")
    var subscriptionReference: String
    @Field(key: "subscription_signature")
    var subscriptionSignature: String
    @Parent(key: "id")
    var customer: CustomerDB

    init() { }
}

Environment

• Vapor Framework version: 4.67.5
• Vapor Toolbox version:
• OS version: Running in docker, swift 5.6 image

Additional context

Add any other context about the problem here.

[0xTim]

@GP89 this is related to https://github.com/vapor/fluent-kit/issues/564 and is due to recent updates in Fluent in the way that queries are built. The model as is doesn't make a whole lot of sense and you don't have duplicate columns in the DB (you can confirm this by looking at the table structure) but you have added a foreign key constraint on ID to customers-ID. Does your customer ID and ID column have the same value? Because I think that's how it's working

[GP89]

Ah yes you're right id and the foreign key are the same value (customer.id in this case). I don't know why it ended up like this, I don't recall but possibly because it wasn't possible to create a foreign key on the primary key field itself?

What would you recommend to remedy this?

You're right there's no field to rename really, so I'm not too sure what the key value in @Parent(key:) actually refers to.

Would I need to make a migration to a new table with a regular uuid pk id field possibly, and the customer_id as it's own field which is the foreign key to customer.id? Then as part of that migration I could copy the data from the existing table.

I guess I would need to update the queries in that case as well that filtered on id to reflect the new field

[0xTim]

You shouldn't need a migration since there should be no changes to the DB. If you're referencing a new Swift field in the model then yeah you'll need to update your queries. Closing this for now, Discord is probably a better place to discuss other stuff