Built a small CLI tool to find/clean duplicate rows in MySQL & PostgreSQL, feedback welcome

[u/db_tech_dev]

been dealing with duplicate customer records in a project for uni and kept rewriting the same GROUP BY/HAVING query every time so i just built a cli tool for it in the end - works with both mysql and postgres, dry run by default so nothing gets deleted unless u explicitly pass --confirm and it backs up to json first just in case. still a student so the detection logic is prob missing some edge cases; that's the part i actually want feedback on tbh. happy to share the repo if anyone's curious, can drop the repo link

[u/Phil_P]

Look into the concepts around integrity constraints, particularly unique constraints in this case. The goal is to not allow bad data to be saved to the database rather than cleaning it up later.

[u/Einar_Son_of_Bjorn]

Dry-run by default plus a JSON backup is the right shape.

That’s the part most “just DELETE FROM …” scripts skip.The detection logic is the whole product. GROUP BY / HAVING COUNT(*) > 1 is fine for exact copies. It lies when “duplicate” means same email different id, or same name different whitespace. Worth handling:

• which columns define a duplicate (not “the whole row”)
• NULL in those columns (GROUP BY treats NULLs as equal in MySQL, not in Postgres - already a fork in your two backends)
• unique key you keep: lowest id, newest row, or a row that has more fields filled
• child tables: deleting the loser breaks FKs

If you built it against MySQL, run the same suite on MariaDB. Same protocol, slightly different GROUP BY / ONLY_FULL_GROUP_BY habits depending on sql_mode.Drop the repo. The interesting bit is how you pick the survivor, not the CLI wrapper.

[u/gumnos]

I usually do this with a pair of queries, an initial one to identify duplicates, something like

SELECT Min(id)
FROM tbl
GROUP BY col1, col2, col3 -- columns that should be unique together
HAVING COUNT(*) > 1

I can then view the set of first-duplicates with

SELECT *
FROM tbl
WHERE id IN (
-- the above query
)

I can then change that SELECT * to a DELETE and delete the first duplicate. If records have more than one duplicate I can rerun the DELETE until it deletes 0 rows.

[u/kantorcodes1]

does the CLI make you choose both the duplicate key columns and the survivor row before --confirm, or does it infer either of those?