Saving JSON in Postgres table with Fluent

Question

I'm looking for a recommendation for how to go about capturing the entire JSON body from a request and save it, along with some of the JSON contents, to a PSQL table. I have a column defined for the json:

.field(.string("json"), .json)

and I'm trying to save the data into the db but I can't seem to satisfy the .json type requirement. I continue to see this error:

server: column "json" is of type jsonb but expression is of type bytea (transformAssignedExpr)

when using this to convert the JSON:

if let reqData: Data = req.body.string?.data(using: .utf8) {
     try await Scan.query(on: req.db)
          .set(\.$json, to: reqData)
          .filter(\.$unitId == unitID)
          .update()
}

I'm certain the issue lies within the way I'm casting the req.body.string?.data(using: .utf8) and I've been fiddling with some of the other methods within the Request class and can't figure out if there is a built-in method for this or if I'm going to need to do some more work to perform a conversion. I have been looking for some resources on this and I'm having a tough time finding more information on what a common practice is for this.

I also attempted initially to use a jsonb type but to keep things simple I'm sticking with the json type for now since jsonb isn't really supported in Fluent.

Thanks in advance!

Answer 1

After a lot of searching for an easy way to do this, it appears that the only way to make this work is to: decode the entire JSON into a custom data type (swift model, not Fluent model) that accurately represents the JSON structure and conforms to PostgresJSONBCodable or PostgresJSONCodable depending on how you want to store it.

Once you have that data storage mechanism, you will decode the JSON into that (swift) model and then encode it into a PostgresData(jsonb: <#T##Data#>) and then make your db query to update the db.

This poses a significant challenge to handling dynamic JSON queries where the structure is unknown, but thankfully that is not the case here and I can rigidly define the data structure. This is sort of a common theme across Swift apps due to the nature of Swift and isn't necessarily a detractor if you value stability and speed.

I am using pieces of information found from these sources to make this determination:

• https://forums.swift.org/t/vapor-4-and-fluent-how-to-add-a-postgres-jsonb-column/34565
• https://losingfight.com/blog/2018/11/25/how-to-add-a-postgresql-jsonb-column-in-vapors-fluent/
• https://githubhelp.com/vapor/fluent-postgres-driver