GRDB - Foreign Key and Coding Keys problem (cannot fetch data, return nil)

[31d4r]

First of all I will be thankful if I solve this issue, I know this description is a "bit huge" but it's better to explain more than short without any info.

Projects uses GRDB and Moya, so everything is seperated into small files. At the beginning there are two tables I need to fix Foreign Key, when I remove FK and whole column and re-install the app everything is working (also same for Documents.swift and DocumentsItem.swift when var documentTypeId is removed it's working).

It's confused because when I am fetching data from JSON for document_types I am getting real id (notNull) but when I want to feth data for documents FK (document_types_id) is always null. Only works when i put ? mark at the end of var (optional var) mean app is running but it's still nil value. Also I put JSON file to know how structured is everything.

In hope for solutions. Thanks !

Table document_types:

• id (PK),
• name,
• fields,
• deleted_at
• template_id

Table documents:

• id (PK),
• name,
• content,
• deleted_at
• document_type_id (FK)

This code down below represents pat of DB.swift (database, migrators, etc.)

        migrator.registerMigration("6") { db in
            try db.create(table: "documentTypes") { t in
                t.primaryKey(["id"])
                t.column("id", .integer).notNull()
                t.column("name", .text).notNull()
                t.column("fields", .text)
                t.column("deleted_at", .text)
                t.column("template_id", .integer)
            }
        }

        migrator.registerMigration("7") { db in
            try db.create(table: "documents") { t in
                t.primaryKey(["id"])
                t.column("id", .integer).notNull()
                t.column("name", .text).notNull()
                t.column("content", .text)
                t.column("documentTypesId", .integer)
                    .notNull().indexed()
                    .references("documentTypes", onDelete: .cascade)
            }
        }

This is code for Documents.swift, placed into /Models folder

import GRDB

struct Documents: Hashable {
    var id: Int64
    var name: String
    var documentTypesId: Int64

    enum CodingKeys: String, CodingKey {
        case id, name
        case documentTypesId = "document_type_id"

    }
}

extension Documents: Codable, FetchableRecord, MutablePersistableRecord {
    enum Columns {
        static let id = Column(CodingKeys.id)
        static let name = Column(CodingKeys.name)
        static let documentTypesId = Column(CodingKeys.documentTypesId)
    }
}

This code is for DocumentsItem.swift, placed into /RuntimeModels

import GRDB

struct DocumentsItem: Hashable, FetchableRecord {
    var id: Int64
    var name: String
    var documentTypesId: Int64
}

extension DocumentsItem: Codable {}

extension DocumentsItem: Equatable {
    static func == (lhs: Self, rhs: Self) -> Bool {
        lhs.id == rhs.id
    }
}

JSON file:

{
    "document_types": {
        "created": [
            {
                "id": 122,
                "name": "First category",
                "created_at": "2020-08-27 10:56:49",
                "updated_at": "2021-01-26 08:12:40",
                "deleted_at": null
            }
      ]
    },

    "documents": {
        "created": [
            {
                "id": 144,
                "name": "Document 1",
                "document_type_id": 122,
                "deleted_at": null,
                "created_at": "2021-01-14T07:36:31.000000Z",
                "updated_at": "2021-12-07T07:27:53.000000Z",
                "data": [
                    {
                        "name": "R1o=",
                        "type": "text",
                        "title": "GZ",
                        "config": {
                            "required": false,
                            "inputType": "text",
                            "showComment": null,
                            "maxValue": null,
                            "minValue": null,
                            "initialValue": null
                        },
                        "values": "850",
                        "placeholder": "Some document description"
                    }
                ],
                "files": [
                    {
                        "hash": "375024ff817e7183d12076c6ba80c3c7b9feba56d9a6981270d79f7104a21014",
                        "name": "logo.png"
                    }
                ]
            }
      ]
}
}

[groue]

Hello @31d4r,

Thanks for pasting code instead of pictures, as requested by email. This makes you a better online person :-) And thanks for pasting your database schema, it is a step in the right direction: we know which database we are talking about.

It's confused because [...] when I want to feth data for documents FK (document_types_id) is always null. Only works when i put ? mark at the end of var (optional var) mean app is running but it's still nil value.

But now we're in the fog. What are you talking about? Take your time, and answer as precisely as possible the questions that were asked when you filled the issue form:

### What did you do?

<!-- Please replace this with what you did. -->

### What did you expect to happen?

<!-- Please replace this with what you expected to happen. -->

### What happened instead?

<!-- Please replace this with of what happened instead. -->

### Environment

**GRDB flavor(s):** (GRDB, SQLCipher, Custom SQLite build?)
**GRDB version:**
**Installation method:** (CocoaPods, SPM, manual?)
**Xcode version:**
**Swift version:**
**Platform(s) running GRDB:** (iOS, macOS, watchOS?)
**macOS version running Xcode:**

[31d4r]

Hello @groue ,

for sure you are right we are in fog. This happens to me sometimes when I try to explain something but under pressure I have been lost somewhere and cannot explain it well...

What did you do?

I created two tables, added PK for both of them, added FK documentTypesId in documents table. This FK needs to reference a PK of documentTypes. Also as you can see in previous post I put code explanation.

What did you expect to happen?

As I wrote in section below, I want when I connect FK of documents table (Column: documentTypesId) with PK of documentTypes (Column: id). To get in console related data for each item in array. As shown in JSON. So I will be able to continue with app development and showing documents into next table view for selected row (id -> documnetTypesId).

What happened instead?

So in this section I will write few scenarios that happened in different ways.

First scenario Without FK column documentTypesId in table documents and without coding keys section into Documents.swift. So when I deleted this column and when I have removed: var documentTypesId: Int64, static let documentTypesId = Column(CodingKeys.documentTypesId) , app is working fine data is fetched (console output) and main table view is populated with main section from table documentTypes.

Second scenario

With FK column documentTypesId and coding keys into Documents.swift but making var documentTypesId: Int64? as optional main table view is populated with documentTypes items, getting console output but for the documnetTypesId I am getting nil what is normal because it's an optional.

Third scenario Same beginning as in second scenario with column and coding keys but this time removing optional for var documentTypesId: Int64. Main table view is not populated, now consoles outputs nothing and shows this error Could not perform operation. Please make sure you are connected to the internet and try again!

Image down below shows console output and arrays for documentTypes and documents items into it (for second scenario). Id for documentTypes show values fine but FK for documentTypesId into documents shows nil value. I now it's declared as an optional value but that's only way I can run the app and shows console output.

Hope so this time I explained much better so you will be able to understand.

Environment

GRDB flavor(s): GRDB GRDB version: 5.12 Installation method: CocoaPods Xcode version: 13.2.1 Swift version: 5.0+ Platform(s) running GRDB: iOS macOS version running Xcode: 12.2.1

[groue]

Main table view is not populated, now consoles outputs nothing and shows this error "Could not perform operation. Please make sure you are connected to the internet and try again!"

What makes you think this error is related to GRDB?