query including referenced table errors sometimes

[barbalex]

I have tried to add data from a referenced table to a query.

The query is:

const { error, results } = useLiveQuery(
    () => db[queryTable]?.liveMany(queryParam),
    [db, table],
  )

When queryTable is 'projects` and queryParam is:

{
  include: {
    subprojects: true
  }
  where: {
    deleted: false
  }
}

the result is as expected: an array of objects including an array of subprojects in the subprojects key.

If the queryTable is 'subprojects' and the queryParam is:

{
  include: {
    projects: true
  }
  where: {
    deleted: false
  }
}

I get an ZodError somewhat like this:

First I thought it had to do with the direction of the relation (my examples errored when the included table was on the 1 side, worked when it was on the n side).

But the error seems to point to an issue with boolean columns.

[linear]

VAX-1460 query including referenced table errors sometimes

[barbalex]

Some more info:

The tables mentioned above are defined like this:

CREATE TABLE projects(
  project_id uuid PRIMARY KEY DEFAULT NULL, -- public.uuid_generate_v7(),
  account_id uuid DEFAULT NULL REFERENCES accounts(account_id) ON DELETE CASCADE ON UPDATE CASCADE,
  name text DEFAULT NULL,
  label_replace_by_generated_column text DEFAULT NULL,
  type text DEFAULT NULL,
  subproject_name_singular text DEFAULT NULL,
  subproject_name_plural text DEFAULT NULL,
  subproject_order_by text DEFAULT NULL,
  values_on_multiple_levels text DEFAULT NULL,
  multiple_action_values_on_same_level text DEFAULT NULL,
  multiple_check_values_on_same_level text DEFAULT NULL,
  data jsonb DEFAULT NULL,
  files_active_projects boolean DEFAULT NULL,
  files_active_projects_reports boolean DEFAULT NULL,
  files_active_subprojects boolean DEFAULT NULL,
  files_active_subproject_reports boolean DEFAULT NULL,
  files_active_places boolean DEFAULT NULL,
  files_active_actions boolean DEFAULT NULL,
  files_active_checks boolean DEFAULT NULL,
  files_active_check_reports boolean DEFAULT NULL,
  deleted boolean DEFAULT NULL
);
CREATE TABLE subprojects(
  subproject_id uuid PRIMARY KEY DEFAULT NULL, 
  account_id uuid DEFAULT NULL REFERENCES accounts(account_id) ON DELETE CASCADE ON UPDATE CASCADE,
  project_id uuid DEFAULT NULL REFERENCES projects(project_id) ON DELETE CASCADE ON UPDATE CASCADE,
  name text DEFAULT NULL,
  label_replace_by_generated_column text DEFAULT NULL,
  since_year integer DEFAULT NULL,
  data jsonb DEFAULT NULL,
  deleted boolean DEFAULT NULL
);

[kevin-dp]

Hi @barbalex, the error Zod throws seems to be unrelated to what you're describing.
Zod is complaining that you're passing a number to a property "use_for_action_values" but it is expecting a boolean.
A wild guess would be that you have a column called "use_for_action_values" of some number type and you are including it as follows:

include: {
  use_for_action_values: someNumber
}

Inside include, the value of the use_for_action_values property should be a boolean (indicating whether or not it should be included) or a nested include object (if that property represents a related table), e.g.:

include: {
  use_for_action_values: {
    include: {
      ...
    }
  }
}

[kevin-dp]

PS: your usage of useLiveQuery seems a bit odd.
It would be better to use it as follows:

const { error, results } = useLiveQuery(
  db.projects.liveMany(queryParam)
)

i.e. you just have to call a live query inside the useLiveQuery hook.
Like this, db.projects should provide you additional type information and should even complain if your queryParam has the wrong type.

[barbalex]

your usage of useLiveQuery seems a bit odd

I encountered cases where the query would not live update as expected. Then I found these docs: https://electric-sql.com/docs/integrations/frontend/react#query-dependencies

That solved my issue. Since then this is how I used it.

[barbalex]

To the main issue.

I am not including anything else then:

If the queryTable is 'subprojects' and the queryParam is:

{
  include: {
    projects: true
  }
  where: {
    deleted: false
  }
}

In this query the exact error mentions: expected boolean, received number, referring to the field files_active_projects. That is one of the boolean fields existing in the projects table. It is set to true when new projects are created.

I have no idea why the error occurs.

One thing I do not completely understand is the usage of boolean values. As far as I understand I can ignore that sqlite expects 0 and 1 and simply use true and false in my client side code. As happens, when setting files_active_projects to true in new projects. And also when filtering for not deleted rows in above query params. Is that a correct assumption?

[barbalex]

What I dont get is why this works when projects are queried, including the subprojects. The result of the query then is:

In this case the included subproject table also has a boolean field, preset to false. That seems to be no problem.

On the other hand when I include projects when querying subprojects, the files_active_projects column, preset to true, seems to cause a problem.

[barbalex]

By the way: I have adjusted the query to not use a function and the error prevails:

const { results, error } = useLiveQuery(db[queryTable]?.liveMany(queryParam))

[barbalex]

Because above dynamic query introduces some more complexity, I have tried including projects in the subproject form that uses this query:

  const { results, error } = useLiveQuery(
    db.subprojects.liveUnique({
      where: { subproject_id },
      include: { projects: true },
    }),
  )

Same error. Here the full error:

{
    "issues": [
        {
            "code": "invalid_union",
            "unionErrors": [
                {
                    "issues": [
                        {
                            "code": "invalid_type",
                            "expected": "boolean",
                            "received": "number",
                            "path": [
                                "files_active_projects"
                            ],
                            "message": "Expected boolean, received number"
                        },
                        {
                            "code": "invalid_type",
                            "expected": "boolean",
                            "received": "number",
                            "path": [
                                "files_active_projects_reports"
                            ],
                            "message": "Expected boolean, received number"
                        },
                        {
                            "code": "invalid_type",
                            "expected": "boolean",
                            "received": "number",
                            "path": [
                                "files_active_subprojects"
                            ],
                            "message": "Expected boolean, received number"
                        },
                        {
                            "code": "invalid_type",
                            "expected": "boolean",
                            "received": "number",
                            "path": [
                                "files_active_subproject_reports"
                            ],
                            "message": "Expected boolean, received number"
                        },
                        {
                            "code": "invalid_type",
                            "expected": "boolean",
                            "received": "number",
                            "path": [
                                "files_active_places"
                            ],
                            "message": "Expected boolean, received number"
                        },
                        {
                            "code": "invalid_type",
                            "expected": "boolean",
                            "received": "number",
                            "path": [
                                "files_active_actions"
                            ],
                            "message": "Expected boolean, received number"
                        },
                        {
                            "code": "invalid_type",
                            "expected": "boolean",
                            "received": "number",
                            "path": [
                                "files_active_checks"
                            ],
                            "message": "Expected boolean, received number"
                        },
                        {
                            "code": "invalid_type",
                            "expected": "boolean",
                            "received": "number",
                            "path": [
                                "files_active_check_reports"
                            ],
                            "message": "Expected boolean, received number"
                        },
                        {
                            "code": "unrecognized_keys",
                            "keys": [
                                "account_id"
                            ],
                            "path": [],
                            "message": "Unrecognized key(s) in object: 'account_id'"
                        }
                    ],
                    "name": "ZodError"
                },
                {
                    "issues": [
                        {
                            "code": "invalid_type",
                            "expected": "boolean",
                            "received": "number",
                            "path": [
                                "files_active_projects"
                            ],
                            "message": "Expected boolean, received number"
                        },
                        {
                            "code": "invalid_type",
                            "expected": "boolean",
                            "received": "number",
                            "path": [
                                "files_active_projects_reports"
                            ],
                            "message": "Expected boolean, received number"
                        },
                        {
                            "code": "invalid_type",
                            "expected": "boolean",
                            "received": "number",
                            "path": [
                                "files_active_subprojects"
                            ],
                            "message": "Expected boolean, received number"
                        },
                        {
                            "code": "invalid_type",
                            "expected": "boolean",
                            "received": "number",
                            "path": [
                                "files_active_subproject_reports"
                            ],
                            "message": "Expected boolean, received number"
                        },
                        {
                            "code": "invalid_type",
                            "expected": "boolean",
                            "received": "number",
                            "path": [
                                "files_active_places"
                            ],
                            "message": "Expected boolean, received number"
                        },
                        {
                            "code": "invalid_type",
                            "expected": "boolean",
                            "received": "number",
                            "path": [
                                "files_active_actions"
                            ],
                            "message": "Expected boolean, received number"
                        },
                        {
                            "code": "invalid_type",
                            "expected": "boolean",
                            "received": "number",
                            "path": [
                                "files_active_checks"
                            ],
                            "message": "Expected boolean, received number"
                        },
                        {
                            "code": "invalid_type",
                            "expected": "boolean",
                            "received": "number",
                            "path": [
                                "files_active_check_reports"
                            ],
                            "message": "Expected boolean, received number"
                        }
                    ],
                    "name": "ZodError"
                }
            ],
            "path": [],
            "message": "Invalid input"
        }
    ],
    "name": "ZodError"
}

[kevin-dp]

Right, i see. The error seems to occur when trying to validate the data that is being read from the database using the Zod schema. So indeed, internally the boolean value is a number (0 or 1) and our data access library takes care of converting those to JavaScript booleans. Probably what's happening is that the number is not being converted back to a boolean when reading from the database and thus Zod complains. Will need to investigate this further.

[barbalex]

I tried this again in v0.9.0 and it works.
Thanks!