issues with upsert and createMany

[barbalex]

I create about 700 datasets when processing the results of a capabilities query i.e. getting properties of wms layers.

I am adding layer options into this table:

 
CREATE TYPE layer_options_field_enum AS enum(
  'wms_format',
  'wms_layer',
  'wms_info_format',
  'wfs_output_format',
  'wfs_layer'
);
 
CREATE TABLE layer_options(
  layer_option_id text PRIMARY KEY DEFAULT NULL,
  tile_layer_id uuid DEFAULT NULL REFERENCES tile_layers(tile_layer_id) ON DELETE CASCADE ON UPDATE CASCADE,
  vector_layer_id uuid DEFAULT NULL REFERENCES vector_layers(vector_layer_id) ON DELETE CASCADE ON UPDATE CASCADE,
  field layer_options_field_enum DEFAULT NULL,
  value text DEFAULT NULL,
  label text DEFAULT NULL,
  queryable boolean DEFAULT NULL,
  legend_url text DEFAULT NULL
);

I first built the insertions using create, that was before adding the boolean column queryable:

  const layerOptions = layers.map((l) => ({
    layer_option_id: `${row.wms_base_url}/${l.Name}/wms_layer`,
    tile_layer_id: row.tile_layer_id,
    field: 'wms_layer',
    value: l.Name,
    label: l.Title,
    legend_url: l.Style?.[0]?.LegendURL?.[0]?.OnlineResource,
  }))
  for (const l of layerOptions) {
    try {
      await db.layer_options.create({
        data: l,
      })
    } catch (error) {
      console.error('hello, getCapabilitiesData 5, error from creating:', error)
    }
  }

Works (also with the boolean column queryable) but is slow (takes about 30 seconds) due to the 700 datasets. Also: A wms service can have multiple layers, in this case over 700. Every time a single layer is created, the same 700 layer data is requeried. As this data can change (but rarely does), upsert would be preferrable to create.

So I changed the query to use upsert instead. First this worked fine:

  const layerOptions = layers.map((l) => ({
    layer_option_id: `${row.wms_base_url}/${l.Name}/wms_layer`,
    tile_layer_id: row.tile_layer_id,
    field: 'wms_layer',
    value: l.Name,
    label: l.Title,
    legend_url: l.Style?.[0]?.LegendURL?.[0]?.OnlineResource,
  }))
  for (const l of layerOptions) {
    try {
      await db.layer_options.upsert({
        create: l,
        update: l,
        where: {
          layer_option_id: l.layer_option_id,
        },
      })
    } catch (error) {
      console.error(
        'hello, getCapabilitiesData 5, error from upserting:',
        error,
      )
    }
  }

Then I added a boolean queryable to the table:

  const layerOptions = layers.map((l) => ({
    layer_option_id: `${row.wms_base_url}/${l.Name}/wms_layer`,
    tile_layer_id: row.tile_layer_id,
    field: 'wms_layer',
    value: l.Name,
    label: l.Title,
    queryable: l.queryable,
    legend_url: l.Style?.[0]?.LegendURL?.[0]?.OnlineResource,
  }))
  for (const l of layerOptions) {
    try {
      await db.layer_options.upsert({
        create: l,
        update: l,
        where: {
          layer_option_id: l.layer_option_id,
        },
      })
    } catch (error) {
      console.error(
        'hello, getCapabilitiesData 5, error from upserting:',
        error,
      )
    }
  }

BANG, this error occurs:

hello, getCapabilitiesData 5, error from upserting: ZodError: [
  {
    "code": "invalid_union",
    "unionErrors": [
      {
        "issues": [
          {
            "code": "invalid_union",
            "unionErrors": [
              {
                "issues": [
                  {
                    "code": "invalid_type",
                    "expected": "boolean",
                    "received": "number",
                    "path": [
                      "data",
                      "queryable"
                    ],
                    "message": "Expected boolean, received number"
                  }
                ],
                "name": "ZodError"
              },
              {
                "issues": [
                  {
                    "code": "invalid_type",
                    "expected": "object",
                    "received": "number",
                    "path": [
                      "data",
                      "queryable"
                    ],
                    "message": "Expected object, received number"
                  }
                ],
                "name": "ZodError"
              }
            ],
            "path": [
              "data",
              "queryable"
            ],
            "message": "Invalid input"
          },
          {
            "code": "unrecognized_keys",
            "keys": [
              "tile_layer_id"
            ],
            "path": [
              "data"
            ],
            "message": "Unrecognized key(s) in object: 'tile_layer_id'"
          }
        ],
        "name": "ZodError"
      },
      {
        "issues": [
          {
            "code": "invalid_union",
            "unionErrors": [
              {
                "issues": [
                  {
                    "code": "invalid_type",
                    "expected": "boolean",
                    "received": "number",
                    "path": [
                      "data",
                      "queryable"
                    ],
                    "message": "Expected boolean, received number"
                  }
                ],
                "name": "ZodError"
              },
              {
                "issues": [
                  {
                    "code": "invalid_type",
                    "expected": "object",
                    "received": "number",
                    "path": [
                      "data",
                      "queryable"
                    ],
                    "message": "Expected object, received number"
                  }
                ],
                "name": "ZodError"
              }
            ],
            "path": [
              "data",
              "queryable"
            ],
            "message": "Invalid input"
          }
        ],
        "name": "ZodError"
      }
    ],
    "path": [
      "data"
    ],
    "message": "Invalid input"
  }
]
    at get error (index.mjs:538:31)
    at _ZodObject.parse (index.mjs:638:22)
    at validate (validation.ts:14:31)
    at Table._update (table.ts:1258:34)
    at table.ts:1467:23
    at table.ts:569:15
    at Table.fetchIncludes (table.ts:770:14)
    at table.ts:564:23
    at transactionalDB.ts:49:11
    at adapter.ts:186:53

I am not passing numbers to the boolean field, only booleans.
Also, the keys it is not recognizing are defined and as mentioned, it worked when using create.

So I got thinking and decided to first deleteMany, then createMany. Thus circumventing the upsert error. And I was also hoping for it to be a lot faster:

  // 1. deleteMany
  const layerOptionIds = layers.map(
    (l) => `${row.wms_base_url}/${l.Name}/wms_layer`,
  )
  try {
    await db.layer_options.deleteMany({
      where: {
        layer_option_id: {
          in: layerOptionIds,
        },
      },
    })
  } catch (error) {
    console.error('hello, getCapabilitiesData 3, error:', error)
  }
  // 2. createMany
  const layerOptions = layers.map((l) => ({
    layer_option_id: `${row.wms_base_url}/${l.Name}/wms_layer`,
    tile_layer_id: row.tile_layer_id,
    field: 'wms_layer',
    value: l.Name,
    label: l.Title,
    queryable: l.queryable,
    legend_url: l.Style?.[0]?.LegendURL?.[0]?.OnlineResource,
  }))
  try {
    await db.layer_options.createMany({
      data: layerOptions,
    })
  } catch (error) {
    console.error('hello, getCapabilitiesData 6, error:', error)
  }

The deletion part works.
But createMany fails:

hello, getCapabilitiesData 6, error: Error: field value must be a string, number, boolean, null or one of the registered custom value types
    at _class16._sanitizeValue (squel.js:421:19)
    at _class16._setFieldsRows (squel.js:1743:26)
    at _class16.setFieldsRows (squel.js:1864:14)
    at _this32. [as setFieldsRows] (squel.js:2722:27)
    at Builder.createMany (builder.ts:59:8)
    at Table._createMany (table.ts:537:31)
    at executor.ts:75:7
    at new Promise ()
    at Executor.execute (executor.ts:74:12)
    at Table.createMany (table.ts:224:27)

[linear]

VAX-1616 issues with upsert and createMany

[kevin-dp]

Hi @barbalex, the issue with upsert may be the same as the one you encounter previously (#782). It looks like the boolean value which internally is stored as an integer is not being converted back to a boolean for some reason. Hence, Zod complaining. Will need to check why this is happening and keep you informed.

[KyleAMathews]

I'll just piggy-back on this issue as I found (I think) another case where zod is seeing an integer instead of a boolean

I have these two tables:

CREATE TABLE ingredients_photo_uploads (
    id UUID PRIMARY KEY,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    uploaded_at TIMESTAMP WITH TIME ZONE,
    upload_duration_sec FLOAT,
    ai_processing_duration_sec FLOAT,
    photo_url TEXT
);

CREATE TABLE ingredients (
    id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    is_reviewed BOOLEAN NOT NULL,
    fill_level INTEGER NOT NULL,
    is_ground BOOLEAN,
    ingredients_photo_uploads_id UUID,
    FOREIGN KEY (ingredients_photo_uploads_id) REFERENCES ingredients_photo_uploads(id)
);

ALTER TABLE ingredients_photo_uploads ENABLE ELECTRIC;
ALTER TABLE ingredients ENABLE ELECTRIC;

And this query:

db.ingredients_photo_uploads.liveMany({
      orderBy: {
        created_at: `asc`,
      },
      include: {
        ingredients: true,
      },
    })

Which errors with:

ZodError: [
  {
    "code": "invalid_union",
    "unionErrors": [
      {
        "issues": [
          {
            "code": "invalid_type",
            "expected": "boolean",
            "received": "number",
            "path": [
              "is_reviewed"
            ],
            "message": "Expected boolean, received number"
          },
          {
            "code": "invalid_type",
            "expected": "boolean",
            "received": "number",
            "path": [
              "is_ground"
            ],
            "message": "Expected boolean, received number"
          },
          {
            "code": "unrecognized_keys",
            "keys": [
              "ingredients_photo_uploads_id"
            ],
            "path": [],
            "message": "Unrecognized key(s) in object: 'ingredients_photo_uploads_id'"
          }
        ],
        "name": "ZodError"
      },
      {
        "issues": [
          {
            "code": "invalid_type",
            "expected": "boolean",
            "received": "number",
            "path": [
              "is_reviewed"
            ],
            "message": "Expected boolean, received number"
          },
          {
            "code": "invalid_type",
            "expected": "boolean",
            "received": "number",
            "path": [
              "is_ground"
            ],
            "message": "Expected boolean, received number"
          }
        ],
        "name": "ZodError"
      }
    ],
    "path": [],
    "message": "Invalid input"
  }
]
    at get error (index.mjs:538:31)
    at ZodUnion.parse (index.mjs:638:22)
    at nonTransactionalDB.ts:58:27
    at Array.map (<anonymous>)
    at nonTransactionalDB.ts:49:32

[barbalex]

I tried today with version 0.9.4 and the errors remain

[kevin-dp]

Hi @barbalex, i've not yet found the time to look into this bug. I hope to find some time soon to investigate further.

[barbalex]

@kevin-dp it's not blocking for me, so no Stress

[barbalex]

I tried today with electric-sql 0.9.6. Upserting now works 😆
But createMany still errors as before.

[msfstef]

@barbalex just to confirm - you're getting this error with createMany but you are not getting it when creating the exact same rows with create one by one?

[barbalex]

@barbalex just to confirm - you're getting this error with createMany but you are not getting it when creating the exact same rows with create one by one?

Exactly

[msfstef]

@barbalex could you check if this issue persists with v0.10.x? I'm wondering whether this is an issue related to #1095

If it still persists, is there any chance you could provide the sample dataset with the full data model so I can reproduce?

[barbalex]

@msfstef I now tried using electric-sql v0.10.1

upsert works (since 0.9.6).

createMany still errors with same error:

Two remarks:

1. I am chunking the array that is passed into createMany to 500 objects to not create errors due to too many objects

const chunked = chunkArrayWithMinSize(layerOptions, 500)
for (const chunk of chunked) {
  try {
    const chunkResult = await db.layer_options.createMany({ data: chunk })
    console.log('hello, getCapabilitiesData 5a, chunkResult:', chunkResult)
  } catch (error) {
    // field value must be a string, number, boolean, null or one of the registered custom value types
    console.error('hello, getCapabilitiesData 5b, error:', { error, chunk })
  }
}

2. I know that more than 500 can create an error because createMany works for me in a different table (implemented recently):

const chunked = chunkArrayWithMinSize(occurrences, 500)
for (const chunk of chunked) {
  await db.occurrences.createMany({
    data: chunk,
  })
}

where occurrences are defined as:

CREATE TABLE occurrences(
  occurrence_id uuid PRIMARY KEY DEFAULT NULL,
  account_id uuid DEFAULT NULL REFERENCES accounts(account_id) ON DELETE CASCADE ON UPDATE CASCADE,
  occurrence_import_id uuid DEFAULT NULL REFERENCES occurrence_imports(occurrence_import_id) ON DELETE CASCADE ON UPDATE CASCADE,
  place_id uuid DEFAULT NULL REFERENCES places(place_id) ON DELETE SET NULL ON UPDATE CASCADE,
  not_to_assign boolean DEFAULT NULL,
  comment text DEFAULT NULL,
  data jsonb DEFAULT NULL,
  id_in_source text DEFAULT NULL,
  geometry jsonb DEFAULT NULL,
  label text DEFAULT NULL
);

while layer_options are defined as:

CREATE TYPE layer_options_field_enum AS enum(
  'wms_format',
  'wms_layer',
  'wms_info_format',
  'wfs_output_format',
  'wfs_layer'
);

CREATE TABLE layer_options(
  layer_option_id text PRIMARY KEY DEFAULT NULL,
  account_id uuid DEFAULT NULL REFERENCES accounts(account_id) ON DELETE CASCADE ON UPDATE CASCADE,
  tile_layer_id uuid DEFAULT NULL REFERENCES tile_layers(tile_layer_id) ON DELETE CASCADE ON UPDATE CASCADE,
  vector_layer_id uuid DEFAULT NULL REFERENCES vector_layers(vector_layer_id) ON DELETE CASCADE ON UPDATE CASCADE,
  field layer_options_field_enum DEFAULT NULL,
  value text DEFAULT NULL,
  label text DEFAULT NULL,
  queryable boolean DEFAULT NULL,
  legend_url text DEFAULT NULL
);

[kevin-dp]

Hi @barbalex and @KyleAMathews, i tried reproducing this issue using your examples but didn't manage to reproduce it. I used your exact schemas and in case of Kyle the same query but the issue did not occur. If this is still a problem for you, could you share a reproducible example such that we can further investigate this issue.