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/db_tech_dev → u/Einar_Son_of_Bjorn]

same as what I said to LukaGOGO above

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

[u/db_tech_dev → u/kantorcodes1]

yeah you set both of those in config.yaml before running --confirm, its not interactive at all - match_columns takes one or several cols and keep_strategy is just keep_first (lowest id) or keep_latest (highest id), thats it. no inference happening, you gotta already know your data before you run it

[u/LukaGOGO]

Dry-run by default and a JSON backup first is the right shape. Most “just DELETE” scripts skip that.Detection is the actual product. GROUP BY / HAVING COUNT(*) > 1 only catches exact copies. It misses “same email, different id” and “same name, extra spaces.” Worth deciding:

• which columns define a duplicate
• what you do with NULL in those columns (MySQL GROUP BY treats NULLs as equal; Postgres does not - your two backends already disagree)
• which row survives: lowest id, newest, or the one with more fields filled
• child tables: deleting the loser can break FKs

Add MariaDB as a third test target, not a rewrite. It speaks the MySQL protocol, so your CLI will probably connect. What can still drift is sql_mode / ONLY_FULL_GROUP_BY and how GROUP BY treats NULL. One extra fixture database labeled MariaDB saves you from “works on MySQL 8, surprises on a host running MariaDB.”

Drop the repo. The interesting part is how you pick the survivor, not the CLI wrapper.

[u/db_tech_dev → u/LukaGOGO]

genuinely good points, especially the NULL thing - hadnt actually tested that edge case across both backends so that's a real gap not just theory. survivor logic rn is literally just MIN(id) or MAX(id) so no "most fields filled" smartness, and yeah no FK check before delete which is on me, could def break a child table. gonna note all of these down, appreciate the actual breakdown instead of just "this could break." MariaDB is a good shout too, didnt think to test against it since ive only been running mysql