only raw queries work due to "invalid uuid". Uuid v7 are rejected

[barbalex]

In a previously working app I am suddenly no more able to query data.

Or rather: it seems that only raw queries work.

This is my boiled down component:

import { useEffect } from 'react'
import { useLiveQuery } from 'electric-sql/react'
import { useCorbadoSession } from '@corbado/react'

import { useElectric } from '../../ElectricProvider'

export const Main = () => {
  const { user: authUser } = useCorbadoSession()

  const { db } = useElectric()!
  const { results: appStateByEmail } = useLiveQuery(
    db.app_states.liveFirst({ where: { user_email: authUser?.email } }),
  )

  const { results: appStateById } = useLiveQuery(
    db.app_states.liveUnique({
      where: { app_state_id: '018ec37d-54b7-7d95-8bd9-a9117e1e7491' },
    }),
  )
  const { results: appStatesMany } = useLiveQuery(db.app_states.liveMany())

  console.log('from render', {
    appStateByEmail,
    appStateById,
    db,
    authUserEmail: authUser?.email,
    appStatesMany,
  })

  useEffect(() => {
    const run = async () => {
      const appStatesFromRawQuery = await db.rawQuery({
        sql: `select * from app_states;`,
      })
      console.log(
        'from useEffect, appStates from raw query:',
        appStatesFromRawQuery,
      )
      try {
        const appStatesFromFindMany = await db.app_states.findMany()
        console.log(
          'from useEffect, appStatesFromFindMany:',
          appStatesFromFindMany,
        )
      } catch (error) {
        console.error('from useEffect, appStatesFromFindMany error:', error)
      }
      try {
        const appStatesFromFindUnique = await db.app_states.findUnique({
          where: { app_state_id: '018ec37d-54b7-7d95-8bd9-a9117e1e7491' },
        })
        console.log(
          'from useEffect, appStatesFromFindUnique:',
          appStatesFromFindUnique,
        )
      } catch (error) {
        console.error(
          'from useEffect, appStatesFromFindUnique error:',
          error,
        )
      }
      try {
        const appStatesFromFindFirst = await db.app_states.findFirst({
          where: { user_email: authUser?.email },
        })
        console.log(
          'from useEffect, appStatesFromFindFirst:',
          appStatesFromFindFirst,
        )
      } catch (error) {
        console.error('from useEffect, appStatesFromFindFirst error:', error)
      }
    }
    run()
  }, [])

  return null
}

When this component runs the output in the console is:

So it seems that:

• data really exists as db.rawQuery returns the expected data (also: postgres contains the data as well, so it seems that sync is working correctly)
• only db.rawQuery works as expected
• all non raw queries return zod errors

I have been implementing auth and the new version 0.10 of electric-sql since yesterday. As part of this the syncing now also uses a where clause (only the data of the user with the email according to auth).

So there could be other reasons. But I feel that it may be due to the update to version 0.10.0. I am now running 0.10.1 but the error persists.

Edit: These are the table definitions:

CREATE TABLE app_states(
  app_state_id uuid PRIMARY KEY DEFAULT NULL,
  -- user_email can not be referenced to users, as it is not unique
  -- because electric-sql does not support unique constraints
  -- unless the column is a primary key
  user_email text DEFAULT NULL,
  -- user_id is needed to ensure user_email can be updated when changed on users
  user_id uuid DEFAULT NULL REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
  account_id uuid DEFAULT NULL REFERENCES accounts(account_id) ON DELETE CASCADE ON UPDATE CASCADE,
  designing boolean DEFAULT NULL, -- FALSE,
  breadcrumbs_overflowing boolean DEFAULT NULL, -- FALSE,
  navs_overflowing boolean DEFAULT NULL, -- FALSE,
  tabs jsonb DEFAULT NULL, -- array of strings
  map_bounds jsonb DEFAULT NULL, -- [minx, miny, maxx, maxy]
  show_local_map jsonb DEFAULT NULL, -- map of id (layer.id, key) and show boolean
  tile_layer_sorter text DEFAULT NULL,
  vector_layer_sorter text DEFAULT NULL,
  editing_place_geometry uuid DEFAULT NULL,
  editing_check_geometry uuid DEFAULT NULL,
  editing_action_geometry uuid DEFAULT NULL,
  occurrence_fields_sorted jsonb DEFAULT NULL, -- array of strings
  label text DEFAULT NULL
);

CREATE TABLE users(
  user_id uuid PRIMARY KEY DEFAULT NULL,
  email text DEFAULT NULL, -- TODO: email needs to be unique. But: not possible in electric-sql
  label_replace_by_generated_column text DEFAULT NULL
);

CREATE TABLE accounts(
  account_id uuid PRIMARY KEY DEFAULT NULL,
  user_id uuid DEFAULT NULL REFERENCES users(user_id) ON DELETE NO action ON UPDATE NO action,
  type text DEFAULT NULL,
  period_start date DEFAULT NULL,
  period_end date DEFAULT NULL,
  projects_label_by text DEFAULT NULL,
  label text DEFAULT NULL
);

...and the prisma client:

export type App_states = {
  /**
   * @zod.string.uuid()
   */
  app_state_id: string
  user_email: string | null
  /**
   * @zod.string.uuid()
   */
  user_id: string | null
  /**
   * @zod.string.uuid()
   */
  account_id: string | null
  designing: boolean | null
  breadcrumbs_overflowing: boolean | null
  navs_overflowing: boolean | null
  tabs: Prisma.JsonValue | null
  map_bounds: Prisma.JsonValue | null
  show_local_map: Prisma.JsonValue | null
  tile_layer_sorter: string | null
  vector_layer_sorter: string | null
  /**
   * @zod.string.uuid()
   */
  editing_place_geometry: string | null
  /**
   * @zod.string.uuid()
   */
  editing_check_geometry: string | null
  /**
   * @zod.string.uuid()
   */
  editing_action_geometry: string | null
  occurrence_fields_sorted: Prisma.JsonValue | null
  label: string | null
}

export type Users = {
  /**
   * @zod.string.uuid()
   */
  user_id: string
  email: string | null
  label: string | null
}

export type Accounts = {
  /**
   * @zod.string.uuid()
   */
  account_id: string
  /**
   * @zod.string.uuid()
   */
  user_id: string | null
  type: string | null
  period_start: Date | null
  period_end: Date | null
  projects_label_by: string | null
  label: string | null
}

...here the client schema:

export const App_statesSchema = z.object({
  app_state_id: z.string().uuid(),
  user_email: z.string().nullable(),
  user_id: z.string().uuid().nullable(),
  account_id: z.string().uuid().nullable(),
  designing: z.boolean().nullable(),
  breadcrumbs_overflowing: z.boolean().nullable(),
  navs_overflowing: z.boolean().nullable(),
  tabs: NullableJsonValue.optional(),
  map_bounds: NullableJsonValue.optional(),
  show_local_map: NullableJsonValue.optional(),
  tile_layer_sorter: z.string().nullable(),
  vector_layer_sorter: z.string().nullable(),
  editing_place_geometry: z.string().uuid().nullable(),
  editing_check_geometry: z.string().uuid().nullable(),
  editing_action_geometry: z.string().uuid().nullable(),
  occurrence_fields_sorted: NullableJsonValue.optional(),
  label: z.string().nullable(),
})

export const UsersSchema = z.object({
  user_id: z.string().uuid(),
  email: z.string().nullable(),
  label: z.string().nullable(),
})

export const AccountsSchema = z.object({
  account_id: z.string().uuid(),
  user_id: z.string().uuid().nullable(),
  type: z.string().nullable(),
  period_start: z.coerce.date().nullable(),
  period_end: z.coerce.date().nullable(),
  projects_label_by: z.string().nullable(),
  label: z.string().nullable(),
})

I repeatedly rebuilt all the data by recreating the db from scratch including all its volumes, migrating it, generating the client and reloading it after clearing all client side data.

The errors mention two things:

• unrecognized_keys: both mentioned keys exist as is visible in the line just above the error log
• Invalid uuid for app_state_id: this is a valid v7 uuid as all the uuid's this app is using. The used uuid is actually the same that worked in previous versions of electric-sql, as it is part of the test-data imported on app startup

Edit: I just tried downgrading electric to 0.9.4 (docker) and 0.9.6 (app) and the errors persist. So not an error caused in the version uprade.

[linear]

VAX-1781 Suddenly only raw queries work

[barbalex]

I now built a branch of the app where there are no references to other tables in app_states.

unrecognized keys does not appear in the errors any more. But Invalid uuid is still claimed.
Also: The query for projects does (still) not return any data either...

[barbalex]

I now built another branch of the app. This time the user_email is the primary key of the app_states table. Thus there is no way there could be an offending uuid.

This would probably not work for the app but I just built this to test this issue.

What is nice:

• no error is thrown
• app_states are correctly queried

BUT:

• no other data is queried using typed queries (all use an uuid primary key)
• using raw queries, other data is returned. As before with the app_states
• even though no data is returned, no error is thrown

So now I am completely at a loss how to get my project working again 🤷
Well, asides from refactoring all 982 queries to use raw versions...

This project has always been using v7 uuids. Is it possible that this (suddenly) causes an issue?

[barbalex]

I now built another branch of my project.

This time all uuid's were converted to text.

And the queries work again.

So it really seems that the uuid's are the culprit?

[barbalex]

So I now built a branch of the project using uuid v4 instead of uuid v7. It works!

So my interpretation is that there must be a place in the implementation of electric-sql / prisma or whatever that checks the validity if uuid's and unfortunately rejects v7 uuid's.

This must be new as my app has been using them for months and so far it has worked.

By the way, to create uuid v7 I was using:

• in code: @kripod/uuidv7 version 0.3.4
• for test data: https://www.uuidgenerator.net/version7. It seems they use uuidv7 to generate the v7 uuid's (https://www.uuidgenerator.net/dev-corner/javascript)

[barbalex]

It seems that zod is not the culprit. I tested two of my v7 uuid's here: https://codesandbox.io/p/sandbox/js-playground-forked-dvtnl3?file=%2Fsrc%2Findex.js%3A7%2C33 (watch the values logged in dev tools)

[samwillis]

I think I know whats happing, Zod seems to have changed how it validates UUIDs, version 3.22.4 correctly validates UUID V7 whereas 3.21.4 (currently specified by Electric) rejects them.

I forked your sandbox and changed the Zod version to match that in your projects package_lock.json and it rejects them: https://codesandbox.io/p/sandbox/js-playground-forked-dvtnl3?file=%2Fsrc%2Findex.js

We obviously need to change the version of Zod that's specified. @kevin-dp could you take a look, I know you had concerns about Zod versions before?

[barbalex]

YESSSSSS!

I installed the current version of zod in my project and now the v7 uuid's are working just fine.

Thanks so much @samwillis

[kevin-dp]

We pinned Electric to Zod version 3.21.1 because something changed in later versions of Zod that can cause the generated client to contain type errors, cf. #700 for more information.

[barbalex]

So it seems we are all waiting for a new version of zod as the last one happened in oktober 2023 and a (hopefully) solution was merged three weeks ago.

[thruflo]

@kevin-dp could we have a clearer section in our docs about pinned dependencies? I.e.: "you must use the pinned versions of these libraries ...".