How to use "Postgres Error: duplicate key value violates unique constraint" in Fluent

LarsIverKruse · March 2, 2025, 2:22pm

Hi all,

I use Vapor/Fluent to import product data into a Postgres database. The import files often contain data which already had been imported before, which causes unique constraint violations.

My first thought was to precheck whether a data record already exists before I save the model but because of possible future concurrency and performance issues I decided to accept the errors, although actually only one instance is running at the same time and performance is currently no bottleneck.

What bothers me are all the logentries in the db logs, but this is a minor issue.

My greater fear is, that I don't really know how to distinguish such a violation error from others that might happen when I do/catch the Model.save call. I don't wanna miss any bigger problems.

I tried to distinguish possible errors in Swift code but am a little bit overwhelmed, especially when I use String(reflecting: error) and get something like this:

PSQLError(code: server, serverInfo: [sqlState: 23505, detail: Key (brand_id, product_id, color_id, size_id)=(0855f070-7a71-4f11-91db-5b24f7d534ea, 03fae487-2d0a-4d7f-8fc1-c5059af1443d, df81a602-b618-46aa-9977-21d7c52d7b42, 26fb73d0-ba04-42b2-be25-90a0a32a4f49) already exists., file: nbtinsert.c, line: 666, message: duplicate key value violates unique constraint "uq:products_colors_sizes.brand_id+products_colors_sizes.product", routine: _bt_check_unique, localizedSeverity: ERROR, severity: ERROR, constraintName: uq:products_colors_sizes.brand_id+products_colors_sizes.product, schemaName: public, tableName: products_colors_sizes], triggeredFromRequestInFile: PostgresKit/PostgresDatabase+SQL.swift, line: 57, query: PostgresQuery(sql: INSERT INTO "table" ("id", "brand_id", "product_id", "color_id", "size_id", "created_at", "updated_at", "deleted_at") VALUES ($1, $2, $3, $4, $5, $6, $7, DEFAULT) RETURNING "id", binds: [(; UUID; format: binary), (; UUID; format: binary), (; UUID; format: binary), (; UUID; format: binary), (; UUID; format: binary), (; TIMESTAMPTZ; format: binary), (****; TIMESTAMPTZ; format: binary)]))

Everything works as intended but I'm wondering if there is a better/safer approach to this.

Best
Lars

0xTim · March 2, 2025, 2:36pm

Catch the error, try casting it to a DatabaseError and check if it's a constraint failure.

github.com/vapor/fluent-kit

Sources/FluentKit/Database/Database.swift

4f886ebdf


      
                  self.configuration = configuration
                  self.logger = logger
                  self.eventLoop = eventLoop
                  self.history = history
                  self.pageSizeLimit = pageSizeLimit
              }
          }
          
          public protocol DatabaseError {
              var isSyntaxError: Bool { get }
              var isConstraintFailure: Bool { get }
              var isConnectionClosed: Bool { get }
          }

LarsIverKruse · March 2, 2025, 5:26pm

Thanks, @0xTim

DatabaseError is what I was looking for. I've implemented it and it works well!