Massively duplicating data when over 100 records about firebase-to-supabase

[spotvin42]

I found a temporary solution, I've commented out the batching in jsonStream.on("data":

// if (insertRows.length >= 100) {
      //   // BATCH_SIZE
      //   const result = await runSQL(makeInsertStatement(fields, insertRows));
      //   insertRows = [];
// }

The issue being that the same code is being launched in jsonStream.on("end":

jsonStream.on("end", async () => {
      const result = await runSQL(makeInsertStatement(fields, insertRows));
      resolve("DONE");
    });

So somehow as the insertRows[] gets emptied, it gets filled with the same data and gets sent again in a new batch until 100 times (?) later.

Worth looking further to help the community, I didn't pinpoint the root cause, probably something related to async code.

[spotvin42]

I've played around and managed to get an error message that could help pin point the area of problem:

runSQL error: error: duplicate key value violates unique constraint "users_uid_key"
    at Parser.parseErrorMessage (C:\Users\User\Documents\Code\fb2sb\firebase-to-supabase\node_modules\pg-protocol\dist\parser.js:287:98)
    at Parser.handlePacket (C:\Users\User\Documents\Code\fb2sb\firebase-to-supabase\node_modules\pg-protocol\dist\parser.js:126:29)
    at Parser.parse (C:\Users\User\Documents\Code\fb2sb\firebase-to-supabase\node_modules\pg-protocol\dist\parser.js:39:38)
    at Socket.<anonymous> (C:\Users\User\Documents\Code\fb2sb\firebase-to-supabase\node_modules\pg-protocol\dist\index.js:11:42)
    at Socket.emit (node:events:390:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:199:23) {
  length: 214,
  severity: 'ERROR',
  code: '23505',
  detail: 'Key (uid)=(04Ep1qjsohdFua9ANBrtVpveB152) already exists.',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: 'public',
  table: 'users',
  column: undefined,
  dataType: undefined,
  constraint: 'users_uid_key',
  file: 'nbtinsert.c',
  line: '664',
  routine: '_bt_check_unique'
}
sql was: insert into "users" ("uid","username","phone","email","account_status","is_premium","is_visible","number_of_swipes_today","is_tuto_seen","is_account_created","push_token") values (

[3IMAD69]

I found a temporary solution, I've commented out the batching in jsonStream.on("data":

// if (insertRows.length >= 100) {
      //   // BATCH_SIZE
      //   const result = await runSQL(makeInsertStatement(fields, insertRows));
      //   insertRows = [];
// }

The issue being that the same code is being launched in jsonStream.on("end":

jsonStream.on("end", async () => {
      const result = await runSQL(makeInsertStatement(fields, insertRows));
      resolve("DONE");
    });

So somehow as the insertRows[] gets emptied, it gets filled with the same data and gets sent again in a new batch until 100 times (?) later.

Worth looking further to help the community, I didn't pinpoint the root cause, probably something related to async code.

Had the same problem , You saved my Life

[organicnz]

@spotvin42 hi mate. Thank you for reporting this issue. I'm also encountering the same problem :)

I'm trying to migrate a Firestore collection Users to Supabase and rename then this to the profiles table, but instead of ~1250 records Supabase creates and dublicates around ~220000 for some reason :)

Coometing hasn't helped so far and I've been using these commands within firestore directory.

Dump Firestore collection to JSON file:
node firestore2json.js Users 1000 0

Import JSON file to Supabase (PostgreSQL)
node json2supabase.js ./Users.json uuid id

[spotvin42]

@burggraf could you please look at it?

[atothewest]

I believe the issue here is that the callback function provided to on is flagged as async, but EventEmitter in Node processes events synchronously and will ignore the return value of the callback function (in this case a Promise that we want to await). So insertRows is not getting cleared as we would expect after each batch, because Node isn't waiting for that database operation to complete (and the call to set insertRows = [] effectively gets orphaned in its own thread).

The solution offered by @spotvin42 works by virtue of collecting every into a single array before processing all at once at the end once the stream has finished parsing. However, this has potential to cause performance/memory issues in the case of very large collections.

An alternative which also avoids the problem of processing all records in a single batch is simply to remove the async/await from the callback function entirely.

if (insertRows.length >= 100) { // BATCH_SIZE
  runSQL(makeInsertStatement(fields, insertRows));
  insertRows = [];
}

In practice it'd probably be better to open a database transaction and then commit in the jsonStream.on('end') callback, but hopefully this helps give a bit more context to the issue.