I am trying to learn how to use knex.js and as part of my learning I want to make some migration on my db, I want to update one of my columns. I am trying to do this with chunks (I know it is not the best solution, it's only for learning purposes). I am using MySQL. In my db I have a table that contains "email" column some of the rows end with ".com". I am trying to update this column by omitting the ".com". In my code, I select the relevant columns and I am using dash lib to chunk all my data. My chunked data looks like this:
my db : (https://ibb.co/c8kKtcb)
chunk no.1
[
RowDataPacket {id: 1, email: dan@gmail.com},
RowDataPacket {id: 2, email: john@gmail},
RowDataPacket {id: 3, email: steph@gmail},
]
chunk no.2
[
RowDataPacket {id: 4, email: eric@gmail},
RowDataPacket {id: 5, email: tommy@gmail.com},
RowDataPacket {id: 6, email: bill@gmail.com},
]
chunk no.3
[
RowDataPacket {id: 7, email: loe@gmail},
RowDataPacket {id: 8, email: helen@gmail.com},
RowDataPacket {id: 9, email: niky@gmail.com},
]
…
This is my code, I tried to do the update but I figure that I am doing something wrong since its list end .update() method work with key value. Can I manipulate my data?
exports.up = async knex => {
const users = await knex("usersWeb")
.select("id", "email")
.where("email", "like", "%.com");
const userChunks = _.chunk(users, 3);
let chunckNumber = 1;
for (const chunk of userChunks) {
console.log("Chunck number: ", chunckNumber);
const k = await knex("usersWeb")
.whereIn(columnId, chunk.map(item => item.id))
.update("email": ); // this line :(
}
chunckNumber++;
}
exports.down = async knex => {};
I solve it after some time and I forgot to share my solution, so here is it:
exports.up = async knex => {
const users = await knex("usersWeb")
.select("id")
.where("email", "like", "%.com");
const userChunks = _.chunk(users, 3);
let chunckNumber = 1;
for (const chunk of userChunks) {
console.log("Chunck number: ", chunckNumber);
const tupleOfAllEmailIds = "(" + chunk.map(item => `${item.id}`).join(", ") + ")";
const query = `UPDATE "usersWeb"
SET "email" = REPLACE("email", ".com", "")
WHERE id IN ${tupleOfAllEmailIds}
`;
await knex.raw(query);
}
chunckNumber++;
}
exports.down = async knex => {};