ORM inserting duplicate records

[KazWar]

Context and Description

The problem is that the ORM inserts duplicate records into a postgres table with constraints which should prevent from duplicate entries being inserted. Regular queries from the database itself, deny me from doing just that, whilst the ORM just does it anyway.

Environment Details

I'm using the following things

• MacOS Mojave ver 10.14.3
• Xcode ver. 10.1(10B61)
• Latest versions of SwiftKueryORM, SwiftKueryPostgreSQL, Kitura
• Using the Kitura Starter template
• PGAdmin 4.2
• Postgres DB ver.11

Used code

Application.swift

extension Client : Model {
    static var tableName:String = "Clients"
}

class Persistance {
    static let pool = PostgreSQLConnection.createPool(host: manager.host, port: manager.port, options: [.databaseName("DashboardDB"), .userName(manager.user), .password(manager.password)], poolOptions: ConnectionPoolOptions(initialCapacity: 10, maxCapacity: 50))

    static func setUp(){
        Database.default = Database(pool)
    }
}

Client.swift

import Foundation
import Kitura
import SwiftKueryORM
import SwiftKueryPostgreSQL

public class ClientEndpoints{
    private let a:App
    typealias T = Client

    required init(_ app:App) {
        a = app
    }

    public func CreateRoutes() -> Void {
        a.router.post("/client/register", handler: Register)
    }

    func Register(object: Client, completion: @escaping (Client?, RequestError?) -> Void) {
        let client = Client(macAddress: object.macAddress, clientId: UUID().uuidString)
        client.save{
            result, error in
            completion (client, error)
        }
    }
}

ModelClient.swift

struct Client:Codable {
    var macAddress:String
    var clientId:String?

    enum CodingKeys: String, CodingKey {
        case macAddress = "client_mac"
        case clientId = "client_id"
    }
}

SQL used to generate table

CREATE TABLE public."Clients"
(
    client_mac character varying(17) COLLATE pg_catalog."default" NOT NULL,
    id bigint NOT NULL DEFAULT nextval('clients_id_seq'::regclass),
    client_id text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT clients_pkey PRIMARY KEY (client_mac, id, client_id),
    CONSTRAINT unique_values UNIQUE (client_mac, client_id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public."Clients"
    OWNER to postgres;

Notes

1. The table is not generated using the tableAsync() function.
2. I'm also using Kitura Credentials with google tokens middleware, but I don't think this could be the source of the problem.

[kilnerm]

My first thought here was whether I could recreate the issue outside Kitura with a very simple table. Using the following table:

CREATE TABLE simplemodels
(
field bigint NOT NULL,
id bigint NOT NULL DEFAULT nextval('simplemodels_id_seq'::regclass),
other bigint NOT NULL,
CONSTRAINT clients_pkey PRIMARY KEY (field, id, other),
CONSTRAINT unique_values UNIQUE (field, other)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;

with the following Model:

struct SimpleModel: Codable {
    var field: Int
    var other: Int
}

extension SimpleModel: Model {
    static var tableName: String = "simplemodels"
}

I defined an instanced and attempted to save it twice:

print("Saving first time")
myModel.save() { model, error in
    guard let _ = model else {
        guard let error = error else {
            handleError("First save - Unknown error")
            return
        }
        handleError("First save" + error.description)
        return
    }
    print("First save succesful")
    print("Saving second time")
    myModel.save() { model, error in
        guard let _ = model else {
            guard let error = error else {
                handleError("Second save - Unknown error")
                return
            }
            handleError("Second save" + error.description)
            return
        }
        print("Second save succesful")
        semaphore.signal()
    }
}

the results of which are below:

Saving first time
First save succesful
Saving second time
Second save706 : Query execution error:
ERROR:  duplicate key value violates unique constraint "unique_values"
DETAIL:  Key (field, other)=(1, 99) already exists.
 For query: INSERT INTO "simplemodels" ("field", "other") VALUES ($1, $2)

Next step is to use a table using String fields.

[kilnerm]

Ok, I think I see what the problem is.

According to the PostgreSQL documentation (https://www.postgresql.org/docs/9.4/ddl-constraints.html) on UNIQUE constraints the constraint defined on the table....

CONSTRAINT unique_values UNIQUE (client_mac, client_id)

... means that the combination of client_mac and client_id must be unique.

As the client_id is generated (UUID().uuidString) on each call to the route then every save call will pass the unique constraint as while the client_mac value is the same, the combination of client_mac and client_id will be unique.

[KazWar]

Solved the problem by changing

CONSTRAINT unique_values UNIQUE (client_mac, client_id)

into

CONSTRAINT unique_value1 UNIQUE (client_mac),
CONSTRAINT unique_value2 UNIQUE (client_id)