Massively duplicating data when over 100 records

[spotvin42]

Bug report

Describe the bug

Firestore data migration tool: When I try to import 245 rows from the JSON file, a bit over 25,000 rows get uploaded.

I tested with JSON files of 5 entries or so and it works perfectly, but in case see below template:

{
  "age_range_min": 18,
  "age_range_max": 65,
  "distance_range_max": 42,
  "division": "All",
  "gender": 0,
  "heigth_range_min": 100,
  "heigth_range_max": 215,
  "looking_for_date": true,
  "looking_for_friends": true,
  "looking_for_short_term": false,
  "nationality": "All",
  "weight_range_min": 70,
  "weight_range_max": 320,
  "firestore_id": "GqMchob6ruTuUZs35CwkfUfpViQ2"
},
{
  "age_range_min": 18,
  "age_range_max": 65,
  "distance_range_max": 42,
  "division": "All",
  "gender": 0,
  "heigth_range_min": 100,
  "heigth_range_max": 215,
  "looking_for_date": true,
  "looking_for_friends": true,
  "looking_for_short_term": true,
  "nationality": "All",
  "weight_range_min": 70,
  "weight_range_max": 320,
  "firestore_id": "HGS7JysWZhfPPtbexH0d7OK0nts2"
},

To Reproduce

Steps to reproduce the behavior:

1. Go to 'Firestore directory'
2. Command line: 'node json2supabase.js users_filters.json serial'

No errors, but on supabase 25,000+ rows are added instead of 245

Expected behavior

A clear and concise description of what you expected to happen.

Screenshots

If applicable, add screenshots to help explain your problem.

System information

• OS: Windows
• Node.js: v16.13.2

"dependencies": {
    "@supabase/supabase-js": "^1.30.7",
    "firebase-admin": "^10.3.0",
    "firebase-scrypt": "^2.1.0",
    "moment": "^2.29.1",
    "pg": "^8.7.1",
    "stream-json": "^1.7.3"

Additional context

Seems like TS files were not compiled into the JS files (I've provided a few corrections as a push, I'll let you see), but it was not affecting this problem.

There are a few commented out lines in the code, it could possibly come from there.
ChatGPT says it has probably something to do with the fact that it runs asynchronously at some places.

[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 (

[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.

[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?

[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

[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.

[antonio-petrov]

Some more information to help out people:
The code is now called jsonStream.on('data', async ({key, value}) => { (single quotes for 'data' - it was with double quotes before).
Comment out those lines in the json2supabase.ts
if (insertRows.length >= 100) { // BATCH_SIZE
const result = await runSQL(makeInsertStatement(fields, insertRows));
insertRows = [];
}

        and then compile the file into .js with `tsc json2supabase.ts`