fix(collection): use UTF-8 byte length for SQL query size check (#3717)

yodakaEngineer · web-flow · commit 9f8402a2f98c · 2026-02-17T16:36:28.000+01:00
diff --git a/src/utils/collection.ts b/src/utils/collection.ts
@@ -119,9 +119,28 @@ export const MAX_SQL_QUERY_SIZE = 100000
 /**
  * When we split a value in multiple SQL queries, we want to allow for a buffer
  * so if the rest of the query is a bit long, we will not hit the 100KB limit
+ * NOTE: This is the byte limit, not the character limit
  */
 export const SLICE_SIZE = 70000
 
+const encoder = new TextEncoder()
+
+/**
+ * Calculate UTF-8 byte length of a string
+ */
+export function utf8ByteLength(str: string): number {
+  return encoder.encode(str).byteLength
+}
+
+/**
+ * Return the largest character index such that str.slice(0, result) has UTF-8 byte length <= targetBytes
+ */
+export function charIndexAtByteOffset(str: string, targetBytes: number): number {
+  const buf = new Uint8Array(targetBytes)
+  const { read } = encoder.encodeInto(str, buf)
+  return read
+}
+
 // Convert collection data to SQL insert statement
 export function generateCollectionInsert(collection: ResolvedCollection, data: ParsedContentFile): { queries: string[], hash: string } {
   const fields: string[] = []
@@ -176,7 +195,7 @@ export function generateCollectionInsert(collection: ResolvedCollection, data: P
   const sql = `INSERT INTO ${collection.tableName} VALUES (${'?, '.repeat(values.length).slice(0, -2)});`
     .replace(/\?/g, () => values[index++] as string)
 
-  if (sql.length < MAX_SQL_QUERY_SIZE) {
+  if (utf8ByteLength(sql) < MAX_SQL_QUERY_SIZE) {
     return {
       queries: [sql],
       hash: valuesHash,
@@ -186,7 +205,7 @@ export function generateCollectionInsert(collection: ResolvedCollection, data: P
   // Split the SQL into multiple statements:
   // Take the biggest column to insert (usually body) and split the column in multiple strings
   // first we insert the row in the database, then we update it with the rest of the string by concatenation
-  const biggestColumn = [...values].sort((a, b) => String(b).length - String(a).length)[0]
+  const biggestColumn = [...values].sort((a, b) => utf8ByteLength(String(b)) - utf8ByteLength(String(a)))[0]
   const bigColumnIndex = values.indexOf(biggestColumn!)
   const bigColumnName = fields[bigColumnIndex]
 
@@ -199,7 +218,7 @@ export function generateCollectionInsert(collection: ResolvedCollection, data: P
   }
 
   if (typeof biggestColumn === 'string') {
-    let sliceIndex = getSliceIndex(biggestColumn, SLICE_SIZE)
+    let sliceIndex = getSliceIndex(biggestColumn, charIndexAtByteOffset(biggestColumn, SLICE_SIZE))
 
     values[bigColumnIndex] = `${biggestColumn.slice(0, sliceIndex)}'`
     index = 0
@@ -211,7 +230,8 @@ export function generateCollectionInsert(collection: ResolvedCollection, data: P
     ]
     while (sliceIndex < biggestColumn.length) {
       const prevSliceIndex = sliceIndex
-      sliceIndex = getSliceIndex(biggestColumn, sliceIndex + SLICE_SIZE)
+      const rawIndex = charIndexAtByteOffset(biggestColumn.slice(sliceIndex), SLICE_SIZE) + sliceIndex
+      sliceIndex = rawIndex >= biggestColumn.length ? biggestColumn.length + 1 : getSliceIndex(biggestColumn, rawIndex)
 
       const isLastSlice = sliceIndex > biggestColumn.length
       const newSlice = `'${biggestColumn.slice(prevSliceIndex, sliceIndex)}` + (!isLastSlice ? '\'' : '')
diff --git a/test/unit/generateCollectionInsert.test.ts b/test/unit/generateCollectionInsert.test.ts
@@ -1,6 +1,6 @@
 import { beforeAll, describe, expect, test } from 'vitest'
 import { z } from 'zod'
-import { generateCollectionInsert, defineCollection, resolveCollection, getTableName, SLICE_SIZE, MAX_SQL_QUERY_SIZE } from '../../src/utils/collection'
+import { generateCollectionInsert, defineCollection, resolveCollection, getTableName, SLICE_SIZE, MAX_SQL_QUERY_SIZE, utf8ByteLength } from '../../src/utils/collection'
 import { initiateValidatorsContext } from '../../src/utils/dependencies'
 
 describe('generateCollectionInsert', () => {
@@ -123,4 +123,97 @@ describe('generateCollectionInsert', () => {
       index++
     }
   })
+
+  test('Split multibyte (UTF-8) values that exceed byte limit', () => {
+    const collection = resolveCollection('content', defineCollection({
+      type: 'data',
+      source: '**',
+      schema: z.object({
+        content: z.string(),
+      }),
+    }))!
+
+    // '心' is 3 bytes in UTF-8. 35000 chars = 105000 bytes > MAX_SQL_QUERY_SIZE (100000)
+    const content = '心'.repeat(35000)
+
+    const { queries: sql } = generateCollectionInsert(collection, {
+      id: 'multibyte.md',
+      stem: 'multibyte',
+      extension: 'md',
+      meta: {},
+      content,
+    })
+
+    // Must be split into multiple queries
+    expect(sql.length).toBeGreaterThan(1)
+
+    // Each query must fit within the byte limit
+    for (const query of sql) {
+      expect(utf8ByteLength(query)).toBeLessThan(MAX_SQL_QUERY_SIZE)
+    }
+
+    // First query should be INSERT, subsequent should be UPDATE
+    expect(sql[0]).toContain('INSERT INTO')
+    for (let i = 1; i < sql.length; i++) {
+      expect(sql[i]).toContain('UPDATE')
+    }
+
+    // Reconstruct the content from all queries and verify it matches the original
+    const insertMatch = sql[0]!.match(/'(心+)'/)
+    let reconstructed = insertMatch![1]!
+    for (let i = 1; i < sql.length; i++) {
+      const updateMatch = sql[i]!.match(/CONCAT\(content, '(心+)'\)/)
+      reconstructed += updateMatch![1]!
+    }
+    expect(reconstructed).toBe(content)
+  })
+
+  test('Succeed when SLICE_SIZE byte boundary falls on an emoji', () => {
+    const collection = resolveCollection('content', defineCollection({
+      type: 'data',
+      source: '**',
+      schema: z.object({
+        content: z.string(),
+      }),
+    }))!
+
+    // 'a' (1 byte) shifts alignment so that the SLICE_SIZE byte boundary
+    // falls in the middle of a '😀' (4 bytes in UTF-8)
+    // biggestColumn = "'a😀😀...😀'" → byte 0: quote(1), byte 1: 'a'(1), bytes 2+: emojis(4 each)
+    // Byte at SLICE_SIZE (70000) = 2 + 4*17499.5 → falls inside the 17500th emoji
+    const content = 'a' + '😀'.repeat(25000)
+
+    const { queries: sql } = generateCollectionInsert(collection, {
+      id: 'emoji-boundary.md',
+      stem: 'emoji-boundary',
+      extension: 'md',
+      meta: {},
+      content,
+    })
+
+    // Must be split into multiple queries
+    expect(sql.length).toBeGreaterThan(1)
+
+    // Each query must fit within the byte limit
+    for (const query of sql) {
+      expect(utf8ByteLength(query)).toBeLessThan(MAX_SQL_QUERY_SIZE)
+    }
+
+    // First query should be INSERT, subsequent should be UPDATE
+    expect(sql[0]).toContain('INSERT INTO')
+    for (let i = 1; i < sql.length; i++) {
+      expect(sql[i]).toContain('UPDATE')
+    }
+
+    // Reconstruct the content from all queries and verify no emoji was split
+    const insertMatch = sql[0]!.match(/'(a(?:😀)+)'/)
+    expect(insertMatch).not.toBeNull()
+    let reconstructed = insertMatch![1]!
+    for (let i = 1; i < sql.length; i++) {
+      const updateMatch = sql[i]!.match(/CONCAT\(content, '((?:😀)+)'\)/)
+      expect(updateMatch).not.toBeNull()
+      reconstructed += updateMatch![1]!
+    }
+    expect(reconstructed).toBe(content)
+  })
 })
