Remove Duplicate Entries Using KnexJS

Fri Nov 25 2022

I accidentally created duplicate entries in my database, so I wrote a function to remove them. The MySQl database connector I use frequently is KnexJS.

Here's how I did it:

ts
|
const removeDuplicateEntries = async (knex: Knex) => { // find duplicate entries const duplicates = await knex<MediaModel>('medias') .select('filepath') .from('medias') .groupBy('filepath') .havingRaw('count(*) > 1'); // remove duplicate entries for (const duplicate of duplicates) { if (duplicate.filepath && duplicate.filepath.length > 0) { // find the first entry of the duplicated record. const keep = await knex('medias') .select('id') .where('filepath', duplicate.filepath) .first(); if (keep && keep.id > 0) { // remove what is not the first entry. await knex('medias') .where('filepath', duplicate.filepath) .whereNot('id', keep.id).del(); } } } }