DT
Drizzle Team•2mo ago
McNugget

db.insert not respecting order of rows

Having an issue where I'm inserting data into the db via a for loop. However the data gets inserted in a different order. Any ideas? 
await db.transaction(async (trx) => {
    await trx.delete(sessionTypes).execute();
    console.log('after del', await trx.select().from(sessionTypes).execute());
    for (const session of sTypes) {
        console.log('ins', session);
        await trx.insert(sessionTypes).values(session).execute();
    }
    console.log('after ins', await trx.select().from(sessionTypes).execute());
});
await db.transaction(async (trx) => {
    await trx.delete(sessionTypes).execute();
    console.log('after del', await trx.select().from(sessionTypes).execute());
    for (const session of sTypes) {
        console.log('ins', session);
        await trx.insert(sessionTypes).values(session).execute();
    }
    console.log('after ins', await trx.select().from(sessionTypes).execute());
});
Produces: 
after del []

------

ins {
  id: 'YnfPzN2kyorNZ27LlAtec',
  name: 'Test2',
  category: 'Test',
  length: 30,
  rating: 3
}
ins {
  id: 'nx-eVe-eV5_5EgH_mxY_K',
  name: 'Test3',
  category: 'Test',
  length: 30,
  rating: 2
}
ins {
  id: 'reilg3cziuG7ZoDFHVV6Y',
  name: 'Test3',
  category: 'Test2',
  length: 30,
  rating: 3
}

------

// index 3 is put to the back and everything is shifted foward one

after ins [
  {
    id: 'nx-eVe-eV5_5EgH_mxY_K',
    name: 'Test3',
    category: 'Test',
    length: 30,
    rating: 2
  },
  {
    id: 'reilg3cziuG7ZoDFHVV6Y',
    name: 'Test3',
    category: 'Test2',
    length: 30,
    rating: 3
  },
  {
    id: 'YnfPzN2kyorNZ27LlAtec',
    name: 'Test2',
    category: 'Test',
    length: 30,
    rating: 3
  }
]
after del []

------

ins {
  id: 'YnfPzN2kyorNZ27LlAtec',
  name: 'Test2',
  category: 'Test',
  length: 30,
  rating: 3
}
ins {
  id: 'nx-eVe-eV5_5EgH_mxY_K',
  name: 'Test3',
  category: 'Test',
  length: 30,
  rating: 2
}
ins {
  id: 'reilg3cziuG7ZoDFHVV6Y',
  name: 'Test3',
  category: 'Test2',
  length: 30,
  rating: 3
}

------

// index 3 is put to the back and everything is shifted foward one

after ins [
  {
    id: 'nx-eVe-eV5_5EgH_mxY_K',
    name: 'Test3',
    category: 'Test',
    length: 30,
    rating: 2
  },
  {
    id: 'reilg3cziuG7ZoDFHVV6Y',
    name: 'Test3',
    category: 'Test2',
    length: 30,
    rating: 3
  },
  {
    id: 'YnfPzN2kyorNZ27LlAtec',
    name: 'Test2',
    category: 'Test',
    length: 30,
    rating: 3
  }
]
2 Replies
Jetrak
Jetrak•2mo ago
Not sure I understand the question/problem correctly, but in the after ins part you are querying a database without specifying the order by, so you are probably getting them sorted how the database seems fit.
Mario564
Mario564•2mo ago
Adding onto what Jetrak said, if not order by clause is specified, it will likely sort by the column that has the primary key. For serial IDs, this would still present the data in order, but with UUIDs (like in this case), the sorting will look random

Did you find this page helpful?